1. Support
  2. Knowledge base
  3. 100016654

How to move the Enterprise Vault SQL databases

Article: 100016654
Last Published: 2024-02-20
Ratings: 24 4
Product(s): Enterprise Vault

Description

This article describes how to move the Enterprise Vault (EV) databases to a different SQL server or a new version of MS SQL Server for EV. 



To watch a video on this process see below.

Note: At minutes 1:33 and 1:58, there is a tab called Indexers that is not shown. This is new in versions 10 and above. We are only concerned with the Database tab for this tutorial.

 

An occasion can exist where the Enterprise Vault databases must be moved from one SQL Server to another.  Examples include, but not limited to:
  1. Upgrade of the SQL Server version (ex.: SQL Server 2008 R2 to SQL Server 2012 R2).
  2. Upgrade of the SQL Server hardware.
  3. Distribution of databases to allow for more processing power to be applied to each database.
The information below will assist in moving the Enterprise Vault databases from one SQL Server to another.

Note: If Compliance Accelerator and / or Discovery Accelerator are also installed in the environment and their databases are to be moved to a different SQL Server and / or SQL Server instance and / or SQL Server TCP Port, refer to the following articles in the Related Articles section:
  • How to move Compliance Accelerator or Discovery Accelerator databases
  • How to modify the configuration files after moving the Compliance Accelerator or Discovery Accelerator databases to a different SQL server
  • How to move Discovery Accelerator and Analytics databases

 

This process will consist of the following steps. 

Before you begin, It is important to note that Master DB collation on the new SQL server should match the EV Databases collation to avoid any issues. Please refer 100023860 for more details.

 
Step 1 – Determine location of databases
 
This step is to be performed on the Enterprise Vault Server.
 
1. Take note of the Directory, Fingerprint and Vault Store databases.

Directory database
a. Right click Enterprise Vault at the root level and select Properties
b. Click Change Directory SQL Server
c. Take note of the SQL Server listed.
 
Fingerprint database
a. Right-click the Vault Store Group and click Properties
b. Click the Database tab
c. Take note of the SQL Server listed.
 
Vault Store database
a. Right-click the Vault Store and click Properties
b. Click the Database tab.
c. Take note of the SQL Server name and database name.
 
2. Stop all Enterprise Vault services
 
 
Step 2 – Configure permissions and protocols
 
This step is to be performed on the new SQL Server.
 
1. Confirm the Vault Service Account is a member of the Local Administrators group.
2. Confirm the Vault Service Account is assigned the dbcreator role.
            a. Open SQL Server Management Studio
            b. Expand Security > Logins > right-click the Vault Service account and select Properties.
            c. Click Server Roles and confirm dbcreator is checked.
3. Grant the Vault Service account "Select" permissions on the following msdb tables:
  • sysjobs
  • sysjobschedules
  • sysjobservers
  • sysjobsteps.
Note: Use the following script to grant select permissions to the Vault Service Account over the MSDB tables. Replace DOMAIN\VSA with the Vault Service Account:

USE MSDB
GRANT SELECT ON dbo.sysjobs TO "DOMAIN\VSA"
GRANT SELECT ON dbo.sysjobschedules TO "DOMAIN\VSA"
GRANT SELECT ON dbo.sysjobservers TO "DOMAIN\VSA"
GRANT SELECT ON dbo.sysjobsteps TO "DOMAIN\VSA"
 
4. Assign the database role SQLAgentUserRole to the Vault Service account.
5. Confirm TCP/IP and/or Named Pipes are enabled.
            a. Open SQL Server Configuration Manager
            b. Expand SQL Server Network Configuration and click Protocols for <instance name>.
            c. Confirm Named Pipes and/or TCP/IP are enabled under the Status.
6. Enter the following script:
 
USE Master
GRANT VIEW SERVER STATE TO "domain\vsa_account"
GRANT ALTER ANY LOGIN TO "domain\vsa_account"
GRANT VIEW ANY DEFINITION TO "domain\vsa_account"
 
