Posts Tagged ‘Best Practices’

Identify SQL Server 2014 Cardinality Estimator June 4th, 2014

Vinod Kumar

SQL Server 2014 released a new cardinality estimator (CE) and more can be learnt from the MSDN documentation on the specifics. In this post I just wanted to call out how you can quickly identify if we are using the old estimator or the new CE. This information stays inside the execution plans or the properties pane.

Recently one of the customers did see me talking about this and complained that his queries run on Adventureworks were never using the new CE and he had to always use a trace flag. This got me interested and I will tell you what I found as part of this learning.

The steps:

  1. Create some random query in samples DB or a DB of your choice. I used the AdventureworksDB for my experiment.
  2. Enable Actual Execution Plan.
  3. Execute the query, and wait for the Execution Plan Tab to appear.
  4. On the Execution Plan tab, right click select “Show Execution Plan XML …”

This now opens up the XML Plan for the query under question. And right at the top we will find the element which says “CardinalityEstimatorModelVersion”, if this is 70 then we are using the old CE and if this reads 120 then we are using the new CE.

We can also find this information in the Properties Pane (F4 shortcut). This is shown in the diagram below.

It was baffling to me why this was always showing as 70 for my environment. So the first place for me to check was the compatibility of the Database. It was at 110 (SQL Server 2012), I changed the same to 120 (SQL Server 2014) and I reran the same query.

As you can see, SQL Server has picked up the new CE and has evaluated the same. There is so much to learn about the new CE that I will reserve it for future posts.

Continue reading...


Checksum on Backup and Restores May 28th, 2014

Vinod Kumar

I am a firm believer of using new features that enhance productivity and life of a DBA. In that category is the feature of CHECKSUM. I have written a number of article on them in the past and are worth a mention:

  1. Torn Page Vs CHECKSUM
  2. CHECKSUM and SQL Server
  3. CHECKSUM and TempDB
  4. CHECKSUM Vs FileStream
  5. SQL Server 2014: Checksum with backups

SQL Server BACKUP and RESTORE statements provide the CHECKSUM option to include checksum protection on the backup stream and trigger the matching validation operations during restore. To achieve a checksum-enabled backup, the BACKUP command must include the CHECKSUM option. Read more syntax of CHECKSUMS from MSDN.

Backup and restore operations that use checksum capabilities increase data integrity protection and also increase CPU usage requirements at the point of backup. A backup or restore with the checksum option requires that each byte be checked as it is streamed, thereby increasing CPU usage. The checksum that is used for backup and restore uses the same algorithm to calculate the checksum value for the backup media as is used for data pages and log blocks.

The following rules apply to the BACKUP and RESTORE command CHECKSUM operations:

  1. By default, SQL Server BACKUP and RESTORE operations maintain backward compatibility (NO_CHECKSUM is the default). This needs to be specified explicitly.
  2. The database’s PAGE_VERIFY setting has no effect on backup and restore operations; only the CHECKSUM setting on the backup or restore command is relevant.
  3. The backup and restore checksum is a single value representing the checksum of the complete stream; it does not represent individual pages or log blocks located in the backup stream. The value is calculated during the backup and stored with the backup. The value is recalculated during the restore and checked against the stored value.
  4. Backup with the CHECKSUM option will not change the pages as it saves them to the backup media; a page’s protection state (NONE, CHECKSUM, or TORN) is maintained as read from the database file. If a checksum was already stored on the data page, it is verified before the page is written to the backup stream.
  5. Restore and Verify commands can be used to validate the CHECKSUM if the backup was created by using the CHECKSUM option. Trying to restore with the CHECKSUM option on a backup without a checksum returns an error as shown below.

Msg 3187, Level 16, State 1, Line 4
RESTORE WITH CHECKSUM cannot be specified because the backup set does not contain checksum information.

From an third-party utilities perspective and CHECKSUM, please read the KB-2656988.

Now that you got some of the fineprints with CHECKSUM and backups, I hope you will use the same in your environments in future atleast if you are not using.

Continue reading...


