Moving SiteProtector's RealSecureDB database to new SQL server

Overview
This document describes the steps for moving the RealSecureDB database from one SQL server to another, and how to configure the SiteProtector Application Server, Event Collectors, and Agent Managers to use the new SQL server. This walk through is constructed based on the following assumptions:

• The database is being moved from like to like deployment types. By this, we mean that the SiteProtector database is originally installed on its own server that is separate from the Application Server and is being moved to a different separate server.
• The new SQL Server has a new Netbios name and new IP address.
• The Windows versions are the same type (64-bit versus 32-bit).

The process is divided into sections based on the tasks that are involved to make it easier to follow. This process takes SiteProtector offline so you should perform these actions during a maintenance window. The length of time that is required depends on the size of your database file, as it needs to be copied over to the new SQL Server.


FAQ

Q. What versions of SQL Server have the instructions been tested on?

• The instructions have been tested on SQL 2008, SQL 2008 R2, SQL 2012, and SQL 2014.

1. Open Windows Services and stop the following services on the servers where SiteProtector components and the old database are installed.
   
   Important: If your installation is spread across multiple servers, ensure that the services are stopped on each server where they are present.
   • issDaemon
   • SiteProtector Application Server Service
   • SiteProtector EventViewer Service (This service does not exist for SP 3.1.1 or higher)
   • SiteProtector Sensor Controller Service
   • SiteProtector Web Server
   • SQL Server Agent
2. Open SQL Server Management Studio on the original SQL server and display Object Explorer by pressing F8.
3. Right-click the RealSecureDB database under the Databases section and select Tasks > Detach.... Select the Drop Connections option and click OK. The database should no longer appear in SQL Server Management Studio.
4. Copy the .mdf and .ldf files from the original SQL Server to the new SQL Server. The default location for the files is \Program Files\ISS\SiteProtector\Site Database\Data. You need to create this directory path on the new server.
5. Open SQL Server Management Studio on the new SQL server. Right-click Databases and select Attach.... Add the RealSecureDB to the new SQL server, being sure to make the sa account the owner of the database. Note that you might have to scroll to the right to locate the Owner column.

PART III: Preparing the new SQL Server

This section covers the creation of the various SQL accounts that are used by SiteProtector and the creation of a System DSN entry that is required by the Application Server.

1. Open the Microsoft KBA How to transfer logins and passwords between instances of SQL Server . Follow the "Method 3" instructions in the KBA to move the following logins to the new database server:
   • EventCollector_HOSTNAME
   • AgentManager_HOSTNAME
   • RPDMLogin
   • IssApp
   1. Optional: You might see an error stating that the User, Group, or Role already exists in the database. If this error is present, run the following query and then re-add the user per the previous step:
      
      Use RealSecureDB;
exec sp_dropuser 'Name_of_account_with_issue';
2. In SQL Server Management Studio on the new SQL server, go to Security > Logins. Right-click the IssApp account and select Properties. In the Server Roles section, ensure that both bulkadmin and public are selected then click OK.
3. Build the SQL Agent Jobs, assign the permissions to the IssApp user, and set trustworthy to ON by running the following query:
   
   Use RealSecureDB;
Alter database realsecuredb set Trustworthy on;
EXEC Iss_BuildJobs;
EXEC Iss_AssignIssAppToJob;
4. Download the Link_DB_to_new_server.sql file and open it in SQL Server Management Studio on the new SQL server. Follow the instructions at the beginning of the script to modify its contents, then execute the script. This updates the database record in SiteProtector to have the correct IP address and DNS name of the new SQL Server.
   
   Link_DB_to_new_server.sql

PART IV: Configuring the Application Server to point to the new SQL Database Server

1. Locate the following file on the Application Server:
   
   \Program Files\ISS\SiteProtector\JavaEE\Geronimo2.1.8\repository\iss\SPDataSource\1.0\SPDataSource-1.0.rar\rar\META-INF\geronimo-ra.xml
2. Open the file in a text editor and change the ConnectionURL parameter so that it includes the new computer name or IP address like the following:
   
   <c:config-property-setting name="ConnectionURL">jdbc:jtds:sqlserver://<New_SQL_IP_or_hostname>:1433/RealSecureDB;ssl=off</c:config-property-setting>
   
   Save and close the file when finished.
3. Create a C:\tempdeploy directory on the Application Server. Copy the following folder into that directory:
   
   \Program Files\ISS\SiteProtector\JavaEE\Geronimo2.1.8\repository\iss\SPDataSource\
4. Rename the original directory (not the copy in C:\tempdeploy) to be \SPDataSource_backup.
5. Open a Command Prompt and run the following command, changing the paths to match your environment. Take care when you are copying the command into the window. It is a single command but might have wrapped on your screen due to its length.
   
   \Progra~1\ISS\SiteProtector\JRE1.7.0_SR6_FP1\bin\java.exe -jar "C:\Program Files\iss\SiteProtector\javaee\Geronimo2.1.8\bin\deployer.jar" --offline deploy C:\tempdeploy\SPDataSource\1.0\SPDataSource-1.0.rar\rar
   
   Note: If you have multiple JRE sub-directories in the \SiteProtector directory, you can identify the correct one by checking the information in the HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ISS key in the Windows Registry.
   
   When the command completes you will see the following messages:
   
   Deployed iss/SPDataSource/1.0/rar
