Archive for the ‘Technology’ Category

SQL Server 2014: Restoring Encrypted Backups April 9th, 2014

Vinod Kumar

What is the point in taking encrypted backup (SQL Server 2014: Encrypted Backups), if we are not sure how to restore them. This blog is about taking a cue from the previous post and we will detail on the steps to restore the same.

Please follow the steps we used in the previous blog – SQL Server 2014: Encrypted Backups as we plan to continue using the same files for restore process in this post.

Restore an Encrypted Backup – Error 33111

If you take an encrypted backup and restore onto a new separate server, it will Error with the following message.

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

As the error suggests, this is a classic case of the certificates missing on this new server. Remember the step of taking a backup of our certificates in our previous article. Now it is going to help us.

Restore the Certificate

The command to restore the same is:

Use [master]


    FROM FILE = ‘D:\SQLFiles\Backup\BackupCert.cert’

    WITH PRIVATE KEY ( FILE = ‘D:\SQLFiles\Backup\BackupCert.privatekey’ ,

    DECRYPTION BY PASSWORD = ‘MyCertPassword-2014′ );


restore Encrypted Backup

The restore statement is same irrespective of encryption or not. The command in our case would look like:

USE [master]



FROM DISK = N’D:\SQLFiles\Backup\03-mySecureDB_nocompress_encrypt.bak’



STATS = 10


It is as simple as it gets. One of the other enhancement that I mentioned before about messages while restore or backup can also be read – SQL Server 2014: Backup Informational messages.

Continue reading...


SQL Server 2014: Encrypted Backups April 8th, 2014

Vinod Kumar

The learning on SQL Server 2014 continues and in this blog, I will walk through TSQL on what are the steps involved in creating an Encrypted backup using SQL Server 2014. The steps involved are:

  1. Create or identify your database (with data)
  2. Setup a Master Key
  3. Create a Certificate which we will use
  4. Backup your Certificate as a best practice
  5. Take the backup using the Certificate

Step 1: Create or identify your database (with data)

In my example, I am just creating a dummy database for testing.


If you want, go ahead and create a table with some values for later reference when we use restore scripts.

USE MySecureDB


CREATE TABLE client_data(name NVARCHAR(30), CreditCard CHAR(20))


INSERT INTO client_data VALUES(‘Pinal’, ’123-01-1234′)

          ,(‘Balmukund’, ’123-01-1235′)

          ,(‘Vinod’, ’123-01-1236′);


SELECT * from client_data


Step 2: Setup a Master Key

The next logical step is to create a Master Key.

USE [master];




Step 3: Create a Certificate which we will use

Next step is to create a certificate which we will use for encryption requirements.

USE [master];



   WITH SUBJECT = ‘My Backup Encryption’;


Step 4: Backup your Certificate as a best practice

This is an important and critical step, always DONOT forget to backup your certificates.

Use [master]

BACKUP CERTIFICATE BackupCert TO FILE = ‘D:\SQLFiles\Backup\BackupCert.cert’

    WITH PRIVATE KEY ( FILE = ‘D:\SQLFiles\Backup\BackupCert.privatekey’ ,

    ENCRYPTION BY PASSWORD = ‘MyCertPassword-2014′);


Step 5: Take the backup using the Certificate

The next step is the easiest step of backing up your database using an certificate.


TO DISK = N’D:\SQLFiles\Backup\03-mySecureDB_nocompress_encrypt.bak’



  NAME = N’mySecureDB – uncompressed, encrypted’,






  STATS = 10


As a bonus step, you can also compress an encrypted backup. From the above statement, just change from NO_COMPRESSION to COMPRESSION to check its effect on your backups. To look at a UI option to enable the same from my friend Balmukund’s post on the same topic – SQL 2014 Learning Series # 3 – New Feature – Backup Encryption.

In future posts, I will walk through some more scenarios of Restore, Failures and troubleshooting while restoring encrypted backups.

Continue reading...


SQL Server 2014: CREATE INDEX in CREATE TABLE construct April 2nd, 2014

Vinod Kumar

This is one of the simplest post that I could think for the mid-week post :). There is nothing big about this feature to talk but surely worth a mention. I was reading the documentation of CREATE TABLE in SQL Server 2014 and saw an extension now available to create indexes as part of the construct inline with the same command. I know for folks who use keyboard and not the UI, this is a great boon.

A simple construct will look like:

CREATE TABLE dbo.myTab1 (

col1 int not null,

col2 int not null,

INDEX myTab1_col1_col2 NONCLUSTERED (col1, col2)



This for me was a nice T-SQL extension and worth a note. Do check the CREATE TABLE documentation for more information and options available.

Continue reading...


Use existing connections in PowerPivot and Visual Studio April 1st, 2014

Vinod Kumar

I have been fortunate enough to work with a lot of data which involve using of PowerPivot or customers using Tabular model using BISM. Though there are striking similarities in building both the tasks, the mistakes made in both these models are almost the same. Let me talk you through this for a moment.

Whenever we work with PowerPivot or BISM model and we are importing data from a data source, the initial tendency is to use the “From Database” or “From Other Sources” tab and we start building the model. This is a fair step and in doing a data mashup, we bring data from multiple sources.

Many a times we might connect to the same data source using the above step. And to our irony we will encounter the following in our existing connections. In the example below, I have made 3 different query to the same data source and it is now pointing to the same source – 3 times. Isn’t it inefficient? I have seen customers having close to 20-25 connections to the same source but they are bringing some 20-25 different tables. I have observed the performance during “refresh” gets severely hampered by this approach.

So how to get around this? The steps are simple. Whenever you want to connect to the same source – click on the “Existing Connections” button.

Select the source that you want to reuse as a connection. And now select “Open”.

We will be presented with the same wizard as we normally do with “New Connection” Wizard.

The steps are similar when using the same in Visual Studio too when developing BISM models.

Continue reading...


SQL Server 2014: New function sys.fn_hadr_is_primary_replica March 25th, 2014

Vinod Kumar

My latest explorations have been to look at what are the fine print details that got added with SQL Server 2014. Especially, I have been exploring what has fundamentally changed when it comes to SQL Server 2014 – AlwaysOn capabilities. Though at the outset this looks simple and basic, some of these enhancements are pretty cool and needs a mention. Hence you are getting these bunch of blogposts inspired by this learning.

In SQL Server 2012, we had this neat little function – sys.fn_hadr_backup_is_preferred_replica which allowed us to be used during the backup operation if the replica we are running backup against is the preferred replica as per the AlwaysOn configuration. Though it was a neat addition there was no way to figure out if the node on which the Maintenance plan was running was ever the Primary replica. To illustrate the enhancement, I am having the following configuration as shown in the figure below.

As you can see I have two servers SQLALWAYSON01 and SQLALWAYSON02 servers and the -01 server is the PRIMARY. With SQL Server 2014, there is a new function that gets introduced – sys.fn_hadr_is_primary_replica. This nifty function when passed with the database name will return 1 if it is run on the PRIMARY. Else it will return you 0, if invoked from the secondary replicas.

In my opinion this is a critical and nifty option because you might want to do certain operations ONLY on the Primary like index rebuilding, full backups or whatever. Apart from these values, if you by any chance pass a non-AG database to the function it will return you NULL as shown in the figure below. Do let me know if you will use this function and what are the scenario’s you will find this useful. It can surely be a great learning for me too to listen from you.

This post is inspired from Book content: SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups” (Paperback, Kindle).

Continue reading...