Page History

...

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 SQLcompliance 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 SQLcompliance Agent using a script:

1. Define variables for the computers that host the SQLcompliance 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 SQLcompliance 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 SQLcompliance 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 SQLcompliance 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 SQLcompliance Agent deployed to your registered SQL Servers.

...