Multiserver Administration

There are several tactics within SQL Server 2005 that enable you to administer multiple servers more easily. The focus of these tactics is to centralize your administration. This can be done by forwarding events to a central event management server. This allows you to centralize alert handling. Another optimization is to use master and target servers to create jobs on a single master server and have the jobs run on multiple target servers.

Using Token Replacement

SQL 2005 has some really nice new capabilities around SQL Agent job tokens. A token is a string literal that you use in your job steps (T-SQL scripts, CMDExec job steps, or Active Script). Before the job runs, SQL Agent does a string replacement of the token with its value. Tokens are usable only in SQL Agent jobs.

One of the tokens is (STRTDT). You might add the following in a T-SQL job step.

 PRINT 'Job Start Date(YYYYMMDD):' + $ESCAPE_SQUOTE(STRTDT)) 

If you capture the output, it should look like this:

 Job Start Date(YYYYMMDD):20060707 

Note

Tokens are case sensitive.

The following is a list of tokens that can be used in any job.

• (DATE): Current Date (YYYYMMDD)

• (INST): Instance name of the SQL Server. This token is empty for the default instance.

• (JOBID): SQL Agent job ID

• (MACH): Computer name where the job is run

• (MSSA): Master SQLServerAgent service name

• (OSCMD): Prefix for the program used to run CmdExec job steps

• (SQLDIR): SQL Server's install directory. The default install directory is C:\Program Files\Microsoft SQL Server\MSSQL.

• (STEPCT): The number of times this step has executed. You could use this in looping code to terminate the step after a specific number of iterations. This count does not include retries on failure. This is updated on each step run during the job, like a real-time counter.

• (STEPID): The job step ID

• (SVR): The server name of the computer running SQL Server, including the instance name

• (TIME): Current time (HHMMSS)

• (STRTTM): The job's start time (HHMMSS)

• (STRTDT): The job's start date (YYYYMMDD)

The following is a list of tokens that can only be used in a job that has been started from an alert. If these tokens are included in a job started any other way, the job will throw an error.

• (A-DBN): Database name where the alert occurred

• (A-SVR): The server name where the alert occurred

• (A-ERR): The error number associated with the alert

• (A-SEV): The error severity associated with the alert

• (A-MSG): The message text associated with the alert

The following token is available for use only on jobs run as the result of a WMI alert.

• (WMI(property)): This provides the value for the WMI property named property. $(WMI(DatabaseName)) returns the value of the DatabaseName property for the WMI alert that caused the job to run.

Beginning with SQL Server 2005 Service Pack 1 (SP1), you must use all of the tokens with escape macros. The purpose of this change is to increase the security around the use of tokens from unknown sources. Consider the following token, which you might have included in a T-SQL job step:

 Print 'Error message: $(A-MSG)' 

