SQL Server Data Files in Windows Azure | Part-2

In first part we discussed this feature and analyzed whether we should use it or not, now let’s see some of the benefits before we’ll discuss the steps to implement it.

Apart from being a wonderful & cool looking concept this technology also delivers many benefits like:

1. You get a centralized copy of data and log files which can be connected to any SQL Server Instance instantaneously and available to all geographic locations. (but yes you need Internet Connection there Winking smile )

2. In case of any eventuality you can meet all the tight SLAs without loosing your precious data, because your data and log files are intact on cloud (even if something goes wrong with the primary site of your storage account you get your files coz you can avail HA n Geo-DR remember) and  on top of everything you don’t even have to restore from backups all you need is to simply attach your files to another Instance. (Having said that it is not a substitute of your backups and it is recommended to keep taking backups the way you were taking)

3. By Placing the files on Azure you get unlimited storage capacity along with built-in HA, SLA and Geo-DR.

4. The best part is you don’t have to change anything in your application, you keep on working just like you work on any other database. (except Connection string- Obviously)

5. Security is not at all compromised all your data stays encrypted at all times in Azure Moreover, you can further increase the security by configuring TDE which is fully supported and all your keys stay on your SQL instance (Which can be On-Premise or on an Azure SQL VM)

 

To implement this feature you need to create a credential which will provide the access to your storage account to be able to do file related operations like (read, write or delete)

CREATE CREDENTIAL [https://mondbfiles.blob.core.windows.net/sql02]
WITH IDENTITY = ‘Shared Access Signature’,
SECRET = ‘sr=c&si=SQL&sig=%2Be1Xq9637csqkRXtGT%2B81V5DkNLoN844DFR1lmv%2B4H4%3D’

for creating credential we must have a Shared Access Signature Key, you will be able to create it easily by following the steps mentioned here….. the steps mentioned here are for the latest version of Azure Storage Explorer, but at the time of writing this article I was using the old version. GUI is a little bit changed however the concept and the steps remain the same and you should be able to create the keys without any issue.

example of a SAS key: “https://mondbfiles.blob.core.windows.net/sql02?sr=c&si=SQL&sig=%2Be1Xq9637csqkRXtGT%2B81V5DkNLoN844DFR1lmv%2B4H4%3D

The SAS key (Storage Access Signature Key) has two parts the first part (that is in Black color) is the blob endpoint name along with the container which becomes the credential name and the second part (which is in Green color) is the one which defines the SAS key granular access and both these parts gets delimited by a “?” sign (which is in Red color) .

while creating the credential you must provide the first part as credential name and second part as SECRET with identity as “Shared Access Signature” to make it work.

 

Once the credential has been created on the SQL instance you can now create the DB by pointing its file creating location to your Azure Storage account container.

CREATE DATABASE monprodv12
ON  PRIMARY
( NAME = N’monprodv121′, FILENAME = N’
https://mondbfiles.blob.core.windows.net/sql02/monprodv12.mdf’)
LOG ON
( NAME = N’monprodv12_log’, FILENAME = N’
https://mondbfiles.blob.core.windows.net/sql02/monprodv12Log.ldf’)
GO

If your credential is correct and your SAS key is valid you should be able to create the DB without any problem.

Here are couple of screenshots to make it more clear.

 

For creating this DB, I first created a Shared Access policy & then used the same policy to create the SAS Key.

SAS-policy 

SAS1

 

Once the SAS key was ready I used the same key to create the Credential and then the DB was created.

db created with files on cloud - sarabpreet

 

Once the DB was created I checked the system tables to find where the files are pointing and it was pointing to my azure storage container as expected.

dbfiles on azure

 

Thanks,
Sarabpreet Singh

SQL Server Data Files in Windows Azure | Part-1

This is a brand new technology which is introduced in SQL Server 2014, having said that believe me this is not something which is new to us – we’ve been using this technology since a long time in our daily life like: Streaming music files through internet or saving all your data on One Drive.

So just like the examples mentioned above now starting from SQL Server 2014 you can take advantage of Azure & place your Data and Log files in Azure no matter if your SQL Server Instance is running on-Premise or on Windows Azure Virtual Machine.

It lets you create & place your data and log files directly on windows azure storage, by doing this you get all the advantages of Azure storage – Bottomless storage, offsite location, Geo-Redundant, No provisioning and No need to do Device Management, its decay free (Full Media Safety) and always accessible remotely from anywhere. To know more read this.

 

Why do you want to place your data\log files on Azure?

Don’t you think this is counter intuitive to keep the files on Cloud? Yes at first even I was thinking the same. This looks odd but can be used in many circumstances. So essentially all you need to do is re-attach your (data and log) files from cloud to your SQL Instance, SQL instance can be on-premise or on Cloud.

The next question will obviously be regarding Performance…. In case you are skeptical about the performance you can host your SQL Server also on Cloud (windows Azure VM) this way your SQL Server & data\log files both will be on cloud.

This feature gives you power of a DR, even if your Data Centre goes down you can bring back the SQL instance in minutes without any special technique all you need is a SQL Instance which you can use just like your production server because all data is still intact & placed on Cloud – you just have to re-attach the files to make it working.

 

Is it Secure?

Obviously it is very much secure and your data is protected in many ways:

a) Windows Azure itself provides a mechanism to secure & protect your data; it’s not like a public folder which is open to all. To know more you can read this blog which will help you understand how the security works in Azure Storage and the importance of each type of key.

b) Granular Encryption encrypts the data (Cell by cell encryption) at all times & the key to decrypt is only available with SQL Server instance and never on the cloud along with data\log files.

c) If you are still not convinced you may still use TDE (transparent data encryption) to further encrypt it. It is fully supported.

 

