Does Encryption Affect Seeing Statements in Deadlock Graphs?

SQL Server 2012
Good question. I don’t have a clue. So let’s set up a test. I’ll create this stored procedure: [crayon-5a6c44892f1aa860306880/] Then I’ll execute things in the following order. From one connection this query: [crayon-5a6c44892f1b9751680175/] From a second connection, my stored procedure: [crayon-5a6c44892f1bf267945174/] Then, back on the first connection, this query: [crayon-5a6c44892f1c3005374409/] That will generate a deadlock. It’s a straight-forward classic deadlock. I’m using extended events to capture the deadlock graph and the output looks like this: [crayon-5a6c44892f1c9075602753/] Note, the statement offset is crazy, but that’s because it’s coming from an encrypted stored procedure. But the short answer is, yes you can (at least in this test).
Read More

Encryption and the Performance DMOs

SQLServerPedia Syndication, TSQL
Ever wonder what you can see in the performance oriented DMOs when stored procedures were encrypted? Me neither. But, I did get that question during my DMO presentation at the PASS Summit. I did not have an answer. I did get an answer from Johan Bijnens (twitter) from the audience, which I repeated without entirely knowing what I was saying. I decided that I ought to actually know the answer to that question, so here’s a little experiment. I'm going to create a simple stored procedure: CREATE PROCEDURE dbo.GetSalesDetails (@SalesOrderId INT) AS SELECT soh.AccountNumber, sod.LineTotal FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID WHERE soh.SalesOrderID = @SalesOrderID When I create this procedure and run it, you can see the general performance of the query being run…
Read More

Disabling Database Encryption

SQL Server 2008, SQLServerPedia Syndication, TSQL
SQL Server 2008 introduced TDE (either Total Database Encryption or Transparent Database Encryption, I've seen both) which is a way to get a passive encryption in place on the database to prevent people from stealing backups or detaching files & stealing them. It works with a Certificate in the server, and there are mechanisms around password protection of the Certificates, management etc. Setting it up & turning it on are very easy. This example is from a chapter on a book I'm working on: USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Chapter8Backup'; GO CREATE CERTIFICATE Chapter8Certificate WITH SUBJECT = 'Chapter 8 Certificate' GO USE AdventureWorksLT GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE Chapter8Certificate GO ALTER DATABASE AdventureWorksLT SET ENCRYPTION ON GO…
Read More