Configure the SQLcompliance Agent connection

To ensure you successfully continue auditing your registered SQL Servers within IDERA SQL Compliance Manager, configure each SQL Compliance Manager Agent to communicate with the new Collection Server.

Apply this update by changing the Server value of the following registry key on the computer that hosts the registered SQL Server instance:

HKEY_LOCAL_MACHINE\SOFTWARE\Idera\SQLcompliance\SQLcomplianceAgent

You can manually apply this update at each registered SQL Server or automate this update using a script. This procedure demonstrates how to use a script, such as a Visual Basic script, to configure the SQL Compliance Manager Agent to communicate to the new Collection Server.

Use this procedure to develop a script that suits your environment. You can run a script locally to update one agent at a time, or remotely to update all agents at the same time.

To configure the SQL Compliance Manager Agent using a script:

1. Define variables for the computers that host the SQL Compliance Manager Agent and the new Collection Server. For example, if you plan to run a Visual Basic script locally on the computer that hosts the SQL Server, your script may include the following code: 
   ' Define the SQL Compliance Manager Agent server
strComputer = "SQLServer01"
strNewCollectionServer = "CollectionServer02"
 
2. Declare the SQL Compliance Manager Agent and registry objects. For example, if you plan to run a Visual Basic script locally on the computer that hosts the SQL Server, your script may include the following code:
   ' Get the SQLcompliance Agent and registry objects
Set objComplianceAgent = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\cimv2:Win32_Service='SQLcomplianceAgent'")
Set objReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\default:StdRegProv")


3. Stop the SQL Compliance Manager Agent Service. For example, if you plan to run a Visual Basic script locally on the computer that hosts the SQL Server, your script may include the following code:
   ' Stop the SQLcompliance Agent Set flgStopStatus = objComplianceAgent.ExecMethod_("StopService")


4. Change the registry key. For example, if you plan to run a Visual Basic script locally on the computer that hosts the SQL Server, your script may include the following code:
   ' Change the location of the Collection Server in the registry
const HKEY_LOCAL_MACHINE = &H80000002
strRegAgentPath = "SOFTWARE\Idera\SQLcompliance\SQLcomplianceAgent"
strServerValName = "Server"
objReg.GetStringValue HKEY_LOCAL_MACHINE, strRegAgentPath, strServerValName, strOldServer
objReg.SetStringValue HKEY_LOCAL_MACHINE, strRegAgentPath,strServerValName, strNewCollectionServer
WScript.Echo "Changed collection server from " & strOldServer & " to " & strNewCollectionServer
 
5. Start the SQL Compliance Manager Agent Service. For example, if you plan to run a Visual Basic script locally on the computer that hosts the SQL Server, your script may include the following code:
   ' Restart the SQLcompliance Agent Set flgStartStatus = objComplianceAgent.ExecMethod_("StartService")


6. Using an administrator account, run your script to update each SQL Compliance Manager Agent deployed to your registered SQL Servers.