How to deploy SQL database on several different SQL Server instances and databases

When work on a database in a development environment is completed, developers are required to send the new version in for testing. If the testing is done on one or two machines – this is not a problem. However, if there is a need to test the new version on 10 or 15 machines (with different operating systems, SQL Server versions, or service packs installed), this can be tedious.

Another situation where scripts need to be executed on multiple servers is if there are many databases for different clients that were all derived from the same database and share many common objects. Any changes made in the development must be pushed to all users.

Deploying multiple SQL scripts to multiple servers manually can be a very time-consuming task, where identical steps need to be repeated over and over again until all databases are updated. This is especially true during the development and testing phase when there are many changes that need to be deployed for testing each day.

The challenge is how to deploy multiple scripts to multiple servers quickly and safely.

Use SQLCMD

1. Create a batch file that executes the required scripts against different SQL Server instances.
   The batch file should look like this:

   :CONNECT <server1>\,<instance1>
   --SQL1 to execute
   GO
   :CONNECT <server2>\,<instance2>
   --SQL2 to execute
   GO
   

2. Execute the batch file using SQLCMD:
   

   SQLCMD –I D:\Scripts\BatchMultiple.bat

A disadvantage of this method is that it requires numerous manual and time-consuming actions, which are very error prone. Each time when new scripts are executed, the batch file must be modified or a new one has to be created.

Use Server groups

1. Start SQL Server Management Studio
2. In the View menu, select the Registered Servers option:
   

   

3. Right-click Local Server Groups and select the New Server Group:
   

   

4. Enter a new server group name and description:
   

   

5. Click the OK button
6. Right-click the Testing server group and select the New Server Registration:
   

   

7. Enter the Server name, authentication type, and credentials:
   

   

8. Test and save the Server registration
9. Repeat steps 6 to 8 to add more Server registrations
10. Right-click the Testing server group and select New Query
    

    

11. Paste the SQL for the execution against multiple databases into the Query Editor window:
    

    USE Adventureworks2014;
    ALTER TABLE TestTabM
    ADD colb int;
    

12. Press the F5 key to execute the script
    The following message will be shown:
    

    Command(s) completed successfully. (2 servers)

    SQL was executed against all Adventureworks2008 databases in the Server group (in this case – 2)

The disadvantages of this method are that the scripts can be deployed only against all registered servers, the database names have to be identical on all registered servers, and executing multiple scripts requires manually opening and pasting them into the Query Editor window one by one.

Use ApexSQL Build

ApexSQL Build is a SQL database deployment tool, which builds new databases or updates existing ones by packaging SQL scripts, script folders, database snapshots, and scripts under source control. It ensures error-free deployments and enables executing SQL scripts against multiple SQL Servers simultaneously by creating personalized lists of databases on different SQL Server instances and lists of SQL that should be executed.

1. Start ApexSQL Build
2. Select the Execute scripts on multiple databases action:
   

   

3. Click the Add button and choose the Add scripts option:

   

4. Select the scripts for the execution and click the Open button. The scripts are listed in the pane on the left:
   

   

5. Click the Save list in the menu to save the list of scripts:

   

6. To create a list of databases against which the scripts will be executed, select the Create new option from the Database deployment drop-down list on the right:
   

   

7. In the Edit database deployment list dialog, click the Add server button:
   

   

8. Enter the Server name, authentication type, and credentials. Select the Remember password option to avoid adding passwords each time when running the execution of scripts:
   

   

9. Expand each SQL Server node, select the databases, and click the Add button to add them to the Database deployment list:
   

   

10. When all databases are added to the list, click the OK button:
    

    

11. Click the Execute button:
    

    

    The execution progress bar will be shown:

    

    When the deployment is finished, a summary will be shown:

    

Deployment of multiple SQL on several different SQL Server instances and databases can be easy and error-free. Select a database on any SQL Server, select the scripts you want to execute, save them to lists, and reuse whenever you want.