How To Execute a Script on Multiple Instance with Registered Server

 

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“.

 

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.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *