Feeds:
Posts
Comments

Archive for the ‘Backup’ Category

With the release of Microsoft SQL Server 2014, we have the first version of SQL Server that supports encrypting database backups directly from the database engine without any third party software being installed on the SQL Server.

Preparing the Instance for Encrypted Backups

Before you can have the SQL Server database engine encrypt your backups, you have some basic setup which needs to be done. The first thing to check is that the master database has a master key in the database. Odds are that there is already a master key within the master database, as SQL Server will put one there by default when SQL Server is installed. Whether a master key has been installed can be verified by querying the master.sys.symmetric_keys catalog view and looking for a key named ##MS_DatabaseMasterKey##. If the row exists, then there is nothing else which needs to be done. If the row does not exist, then create a master key within the master database by using the CREATE MASTER KEY command as shown in Listing 1.

CREATE MASTER KEY ENCRYPTION 
BY PASSWORD='MyPa$$w0rd'

The nest preparation item which needs to be done is that a certificate or an asymmetric key must be created within the master database. If an asymmetric key is going to be used (not shown in this article), then the asymmetric key must be protected through an EKM (Extended Key Management).

If there is already a certificate within the master database, this certificate can be used, provided that it has not expired. You can see the list of certificates which exist within the master database by querying the sys.certificates catalog view, with the expiration date shown in the expiry_date column.

If there is no certificate which is usable, then a certificate can be created using the CREATE CERTIFICATE command as shown in Listing 2.

CREATE CERTIFICATE MyBackupCert 
WITH SUBJECT='Backup Encryption Certificate'

If using a certificate, the certificate must be backed up and the backup placed in a safe location. This backup will be needed if the server needs to be rebuild and the backup restored, or if you wish to restore the database to another server. Backing up the certificate is done via the BACKUP CERTIFICATE statement as shown in Listing 3.

BACKUP CERTIFICATE MyBackupCert 
TO FILE='c:\backup\MyBackupCert.crt'

Backing up the database using SQL Server Management Studio (SSMS) setup requirements, are the same as when using T-SQL. Using encryption with the GUI requires that you set the database to backup to a new media set on the “Media Options” page as shown in Figure 1.

Figure 1: Set the database to backup to a new media set under "Media Options"

Figure 1: Set the database to backup to a new media set under “Media Options”

The encryption options are available on the “Backup Options” page of the database backup screen. You can enable the backup encryption by checking the “Encrypt backup” check box as shown in Figure 2. You can then set the Algorithm and the certificate or asymmetric key that will be used from the two dropdowns which are shown in Figure 2.

Figure 2: Enable the backup encryption by checking "Encrypt backup"

Figure 2: Enable the backup encryption by checking “Encrypt backup”

Backing up the Database

Once the certificate or asymmetric key has been created, the database can be backed up using the certificate or asymmetric key, to secure the backup. When backing up the database you can select from four different encryption keys. These are shown below, in order of strength:

  • DES
  • AES 128
  • AES 192
  • AES 256

The stronger the encryption that is used, the more CPU power required to encrypt the data, and the more CPU power required for someone to attempt to break the encryption. With the CPU power available in modern SQL Servers and the amount of CPU power available to people who may attempt to break the encryption, selecting AES 256 is strongly encouraged.

When using backup encryption with SQL Server backups, each backup must be written to a new media set. Essentially, this means that the backup must be written to a new backup file each time a backup is taken.

Encrypting a database when it is being backed up is very similar to a traditional SQL Server backup. The normal BACKUP DATABASE (or BACKUP LOG) command is used, simply supplying the WITH ENCRYPTION parameter as shown in Listing 4.

BACKUP DATABASE [AdventureWorks2008R2] 
TO DISK = N'C:\Backup\AdventureWorks2008R2.bak'
WITH FORMAT, INIT, NAME = N'AdventureWorks2008R2-Full DB Backup',
ENCRYPTION(ALGORITHM = AES_128, SERVER CERTIFICATE = [NewCert])

Restoring an Encrypted Database

Restoring an encrypted database is just as simple as restoring an unencrypted database. The same RESTORE DATABASE command is used, as normal. The only requirement is that the certificate, or asymmetric key, that was used to encrypt the backup, must exist on the instance before attempting to restore the database. If the certificate or asymmetric key does not exist on the server, then the database will not be restored. If the certificate or asymmetric key has been lost, then there is no way to restore the database from its backup.

If the certificate or asymmetric key is not already on the server, you’ll not be able to use the RESTORE FILELISTONLY or RESTORE HEADERONLY commands to view the metadata about the backup, either, as the entire backup file is encrypted.

