Archive for May, 2014

Don’t sell, solve the problem May 30th, 2014

Vinod Kumar

I have been working at the MTC for the past 3+ years as Technical Architect and the learning experience has been amazing. As part of my daily work we solve some of the toughest customer problems from performance, scalability, deployment, architecture, solutioning, best practices and more for big enterprises to product companies. Most of the customers are brought-in by our Sales team and when the customer experiences MTC, they get a different perspective of MS. So what are we doing differently. As part of my picture post, I have been wanting to pen down this thought process so that you will get a better idea.

Typical Sales mindset

Most of the sales teams are focused and start their discussion with Product / License and SKU’s in mind. Many are obliterated of what the problem they are solving end of day. They are worried about their sales quota to achieve for the quarter etc. I would put a typical mindset of a salesperson as following.

PS: I know not all salesman are like this, but just a general majority’s thought process.

MTC Architects Mindset

At MTC, we always start a conversation asking – “What is the problem?”. This is totally different way to start any conversation with customers. We make sure we have all the requirements, challenges and problems on the table and then we approach a solution as a whole. Rarely do we try to talk about specific product features unless it solves a problem for our customers. We want to make sure customers get the maximum value out of the solution and we want to be complete in our approach.

This is a simple workflow that I thought was worth sharing like a picture post. Do let me know if you have experienced something similar in your daily life or organizations that you work for. Does this make sense? Let me know your thoughts.

Continue reading...


8 ways to be Positive at Work !!! May 29th, 2014

Vinod Kumar

Being happy doesn’t mean that everything is perfect, it means that you’ve decided to look beyond the imperfections.

We all have had our good and bad days at work, but what sets us aside is how we deal with the stress that work brings to us. The high level term I am using here is to be “Positive”. There is surely a lot one can do to themselves than what you can see. Here are my top 8 things you can do to stay happy at work irrespective of the external pressures, office politics, deadlines, discontentment and others factors. I love to take control on things that I have influence on and I personally feel it is the best way to get the most out of you.

Learning is the key

This goes without saying that you need to find ways to learn something new everyday at work. Learning keeps you excited and keeps your confidence high and current. Just because you are out of formal college / learning environment doesn’t mean you have to stop learning. The day you stop updating your skills, you will be obsolete in this IT era in less than 6 months to an year – trust me.

Also, don’t expect to learn everything at work or as part of your work. That has never worked for me in this ~15+ yrs of career. I always say – “Any knowledge is good knowledge”, so don’t stop learning.

Share what you learn

I have come from the school where sharing is caring. So if you learn something, try to share them. One of my sharing avenues has been my blog, UG meets, presentations etc but at work this can mean a lot. If you share – you obviously making a statement to your management that you know something and because you have empowered your team mates, they are going to be lesser dependant on expecting you to finish the task assigned to them because they now know how to solve the same. I hate it when someone says – “You will miss the edge you have on others when you share”. I don’t subscribe to that thought process though.

It doesn’t matter if the glass is half empty or half full. Be thankful that you have a glass and grateful that there’s something in it.

Take mini-breaks

We all have an tendency to go on like a marathon when we do something that is really close to our hearts. Understand, there will always be work and work will fill all the time that you can give it. So try to leave your work at work and when you get home spend all the time dedicated to the family. They deserve every bit of it – your undivided attention.

The one thing I learnt after my first job change is not to work over weekends and I have been successful for the past 10+ years. I do other things that I love doing over weekends like blogging, speaking, going to orphanage, community building, teaching differently abled children or other activities which are not my official work.

Make loads of friends

I personally like to meet, greet and mingle with people. As part of my above Point-2, the best way to learn is by talking with people and sharing what you know. In this process you get to learn a lot more because they are going to ask some fundamental questions. It is an iterative process and symbiotic relation that you can build with people.

Do one thing at a time

I don’t quite believe in this multi-tasking concept (Multi-Tasking reality or just fad?). Take the task in hand and finish before going into a new activity. This has been my mantra for a long time. It is worth to recall yet another blog where I talk about the same thing – Getting more from your day.

Happy are they who take life day by day, complain very little, and are thankful for the little things in life.

Treat yourself for what you achieved

A pat on the back always helps and a sense of achievement can be overwhelming. It is always not necessary that you be recognized for what you do. If in your inner-self you are confident that you gave 100% in doing a task, take a break and give feel free to treat yourself (also your family maybe :)). Good work will always be recognized – if you gave your 100% it will automagically get noticed by folks in your organization. So don’t shrug – move on as you have a larger life in front of you than what you have within a given organization.

Be with positive people

A rotten fruit can spoil the complete batch – so it is important to be with people who have an positive attitude. A positive attitude helps you drive greater results. Your mind plays games that need positive blood, so try to hang around with people who inspire / motivate you always.

Be happy to share a smile

The best thing you can share with your colleague as you wish them in the morning is a warm smile. This is inline to the previous point, it sends positive vibes and creates an environment of inclusion and sharing. You now have become an approachable person and that is always a good quality to have. It is one of the simplest tasks one can do to create a great day for everyone at work.

Life is like a mirror: It’ll smile at you if you smile at it.

These were my Top 8 tricks to be happy and positive at work. All these are things under your control and you can influence them directly to stay positive in your daily life too. Hope you agree with me and let me know your thoughts.

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