Archive for the ‘Technology’ Category

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 with Policy Based Management (PBM) May 26th, 2014

Vinod Kumar

SQL Server 2008 introduced a feature called Policy Based Management that allows a database administrator to manage one or more instances or SQL Server 2008 through policies. Depending on the policy and how it is defined, Policy Based Management can either audit compliance of the policy or enforce the compliance of the policy.  Policy Based Management allows database administrators to create policies and determine how they should be applied to either audit or enforce the policy.  This gives database administrators a powerful a new feature to administer one or more instances of SQL Server 2008 and above.

I have recommended using PBM in some form to most of my customers atleast to keep tab on their environment and make sure the standards / best practices have been followed. Moreover it also gives a great way for Administrators to keep an eye on their environment. Recently an administrator asked be during a session, “Vinod, is there a way to keep track on all the work I am doing with PBM?”. I got down to search my script arsenal from my sessions on PBM immediately. I got a great set of diagnostics script which I felt was worth sharing over blog – infact I was wondering how I forgot to share it with you folks :).

/* What policies exist on the server? */

SELECT as ‘policy_name’, p.policy_id, p.is_enabled, p.execution_mode, as ‘condition_name’, c.facet, p.date_created, p.created_by,

p.modified_by, p.date_modified

FROM msdb.dbo.syspolicy_policies p

INNER JOIN msdb.dbo.syspolicy_conditions c

ON p.condition_id = c.condition_id



/* What conditions exist on the server? */

SELECT as ‘condition_name’, c.condition_id, c.facet, c.description,

c.expression, c.date_created, c.created_by, c.modified_by

FROM msdb.dbo.syspolicy_conditions c



/* What is the health of the policies ON the server? */

SELECT sh.health_state_id, as ‘policy_name’, sh.policy_id,

sh.last_run_date, sh.result, sh.target_query_expression

FROM msdb.dbo.syspolicy_system_health_state sh

INNER JOIN msdb.dbo.syspolicy_policies p

ON sh.policy_id = p.policy_id

ORDER BY sh.health_state_id


SELECT sh.health_state_id, as ‘policy_name’, sh.policy_id,

sh.last_run_date, sh.result, sh.target_query_expression, as

‘condition_name’, c.expression

FROM msdb.dbo.syspolicy_system_health_state sh

INNER JOIN msdb.dbo.syspolicy_policies p

ON sh.policy_id = p.policy_id

INNER JOIN msdb.dbo.syspolicy_conditions c

ON c.condition_id = p.condition_id

ORDER BY sh.health_state_id


/* What is the count of policy health results? */

SELECT, sh.policy_id, sh.result, COUNT(*) as ‘count’

FROM msdb.dbo.syspolicy_system_health_state sh

INNER JOIN msdb.dbo.syspolicy_policies p

ON sh.policy_id = p.policy_id

GROUP BY, sh.policy_id, sh.result

ORDER BY count(*) DESC


/* What categories and subscriptions exist ON the server? */

SELECT * FROM msdb.dbo.syspolicy_policy_categories

SELECT * FROM msdb.dbo.syspolicy_policy_category_subscriptions


/* What is the execution history of policy violations */

SELECT h.history_id, as ‘policy_name’, pp.policy_id,

pp.execution_mode, h.result, h.start_date, h.end_date, datediff(ss,

h.start_date, h.end_date) as ‘exec_time_ss’, d.target_query_expression,

cast(d.result_detail as XML) as ‘result_detail’, d.exception,


FROM msdb.dbo.syspolicy_policies AS pp

INNER JOIN msdb.dbo.syspolicy_policy_execution_history AS h

ON h.policy_id=pp.policy_id

INNER JOIN msdb.dbo.syspolicy_policy_execution_history_details AS d

ON d.history_id=h.history_id

ORDER BY h.history_id


/* What is the count and execution times of execution history policy

violations */

SELECT as ‘policy_name’, p.policy_id, COUNT(*) as ‘count’,

AVG(datediff(ss, h.start_date, h.end_date)) as ‘avg_exec_time_ss’,

MAX(datediff(ss, h.start_date, h.end_date)) as ‘max_exec_time_ss’

FROM msdb.dbo.syspolicy_policies AS p

INNER JOIN msdb.dbo.syspolicy_policy_execution_history h

ON p.policy_id = h.policy_id

GROUP BY, p.policy_id

ORDER BY count(*) DESC , avg_exec_time_ss DESC, max_exec_time_ss DESC


/* What exceptions have occurred? */

SELECT h.history_id, as ‘policy_name’, p.policy_id, h.start_date,

h.end_date, h.result, h.exception, h.exception_message

FROM msdb.dbo.syspolicy_policies AS p

INNER JOIN msdb.dbo.syspolicy_policy_execution_history h

ON p.policy_id = h.policy_id

WHERE datalength(h.exception) > 1

ORDER BY h.start_date DESC, h.end_date DESC

So feel free to use them to troubleshoot and know about your PBM environment. Will share other scripts as I find them from my arsenal of scripts.

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


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