Migrating the epiCentre database from one SQL instance to another

Follow instructions for each step of database migration procedures using Microsoft SQL Server Management Studio:

Step 1: Inform Users about the Test

Before you begin the database migration procedure, it's important to inform your users about the upcoming test. This will help minimize disruption and ensure that they are aware of potential downtime during the migration. Consider sending out an email or a notification through your organization's communication channels with the following information:

• Date and time of the migration: Specify when the migration will take place. It's a good practice to schedule migrations during low-traffic periods to minimize user impact.

• Expected downtime: Inform users about the expected duration of the database migration and any potential service interruptions.

• Backup and data safety: Assure users that data will be backed up before the migration, and precautions are in place to ensure data safety.

• Point of contact: Provide contact information for any questions or issues that may arise during the migration.

Step 2: Export Database

In this step, you will export the database that you intend to migrate. Follow these instructions:

1. Open SQL Server Management Studio (SSMS).

2. Connect to the SQL Server instance where the database is located.

3. In the Object Explorer, expand the Databases node to view the list of databases.

4. Right-click on the database you want to export and select "Tasks" > "Export Data."

5. The SQL Server Import and Export Wizard will open. Follow the wizard's steps to configure the export settings:

   • Select the data source (your current database).

   • Choose the destination (e.g., a flat file or another SQL Server database).

   • Specify mapping options, data transformation if needed, and any advanced settings.

   • Review and confirm the export settings.

6. Execute the export by clicking the "Finish" button. The wizard will export the data to the specified destination.

Step 3: Import Database

After exporting the database, you can import it into the target environment. Here's how:

1. Open SQL Server Management Studio (SSMS).

2. Connect to the SQL Server instance where you want to import the database.

3. In the Object Explorer, right-click on the "Databases" node and select "Restore Database."

4. In the Restore Database window:

   • Specify the destination database name.

   • Under the "Source" section, choose "Device" and then click the ellipsis (...) to select the backup file created in Step 2.

   • Review and adjust other settings as needed.

5. Click the "OK" button to start the database import process.

Step 4: Fix Orphaned Users

Orphaned users may occur when migrating databases. These are users in the database without a corresponding login. for detailed instructions for this step please go to: How to fix orphaned SQL Server users after migration - epiCentre Knowledge Base - AHSRI (atlassian.net)
OR
fix orphaned users, follow these steps:

1. In SQL Server Management Studio (SSMS), connect to the target database.

2. Open a new query window.

3. Run the following script to identify orphaned users:

   USE YourDatabaseName;
   EXEC sp_change_users_login 'Report';

   Replace "YourDatabaseName" with the actual name of your database.

4. The query will display a list of orphaned users. To fix them, use the following command for each orphaned user:

   USE YourDatabaseName;
   EXEC sp_change_users_login 'Auto_Fix', 'UserName';

   Replace "YourDatabaseName" with the database name and "UserName" with the orphaned user's name.

5. Verify that all orphaned users have been fixed by re-running the sp_change_users_login 'Report' query.

Step 5: Change Database Name in Config File

In this step, you will locate the configuration file, update it with the new database server name, and then replace the old configuration file on each epiCentre installation. Follow these instructions:

1. Locate the Configuration File:

   • Navigate to the exact location of the configuration file, which is C:/epicentre in the folder named epiCentre.

   • Look for the configuration file named epiCentre.config.

2. Send Config File and New Database Server Name to Eppoc Team:

   • Email the configuration file (epiCentre.config) to the Eppoc team.

   • Include the new database server name in your communication to ensure they have all the necessary information for the update.

3. Receive Altered Config File:

   • Wait for the Eppoc team to make the necessary changes to the configuration file and send it back to you.

4. Replace the Old Config File on Each epiCentre Installation:

   • Once you receive the altered configuration file from the Eppoc team, follow these steps for each epiCentre installation that needs the database name update:

     • Locate the existing epiCentre.config file in the epiCentre installation directory.

     • Make a backup copy of the old epiCentre.config file for safety.

     • Replace the old epiCentre.config file with the new one received from the Eppoc team.

     • Ensure that the file is named exactly epiCentre.config.

   • Repeat this process for each epiCentre installation that relies on the updated database configuration.

By following these steps, you will ensure that the epiCentre application is configured to connect to the new database server after the migration, and all installations will use the updated configuration file.