Running Queries with Central Management Servers

Imagine a scenario in which you have a number of SQL Server instances, wherein the database files are hosted on a SAN (Storage Area Network). You come into work one morning to find that there has been an issue with the production SAN, and you have to check all databases, on all instances, for corruption. This can be easily achieved, using central management servers.

Because you have a separate SAN for each environment, in each data center, you will only want to check the servers in the NTAM server group , as this is where the SAN issue occurred. Therefore, select New Query from the context menu of the NTAM server group. This will cause a new query window to be displayed, and we will use DBCC CHECKDBto check the consistency of all databases on both instances. This is demonstrated in Listing 3-1.

Listing 3-1. Checking Database Consistency

EXEC sp_MSforeachDB 'DBCC CHECKDB (?)' ;

This command executes the undocumented sp_MSForeachDB system -stored procedure. We pass the command that we want to run against each database into this procedure and use ? as a placeholder for the database name. This is automatically populated by sp_MSForeachDB for each database, in turn.

Figure 3-6 shows a partial output. You can easily see which results relate to each server, along with the execution context. You may also notice that the query status bar is pink instead of yellow and displays the name of the server group that we can pass the command against.

Figure 3-6. DBCC CHECKDB results

If you have discovered issues, you may decide to restore some databases. Because the integrity issues are caused by disk problems, you may want to check that all databases have been backed up with a checksum. To do this, we will query the sys.databasescatalog view , as demonstrated in Listing 3-2.

Listing 3-2. Checking Backup Page Verification Option

SELECT
        name
        ,recovery_model_desc
        ,page_verify_option_desc
FROM sys.databases ;

As you can see from the results, which are illustrated in Figure 3-7, an additional column has been added to the results set, so that the server that the row was generated from can be easily identified.

Figure 3-7. Results of checking page verification option

Evaluating Policies with Central Management Servers

To evaluate a policy against a server group, you will have to select Evaluate Policies from the context menu of the server group that you wish to evaluate the policy against. This will cause the Evaluate Policies dialog box to be displayed. Here, we can use the ellipse next to the Source field, to invoke the Select Source dialog box, which is illustrated in Figure 3-8.

Figure 3-8. Select Source dialog box

In this dialog box, you can either connect to an SQL Server instance, or you can select a policy that is stored in the file system. For this demonstration, we will evaluate the Last Successful Backup Date policy, which is stored in the file system.

Once we have navigated back to the Evaluate Policies dialog box (Figure 3-9) we can use the Evaluate button to evaluate the policy or policies that we have selected. The results will then be displayed on the Evaluation Results page of the dialog box.

Figure 3-9. Evaluate Policies dialog box

The Evaluation Results tab, which is illustrated in Figure 3-10, shows that our backups are not currently in a good way. The policy has discovered issues on both servers, and drilling through the View link to ESPROD2 displays the Results Detailed View dialog box. Here, we can see that the AdventureWorksDW2016 database has never been backed up. We should, of course, resolve this situation immediately, by taking backups of all affected databases.

Figure 3-10. Evaluation results

PowerShell is a fantastic tool for creating scripts that loop around multiple servers and running commands against each. It even has dedicated cmdlets for working with policies. If you have an ad-hoc requirement, however, and if the script you require can be written entirely in T-SQL, it is much more time efficient to use central management servers as a method of script execution.

Prerequisite Tasks

The first step in this configuration is to edit the registry of the target server(s), to allow jobs on target servers to download and match the name of the Proxy account(s) used to run the job’s steps, along with the job itself, from the master server. Failure to follow this step will mean that a Proxy account name cannot be matched, resulting in an error. We will configure this by setting the AllowDownloadedJobsToMatchProxyName REG_DWORD key to 1. This key can be found in the HKLMSoftwareMicrosoftMicrosoft SQL ServerMSSQL[VERSION NUMBER].[YOUR INSTANCE NAME]SQL Server Agent key, and we can change the value from within SQL Server, using an undocumented stored procedure called xp_regwrite, as demonstrated in Listing 3-3.

Listing 3-3. Configuring Target Jobs to Match Master Proxy Name

USE Master
GO

