Restore a database from Windows Azure with SQL Server 2014 CTP2

In my previous blog post I explained how you could take a backup of a database to Windows Azure Storage with SQL Server 2014 CTP2.
In this blog post I’m going to explain how you can restore your database from Windows Azure.

If your database is still available on your server, right click on the database, select Tasks à Restore à Database


Apparently, the management studio knows that a backup has been taken to Windows Azure and it opens the “Connect to Windows Azure Storage” window.

First of all you need to fill in the Storage Account. That will be the one that you have created when taken the backup to Windows Azure.
To know the correct storage account, go to your Windows Azure web site, select Storage and choose the correct storage account.
In my example here, this will be “koherastorage”.


You should also fill in the Account Key that is linked to your storage. To access stored content, applications and also the SSMS, use one of two 512-bit storage access keys.
Click on the button in the main storage window. A new window with the manage keys will open.

Copy the primary Access Key on your clipboard and past it in the Account Key field of the “Connect to Windows Azure Storage” windows of the SSMS.
You will notice that the key will be masked with *stars*

Select the correct credential to connect to Windows Azure. You can use the credential that you have created while creating the backup.

Click the Connect button and the restore window will appear as we all know it, like in previous SQL Server editions.


Select the appropriate backup sets that you want to restore and click OK to start the restore.
On top, you see that a tail-log backup will be taken. And yes indeed, it’s also stored to my Windows Azure Storage Container!
You should get a similar result like this J


As you can see, restoring a backup from Windows Azure is as simple as restoring a database from local disk.
However, there is still one question that keeps me busy. How will I backup or restore my database if I don’t have any internet connection available…?

Have fun!




Backup to Windows Azure with SQL Server 2014 CTP2

Since SQL 2014 is released, you can take a backup of your database to Windows Azure Storage.
CTP2 has recently be released and it is now possible for you as DBA to create a manual backup to Azure in SQL Server Management Studio.
In this blog post, I’m going to show you how easy it is to do this.

Before you can start with creating a backup, you have to create a storage account to store the backup.
Go to your manage page of Windows Azure.

Click on Storage and select New in the left bottom corner



Now select Data Services à Storage à Quick Create.
Type in a URL name that you want to use and select the location where you want to create the storage


When all the data is filled in click

As a result your storage account is created


Next step is creating a container to store blob files. Containers provide a logical grouping for blobs stored in the Windows Azure Blob service.
When you upload a backup to the Blob service, you must specify a container.

Click on the storage you have just created and select containers.
Click on the link “Create a Container”

In the new container window, type in the container name and select the appropriate Access type.
I’m choosing Private because I’m the only one that can have access to my backup files.

As a result the container is created. You will see that a new URL for the container is created. We will need that later on in the management studio when taking the backup.

Now that the Azure side is configured. Let’s open the management studio and take a backup of our database.
Right click on the database à Tasks à Backup


As you can see, the destination options has been changed a lot. You can choose to take a backup to disk or to URL.
Choose URL to take a backup to Windows Azure.

By default the management studio suggest a file name, but of course this can be changed.

We also have to create a SQL Credential to store the Windows Azure storage authentication information in SQL Server.
Once it is create, you can reuse it.

Click on the button “Create” to create the credential
Specify the credential name.
You have to specify the management certificate, so that you can connect to Windows Azure.
If you don’t have the certificate, you can also connect to Azure by using the publishing profile file.
Go to to download your configuration file and import it into your management studio by clicking the browse button.

Select the storage account to use and click the create button.


You should get a similar result as this one. Notice that the URL prefix is now the same as the URL of the container that you just have created.
If it is not the same, past the container URL into the URL prefix field.


Click OK and your backup will start and finish successfully.

Let’s go back to my Azure Manage page. When you check the container, you will see that a bak file has been added

Well, that wasn’t that difficult if you ask me J.

Next blog post will be the restore of the database from Windows Azure.


Verify Best Practices for Tempdb

As a DBA for many years now, I know that tempdb needs some tuning after installation.
I found these 3 articles on the Confio website that explains the best practices for Tempdb very well

You could check these settings manually but I had to check tempdb configuration for more than 100 servers today.
A good DBA tries to automate as much as possible so I build a script that verifies all the best practices for tempdb.

Just run it against your Central Management Server or on a separate server and you have all the “wrong” tempdb settings in no time!
Please note that the script only works from SQL 2005 onwards.

--Place data file: Data files on own disk
if exists(select substring(physical_name,1,2) from sys.master_files
	where database_id=2
	and substring(physical_name,1,2) IN (select substring(physical_name,1,2) from sys.master_files
	where database_id<>2))
	print 'Data files of Tempdb are not on a seperate drive'

--Place data file: Log file not on same disk as data disk
if exists(select substring(physical_name,1,2) from sys.master_files
	where database_id=2 and type=0
	and substring(physical_name,1,2) IN (select substring(physical_name,1,2) from sys.master_files
	where database_id=2 and type=1))
	print 'Log file of Tempdb is on the same drive as the data file'

--Place data file: Tempdb on the C-drive
if exists(select substring(physical_name,1,2) from sys.master_files
	where database_id=2 and type=0
	and substring(physical_name,1,2) = 'C:')
	print 'Tempdb is on the C-drive'

--Initial Size is set to default 8 MB
if (select sum(size) from sys.master_files where  database_id =2 and type=0)<=1024
	print 'Tempdb initial size is too small'

--Autogrowth is set to percentage
if (select sum(convert(tinyint,is_percent_growth)) from sys.master_files where database_id =2)>0
	print 'Tempdb has configured one or more files as percentage autogrowth'

--Multiple data files according to number of logical processors
if ((select count(1) from master.sys.master_files where type = 0 and database_id = 2)<(select cpu_count from sys.dm_os_sys_info)
   and (select cpu_count from sys.dm_os_sys_info)<=8) OR
   ((select count(1) from master.sys.master_files where type = 0 and database_id = 2)<8
	and (select cpu_count from sys.dm_os_sys_info)>8)
		print 'Fewer tempdb data files than logical processors'
	IF (select count(1) from master.sys.master_files where type = 0 and database_id = 2)>8
	  print 'Too many tempdb data files according to the number of logical processors'

--Verify if all data files have the same size
if (select count(1) from (select  count(1) as counter from master.sys.master_files c where   c.type = 0 and c.database_id = 2 group by c.size) as c)>1
  print 'Different sized tempdb data files detected'