 just going to use the GUI to encrypt the CardNumber column.
Next, select the column(s) you want to encrypt and choose the Encryption Type (Deterministic or Randomized) and the Encryption Key you want to use. I’m just going to be generating a new key for this demonstration.
Choose the Column Master Key. Once again, I’m generating a new master key and storing it in the Windows Certificate store.
Our CardNumber column is now encrypted! That wasn’t too difficult.
So what do we do if we actually want to view the encrypted data in SSMS?
I am now able to view the encrypted data. Why is this?
Without access to that certificate, I would not be able to see the encrypted data.
We can test this by attempting to connect to the database with the encrypted table from another machine and passing in the “Column Encryption Setting = Enabled” connection option in SSMS.
Failed to decrypt column 'CardNumber'.
Failed to decrypt a column encryption key using key store provider: 'MSSQL_CERTIFICATE_STORE'. The last 10 bytes of the encrypted column encryption key are: '36-B6-E0-8F-9F-54-AC-CF-9B-CB'.
What about viewing the encrypted data from SSMS on another machine?
So how can we get a copy of the Always Encrypted certificate over to the other machine?
Finish the export wizard and browse to the destination folder. We now have a copy of the Always Encrypted certificate.
Enter the password if one was used. Also, check Include all extended properties.
Step through the final screens and finish the import process.
After the import is finished, open certmgr.msc and ensure the Always Encrypted certificate shows up under Personal -> Certificates.
Now that the certificate has been imported, we can try to query the Encrypted table again.
BOOM! We can now query the encrypted data on a different machine with SSMS.
In this blog post, I’ve shown how to easily encrypt a column using Always Encrypted. I’ve also shown how to enable users using SSMS to view that encrypted data.
I’m pretty excited about Always Encrypted and think that it could potentially be a great solution for encrypting sensitive data.
T-SQL Tuesday #79 is being hosted by Michael J. Swart this month. His prompt was to check out what’s new with SQL Server 2016. I figured I would play around with the Query Store and our old friend HammerDB.
This post is a follow-up of sorts to my post HAMMERDB AND THE NEW SQL SERVER 2014 CARDINALITY ESTIMATOR. Read that first to get a basic overview of the problem we are trying to solve. Come back once you are finished.
With the new cardinality estimator introduced in SQL Server 2014, performance of the SLEV stored procedure used by HammerDB seriously regresses.
Adding Trace Flag 9481 to the statement that regressed in the stored procedure brings the performance back to the same levels as SQL Server 2012.
First, we need to figure out if the HammerDB workload performs similarly to when we first upgraded to SQL 2014. I’m going to follow the same steps that I took when upgrading the database to 2014, except I will be changing the compatibility level to 130 (the new 2016 level) and at the end I will be enabling the query store on the database. (For my specific HammerDB build instructions, check out that original 2014 post).
--Be sure to set the database compatibililty_level up to the newest 2016 version!
Here are the results from the same workload on SQL 2012, 2014, and 2016 all in one table. You can see that we have the same issue on SQL 2016 that we had when first upgrading to SQL 2014.
Sure enough, when we check the plan cache for the most expensive queries based on total_worker_time, we see the same query we would expect rise to the top. It is also using the same plan as it was on SQL 2014.
Can we use the new Query Store to force the ‘good 2012 plan’ for the query without modifying the stored procedure?
Let’s say, for whatever reason, that we don’t want to modify the stored procedure and manually insert the trace flag option onto the query. Is there still a way to force the ‘good plan’? Well, let’s find out.
First, I’m going to pull up the Top Resource Consuming Queries window in the Query Store.
You can see that our problem query is incredibly easy to find in the top left window based on total duration. Also notice that in the top right Plan summary window, there is currently only one available plan for the query (plan_id 49).
We need to figure out how we can get our ‘good plan’ using Trace Flag 9481 as an available plan that we can force using the Query Store.
--SET OPTIONS MUST BE IDENTICAL TO THE EXISTING PLAN IN CACHE!!
Just so that we can get a cool perfmon graph, I’m going to start the HammerDB workload before forcing the ‘good plan’. After about 30 seconds or so, I’ll force the ‘good plan’ using the query store and we can see what impact it has on the workload performance.
You can see the plan was forced right around the 3:55:20 mark. It quickly brought batches / second up to around 1800 and close to the SQL Server 2012 benchmark that was in the original post.
…and here are the stats after running a full workload with the Query Store fix in place. We still aren’t all the way back to the 2012 baseline levels, but we are much closer.
With new versions of SQL Server come new tools, and the Query Store is a very powerful tool that can be useful when you need to force a plan based on a trace flag but don’t want to necessarily hard code the trace flag into the query or stored procedure. I can’t wait to start using the Query Store on production databases.
Once again, always be sure to test workloads and applications before upgrading a production database to a newer version of SQL Server – especially when going from 2012 or older to 2014 and newer.
The other day, a developer came to me and requested a database backup be taken before we ran some upgrade scripts on the database provided by a vendor. This was only a test server so there wouldn’t be any traffic on the database. I thought about it for a second and figured that creating a snapshot of the database prior to the upgrade script being executed would probably be just as easy – and in the event something with the upgrade script errored out or went wrong, we could easily revert the database back to the snapshot which would be much quicker than an entire database restore.
I went to create the database snapshot and could not remember the syntax to save my life. I decided to make a simple script to help myself out in the future.
The entire script will be available down below. But I’ll show a quick example of how to get it setup. I will be using the StackOverflow database on which I have added two additional files/filegroups.
The only two changes that NEED to be made in the script are the @Target_DB and @Snap_Desc variables. Here, I am setting @Target_DB to ‘StackOverflow’ and @Snap_Desc to ‘Pre_Database_Upgrade_’ plus a timestamp. By default, the snapshot name will be a combination of these two variables (this can be easily changed if you like – but I like descriptive names).
--Combining @Target_DB and @Snap_Desc to create a snapshot name.
--This can be changed to whatever you wish.
Here is the example output from the script using the input variables from above. It generates the command to create the snapshot and also provides the command to revert the database back to the snapshot (just in case).
As of now, the script places the snapshot sparse files (.ss) in the same locations as the existing database.mdf and.ndf files. This can be manually tweaked pretty easily, but I may add in some options to specify a new location for the sparse files at some point in the future. Also, this script doesn’t create the actual snapshot – it simply outputs the command needed to create the snapshot. You will still have to manually execute the output yourself.
--begin creation of the create snapshot script here...
--if there is more than 1 database data file, we will need to iterate through each file....cursor time.
Well, that’s no good. I decided to investigate and quickly ran sp_WhoIsActive on the server to see what was going on.
This isn’t the original screenshot from when the job had been running for 53+ hours, but everything else was the same.
Why would MEMORYCLERK_XE be taking up so much of the memory on the box? I knew that the MEMORYCLERK_XE clerk was the total amount of memory that the extended events sessions on the server were using. So, once again, time for further analysis.
You can see that there were 5 different XEvent sessions on the server (we use Dell Spotlight which opens up three XEvent sessions on every server it monitors) – and all of them combined were using about 16.5mb of buffer space in memory. That was nowhere near the 1GB+ of memory that MEMORYCLERK_XE was using! So what gives?
My next course of action was to simply drop all XEvent session on the instance and hope that memory usage cleared up. I dropped all three Spotlight sessions, and disabled the system_health session