Azure database on-going maintenance

PROBLEM:
Once your SQL Azure database is in production, what are the tasks required for ongoing maintenance?

SOLUTION:
1. Backing up SQL Azure data

For the latest information on SQL Azure backup and restore strategy and disaster recovery techniques, see Business Continuity in SQL Azure article in the SQL Azure MSDN library.
Native backup and restore as provided by on-premise SQL Server is not currently supported by SQL Azure.

SQL Azure natively supports a Database Copy capability. This creates a new database in SQL Azure which is a transactionally consistent copy of an existing database. This is accomplished using special syntax within the CREATE DATABASE command:

CREATE DATABASE destination_database_name

AS COPY OF [source_server_name.]source_database_name

For full discussion of this feature, see Copying Databases in SQL Azure.

The data in a SQL Azure database can be backed up on-premise, or to another SQL Azure database, using BCP, SSIS, or the .NET SqlBulkCopy API.
These approaches do not backup the structure and objects in the database, so you will need to generate build scripts to accompany the data if you wish to bootstrap the database into a new environment.
For a thorough discussion of the options for moving data in bulk out of (or into) a SQL Azure database, see this blog post and this discussion of BCP.

2. Defragmenting Indexes and other routine maintenance

In SQL Azure, as with on-premise SQL Server, indexes can become fragmented over time. They should be periodically checked and, if necessary, rebuilt. The syntax for the ALTER INDEX statement is restricted in SQL Azure, as compared to on-premise SQL Server, but the syntax for rebuilding indexes is identical. Reorganizing indexes is not supported by SQL Azure.
Both online and offline index rebuilds are supported by SQL Azure.

3. Keeping statistics current
There are no significant differences in statistics management between SQL Azure and on-premise SQL Server. The following are supported:

• UPDATE STATISTICS
• sp_updatestats
• sp_autostats

The ALTER DATABASE statement, however, cannot be used to change the database-level settings automatic statistics creation and updating. These settings both default to True (ON) for SQL Azure databases and they cannot be changed. If you truly find it necessary to suppress auto stats updates, use the sp_autostats stored procedure to control this behavior at the table/index level. In the vast majority of circumstances, you can just leave the auto update behavior unchanged.

4. Tracking Usage and Billing

Depending on the subscription model used by your customer, SQL Azure may be billed on a consumption basis. This makes it valuable to track consumption of bandwidth and space on an ongoing basis. Although there are reports within the Azure billing portal which help track this, customers often wish to do some kind of customized tracking of usage expense. SQL Azure provides DMV’s to enable this.

Bandwidth usage from your SQL Azure account is available in a DMV calledsys.bandwidth_usage. This DMV is only available in the master database. Since three-part names are invalid in SQL Azure, you will need to be connected to the master database to query the sys.bandwidth_usage view.

Database usage is available in a DMV called sys.database_usage. This DMV is also available only in the master database.

These bandwidth_usage and database_usage DMV’s are helpful, but they only report past usage. You may want to use T-SQL to check the size of a SQL Azure database as it is right now. Unfortunately, the sp_spaceused stored procedure is not available in SQL Azure. Also, sys.database_files is among those DMV’s not exposed by SQL Azure. Another DMV,sys.dm_db_partition_stats, can help here though. The following T-SQL query will report the approximate size of the database.

SELECT (8.0 * SUM(reserved_page_count)) / 1024 AS 'Database Size (MB)'

FROM sys.dm_db_partition_stats

This query reports on the current database only, so you will need to run it while connected to the specific database you are interested in. Note that permission on sys.dm_db_partition_stats is denied in the master database, even for administrators.

5. Scheduling Maintenance Tasks

Scheduling maintenance (e.g., index reorganization) will sometimes be required, especially for large or heavily accessed SQL Azure databases. Unfortunately, SQL Server Agent, the on-premise SQL Server scheduling engine, is not part of SQL Azure. This means there is no Azure-hosted method for scheduling tasks to occur.

For the time being, the only way to address this requirement is with an on-premise or non-Azure scheduling tool. A natural choice is, of course, an on-premise instance of SQL Server Agent. This works perfectly well and will make SQL Server DBA’s comfortable.

Using on-premise SQL Agent is OK for customers with existing SQL Server infrastructure. Those with no existing SQL licenses, however, may wish to avoid buying database software just to get a scheduling engine. In this case, Windows Task Scheduler can be used to invoke sqlcmd scripts, Powershell scripts, or custom executables which reach out to SQL Azure databases to perform maintenance tasks.

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!