How to move Compliance Accelerator or Discovery Accelerator databases.

Description

There may be a need to move Compliance Accelerator (CA) or Discovery Accelerator (DA) databases on the same or different SQL Server. Possible reasons to require moving the CA or DA databases are, but not limited to:

1. Running out of available disk space on the drive hosting the database and / or transaction log files.
2. Moving to a SQL instance other than the instance currently hosting the databases.
3. Moving to a new SQL Server.

Complete the following steps to move the database(s) and the associated transaction log(s) to a new location on the same SQL Server instance, a different SQL Server instance, or a different SQL Server.

Note: If moving a DA Customer database that has Analytics enabled on any DA Case(s), follow the instructions in Technical Article How to move Discovery Accelerator and Analytics databases to a new logical drive on the same SQL server in the Related Articles section.

Pre-Move Steps for Compliance Accelerator 14.3 and newer

Before moving the CA Configuration and Customer databases to another SQL Server, follow these steps:

1. Verify the Database Master Key password is available. The Database Master Key password was entered when installing CA version 14.3 or newer, or upgrading to CA 14.3 or newer.
If the Database Master Key password is not available, continue to step 2 to add an additional password.
If the Database Master Key password is available, continue to step 3.

Note: If the Database Master Key password is not available, do NOT move the CA databases to another SQL Server until an additional password has been added in step 2 below, as doing so could render the data unreadable by Veritas Advanced Supervision/Veritas Advanced Surveillance (VAS). Veritas can not assist with retrieving a Database Master Key password from the SQL Server.

2. A Database Master Key can have multiple passwords. If the Database Master Key password that was entered when installing or upgrading to CA 14.3 or newer is not known, add an additional password as follows.

2.1. Copy the following query into a new query window focused on the CA Configuration database on the original SQL Server. Replace Configuration_database_additional_password with an additional password. This password must comply with the Windows Password Policy of the computer that is running the instance of SQL Server. Be sure to enclose the password in single quotes, as listed in the query. Then execute the query.

ALTER MASTER KEY
ADD ENCRYPTION BY PASSWORD = 'Configuration_database_additional_password'; -- Edit password here
CLOSE MASTER KEY;

2.2. If the Messages tab lists the following message, the password was correctly updated. Continue with the steps.

Commands completed successfully.

3. Execute the following query against the CA Configuration database. The query lists the KeyValueCipher information for each Customer database in the environment. Record this information along with the CA Database Master Key password for safekeeping.

SET NOCOUNT ON;
USE EVConfiguration;
GO
DECLARE @CustomerGUID uniqueidentifier;
DECLARE CustomerCryptoKeySel CURSOR FOR
SELECT CustomerGUID FROM tblCustomer WHERE Type = 50;
OPEN CustomerCryptoKeySel;
FETCH NEXT FROM CustomerCryptoKeySel INTO @CustomerGUID;
WHILE @@FETCH_STATUS = 0
BEGIN;
EXEC usp_CustomerCryptoKey_Sel @CustomerGUID;
FETCH NEXT FROM CustomerCryptoKeySel INTO @CustomerGUID;
END;
CLOSE CustomerCryptoKeySel;
DEALLOCATE CustomerCryptoKeySel;

How to move the databases

1. On the Compliance Accelerator (CA) or Discovery Accelerator (DA) server, stop the Enterprise Vault Accelerator Service (EVAMS).
2. On the SQL Server that will host the CA/DA database(s) to be moved, create one or more folder locations on the new drive resource(s) (i.e., E:\SQLData for the database file(s) and F:\SQLLogs for the transaction log file(s)).
3. Backup the database(s) to be moved (i.e., both the CA or DA customer and configuration databases), saving them to a location easily accessible by the SQL Server Management Studio on the SQL Server where the database(s) will reside.  Recommended steps to backup the database are:
   1. Open SQL Server Management Studio using an account with proper access to the database(s) to be backed up.
   2. Log into the SQL instance hosting the database(s) to be backed up.
   3. Expand the Databases folder in the left pane.
   4. Right click on the database to be backed up.
   5. Hover over the Tasks option to expand it.
   6. Select the Back Up... option in the list of Task options.
   7. On the General page -
      1. In the Source section -
         1. Ensure the Database: field is showing the name of the database to be backed up.
         2. Ensure the Backup type: option is set to Full.
         3. Ensure the Backup component: options have the Database radio button selected.
      2. In the Backup set section -
         1. Modify the Name: field contents if desired, or leave the default name in place.
         2. Enter a description of the backup in the Description: field if desired.
         3. Leave the Backup set will expire: option of After: radio button selected and leave the days specification at 0.
      3. In the Destination section -
         1. Ensure the Back up to: option's Disk radio button is selected.
         2. Open a new Notepad file.
         3. Enter into the Notepad file the paths and names of any files listed in the Back up to: panel.
         4. Save the Notepad file to have a copy of the backup file(s) path(s) and name(s) for safe keeping.
         5. Select any backup paths and files listed in the dialog pane below the Back up to: option.
         6. Click the Remove button to clear the backup path(s) and file(s) listed in the dialog pane.
         7. Click the Add... button.
         8. On the Select Backup Destination pane that will be displayed, click the ellipse [...] button to be able to browse to the location where the backup file is to reside.
         9. On the Locate Database Files pane that will be displayed, navigate to and select the drive and folder to contain the backup file.
         10. In the File name: dialog box, type the name to be assigned to the backup file, including the .bak file extension (i.e., EVBADACustomer1_Pre_Move_Backup_of_31_Dec_2013.bak).
         11. Ensure the Files of type: option is set to Backup Files(*.bak, *.trn).
         12. Click the OK button to save the path and specified file name and return to the Select Backup Destination dialog pane..
         13. Click the OK button to save the path and filename in the Destination on disk: File Name: option field.
         14. Click the OK button in the Back Up Database pane to begin the backup operation.
         15. Click the OK button when the backup has completed to acknowledge the backup has completed.