Step 3 – Backup and copy the databases
 
This step is performed on the old SQL Server.
 
1. Backup the Enterprise Vault databases. This includes any database whose name has EnterpriseVault or EVVS at the start. It tends to be easiest to use the built-in backup feature of SQL Management Studio.
 
Note: Dedicated backup solutions with redirected restore, such as Veritas's NetBackup and Backup Exec, will also work fine but are beyond the scope of this guide. Consult the backup software's documentation for help with restoring database backups to a new host.

2. Copy the backup files to the new SQL server.
 
Step 4 – Restore the databases
 
This step is to be performed on the new SQL Server.
 
1. Restore the databases using the copied backup files
 
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. Please consult the onsite SQL team for guidance.
 
 
Step 5 - Update the locations of the databases
 
This can be done in one of two methods. The first method is to use PowerShell, the second is to perform the update manually. Select either one of the following:
 
1. Use the PowerShell commandlets provided with EV. Please refer to the  Enterprise Vault PowerShell Cmdlets guide regarding the  Get-EVDatabaseDetail and  Set-EVDatabaseDetail commandlets to move databases (EV 12 and newer). This guide is version-specific and can be found in the product documentation (see Related Articles below).
 
2. The updates can also be performed manually, using a combination of RegEdit, SQL Server Management Studio, and ODBC Data Source Administrator. Use the following steps to update these locations manually.

 
Step 5A – Update the Directory database in the Registry
 
This step is to be performed on the Enterprise Vault Server.

1. Click Start > Run , type regedit and click OK.

2. Open  HKEY_LOCAL_MACHINE\Software\Wow6432Node\KVS\Enterprise Vault\Directory\DirectoryService.

3. Double-click the SQLServer Name string value.

4. Enter the name of the new SQL server in the value data field and click OK.


 
Step 5B – Update the Audit database
 
This step is to be performed on the Enterprise Vault Server.
 
Note: No Enterprise Vault services need to be stopped for this step.
 
The EVAudit System DSN must be updated with the new location of the audit database.

1. For 32-bit systems, Open Data Sources (ODBC) from the Administrative Tools panel on the Enterprise Vault Server. For 64-bit systems, browse to %systemroot%\syswow64\odbcad32.exe

2. Select the System DSN tab.

3. Select the EVAudit entry.

4. Click the Configure button.

5. On the first page of the wizard, under Which SQL Server do you want to connect to?, enter the name of the new SQL server.

6. Continue through the wizard and click Finish to apply the changes.

Note: If TLS 1.0 and 1.1 are disabled and TLS 1.2 is enabled, this will fail with the following:

Connection failed: SQLState: '01000' SQL Server Error: 1 [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (SECCreateCredentials()). Connection failed: SQLState: '08001' SQL Server Error: 18 [Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error

   This error occurs because TLS 1.2 uses Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) and ODBC drivers other than ODBC Data Source Administrator (32-bit) Microsoft OLE DB Provider for SQL Server (SQLOLEDB) for their drivers. 

To work around this issue, do the following:

  • Open ODBC Data Source Administrator (32-bit)
  • Select the System DSN tab
  • Select the Add button
  • Select the SQL Server Driver, then click on Finish
  • Name: Audit DB name
  • Server: SQL Server
  • Select Finish.
  • Do not do any test connections as they will fail.

 

Step 6 - Resume EV operations using the new SQL Server

This step is to be performed on the Enterprise Vault Server.
 
1. Restart all Enterprise Vault services.
 
2. Check the Event Viewer for errors connecting to databases.
 
3. Check the locations of the Directory, Fingerprint, and Vault Store databases again, as in Step 1. They should all display the name of the new SQL Server.
 
4. Do not forget to update any scheduled backup jobs so that they backup the new SQL Server. It is recommended that the copies of the EV databases on the old SQL Server not be removed until a full backup of the databases on the new SQL Server has been completed.
 

Was this content helpful?

Article Languages