How To Execute a Script on Multiple Instance with Registered Server

dbtut October 17, 2018 MSSQL

Sometimes you may want to execute a script in more than one instance.

It is a solution to connect to each instance individually, but with Registered Server it is possible to do this job in one click.

In the next section of the article, we will create the Registered Server step by step.

And with the help of the Registered Server, we will run a script on more than one instance.

First, we click Registered Servers from View on SSMS as below.

Then we click on New Server Group via Local Server Group.

We give a name for the server group that we create in Group name. I’ve written something like this.

Sunucu1\Instance1,Sunucu2\Instance1

You can give the name you want. You can also write something in the Group description section.

Then, right-click the server group we created and click New Server Registration.

Then, in the Server name section, we write the name of the instance to which we will connect as shown in the following screen.

And we also make the necessary selection from the authentication part. I mentioned the types of Authentication in my article “How To Install SQL Server“.

I’m adding all the servers I want to run my script at once, and I’m running my script at the same time by clicking new query as follows.

For example, I have 2 servers running Always On. One is primary and the other is secondary. I need to restart one of the servers.

I must failover all the ag (availability group) ‘s in the server to the other server. I explained this process in my article “SQL Server Availability Group Failover Process“.

But before failover, I need to see the synchronization status of all the availability groups on these two servers. I explained the details of this process in my article “SQL Server Availability Group Databases Synchronization Status“.

If you read these two articles, the process will be clearer in your mind. I used the following script in my article “SQL Server Availability Group Databases Synchronization Status“.

SELECT
AG.name AS [AvailabilityGroupName],
dbcs.database_name AS [DatabaseName],
  CASE
WHEN dbrs.synchronization_state =0 THEN 'Not synchronizing'
WHEN dbrs.synchronization_state =1 THEN 'Synchronizing'
WHEN dbrs.synchronization_state =2 THEN 'Synchronized'
WHEN dbrs.synchronization_state =3 THEN 'Reverting'
WHEN dbrs.synchronization_state =4 THEN 'Initializing'
  END AS AGState,
  ar.failover_mode_desc,
  ar.availability_mode_desc,
ISNULL(dbrs.is_suspended, 0) AS [IsSuspended]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
where dbcs.is_database_joined=1
ORDER BY AG.name ASC, dbcs.database_name

SELECT

AG.name AS [AvailabilityGroupName],

dbcs.database_name AS [DatabaseName],

CASE

WHEN dbrs.synchronization_state =0 THEN 'Not synchronizing'

WHEN dbrs.synchronization_state =1 THEN 'Synchronizing'

WHEN dbrs.synchronization_state =2 THEN 'Synchronized'

WHEN dbrs.synchronization_state =3 THEN 'Reverting'

WHEN dbrs.synchronization_state =4 THEN 'Initializing'

END AS AGState,

ar.failover_mode_desc,

ar.availability_mode_desc,

ISNULL(dbrs.is_suspended, 0) AS [IsSuspended]

FROM master.sys.availability_groups AS AG

LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates

ON AG.group_id = agstates.group_id

INNER JOIN master.sys.availability_replicas AS AR

ON AG.group_id = AR.group_id

INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates

ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1

INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs

ON arstates.replica_id = dbcs.replica_id

LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs

ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id

where dbcs.is_database_joined=1

ORDER BY AG.name ASC, dbcs.database_name

This script gives the synchronization status of all databases in an instance.

Assume that there are 10 instances in these two servers. In this case, I need to run the above scripts for each instance individually.

However, with the help of registered server, we can get the result we want by running this script at once.

If you run the script above with a registered server, you can see a long screen. You can modify the script so that it returns only the problematic databases.

The following query returns the following values:

databases that are not synchronized yet,

databases that failover mode are not automatic,

databases that availability mode are not synchronous

If the result is not coming, you are ready for failover.

SELECT
AG.name AS [AvailabilityGroupName],
dbcs.database_name AS [DatabaseName],
  CASE
WHEN dbrs.synchronization_state =0 THEN 'Not synchronizing'
WHEN dbrs.synchronization_state =1 THEN 'Synchronizing'
WHEN dbrs.synchronization_state =2 THEN 'Synchronized'
WHEN dbrs.synchronization_state =3 THEN 'Reverting'
WHEN dbrs.synchronization_state =4 THEN 'Initializing'
  END AS AGState,
  ar.failover_mode_desc,
  ar.availability_mode_desc,
ISNULL(dbrs.is_suspended, 0) AS [IsSuspended]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
where (dbcs.is_database_joined=1) and (ar.failover_mode_desc<>'AUTOMATIC' 
or ar.availability_mode_desc<>'SYNCHRONOUS_COMMIT' or
dbrs.synchronization_state<>2)
ORDER BY AG.name ASC, dbcs.database_name