# Sunday, 29 April 2007

Some useful links with personal impressions, valuable resources or important information and their date posted. Don't forget, always check the preparation guide (last update 2007-03-20), both as a road map for your preparation and just prior to taking the exam (maybe things have changed since you started).

The links prior to 2007 don't include info on the simulation questions. The first time I heard about the simulation questions was in December 2006, it seems they were running beta into March. Starting March 2007 I heard and read about failing exams because of the simulation questions, which seem to make up a very big part of the overall score for the exam.

Must read is the discussion on simulations (2007-04-12) started by Zieglers after he passed his 70-431 (2007-01-23). Some very valuable links can be found in the discussion, including this simulation example.

Also check out the database certification newsgroup microsoft.public.cert.mcdba. This newsgroup is aimed at all SQL Server exams and certifications, not just 70-431.

Other links:

If you use the MCTS Self-Paced Training Kit (Exam 70-431): Microsoft® SQL Server™ 2005—Implementation and Maintenance, check out the comments and corrections.

Please post a comment if you have other good and free resources as well.

*UPDATED 2007-05-05*

Sunday, 29 April 2007 10:38:37 (W. Europe Daylight Time, UTC+02:00)
# Saturday, 28 April 2007

Initially I started writing this article because of the poor quality of Module 4, Lesson 4 in the original version of the course MOC 2780. What's wrong with those 6 pages? Technically nothing, but if you've never seen what cryptography does in security systems, you're lost. Fortunately Microsoft Learning has acknowledged the course and its timetable were "suboptimal" and will be releasing the B-revision soon. From a MOC2780B (revised module 4, lesson 4) perspective, this article can be considered additional reading.

In much the same way as in MOC2780A, the entire Module 4 of the MOC 2787 is affected as well. Therefor reading this article is recommended in preparation of the exams 70-443, 70-444 and 70-447.

To be honest, I don't know how relevant the encryption stuff is towards the exam 70-431. I don't recall it from the beta exam I sat, nor have I read reports that specifically included cryptography. The preparation guide provides little guidance on it, it only says Configure encryption under Installing and Configuring SQL Server 2005, Configure SQL Server security, but that may be aimed at protocol encryption. Should you want to play at safe while preparing based on the MS Press Self-Paced Training Kit "SQL Server 2005, Implementation and Maintenance" (ISBN-10: 0-7356-2271-X), this article provides the necessary background for Chapter 2, Lesson 6. If you prepare for the exam based on the Sybex MCTS Study Guide "Microsoft SQL Server 2005 Implementation and Maintenance" (ISBN-10: 0-470-02565-4) this article is a must read, the Sybex book doesn't cover SQL Servers cryptography.

----

First I'll describe the tools needed for cryptographic security;

  • Cryptographic hashes (are not mentioned in courseware, but included here for completeness)
  • Key based encryption 
    • Symmetric keys
    • Asymmetric key-pairs (private key & public key)
    • Certificates
  • Passwords/passphrases

and then continue to encryption hierarchy, combining different forms of cryptography and the double protection of keys and certificates. 

Cryptographic hashes

Cryptographic hashes are in security related documentation mostly referred to as just hash. However in a database context, you may also read about hashes in the terms like hash indices and hash joins. Hash indices and hash joins are related to the query processor and have nothing to do with the security subsystem. So when you encounter hash in SQL Server documentation, verify if it is meant cryptographic or related to the query processor. In the remainder of this article, the term hash will refer to a cryptographic hash.

Now for what a hash does, a hash is based on some input of any length. This input is divided in blocks and based on these blocks, calculations are performed. These combined calculations are called the algorithm. One of the characteristics of the algorithm is that it's output has a fixed length. In SQL Server the following algorithms for hashes can be used, with the output length in bits between parentheses; MD2(128), MD4(128), MD5(128), SHA(160) aka SHA0, SHA1(160). The output of the hashing operation may be referred to as hash or as Message Digest, the input of a hash operation may also be named Message. The most important characteristic of a hash is that it is one way, so you can calculate the Message Digest of a Message, but it isn't possible to retrieve the Message if you only have the Message Digest.