4. Database Move Method 1 using SQL Server Management Studio - detaching and attaching the database:
   1. Detach the database(s) to be moved using SQL Server Management Studio on the SQL Sever \ Instance currently hosting the database - 
      1. Expand the Databases folder in the Object Explorer panel if not already expanded.
      2. Right click on the database to be moved.
      3. Hover over the Tasks option to see a listing of available options appear.
      4. Select the Detach... option.
      5. In the Detach Database pane that will be displayed, place check marks in the check boxes for the Drop Connections and Update Statistics options by clicking on those check boxes.
      6. Click the OK button to detach the database.
   2. Move the database .mdf and, as appropriate, the transaction log .ldf files to the new location(s) using Windows Explorer or another file copy/paste/move application.
   3. Using SQL Server Management Studio on the SQL Server \ Instance that will be hosting the moved database, attach the database and log files to SQL, specifying their new location(s) - 
      1. Right click on the Databases folder in the Object Explorer panel.
      2. Select the Attach... option.
      3. Click the Add button in the Databases to attach: section.
      4. Navigate to the drive and folder containing the moved database's .mdf file.
      5. Select the moved database's .mdf file.
      6. Click the OK button to save the selection and move to the next pane.
      7. In the database details: pane -
         1. Verify the path to the database's .mdf file is correct for its new location.
         2. If the moved database's transaction log file was also moved, verify its path is correct -
            1. Locate the row containing the File Type column entry of Log.
            2. Look in that row's Message column.
            3. If the Message column is empty, the transaction log's ldf file exists in the specified path noted in the Current File Path column entry for the row.
            4. If the Current File Path column entry for the log file row is incorrect -
               1. Click on the ellipse [...] button next to that row's Current File Path column.
               2. Navigate to the drive and folder containing the moved database's transaction log's .ldf file.
               3. Select the moved database's transaction log's .ldf file.
               4. Click the OK button.
               5. An alternate method to change the path is to click on the cell containing the path and manually changing the path  (i.e., change 'E:\SQLData' to 'G:\SQLData') before clicking into another cell in the pane.
               6. Note the Message column for the log file row should be empty, indicating the log file was found in the specified path.
            5. When the correct paths for the database's .mdf and its transaction log's .ldf files are correct, click the OK button to complete the database attachment process.
            6. The  newly attached database will be listed at the bottom of the Databases folder contents until either the folder is refreshed or until SQL Server Management Studio is closed and re-opened.  Refresh the listing of databases to see the newly attached database listed alphabetically under the Databases folder in the Object Explorer panel.