Restoring the database from an expired certificate is done just like normal, provided that the certificate already exists on the server. If the certificate has expired, but does not exist on the SQL Server instance, the certificate can still be created from the backup file. The creation of the certificate from the backup file where the certificate is expired returns a warning, but it will import correctly.

Instance Limitations

Database backup encryption is a Standard Edition feature, meaning that it’s available on the Standard and Enterprise editions of Microsoft SQL Server 2014. While you can only backup with Standard or Enterprise Edition, an encrypted backup can be restored to any edition of Microsoft SQL Server 2014, including Express and Web editions. The only limitation is that the database must be under 10 GB in size in order to be restored to an Express Edition instance.

Summary

When used properly, database backup encryption is a great way to protect database backups from being viewed by unauthorized people. SQL Server database backup encryption is easy to setup and simple to use requiring only a master key within the master database and either a certificate or asymmetric key. However, if the certificate is lost, then the database backup becomes useless to you as you’ll have no way to restore the database from the database backup file.

 

Advertisements

Read Full Post »

In this article I will show you how to restore a database that is encrypted with Transparent Data Encryption (TDE) on to a different server. Let’s use the same database ‘TDE_Test’ that we have created in the previous post HERE

Restoring a database to a different SQL Instance is usually a straightforward task. However, this attempt will return an error as shown below for an encrypted database when restoring into a different instance.

USE [master]
RESTORE DATABASE [TDE_Test_restore] FROM  
DISK = N'C:\Backup\TDE_Test_withtde.bak' 
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

Output:

Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint..
Msg 3013, Level 16, State 3, Line 2
RESTORE DATABASE is terminating abnormally

To restore successfully, we will need to physically copy the certificate (.cer) and private key (.pvk) to the destination server. As a best practice, we should immediately back up the certificate and the private key when we enable TDE. However, we can still take backup the certificate and private key now in the source server as shown below if not done earlier.

USE master; 
GO 
BACKUP CERTIFICATE TDECert
TO FILE = 'E:\Backup\certificate_TDE_Test_Certificate.cer'
WITH PRIVATE KEY
(FILE = 'E:\Backup\certificate_TDE_Test_Key.pvk',
ENCRYPTION BY PASSWORD = 'Password12#')

Create a Master Key in destination server.

The password provided here is different from the one we used in the source server since we are creating a new master key for this server.

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'D1ffPa$$w0rd'

After a master key has been created, create a certificate by importing the certificate we created earlier. Here the ‘Decryption By Password’ parameter is same as that provided to export the certificate to a file.

CREATE CERTIFICATE TDECert2
FROM FILE = 'E:\cert_Backups\ certificate_TDE_Test_Certificate.cer'     
WITH PRIVATE KEY (FILE = 'E:\cert_Backups\certificate_TDE_Test_Key.pvk', 
DECRYPTION BY PASSWORD = 'Password12#')

Restore Database in destination server

We will now be able to restore the encrypted database backup successfully.

USE [master]
RESTORE DATABASE [TDE_Test] FROM  DISK = N'F:\Backup\TDE_Test_withtde.bak' 
WITH  FILE = 1, NOUNLOAD,  REPLACE,  STATS = 5

Listed are some of the advantages and disadvantages of using TDE.

Advantages

  • The performance impact of TDE is minor. Estimated to be around 3-5%
  • Performs real-time I/O encryption and decryption of the data and log files
  • Encrypts the Entire Database in rest
  • No architectural changes needed
  • No application code changes are required and the user experience is the same
  • Easy to implement
  • DBAs can still see the data

Disadvantages

  • No protection for data in memory
  • Not granular – Cannot just encrypt specific tables/columns
  • Not good for high CPU bottleneck servers
  • Not protected through communication/networks

Read Full Post »

Being a part of the database engineering team, I was given a task to setup disaster recovery program for some of our SQL Servers hosting critical applications. After a few discussions, I planned to make use of log shipping to sync data between the two data centers like most DR sites work. This requires us to copy the backup files to the Disaster Recovery (DR) site for the initial setup. But in a little while, I realized moving 30 TB of data over network is a Herculean task considering our extra large databases at an approximate 12-15 mbps over network. This might not be a feasible option with 45 days of wait time along with something that chews up all our bandwidth. So we rather decided to ship the hard drives in a truck to the DR site which is just a few hundreds miles away.

Everything was good until one of the IT Directors who overheard this while he was passing by DBAs  was curious and asked if it isn’t a security risk if we lose our disks during the transit?

I jumped in and replied him with a smile, “Not when we encrypt our data with Transparent Data Encryption”

Lets see what Transparent Data Encryption is?

