Tag Archives: Restore

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 Server Backup and Restore Primer

— by Lori Brown @SQLSupahStah

Back it up!!

blog_20170302_1  …. BEEP! BEEP! BEEP! … Oh wait! Not that kind of backup…

Since I spent a lot of time trying to creatively move a database from one server to another last night, I thought that a review of ways to backup and restore would be a good topic for today.

I had someone who needed me to move a database from one server to another in an effort to consolidate servers. The database backup file was a significant size and could not fit onto any of the local drives of the new server. And to make things more fun, the SQL Server service account did not have permission to view the old server at all. I think the inability to see the old server had something to do with the old server had been in a different domain at one point and there was some issue that was never fixed that could give the service account access. Nonetheless, the move had to be completed. So, this made it so that restoring would have to be performed using a location that the SQL Server service account could access. I could not use a UNC path for restoring the database.

The new server was really tight on space and the admins were very reluctant to add more space if that space was not needed permanently. I had several drives with differing amounts of free space so if I could create enough small backup files, I could put those files in several different places on the new server and could then restore since the SQL Server service account did have access to all local drives.

You can backup databases to one file or multiple files. Here are the basics of backing up to a single file. Since I am giving the T-SQL way to do things, I am also including a way to make the file name unique with a timestamp.

— Backup a database to a single backup file

DECLARE @BackupFile NVARCHAR(200)

DECLARE @Timestamp VARCHAR(50)

— create a timestamp for a unique name

SET @Timestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(), 20),‘-‘,),‘:’,),‘ ‘,)

— create backup file string

SET @BackupFile = ‘D:\SQLBackups\WideWorldImporters_Full_’+@Timestamp+‘.bak’

— backup the database

BACKUP DATABASE WideWorldImporters

TO DISK = @BackupFile

WITH NOFORMAT, INIT, NAME = N’WideWorldImporters-Full Database Backup’,

SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10;

GO

Backing up to multiple files is good to use for large databases and will break up a single large backup file to several smaller files. This is what I had to do last night when a large single database file would not fit on any local drive.

— Backup a database to multiple backup files

DECLARE @BackupFile1 NVARCHAR(200)

DECLARE @BackupFile2 NVARCHAR(200)

DECLARE @BackupFile3 NVARCHAR(200)

DECLARE @BackupFile4 NVARCHAR(200)

DECLARE @Timestamp VARCHAR(50)

— create a timestamp for a unique name

SET @Timestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(), 20),‘-‘,),‘:’,),‘ ‘,)

— create backup file strings

SET @BackupFile1 = ‘D:\SQLBackups\WideWorldImporters_Full1_’+@Timestamp+‘.bak’

SET @BackupFile2 = ‘D:\SQLBackups\WideWorldImporters_Full2_’+@Timestamp+‘.bak’

SET @BackupFile3 = ‘D:\SQLBackups\WideWorldImporters_Full3_’+@Timestamp+‘.bak’

SET @BackupFile4 = ‘D:\SQLBackups\WideWorldImporters_Full4_’+@Timestamp+‘.bak’

— backup the database

BACKUP DATABASE WideWorldImporters

TO DISK = @BackupFile1,

DISK = @BackupFile2,

DISK = @BackupFile3,

DISK = @BackupFile4

WITH NOFORMAT, INIT, NAME = N’WideWorldImporters-Full Database Backup’,

             SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10;

GO

Here is the MSDN link for all things backup…. https://msdn.microsoft.com/en-us/library/ms186865.aspx

Some backup options that are good to know about are BLOCKSIZE, BUFFERCOUNT and MAXTRANSFERSIZE. These can help speed up backups immensely but should be thoroughly tested. I recently had a 1.8 TB database that without setting those backup options, caused the database backup to take around 4 hours. With them set, the backup time was reduced to 50 minutes.

Restore it!!

blog_20170302_2  … Reunited and it feels so goooood… Yeah, no one wants to hear me sing.  Somehow I never win at karaoke.

Practicing restoring databases proves that the backup files are usable and will polish up your skills so that you are ready to restore in the event of a crisis. If you have to restore database files to different drives than are available on the production server or if you have to restore often as in the case of refreshing a database, create a script that will be easier to use than restoring using the GUI and navigating to the new file locations. Don’t forget that databases from newer version of SQL cannot be restored to an older version.

Here are some basic examples of restore statements.

— Restore a database from a single backup file

USE master

GO

RESTORE DATABASE WideWorldImporters

FROM DISK = N’D:\SQLBackups\WideWorldImporters_Full.bak’

WITH RECOVERY

GO

 

— Restore a database from a single database backup file

— and a transaction log backup file

USE master

GO

RESTORE DATABASE WideWorldImporters

FROM DISK = N’D:\SQLBackups\WideWorldImporters_Full.bak’

WITH NORECOVERY — NORECOVERY allows more backup files to be restored

GO

RESTORE LOG WideWorldImporters

FROM DISK = N’D:\SQLBackups\WideWorldImporters_tlog.trn’

WITH RECOVERY

GO

In my case, since I had backed up the database to multiple files, I moved those files to several different local drives and was able to restore from there. I don’t like it when space is that tight but some shops run a very tight ship and it is just something I have to live with.

— Restore a database from multiple backup files

USE master

GO

RESTORE DATABASE WideWorldImporters

FROM DISK = N’D:\SQLBackups\WideWorldImporters_Full_1.bak’,

DISK = N’D:\SQLBackups\WideWorldImporters_Full_2.bak’,

DISK = N’D:\SQLBackups\WideWorldImporters_Full_3.bak’,

DISK = N’D:\SQLBackups\WideWorldImporters_Full_4.bak’

WITH REPLACE, — REPLACE WILL OVERWRITE EXISTING FILES

RECOVERY

GO

 

— Restore a database from multiple backup files

— and move the database and log files to new drives

USE master

GO

RESTORE DATABASE WideWorldImporters

FROM DISK = N’D:\SQLBackups\WideWorldImporters_Full_1.bak’,

DISK = N’D:\SQLBackups\WideWorldImporters_Full_2.bak’,

DISK = N’D:\SQLBackups\WideWorldImporters_Full_3.bak’,

DISK = N’D:\SQLBackups\WideWorldImporters_Full_4.bak’

WITH MOVE N’WideWorldImporters’ TO N’H:\MSSQL\WideWorldImporters.mdf’,

MOVE N’WideWorldImporters_log’ TO N’L:\MSSQL\WideWorldImporters_log.ldf’,

RECOVERY

GO

Restore WITH RECOVERY to make the database ready to use. Restore WITH NORECOVERY if you want to restore more backups.   Use REPLACE only when you are certain that you want to overwrite the existing files so be careful with it.

More on RESTORE can be found at https://msdn.microsoft.com/en-us/library/ms186858.aspx

Some good things to know are available in the RESTORE are the ability to restore pages, verifying backups, NORECOVERY and STANDBY, and STOPAT. Some of those options are specific when restoring transaction logs but are good to know anyway.

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!