Take Away

We have covered a lot of important ground in this chapter, because maintaining the system is one of your most important jobs. Because there are many things to maintain and audit, automation forms the key for being effective at that job. In this section, I give you an idea of "what you need to do when" as part of your plan.

In many installations, SQL Server 2005 may be used in smaller implementations. If you are in charge of an entire data infrastructure, however, you need to be able to check multiple servers in many locations. Microsoft has a mechanism to help you control maintenance and reporting across the enterprise. In the second part of this section, I show you how to tie several servers together for centralized control.

Maintenance Checklist

Certain tasks, such as backing up the database or performing a rebuild on an index, have schedules dictated by factors other than the calendar. You will develop a schedule for your backups and index maintenance, but it will not have the granularity of a single day or be something that everyone has in common. What most sites do have in common for daily and weekly schedules are checks and audits.

Your situation might also dictate a different schedule from what I propose here. The important thing is to use this checklist as a spring-board for your own checklist.

Daily

Run backups (full, differential, filegroup, and log).

Check services and instances to ensure they are running.

Check SQL Server 2005 Logs (engine and SQL Agent).

Check Windows event logs for SQL Server or SQL Server Agent

entries.

Verify backup and maintenance steps.

Weekly

Check Jobs history and any logging they create.

Audit for security changes.

Performance tuning.

Check Microsoft and other Web sites for updates and issues.

Quarterly

Audit for database growth (monthly if the growth is rapid).

Perform a test disaster recovery.

On Demand

Run backups (full, differential, filegroup, and log).

Perform index and table maintenance.

Add and edit users.

Restore databases.

Create databases.

Tune the system.

Apply service packs.

Enabling Multiple-System Automation

In many environments, you will be asked to manage several servers. Even if you are in a large environment where you do not manage all the servers, it helps to group the servers into regions or even by purpose.

You can set the SQL Server Agent to control and report the status of jobs on other servers. The server that will act as the main focal point is called the master server. The process involves creating an operator and starting the SQL Server Agent as the master server. You can then "enlist" other servers into the scheme so that they report up to the master server.

It is as easy as running another wizard. Open SQL Server Management Studio and right-click the SQL Server Agent in the Object Browser. Select Multi Server Administration and then Make this a Master… from the menu that appears.

You will be asked to set primary contact information for a new operator, called MSXOperator, on your server. You will then be guided through selecting the servers you want to report to this one. The wizard will check the version and connection information on the "target" server and then create the new operators on both systems. The systems will be enlisted and you can now report back to the master server.