Monthly Archives: June 2015

SQL Server TDE Encryption and Query Performance

— By Lori Brown  @SQLSupahStah

I recently was tasked with finding out how implementing TDE on a database along with encrypting several columns in a heavily used table was going to affect application code and query performance. Here are the steps I took to implement TDE encryption along with some query run statistics. I also have the steps needed to restore a TDE encrypted database to another SQL instance.

I will make another blog post that contains the steps for column encryption with query performance so stay tuned for that.

Encrypting a database at rest using TDE

TDE (Transparent Data Encryption) is only available in Enterprise Edition and is enabled at the database level. Turning on TDE for a database will encrypt the database at the file level including tempdb and is often described as protecting the database at rest. So what does “at rest” mean? It means that the database files at the IO level including the backups are encrypted so that if a thief took a backup and tried to restore it they should be unable to if they do not have the master key. The same goes for detaching, copying the database and transaction log files and attaching them to another server.

While many companies are reluctant to encrypt because of the potential changes that may have to be made to application code, TDE as the name states is transparent. With TDE enabled if a user has permission to access the database then they will still be able to access data without any change to code. Notice that a master key and certificate are created as part of the process of enabling TDE encryption. It is also extremely important to back these up and document any passwords associated with them.

Here is how to enable TDE for a database.

1.Create (DMK) database master key, open it up and back it up.

USE master

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘C001P@sswo4d!!’;

GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘C001P@sswo4d!!’

GO

BACKUP MASTER KEY TO FILE = ‘C:\KeyBkup\SQLDMK.dmk’

ENCRYPTION BY PASSWORD = ‘0urDB1sS3cur3#’

GO

2.Use this query to check to make sure that the master key has been set up.

SELECT b.name, a.crypt_type_desc

FROM sys.key_encryptions a

INNER JOIN sys.symmetric_keys b

ON a.key_id = b.symmetric_key_id

WHERE b.name = ‘##MS_DatabaseMasterKey##’;

GO

3.Create and backup a certificate.

CREATE CERTIFICATE TDECert WITH SUBJECT = ‘Cert used for TDE’;

GO

BACKUP CERTIFICATE TDECert

TO FILE = ‘C:\KeyBkup\Cert4TDE.cer’

WITH PRIVATE KEY

(

FILE = ‘C:\KeyBkup\TDEPvtKey.pvk’,

ENCRYPTION BY PASSWORD = ‘*DBA$@r3C00l’

);

GO

4.Create a database encryption key (DEK) encrypted with the certificate created.

USE TDEtest

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM   = AES_256 –AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY

ENCRYPTION BY SERVER CERTIFICATE TDECert

GO

USE master

GO

5.Enable TDE for the database.

ALTER DATABASE TDEtest SET ENCRYPTION ON

GO

6.Use this query to monitor encryption progress.

SELECT db_name(database_id), encryption_state, percent_complete, key_algorithm, key_length

FROM sys.dm_database_encryption_keys

GO

Simple test of performance

So, let’s take a quick look at a how query performance is impacted by TDE with a very simple test. Most shops avoid encrypting data because of fear of how it will impact performance and code. My test server only has 2 processors and the database is on a small RAID5 LUN with no other activity going on. I created a database named TDEtest with a few small tables with indexes and before actually implementing TDE I ran the following query every few seconds and captured perfmon and SQL stats:

SELECT c.companyname, c.contactname, c.address, c.city, c.country,

o.orderdate, o.requireddate, o.shipaddress, o.shipcity, o.shipcountry

FROM Orders o

JOIN Customers c ON (o.custid = c.custid)

WHERE o.shipcountry = ‘USA’

Before TDE is enabled, CPU utilization averaged 22% utilization for the duration of the test. Query elapsed time was about 48 ms.

Blog_20150625_1

After TDE is enabled using the steps above and ran the same query as before. CPU utilization averaged 28% utilization for the duration of the test. Query elapsed time was about 253 ms.

Blog_20150625_2

So, you can see that there is an increase in CPU utilization and queries might run longer. The good thing is that I did not have to change anything about my query to make it successfully run. On a system that has more processor power and better disks than my small test server, you very well may not notice a large uptick in CPU utilization or query duration but everyone’s mileage will vary in this. I would advise to carefully test performance if you are going to implement TDE encryption.

Restoring a TDE encrypted database

