Move SQL Database to New Server

In certain scenarios it may be required to move the database to a new server. This may be to increase the performance of the web server, setting up a test environment, etc.

The process involves configuring SQL Server on a new server, then taking a backup of the existing database and restoring it onto the new server.

SQL Server Migration

  1. Install and Configure new SQL Server
  2. Install SQL Server as per the Microsoft installation instructions.
  3. Ensure that you have enabled Mixed Authentication (i.e. both SQL and Windows Authentication on the SQL server are enabled).
  4. Open SQL Management Studio
  5. Right click Databases and select New Database…
  6. In the Database Name field type in IntranetConnections
  7. Keep the rest of the default settings
  8. Repeat this process for the SQBoxStatistics database if you're on Version 14.0+.

Create the IntranetConnections Login

  1. Right click Logins (under Security) and select New Login…
  2. In the Name field type in IntranetConnections
  3. Change the default of Windows NT Authentication to SQL Server Authentication
  4. Enter a password or leave it blank.
    • If entering a password, note it down for later use when creating the datasource; ensure that you uncheck the ‘Enforce Password Policy’, ‘Enforce Password Expiration’ and ‘User must change password at next login’ options.
    • In the Database Field from the drop down box choose the database IntranetConnections (contact Support for the default password)
  5. Click on User Mapping
  6. Select the Database(s) IntranetConnections and SQBoxStatistics (only applicable if you're on Version 14.0+) by clicking in the checkbox
  7. Under Database Roles make sure public is checked and db_owner is checked
  8. Click OK to finish creating the login

Moving your old SQL content to the new SQL database

  1. Create a backup of your old production IntranetConnections database. You can do this via SQL Management Studio.
    • Right click the database name > Tasks (or All tasks) > Backup
    • Backup the database to a location on Disk
  2. Copy the backup file (filename.bak) from the old server to the new server
  3. Open SQL Management Studio on the new server. Right click on the IntranetConnections database and choose Tasks and then Restore Database.
  4. Go to Options in the top left hand corner and choose ‘Overwrite the existing database’ from the Restore options menu.
  5. Once the restore has completed, open a new query and run the following:
EXEC sp_change_users_login 'auto_fix', 'intranetconnections'

Repeat this process for the SQBoxStatistics database if you're on Version 14.0+.

If your SQL user created in the previous section is not called intranetconnections, replace the occurrence of that name in the query with the appropriate name. This query repairs any orphaned user issues associated with the database transfer.

Once you have moved your old SQL Database to the new SQL Server, make sure to update the "connectionStrings" config file for statistics and modern to reflect the changes on the new server. Standard location of the config file is C:\sqbox\webroot\statistics or C:\inetpub\wwwroot\statistics, and C:\sqbox\webroot\modern or C:\inetpub\wwwroot\statistics.

Create datasource

  1. Log into Lucee Web Admin (Railo Web Admin) via HTTP://YOURSERVERNAME/lucee/admin/web.cfm (HTTP://YOURSERVERNAME/railo-context/admin/web.cfm)
  2. Click on Datasource under Services
  3. Create a new Datasource called SQLFunctions
  4. The type is MSSQL - Microsoft SQL Server (Vendor jTDS)
  5. Enter in the name of your server
  6. For Database, type in: IntranetConnections
  7. For Username, type in: IntranetConnections
  8. For password, type in password entered for that account (Learn how to access the IntranetConnections user account to set the password)

Related articles