Deploy Database Objects to Multiple SQL Server Instances

Problem

When working with dozens or hundreds of SQL Server instances, there’s a good chance that you might need to deploy a new object to each instance. For example, you might need to create a new login for a specific application that will connect to each of the instances. If you don’t have registered servers, then it might be a bit of a challenge to achieve that in an efficient way. However, if you are familiar with my work, you probably know that I have a PowerShell script that can help you with that.

Solution

Based on the core structure of the monitoring series of the PowerShell scripts I have contributed to the MSSQLTips community, I will present a PowerShell script that you can use to deploy a database object (or several) in a set of instances of your choosing.

Prerequisites

In order to use this module, you need to create the core objects found here.

Make sure that the account that will connect to each instance to execute the desired T-SQL code has enough privileges to perform such action(s). Otherwise you might run into issues, but you should be able to detect those within the monitoring.ErrorLog table.

Deploying Object

Inside the PowerShell script, you will find a variable called "$objectDeployerQuery" that contains the T-SQL instruction that will be executed against each instance returned at "$instanceLookupQuery". For demonstration purposes, I have specified the creation of a new database called "ObjectDeployer", so that you can get a feeling of what to expect from the execution of this script.

This is what the particular section of code looks like:

$objectDeployerQuery = "
CREATE DATABASE ObjectDeployer;
"

If you’d like to create an instance login in all of the desired instances, then this is what the $objectDeployerQuery variable would look like:

$objectDeployerQuery = "
CREATE LOGIN test_login WITH PASSWORD = 'Pass123';
"

Important Notes:

You have to make sure that the T-SQL code inside the $objectDeployerQuery variable is crafted to do exactly what you want to do.

Depending on the actions to be applied and how you crafted the T-SQL code, you might run into issues if you execute the script multiple times, so be careful.

Make sure that the account executing the script has enough privileges to perform the desired action in each instance, or else you might end up running into issues.

PowerShell Script

The PowerShell script that deploys the desired object is called:

MSSQL-Instance-Object-Deployer.ps1

The script has some validations that will help you check if some key elements are missing for the script to run successfully. For instance, it will confirm that the inventory.MasterServerList table exists and that it has at least 1 active instance registered to be able to have something to work with.

If you have followed along the other modules, you will notice that we have been storing all objects in "C:\temp", but you can use any folder you want. If you make a change to the central folder location, you will need to edit the first line in the following PowerShell script to specify the new folder location.

How to Use

Navigate to the folder where you created the files and you can run the PowerShell script as follows:

Option 1

Right click on MSSQL-Instance-Object-Deployer.ps1 and select Run with PowerShell

Option 2

Open a command window and navigate to the folder where you saved the above files and run

powershell "C:\temp\MSSQL-Instance-Object-Deployer.ps1"

Check Object Deployment

To follow along with the example I have put in the script, here’s what I’m able to see after executing the script against my 2 instances:

As you can see above, the new database "ObjectDeployer" was successfully created in both instances.

Checking for Errors

To check for errors query the monitoring.ErrorLog table using the following query:

SELECT *
FROM monitoring.ErrorLog
WHERE script = 'MSSQL-Instance-Object-Deployer'

If you’d like to know the SQL Server instance that got the errors, you would have to issue the query like this:

SELECT
   CASE WHEN msl.instance = 'MSSQLSERVER' THEN msl.server_name 
        ELSE CONCAT(msl.server_name,'\',msl.instance) 
   END AS instance, 
   e.script,
   e.message,
   e.error_timestamp
FROM monitoring.ErrorLog e
JOIN inventory.MasterServerList msl ON msl.serverId = e.serverId
WHERE e.script = 'MSSQL-Instance-Object-Deployer'

Download Scripts

Download the scripts for this module

Next Steps

Check out the other parts of this series

Resources

Community

Subscribe