Managing Multi SQL Server Environments

20 Aug 2019 40387 views 0 minutes to read Contributors

Managing a large SQL Server estate made easy

When you have to manage a large SQL Server estate starting from 10+ SQL Servers, you do not want to perform repetitive tasks on every SQL Server.

SQL Server has the ability of managing necessary DBA tasks from 1 single server in a couple of ways:

MSX/TSX

Main purpose managing multi server jobs. There are more possibilities with msx/tsx. Multi server jobs are jobs defined on 1 server (MSX) to be executed on multiple sql servers (TSX).

Multiserver administration requires that you set up a master server (MSX) and one or more target servers (TSX). Jobs that will be processed on all the target servers are first defined on the master server and then downloaded to the target servers.

Select multiple targets for the job:

On a target the job will look like this:

Any schedule attached to a job is honouring the local time of that SQL Server.

Targets do NOT need to be in the same domain.

Read more here: https://docs.microsoft.com/en-us/sql/ssms/agent/create-a-multiserver-environment

Central Management Server

Central management servers store a list of instances of SQL Server that is organized into one or more central management server groups. Actions that are taken by using a central management server group act on all servers in the server group. This includes connecting to servers by using Object Explorer and executing Transact-SQL statements and Policy-Based Management policies on multiple servers at the same time.

Read more here: https://docs.microsoft.com/en-us/sql/ssms/register-servers/create-a-central-management-server-and-server-group

There are many more techniques to be able to manage a large SQL Server Estate.