Deploy Database Objects to Multiple SQL Server Instances Using PowerShell
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.
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.
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.
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'; "
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.
The PowerShell script that deploys the desired object is called:
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:
- Right click on MSSQL-Instance-Object-Deployer.ps1 and select Run with PowerShell
- Open a command window and navigate to the folder where you saved the above files and run
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'
- Check out the other parts of this series
- Monitoring SQL Server with PowerShell Core Object Setup
- Monitoring SQL Server with PowerShell Instance Data Collection
- Monitoring SQL Server with PowerShell Instance Jobs Collection
- Monitoring SQL Server with PowerShell Instance Jobs Last Execution
- Monitoring SQL Server with PowerShell Instance Backups
- Monitoring SQL Server with PowerShell Instance Database and Database Files
About the author
View all my tips
Article Last Updated: 2020-11-19