5. Database Move Method 2 using SQL Server Management Studio - deleting and restoring the database:
   1. Delete the database using SQL Server Management Studio on the SQL Server \ Instance currently hosting the database - 
      1. Expand the Databases folder in the Object Explorer panel if not already expanded.
      2. Right click on the database to be moved.
      3. Select the Delete option.
      4. In the Delete Object pane that will be displayed -
         1. Ensure the Delete backup and restore history information for databases option check box contains a check mark if the database's .mdf and its transaction log's .ldf files are to be deleted (this option is checked by default).
         2. Click on the check box for the Close existing connections option to place a check mark in it.
         3. Click the OK button to delete the database and its files from the SQL Server.
   2. Restore the database using the backup file from Step 3 but changing the file locations for the database's mdf file and its transaction log's ldf file to the new location(s) where they will reside.
      1. Ensure the backup file from Step 3 above is available to be used in the SQL Server Management Studio on the SQL Server or SQL instance that will be hosting the moved database.
      2. Open SQL Server Management Studio if not already open on the SQL Server or SQL Instance that will be hosting the moved database.
      3. In the Object Explorer panel, expand the Databases folder.
      4. Ensure the database does not already exist.
         1. Delete the database if it already exists after backing it up and if allowed to delete it.
      5. Right click on the Databases folder.
      6. Select the Restore Database... option.
      7. In the Restore Database pane that will be displayed -
         1. Click on the From device: radio button in the Source for restore section.
         2. Click on the ellipse [...] button for the From device: option.
         3. Click the Add button in the Specify Backup pane that will be displayed.
         4. In the Locate Backup File pane that will be displayed, navigate to the drive and folder containing the backup file created in Step 3 above.
         5. Select the backup file created in Step 3 above.
         6. Click the OK button.
         7. Back in the Specify Backup pane, click the OK button to save the backup file selection and return to the Restore Database pane.
         8. In the Select the backup sets to restore: panel, click the Restore check box to place a check mark in it.
         9. In the Destination for restore section -
            1. Click the drop down arrow for the To database: option.
            2. If necessary, scroll to the bottom of the list where the name of the database to be restored should be listed.
            3. Select the name of the database to be restored by clicking on it.
         10. In the Select a page panel, click on the Options option.
         11. In the Restore the database files as: panel -
             1. Note the Restore As column entries for the database and log files' locations.
             2. If the database file location needs to be changed, click on the ellipse button [...] next to the database's file path.
             3. In the Locate Database Files pane that will be displayed -
                1. Navigate to and select the drive and folder to contain the database's .mdf file (i.e., E:\SQLData).
                2. In the File name: field, enter the name of the database's .mdf file including the .mdf file extension (i.e., EVBACACustomer1_Data.mdf).
                3. Click the OK button to accept the file path and name change.
             4. If the database's transaction log file path needs to be changed, repeat the above steps 5.b.vii.K.ii through 5.b.vii.K.iii.iii to specify the log file's drive, folder and file name.
             5. An alternate to changing the database and / or its log files' paths is to click on the cell in the Restore As column and manually change the drive and folder as needed, leaving the file name unchanged, then clicking elsewhere in the row or column.
             6. Ensure the radio button is selected for the Recovery state section's option Leave the database ready to use by rolling back uncommitted transactions.
             7. Click the OK button to begin the restore operation.
             8. If the restore is successful, a dialog pane will be displayed confirming the successful restore. Click the OK button to acknowledge the successful restore.  The database is now ready to use.
         12. The restored database will be listed at the bottom of the Databases folder contents until either the folder is refreshed or until SQL Server Management Studio is closed and re-opened.  Refresh the listing of databases to see the newly restored database listed alphabetically under the Databases folder in the Object Explorer panel.
6. If the SQL Server name or instance changed, refer to Technical Article How to modify the configuration files after moving the Compliance Accelerator or Discovery Accelerator databases to a different SQL server in the Related Articles section for instructions on how to edit the appropriate table in the CA or DA configuration database and the configuration files on the CA or DA server.  Skip this step if the configuration database is not being moved.
7. On the CA or DA server, start the EVAMS.

Post-Move Steps for Compliance Accelerator 14.3 and newer

After the CA Configuration and Customer databases have been moved to the new SQL Server and the Database Master Key password is known, follow these steps to re-add the Database Master Key password:

1. Copy the following query into a new query window focused on the CA Configuration database. Replace Configuration_database_password with the CA Database Master Key password (or the additional password if added above). Be sure to enclose the password in single quotes, as listed in the query. Then execute the query and review the Messages tab.

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Configuration_database_password' -- Edit password here
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY;

2.1. If the Messages tab lists the following error message, the password provided is not the correct password:

Msg 15313, Level 16, State 1, Line 6
The key is not encrypted using the specified decryptor.

Correct the password and re-run the step 1 query.

2.2. If the Messages tab lists the following message, the password was correctly updated. Continue with the steps.

Commands completed successfully.

3. To confirm the password update, execute the following query against the CA Configuration database. The query generates a random GUID and tests that GUID to see if it can be encrypted and decrypted using the CA key and certificate.