Troubleshooting Errors with SQL Server 2014 Encrypted Backups May 22nd, 2014

Vinod Kumar

The more I play around with Encrypted backups, the more I get to uncover things. It is highly recommended that you read the other posts (Troubleshooting SQL Server 2014 Encrypted Backups) before going ahead because these are all linked in one way or other. In this blog post, let me call out some of the common errors that we can encounter when working with Encrypted backups – couple of them have been already called out at SQL Server 2014: Backup to Azure Blob post too.

Is this not similar to TDE?

When I was talking about Encrypted backups, someone at a our local user group (SQLBangalore UG) asked. I thought it is worth to mention a word over the blog. The fundamental difference between Encrypted backups and TDE is:

  1. TDE encrypts the whole database, Encrypted backups don’t encrypt the database.
  2. Encrypted backups encrypt ONLY the backup.

01 – Missing Certificate

The simplest and basic error one can get is when the certificate is not available on the server where we are restoring. The error message we will get is:

Msg 33111, Level 16, State 3, Line 133
Cannot find server certificate with thumbprint ‘0x1C706DAAC3059BDDAD4573DBF3F7D7A685397F21’.
Msg 3013, Level 16, State 1, Line 133
RESTORE DATABASE is terminating abnormally.

Well, to resolve this – restore from the certificate from our backup location. I did talk about this in our previous post – SQL Server 2014: Restoring Encrypted Backups.

02 – Certificate missing private key

This is a little misleading but the certificate we restored missed the Private Key. The error we get is:

Msg 15507, Level 16, State 30, Line 166
A key required by this operation appears to be corrupted.
Msg 3013, Level 16, State 1, Line 166
RESTORE DATABASE is terminating abnormally.

Make sure you restore the certificate with the Private key as backed up before. If the certificate was backed up without a private key, we must take a new backup from the source server of the certificate with the private key.

03 – Invalid Algorithm used

When the backup command has an invalid algorithm this error is raised.

Msg 102, Level 15, State 1, Line 88
Incorrect syntax near ‘AES_266’.

The supported algorithms as per documentation are – AES 128, AES 192, AES 256, and Triple DES.

04 – Backup cannot find Certificate

When an backup operation fails to find an certificate to do encrypted backups, the following error message is raised.

Msg 15151, Level 16, State 1, Line 81
Cannot find the certificate ‘BackupCert’, because it does not exist or you do not have permission.
Msg 3013, Level 16, State 1, Line 81
BACKUP DATABASE is terminating abnormally.

05 – Backup fails when adding to an encrypted backup set
This is a strange error actually. If you have different encryption keys used, then they cannot be part of the same backup set. This means an encrypted backup media needs to have the same key for encryption else a separate backup needs to be created if we are using a different key / thumbprint.

Msg 3095, Level 16, State 1, Line 81
The backup cannot be performed because ‘ENCRYPTION’ was requested after the media was formatted with an incompatible structure. To append to this media set, either omit ‘ENCRYPTION’ or create a new media set by using WITH FORMAT in your BACKUP statement. If you use WITH FORMAT on an existing media set, all its backup sets will be overwritten.   
Msg 3013, Level 16, State 1, Line 81
BACKUP DATABASE is terminating abnormally.

This wraps the complete series on encrypted backup with SQL Server 2014. I am sure there are a number of other errors which I might have skipped. But these are the most common error messages we are likely to encounter with working with Encrypted backups. Do let me know if you are using this feature in your deployments.

Continue reading...


Troubleshooting SQL Server 2014 Encrypted Backups May 7th, 2014

Vinod Kumar

My adventures of working encrypted backups still are on. There are couple of posts already on how to take encrypted backups – I highly recommend you to read them before getting into this.

When I wrote the first post, a lot of folks asked me how to know we have taken an encrypted backup and what are the things we need to keep in mind when working with encrypted backups. This blog will walk you through some of the basic troubleshooting techniques in first understanding what it entails to work with encrypted backups.

Did you backup your certificate

