Monthly Archives: May 2017

AlwaysOn 2016 – Encrypted Databases in an AG

–By Ginger Keys

In the AlwaysOn 2016 release, there are several enhancements that have improved manageability, scalability, and availability. One of the enhancements is the ability to add encrypted databases to an AlwaysOn group.

Encrypted databases were allowed in earlier versions of AlwaysOn, however they could not be accessed if there was a failover to a secondary replica because the primary and secondary replicas had different service master keys. So when the secondary would try to decrypt the database master key using its own service master key, the master key would fail to open because of an invalid password.

In SQL 2016 there is now access to the database on the secondaries in the event of failover since it is no longer a requirement for each replica to have the same Database master Key (DMK) and password on each instance. There is no need to back up the DMK from the primary and restore it to the secondary(s), it is only necessary that a DMK exist on each replica. As long as each replica has a DMK created, even without the same password, there will not be any encryption/decryption issues because of different keys.

Transparent Data Encryption (TDE) is a method of protecting your data ‘at rest’. There are several methods of protecting your data in addition to TDE, but this article will only discuss TDE encrypted databases in an AlwaysOn Availability Group. If your physical media (drives or backup tapes) are stolen, TDE will encrypt the sensitive data in the data and log files, and will also protect the keys used to encrypt the data with a certificate. TDE provides for real-time I/O encryption and decryption, but it does not protect data ‘in-flight’ flowing from the server to the end user. Also it does not protect data from a user who has privileges in the SQL instance.

The steps below demonstrate how to add an encrypted database to an AlwaysOn Availability Group

  1. Encrypt your database (if not already encrypted)
  2. Create/verify Database Master Key (DMK) on secondary replica(s)
  3. Create the TDE Certificate on secondary replica(s)
  4. Perform Full and Tlog backup of encrypted database on primary replica
  5. Add encrypted database to AlwaysOn group on primary replica
  6. Restore Full and Tlog backups on secondary replica(s)
  7. Join the encrypted database to the AlwaysOn group on secondary replica(s)

Step 1 – Encrypt Database

If your database has not already been encrypted, follow these steps to create a master key (DMK), backup the certificate, create a database encryption key (DEK), and enable encryption on the database on your primary replica.

–create DMK database master key

USE master

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘DMKP@ssw0rd’

GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘DMKP@ssw0rd’

GO

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

ENCRYPTION BY PASSWORD = ‘G00dP@ssw0rd’

GO

 

–check to make sure master key is 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

 

–create and backup certificate

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

GO

BACKUP CERTIFICATE TDECert

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

WITH PRIVATE KEY

(

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

ENCRYPTION BY PASSWORD = ‘*DBA$RC00l’

);

GO

 

–create DEK database encryption key with certificate

USE GKTestDB

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

 

enable TDE for the database

ALTER DATABASE GKTestDB SET ENCRYPTION ON

GO

 

–monitor encryption progress

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

FROM sys.dm_database_encryption_keys

GO    

 

Step 2 – Verify Database Master Key on Secondary Replica(s)

On each secondary replica, verify that a Database Master Key (DMK) exists in the master database.

 

–check to make sure master key is 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

If a record is returned, that means a DMK exists on the secondary instance. If no record is returned, then create a one as follows:

–create DMK database master key

USE master

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘DMKP@ssw0rd2’

GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘DMKP@ssw0rd2’

GO

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

ENCRYPTION BY PASSWORD = ‘G00dP@ssw0rd’

GO

 

Step 3 – Create TDE Certificate on Secondary Replica(s)

On each secondary replica instance, create/restore the TDE Certificate from the server certificate and private key that you backed up on the primary. Copy the certificate as well as the private key from the primary over to a file path on the secondary(s). The following will install the certificate onto the secondary replica(s).

–create TDE Certificate from the certificate backed up on primary

CREATE CERTIFICATE TDECert

FROM FILE = ‘C:\SQLRX\KeyBkup\Cert4TDE.cer’

WITH PRIVATE KEY

(

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

DECRYPTION BY PASSWORD = ‘*DBA$RC00l’

)

Step 4 – Create Full & Tlog Backups on the Primary Replica

Create a full and tlog backup of the TDE encrypted database on the primary replica.

