SQL Server in Windows Azure

  • Article

It is certainly possible to run SQL Server 2008 R2 in Azure virtual machines, but keep in mind that they are not persistent between deployments, hence you want to use them for testing only and be aware of potential data loss.

In order to mitigate that risk you may want to set sql up in such a way that logs and data files are stored on Azure drives mounted by the VM – but that’s a topic for another blog post Smile

Azure VMs must be uploaded in a sysprepped state (see previous post) for their deployment to succeed. Fortunately in SQL 2008R2 there is a way to “package” the application so that it can be installed after the virtual machine deployment. The trick is to automate the completion of such installation.

Here’s an outline of the process to follow:

1. Build a Sever 2008 R2 virtual machine in hyper-v

2. On that virtual machine, prepare a SQL Server 2008 R2 image for deployment with sysprep

a. copy the installation media on a local directory (e.g. c:\sqlinstall)

b. prepare the image as described here: https://msdn.microsoft.com/en-us/library/ee210664.aspx

c. Capture the sql server setup configuration file configurationfile.ini

3. Edit the configuration file to instruct sql setup to complete the installation as required. Keywords are:

a. ACTION="CompleteImage"

b. QUIET="True"

c. IACCEPTSQLSERVERLICENSETERMS="TRUE"

d. SECURITYMODE="SQL"

e. SAPWD="<insert your sa password>"

Remember that no active directory exists on Azure, so you will need to use SQL authentication and run the sql services under local credentials, e.g. “NT AUTHORITY\NETWORK SERVICE”. If you plan to connect to sql server, make sure that tcpip is enabled and the relevant port (1433 by default) is open on the windows firewall.

4. Save the configuration file with a new name, e.g. c:\sqlcompleteconfig.ini

5. Install the Windows Azure components

6. Create a %WINDIR%\Setup\Scripts\SetupComplete.cmd file. This file is executed once after windows setup completes. It can be used to perform additional unattended setup tasks, including installing applications. It runs under the system authority context. The file should contain the commands to complete the sql installation, such as:

“C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\ setup.exe"

/CONFIGURATIONFILE="C:\SQLCOMPLETECONFIG.INI"

/installmediapath="c:\sqlinstall\x64\setup”

7. Sysprep the VM and upload it to Azure as instructed in my previous post.

Remember to configure the appropriate ports & endpoints for the VM when creating the package to upload. Enjoy SQL running on Azure Smile