EXEC xp_regwrite
  @rootkey = N'HKEY_LOCAL_MACHINE'
 ,@key = N'SoftwareMicrosoftMicrosoft SQL ServerMSSQL13.MSSQLSERVERSQLServerAgent'
  -- If you have a default instance, the instance name is MSSQLSERVER by default
 ,@value_name = N'AllowDownloadedJobsToMatchProxyName'
 ,@type = N'REG_DWORD'
 ,@value = 1 ;

Next, we will have to decide if we wish to use SSL encryption for the communication between the MSX and TSX servers. Encryption is enabled by default, so if it is not required, it will have to be turned off. This means another change to the registry on the target servers. This time, we will set the MsxEncryptChannelOptionsREG_SZ key to 0. This key can be found in HKLMSOFTWAREMicrosoftMicrosoft SQL ServerMSSQL[VERSION NUMBER].[INSTANCENAME]SQLServerAgent key. To turn encryption off, you should run the script in Listing 3-4 on each of the target servers.

Listing 3-4. Disabling Encryption

EXEC xp_regwrite
  @rootkey='HKEY_LOCAL_MACHINE'
  ,@key = N'SoftwareMicrosoftMicrosoft SQL ServerMSSQL13.MSSQLSERVERSQLServerAgent'
  -- If you have a default instance, the instance name is MSSQLSERVER by default
  ,@value_name='MsxEncryptChannelOptions'
  ,@type='REG_DWORD'
  ,@value= 0 ;

Our next task will be to configure a Proxy Account on each instance. This will be used for accessing resources outside of the database engine and to allow us to reduce the security footprint of the account running the SQL Server Agent service. A proxy account maps to a credential, so we will create a credential that maps to a domain user named WinServiceAccount on ESSASMGMT1, by using the script in Listing 3-5. The credential should then be created on ESPROD 1 and ESPROD2.

Listing 3-5. Creating a Credential

USE master
GO

CREATE CREDENTIAL WinUserCredential
        WITH IDENTITY = 'ESASSWinServiceAccount'
        , SECRET = 'Pa$$w0rd'
GO

This script uses the CREATE CREDENTIAL command . The arguments that can be used with the WITH clause are described in Table 3-2.

We will now create the Proxy account, which can be used by the PowerShell subsystem, by using the script in Listing 3-6. The script should be run against the MSX server and all TSX servers. This script uses the sp_add_proxy and sp_grant_proxy_to_subsystem system-stored procedures, which are located in the MSDB database. The sp_add_proxy procedure is used to create the proxy and accepts the parameters listed in Table 3-3.

The sp_grant_proxy_to_subsystem system-stored procedure is used to assign the proxy account permissions to use specific subsystems and accepts the parameters detailed in Table 3-4.

The mapping of subsystem IDs to subsystem names can be found in Table 3-5.

Listing 3-6. Creating a Proxy

USE msdb
GO

EXEC msdb.dbo.sp_add_proxy
            @proxy_name = 'WinUserProxy'
          , @credential_name = 'WinUserCredential'
          , @enabled = 1 ;
GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem
        @proxy_name = 'WinUserProxy'
          , @subsystem_name = 'PowerShell' ;
GO

Configuration Tasks

Now that our prerequisite tasks are complete, we can begin to configure the ESASSMgmt1 instance as the MSX Server. We will first look at how to achieve this through the GUI, before reviewing the system-stored procedures that can be used to achieve the same results.

Selecting Multi Server Administration ➤ Make this a master server, from the context menu of SQL Server Agent in Object Explorer, will cause the Master Server Wizard to be invoked. After passing through the Welcome page of the wizard, you will be presented with the Master Server Operator page, where you will be prompted to enter an e-mail address, pager address, or NET SEND address that will be used to notify an operator of the success or failure of multi-server jobs. It is important to note that multi-server jobs can only have a single operator, so it makes sense to use a distribution list or group mailbox, as opposed to an individual. Pager and NET SEND notifications are deprecated and should not be used. The Master Server Operator page is illustrated in Figure 3-12.

Figure 3-12. Master Server Operator page

The Target Servers page of the wizard is illustrated in Figure 3-13. On this page, you will specify the instances that will be enlisted as TSX servers. Because both of the servers that we want to register are in the NTAM server group, we can select the NTAM server group and use the right arrow to move all servers within that group.

Figure 3-13. Target Servers page