–create Full Backup of encrypted database on primary

BACKUP DATABASE [GKTestDB]

TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\GKTestDB.bak’

WITH NOFORMAT, NOINIT, NAME = N’GKTestDB-Full Database Backup’, SKIP, NOREWIND,NOUNLOAD, COMPRESSION, STATS = 10

GO

–create Tlog backup of encrypted database on primary

BACKUP LOG [GKTestDB]

TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\GKTestTlogBackup.trn’

WITH NOFORMAT, NOINIT, NAME = N’GKTestDB-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

Step 5 – Add Encrypted Database to AlwaysOn Group

On the primary replica instance, add the TDE encrypted database to the Availability Group. As of the current release of SQL Server 2016, you cannot do this yet through the wizard

Blog_20170525_1

Instead add the TDE database to your AlwaysOn group using TSQL:

USE master

GO

ALTER AVAILABILITY GROUP AGroup_SQLRx

ADD DATABASE GKTestDB

GO

Step 6 – Restore Full & Tlog Backups on Secondary Replica(s)

On each secondary replica instance, restore the full and tlog backups of the encrypted database with no recovery. Copy the backup files from the primary replica to a file location on your secondary and restore from that file path as follows:

–Restore Full backup of encrypted database on secondary with no recovery

USE [master]

RESTORE DATABASE [GKTestDB] FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\GKTestFullBackup.bak’ WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 5

GO

–Restore the Tlog backup of encrypted database on secondary with no recovery

RESTORE LOG [GKTestDB] FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\GKTestTlogBackup.trn’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5

GO

Step 7 – Join the Encrypted Database to AlwaysOn Group on Secondary Replica

On each secondary replica instance, join the database to the availability group.

USE master

GO

ALTER DATABASE GKTestDB

SET HADR AVAILABILITY GROUP = AGroup_SQLRx

GO

Blog_20170525_2

Conclusion

After joining the encrypted database to the AlwaysOn group, you should now be able to fail over the group to the secondary replica and be able to access the database without any issues. It is common knowledge that encrypting a database causes degraded performance, so the next article will demonstrate the performance effects of having an encrypted database in your AlwaysOn Group.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

 

SQL 2017 Is On The Way!!

–By Lori Brown  @SQLSupahStah

SQL Server 2017 is rolling our way like a big ole train. And, with the new features that are going to be available there may be quite a few shops that want to jump onboard.

Blog_20170519_1

As of this writing SQL Server 2017 CTP 2.1 is available for download and testing. (https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2017-ctp/ ) I managed to recently attend a presentation by Denzil Ribeiro who is a manager with the SQLCAT team on some of the new features in SQL 2017. I tend to pay attention to the things that get the guys on the inside excited so here are some of the highlights with links that I could find and my notes on SQL 2017.

Resumable Online Index Rebuild

https://blogs.technet.microsoft.com/dataplatforminsider/2017/04/20/resumable-online-index-rebuild-is-in-public-preview-for-sql-server-2017-ctp-2-0/

Index rebuilds can be paused and restarted. Since index must be created with ONLINE = ON then it is likely that this is an Enterprise Edition feature.

ALTER INDEX IDX_MyInx ON SomeTable

REBUILD WITH (RESUMABLE = ON, ONLINE = ON, MAX_DURATION = 1)

— Pause

ALTER INDEX IDX_MyInx ON SomeTable PAUSE

Use sys.index_resumable_operations to view the status of resumable index rebuilds. https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-index-resumable-operations

Wait stats for per query executions

Available by default. Statistics info is now in query plans. Use sys.query_store_wait_stats to see wait info for a query plan. https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-wait-stats-transact-sql

Automatic Query Tuning

https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning

Recognizes plan regressions and will automatically force a good plan to be used. Database must be in 140 (SQL2017) compatibility. Must enable the Query Store for the database. Can then use sys.dm_db_tuning_recommendations (https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-tuning-recommendations-transact-sql ) to get info on plans and recommendations that were used to fix regressions. Won’t force a good plan unless AUTOMATIC_TUNING = ON.

ALTER DATABASE current

SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

Adaptive Query Processing

Interleaved Execution

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/04/19/introducing-interleaved-execution-for-multi-statement-table-valued-functions/

