Background

A few months ago, I spoke to one of our SQL Safe Backup customers that had a very large SQL Server environment. In an effort to streamline the deployment of the SQL Safe Backup Agents throughout his environment, he created a Powershell script to execute the deployment. I, personally, thought this was great and wished that I knew Powershell well enough to write such a script myself.

Recently, I had some spare time available and thought to myself "why don't I give it a shot?!" So I concluded that I would spend some time to write a script to deploy agents. However, I thought the deployment of the SQL Safe Backup Agents worked pretty well and decided to work on something that was a bit more difficult, which was the deployment of the SQLcompliance Agents in a cluster environment.

The deployment of the SQLcompliance Agent in a cluster environment isn't truly difficult, it's just a number of manual steps that have to be performed. The number of steps can be a bit deterring for some users. I thought that writing a script to automate some of those steps would ease some of those pains users are encountering.

The Journey

Given that I'm a complete newbie, I had to resort to several tutorials that I was able to find online. One valuable resource that I found was a script from Microsoft's Script Center. The script basically copies files to the target server(s) and installs it. After spending some time reviewing the script and learning from it, I came to the decision that it wasn't what I wanted. There were two problems that I found with the script.

1. One of the parameters is the file path to the file that you want to have installed on the target machine. The script, however, would copy the entire directory to the target server(s). This meant that users would have to create a directory and copy the installer into that directory to avoid sending several unneeded files to the target server(s).
2. Deploying the SQLcompliance Agent into a cluster environment basically consists of 3 steps.

   – Installing the SQLcompliance Cluster Configuration Console to each node.
   – Using the SQLcompliance Cluster Configuration Console to deploy the SQLcompliance Agent.
   – Configuring a cluster resource for the SQLcompliance Agent.

   In this case, the script that I had found would only be useful for one of those steps.

At this point, I decided that I would write my own script rather than using the one from the Script Center. I did, however, continued to use the script as a learning resource, among other online sites.

I'm not a developer by trade so it took me a couple of days to fully understand how to use loops, if/else statements, try/catch statements, and etc. There were times where I thought I had a solid script but then found out my loop was broken. I also struggled for a while trying to figure out how to hide outputs of certain commands. After a grueling three days and a few head banging against the wall sessions, I finally had a usable script!

The End Result

In the end, I decided to only script out 2 of the 3 steps required when deploying the SQLcompliance Agent to a clustered environment. I left out the step of configuring the cluster resource. I felt that changing settings in a cluster environment would be better left to a human being that was familiar with the environment. The script did require that PS Remoting is enabled on the target server(s) and is executed by a user with local admin rights on all of the target servers.

The script does require a few parameters such as the target server names (comma delimited), username & password (to be used by the deployed SQLcompliance Agent service), file paths, and etc. All of the parameters are documented in the script itself, which I've attached as a TXT file (this blog does not allow me to attached it as a PS1 file). While I do have some examples also documented within the script, I went ahead and copied them below.

Example 1:

.\SQLCM_AgentDeployment_Cluster.ps1 -ComputerList SAKURA-N1,SAKURA-N2 -Instance SAKURA\SQL2016 -CollectionServer CollectionServerHostname -Username MyDomain\Username -Password myPassword -TraceDirectory F:\Trace -InstallerPath "C:\Program Files\Idera\SQLcompliance\SQLcomplianceClusterSetup-x64.exe"

Example 2:

.\SQLCM_AgentDeployment_Cluster.ps1 -ComputerList SAKURA-N1,SAKURA-N2 -Instance "SAKURA,1433" -CollectionServer CollectionServerHostName -Username "MyDomain\Username" -Password "myPassword" -TraceDirectory "F:\Trace Directory" -AssemblyDirectory "F:\Assembly"

I hope that this script finds its way to some of you that might be struggling with the steps. Keep in mind that you will still have to manually create the cluster resource for the service itself. However, it should still cut down the time it takes to complete the deployment process.

EDIT 5/30/2019

I've updated the script a bit to check the .NET requirements on the target servers.