At this point, the compatibility of the target servers will be checked. Once this is complete, the Master Server Login Credentials page will be displayed, as shown in Figure 3-14. On this page, we will specify if an account should be created that will be used to log in to the MSX server, to download jobs. If you do not select this option, you must ensure that the service account that runs the SQL Server Agent service on the TSX servers has appropriate permissions to the MSX server.

Figure 3-14. Master Server Login Credentials page

The complete Wizard page will provide a summary of the actions to be performed. After choosing Finish, the progress of each task will be displayed in real time.

To perform the tasks with T-SQL, we will enlist the server with the sp_msx_enlist and sp_add_operator system-stored procedures, which are located in the MSDB database. The parameters accepted by the sp_msx_enlist stored procedure are detailed in Table 3-6.

The sp_add_operator stored procedure accepts the parameters detailed in Table 3-7.

The sp_msx_enlist procedure is also used to bring TSX servers under management, after the MSX server has been enlisted. Listing 3-7 demonstrates how these system-stored procedures can be used to configure the ESASSMgmt1 instance as an MSX Server and enlist ESProd1 and ESProd2 instances as TSX servers.

Listing 3-7. Enlisting the MSX Server

:connect ESASSMgmt1

USE MSDB
GO

EXEC msdb.dbo.sp_add_operator
                  @name = 'MSXOperator2'
                , @enabled = 1
                , @email_address = '[email protected]' ;

EXEC sp_msx_enlist
        @msx_server_name = 'ESASSMgmt1'
, @location = 'NTAM - MGMT network block' ;

:connect ESProd1

USE MSDB
GO

sp_msx_enlist
        @msx_server_name = 'ESASSMgmt1'
, @location = 'NTAM - PROD network block' ;

:connect ESPROD2

USE MSDB
GO

sp_msx_enlist
        @msx_server_name = 'ESASSMGMT1'
, @location = 'NTAM - PROD network block' ;

Creating the Job in Object Explorer

We will create the job using the New Job dialog box, which can be accessed by selecting New Job, from the context menu of SQL Server Agent, then Jobs ➤ Multi-Server Jobs in Object Explorer . On the general page of the wizard, which is illustrated in Figure 3-15, we will provide a name and owner for the job, as well as specifying the category. Because we are creating a multi-server job, the category will default to Uncategorized (Multi-server). We will also ensure that the Enabled check box is selected, so that our job will be enabled on creation.

Figure 3-15. General page

On the Steps page of the wizard, we will use the New button to invoke the New Job Step dialog box. The General page of the New Job Step dialog box is illustrated in Figure 3-16. On this page, we have provided a name for the job step, specified the subsystem that will be used in the job step, selected the account that will be used to run the job step, and, finally, pasted our PowerShell script into the Command window.

Figure 3-16. New Job Step—General page

The Advanced page of the New Job Step dialog box allows you to define the flow control of your job, which is useful when you have multiple steps, especially if some steps perform error handling for previous steps. Essentially, configuring the options on the Advanced page for each job step allows you to create a decision tree for your job steps.

As our job will have a single step, our requirements on this page are minimal. As shown in Figure 3-17, we have specified that if the step succeeds, the job will quit, returning success, and if it fails, the job will quit with failure. Because our PowerShell script generates a terminating error if any SQL Server–related services are not running, the job will also fail. We can then configure the MSXOperator to be notified. We have also specified that the output of the job step should be recorded in the job history.

Figure 3-17. New Job Step—Advanced page

On the Schedules page of the New Job dialog box, we will use the New button to invoke the New Job Schedule dialog box and create a new schedule, which will run on a daily basis. As illustrated in Figure 3-18, we will name the schedule Daily and configure it to run at midnight every night.

Figure 3-18. New Job Schedule dialog box

As shown in Figure 3-19, we will use the Notifications page of the New Job dialog box to configure the MSXOperator operator to be notified in the event that the job fails. This makes managing service checks easy, as the operator will only be notified in the event of a service not being in a running state.

Figure 3-19. Notifications page

On the Targets page of the New Job dialog box, we will specify which of our TSX servers we want the job to run against. We would like this specific job to run against both of our target servers, so we will select both of them, as shown in Figure 3-20.

Figure 3-20. Targets page

Creating the Job with T-SQL

To use T-SQL to create the CheckServices job , we will have to use the sp_add_job, sp_add_jobserver, sp_add_job_step, and sp_add_job_schedule system-stored procedures. All of these procedures can be located in the MSDB database.

