Project SetUp

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 objects:

Tables

  • 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

Views

  • dbo.vwJobsToExecute - it is just a helper view which joins JobDefinitions and JobActivity to filter out only non-running jobs for execution

Stored procedures

  • 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:

If you have any troubles

Just write in Ask your questions here and I will be happy to help!

Last edited Mar 28, 2011 at 9:09 PM by astaykov, version 3

Comments

No comments yet.