Ok, I am convinced but what about my RTO & RPO?

You don’t have to worry about these two because inherently this technology take care of these two; It saves a lot of your time – you don’t have to restore the Database from a backup even if your server got crashed because your data files are intact in Cloud, you just need to re-attach them on a new Instance and your database is available right from the point where you left (you lose only uncommitted transactions) so it gives you better RPO (Recovery Point Objective) along with RTO (Recovery Time Objective)

Enough of talking now, let me know how to use this amazing feature….

 

Thanks,
Sarabpreet Singh

Lessons learnt by implementing Backup Encryption

We’ve already discussed Backup Encryption feature in detail, incase you’ve not covered those please find the link:

Part-1
Part-2
Part-3

 

There are a few things you must know before you implement backup encryption in your environment, here I’ll try to list them:

1. Original Certificate which was used as an encryptor should be available at the destination server\Instance to restore an encrypted backup. Encrypted backup keeps track of the thumbprint of the certificate, if the thumbprint doesn’t match it will never allow SQL Engine to read the backup, you won’t even be able to even get the file list details from the backup file (Restore FilelistOnly option). Refer the screenshot below which shows the error:

REstore-error-without-certificate

 

2. We should also refrain our self from renewing or changing the certificate in any way, renewal or changing the certificate results in changing the thumbprint of the certificate therefore making the certificate useless for the backup restore.

3. The login ID being used to restore the encrypted backup must have View Definition permissions on the encryptor (Certificate\Asymmetric Key) which was used to encrypt the backup.

4. You must restore the Database Masker Key on the destination server\SQL Instance since the certificate we used was encrypted using DMK only.

5. You need not to Restore the Service Master Key, if your DMK was encrypted using a Password- that is not a must.

6. You must take a backup of all your keys\certificates and passwords and keep it on a safe location other than the source server. The best practice would be to keep a copy of these things at your DR site or Offsite.

7. The Database master key (DMK) must be explicitly opened if it was encrypted using a password.

8. Restoring a Master Key can be a resource intensive task, if the current master key is being used to encrypt many things and you are restoring a new Master Key it has to decrypt and re-encrypt everything- we must try to do it in Off-Peak time.

 

Happy Learning

Thanks,
Sarabpreet Singh

Backup Encryption | Part-3

In Part-1 we discussed the feature of backup encryption and part-2 walks us through the scripts to prepare for the pre-requisites and take an encrypted backup, In part-2 we took the backup of Repro_FGDN Database on MBTEST server.

We took the encrypted backup using Certificate as an encryptor and used AES_256 encryption algorithm.

In this part we’ll try and restore the same encrypted backup on another server – FGDNPROD.

 

–Before you restore the encrypted backup on the destination server, you must create the Encryptor; in part-2 we created a certificate which was used as an encryptor. The certificate was encrypted with a DMK so we must first restore the DMK before creating the certificate.
RESTORE MASTER KEY
FROM FILE = ‘C:\backup\SQL_Prod_Repro_F.key’
DECRYPTION BY PASSWORD = ‘alseImRtjSnuEiheYf8b’
ENCRYPTION BY PASSWORD = ‘alseImRtjSnuEiheYf8b22e’;

 

