Executive Summary:
Microsoft's internal Database Operations team makes SQL Server automation scripts available to database administrators (DBAs). SQL Server automation scripts make up a suite of SQL Server maintenance jobs. SQL Server maintenance jobs provide DBAs with automated tasks that they can run on their servers on a regular basis.
|
Microsoft makes a lot of its internal IT operations open to the public so that
people can see how this world-class company runs its IT business. At Tech-Ed
2007, Mark Pohto, who heads Microsoft's internal Database Operations team, showed
me some new SQL Server DBA automation scripts his team uses. Together, these
scripts constitute a suite of SQL Server Agent maintenance jobs the team uses
to perform backups, automate database maintenance, improve performance, mitigate
risk, and improve the disaster-recovery process. But the coolest thing about
these scripts is that they're free for you to download and use.
The scripts automate tasks that almost everyone using SQL Server should regularly
perform on their servers. Because these are SQL Server Agent jobs, you can easily
install them by running a T-SQL script. Each job runs a related stored procedure
that's installed when the job is created.
After you install the package, you'll have the following scripts:
- SQLBackupAll performs a full backup of all databases except those you specifically
exclude. You can specify the desired backup location, whether to do a third-party
backup or a native backup, whether to exclude system or read-only databases,
which backup threads to use, and job priority.
- SQLBackupDiffAll performs a differential backup of all unexcluded databases
by using either the built-in backup utility or Quest Software's LiteSpeed
for SQL Server. This job is disabled by default because some environments
don't use differential backups.
- SQLBackupTranAll backs up transaction logs for all unexcluded databases.
The job includes parameters to dynamically define the desired backup location
and specify whether to use native backup or a third-party backup tool.
- SQLBackupChecker looks for databases that haven't been backed up within
a specified number of hours and alerts you if it finds any.
- SQLDBCCAll performs a battery of database consistency checks on all system
and user databases.
- SQLIndexDefragAll defragments indexes on all unexcluded SQL Server databases.
You can also use this job to defragment indexes within a specific SQL Server
instance, database, or table. This job is disabled by default.
- SQLUpdateStatistics updates the statistics for the tables in all unexcluded
databases. You can also use this job to update index statistics within a specific
SQL Server instance, database, or table. This job is disabled by default.
- SQLCleanupMsdbBackupHistory cleans up the backup-history information in
the msdb database.
- SQLCycleErrorLog periodically cycles the SQL Server error log to keep it
at a size that's manageable for viewing through the native tools.
The download for these scripts includes a Word document that explains the parameters
and options for each script and provides examples of how to use it. For example,
to defragment all the indexes in all databases, you can type the following at
the query prompt in SQL Query Analyzer or SQL Server Management Studio:
EXEC msdb.. SQLIndexDefragAll:
Parameters let you specify the database, the table, and the percent fill factor;
the sample command above uses the default values (null, null, and 10.0, respectively).
Installing the automation scripts is easy. First, make sure that your SQL Server
2005 or SQL Server 2000 server has SQL Server Agent running on it. Then run
the command
C:\> Jobsinstall.cmd servername
where servername is the name of your server. To uninstall the jobs,
use the command
C:\> Jobsuninstall.cmd servername
You can download the automation scripts at http://download.microsoft.com/download/4/0/C/40CBAD9A-D990-450B-8785-F288CEBFB448/AITScripts.zip
End of Article
