casinos

Archive for the ‘Technology’ Category

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 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 p.name as ‘policy_name’, p.policy_id, p.is_enabled, p.execution_mode,

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

ORDER BY p.name

 

/* What conditions exist on the server? */

SELECT c.name 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

ORDER BY c.name

 

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

SELECT sh.health_state_id, p.name 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, p.name as ‘policy_name’, sh.policy_id,

sh.last_run_date, sh.result, sh.target_query_expression, c.name 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 p.name, 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  p.name, 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, pp.name 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,

d.exception_message

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 p.name 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.name, 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, p.name 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 cer.name AS Certificate_Name,

bks.encryptor_thumbprint,

bks.encryptor_type,

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

RESTORE HEADERONLY

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