–You must open the master key before using the DMK, once the Database Master Key is open – it stays open for the complete session.
OPEN MASTER KEY
DECRYPTION BY PASSWORD = ‘alseImRtjSnuEiheYf8b22e’

 

–Create the certificate
CREATE CERTIFICATE FGDN_DMK
FROM FILE = ‘C:\backup\FGDN_DMK_certificate.cer’
WITH PRIVATE KEY
        (
                FILE = ‘C:\backup\FGDN_DMK_certificate_private_key.key’
                , DECRYPTION BY PASSWORD = ‘abcalseImRtjSnuEiheYf8b’
        )

–Closing the DMK
close master key

 

Restoring service master key on destination server is not required.

–Restore encrypted Database backup
Restore database repro_FGDN
from disk =’C:\backup\Repro_FGDN_22.bak’

 

Finally after creation of the encryptor you will be able to restore the encrypted backup, refer the screenshot where I was able to restore the encrypted backup on another server named FGDNPROD.

Restore completed without service master key2

 

Thanks,
Sarabpreet Singh

Backup Encryption | Part-2

In the first part we discussed the new feature of Backup Encryption which is introduced in SQL Server 2014, the post describes the benefits, restrictions, permissions required etc.

Now let’s see this in action, the below mentioned scripts will generate Service Master Key, Database Master key and a Certificate which will act as an encryptor and post that we’ll use the encryptor to take a backup with Encryption for a DB named Repro_FGDN.

 

–Regenerating Service Master Key
ALTER SERVICE MASTER KEY REGENERATE;
GO

–script to Backup Service Master key
BACKUP SERVICE MASTER KEY
TO FILE = ‘C:\backup\SQL2014_service_master_key.key’
ENCRYPTION BY PASSWORD = ‘ZlseImRtjSnuEiheYf8a’;
GO

–Creating Database Master Key also known as DMK
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = ‘lseImRtjSnuEiheYf8’;
GO

–script to backup DMK
BACKUP MASTER KEY
TO FILE = ‘C:\backup\SQL_Prod_Repro_F.key’
ENCRYPTION BY PASSWORD = ‘alseImRtjSnuEiheYf8b’;
GO

–Creating Certificate (Encryptor) which is encrypted by DMK
CREATE CERTIFICATE FGDN_DMK
   WITH SUBJECT = ‘FGDN_DMK_Subject’

–Script to Backup certificate as soon as it gets created.
BACKUP CERTIFICATE FGDN_DMK
TO FILE = ‘C:\backup\FGDN_DMK_certificate.cer’
WITH PRIVATE KEY
        (
                FILE = ‘C:\backup\FGDN_DMK_certificate_private_key.key’
                , ENCRYPTION BY PASSWORD = ‘abcalseImRtjSnuEiheYf8b’
        );
GO

–Script to backup Database using Encryption
BACKUP DATABASE repro_FGDN
TO DISK = N’C:\backup\Repro_FGDN_22.bak’
WITH INIT, CHECKSUM, COMPRESSION, STATS = 1
        , ENCRYPTION
        (
        ALGORITHM = AES_256
        , SERVER CERTIFICATE = FGDN_DMK
        )

This shows the backup completion from the Server named MBTEST.

MBTest

 

This is the screenshot of the Backup folder which consists backup of all Certificates, Keys along with Encrypted backup.

image

 

Learning\Points to consider:

1. Backup the Certificate\Keys as soon as you create them, you never know when you might need to recover the database and without these encryptors you won’t be able to recover the database.

2. Certificate to be used as an Encryptor for backup encryption should be encrypted by DMK only.

 

Thanks,
Sarabpreet Singh

Backup Encryption | Part-1

We’ve been asking for this feature from a long time and it was one of the main reasons why third party tools were being used, they were minting money like anything. Thankfully Microsoft introduced this feature in SQL Server 2014, now you can encrypt the data while creating a backup at runtime and creates an encrypted backup file.

