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 webserver, 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 data source; 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'

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.

Additional Steps

  1. Version 14.0+ - Repeat the above process for the SQBoxStatistics database
  2. Update the "connectionStrings" config files in the following locations to reflect the new SQL Server name:
    • Statistics - C:\sqbox\webroot\statistics or C:\inetpub\wwwroot\statistics
    • Modern - C:\sqbox\webroot\modern or C:\inetpub\wwwroot\statistics.
  3. Update Datasource - See instructions below
  4. Detach or decommission the database from previous server
  5. Confirm the intranet loads

Create/Update 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. Edit the SQLFunctions datasource or 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