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.
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.
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.
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.
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.
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.
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>” />
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.
In addition, there are some other online resources which has more detailed discussion on several topics, such as
- Windows Firewall configuration to allow the access to SQL Server,
- ports used by SQL Server,
- SQL Server in Azure Virtual Machines, and
- security considerations for SQL Server in Azure VM.