INSTALL BI360 How-To: Migrate databases to a new SQL Server

Summary:

This article details how to migrate databases to a new SQL server. The information provided here applies to the BI360 4x and older versions. If you are a Solver Private Host  (5x) user, please reference this kb article.

When migrating SQL databases from a development to a production environment, moving between environments, or restoring an environment, it may be necessary to ensure that the BI360 components reflect the new name to the SQL Server and instance.

Migrating databases can be done in SQL Server Management Studio by backing up the databases from the existing/old environment, and restoring the databases into the new environment. Migration of databases are usually a Database Administrator task and is not covered in the scope of this article.

Method:

The following tasks should be done to ensure that BI360 is correctly configured to reference the correct databases. These tasks should be done in the correct order, as the Data Source connections are dependent on the Repository connection.

Configuring the Repository Connection:

Every workstation (or the terminal server) will reference the OSR_Repository database to retrieve the data source connection information. To point to a new repository database the following two options are available:

1. In the Administration Tool, click on System Settings. In the Server text box, change the name of the SQL server and instance to the location where the OSR Repository is located. This will update the registry to the value in the next option, which is the manual method.
2. Manually specify the SQL server by opening the registry editor. Change the value of the RepositoryServer entry to the SQL server name. The entry is located in the following keys:

   a. For Windows x86: HKEY_LOCAL_MACHINE\SOFTWARE\OneStop Reporting\Settings
   b. For Windows x64: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\OneStop Reporting\Settings

   Note: Changes must be done for both registry keys for 64-bit operating systems.

Configuring Data Source Connections:

Since the data sources are specified in the Administration Tool, then stored in the OSR_Repository database, changes to the repository server name for each of the workstations or Terminal Server will not be the only changes needed. If the data sources have also been moved to a new server, then the data source connections will also need to be configured to reflect the new server information. Follow the steps below to point the application to the correct data sources. Note: This only needs to be done once, as all of the workstations that are pointed to this repository will automatically reflect the changes.

1. In the Administration Tool, click on System Settings.
2. Select the Integration Package, then click Setup.
3. In the Data Connections window, either click New to create a new connection for the data source, or select an existing connection from the list and click Change. Change the Database name accordingly.
   Note: Creating a new connection will require you to also create new data access filters in the Access Management section of the Administration Tool.

Properties:

BI360 4.x