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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s