In certain circumstances, you might find yourself with a need to install SQL Server Express on one of your Windows Azure worker roles. Exercise caution here though folks: this is not a supported design pattern (remember, a restart of your role instance will cause all data to be lost).

It was however exactly what I needed for my scenario and I thought I’d share it in case it serves a purpose for you.

There are a couple of approaches you can take, of course, one of which is ‘startup tasks’ specified in the service definition files. However, these offered me limited configuration options because I needed to customise some of the command line arguments being passed to the installer based on values from the Role Environment itself.

The trickiest part was actually figuring out the correct command line parameters for SQL Server 2008 R2 Express, which to be honest wasn’t that fiddly at all. Here are the parameters you’ll need:

/Q/ACTION=Install/FEATURES=SQLEngine,Tools /INSTANCENAME=YourInstanceName
/HIDECONSOLE /NPENABLED=1 /TCPENABLED=1 /SQLSVCACCOUNT=”.\YourServiceAccount\” /SQLSVCPASSWORD=”YourServicePassword” /SQLSYSADMINACCOUNTS=”\.\ADMINACCOUNT” /IACCEPTSQLSERVERLICENSETERM S/INSTALLSQLDATADIR=”FullyQualifiedPathToFolder”

In the parameters above, we’re specifying a silentinstall with the /Qparameter, installing the SQL Database Engine and Management Tools (basic) with the /FEATURESparameter, setting the instance name, enabling named pipes and TCP, while setting service accounts and specifying the SQL data directory.

The next part then, is to actually build this as a command line and execute it in the cloud environment. How do we do this? Simples: we use System.Diagnostics to create a new Process()object and pass in a ProcessStartInfoobject as a parameter:

var taskInfo=new ProcessStartInfo
{
FileName=file,
Arguments=args,
Verb="runas",
UseShellExecute=false,
RedirectStandardOutput=true,
RedirectStandardError=true,
CreateNoWindow=false
};
//Start the process
_process=new Process(){StartInfo=taskInfo,EnableRaisingEvents=true};

For good measure, we’ll also redirect the standard and error output streams from the process so that we can capture those out to our log files:

//Log output
DataReceivedEventHandler outputHandler=(s,e)=>Trace.TraceInformation(e.Data);
DataReceivedEventHandler errorHandler=(s,e)=>Trace.TraceInformation(e.Data);

//Attach handlers
_process.ErrorDataReceived+=errorHandler;
_process.OutputDataReceived+=outputHandler;

Then, we’ll execute our task and ask the role to wait for it to complete before continuing with startup:

//Start process
_process.Start();
_process.BeginErrorReadLine();
_process.BeginOutputReadLine();

// Wait for the task to complete before continuing...
_process.WaitForExit();

Stick all of that into a method that you can re-use, and don’t forget to add parameters called fileand args(strings) that contain the path to the SQL Server Express installation executable and the command line arguments you want to pass in.

How to build your command line argument

If you’re wondering why I didn’t hardcode my command line options, it’s because up in Azure, the standard builds for web and worker roles don’t come preloaded with any administrative accounts – you have to specify those during design time. I actually ‘borrow’ the username of the Remote Desktop user (which is provisioned as an administrator for you when you ask to enable Remote Desktop).

I actually end-up with this quick-and-dirty snippet:

string file=Path.Combine(UnpackPath,"SQLEXPRWT_x64_ENU.exe");
string args=string.Format("/Q/ACTION=Install/FEATURES=SQLEngine,TOOLS/INSTANCENAME={2}/HIDECONSOLE/NPENABLED=1/TCPENABLED=1/SQLSVCACCOUNT=\".\\{0}\"/SQLSVCPASSWORD=\"{1}\"/SQLSYSADMINACCOUNTS=\".\\{0}\"/IACCEPTSQLSERVERLICENSETERMS/INSTALLSQLDATADIR=\"{3}\"", username,password,instanceName,dataDir);

So, ultimately, you’ll then want to wrap all of this up in to your role’s OnStart() method. Include a check to see whether SQL Express is already installed, too.

And, if you’re stuck trying to debug what’s going on with your otherwise silent installation, SQL Server Setup Logs are your friend. You’ll find them by connecting to your role via Remote Desktop and opening the following path:

%programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\

Enjoy!