Friday, February 27, 2015

SQL Server Backup to Windows Azure

There are several ways of storing your databases in the cloud, but what if you want to keep your databases on your local servers and just dip your toe in the cloud (apologies for the terrible mixing of metaphors)? Just because you haven’t decided to move all of your data to the cloud doesn’t mean you shouldn’t benefit from cloud services. In this post I’m going to have a look at using Windows Azure storage as a backup location for SQL Server. Cloud storage is a great option for backups because no matter what happens at your primary site, the cloud backup is always available.This is available in Windows Server 2012 Service Pack 1 Cumulative Update 2 and later.

The first step is to set up your Windows Azure account. If you don’t already have an account you can go here to create a free trial.

You now need to create some storage in the cloud. SQL Server uses the Windows Azure Blob Service for storage and will create new files as necessary, however, you must create a container to store the blob. Follow these steps to set up Azure stoarge and a container:

  1. Log in to your Windows Azure Management Portal here.
  2. At the bottom of the screen click the NEW button if the NEW pane is not visible. Click DATA SERVICES, click STORAGE and click QUICK CREATE. In URL type a name for your demo organization; I’m going to use gtasqlbackup. In LOCATION/AFFINITY GROUP select your region and click CREATE STORAGE ACCOUNT.
  3. To be able to access the cloud-based storage, and backup or restore your data, you need the access keys, so click MANAGE ACCESS KEYS at the bottom of the screen. Write down the STORAGE ACCOUNT NAME, PRIMARY ACCESS KEY, and SECONDARY ACCESS KEY and then click the tick to close the window.
  4. Now you need to create the container to store your backups. Click your storage account name and then click CONTAINERS. Click CREATE A CONTAINER and in NAME type something relevant for your backups; I’m going to type sqlbackup. By default the container is private and it makes sense to leave it as private in this scenario. Click the tick to create your container.

 

Now you need a SQL Server Credential to store the security information used to access your cloud storage. Follow these steps to set up your credential:

  1. Start SQL Server Management Studio and connect to the database engine instance that you are using for this demo. Click New Query.
  2. Type the following query:
    CREATE CREDENTIAL sqldemo
    WITH IDENTITY= 'gtasqlbackup' /* use the account name that you specified earlier */
    , SECRET = 'XXXXXXXXXXXXXXXXXXXXX' /* use one of the access keys that you wrote down earlier */
  3. Click Execute.

Now you’re ready to perform the backup with the following steps:



  1. Click New Query
  2. Type the following query:
    USE master
    GOBACKUP DATABASE [Finance] /* use your database name */TO URL = 'http://gtasqlbackup.blob.core.windows.net/sqlbackup/finance.bak'/* use your account name, container name, and backup name*/

    WITH CREDENTIAL = 'sqldemo' /* use your crential that you just created */


    GO


  3. Click Execute.

That’s all there is to it. To see a demonstration of a backup and restore, have a look at my demo:

SQL Azure Backup

Thursday, February 26, 2015

Windows Live Writer

I thought I’d create a blog post after a long time away and fired up Live Writer. It instantly crashed every time with a Live Writer has stopped working error.

Tried the usual; reboot, repair, uninstall, reinstall. No success. Searched the Internet and found useful articles here and here and here and here, but still no success. Searched Event Viewer and couldn’t find the issue.

Finally, I came across this article. It turns out that if you have your documents folder on an external drive (I have an internal SSD and a much bigger external disk), and Live Writer can't see this folder, then it will crash at startup. Reattached my drive and all is good.

Backup Encryption with SQL Server 2014

You’ve been able to encrypt data in SQL Server for several versions using Transparent Data Encryption and, if you backup encrypted data, the backup is encrypted. The downside of this approach is that Transparent Data Encryption has a performance hit and so there was no straightforward method to have an encrypted backup of non-encrypted data. This meant that if you didn’t want your data encrypted then your backup, possibly held off-site, could be restored to another server and your data would be compromised.

SQL Server 2014 introduces encrypted backups and they’re very easy to implement:

1. First, create a certificate and keep it very safe. The following code demonstrates how to create a certificate (use your own name and password):

USE MASTER

GO

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

GO

CREATE CERTIFICATE BackupEncryptionCertificate

WITH SUBJECT = ‘This is a certificate to encrypt backups’

2. Now encrypt your backups. You can use the WITH ENCRYPTION clause like this:

3. SQL Server 2014

4. Or you can specify encryption graphically like this:

5. SQL Server 2014

Note that it must be a new backup set and cannot be added to an existing set.

Now you have an encrypted backup without having the performance hit of encrypted data.