Encryption, Trace Flags

Pause and Resume Transparent Data Encryption (TDE) in SQL Server

Transparent Data Encryption (TDE) was originally introduced in SQL Server 2008 (Enterprise Edition) with a goal to protect SQL Server data at rest. In other words, the physical data and log files along with the database backup sitting on file system are protected (encrypted).

Few things to be aware of when implementing TDE:

With TDE, The data transmitted over the network is not encrypted and the data at the object level remains unencrypted. In other words, if a user has select access to a table(s) within the TDE enabled database, he/she will be able to read data with simple select statements, as the name suggests, it’s transparent. TDE does not protect FILESTREAM data and any files related to Buffer Pool Extension (BPE) are not encrypted as well, you should use file system encryption tools like windows Bit-locker or any other third party tools for this purpose. Another caveat is TDE doesn’t support Instant File Initialization for database files. Also, when TDE is enabled on a user database your tempdb database gets encrypted behind the scenes.

Okay, let’s move on to our topic. In this article, we will see how to monitor and manage TDE progress, not essentially how to setup TDE on a user database. Before moving on to our main topic, here is a quick refresher on how Transparent Data Encryption works.

Image Source

Enabling TDE on a given database is a very straightforward process. 20,000-foot view of the process is basically creating a DMK (Master DB) which is protected by Service Master Key, Cert (Master DB), DEK (User DB) and enable TDE(User DB) and you are done. But things get little tricky when you are dealing with VLDBs. What if you have a ginormous database (Let’s say a 30 TeraBytes monster) on which you have to enable TDE? Enabling TDE is not instantaneous, the SQL Server Encryption Scanner has to read all the underlying database pages and encrypt them, For a 30 TB database it might take multiple days for SQL Server to encrypt the entire database and we as DBAs should monitor the encryption progress making sure there are no side effects. In this context I am not talking about server resources, I am talking about the impact on the transaction log file when encryption scanner is in progress. So, why should we worry about LDF file when TDE scanner is in progress? Well, SQL Server doesn’t truncate the transaction log file of your database when TDE Encryption Scanner is doing its job. Things get more complicated if you are not allowed to run TDE during business hours and let’s say you have nightly ETL loads or some other scheduled job(s) which generates a considerable amount of log records. In this article, let’s see how to monitor TDE progress and how to manage transaction log when TDE scanner is in progress.

Further reading : Monitoring/Managing TDE

Advertisements
Backups and Restores, Disaster Recovery, Tools

Ola Hallengren’s DDBoost Backups

The already awesome Ola’s Maintenance solution for SQL Server became more awesome with his latest release. If you are not aware of Ola’s maintenance solution, you should definitely check it out. In the July 2018 release, quite a few enhancements were introduced. My personal favorites are ability to add percentage of modified rows to update stats, resumable index rebuilds support and support for EMC DDboost(Data Domain Boost). In fact I was badly waiting for DDboost support last year(we were using DDboost to backup our VLDBs). Unfortunately, DDboost was not something Ola’s solution suported at that time and what I ended up was writing up my own stored procs calling “dbo.emc_run_backup” and “ddbmexptool.exe” based on our Lockbox settings and scheduled the jobs as needed. Well, I got it worked without any issues but had to spend quite a bit of time to understand all the bells and whistles of DDboost and come up with my own procs and error logging.

If you happen to have any plans evaluating DDBoost for SQL backups now or in near future, well you have an awesome solution already available. All you need to do is pass ‘DATA_DOMAIN_BOOST’ as a parameter for third-part backup software in Ola’s backup job and specify below parameters.

Image source: https://ola.hallengren.com/sql-server-backup.html

References:
https://ola.hallengren.com/

A big shout-out to Ola! Cheers, Happy weekend.

SSMS, Tools

Dedicated Admin Connection(DAC) from SSMS

DAC is probably the most useful tool for troubleshooting SQL Server in few scenarios. Basically, It can be used as a back door entry in case the server goes unresponsive and doesn’t accept any new connections. So, how to make a DAC to SQL Server Instance? Preferred way is to use SQLCMD by issuing -A switch. But few folks might prefer SSMS over SQLCMD. In this blog post let’s see how to make a dedicated admin connection from SSMS.

Well, all you need to do is prefexing “admin:”to your instance name. I’ve a default SQL Instance(SQLTREK2), so in order to make a dedicated admin connection, all I’ve to do is use “admin:SQLTREK2” as my connection string. Okay, Let’s see what my SSMS does when I do that.

Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design. (Microsoft.SqlServer.Management.SqlStudio.Explorer)

If you wonder what multiple connections its talking about, It is referring to Object Explorer(Yes, OE establishes its own connection). But there can be only one DAC session at any given time. So, how to get around this issue in SSMS?

Method 1:
Creating DAC session by initiating connection from query pane. In the below screenshot, you can see I have a query pane which is currently disconnected.

Right click in the query pane and go to connection and click connect(providing admin:Instance name)