The sp_add_job procedure accepts the parameters detailed in Table 3-8.

We will use this procedure to create the CheckServices job , using the script in Listing 3-9.

Listing 3-9. Creating the Job

USE msdb
GO

EXEC  msdb.dbo.sp_add_job
                  @job_name = 'CheckServices'
                , @enabled = 1
                , @notify_level_email = 2
                , @category_name = '[Uncategorized (Multi-Server)]'
                , @owner_login_name = 'sa'
                , @notify_email_operator_name = 'MSXOperator' ;
GO

The sp_add_jobserver system-stored procedure accepts the parameters detailed in Table 3-9.

We will use the sp_add_jobserver procedure to target our job against the two TSX servers. The procedure has to be executed for every server the job will be targeted against. This is demonstrated in Listing 3-10.

Listing 3-10. Targeting the Job

USE msdb
GO

EXEC msdb.dbo.sp_add_jobserver
                  @job_name = 'CheckServices'
                , @server_name = 'ESPROD1' ;
GO

EXEC msdb.dbo.sp_add_jobserver
                  @job_name='CheckServices'
                , @server_name = 'ESPROD2' ;
GO

The sp_add_job_step system-stored procedure accepts the parameters detailed in Table 3-10.

We will use the sp_add_jobstep procedure to add our PowerShell job step, by using the script in Listing 3-11.

Listing 3-11. Creating the Job Step

USE msdb
GO

EXEC msdb.dbo.sp_add_jobstep
                  @job_name = 'CheckServices'
                , @step_name = 'CheckServicesRunning'
                , @step_id = 1
                , @on_success_action = 1
                , @on_fail_action = 2
                , @subsystem = 'PowerShell'
                , @command = ' # Return SQL Server services to a variable,
            #if they are not running

$services = Get-Service | where{$_.Name -like "*SQL*" -and $_.Status -ne "Running" }

# If the variable is non-empty, write a list of services
            # that are not running and force the script to fail

IF ($services.Length -gt 0)
{
        write-warning "Warning! The following Services are not running"
        foreach ($service in $services)
{
write-warning $service.Name
        }
write-error "Please check services and start as required" -EA stop
}'
                , @flags = 32
                , @proxy_name = 'WinUserProxy' ;
GO

The sp_add_jobschedule procedure accepts the parameters detailed in Table 3-11.

Table 3-12 describes how to calculate the value for @frequency_interval, based on the value supplied for @frequency_type.

We will use the sp_add_jobschedule procedure to create our Daily schedule and attach it to our job. The script in Listing 3-12 demonstrates this.

Listing 3-12. Creating the Job Schedule

USE msdb
GO

EXEC msdb.dbo.sp_add_jobschedule
                  @job_name = 'CheckServices'
                , @name = 'Daily'
                , @enabled = 1
                , @freq_type = 4
                , @freq_interval = 1
                , @freq_subday_type = 1
                , @freq_subday_interval = 0
                , @freq_relative_interval = 0
                , @freq_recurrence_factor = 1
                , @active_start_date = 20160101
                , @active_end_date = 99991231
                , @active_start_time = 0
                , @active_end_time = 235959 ;
GO

Limitations of the PowerShell Job Step

The PowerShell subsystem is very powerful and allows your SQL Server Agent jobs to interact with external resources in a cleaner way than using the cmdexec subsystem . It does have limitations, as compared to using PowerShell interactively, or via scheduling scripts via alternative means.

Firstly, cmdlets such as write-host and write-debug cannot be used. This is because SQL Server Agent uses the SQLPS.exe stub to execute the commands, and this does not use a console. To return information to the job step, you have to use write-output or write-error.

Assemblies are not pre-loaded. Therefore, if you have to use an assembly, such as Microsoft.AnalysisServices or Microsoft.SharePoint.PowerShell, they must be manually loaded.

If your job step uses the sqlps module, a process will be spawned that consumes around 20MB of memory. Therefore, you should limit the number of concurrent jobs that run PowerShell job steps, to avoid performance issues.

Additionally, there is no scripting environment provided. This, combined with the limitations mentioned, means that you should create and test your scripts using the PowerShell ISE before testing them again inside your SQL Server Agent job.