Manually Move the SEPM SQL Database to a New SQL Server
search cancel

Manually Move the SEPM SQL Database to a New SQL Server

book

Article ID: 155426

calendar_today

Updated On:

Products

Endpoint Protection

Issue/Introduction

Manually move a Symantec Endpoint Protection Manager (SEPM) SQL database to a new SQL server using SQL Server Management Studio.

Environment

Microsoft SQL Server Database

Resolution

NOTE: The first two options accomplish the same task of getting the existing database to a new server.  Only one of the steps needs to be done, either (Backup the Database as per Best Practices or Detach and Move the Database Files)

NOTE: This process is not suitable for moving from SQL Express to SQL standard. Such move would require reinstalling of SEPM.

Backup the Database as per Best Practices

  1. Open the SQL Server Management Studio for the current server
  2. Right-click the sem5 database and choose Tasks > Back Up
    Note: Choose to use the existing backup file or for a smaller backup file remove that path and designate a new path and file name.
  3. Copy the backup file to the new SQL server
  4. Open the SQL Server Management Studio for the new server
  5. Right-click Databases in the server tree and click Restore Database
  6. Using the Device option, locate and restore the .bak file that was created in step 2

Detach and Move the Database Files

  1. Stop all Symantec Endpoint Protection Manager services
  2. Detach the database in question from the current Instance using the SQL Server Management Studio
  3. After the Database is detached, navigate to the appropriate data folder where the existing SQL server data files are stored. Depending on your SQL version this could appear in a variety of forms
    Examples:
    C:\Program Files\Microsoft SQL Server\MSSQL\<instance name>\DATA
    -OR-
    C:\Program Files\Microsoft SQL Server\MSSQL\DATA
    -OR-
    C:\Program Files\Microsoft SQL Server\MSSQL##.<Instance Name>\MSSQL\DATA
     
  4. Copy the following database files to the data folder on the new server you plan to house this SQL database:
    <db_name.MDF>
    <db_name.NDF>
    <db_name.LDF>

     
  5. On the new SQL server, Right-click on the Database instance and click 'Attach'
  6. Type the path to the <db_name.MDF> file, and the wizard will automatically pick up the other files (.NDF and .LDF)
  7. Click OK. The wizard will automatically collate and attach the SEPM SQL Database to the chosen SQL Instance on the new server

Delete the sem5 and reporting users (SQL Authentication method only)

Note: These instructions assume you are using the default naming conventions. If your previous instance was not named sem5 your database usernames will be different. The reporting user will appear similar to 'REPORTER_databasename'.

  1. Open the database
  2. Click Security > Users
  3. Locate and delete the sem5 user
  4. Locate and delete the REPORTER_sem5sem5 user, if it exists (SEP 14.x only).

Create a new login for sem5 (SQL Authentication method only)

  1. From the top of the hierarchy, open Security > Logins
  2. Right-click Logins and choose New Login
  3. Use the login name: sem5
  4. Choose 'SQL Server authentication'
  5. Enter a password
  6. Uncheck:
    • Enforce password expiration
    • User must change password at next login
  7. Select sem5 as the default database
  8. On the left, click User Mapping, and then check sem5
  9. In Database role membership for: sem5, check dbowner 
  10. On the right pane, under default schema make sure this matches your schema if you changed it from the default (dbo) Note : You can verify the current schema in use by expanding the tables folder under the sem5 database object and looking at the table prefix example (dbo."TableName" ) 
  11. Click Search on the Securables page and add the option for "The server <servername>" and click OK
  12. Under Explicit, find 'Alter any login' and check the box for 'Grant'
  13. Click OK

Connect the SEPM to the database

At this stage you have either installed a new SEPM, or plan to use the same SEPM as before. In either case, the SEPM needs to be pointed to the new database location.

Run the Management Server Configuration Wizard to point the SEPM to the new database.

If SEPM is already installed:

  1. Choose the option to Reconfigure the Management server 
  2. When prompted, enter the credentials for the sem5 user created in the steps above

If SEPM is a new installation, the wizard will launch after the setup is complete:

  1. Choose the option to add an additional manager to an existing site.
  2. When prompted, enter server name(\instance name) of the new SQL server, and the credentials for the sem5 user created in the steps above.

Notes:

  • If you are installing a new SEPM, you must use the same version of SEPM as the original SEPM. If you install a newer version of SEPM, you will get an error that the schema is not compatible.
  • For Windows Authentication method: If the account that you use is a domain user account, make sure that the Endpoint Protection Manager computer and the SQL server are in the same domain (or a trusted domain), and that the domain user account exists in its domain controller. If the account that you use is a local user account, make sure that the Endpoint Protection Manager computer and the SQL server are in the same workgroup, that the same local user account exists on both computers (same name and password), and that the account has local Administrator privileges on both computers.