Well, SSMS created a pop-up which says it failed to connect, But I would like you folks to focus on the status bar where it did established a connection using admin:SQLTREK2. So basically this window is your DAC session.

Method 2:
By using ‘Database Engine Query’ connection. By using this method, you can avoid the error message which SSMS returned(shown in the above screenshot). All you have to do is click on Database Engine Query button right beside ‘New Query’ and make a connection. See below screen shots.

Voila! That was easy right….

Bonus points to you if you have noticed difference between ‘connect to server’ and ‘connect to database engine’. See below if you haven’t.

From Object Explorer: (DAC fails)

DB Engine query: (DAC Succeeds)

Hope this helps! Happy holidays…!

Corruptions, DBCC., SQL Server 2016

dbccLastKnownGood – The 2016 way.

How to check when was the last known good CheckDB for a given database? As of today on all the versions including SQL 2017(Except 2016 SP2), the easiest way we can get that information is by querying boot page of the database and look for “dbi_dbccLastKnownGood” value. You could do that by using DBCC page or DBCC DBINFO(). For folks who are paranoid about running DBCC PAGE or DBCC DBINFO command in production, If you have a monitoring tool which is displaying this information, I am 100% sure that’s exactly what your tool is running behind the scenes. Nothing to worry about, we are just reading the contents, not modifying anything in the boot page. Anyways, what got changed or I should say what was introduced in SQL 2016 SP2?

Well, we can pull this information from our good old friend DATABASEPROPERTYEX()  🙂

The Microsoft documentation says “Starting SQL 2016 SP2”. Okay, let’s test this on SQL 2017. I have a test database named “packers” on which I ran checkdb around 10:56 PM 05/14/2018 successfully. FYI

Let’s verify by pulling the info from DATABASEPROPERTYEX() on my SQL 2017 RTM Instance.

Oops! It returned NULL. So, basically it’s saying that we provided an invalid Input. Hmm, that’s not good!

Alright, Let’s check what does that report on a SQL Server 2016 SP2 Instance.
From Boot page:

Now, let’s pull the info from Databasepropertyex().


Yeyyy….That works! So….As of today, May 14th 2018, looks like this only works on SQL 2016 SP2 which was released just few weeks ago. I hope Microsoft fix their documentation or get this thing implemented in SQL 2017 in the next CU.

I wonder why it took so many years for Microsoft to implement this simple feature. Anyways we have it now and we don’t need to worry about querying boot page to pull this basic yet very useful/critical information.

Cheers!

Service Packs/Cumulative Updates, SQL Server 2016

SQL Server 2016 SP2 got released :)

Microsoft released SQL Server 2016 Service Pack 2 today. Visit https://support.microsoft.com/en-us/help/4052908/sql-server-2016-service-pack-2-release-information for all the details you need. There’s a looooong list of number of bugs fixed and enhancements made. Couple of DMVs and features from SQL 2017 were back ported as well.

Make sure you go all the way to the bottom of the page regarding GOTCHAS with uninstalling 2016 SP2 if needed. This is the section which I would like you guys to focus on if you are not on Enterprise Edition.

Cheers!

SQL Server 2017, SSMS

SSMS – Security Vulnerability Assessment(VA)

In this blog post, let’s explore one of the fantastic features added to our good old friend SSMS by Microsoft, Security Vulnerability tool. I am currently running SSMS 17.6, but this feature which we are going to see has been introduced in version 17.4. Okay….So, let’s see what this is all about.

I have a database named “ChickenNuggets” which I would like to know what are the potential security risks it has. Well, All I need to do is connect to SQL Instance from SSMS 17.4 or above and Right click on the database and go to tasks and navigate to VA and do a scan. See below screenshot for what I am talking about.

Give it a path and Wait for few seconds and voila…..you get something like this.

As you can see, it reported 10 failed checks for this DB. Also, You can select any item and approve as baseline if that’s normal in your environment, That way SSMS doesn’t include that particular item under failed list as it has been included in baseline.

As you can see it’s giving a detailed explanation along with what exact Query it ran for this particular check and the best part is it gives you remediation script as well.

Now, What If I want to scan something at Instance level, I could perform the same on Master Database.

That’s all good so far right? But there is still huge room for improvement. What if my instance has 100 databases? I hope you already guessed where I am going with this….Yes, That’s correct, I have to repeat this 100 times at individual database level. Well that sucks. Also, I don’t see anyway to export these results to different formats. It would be really nice if Microsoft gave us an option to export this to PDF format or HTML.  But hey…this is a start and I am positive Microsoft is listening to community and these will be made available to us very soon in future releases of SSMS.

BTW only SQL Server 2012 Instances and later are supported, and of course Azure SQL Database 🙂 .This is extremely light weight tool which you can run at any time without impacting your workload. It just runs bunch of DMVs…Shouldn’t block anything. But hey, don’t trust anyone and anything posted online when it comes to your Production Databases. After all that’s our Bread and Butter!

Happy weekend folks.Cheers!