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.
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.
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!
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.
.\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"
.\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.
Powered by IDERA