Deployed org.apache.geronimo.framework/jee-specs/2.1.8/car
Deployed org.apache.geronimo.framework/rmi-naming/2.1.8/car
Deployed org.apache.geronimo.configs/j2ee-server/2.1.8/car
Deployed org.apache.geronimo.framework/j2ee-security/2.1.8/car
Deployed org.apache.geronimo.configs/transaction/2.1.8/car
6. Delete the following temporary and backup directories that were used in the previous steps:
   • \Program Files\ISS\SiteProtector\JavaEE\Geronimo2.1.8\repository\iss\SPDataSource_backup
   • C:\tempdeploy
7. Open regedit and go to the following key:
   
   32-bit:
   
   HKEY_LOCAL_MACHINE\SOFTWARE\ISS\SiteProtector\Application Server
   
   64-bit:
   
   HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ISS\SiteProtector\Application Server
8. Modify the SQL Server Name entry to reflect the correct IP address.
9. Open Administrative Tools > Data Sources (ODBC) on the new DB server.
   
   Important: SiteProtector uses 32-bit ODBC connections even when running on a 64-bit version of Windows. If your Windows installation is a 64-bit version, be sure to open the 32-bit ODBC dialog. This can be accomplished by opening the version that is labeled 32-bit under Administrative Tools, if present. Otherwise, you can open the 32-bit ODBC dialog by going to \Windows\SysWOW64\odbcad32.exe.
10. Select the System DSN tab, then select IssADReconciler and click Configure.
11. On the first screen of the wizard, enter the IP address for the new SQL Server and then click Next. Note that if you are using a named instance in SQL other than the default instance, you also need to specify this. The form is <IP address>\<Instance Name>.
12. Select the option to use the appropriate authentication type for your environment. Note that if you select SQL Server Authentication, you should clear the Connect to SQL Server check box. This is used for testing the connection, which is not necessary here. Click Next.
13. Enable the Change the default database to field and verify that it is set to RealSecureDB, then click Next.
14. Click Finish. A configuration summary dialog box will appear on-screen. Click OK in the configuration summary dialog to return to the ODBC window.
15. Select SiteProtectorAppServer and click Configure.
    
    Note: Some users may not have this DSN on their Application Server system. This is normal depending how the system was originally installed. If the DSN does not exist, create it by doing the following:
    1. Click Add in the System DSN tab.
    2. Select SQL Server as the driver and click Finish.
16. In the Name field, enter SiteProtectorAppServer if this is a newly created DSN. In the Server field, enter the IP of the new DB server. If you are using a named instance in SQL other than the default instance, you also need to specify this. The form is <IP address>\<Instance Name>. Click Next.
17. Select the option to use the appropriate authentication type for your environment. Note that if you select SQL Server Authentication, you should clear the Connect to SQL Server check box. This is used for testing the connection, which is not necessary here. Click Next.
18. Enable the Change the default database to option and enter RealSecureDB in the field, then click Next.
19. Click Finish. A configuration summary dialog box will appear on-screen. Click OK in the configuration summary dialog to return to the ODBC window, then click OK again to close the ODBC window.

PART V: Configuring the Event Collectors to point to the new SQL Database Server

You need to follow these instructions on every Event Collector.

1. In Windows, open Administrative Tools > Data Sources (ODBC).
   
   Important: SiteProtector uses 32-bit ODBC connections even when it is running on a 64-bit version of Windows. If your Windows installation is a 64-bit version, be sure to open the 32-bit ODBC dialog. This can be accomplished by opening the version that is labeled 32-bit under Administrative Tools, if present. Otherwise, you can open the 32-bit ODBC dialog by going to \Windows\SysWOW64\odbcad32.exe.
2. Select the System DSN tab, then select RSNTEventCollector and click Configure.
3. On the first screen of the wizard, enter the IP address for the new SQL Server and then click Next. Note that if you are using a named instance in SQL other than the default instance, you also need to specify this. The form is <IP address>\<Instance Name>.
4. Select the option to use the appropriate authentication type for your environment. Note that if you select SQL Server Authentication, you should clear the Connect to SQL Server check box. This is used for testing the connection, which is not necessary here. Click Next.
5. Enable the Change the default database to field and verify that it is set to RealSecureDB, then click Next.
6. Click Finish. A configuration summary dialog box will appear on-screen. Click OK in the configuration summary dialog to return to the ODBC window, then click OK again to close the ODBC window.

PART VI: Configuring the Agent Managers to point to the new SQL Database Server

You need to follow these instructions on every Agent manager.

1. Navigate to \Program Files\ISS\SiteProtector\Agent Manager and open the rsspdc.ini file in a text editor.
2. Locate the following line:
   
   dbSource=Old_SQL_IP_or_hostname
   
   and change the value to:
   
   dbSource=New_SQL_IP_or_hostname
   
   Save and close the file when finished.

PART VII: Start the SiteProtector services and verify that SiteProtector is functioning

1. Open Windows Services and start the following services on the servers where SiteProtector components are installed:
   • issDaemon
   • SiteProtector Application Server Service
   • SiteProtector EventViewer Service (This service does not exist on SP 3.1.1 or higher)
   • SiteProtector Sensor Controller Service
   • SiteProtector Web Server
2. Open the SiteProtector Console and verify that you are able to log in successfully.
3. Change to the Agent view and verify that your SiteProtector components are listed as Active.
4. Expand Ungrouped Assets in the navigation pane on the left and select the IP address range that covers the address of the SiteProtector Database server. Add the range if it is not present.
5. Drag the SiteProtector Database component into the top-level group on the left.