The best part is all type of storage destinations be it On-Premise or Windows Azure Storage all of them are fully supported. In addition to that you can also use this feature if you are using SQL Server Managed Backup to Windows Azure. I know what you are thinking “you are still using old version and you’ve to rely on these third party tools” The good news is, if you want you can use Backup encryption in your old SQL versions also (like SQL Server 2005, 2008 and SQL 2008 R2) but only if you plan to take backups on Azure by using SQL Server Backup to Windows Azure Tool. Read more about this feature here…

 

Benefits

1. This helps in securing the data; SQL Server encrypts the data at the time of creating a backup – so no need to change anything on the application front.

2. You can also use Backup Encryption for TDE enabled databases.

3. Since this is also supported with SQL Server Managed backup to Windows Azure this gives an added security for off-site backups.

4. You’ve got multiple options to select the encryption algorithm to meet your requirements.

5. You can integrate encryption keys with Extended Key Management (EKM) providers.

 

Backup

To make use of Backup Encryption all you need to do is provide an encryptor (that could be a Certificate or Asymmetric Key) and choose an Encryption algorithm. SQL Server provides four encryption algorithm to choose from these are: AES 128, AES 192, AES 256 and Triple DES.

 

Restoring

During restore you don’t have to specify any encryption parameters. However the certificate or the asymmetric key used to encrypt the backup file must be available on the instance you are restoring to. The user account performing the restore must have View Definition permissions on the certificate or key. If the DB you are restoring is already TDE configured, the TDE certificate should also be available on the instance you are restoring to.

 

Encryptor

You must create a Database Master Key (DMK) before creating a Certificate or Asymmetric Key. A DMK is symmetric key that is used to protect the private keys of Certificates and Asymmetric keys that are present in the database.

Once a DMK is created you can create a certificate or asymmetric key to use for backup encryption.

 

Restrictions

1. While encrypting backups using asymmetric key always remember the keys residing in the EKM provider are supported.

2. Two SQL Editions (SQL Express and SQL Server Web) do not support encryption during backup. Having said that restoring from an encrypted backup to these editions is fully supported.

3. Also note that earlier versions of SQL Server cannot read encrypted backups.

4. Appending to an existing backup set option is also not supported if you are encrypting the backup.

It is very important to back up the certificate or asymmetric key, and preferably to a different location than the backup file it was used to encrypt. Without the certificate or asymmetric key, you cannot restore the backup, rendering the backup file unusable.

 

Permissions

To encrypt or restore from an encrypted backup View Definition permission on the certificate or asymmetric key that is used to encrypt the database backup. However Access to the TDE certificate is not required to backup or restore a TDE protected Database.

Thanks,
Sarabpreet Singh

SQL Server Managed Backup to Microsoft (Windows) Azure | Part-2

In the last post we discussed about the features of SQL Server Managed Backup to Windows Azure, how it is different from conventional backup schedules, how the backup and container gets named and based on what conditions SQL Server will initiate a Full\Transactional Log backup or skip it.

 

What are the benefits of using this technology:

1. If you want to automate backups without using this feature you need to develop a backup strategy, you may also have to write your own code and schedule the jobs etc. But by using this feature all you need to do is provide the retention period & storage location that’s it and SQL Server Managed backup to windows azure will schedule, perform and maintain the backups for you.

2. This feature can be configured at DB level or at the instance level.

a. If you enable this at the instance level this feature will be automatically applied to all DBs created thereafter that means you don’t have to worry about the newly created DBs.

b. If you enable this at the DB level and specify the retention period this will override the default settings set at the instance level; means more granular control.

3. You don’t even have to worry about the type of backup and frequency, all of these things gets determined automatically by SQL Server Managed Backup to Azure based on the retention period you specify and the workload on the database.

4. If you configure to use Encryption it gives you additional Security. Backup Encryption is new feature added in SQL 2014 and you can also use it in Native backups on disk\tape and you can take backup with Encryption using T-SQL or GUI both.

 

Things you must know about this feature:

1. A full backup and a transaction log backup can run concurrently, however if there are more than 1 Full DB backup jobs running for the same database it will wait for the current job to complete before initiating another backup; the same applies for transaction log backup also.

2. If more than 10 concurrent full DB backups are scheduled, it will issue a warning through debug channel of Extended Events; then maintains a priority queue for the remaining DBS that require a backup until all the backups are scheduled and completed.

3. Failures are logged as Extended Events.

 

Limitations of Managed Backup in SQL 2014

