Setting up the database
After you download the packaged ZIP file, or the source code, you have to create a database in your local SQL Server (be it SQL Server Express, or any other edition). After you create the data base using your favorite tool, you have execute the "SqlAzureAgent.sql"
script against that database. Note the script will not create the database itself. It will only create necessary tables, views and stored procedures to use with SQL Azure Agent Service. You will find this script under "References" solution folder.
After you created the database, you have to create a login and user for that database. You are advised not to use "Integrated authentication" because SQL Azure only supports SQL Server Authentication. A successful run should produce following database
- dbo.JobDefinitions - this is the main table where you store your jobs. You define Friendly name, SQL to execute against targeted server (stored procedure name), optional ConnectionString to use when connecting to targeted server. If ConnectionString is
not specified, the stored procedure is executed against the same database, where the job definitions are;
- dbo.JobActivity - this is jobs activity logging and monitoring table. The Agent service inserts records here for each job execution
- dbo.DummyTable - dummy table for demo purposes
- dbo.vwJobsToExecute - it is just a helper view which joins JobDefinitions and JobActivity to filter out only non-running jobs for execution
- dbo.CleanUp - cleans up any running job logs for jobs that have been running more than an hour (assuming a job will finish for an hour!)
- dbo.StartJob - inserts record in the JobActivity table and returns a unique identifier, identifying a running job. If this procedure succeed, the stored procedure in JobDefinition table is being executed
- dbo.StopJob - updates a JobActivity record for given unique identifier, to flag job as finished
- dbo.DummyIncrementer - dummy procedure for demo purposes - will be executed from the Agent Service
Having the database ready you can move to the next step:
Setting up the service
Before running the service, you have to setup the correct connection strings to be used by it. Connection strings are located in the app.config file, under the "AgentService" project. One is default connection string which will be used to execute
jobs in case no connection string is specified in the JobDefinition. Second one is a EF connection string. I use EF as ORM to get the results from vwJobstoExecute view.
Ready to go
Put a breakpoints in your favorite places and hit F5! Your SQL Azure agent is ready for deployment!
Before deploying to LIVE
If you want to deploy your SQL Azure Agent to live Azure environment do not forget to do a couple of things:
- first create your SQL Azure server & database & login
- edit the sql server connection strings in app.config
- edit the ServiceConfiguration.cscfg under SqlAzureAgentService project. You have to change the configuration setting Microsoft.WindowsAzure.Plugins.Diagnostics.ConnectionString with a value corresponding to your Cloud Storage account
- check out these blog posts for common mistakes when deploying to Azure:
- check out the blog post on how to deploy a Windows Azure service right from Visual Studio:
If you have any troubles
Just write in Ask your questions here
and I will be happy to help!