Archive for the ‘Technology’ Category

GIDS 2014 – 5 Tech session Scheduled April 21st, 2014

Vinod Kumar

It has become a custom for me to talk at this premier event year-on-year and this will be my 5th year partnering with the Great Indian Developer Summit (GIDS 2014) folks. I am excited as ever because we get to meet a different set of audience and the expectations are completely different. I generally get exhausted by end of the day and this year I am super excited as I will be delivering close to 5 different sessions at different timings. If you are attending the event, you don’t want to miss my sessions for sure :) …

If you are wondering where this event happens every single year -

Event Location: J. N. Tata Auditorium
National Science Symposium Complex (NSSC)
Sir C.V.Raman Avenue, Bangalore, India

The complete schedule is published here.

GIDS .NET Day – April 22nd

This day I am doing three sessions.

Time: 10:35 – 11:35

SQL Server Management Studio – Tips and Tricks

If you are a SQL Server developer or DBA there is no way you can hide away from working with SQL Server Management Studio from your daily life. This tool which was introduced with SQL Server 2005 replacing the old Enterprise Manager has come a long way in making productivity of our developers and DBA’s to the next level. In this DEMO ONLY session we will look at this amazing tool and find out how this tool can be used effectively. There are a number of features like Object Explorer, Explorer Details, Template Explorer, Activity Monitor, Solution Explorer, Debugging, Tool bar and command enhancements that go unnoticed. We will look at each of these sections with loads of shortcut keys to play with SQL Server Management Studio. We assure you will go away with atleast 10 new things to do with the tool end of the sessions. 60+ tips in less than 60 mins.

Time: 11:45 – 12:45

Understanding Windows Better Using SysInternals

The Sysinternals utilities are vital tools for any computer professional on the Windows platform. This session focuses primarily on the utilities themselves, deep-diving into as many features as time will allow. Some of the popular utilities include Process Explorer, Process Monitor, Autoruns, VMMaps, PageDefrag, Desktops, Blue Screens and many more. It is humanly impossible to walk through each of these utilities at length with the tons of features loaded onto each one of them. We will take some of these lesser known tools to look at some common scenarios which everyone wants to use to troubleshoot in their daily life. This session will have a lot of demos and will question our very understanding of how Windows, Processes and threads work. Most of the session will dwell on the new and improved Sysinternals tool set. There is loads to learn and learning the basics can never be this much fun.

Time: 14:45 – 15:45

Architecting SQL Server HA and DR Solutions on Windows Azure

In the real life scenarios, SQL Server high-availability (HA) and disaster recovery (DR) solutions are not simply cloud-based, on-premises solutions, internally facing or external solutions. They are mix of a little bit everything. This session is highlighted for blending two state-of-the-art technologies with key learning’s: SQL Server AlwaysOn and Windows Azure. SQL Server AlwaysOn offers better availability, higher ROI and simplicity. With easy integration of SQL Server and Windows Azure technologies, customers can easily increase the efficiency and effectiveness of their solutions. SQL Server and Windows Azure can easily become the best HA & DR offering for customers on different segments for multiple aspects. The session highlights product capabilities, limitations, and trade-offs for various customer needs such as complex HA & DR scenarios. Cloud is the perfect solution to minimize CAPEX, why they are important to customers, how to implement them, and considerations to take. This session covers cloud, on premises and hybrid distributed geography solutions from technical perspective including lessons learned from real world deployments.

GIDS DATA Day – April 25th

This will be a marathon day sorts for me I think and the most anticipated as I will be doing a tango session with my friends.

Time: 14:05 – 14:50

Introduction to Microsoft Power BI

We use office tools in a number of ways and our day job involves in playing around with these tools. In this session we will focus on Microsoft Office Excel 2013 version and look at some of the new features introduced with Excel 2013. At the same time we will also look at some of the tips of using Excel for our daily use. Did you know the visual formatting, PowerPivot, Excel FlashFills introduced inside Excel? Did you know the tricks used for identifying invalid data using Excel as a powerful tool? Did you know the basic shortcuts to use when working with Microsoft Office Excel 2013? All these are interesting yet unexplored features of Excel. We will dwell deep into each of these features and will show how to use them effectively. It is a completely demo filled session with lot of interesting trivia in store for the attendees.

Time: 15:00 – 17:45

Hidden Secrets and Gems of SQL Server We Bet You Never Knew

It really amazes us every time when someone says SQL Server is an easy tool to handle and work with. Microsoft has done an amazing work in making working with complex relational database a breeze for developers and administrators alike. Though it looks like child’s play for some, the realities are far away from this notion. The basics and fundamentals though are simple and uniform across databases, the behaviour and understanding the nuts and bolts of SQL Server is something we need to master over a period of time. With a collective experience of more than 30+ years amongst the speakers on databases, we will try to take a unique tour of various aspects of SQL Server and bring to you life lessons learnt from working with SQL Server. We will share some of the trade secrets of performance, configuration, new features, tuning, behaviours, T-SQL practices, common pitfalls, productivity tips on tools and more.
This is a highly demo filled session for practical use if you are a SQL Server developer or Administrator. The speakers will be able to stump you and give you answers on almost everything inside the Relational database called SQL Server.

Apart from this my good friend Pinal is also doing a number of sessions and a must go if you are interested in some serious fun filled learning. He also wrote a detailed post on his sessions. I will be meeting a number of friends on these two days as many are coming as speakers too at this event. So if you are around at the event – dont forget to say a “Hi“ to me :).

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...


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...