500TB Storage for Database Backup

When your database is big, sometimes just its backup will be around 20GB in size already. Hence, keeping them on disk is always not a solution even though Microsoft Azure provides a data disk with 1TB.

Fortunately, Microsoft Azure offers a scalable and larger storage. It is called Microsoft Azure Storage, a storage with 500TB capacity limit. The good thing about it is we only need to pay for the amount we are using in the storage.

Hence, Chun Siong from Microsoft Singapore suggested my company to try out this service to store our database backups. It turns out that it can be easily done in just 3 steps.

Step 1: Create Azure Storage Account and Retrieve Access Keys

To create a new Azure Storage Account, I simply login to the Azure Management Portal and then choose the Quick Create option of the Storage under Data Services section. I am able to specify the affinity group and replication rule for the Storage Account.

Creating a Storage Account.
Creating a Storage Account.

After the Storage is created, I can retrieve access keys which will be used later in SQL Server to access the Storage Account.

Retrieve the access keys to the Storage Account.
Retrieve the access keys to the Storage Account.

Finally, I just need to create a Container in the Storage Account. All the database backup files will be put inside the Container later.

Created a container in the Storage Account.
Created a container in the Storage Account.

Step 2: Create SQL Server Credentials

I then execute the following T-SQL statement to create credentials so that SQL Server later can connect to the Storage Account.

CREATE CREDENTIAL mycredential 
WITH IDENTITY= 'chunlindbbackup', 
SECRET = '<storage account access key>'

The Storage Account access key here can be either Primary or Secondary access key retrieved in Step 1 above.

Step 3: Backup Database

I create a scheduled job in SQL Server Agent to do backup of my database daily. The URL is the URL of the container created in Step 1.

BACKUP DATABASE mydatabase 
TO URL = 'https://chunlindbbackup.blob.core.windows.net/dbbackup/mydatabase_' + REPLACE(CONVERT(VARCHAR ,GETDATE(),126) ,':','_')+ '.bak'
WITH CREDENTIAL = 'mycredential', INIT, NAME = 'Backup of Database mydatabase'

So yup, now the database backups will be stored on the Storage Account directly.

Restore Database Backup from Azure Storage

To restore a database backup from Storage Account, if the backup file is small, I can just simply execute the following T-SQL statements. The URL of the database backup can be found in the Container in Azure Management Portal.

RESTORE DATABASE mydatabase_test 
FROM URL = 'https://chunlindbbackup.blob.core.windows.net/dbbackup/mydatabase_2014-10-14T13_16_01.243.bak' 
WITH RECOVERY,
MOVE 'mydatabase_db_Data' TO 'F:\db\mydatabase_test_Data.mdf',
MOVE 'mydatabase_db_Log' TO 'F:\db\mydatabase_test_Log.ldf',
CREDENTIAL = 'mycredential'
GO

Unfortunately, the backup that I have is too big. So, I can only download it from Azure Management Portal to the database server first before restoring the database. The download is quite fast.

Download backup file from Storage Account
Download backup file from Storage Account

In case, you wonder why I do not use tool like Azure Storage Explorer, no, it did not work. It would crash also if the backup file was too big.

Pricing

Oh ya, just in case you would like to know the pricing of Azure Storage, you can check it out here: http://azure.microsoft.com/en-us/pricing/details/storage/.

Pricing of Azure Storage in Southeast Asia.
Pricing of Azure Storage in Southeast Asia.

Setting Up MS SQL Server on Azure Virtual Machine

MS SQL Server 2012 + Azure VM

So, now we have an ASP .NET web application running on Microsoft Azure. What we are going to do next is to host our MS SQL Server on the cloud also.

There are two options available in Microsoft Azure to host our SQL database. One is the well-known Azure SQL Database, an implementation of Platform as a Service for a relational database service in the cloud. The other one option is introduced after the new Infrastructure as a Service capabilities of Microsoft Azure. It is now possible to easily deploy instances of MS SQL Server in Azure Virtual Machine.

