Performing a Full Database Migration – SQL Server

Topics:

Exporting The Data

Because most SQL Server DBAs use the SQL Server Enterprise Manager, this document outlines the steps to follow using that tool.

Create database backups of the source Accela and Jetspeed databases by individually right-clicking each database and selecting Tasks->Backup. This operation opens the Backup dialog as seen below:



The import items to verify includes:

  • A check that you set the Backup Type to Full and that the Backup Component equals Database

  • A verification of the destination location and destination file name

On the Options tab, you can optionally select Verify the Backup.

Repeat this process for the JETSPEED database.

Preparing the Target Database

The Civic Platform installer creates the database tables, into which you migrate the configuration data and transaction data, in the target database.

  1. Install Civic Platform to the target database (see the Civic Platform Installation Guide).

    Note:

    The Civic Platform version for the target environment must match the SP version number of the Civic Platform version for the source environment.

Importing the Data

If importing into an existing Civic Platform environment, record the environment settings before you import or restore into the target database (Copying and Setting Accela Environment Parameters).

Importing the data

  1. At the target site, copy the source backup file into a location accessible to SQL Server.

  2. Right-click the Databases tree item, on the right-hand side of the SQL Server Management Studio window, and choose “Restore Database.”

    This operation opens a screen where you can select the backup file and specify a name for the restored database.

  3. Perform this restoration for both the ACCELA and JETSPEED databases.

Synchronizing User Accounts

If the SQL Server instance already contains an Accela and Jetspeed SQL login, re synchronize those logins with the migrated data by performing the following query:

use <MyAccelaDatabaseName> 
go 
EXEC sp_change_users_login ’Update_one’, ’<MyAccelaDatabaseName>’, ’<MyAccelaDatabaseName>’ 
go 
use <MyJetspeedName> 
go 
EXEC sp_change_users_login ’Update_one’, ’<MyJetspeedName>’, ’<MyJetspeedName>’ 
go
Note:

If the previous script fails, manually delete users from the Security section of each database, then add the users back under the main Security section of the database environment. Set the User Mappings to the correct database with the correct permissions.