Now that I have TDE enabled, let’s look at how it protects the database from being restored to another SQL instance without the certificate. I took a backup of my TDEtest database and moved the backup file to another server. Since I know I will need them I also moved the backups of the certificate and private key as well.

Blog_20150625_3

First I tried restoring without creating a master key or restoring the certificate. It fails with this message:

Blog_20150625_4

It can’t find the certificate so I can’t restore the database. Here’s how to do this right….

1.On the second server, create a database master key if you don’t already have one. If you already have one on the second server don’t do this step

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = ‘@n0th3rMKPassw0rd’;

GO

2.Restore the certificate with the private key.

CREATE CERTIFICATE TDECert

FROM FILE = N’V:\SQLBackup\Cert4TDE.cer’

WITH PRIVATE KEY (

FILE = N’V:\SQLBackup\TDEPvtKey.pvk’,

DECRYPTION BY PASSWORD = ‘*DBA$@r3C00l’

);

GO

3.Restore the database.

Blog_20150625_5

Cleanup

And finally, here are the steps to disable TDE and cleanup everything if you ever need to.

1.Disable TDE.

USE master

GO

ALTER DATABASE TDEtest SET ENCRYPTION OFF

GO

2.Drop the database encryption key.

USE TDEtest

GO

DROP DATABASE ENCRYPTION KEY

GO

3.Get rid of the certificate and master key.

USE master

GO

DROP CERTIFICATE TDECert

DROP MASTER KEY

GO

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRxSupport@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!

Enhanced ColumnStore Indexes in SQL 2014

-by Lori Brown @SQLSupahStah

Available in SQL 2012 & 2014 Enterprise Edition

ColumnStore Indexes were introduced in SQL 2012 and are designed to improve performance in data warehouses. These indexes are good for batch processing and read only queries that use large data sets. Tables that are bulk loaded are good candidates. ColumnStore Indexes stores data in a columnar fashion and uses compression to reduce the amount of IO on disks when used. Each column of data is separated into its own segment. The segments are organized into a row group, which can contain over one million rows. If a table exceeds that amount, SQL will create more row groups and will break the column segments across them. The tuple mover checks every 5 minutes and performs the move if conditions are right.

Blog_20150611_1

When saving the index to disk, SQL Server compresses the data in the segments. As a result ColumnStore Indexes can achieve higher compression rates than traditional indexes. Higher compression rates = better performance, since compressed data is a smaller and faster in-memory. According to MS documentation: “Use the ColumnStore Indexes to achieve up to 10x query performance gains over traditional row-oriented storage, and up to 7x data compression over the uncompressed data size.”

As always, your mileage may vary based on values in each column. And keep in mind that more data held in memory also reduces disk IO. So, make sure you have plenty of memory…add if needed. The advantages can be mitigated if you have queries that seek specific data or is updated a lot.

Below is a list of limitations of ColumnStore Indexes. I have highlighted in green how it has been enhanced:

Limitations:

  • A table with a ColumnStore Index is now updatable – In the past had to drop and rebuild the index or swap partitions to update
  • ColumnStore index creation takes longer than creating a traditional B-tree index (on same columns) due to compression
  • Can have only one ColumnStore Index per table so think about including all columns or all frequently used columns
  • Cannot have more than 1024 columns
  • Can be non-clustered and now expanded to support clustered indexes
  • Cannot specify ASC/DESC or INCLUDE clauses
  • Expanded to all data types except ntext, text, image, rowversion, sql_variant, (n)varchar(max), varbinary(max), XML, Spatial, CLR
  • The definition of a ColumnStore Index cannot be changed with the ALTER INDEX command, must drop and create the index or disable it then rebuild it.  Can use ALTER INDEX command to disable and rebuild.
  • Can be created on a table which has compression enabled, but cannot be combined with Page and row compression, and vardecimal storage format
  • Cannot be created on view or an indexed view
  • Cannot include a sparse column
  • Cannot be created on table that uses features: Replication, Change Tracking, Change Data Capture and Filestream

How to create a basic ColumnStore Index:

To create a ColumnStore Index go to Database – tables – indexes – new index – select your type of columnstore index you want to create.

Blog_20150611_2

In the new index dialog add your index name and the columns you want.

Blog_20150611_3

Or, in TSQL, here is code to do the same:

CREATE TABLE RunningRoutes

(RouteID [int] NOT NULL,

RaceKey [int] NOT NULL,

RaceDateKey [int] NOT NULL,

MileageKey [int] NOT NULL)

GO