Available by default. Adjust plans by testing plan and then redesigning plan based on better row estimates. All while a query is executing.

Batch Mode Memory Grant Feedback

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/29/introducing-batch-mode-adaptive-memory-grant-feedback/

Adjust plan in cache if memory grant is not good enough (either too much or not enough).

Batch Mode Adaptive Joins

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/04/19/introducing-batch-mode-adaptive-joins/

After first join in a plan, better decision made to choose Hash Join or Nested Loop Join. If number of rows small then likely Nested Loop Join will be used

Graph Data Processing

https://blogs.technet.microsoft.com/dataplatforminsider/2017/04/20/graph-data-processing-with-sql-server-2017/

Nodes and edges stored as tables. Many – many modeling. New MATCH clause in queries. https://docs.microsoft.com/en-us/sql/t-sql/statements/match-sql-graph

Blog_20170519_2

SELECT Person2.Name

FROM Person Person1, Friends, Person Person2

WHERE MATCH(Person1 – (Friends) -> Person2)

AND Person1.Name = ‘John’;

Python

https://blogs.technet.microsoft.com/dataplatforminsider/2017/04/19/python-in-sql-server-2017-enhanced-in-database-machine-learning/

Python is now integrated into SQL just like R Services and is used for advanced analytics. I have to admit that Python is way beyond my skillset right now but wanted to mention it.

Conclusion

A lot of these new features for SQL 2017 are evolving and more will come out I am sure. I am in the process of getting a VM with Linux installed on it so that I can check out installing and running SQL Server on Linux so expect a post on that soon.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

AlwaysOn 2016 – Direct Seeding

–By Ginger Keys

SQL Server 2016 provides some awesome new enhancements and features, especially in Enterprise Edition.  One of the new features relating to AlwaysOn is Direct Seeding.

Good stuff – No more time consuming backup and restore

  • Direct Seeding allows you to initialize the databases to your secondary replica(s) without having to configure a network share or perform backup and restore operations.  It will send the database directly over to the secondary replica without you needing to do anything.  Woo hoo!!

Gotchas – Some with regards to Large Databases

  • Direct seeding does not use compression by default, so adding a very large database to your AlwaysOn group could take a while and can use a lot of your network bandwidth.  Compression can be enabled by turning on Trace Flag 9567.   However, know that adding a large database with compression enabled will cause high CPU utilization during the seeding process.   For more info click here https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/tune-compression-for-availability-group .
  • Also during direct seeding the transaction log will not truncate, so in a highly transactional database your transaction log could potentially grow very large during this time.
  • For large databases being initialized to secondary replicas using direct seeding, be mindful of your network capacity (as the entire database will be sent across the network to the secondary(s)), workload amount which could bloat your transaction log, CPU pressure if you enable compression, and database size which will affect the seeding time.

You may want to use direct seeding during afterhours to prevent your Network or System Admin from hunting you down for the network hit that will happen.

If you are using SQL Server Management Studio version 17, Direct Seeding of your databases is now available in the GUI! You can download this version of SSMS here https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms .

Let’s Do This!

I will assume you already have your environment ready (i.e. failover cluster created and configured, SQL installed and configured, AlwaysOn enabled, replicas and logins configured, etc.). So from the New Availability Group Wizard, you will specify your AG name:

Blog_20170511_1

Just a side note… in earlier versions of AlwaysOn (SQL 2012 & 2014), failover would occur if there was a problem with the health of the instance. If one of your databases was in trouble however, the AlwaysOn group would not fail over as long as the instance was okay. So if you had a database that was offline, suspect or corrupt, no failover would occur. In AlwaysOn 2016, failover will occur not only if the instance is in trouble, but also if one or more of your databases is in trouble. This is not a default setting however. You have to specify this when creating your AlwaysOn group, by selecting the ‘Database Level Health Detection’ checkbox in the setup wizard.

Next, select your database(s) to add to the AlwaysOn group:

Blog_20170511_2

Then add the replicas to be included in your AG and what type of failover (automatic or manual), data replication (synchronous or asynchronous), and whether they will allow for read only access:

Blog_20170511_3

Next click on the Endpoints tab and make sure URL and port numbers are correct:

Blog_20170511_4

Click on the Backup Preferences tab and specify how you want your backups to occur:

Blog_20170511_5

Click on the Listener tab and specify the Listener (DNS) name, port number and IP address:

Blog_20170511_6

And THIS is where you can specify Automatic (Direct) Seeding!

Blog_20170511_7

Click Next through the next screens and THAT’S IT!

Blog_20170511_8

You’re done, simple as that.

Blog_20170511_9

If you do not have SSMS v17 you can still initialize your databases to secondary replicas using direct seeding, but you will need to do this through TSQL from the primary replica:

CREATE AVAILABILITY GROUP []

FOR DATABASE db1

REPLICA ON ‘<*primary_server*>’

WITH (ENDPOINT_URL = N’TCP://<*primary_server*>.<*fully_qualified_domain_name*>:5022′,

FAILOVER_MODE = AUTOMATIC,

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

BACKUP_PRIORITY = 50,

SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),

SEEDING_MODE = AUTOMATIC),

N'<*secondary_server*>’ WITH (ENDPOINT_URL = N’TCP://<*secondary_server*>.<*fully_qualified_domain_name*>:5022′,

FAILOVER_MODE = AUTOMATIC,

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

BACKUP_PRIORITY = 50,

SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),

SEEDING_MODE = AUTOMATIC);

GO

For more information click here https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/automatically-initialize-always-on-availability-group .

Conclusion

SQL Server 2016 AlwaysOn gives you the ability to join databases to your AlwaysOn group and initialize them on your secondaries without having to backup and restore, or establish a network share. This ultimately saves much time and effort, but can take a while if your database is large. And using compression during the seeding process can save some time, but your CPU will take a performance hit. Also keep in mind that your transaction log will grow during this time as it will not truncate during the seeding process. Of course, be sure to test this first before doing it in your production environment!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

List Partitioned Tables And Other Info About Them

— By Lori Brown @SQLSupahStah

Here is a good way to find out if any of your tables are partitioned in your database using the query below. It is important to know so that you can find out how the tables are partitioned.

— Partitioned Tables

SELECT DISTINCT t.name AS ParitionedTables

FROM sys.partitions p

INNER JOIN sys.tables t

ON p.object_id = t.object_id

WHERE p.partition_number <> 1

If you have partitioned tables here is a good way to find out how it is partitioned up.

— Get partition info

SELECT SCHEMA_NAME(o.schema_id) + ‘.’ + OBJECT_NAME(i.object_id) AS [object]

, p.partition_number AS [p#]

, fg.name AS [filegroup]

, p.rows

, au.total_pages AS pages

, CASE boundary_value_on_right

WHEN 1 THEN ‘less than’

ELSE ‘less than or equal to’ END AS comparison

, rv.value

, CONVERT (VARCHAR(6), CONVERT (INT, SUBSTRING (au.first_page, 6, 1) +

SUBSTRING (au.first_page, 5, 1))) + ‘:’ + CONVERT (VARCHAR(20),

CONVERT (INT, SUBSTRING (au.first_page, 4, 1) +

SUBSTRING (au.first_page, 3, 1) + SUBSTRING (au.first_page, 2, 1) +

SUBSTRING (au.first_page, 1, 1))) AS first_page

FROM sys.partitions p

INNER JOIN sys.indexes i

ON p.object_id = i.object_id

AND p.index_id = i.index_id

INNER JOIN sys.objects o

ON p.object_id = o.object_id

INNER JOIN sys.system_internals_allocation_units au

ON p.partition_id = au.container_id

INNER JOIN sys.partition_schemes ps

ON ps.data_space_id = i.data_space_id

INNER JOIN sys.partition_functions f

ON f.function_id = ps.function_id

INNER JOIN sys.destination_data_spaces dds

ON dds.partition_scheme_id = ps.data_space_id

AND dds.destination_id = p.partition_number

INNER JOIN sys.filegroups fg

ON dds.data_space_id = fg.data_space_id

LEFT OUTER JOIN sys.partition_range_values rv

ON f.function_id = rv.function_id

AND p.partition_number = rv.boundary_id

WHERE i.index_id < 2

AND o.object_id = OBJECT_ID(‘dbo.SomeTableName’);