Since SQL Azure is fairly new, there are still many SQL Server technologies that haven’t been ported to the cloud...
offering. One is SQL Agent, a tool that gives administrators the ability to execute scheduled tasks on the database server.
There are several ways to simulate this functionality, such as using the sqlcmd utility or creating a "worker role" cloud application that runs in Windows Azure. But these techniques are complicated and less than perfect. There is a much simpler way to execute a scheduled task in SQL Azure -- using a local SQL Agent to execute a SQL Server Integration Services (SSIS) package on the SQL Azure server.
Each of the two recommended ways of executing a scheduled task has drawbacks. You can schedule a batch job to execute a file with the sqlcmd utility, but you have to specify the password as a parameter since SQL Azure only supports SQL Authentication. Unless you want to include the password in a command line stored in a text file, you have to do some coding to handle encrypting and decrypting the password, and then kick off sqlcmd with the decrypted password. Microsoft provides a sample that shows how to create a worker thread application that runs in Azure, but (1) you would have to custom code each task and (2) you may not even have a Windows Azure account running, so a cloud application is not an option.
The technique using SSIS packages is fairly straightforward and takes only minutes to implement if you have basic SSIS skills. The idea is to create an SSIS package that defines a connection to SQL Azure and executes one (or more, if desired) SQL tasks. Then, you schedule the package to be executed on a local SQL Agent. When the job runs, it connects to SQL Azure and executes the defined SQL tasks.
Using a local SQL Agent gives you access to its many features -- you can define execution properties such as number of retries, job step workflow and notifications. In addition, your jobs will show in the SQL Agent job history just like any other jobs. Moreover, if you use one that’s already in use, you will have one less agent to monitor.
The first step is creating an SSIS package. In the package, define your connection to SQL Azure using SQL credentials. Select the “New ADO.NET Connection” option. Your completed dialog box should look something like the one in Figure 1.
Figure 1. The Connection Manager dialog box shows how to configure a connection to a Azure database.
A benefit of using SSIS is that the password is automatically encrypted before being stored in the package, and it gets decrypted at runtime.
Next, add an “Execute SQL Step” task and configure it to execute Transact-SQL script or a stored procedure. You can hardcode parameter values or configure the package to handle parameters passed in at runtime.
Once the package is finished, you have to do one important configuration step. A benefit of using SSIS is that the password is automatically encrypted before being stored in the package, so it needs to be decrypted at runtime. To do that, SQL Agent needs to run under the same user account as the user who created the package. Otherwise, when you schedule the package in SQL Agent, you might end up with the following error:
“SSIS Error : Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state."
There are several workarounds; I find the easiest way to circumvent the error is to protect the package itself with a password. That way, the package won’t have to use the key that’s tied to the user. To implement this, open the package Properties and change the ProtectionLevel property from “EncryptSensitiveWithUserKey” to “EncryptSensitiveWithPassword.” Next, enter the password you want to use into the PackagePassword property field. From this point on, anytime you want to edit or execute the package, you will need to supply the password.
After your SSIS package is completed, create a new scheduled job in SQL Agent and then a new step. In the step’s Type property, select “SQL Server Integration Services Package.” You can execute your package either from a file system or from SQL Server storage.
You will then be prompted for the package password. SQL Agent will store this password using SQL Server encryption, and at runtime it will use it to execute the package. Once that’s done, schedule the package and then view its history. I created a package to purge old data from SQL Azure and then changed the column name in the table to force a package failure. Figure 2 shows a snapshot of the job history.
Figure 2. The local SQL Agent history shows error details from SQL Azure just as if an error occurred on a local SQL Server.
So you can piggyback on the existing features of SQL Agent to view the job history and gain runtime job workflow options. You can implement this with very little effort using familiar tools like SSIS and SQL Agent, and you can view and monitor execution history in SQL Agent, as well as configure automatic retries if the job fails.
By creating SSIS packages to run T-SQL scripts or stored procedures in SQL Azure, you can use the existing features of SQL Agent to simulate the missing scheduling functionality in SQL Azure.
ABOUT THE AUTHOR
Roman Rehak is principal database architect at MyWebGrocer in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools.