Move SQL Database To New Server

You are here:

Follow these steps to migrate or restore the System Frontier database to a new SQL server. If the source database is still online, use Method 1, otherwise use Method 2.

Method 1 (Old database is online)

  1. While the old database is still online, open SQL Server Management Studio on the old server and run the following T-SQL statement, where your_new_password is a password that meets industry best practice complexity requirements:
    USE SystemFrontier
    
    ALTER MASTER KEY 
    ADD ENCRYPTION BY PASSWORD = 'your_new_password'
  2. Backup the SystemFrontier database.
  3. Restore the database to the new server.
  4. On the new SQL server, re-encrypt the master key with your new password, by running the following T-SQL statements:
    USE SystemFrontier
    
    OPEN MASTER KEY
    DECRYPTION BY PASSWORD = 'your_new_password';
    ALTER MASTER KEY 
    ADD ENCRYPTION BY SERVICE MASTER KEY
  5. To ensure the System Frontier web application is communicating with the new database server, navigate to the web install folder and edit the Web.config file.
  6. Under the <connectionStrings> section, replace the old server name with the new server name in each connectionString entry. Disregard this step if doing a new web application install.
  7. Verify the new database connection by opening System Frontier and performing any management action, such as clicking on the Hardware tab for a computer or executing a custom tool, both of which will verify that admin credentials are being decrypted properly.

Method 2 (Old database is NOT online)

  1. Restore the most recent database backup to the new server.
  2. On the new SQL server, re-create the master encryption key by running the following T-SQL statements. Be sure to replace your_new_password with a password that meets industry best practice complexity requirements:
    USE [SystemFrontier]
    GO
    
    --CLOSE SYMMETRIC KEY [AdminLogin_Key01]; 
    DROP SYMMETRIC KEY [AdminLogin_Key01];
    GO
    
    DROP CERTIFICATE [AdminLogin01];
    GO
    
    DROP MASTER KEY;
    GO
    
    CREATE MASTER KEY ENCRYPTION BY 
     PASSWORD = 'your_new_password'
    GO
    
    CREATE CERTIFICATE AdminLogin01
     WITH SUBJECT = 'Administrative access logins';
    GO
    
    CREATE SYMMETRIC KEY AdminLogin_Key01
     WITH ALGORITHM = AES_256
     ENCRYPTION BY CERTIFICATE AdminLogin01;
    GO
  3. To ensure the System Frontier web application is communicating with the new database server, navigate to the web install folder and edit the Web.config file.
  4. Under the <connectionStrings> section, replace the old server name with the new server name in each connectionString entry. Disregard this step if doing a new web application install.
  5. Open a web browser and navigate to your System Frontier application URL.
  6. Go to Settings > Credentials.
  7. Edit each credential, type in the correct password for it and click Save.
  8. Verify the new database connection by opening System Frontier and performing any management action, such as clicking on the Hardware tab for a computer or executing a custom tool, both of which will verify that admin credentials are being decrypted properly.
Was this article helpful?
Dislike 0