How to Restart Multiple Remote SQL Servers via a Script on Central SQL Server

In this article, we would learn how to set up a simple script from a Central SQL Server and allow it to restart other machines in a SQL Server environment.

Restarting SQL servers:

Remote SQL ServerSQL Server patching might require server reboot either in the beginning or after the patching process. Though restarting a machine is not a big task, allowing a script to restart SQL Servers is a cool way to automate and save time.

Points to remember:

Before deploying the script, please make sure that the account with which the script would be running have all necessary access on target machines.

The First step in automation:

The first step in this automation is to create a table and add SQL Server names to it. Machines listed in this table would be restarted by the script. We also need to create a second table which will act as a log table. This table will hold the SQL Server name and the time it was restarted by the script. Now we are done with the initial setup.

Final script-No scheduling:

You can either run the Final script from a query window or run it through a SQL Job. However, do not schedule the script. Corrupted MDF files will not allow a SQL Server to start. Fix corrupted database files and try to start the SQL Server Service from Configuration manager.

Best Practice:

Restart SQL ServerIt is not a good practice to restart SQL Service or SQL Server unless it’s for Windows or SQL Server patching. There are myths that restarting a SQL Service or the entire SQL Server will improve the performance. That’s completely wrong. With proper server memory configuration and performing tuning methods, you can always keep your SQL Server performing better.

Modifying the script:

This script uses command line’s shutdown command. By default, this script sets 60 seconds as the timeout period for the system to restart. We are using force restart without which the script cannot restart the SQL Server if users are logged into the target servers. The comment “Restarting through TSQL” would be mentioned as the reason for a restart. You can change the timeout period and the comment as per your requirement.

Alternate route:

As an alternate way, if you had already setup Registered Servers, you can directly run this script on Registered Servers. However, before doing so, you should modify the script to fetch the local host name instead of reading the server name from the Table1 that we have created.

You can extend the script to monitor the ping status and identify if SQL Servers that were rebooted by the script are reachable. Thus we can track the time when the machine was rebooted and the time it was online once again. It is good practice to ensure that after a reboot all SQL Server services, databases are in their regular state. You can also remotely check SQL Server’s version to confirm that the patching had completed successfully.

Script

CREATE TABLE Servertable (servername VARCHAR(200))

INSERT INTO Servertable
VALUES ('Server1')
    ,('Server2')

CREATE TABLE RestartLog (
    servername VARCHAR(200)
    ,RestartTime DATETIME
    )

DECLARE @servername AS VARCHAR(200)
DECLARE @cmd AS VARCHAR(500)

DECLARE restartcursor CURSOR
FOR
SELECT servername
FROM master.dbo.Servertable

OPEN restartcursor

FETCH NEXT
FROM restartcursor
INTO @servername

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @servername

    SET @cmd = 'shutdown -r -f -m \' + @servername + ' -t 60 -c "Restarting through TSQL"'

    PRINT @cmd

    EXEC xp_cmdshell @cmd

    INSERT INTO RestartLog
    VALUES (
        @servername
        ,getdate()
        )

    FETCH NEXT
    FROM restartcursor
    INTO @servername
END

CLOSE restartcursor

DEALLOCATE restartcursor

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Outlook repair and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.