How to Schedule Database Jobs and Maintenance with SQL Azure

If you’ve started looking at or working with SQL Azure, as a DBA, you may be disappointed to know that much of the visibility and control into the servers & databases that you’ve come to live by with SQL Server are not there in SQL Azure. For example, there are no SQL Agent Jobs and there are no Maintenance Plans. But that doesn’t mean you are smack out of luck. Here are 4 ways to schedule jobs & maintenance against SQL Azure:

1. Schedule jobs & maintenance plans for your local on-premise good old SQL Server 2008 R2 SSMS! You can write T-SQL directly to SQL Azure from SQL Server 2008 R2 by using maintenance plans or jobs with linked servers.

Let me be honest with you, though. Take a look at the Management Node and the System Database folders from SSMS on your SQL Azure instance. Not much there, is there? You also cannot change database context with USE DB commands in SQL Azure. So when you connect into the instance, you either need to have the user set with a default database or do like I did above and run a maintenance job like that one to grab the server sessions, which I can here because it is in the Master database, which is my user’s default database. BTW, there is no sp_who, sp_configure and a lot of DBCC commands are not there in SQL Azure as well.

You must keep in mind that the current use cases for SQL Azure are Web-based database apps where developers need to have a SQL database and they normally do not want to or know how to get involved in the database at this level, which DBAs do all the time with SQL Server. SQL Azure support for system views is listed in that link for you see what is there.

2. Azure Data Sync. This is a Microsoft-provided tool that you can get from Microsoft.com and download it onto your local SQL Server systems. It uses the Data Sync Framework APIs, instead of BCP like the popular Codeplex SQL Azure tool called Azure Data Migration Wizard uses. You can run this tool to copy data and schemas from SQL Azure to another SQL Azure or SQL Server on-premises database, giving you a tool similar to SQL Server backup, for data.

3. Windows Scheduler. You can use PowerShell or even native local SQL Server client tools like BCP to perform one of the most common automated, maintenance DBA tasks: backup the database. SQL Azure does not support the native SQL Server backup/recover commands or capabilities, so you need to either use the database copy capability in SQL Azure to create your own backup, rely on the SQL Azure replicated database scheme, or extract the data with SSIS or BCP. You can schedule a BCP command in Windows Scheduler to connect to your SQL Azure database and backup the data into a local SQL Server database. To keep the schemas synched-up, you’ll need to use a mechanism like data-tier apps.

ex.: (run from local SQL Server box) bcp.exe mark.dbo.Table_1 out  “c:\temp\out.txt” -c -U username@azureserver -S azureserver.database.windows.net  -t “|”

4. Windows Azure Worker Role. If you want to keep the entire solution “in the cloud”, then you can use a Windows Azure worker role and export the data from SQL Azure using BCP. By using the SQL Server client tools in a worker role on Windows Azure, you can perform the entire data backup process in Azure in the cloud by scheduling the job to pull data from SQL Azure and store it into a BLOB storage from Windows Azure. There is a company called Cerebrata that offers pre-scripted PowerShell scripts to do exactly that: copy your data from SQL Azure and back it up in Windows Azure blob storage. Download it here.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s