Change the location of the GMS database (SQL Server)

Overview

Use this article to transfer your GMS databases to a new location on your existing SQL Server, or to a different SQL Server.

Resolution or Workaround

1.     Stop all GMS services.

2.     In SQL Server, detach the sgmsdb & sgmsdb_archive:

3.     If moving the databases to a different host - On each GMS server (console, agents, summarizers), edit the C:sgmsConfig.xml file.  Edit the values for ‘dbhost’ and ‘dburl’ - replace ‘127.0.0.1’ and ‘localhost’ with the IP address of the new SQL Server.

4.     Move the sgmsdb and sgmsdb_archive files (mdf and ldf) to the new location.

5.     In SQL Server (on the new database server), attach the sgmsdb and sgmsdb_archive:

6.     In SQL Server (on the new database server), correct the database ownership.  The owner of the databases must match the ‘dbuser’ value specified in the sgmsConfig.xml file.  This value is encrypted, so if you don’t remember the username chosen during installation, please call techsupport at 888-793-2830 and they can decrypt it for you.  If you know the database username, use this SQL to change the owner:

use sgmsdb

sp_changedbowner ‘newOwner’;

(replace newOwner with the unencrypted value defined in sgmsConfig.xml)

7.     Start the GMS services.