SQL Server TDE (Data at Rest Encryption)

What is TDE ?

Transparent Data Encryption (TDE)  is used by Microsoft, Oracle, and other Database provider companies to encrypt data files. This solves the compliance issue generally known as Data at Rest Encryption.

TDE was first introduced by Microsoft with SQL Server 2008. TDE is available with Evaluation, Developer and Enterprise Editions only. (Also Datacenter Editions in some older versions).

How TDE works ?

With TDE Database Files are encrypted, hence, prevents the Data Files from exploitation if data files or backups are copied out with bad intentions. 

Some Facts about TDE

• During Backup, if compression is enabled, it cannot take much advantage from compression due to the encryption.
• If any of the database is encrypted on an instance, TempDB is anyways encrypted.
• TDE only encrypts Data at Rest. Data in Motion, Data in Transit or Data in Memory is not encrypted.
• Any File groups which are Read-Only, won't be encrypted, hence TDE implementation will fail.
• Once TDE is enabled, Mirroring or Log Shipped databases on the other side will also need be encrypted as TDE keeps Transaction Logs Encrypted.
• Even though the Certificate Expires, it still encrypts database. Certificate Expiration is more of compliance related and you can set custom expiry date with EXPIRY_DATE option while creating the certificate, by default it is a year.

Encryption Hierarchy (https://msdn.microsoft.com/en-us/library/bb934049.aspx)

• Encryption Hierarchy starts with Windows level Data Protection API (DPAPI). During the setup or installation of SQL Server Instance, the Service Master Key is encrypted with DPAPI. 
• Service Master Encrypts the Database Master Key on Master database.
• Using Database Master key, a Certificate on Master Database is created. 
• The Certificate on Master Database is used by all other databases on same instance to create Database Encryption Key (DEK). 
• Finally the DEK is used to Encrypt the entire User Database.

Implementing TDE

1. Create Master Key on Master Database
2. Create Certificate protected by Master Key on Master Database
3. Create Database Encryption Key using Certificate on User Database that has to be encrypted
4. Change the Database Option setting to Encryption
5. Backup Certificate and Private Key at safe place (optional but recommended)

Demo

Create Master Key

Use Master
Go
Create Master Key
Encryption By Password ='@$trongPa$$word@'
Go


Create Certificate

use master
Go
create Certificate TDE_CERT
WITH SUBJECT ='TDE Certificate'
Go



Create DEK (Database Encryption Key) using the certificate created in last step. This step has to be run on the database where the TDE has to be enabled

Use myDB
Go
Create Database Encryption Key
with algorithm = AES_256
Encryption By Server Certificate TDE_CERT;
GO



Finally Enable TDE for the database

use master
Go
Alter database myDB
Set encryption on
Go

You can check the status of encryption by using DMV: sys.dm_database_encryption_keys

select encryption_state, percent_complete, * from sys.dm_database_encryption_keys

Encryption_States:
0 - No Encryption
1 = Unencrypted
2 = Encryption in Progress
3 = Encrypted
4 = Key Change in Progress
5 = Decryption in Progress (Disabling TDE)
6 = Protection change in progress



Backup Certificate

Use Master
Go
backup certificate TDE_CERT
To FIle='C:\encryption\TDE_CERT.cer'
with private key (file='C:\encryption\pkey.key' ,encryption by password ='@$trongPa$$word@')
Go

Make sure to keep the Certificate and Private key at safe place. Without Certificate and Private Key, database recovery will be impossible.

How to reload Missing SQL Server Performance Counters

Recently I was working on one of the servers to analyze the performance using Performance Counters which were collected historically for a long period of time.

I noticed some of the needed counters were missing for the collection. 

So what was happening with that server where performance counters were missing? After running the following query on current snapshot of performance counters, I did not get the result. SQL Server was not reporting any counters. 

After doing some research I found that there is one configuration file that lists all the performance counters which SQL Server loads. The name of the performance counter configuration file is generally perfsqlctr.ini or perf-sqlctr.ini located in Binn directory.

To precisely get the file name and location, you may take help from Registry.

1. In Registry Go to HKLM\SYSTEM\CurrentControlSet\services\
2. Extract the value from Key ImagePath until Binn
3. Go to HKLM\SYSTEM\CurrentControlSet\services\[InstanceName]\Performance
4. Extract the value from key PerfIniFile
5. Merge the values from #2 and #4, that is the file name which has all of the counters.

Now you got all of the file names needed, follow these steps to reload the counters.

1. Open file in text editor, make sure that all of the needed counters are there in the file, if not there then add counter while matching the format of existing counters.
2. Unload the Counters
• If Default Instance then run following command from shell.
• Unlodctr MSSQLSERVER
• If Named Instance then run the following command from shell.
• Unlodctr MSSQL$[ServiceName]
3. Restart the MSSQL Service for that instance
4. Reload the counters with following command from shell.
• Lodctr [path\file you got in #5]
5. Restart MSSQLService for the instance

It worked for me, I could see the counters.



If you still see the issues, then please make sure that the registry keys as discussed above are accessible by Service Account of SQL Service. Then repeat steps between 2 and 4.

A glance at SQL Server Statistics

Statistics are sometimes ignored when it comes to Performance Tuning. Statistics can play a big role in identifying recurring slowness.

Before executing any query, SQL Engine Optimizer should know in advance some information, like the number of records, page density, indexes etc. If updated information is available, the query optimizer will use best execution plan to execute the query.

Before go further on this, here is an example, an online store makes sure that they have enough stock in warehouse that is ready to ship to meet the demand. But their system is broken and online ordering system is unaware of stock quantities in warehouse, then probably online ordering system will order item directly from distributor which will take longer time for item delivery.

On the other hand if online store has outdated information that their warehouse has enough stock, the online order will be mistakenly sent to warehouse, which then be re-routed to the distributor.

Both of these scenarios have bad impacts because information was outdated somewhere.

This example may not fit 100% with SQL Server statistics but it is very close.

Let's take a look at SQL Server with same example. Store A has 10 warehouses in a state at different zip code locations. Store decided to merge all warehouses in one big warehouse to reduce overhead cost.

After the physical change, this information has been updated in database, by simply updating affecting zip codes to one zip code. Note that the index is built on Zip code. There is an pre-existing index on the zipcode column.

Order entry system complains that after merging the Zip codes, the system is little sluggish. The reason of this sluggishness lies behind mass updates. The reason behind this issue could most probably be outdated statistics. Let's take this example:

• Create a table:

CREATE TABLE [dbo].[Warehouse_Inventory](
[ItemCode] [varchar](50) NOT NULL,
[ZipCode] [int] NOT NULL
) ON [PRIMARY]
GO

• Now insert some data:

declare @zip int, @inventory int
set @zip = 1
while (@zip < 200000)
begin
set @inventory = @zip+22.5
insert into [dbo].[Warehouse_Inventory]
select 'Inventory ' + cast(@inventory as varchar(10)), @zip
select @zip = @zip+1
end

• Create a non clustered index on zip code:

CREATE NONCLUSTERED INDEX [ClusteredIndex-myindex] ON [dbo].[Warehouse_Inventory]
(
[ZipCode] ASC
)
GO

•  Let's run our first query to get inventory from location 30001. Only 1 row should be returned.

select zipcode from [Warehouse_Inventory] where ZipCode = 30001 OPTION (RECOMPILE)

Query used the index as expected, let's look at the index statistics. See from the screenshot below the Estimated and Actual number of rows matches. What query optimizer estimated, same was returned. This sounds good.

Execution Plan after creating Index

Since the zip codes were updated, about 90% of the warehouses are moved to one zip code, 30001.  In table, we need to update those 90% of the zip codes.

update [dbo].[Warehouse_Inventory]
set zipcode = 30001
where zipcode < 180000

Without making any further changes, without rebuilding the indexes etc., running the same query again should return 179,000 rows. Let's look at the same index execution plan created by query optimizer.

Query optimizer estimated only 1 row to be returned, though there were 179,000 rows expected.

Now, let's fix the statistic by updating it manually.

update statistics dbo.warehouse_inventory.

After updating the statistics the number of rows estimated by query optimizer are 179,000. This is what we wanted to see in execution plan.

If query optimizer sends wrong estimate then probably wrong execution plan can be picked which can cause some performance sluggishness.

Here is the query that can be used to find out how many modifications were made to statistics. This can probably help in devising a custom solution for some of the indexes which are updated throughout the day in production application.

SELECT
[sp].[object_id],
OBJECT_NAME([sp].[object_id]) AS 'ObjectName',
[sp].[stats_id] AS 'StatID',
[st].[name] AS 'StatName',
[sp].[last_updated] AS 'LastTimeStatUpdated',
[sp].[modification_counter] AS 'NumberOfModifications'
FROM [sys].[stats] AS [st]
OUTER APPLY sys.dm_db_stats_properties ([st].[object_id],[st].[stats_id]) AS [sp]
where OBJECT_NAME([sp].[object_id]) is not null
and OBJECT_NAME([sp].[object_id]) not like 'sys%'
and OBJECT_NAME([sp].[object_id]) in ('Warehouse_Inventory')

The example above is very simple and probably you may not see any major difference in performance, it was just to demonstrate how to proceed with Statistiscs.

There are some options that SQL Server provides at database level. These options are Auto Update Statistics and Auto Update Statistics Asynchronously.

Auto Update Statistics: If while executing the query, the statistics found outdated by a ratio then update the statistics before executing the query.

Auto Update Statistics Asynchronously: If while executing the query, the statistics found outdated by a ratio and statistics update request will be sent to engine in background.

One or both of these options can be switched on. Since all servers have different load, environment, usage, thus be little careful before opting these options. In General, OLTP may respond very well to ASYNC, though database warehouses have SYNC options go well.

Data Collection (MDW) in SQL Server for performance monitoring.

With Microsoft SQL Server 2008, Microsoft has incorporated new performance tool called Data Collector, also known as MDW – Management Data Warehouse and also Known as Performance Studio.
With MDW, you can collect SQL Server performance information, IO usage, disk usage, memory usage, locks, blocking, queries taking longer time, couple of performance counters etc. The useful information it collects can be preserved for long time and can be analyzed for the purpose of troubleshooting.
MDW creates it’s own database warehouse (MDW) to collect information. There are pre-configured collection sets including Server Activity, Query Statistics and Disk Usage. The data collected in MDW is published in SQL Server Management studio with very useful reports. This feature is not configured by default and can be found under Management in Object Explorer of SQL Server Management Studio.




As mentioned above this feature is  by default disabled, this can be enabled and configured by selecting ‘Configure Management Data Warehouse wizard’ option after right clicking on Data Collection.



Selecting ‘Configure Management Data Warehouse’ starts a wizard as shown below to configure data warehouse.



First part of configuration is to create Database Warehouse and second part is where you setup data collection.
In the following part, we are setting the database for first time, and selected option “Create or upgrade a management data warehouse”.



Create New database to collect information by select New option from following screen.








From the following screen, select Login and map MDW users to that Login or Logins. There are three roles for MDW, admin, writer and reader. You may either create a single Login and give admin or you may give appropriate rights to Logins, as per your requirements.



Review the information before clicking on Finish.




Since the database has been configured, the next part is to set the data collection. In case if MDW database has been setup on another server, this will be your first step for this server.



Data collector collects some information from traces and other parts which it keeps locally in a folder before pushing it to database. In the following Window you may chose directory to keep this information.



Review the information before clicking on Finish.




Once this process is completed, Next step is to schedule the jobs, configure the maximum retain period of collection.



Right click on Disk Usage, select Properties. In the property Window review and change the information like the frequency of collection, default service account to run jobs associated with current collector. You can also configure the ‘Retain data’ property and specify number of days you want to keep the information.



Follow the same practice for all three collector sets. Once it will configure correctly you will see reports and useful information by right clicking on Data Collection and selecting the appropriate report, as shown in following screen shot.



Server Activity report will look like as shown below.



By default it will show you data for last four hours, however you can change the date and range you want to see the reports. As shown below, click on calendar icon and select date range and number minutes/hours you want to see the report.



In this case, the report will be shown for data collected for 15 minutes starting 4 PM.



Following same way, you can view other reports as well. You may explore this feature to understand this and get used to this.
This is a great and may save money for your organization, if you are planning to buy third party monitoring tool for SQL Server.

Real time performance analysis of Stored Procedures

By assuming that there are no Hardware issues with SQL Server, but still SQL Server performance is not up to the mark, following query can provide some useful information for troubleshooting.

select a.*, p.query_plan , p.number
from sys.dm_exec_procedure_stats as a
cross apply sys.dm_exec_query_plan(a.plan_handle) as p
where a.object_id=object_id('[sch].[User_SP]')
There are some very useful columns returned by this query, those are:

cached_time : Recent Recompile Time
last_worker_time : Execution time in micro seconds
last_physical_reads: Number of pages read from physical storage system.
last_logical_reads: Number of pages read from buffer, instead of physical storage.
query_plan: XML representation of query plan, which was used during recent sp run.

The tables in query just keeps the last event i.e. only one record. Adding this query in job allows to store the information in temporary table and after couple of minutes you can get enough details for investigation. For single procedure, more than one query plan may exist. You may compare query plans to find out the differences.

In one of the real time scenarios, I realized that number of logical reads were few millions, for just returning 100 rows. There was issue with one of the join that caused more number of logical reads.

This is not the end, you may also compare physical reads with logical reads. Also consider frequency of procedure being recompiling. Too much recompiling may cause overhead to CPU.

Note: Logical Reads over Physical reads are good. More logical reads shows that the query is getting it’s data from buffer, more physical reads are sign of potential IO bottleneck.

How to enable Replication on Publisher (SQL 2008)

Introduction to Replication.

Before enabling Replication on Publisher you must decide where you want to run the distribution database. Distribution database keeps track of logs to be replicated among subscribers.
Keeping distribution database on same server as publisher may cause IO bottlenecks, depending upon hardware configuration and existing load on server.
Follow the steps to below to enable Distribution and Replication. These steps will add distribution server on selected server.
1. Open SQL Server Management Studio, Connect to SQL Server and Expand SQL Server where you wish to enable publishing and distribution.



2. Right click on Replication in Object Explorer, then select Configure Distribution. This will start “Configure Distribution Wizard”.



3. In case distribution is already setup somewhere, then select appropriate option,



4. In the following screenshot, notice that Snapshot Folder location is a network location. It is a good idea to keep it as network location, so that sql server agent can running on another server can access this location in case of pull subscription.



5. Enter the location to create data files,


6. From the following screen, you can add publisher server to distribution list. Click on Add button and select “Add SQL Server Publisher” to add SQL Server publisher. There is one more option available to add Oracle Publisher too. You may add more than one publisher here.



7. From the below screen select password for remote connection to distribution database.



8. From the following window, you can either select to Configure distribution, or generate the t-sql to configure distribution manually or both.



9. Read the summary of actions below before configuring the distribution.



10. Click Finish to complete the Configuration.

Replication on SQL Server 2008.

One of the High Availability feature of SQL Server is Replication. Replication shares the selected tables, views or procedures among other servers. In fact Replication is not fully High Availability solution, but it can helpful for reporting servers and Warehouse etc.
There are three models of Replication supported by SQL Server 2008. Transaction Replication, Merge Replication and Snapshot Replication.
Snapshot Replication This is most simplest model of Replication. Snapshot Replication just restores the initial set of data and does not support any incremental restore of logs.
Agent: Snapshot Agent, Distribution Agent
Transactional Replication This is most common replication model. Typically this Model starts with initial snapshot. Using this model, Log Reader service reads the transactions and send them over to Distribution Server. Distribution server then distributes the transactions to all subscribers. This entire process by default runs continuously, however this can be scheduled to run on specific intervals.
Agents: Snapshot Agent, Log Reader Agent, Distribution Agent.

Figure Courtesy SQL Server BOL: Transaction Replication

Merge Replication Merge Replication is Similar to Transaction Replication, Merge Replication also typically starts with Snapshot of initial data. Once initial snapshot is applied, it merges the changes occurred at publisher or subscriber.
Merge Replication requires UniqueIdentifier type column in each published table. If this column type is not already added, it adds this column. To track changes, Merge Replication also adds Insert, Delete and Update Triggers for each of the articles (aka Published Objects). These triggers use the UniqueIdentifier type column in each table to identify the changes, which was added above.
Agents: Snapshot Agent, Merge Agent.

Figure Courtesy SQL Server BOL: Transaction Replication

There can either be Push Subscription or Pull Subscription. In case of Push Subscription, Distribution Agent and Merge Agent runs at the Distributor. However, in case of Pull Subscription, Distribution Agent and Merge Agent runs at Subscriber.

Each agent of Replication is well described on BOL too. Please click here.

Enabling Distribution on SQL Server 2008.

Windows NT user or group 'Domain\User' not found. Check the name again. (Microsoft SQL Server, Error: 15401)

When you add new Domain Login to SQL Server, you may see error  Windows NT user or group 'Domain\User' not found. Check the name again. (Microsoft SQL Server, Error: 15401).

This error message is very general and it does not explain any specific problem or reason itself. Microsoft has a very good article to fix this issue at http://support.microsoft.com/kb/324321/en-us.

However, there is another scenario which is not covered in above article (at the time when writing this article) or may be I am the first person who faced this issue.

This scenario can be reproduced on Windows 2008 Server with SQL 2008 Server where the Domain Controller is Windows 2000 server. On Windows 2008 server, 2 new policies have been enabled by default that encrypts the secure channel data when new LOGIN request is sent to Domain Controller by Domain member(Also SQL Server). In this scenario, Domain Controller is Windows 2000, thus it does not understand the encrypted request thus refuses the LOGINrequest. All you need to do is to fix this behavior in Windows 2008 (SQL Server) to not to send encrypted secure channel data to Domain Controller. To do this follow the steps below and it should fix the issue.

1. From the SQL Server running Windows 2008 R2, Click Start-> Run and type the command GPEDIT.MSC. This will open the Policy Editor.
2. From Policy Editor Expand “Computer configuration” - > Windows Settings -> Security Setttings -> Local Policies -> Security Options.
3. You will see all security policies on right hand side window. Make changes into the following two policies.

• Domain member: Digitally encrypt secure channel data (when possible) – Disable this policy
  
• Domain member: Digitally sign secure channel data (when possible) – Disable this policy

After making these changes, close the policy editor and reboot the box. (Not SQL Server, but restart entire system).

In case your local policy does not allow you to make changes, you may have to make changes using Group Policy Management Console. Instructions to install GPMC are located at http://blogs.technet.com/askds/archive/2008/07/07/installing-gpmc-on-windows-server-2008-and-windows-vista-service-pack-1.aspx.

• Run gpmc.msc (Group Policy Management)

• Expand your Domain

• Go to and select and then follow steps 2 and 3 from above.