In our first post around SQL Server 2014: Encrypted Backups, we show it is important to take a backup of your certificates. Many a times some of these best practices can be overlooked. But if you take a backup using a certificate which was not backed up, we will be presented with the below warning every single time.

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

I think the warning is quite self explanatory. This message will keep on coming till we take a backup. Just thought this is worth a mention and note here.

How to know which backups are encrypted

A number of times DBAs will want to know which are the backups and the corresponding certificates used to encrypt a given backup. The below query uses the DMVs to give you this details.

SELECT AS Certificate_Name,




FROM sys.certificates cer

INNER JOIN msdb.dbo.backupsetbks

ON cer.thumbprint = bks.encryptor_thumbprint

If you want to look at all the certificates in the system, just query the DMV.

SELECT * AS Certificate_Name,

FROM sys.certificates

Restoring Headers of backups

We can also restore the headers, but this will never show the backup was encrypted.


FROM DISK = ‘D:\SQLFiles\Backup\03-mySecureDB_encrypt.bak’

I am sure, this is a good starting point. Next blog post – let me call out some of the other errors that we can get when working with Encrypted backups.

Continue reading...


SQL Server 2014: Backup to Azure Blob April 14th, 2014

Vinod Kumar

SQL Server 2014 has this new capability which allows us to take a backup to a URL. Infact, it is available in SQL 2012 SP1 CU2 itself – I lied to you in my first statement :). Anyways, I thought it is worth a mention and a blog for itself.

The concepts of accessing the keys have been mentioned also at: Viewing Windows Azure Storage using SSMS.

Copy the keys and we are ready.

Now that you have these key’s handy. We will take an encrypted backup as explained in SQL Server 2014: Encrypted Backups and upload it into Azure Blob storage rather than our conventional disk subsystem. So here are the steps to follow.

Create your credential

The create credential command has been there since SQL Server 2005 but I never found a better scenario as this to use the same. So here I am sort of creating my proxy name to access the blob storage account.


WITH IDENTITY= ‘mtcvinod’



Backup your database to Azure

The next step is to backup your database. This command is ALMOST similar to a normal backup statement with the ONLY change where we point to a URL rather than a disk location. Otherwise everything is same.


TO URL = ‘http://<< TODO: ENTER YOUR CONTAINER URL >>/backup/04-mySecureDB_compress_encrypt.bak’

WITH CREDENTIAL = ‘mycredential’,


  NAME = N’mySecureDB – compressed, encrypted’,






  STATS = 10;


If you are playing around with this script again and again, make sure you delete the file that got uploaded the previous time. Else we will be presented with the below error 3271. Alternatively you can add the WITH FORMAT option too.

Msg 3271, Level 16, State 1, Line 7
A nonrecoverable I/O error occurred on file The file exists on the remote endpoint, and WITH FORMAT was not specified. Backup cannot proceed..
Msg 3013, Level 16, State 1, Line 7
BACKUP DATABASE is terminating abnormally.

Restore from URL

Just like we did a backup, now we can also do a restore using a similar command with the change being the URL instead of a Disk location.


FROM URL = ‘http://<< TODO: ENTER YOUR CONTAINER URL >>/backup/04-mySecureDB_compress_encrypt.bak’

WITH CREDENTIAL = ‘mycredential’

, STATS = 5


To Viewing Windows Azure Storage using SSMS, read that blogpost or feel free to use other Azure Blob tools like the one I have used here is Cloudberry or any others of your choice.

There are a number of other error messages which are self-explanatory which need a mention here. If you are going to restore from this backup on a database which is running currently, you might get the below error:

Msg 3159, Level 16, State 1, Line 21
The tail of the log for the database "MySecureDB" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

If the backup URL mentioned doesnt exist in the given location then we will get the blow error:

Msg 3271, Level 16, State 1, Line 21
A nonrecoverable I/O error occurred on file "" Backup to URL received an exception from the remote endpoint. Exception Message: Error occurred while referencing Blob in remote storage.

PS: Note, the above example uses encrypted backups – but the same can be achieved using normal backups too.

In my subsequent blogs, let me also bring out some of the other errors one might get by using Encrypted Backups.

Continue reading...