Transparent Data Encryption is a new feature in SQL Server 2008 which allows the encryption of the entire database while providing real time encryption of data files (.mdf) and log files (.ldf). Apart from this, TDE also encrypts any backups taken while the encryption is enabled. Data is encrypted before it is written to disk and data is decrypted when it is read from disk as it is read into the memory. This will help in preventing unauthorized access to the data and backup files.

Demo

I will show you an example of how anyone can read the backup files even without restoring the backup into another database.

I have created a database called TDE_Test that contains address details as shown below.(This database basically contains a few tables imported from the AdventureWorks2012 sample database. For downloading and installing AdventureWorks2012, refer HERE)

1

Figure 1: Result set showing Address data

Let’s backup the ‘TDE_Test’ database

BACKUP DATABASE [TDE_Test] 
TO DISK = 'E:\Backup\TDE_Test__Backup.bak' 
WITH INIT, STATS = 5

Open the backup file using Notepad and search for address ‘‘5 4 4   M a g d a   W a y’, as shown in Figure 2 (note the spaces between the letters and three spaces between each word as this is Unicode text)

We can easily see that the data in backup file is readable. This means anyone with the backup file can actually read your data with little skill, even without restoring it. 

2

Figure 2: Backup file opened in Notepad – Un-encrypted format

Implementing Transparent Data Encryption

TDE setup includes the below four basic steps.

  1. Creating a Master Key
  2. Creating a Certificate Protected by the Master Key
  3. Creating a Database Encryption Key
  4. Turning on the TDE

1. Creating a master key 

It is a good practice to backup the database before proceeding with the encryption. So in case we want to reverse the implementation of TDE we have a latest copy of database to bring back.

The master key must be created within the master database

USE MASTER; 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password12#'
GO

2. Creating a Certificate Protected by the Master Key

The certificate is used to protect the database encryption key that we will create next. In addition, this certificate is protected by the master key created in the previous step.

USE MASTER; 
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate'
GO

3. Creating a Database Encryption Key

Create Database Encryption Key inside the database to be encrypted, and protect it with the certificate. You can choose from one of the following encryption algorithms: AES_128, AES_192, AES_256, or TRIPLE_DES_3KEY. Details on the encryption algorithms is beyond the scope of this article. Check out this Microsoft Technet article for guidance on choosing the encryption algorithm

USE TDE_Test  

GO 
CREATE DATABASE ENCRYPTION KEY WITH 
ALGORITHM = AES_256 ENCRYPTION BY 
SERVER CERTIFICATE TDECert ; 
GO

I have also added an additional script below to backup the private key and certificate to disk drive. Since TDE also encrypts the backup files, it is always required to maintain a backup of private key and certificate along with the database backup to use at the time of restoring.

USE master; 
GO 
BACKUP CERTIFICATE TDECert 
TO FILE = 'E:\Backup\certificate_TDE_Test_Certificate.cer'
WITH PRIVATE KEY
(FILE = 'E:\Backup\certificate_TDE_Test_Key.pvk',
ENCRYPTION BY PASSWORD = 'Password12#')

4. Turning TDE ON

Since encryption is a resource intensive process it is always a best practice to turn on TDE during off-business hours or when the server is least busy.

USE  TDE_Test
ALTER DATABASE  TDE_Test
SET ENCRYPTION ON ; 
GO

We can check the progress of Encryption using the below script from utilizing ‘sys.dm_database_encryption_keys’ DMV. A value of ‘2’ for ‘encryption_state’ indicates that encryption has begun and ‘3’ for completed

SELECT
db.name,
db.is_encrypted,
dm.encryption_state,
dm.percent_complete,
dm.key_algorithm
FROM sys.databases db JOIN sys.dm_database_encryption_keys dm
ON db.database_id = dm.database_id WHERE db.name = 'TDE_TEST'

Let’s take a backup of our encrypted database now and search for address as mentioned earlier.

BACKUP DATABASE [TDE_Test] 
TO DISK = 'E:\Backup\TDE_Test_withtde.bak' 
WITH INIT, STATS= 5

4
Figure 3: Backup file opened in Notepad – Encrypted format

Whoopee! Our data is now encrypted and secured 🙂

This way, we can make sure the data is secured as well protected from any unauthorized access.

As a continuation to this topic,  I have explained how to restore a TDE Encrypted database backup HERE

Technical Reviewer(s): Venkata Suresh Raavi; Jaipal Vajrala

Read Full Post »

Even though there are more pros than cons, Striping database backups are often overlooked by many DBAs. Based on my observations in our environment, striping can significantly benefit larger database backups (~500+ GB).

As shown in the picture below, striping is nothing but splitting one backup file to multiple backup files (maximum 64 files). However, these files may or may not be the same size (depends on the storage disks IO).

Striped Backups

