SQL Server Migration
- Install and Configure new SQL Server
- Install SQL Server as per the Microsoft installation instructions.
- Ensure that you have enabled Mixed Authentication (i.e. both SQL and Windows Authentication on the SQL server are enabled).
- Open SQL Management Studio
- Right click Databases and select New Database…
- In the Database Name field type in IntranetConnections
- Keep the rest of the default settings
Create the IntranetConnections Login
- Right click Logins (under Security) and select New Login…
- In the Name field type in IntranetConnections
- Change the default of Windows NT Authentication to SQL Server Authentication
- 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 (by default, we set the password to ‘ICu$er&[email protected]’)
- Click on User Mapping
- Select the Database IntranetConnections by clicking in the checkbox
- Under Database Roles make sure public is checked and db_owner is checked
- Click OK to finish creating the login
Moving your old SQL content to the new SQL database
- 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
- Copy the backup file (filename.bak) from the old server to the new server
- Open SQL Management Studio on the new server. Right click on the IntranetConnections database and choose Tasks and then Restore Database.
- Go to Options in the top left hand corner and choose ‘Overwrite the existing database’ from the Restore options menu.
- 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.
- Log into Lucee Web Admin (Railo Web Admin) via HTTP://YOURSERVERNAME/lucee/admin/web.cfm (HTTP://YOURSERVERNAME/railo-context/admin/web.cfm)
- Click on Datasource under Services
- Create a new Datasource called SQLFunctions
- The type is MSSQL - Microsoft SQL Server (Vendor jTDS)
- Enter in the name of your server
- For Database, type in: IntranetConnections
- For Username, type in: IntranetConnections
- For password, enter ‘ICu$er&[email protected]’ or the password entered for that account