You can call a hash function from SQL Server directly, see HASHBYTES for more info.

The typical application for hashes is to verify the original content, without having to store the original content. For example with passwords, where systems typically store the password hash, not the password itself. When the user types the password, it is hashed and compared to the stored hash. If they match, the user has proven to know the password. You may also encounter hashes with downloads, where the (MD5) hash of the downloadable file is posted. After you downloaded the file, you can compute the hash of the file. When it matches the posted hash, the download was successful. Check out winMd5Sum as example.

Key based encryption

When encrypting information, the original information is referred to as plaintext and the encrypted information as ciphertext (or cyphertext). During the encryption operation a key is applied to the plaintext based on an algorithm (the keys' properties must match the algorithm), resulting in unintelligible ciphertext. The only way to read the ciphertext is by decrypting it, which again involves applying a key to the ciphertext based on the corresponding algorithm.

The words plaintext and ciphertext could be a little misleading, as you might think of it as text, but plaintext and ciphertext can be binary too. Specific for SQL Server 2005, the key based encryption functions can handle the data types; char, varchar, nchar, nvarchar, binary, varbinary. Other data types should be casted as one of the afore mentioned datatypes, also the datatypes are limited to 8000 bytes (actually the encrypted data is limited to 8000 bytes, which means the plaintext usually is shorter based on the algorithm used)!

Symmetric keys

With symmetric keys, the algorithm performing the operations uses the same (symmetric) key for both the encryption and decryption operation. Symmetric keys are considered fast for cryptographic operations in comparison to asymmetric keys, naturally the real speed of the encryption and decryption depends on the encryption algorithm, the length (in bits) of the symmetric key and the available processing power. With encryption, the algorithm is bound to the symmetric key, so during creation you must specify for which algorithm (DES, DESX, Triple DES, RC2, RC4, AES 128, AES 192, AES 256) the key is created. The second requirement when creating a symmetric key is that it is encrypted. It may sound strange, encrypting a key, but in reality the key is the most vulnerable part in cryptographic systems. By encrypting the symmetric key (that may have encrypted numerous fields in the database), we prevent someone who has no access to the decryption of the symmetric key, to access the data protected by this key. At the end of this article, you will see that you can build/use a hierarchy of keys that will enable you to keep numerous secrets by guarding only a few.

Creating a symmetric key is done via CREATE SYMMETRIC KEY. After creation, the key is stored in the database and information about the key can be retrieved from the system view sys.symmetric_keys. As you may see in this view, the key has a name and a GUID (you'll also see the columns for storing the algorithm). Both the name and the GUID are important when identifying the key in encryption and decryption operations.

To make use of a symmetric key, this symmetric key should be open. This has to be done because the symmetric key is stored in an encrypted state. So you can only make use of the symmetric key when you have access to the key or know the password that was used to encrypt the symmetric key. For syntax, see OPEN SYMMETRIC KEY.

The final step is using the symmetric key to encrypt and decrypt information. This can be done using the functions EncryptByKey and DecryptByKey. Note that you can include an "authenticator", which basically is a property of the record that you can encrypt along with the data.

To see how the symmetric key based encryption should be used, please take a look at the example by Laurentiu Cristofor.

Asymmetric keys

With asymmetric keys, the algorithm performing the cryptographic operations uses a key pair. The keys of this pair (let's call them Pub and Priv right away) match in such a way that one key can undo the operation of the other key. So if you have plaintext and encrypt this with the key Pub, the resulting ciphertext can only be decrypted with Priv. The other way around, when you use Priv to encrypt some plaintext, the only way to decrypt the resulting ciphertext is using Pub.

By calling the key-pair Priv (private key) and Pub (public key), the main area of use is very clear. While a person or system can share it's public key with the whole world, the private key is kept secret. Now let's say that two people, Alice and Bob, want to exchange information, without anyone else being able to learn about the exchanged information. Both Alice and Bob have a private key (which each of them keeps a secret) and a public key (known to the both of them and the rest of the world). Now Alice wants to leave Bob a message to.... (well, it's to remain unknown to the rest of the world), so Alice encrypts the message with Bob's public key (Pub-B). With Bob being the only one who has access to Bob's private key (Priv-B), Alice knows only Bob can decrypt the message and she can safely store the encrypted message for Bob to read. After decrypting the message, Bob wants to answer Alice in an equally secure manner, so he uses Pub-A to encrypt the message. Even with everybody being able to retrieve the encrypted message, only Alice can decrypt it, for she has Priv-A.

Alice encrypts and stores:

EncryptPub-B(Plaintext_1:I am Alice) = Ciphertext_1

Bob retrieves and decrypts:

DecryptPriv-B(Ciphertext_1) = Plaintext_1:I am Alice

-------------------------------------

Bob encrypts and stores:

EncryptPub-A(Plaintext_2:I am Bob) = Ciphertext_2

Alice retrieves and decrypts:

DecryptPriv-A(Ciphertext_2) = Plaintext_2:I am Bob

Besides preventing information disclosure, asymmetric keys can also provide authentication. In the previous information exchange, Alice and Bob wanted to keep the information undisclosed. But what if Oscar wants to trick Bob and pretend he is Alice. Oscar would only have to fetch Bob's public key, encrypt the message with Pub-B and state in the message that he is Alice.

Oscar encrypts and stores:

EncryptPub-B(Plaintext_1:I am Alice) = Ciphertext_1

Bob retrieves and decrypts:

DecryptPriv-B(Ciphertext_1) = Plaintext_1:I am Alice

To prevent Oscar (or anyone else) from manipulating the flow of information, Alice and Bob agree to encrypt the message with their private keys prior to storing. So Alice has a message; "plaintext" and encrypts it with Pub-B (she now knows only Bob can decrypt it). Next she encrypts the resulting ciphertext with Priv-A and stores that message. When Bob retrieves that message, he uses Pub-A to decrypt the first stage, this verified that the message was encrypted by Alice (as only she has access to Priv-A), next he decrypts the message with Priv-B and now has access to the plaintext stored by Alice.

Alice encrypts and stores:

EncryptPriv-A(EncryptPub-B(Plaintext_1:I am Alice)) = Ciphertext_1

Bob retrieves and decrypts:

DecryptPub-A(DecryptPriv-B(Ciphertext_1)) = Plaintext_1:I am Alice

-------------------------------------

Bob encrypts and stores:

EncryptPriv-B(EncryptPub-A(Plaintext_2:I am Bob)) = Ciphertext_2

Alice retrieves and decrypts:

DecryptPub-B(DecryptPriv-A(Ciphertext_2)) = Plaintext_2:I am Bob

So it doesn't matter that Oscar has access to all public keys and can retrieve the ciphertext, because Priv-A and Priv-B are a secret held by their respective owners, Oscar can't interfere with this process. The outer encryption, where the originator uses its private key, is also referred to as signing. Also note that a message does not have to be encrypted to be signed.

Alice signs and stores:

EncryptPriv-A(Plaintext_1:I am Alice) = Ciphertext_1

Anyone retrieves and verifies:

DecryptPub-A(Ciphertext_1) = Plaintext_1:I am Alice

Like with symmetric key, asymmetric keys can be created with a T-SQL CREATE statement and viewed through a security catalog view; CREATE ASYMMETRIC KEY and sys.asymmetric_keys. Asymmetric keys are based on the RSA-algorithm with keys of 512, 1024 or 2048 bits. As you learned above, the private key is to remain secret, so in order to protect it, the private key has to be stored in encrypted form. Unlike symmetric key based encryption and decryption, with asymmetric key based operations the keys do not need to be opened. However when performing an operation that requires access to the private key, the private key must be decrypted during the operation. The following functions are performed with the public keys; EncryptByAsmKey and VerifySignedByAsmKey. The operations performed with a private key are; DecryptByAsmKey and SignByAsmKey.

Certificates

Very close to the asymmetric keys are certificates. In fact, nothing changes on the side of the private key and the public key. The certificate is only used to store properties associated with X.509 v1 certificates and associate those with the public key, read Laurentiu's post for more info. Because certificates are named different from asymmetric keys, you'll get different syntax and another security catalog view; CREATE CERTIFICATE, sys.certificates, EncryptByCert, VerifySignedByCert, DecryptByCert, SignByCert. However certificates do have a major advantage over asymmetric keys; you can backup a certificate (certificates were designed with .CER files in mind); BACKUP CERTIFICATE.

Passwords and passphrases

As you may have noticed in the syntax for creating and opening symmetric keys and when creating or using the private key on asymmetric keys and certificates, there is the option to use a password (ENCRYPTION BY PASSWORD='@v3RyCo/\/\pl&xPa$suu0rD'). When a password is provided on creation of the key or certificate, that password is used as a sort of symmetric key to provide the necessary encryption. Also, passphrases can be used to encrypt data directly without the need for any keys and key handling. This can be done with the functions EncryptByPassPhrase and DecryptByPassPhrase. Passwords and passphrases are essentially the same kind of thing, though we see passwords as hard to guess character strings and passphrases as long but easy to remember phrases. Passphrases typically contain a lot of spaces, whereas passwords contain mixed case, numbers and symbols and are usually enforced by a password policy.

Encryption hierarchy

Encrypting keys and certificates with a password is one option of protecting that key or certificate, but it does involve a lot of password management. Another option is building an encryption hierarchy in SQL Server 2005. To be able to explain the hierarchy, two special symmetric keys must be introduced;

  • SERVICE MASTER KEY
  • DATABASE MASTER KEY

Service master key

The service master key is created when SQL Server 2005 is installed. The key is created based on and protected with the credentials of the Windows Account that is used as the SQL Server Service account. The service master key is used to encrypt password which are stored in the master database (like SQL logins and credentials for linked servers), also this key acts as the root for the SQL Server encryption hierarchy. The service master key can not be created or dropped, it can however be altered, backed up and restored. In fact, backing up the service master key is recommended right after installing the SQL Server instance. See ALTER SERVICE MASTER KEY for more information.

Database master key

The database master key can be created as a starting point for encryption in the database. This can be done with the statement CREATE MASTER KEY which must include the ENCRYPTION BY PASSWORD. This creates the master key in the database (encrypted by the password and by the service master key), also the database master key is stored in the master database where it is encrypted with the service master key. The database master key is stored in the master database to facilitate automatic decryption. It is possible to drop the copy from the master database, but then it is required to manually open a database master key prior to using it. Like the service master key, it is best practice to backup the database master key too. See CREATE MASTER KEY for more information.

Encrypting asymmetric keys and certificates

When the ENCRYPTION BY PASSWORD directive is omitted for an asymmetric key or certificate when it is created, the master key of the database where the asymmetric key or certificate will be stored is used to encrypt the private key. This way, the new key or certificate is automatically tied into the encryption hierarchy of SQL Server. Asymmetric keys and certificates can only be encrypted once, so when you alter them, you can switch between encryption by the database master key and encryption by a password, or in the later case change the encryption password. 

Multiple encryptions of symmetric keys

Normal symmetric keys and database master keys can be encrypted more than once. For the database master key, this is very convenient, as it is both possible to use cryptographic functionality transparently (based on the encryption by the service master key) and transfer the database to another instance while preserving the encryption hierarchy (based on the encryption by password). For normal symmetric keys this is very convenient as well, as multiple users may need access to the same encrypted data. The bulk of the data (think like thousands of records with an encrypted field) does not need to be encrypted multiple times and can be accessed through the same symmetric key. This symmetric key is, depending on the capabilities users/processes, accessible through one or more passwords, other symmetric keys, asymmetric keys and/or certificates.

Combining cryptographic operations

Typically symmetric and asymmetric key (or certificate) based encryptions are combined to achieve the desired security level, while still maintaining good performance. If you revisit the example, you'll notice that the data in the column is encrypted with the symmetric key (remember: symmetric key = fast). The table in the example only has 2 rows, but that same table could hold millions of rows, making the algorithm doing the encryption and decryption very important in terms of performance. Because all this data is encrypted with a single symmetric key, this key should be well protected. The example used a password to encrypt the key, but a very common approach is to secure the symmetric key with a certificate (or asymmetric keys, which essentially comes down to the same). As a non-SQL Server example, EFS takes the same route, using a symmetric key to encrypt a potentially big file and then encrypting that symmetric key with the public key for each user that should have access to the file.

Now for SQL Server 2005, Laurentiu has a great 2nd example where he uses the possibilities offered by the encryption hierarchy. A database master key is created and used to encrypt the certificates for all users (dbo and Charlie) participating in the example. The certificates are then used to encrypt the symmetric key and the symmetric key will be used to encrypt the (2, but potentially thousands) salaries in the t_employees table.

Interesting to note about the 2nd example is the function DecryptByKeyAutoCert. The main advantage of this function is that it utilizes the encryption hierarchy and transparently opens keys on demand (and closes them after the operation completed). Similar functionality is provided by the function DecryptByKeyAutoAsymKey, if the symmetric key is encrypted with an asymmetric key.

Permissions on keys and certificates

One important thing not yet mentioned about the keys and certificates, is that they are securables. So in order to use them, a principal should be granted the necessary privileges on the keys and certificates. If you look closer at the second example, you see it is no issue at first, as everything is done as dbo, so dbo automatically is owner (and in full control) of the keys and certificates created. But when Charlie makes his appearance, he must be granted the necessary privileges; naturally select on the views, but also VIEW DEFENITION on the symmetric key (to be able to use it) and CONTROL on the certificate. Alternately Charlie could have been made owner of the certificate when it was created through the AUTHORIZATION directive. Bottom line is, to use a key or certificate, you must be granted the proper access to the key or certificate and be able to decrypt it. For more information on permissions, see GRANT.

Cryptography and authentication

What this article doesn't cover is signing programmed modules and mapping users and logins to certificates. However, I will add those topics when covering impersonation (EXESUTE AS on the todo-list).

Recommended reading: Handbook of Applied Cryptography, Laurentiu Cristofor's blog, Wikipedia. Not exactly reading, but check out those 4 presentation from the 2006 PASS conference. 

Saturday, 28 April 2007 21:34:46 (W. Europe Daylight Time, UTC+02:00)
# Friday, 13 April 2007

If you're looking for the SQL Server 2000 perspective, go to Space Program.

Before venturing further, when was your last checkpoint? I learnt in my test setup, that if you work with a small number of operations, there is no data recoverable from the data file. It was just on the dirty pages and in the transaction log (which I deliberately crashed, to be able to advise on recovery of that event). This is an indicator that, if you lose the transaction log in a running database, you are very likely going to end up with data loss.

-----

...continued from Recovery when the transaction log is lost, if the database was detached or you want to connect the database to another instance.

Unfortunately, after the failed attach, there is no entry for the LogCrash_Demo database in the system tables anymore. So first you have to create a new database with the same name. Next take this database offline and replace the data file of the newly created database with the datafile containing the data you want back. Also, throw away the transaction log that was just created.

Now proceed to the next section, where the database is started in emergency mode.

-----

...continued from Recovery when the transaction log is lost, if you didn't detach the database.

Start the LogCrash_Demo database in emergency mode (switch to emergency mode has changed with SQL Server 2005, as it required direct updating to system tables, which are now hidden from the DBA).

ALTER DATABASE LogCrash_Demo SET EMERGENCY

Now you can read from the database, which is enough to script the definition of the objects (SSMS: right-click the database, Tasks --> Generate Scripts), create a new database based with those scripted objects, and transfer (BULK INSERT, SQL Server Integration Service, etc.) the data from the database in emergency mode, to the new database.

Be careful though about data loss. Data data file that was made available for select in emergency mode does not contain the dirty pages.

So should you be worried about crashing of the transaction log media, make sure you use at least disk mirroring for your transaction log. Because when you look at backup restore as in the 2780, you can rebuild your data files after a disk crash using the right backup and restore strategy without data loss, but you can't if your transaction log is the disk crash victim.

 

Technorati tags: , ,
Friday, 13 April 2007 14:43:23 (W. Europe Daylight Time, UTC+02:00)

When does SQL Server, under its default settings, write dirty pages to disk?

The answer depends on your recovery model, but let's assume Full Recovery. In that case, either when the lazy writer kicks in or when a checkpoint is issued.

-- (Relevant) Memory usage by SQL Server --

To simplify, SQL Server needs datapages in memory to work with them. To do so, SQL Server uses available memory from the system for a buffer pool. This buffer pool is filled with datapages, while SQL Server has the opportunity to do so (and when memory runs out on the system, SQL Server will release memory from its buffer pool). When one or more records are SELECTed from the datapage, the datapages containing these records are either already present in the buffer pool, or read from disk. When the datapages are modified (like in UPDATE, DELETE and INSERT), SQL Server carries these operations out on the pages in memory (and also writes the modification to the disk-bound transaction log). When the data page modification is completed, the data page stays in memory and is marked dirty (because the page in memory and the corresponding page in the datafile are different).

There are also times when the buffer pool needs to be cleaned up, either because it's full and more needs to be stuffed in (like my garage) or because you want to clean it out (no real-world example applies to me here).

-- Lazy Writer and Checkpoint --

In the first case, where more data pages need to be stuffed in, the lazy writer gathers (some of) the dirty pages and has them written to disk. That way the memory that was occupied by those dirty pages can be reused. In the second case, where you want to clean the dirty pages, you issue a checkpoint, thereby ordering SQL Server to flush the dirty pages to the data file(s) on disk.

This way issueing a checkpoint, has the positive side-effect of making memory in the buffer pool available for reuse. Side note; neither the lazy writer, nor checkpoint, free data pages. They just mark them safe for reuse.... but wait a minute, cleaning out the buffer pool (or the garage), isn't that a must do??? To be honnest, only when it's full!

So when does a checkpoint occur? When a database is closed (like when taking the database offline, stopping the service) or when a backup is made, as part of the opperation a checkpoint is issued. Besides those events, there is also the option to programatically/interactively issue a checkpoint, finally SQL Server issues checkpoints automatically, based on the recovery interval.

-- Recovery --

There is a more compelling reason (compared to buffer pool maintenance) to issue a checkpoint every now and then. It is recovery!

And why is recovery important? When a database starts up, recovery is one of the processes. During recovery, SQL Server looks up the last known checkpoint in the transaction log and then starts redoing the committed transaction from the transaction log. If the database was closed clean the last time, the checkpoint is the last thing in the transaction log, so recovery costs next to nothing. But if the database wasn't closed nicely (like when the power supply unit fails), then it may be hours (or longer) since last checkpoint. SQL Server then starts redoing from the transaction log, this may take some time, but at least you don't lose commited transaction.

BUT DIDN'T YOU .... ?

YES, I DID! That is, pull the memory stick with the transaction log out, to simulate a failure of the disk. And that's where it hurts as data pages only get flushed to disk if;

  • Both the buffer pool and system memory are full (or memory is capped with max server memory). Based on a few inserts and updates... don't think so.
  • The database is closed or backed up. Not the case when you pull the stick.
  • A manual CHECKPOINT is issued. Didn't do that.
  • Recovery interval kicks in. Waited just for that to happen...

However, the recovery interval of 0 does not mean SQL Server will issue a checkpoint automatically within a minute. It says, checkpoints do occur when the database engine estimates it can process the number of log records within the interval specified during the recovery process! In other words, it may take very long between checkpoints if a system is running very few transactions... like I noticed during my tests.

Technorati tags: , ,
Friday, 13 April 2007 14:41:49 (W. Europe Daylight Time, UTC+02:00)
# Thursday, 12 April 2007

This morning, as I wanted to start working, I noticed my server wasn't working... Outlook couldn't connect to Exchange and the ReSQueL website was down as well. The server still had power, but no response (not even the keyboard LED for NumLock). So a power-cycle was next, well at least half of it, power down worked, power up didn't.

Absolutely nothing happened! Now I must admit, I've been there before with that type of system (not just my own). So I pulled the power cord and left the system for about 10 minutes, then tried again. This time I heard some ticks coming out of the power supply unit. And believe me, ticks from the PSU is not a good sign about the health of the thing.

However, I did have another unused ATX cabinet with PSU, so I tried transferring the internals of the old system to the replacement cabinet. Unfortunately, the TP123 motherboard has an extra power connector for the CPU core voltage. So putting power on the board worked, but (as expected) it didn't boot.

Not wanting to extend the downtime much further, I grabbed the car-keys, drove to Office Centre (cash and carry beats a webshop when you need something NOW!!!) and bought the cheapest config with 1 GB internal memory. Back home, I tested if the system worked. It did. Next I added the old disk and an extra network card and booted the system from the old disk... BOSDed and rebooted right away. But since the config is totally different, that shouldn't be too surprising. So I fetched the Windows Small Business Server 2003 R2 DVD and started the repair installation. Finally some time to sit down and write some... if all is well, this is on the blog (and the blog available) little over 5 hours 6 hours after I noticed the server down... always fun when you don't have the drivers for your new system (only the Vista drivers supplied)... NOT!

Hardware | ReSQueL | SBS
Thursday, 12 April 2007 14:18:32 (W. Europe Daylight Time, UTC+02:00)
# Monday, 09 April 2007

"Let's check and see..." and we came back with no license info on SQL Server. Yet the course certainly gives an overview of the various licensing options, so does the Microsoft website. However, when installing SQL Server, you're not asked about licensing. Also the queries SELECT SERVERPROPERTY('LicenseType') and SELECT SERVERPROPERTY('NumLicenses') both return DISABLED.

By design, the product SQL Server 2005 does nothing in terms of enforcing or even storing license information. Eric Burgess mentions this on the Microsoft SQL Server Support Blog, including how to store the license information in the Windows Registry, as was done with SQL Server 2000.

Okay, that for the technical detail, but what remains puzzling is Microsoft's current strong/aggressive campaigning on licensing versus no instruments in SQL Server.

Technorati tags: , , ,
Monday, 09 April 2007 13:57:52 (W. Europe Daylight Time, UTC+02:00)
# Saturday, 07 April 2007

Module 3 of the course 2780 is about backup and restore. When you combine this module with the database best-practice of separating the data files and transaction log files on different disk, you can even demonstrate how to recover from disk failure using the right backup and restore strategy without losing data... that is, recover if the failing disks contain the data files!!!

But what if the failing disk contains the transaction log files? Backup and restore are not going to help you to recover up to the moment of disaster. Sure, you can restore the database from last backup, but if last backup was on Sunday and the incident occurs Friday afternoon, about a weeks work is lost!

Yet we know all data is still sitting in the data file(s), the only sad part is, we can't bring the database up without the log file. So the question is, how to give the database its log file(s) back. Not knowing the answer from the top of my head, in which case I usually say "Let's see what happens if we stop the service and throw away a logfile", results were inconclusive and since (lack of) time is a major issue on the 2780, the subject of lost transaction log files landed on the todo-list.

/* BEGIN UPDATE 1 */

HALT, don't do anything yet if you have this problem in a production environment. Just read this post and the post Recovery continued, as there is no need to detach the failed database if your instance is still up and running.

/* END UPDATE 1 */

Now for the answer, which get's pretty complicated, depending on the state of your database. IF your database shutdown was clean:

Detach the damaged database. Then attach the data file(s) again, but remove the missing logfile from the list (see picture). This way, SQL Server recreates the logfile.

Depending on the state of the database SQL Server may even perform this operation at database startup. So you won't even notice it when you don't visit the log-files (Windows Application EventLog or SQL Server Activity log). Explains why results were inconclusive during class. This is easily tested (not on production databases, naturally) by stopping the database service, throwing away the logfile of a user database and starting the database service again. Either, you'll see the recreation of the transaction log in the Event log and Activity log, or you have to perform the detach and attach yourself.

Both for detaching and attaching, you may get error messages. However, these are warnings, the operations do complete successfully.

That was the easy part, but surely you've seen the big, fat IF! Databases don't shutdown clean if the log file crashes. To get a picture of what happens when a log-file is lost due to a disk crash, I created a database with the data file on disk (D:\SQL_DATA\LogCrash_Demo.mdf) and the transaction log on a memory stick (G:\SQL_DATA\LogCrash_Demo_Log.ldf). Next I added a table and started inserting new records. And... pull the stick out!

Msg 9001, Level 21, State 4, Line 1
The log for database 'LogCrash_Demo' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

After detaching the database and trying to attach (CREATE DATABASE ... FOR ATTACH_REBUILD_LOG) it again, the following error comes up;

File activation failure. The physical file name "G:\SQL_DATA\LogCrash_Demo_log.ldf" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'LogCrash_Demo'. CREATE DATABASE is aborted.

/* BEGIN UPDATE 2 */

I was going to look into what DBCC REBUILD_LOG could have done in this scenario and how that could be applied to SQL Server 2005... as of SQL Server 2005 DBCC REBUILD_LOG is no more and since DBCC REBUILD_LOG is an undocumented command, so the succession isn't documented either (if you care for an overview of DBCC changes, including "undocumented" stuff, look at Norb Technologies).

But just forget about DBCC REBUILD_LOG, Paul Randal is pretty clear about it (and all of a sudden it's gone in SQL Server 2005). Besides, the promised later post, how to get to the data in the data file is available too.

/* END UPDATE 2 */

UPDATED 2007-04-13

Technorati tags: , ,
Saturday, 07 April 2007 14:43:15 (W. Europe Daylight Time, UTC+02:00)
# Wednesday, 04 April 2007

Just read on Mary Jo's Microsoft wants Open XML to be an ISO standard... They have my support.

Sign the petition
Wednesday, 04 April 2007 20:11:07 (W. Europe Daylight Time, UTC+02:00)

Recently I finished teaching a couple of courses, to be more specific; the Microsoft courses 2779 and 2780. This resulted in a little todo-list for postings on the blog, either because time was lacking or because I needed to look up some details to properly answer the question.

In the next couple of days, I will post answers (if I can answer the question) on the following questions;

As the answer start appearing on the blog, I will update this post, linking to the answers. Another answer is also relevant in this context, the things that changed when doing full server recoveries.

Wednesday, 04 April 2007 19:35:02 (W. Europe Daylight Time, UTC+02:00)

Lately is was confronted with a couple of textfiles that wouldn't load into the database (SQL Server 2005). I was getting the errors 

Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

when trying to acquire the file through

BULK INSERT tblImport
FROM 'Z:\concat-file.txt'
WITH(TABLOCK, FORMATFILE = 'Z:\xmlformatfile.xml')

Turned out I had an arrow sitting at EOF (end of file) with the troublesome textfiles, which where concatenated using the copy-command;

COPY source*.txt concat-file.txt

The arrow representation was new to me, but after a little digging it boiled down to CTRL+Z and the difference of ASCII and binary copying... by default copy is ASCII and when merging files, so an end of file character gets inserted. The EOF character is omitted when doing a binary copy, so

COPY source*.txt /b concat-file.txt

resolved the error.

Curious about the arrow representation on Vista, I decided to verify with Windows Server 2003. Here the EOF story is (almost) the same, only the arrow is shown as a rectangle. Read more about copy here.

Wednesday, 04 April 2007 16:13:08 (W. Europe Daylight Time, UTC+02:00)