SET NOCOUNT ON;
PRINT 'Test to verify VASConfiguration_Key_AES_256_ProtectCustomerKey key functionality.
Any errors indicate the key and/or certificate are not functional.';
PRINT '';
If EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = 'VASConfiguration_Key_AES_256_ProtectCustomerKey') AND EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'VASConfiguration_Certificate_ProtectDBKey')
BEGIN;
-- If yes then test encryption/decryption using a random key
PRINT 'Generate a random key';
DECLARE @Key varbinary(32) = newid();
SELECT [RandomKey] = @key;
PRINT 'Open the VASConfiguration_Key_AES_256_ProtectCustomerKey key and decrypt using the VASConfiguration_Certificate_ProtectDBKey certificate';
OPEN SYMMETRIC KEY VASConfiguration_Key_AES_256_ProtectCustomerKey
DECRYPTION BY CERTIFICATE VASConfiguration_Certificate_ProtectDBKey;
-- Encrypt the cipher key
DECLARE @encrypted varbinary(1024)
SELECT @encrypted = ENCRYPTBYKEY(KEY_GUID('VASConfiguration_Key_AES_256_ProtectCustomerKey'), @Key)
SELECT [RandomKey] = @key, [RandomKeyEncrypted] = @encrypted;
PRINT 'Close the VASConfiguration_Key_AES_256_ProtectCustomerKey key';
CLOSE SYMMETRIC KEY VASConfiguration_Key_AES_256_ProtectCustomerKey;
-- Decrypt the cipher key
PRINT '';
PRINT 'Open the VASConfiguration_Key_AES_256_ProtectCustomerKey key and decrypt using the VASConfiguration_Certificate_ProtectDBKey certificate';
OPEN SYMMETRIC KEY VASConfiguration_Key_AES_256_ProtectCustomerKey
DECRYPTION BY CERTIFICATE VASConfiguration_Certificate_ProtectDBKey;
DECLARE @plain varbinary(32);
SELECT @plain = DECRYPTBYKEY(@encrypted);
-- Both key and decrypted key values should be same
SELECT [RandomKey] = @key, [RandomKeyEncrypted] = @encrypted, [RandomKeyDecrypted (should be the same as RandomKey)] = @plain;
PRINT 'Close the VASConfiguration_Key_AES_256_ProtectCustomerKey key';
CLOSE SYMMETRIC KEY VASConfiguration_Key_AES_256_ProtectCustomerKey;
END
ELSE PRINT 'VASConfiguration_Key_AES_256_ProtectCustomerKey does not exist in sys.symmetric_keys and/or VASConfiguration_Certificate_ProtectDBKey does not exist in sys.certificates';

3.1. If the Results tab shows NULL for the RandomKeyDecrypted and the Messages tab may list errors similar to the following, the password was not successfully updated or the password provided was not the correct password.

Open the VASConfiguration_Key_AES_256_ProtectCustomerKey key and decrypt using the VASConfiguration_Certificate_ProtectDBKey certificate
Msg 15581, Level 16, State 7, Line 12
Please create a master key in the database or open the master key in the session before performing this operation.
Close the VASConfiguration_Key_AES_256_ProtectCustomerKey key
Msg 15315, Level 16, State 1, Line 19
The key 'VASConfiguration_Key_AES_256_ProtectCustomerKey' is not open. Please open the key before using it.

3.2. If the Results tab lists the same information for RandomKey and RandomKeyDecrypted, the password was successfully updated. 

4. Execute the following query against the CA Configuration database. The query lists the KeyValueCipher information for each Customer database in the environment. Compare the results with the output of step 3 in the Pre-Move steps - the KeyGuid and corresponding KeyValueCipher values should match.

SET NOCOUNT ON;
DECLARE @CustomerGUID uniqueidentifier;
DECLARE CustomerCryptoKeySel CURSOR FOR
SELECT CustomerGUID FROM tblCustomer WHERE Type = 50;
OPEN CustomerCryptoKeySel;
FETCH NEXT FROM CustomerCryptoKeySel INTO @CustomerGUID;
WHILE @@FETCH_STATUS = 0
BEGIN;
EXEC usp_CustomerCryptoKey_Sel @CustomerGUID;
FETCH NEXT FROM CustomerCryptoKeySel INTO @CustomerGUID;
END;
CLOSE CustomerCryptoKeySel;
DEALLOCATE CustomerCryptoKeySel;

Notes:

1. The CA, DA and EV reports are stored as BLOBs (binary large objects) in a SQL Server Reporting Service (SSRS) server's ReportServer database.  If the CA, DA and / or EV databases are moving to a new SQL Server so the current SQL Server is to be decommissioned and that SQL Server also hosts SSRS, then the ReportServer database also needs to be moved to the new SQL Server that hosts the SSRS server.  This will need to be performed by the onsite SQL team.   For additional information, the onsite SQL team can refer to the following URL:  Sample Reporting Services rs.exe Script to Copy Content between Report Servers.  If the current SSRS server will remain in place, the reports should also remain intact and no further action should be required.

2. If the databases are restored to a newer SQL Server version, the databases' compatibility levels should be edited to match the Master database in the new SQL Server. See https://learn.microsoft.com/en-us/sql/relational-databases/databases/view-or-change-the-compatibility-level-of-a-database?view=sql-server-ver16 for more details. Please consult the onsite SQL team for guidance.