CREATE CLUSTERED INDEX cl_simple ON RunningRoutes (RouteID)

GO

CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_runningroutes

ON RunningRoutes

(RaceKey, RaceDateKey, MileageKey)

GO

*************************************************** 

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRxSupport@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!

SQL 2014 Statistics Enhancement – Incremental Statistics

— By Lori Brown   @SQLSupahStah

Available in SQL 2014 Enterprise Edition since only used for partitioned indexes

Statistics are VERY important to the SQL database engine and are what helps the optimizer make good execution plans. So, maintaining statistics is a super important part of any DBA’s job. In the past updating statistics on large tables can be very resource consuming and many times ended up with an entire large table being scanned or not enough data scanned if using sp_updatestats.

With the addition of the INCREMENTAL option statistics on large partitioned indexes, statistics maintenance can be managed in a more deliberate way. The INCREMENTAL option allows statistics creation per partition by setting the INCREMENTAL option to ON in the CREATE STATISTICS statement. So, when we update stats we can now tell SQL what partition to update meaning that we can maintain the partition that is most rapidly changing without having to scan data that does not change.

— Create a partition function and scheme

CREATE PARTITION FUNCTION RouteDtRangePF1(DATETIME)

AS RANGE RIGHT FOR VALUES (‘20130101’, ‘20140101’, ‘20150101’)

GO

CREATE PARTITION SCHEME RouteDtRangePS1 AS PARTITION RouteDtRangePF1 TO

([PRIMARY], [PRIMARY], [PRIMARY])

GO

CREATE TABLE RunningRoutes

(RouteID [int] NOT NULL,

RaceKey [int] NOT NULL,

RaceDate [DATETIME] NOT NULL,

MileageKey [int] NOT NULL)

ON  RouteDtRangePS1(RaceDate)

GO

— Create stats on a column with INCREMENTAL = ON

CREATE STATISTICS rrincrstats ON dbo.RunningRoutes(RaceDate)

WITH FULLSCAN, INCREMENTAL = ON

GO

— Update stats on specific partitions

UPDATE STATISTICS RunningRoutes(rrincrstats)

WITH RESAMPLE ON PARTITIONS(2, 3)

GO

There are limitations. Incremental statistics is not supported in the following conditions:

  • Statistics created with indexes that are not partition-aligned with the base table.
  • Statistics created on AlwaysOn readable secondary databases.
  • Statistics created on read-only databases.
  • Statistics created on filtered indexes.
  • Statistics created on views.
  • Statistics created on internal tables.
  • Statistics created with spatial indexes or XML indexes.

https://msdn.microsoft.com/en-us/library/ms187348.aspx

Changes to System Views in SQL 2014

— By Lori Brown  @SQLSupahStah

There are two new system views and two system views that have new columns added in SQL 2014. Most notable is sys.databases since this is widely used.

  •   sys.xml_indexes – Has 3 new columns: xml_index_type, xml_index_type_description, and path_id.

https://msdn.microsoft.com/en-us/library/ms176003.aspx

  • sys.databases Has 3 new columns: is_auto_create_stats_incremental_on, is_query_store_on, and resource_pool_id.

https://msdn.microsoft.com/en-us/library/ms178534.aspx

  • sys.column_store_row_groups New view.  Provides clustered ColumnStore Index information on a per-segment basis to help determine which row groups have a high percentage of deleted rows and should be rebuilt.

https://msdn.microsoft.com/en-us/library/dn223749.aspx

  • sys.dm_exec_query_profiles – New view.  Monitors real time query progress while a query is in execution.

https://msdn.microsoft.com/en-us/library/dn223301.aspx

–Configure query for profiling with sys.dm_exec_query_profiles

SET STATISTICS PROFILE ON

GO

–Optionally return the final results of the query to SHOWPLAN XML

SET SHOWPLAN XML ON

GO

–Next, run a query in this session

SELECT *

FROM RunningRoutes rt

CROSS JOIN RoadRaces race

WHERE [State] = ‘TX’

 –Run this in a different session than the session in which your query is running.

–Serialize the requests and return the final results to SHOWPLAN XML

SELECT 

node_id,physical_operator_name, SUM(row_count) row_count, SUM(estimate_row_count) AS estimate_row_count,

CAST(SUM(row_count)*100 AS float)/SUM(estimate_row_count)

FROM sys.dm_exec_query_profiles

WHERE session_id=<<your session id>>

GROUP BY node_id,physical_operator_name

ORDER BY node_id