Azure SQL Database or SQL Server in Azure VM?

Personally, I prefer to directly deploy SQL Server in the virtual machine. At least the entire process looks about the same as what I have already done in our on-premise database server. So, having SQL Server deployed on Azure virtual machine actually means that the developers do not need to make huge changes to our existing applications. In addition, it’s also because migrating existing applications to the cloud normally needs to emulate on-premises behaviour. In short, choosing SQL Server in Azure virtual machine saves the time on migration.

The following is a nice decision diagram that I found on MSDN blog for us to choose which option to use. Also, there is a comparison summary between those two options, Azure SQL Database or SQL Server in Azure Virtual Machine.

To use Azure SQL Database or SQL Server in Azure VM?
To use Azure SQL Database or SQL Server in Azure VM? (Image Credit: MSDN Windows Azure Blog)

 

Creating the Virtual Machine with MS SQL Server Installed

There entire process of creating a virtual machine to host the MS SQL Server is similar to the creation of virtual machine for Windows Server. The only main difference is probably the part of choosing an appropriate image. There are a few editions of SQL Server 2012 for us to choose. You can find a comprehensive comparison among them on MSDN website, again.

Choose "SQL Server 2012" image to deploy MS SQL Server on the new virtual machine.
Choose “SQL Server 2012” image to deploy MS SQL Server on the new virtual machine.

The following table shows the pricing of each edition running on Azure VM as well as the disk sizes available. Here I only pay attention to the memory intensive instances, i.e. A5, A6, and A7. They have larger RAM and disk sizes for the virtual machine and they are thus considered optimal for hosting databases and other high-throughput application. The data shown in the table is applicable for virtual machines deployed in Asia Pacific Southeast, i.e. Singapore.

Asia Pacific Southeast (Singapore) VM pricing for each edition of SQL Server
Asia Pacific Southeast (Singapore) VM pricing for each edition of SQL Server (screenshot taken on 20 April 2014)

Connect to SQL Server Database Engine on Azure VM

After the virtual machine is up and running, we can immediately RDP in to the VM. Then in there, we just need to launch Microsoft SQL Server Management Studio to access the database with the Windows Authentication.

Running SQL Server Management Studio on the virtual machine.
Running SQL Server Management Studio on the virtual machine.

Open TCP Port 1433

SQL Server typically uses TCP port 1433 for remote connections to the database. So, we need to add an endpoint as well as to open the port in the virtual machine firewall for this. However, to avoid security attack, it’s recommended to specify a different Public Port when creating the endpoint in Azure.

1433: A TCP port normally used by MS SQL Server for remote connection to the database.
1433: A TCP port normally used by MS SQL Server for remote connection to the database.

SQL Server Authentication

We need to change the server authentication to “SQL Server and Windows Authentication mode”. This enables us to create logins in SQL Server which are not based on Windows user accounts. Both the login ID and passwords will be stored in the SQL Server. This allows SQL Server to continue supporting our third-party applications that require SQL Server Authentication. After that, we just right-click on the server in Microsoft SQL Server Management Studio Object Explorer to restart the server.

SQL Server and Windows Authentication Mode
SQL Server and Windows Authentication Mode

Connecting Application to the SQL Server

To connect your ASP .NET web application with the database, in web.config, you can just key in the server name, port number together with login ID and password in the following connection string that is used to connect the instance of the SQL Server running on Azure VM.

<add key=”strDBconn” value=”Data Source=****.cloudapp.net,<port-number>;Initial Catalog=<database name>;UID=<login ID>;PWD=<login password>” />

Conclusion

The steps taken to deploy a Microsoft SQL Server on Azure virtual machine are quite straight-forward. There is also an official detailed documentation about provisioning a SQL Server Azure Virtual Machine. I like one of its diagrams which shows the two main connection paths. The complete diagram is shown below.

SQL Server Azure VM Connection Paths
SQL Server Azure VM Connection Paths (Image Credit: Microsoft Azure Documentation Center)

In addition, there are some other online resources which has more detailed discussion on several topics, such as