The T-SQL job step runs as the result of a user error (raiserror). A malicious user could raise an error like this one:

 Raiserror(''';Delete from dbo.Employee',16,1) 

The error returned would be:

 ';Delete from dbo.Employee 

The print message would be:

 Print 'Error message:';Delete from dbo.Employee 

You have just been attacked with a SQL injection attack. The delete statement will run if the T-SQL job step has permission.

Beginning with SP1, you must add an escape macro. Since the print statement uses single quotes, a SQL injection attack would close out the single quote and then insert its own SQL. To prevent this attack, you could double-quote any quote that comes in via the token. The escape macro ESCAPE_SQUOTE does this very thing. It is used like this:

 Print 'Error message: $(ESCAPE_SQUOTE(A-MSG))' 

Continuing the example, you would end up with:

 Print 'Error message:'';Delete from dbo.Employee 

You would get an error due to the unmatched quote, and the step would fail; you would be safe.

The following is a list of escape macros:

• $(ESCAPE_SQUOTE(token)): Doubles single quotes (') in the replacement string

• $(ESCAPE_DQUOTE(token)): Doubles double quotes (") in the replacement string

• $(ESCAPE_RBRACKET(token)): Doubles right brackets (]) in the replacement string

• $(ESCAPE_NONE(token)): The token replacement is made without changes. This is used for backward compatibility only.

You can also use these values directly if you ensure proper data types. The SQL script-looping job with tokens contains the following code that terminates a job step after it has executed five times. The top line converts the STEPCT token to an integer so it can be used in a comparison. Then the JOBID token for this job is converted to a binary 16 and passed to the sp_stop_job stored procedure, which can take the job ID of the job you wish to stop.

 IF Convert(int,$(ESCAPE_NONE(STEPCT))) >5   BEGIN   DECLARE @jobid binary(16)   SELECT @jobid =Convert(Uniqueidentifier,$(ESCAPE_NONE(JOBID)))   EXEC msdb.dbo.sp_stop_job @job_id = @jobid   END 

Imagine how you might use the alert-based tokens. You could create a SQL performance alert that fires when the <any database> transaction log gets greater than 80-percent full. Create a job with a T-SQL step like this:

 DECLARE @a varchar(100) SELECT @a = 'BACKUP LOG $(ESCAPE_SQUOTE(A-DBN))   TO DISK = ''\\UNCName\Share\$(ESCAPE_SQUOTE(A-DBN))\log.bak'' ' SELECT @a BACKUP LOG $(ESCAPE_SQUOTE(A-DBN))   TO DISK = '\\UNCName\Share\\$(ESCAPE_SQUOTE(A-DBN))\log.bak' 

Where UNCName is the name of the server where you wish the backup to be stored and Share is the share on the server. Make sure the job runs when the alert occurs. If the alert fires for AdventureWorks, the backup command will look like this:

 BACKUP LOG AdventureWorks TO DISK = \\UNCName\Share\\AdventureWorks\log.bak 

You would have to create the directory first and grant appropriate permissions to the proxy you use. You could create a CMDExec step, which creates the directory on the fly. Now a single log backup job can back up any transaction log. You might improve this by adding the date and time to the filename.

Event Forwarding

Where events and alerts are concerned, you can create operators and alerts on a single system and then have the other systems forward their events to your central alert handling SQL Server, who will respond to those alerts as necessary. The "Configuring SQL Server Agent" section of this chapter covers forwarding events.

You can set up operators on your master event management system. Create the jobs that will respond to the alerts. Then create alerts on the single master event management system to handle the event. The jobs you create can take advantage of SQL Agent tokens and can know what server and database the original event occurred on.

Using WMI

Windows Management Instrumentation (WMI) is a set of functions embedded into the kernel of Microsoft Operating Systems and Servers, including SQL Server. The purpose of WMI is to allow local and remote monitoring and management of servers. It is a standards-based implementation based on the Distributed Management Task Force's (DMTF) Web-Based Enterprise Management (WBEM) and Common Information Model (CIM) specifications.

WMI is a big initiative and could probably be an entire book all by itself. What you need to know is that WMI has many events for SQL Server. Search for WMI to get you started in Books Online, and you will discover the many, many events. You can create alerts on these events. There are Data Definition Language (DDL) events that occur when databases are created or dropped and when tables are created or dropped as an example.

WMI has a language to query these events called Windows Management Instrumentation Query Language (WQL). It is very much like T-SQL, and you will get comfortable with it immediately.

Browse Books Online for the kind of event you wish to monitor. Each event will have a list of attributes, just like a table has a list of columns. Using WMI, you can select the attributes from the event in an alert.

To create an alert, use SQL Serve Management Studio. In Object Explorer, open SQL Agent tree node, right-click Alerts, and choose New Alert. In the alert-type drop-down box, choose WMI Event Alert.

The namespace will be populated based on the server you are connected to and should look like this:

 \\.\root\Microsoft\SqlServer\ServerEvents\SQL2005 

The . represents the server name, which you can change, such as \\MYSQLSERVER\. The last node should be MSSQLSERVER for a default instance and the <instance name> for named instances. The instance I was running is called SQL2005.

In the textbox, you enter your WQL query, as shown here:

 SELECT * FROM DDL_DATABASE_LEVEL_EVENTS 

Or you could use this query:

 Select TSQLCommand from DDL_DATABASE_LEVEL_EVENTS 

To select only the TSQLCommand Attribute. There will be a parse when you click OK. If your namespace is incorrect, or the syntax or event/attribute names are incorrect, you will get a message immediately.

Then in your job you may use the WMI(attribute) event token, in this case:

 Print '$(ESCAPE_SQUOTE(WMI(TSQLCommand)))' 

To get events from a database, service broker notifications must be turned on for that database. To turn on service broker notifications for AdventureWorks, use the following syntax:

 ALTER DATABASE AdventureWorks SET ENABLE_BROKER; 

If your alerts are occurring but the text replacement for the WMI token is not being done, you probably need to turn on the service broker for your database.

Note

The service account that SQL Server Agent uses must have permission on the namespace and ALTER ANY EVENT NOTIFICATION permissions. This will be done automatically if you use SQL Server Configuration Manager to set up accounts. However, to adjust these settings manually, from the Run prompt, type wmimgmt.msc. An administrative dialog will return, allowing you set up permissions.

There is a test program for WMI on your server. To run it from the command line, type WBEMTest. It is installed in the WBEM directory of your Windows system directory.

Microsoft has an entire subsection of its Web site devoted to WMI. Just search for WMI on www.microsoft.com.

You might also create an alert for database mirroring. You need to know when a mirrored database's mirror status changes. This is exactly the kind of thing you would create a WMI alert for. This particular example and its implementation can be found at http://www.microsoft.com/technet/prodtechnol/sql/2005/mirroringevents.mspx.

Multiserver Administration - Using Master and Target Servers

SQL Server allows you to set up a master server (MSX). The master server can send jobs to be run on one or more target servers (TSX). The master server may not also be a target server, receiving jobs from another master server. The target servers receive and run jobs from a single master server, in addition to their own local jobs. You may have multiple master servers in your environment, but a target server is associated with a single master server. This is a very simple two-level hierarchy; a server is a master server, a target server, or neither. The language used to describe the process is military in character: You enlist target servers to add them, and they defect to go away.

Setting up servers is easy. In SSMS, right click the SQL Server Agent node, select Multiserver Administration, and choose Make this a Master. After the initial dialog box, you will see a box where you can provide the e-mail address, pager address, and Net Send location to set up a Master Server Operator. This operator will be set up on the master server and all target servers. This is the only operator who can be notified from multiserver jobs.

The next dialog box allows you to choose all of the target servers. The list includes the servers that you have registered in SSMS. You may add additional registrations by clicking the Add Connection button. Choose the servers that you want to be targets of this master, and Click next. SQL will check to ensure that the SQL versions of the master and targets are compatible. Close this dialog box. If the versions are not compatible, drop the target from the list; then continue. Later you can upgrade the target or master, so the versions are the same.

Target servers must connect to the master server to share job status information. The next dialog box allows you to have the wizard create a login on the target, if necessary, and grant it login rights to the master server. Once you have completed the setup, you can refresh your SQL Agent nodes and see the change. There is a note on the master server (MSX). There is also a note on the target server, as shown in Figure 5-19.

Figure 5-19

Now you can create jobs to be used at multiple target servers. Notice on the MSX that the jobs node is divided into two sections: local jobs and multiserver jobs. Right-click multiserver jobs, and select New job to create a simple job. You can see the new dialog box (Targets) in the New Job dialog, shown in Figure 5-20.

Figure 5-20

Now you can create jobs on the MSX server and have them run at one or many TSX servers. You should create a simple job right now. While doing this, be sure to go to the notifications page. The only operator you can notify is MSXOperator.

Creating multiserver jobs is a really nice way to manage a larger implementation without having to buy additional third-party products. No one on the TSX box can mess up your jobs, either. Use SSMS to connect to the target server as an administrator and look at the job properties for the job you just created and downloaded from the MSX. You can see the job, you can see the job history, and you can even run the job. You cannot delete the job, change the schedule, change the steps, or anything else. This job does not belong to you; it belongs to the MSX.

As you begin to think about how you might use this, be sure you consider the implications of a single job running on multiple servers. Any reference to directories, databases, and so on must be valid for all of the TSXs where this job runs. You can create a single backup share that all of the backups can use, for instance.

Since a job can start another job, you could also create a master job that has a single step that starts another job. This other job is created on each TSX and is specific to each TSX. This will allow you some customization, if necessary.

Back in SSMS, right-click the SQL Agent node on the master server, choose Multi Server Administration, and you can add target servers and manage target servers. Choose Manage Target Servers. In this dialog box, shown in Figure 5-21, you can monitor the status of everything. When you create a job for a target server, the job is automatically downloaded to the target server. If the unread instructions count does not go down to 0, as in this figure, poll the target server. This will wake it up to accept the instructions.

Figure 5-21

You can also see the details of downloaded instructions. This will show you details of when jobs are downloaded and updated, as in Figure 5-22.

Figure 5-22

Using the Post Instructions button in the Target Server Status dialog, you can synchronize clocks between the servers, defect target servers, set polling intervals, and start jobs. You can also start the job directly from the jobs node on the MSX or the TSX.

Job histories can be seen on the MSX for the job, just like any other job, except you cannot see the job-step details. To get the step details, view the job history from the TSX.

You can defect TSXs from the TSX SQL Agent node or from the Manage Target Servers dialog on the MSX. Once all of the TSXs have been defected, the MSX is no longer an MSX.