Don’t you think features discussed till now are too good to be true, yes just like any other technology there are some limitations for SQL Server Managed Backup to Windows Azure, these are:

1. Only Full and Transaction Log backup is possible through Managed Backup.

2. System Databases are not supported.

3. Only full & Bulk-Logged recovery models are supported so the recovery model of the DB should be one of them.

4. File backup automation is not supported as of now.

5. SQL Server Managed backup to Azure is currently supported only by T-SQL; you can monitor and troubleshoot by using Extended Events though. If you are a PowerShell & SMO freak then watch out, because PS & SMO support is limited to Configuring storage and retention period default settings for an instance of SQL Server, and monitoring the backup status and overall health based on SQL Server Policy Based Management policies.

6. Windows Azure Blog Storage service is the only supported backup storage option. Backup to disk and tape are not supported.

7. Currently the maximum file size allowed for a page blog in windows azure storage is 1 TB that means you can’t backup the DB if the backup size goes beyond 1 TB & it will fail. To avoid such scenarios you can use Compression and test the size before configuring the backup & keep a check on the backup size.

8. It also may have some limitations when it is configured with other technologies supporting backups, HA & DR.

 

Thanks,
Sarabpreet Singh

SQL Server Managed Backup to Microsoft (Windows) Azure | Part-1

SQL Server Managed backup is a new feature introduced in SQL Server 2014, this feature is simply outstanding – you don’t have to worry about the backup strategy anymore, this feature manages and automates the backup process.

This feature uses sort of artificial intelligence – if you see at current scheduling features they just keep on taking backups at a specified time interval whereas, in this feature it checks couple of things and based on the situation it decides whether a backup needs to be initiated for a Database or not. This Feature is based on Retention period selected for backups and transaction workload on the database and it also provides Point-in-time recovery. You want to know how? Keep reading… you’ll get your answers soon.

You can enable this feature at two levels Instance as well as Database level. It doesn’t really matter if your SQL Server is running On-Premise or on Cloud. SQL Server Managed Backup to Microsoft (Windows) Azure is in-fact recommended for SQL Server running on a VM in Cloud.

The Retention period is a value which determines what backup files to keep and what needs to be deleted in the Azure Storage so that the underlying DB can be recovered to a point in time within the time frame specified. The valid retention period values are from 1 till 30 days.  The backup_finish_date of the backup is used to determine and match the time specified by the retention period settings.

Naming convention it follows for the backup and container

The backup files are named using a combination of first 40 characters of the database name, the GUID of DB without “-“& the timestamp; all these values are separated using Underscore to make it readable. The .bak and .log extensions are used for Full and Log Backups respectively. In case of Availability Group databases the Availability group database GUID is added after the first 40 characters of the DB name, you can get this GUID value from group_database_id column of Sys.databases.

The same way Backup container of (Windows) Microsoft Azure also get named using the SQL Instance name and in the case of Availability databases it gets named using GUID of Availability group.

 

Conditions that trigger a backup

As we discussed earlier also SQL Server managed backup doesn’t initiate a backup merely on the basis of schedule which has been fixed but takes a calculative decision whether a backup is really required for the given DB or not. It decides based on the below mentioned conditions:

A. Full backup gets triggered:

1. At the time when Managed Backup is configured on DB or when it is configured on the complete instance with default settings.

2. If the log generation since last full backup is greater than or equal to 1 GB in size.

3. If maximum time interval of one week has passed since the last full backup. (it tries to take at least one full back up every week)

4. When the log chain is broken; it checks periodically whether the log chain is intact or not by comparing first and last LSN of backup files, if it finds there is a gap it initiates a full DB Backup. There could be n number of reasons for log chain breaks, most common would be:
a. Switching recovery model to simple.
b. Taking back up through any other job\manual process.
c. Deleting some backup files
d. Accidental overwrites of backup files.
e. Truncating the log.

B. Transaction Log backup gets triggered:

1. When the Transaction Log space used is equal to or greater than 5 MB.

2. The maximum time interval of 2 hours has passed since the last log backup.

3. If no log backup history is found; for example when you enable Managed backup for the first time for the respective DB.

4. Anytime the TLog backup is lagging behind the full backup, it always tries to keep the log chain ahead of the Full backup. In simple words after every full backup it will initiate one TLog backup.

 

Thanks,
Sarabpreet Singh Anand