By Striping a backup we can:

  • Increase backup throughput and reduce the backup time window
  • Allow backups & restores to be written or to be read from all devices in parallel
  • Enable backup to different disks, thus distribute the space usage

Below are the simple T-SQL backup commands using AdventureWorks2012 sample database as an example.

T-SQL command for Striping a database backup

Note: In the below script, I used only Disk C to contain all the striped .bak files. However, we can  direct to multiple disks if required

-- Striped Backups -- Backup to multiple files - 4 files in this case
BACKUP DATABASE [AdventureWorks2012]
TO 
DISK='C:\AdventureWorks2012_1.bak', 
DISK='C:\AdventureWorks2012_2.bak', 
DISK='C:\AdventureWorks2012_3.bak',
DISK='C:\AdventureWorks2012_4.bak'
WITH STATS = 10
GO

T-SQL command to restore from Striped database backup 

--Restoring from striped backup -- from multiple files
RESTORE DATABASE [AdventureWorks2012] 
FROM  
DISK='C:\AdventureWorks2012_1.bak', 
DISK='C:\AdventureWorks2012_2.bak', 
DISK='C:\AdventureWorks2012_3.bak',
DISK='C:\AdventureWorks2012_4.bak'
WITH STATS = 10 
GO

Also, we can apply the same striping concept on Log backups. Below is how we do it

T-SQL command for Striping transaction log backup

--Striped Log backup
BACKUP LOG [AdventureWorks2012]
TO
DISK='C:\AdventureWorks2012_1.trn', 
DISK='C:\AdventureWorks2012_2.trn', 
DISK='C:\AdventureWorks2012_3.trn',
DISK='C:\AdventureWorks2012_4.trn'
WITH STATS = 10
GO

Demo: Normal Backup Vs Striped Backup

Below are the results and screenshots from a live production environemnt. This once again proves striping backup files increase data transfer rate and reduce the time to backup

Results:

Backup Type Time to Backup Data Transfer Rate
Normal Backup (1 File) 537.6 Seconds 111.9 MB/Sec
Striped Backup (4 Files) 201.0 Seconds 299.3 MB/Sec

Screenshots:

StripedBackupDemo_1of2

StripedBackupDemo_2of2

However, the major downside of striping a backup is that if at-least one backup file is corrupt, restore operation cannot be performed using the other files.

Also, HERE is why I haven’t discussed striping backups using SSMS GUI.

Technical Reviewer(s): Hareesh Gottipati; Jaipal Vajrala

Read Full Post »

Cleaning/deleting the old backup files is equally important as taking the backups. Doing so we can (i) Avoid overhead cost for the storage space, and (ii) Ensure and retain enough space on the disk for the next successful backup

Using ‘Maintenance Plans’ this task will be a cake walk for the DBAs who are having hard time deleting the old backup files manually!

Below is the step by step process with screenshots showing how to setup and Automate backup files cleanup task using Maintenance Plans in SQL Server.

Step 1: Connect to the SQL Server instance, expand ‘Management’ folder, right click on ‘Maintenance Plans’ and select ‘Maintenance Plan Wizard’

BackUp Clean - 1

Step 2: ‘Maintenance Plan Wizard’ pops out, click Next

BackUp Clean - 2

Step 3: On the ‘Maintenance Plan Wizard’ give a Name and Description. To schedule this as a job, select Change under ‘Schedule’

BackUp Clean - 3

Step 4: ‘New Job Schedule’ pops out. Define a schedule. In my case I gave it to run Daily at 12:00:00 AM. Click ‘ok’ when done.

BackUp Clean - 4

Step5: Make sure everything is correct and click Next

BackUp Clean - 5

Step 6: Select the Maintenance Cleanup Task option and click Next

BackUp Clean - 6

Step7: Here we only have one task, so nothing to order/re-order. Click Next

BackUp Clean - 7

Step 8: This window is the heart for this task. Under ‘Delete files of the following type’ select Backup files. Under ‘File location’ select Search folder and delete files based on an extension. Give the path of the backup files location in Folder and “bak” (for native backups) in File extension. For Light Speed Backups you need to mention ‘lsb’.

Under ‘File age’ check the option Delete files based on the age of the file at task run time. Under ‘Delete files older than the following’ select a retention policy. I left the default 4 weeks. Click Next

BackUp Clean - 8

Step 9: The wizard will write a report to a text file to the defined Folder location. You can leave the default location or chose a location of your choice. Click Next

BackUp Clean - 9

Step 10: Click Finish

BackUp Clean - 10

This should create the Maintenance Plan ‘Delete old backup files’ as shown in the screenshot below

BackUp Clean - 11

Note: SQL Agent Services must be running in order to execute the above discussed Maintenance Plan.

Read Full Post »