Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Edited code for newer version of SQL

...

Zen Section
id1379412869

Migrating the Database

Info

Migrating ILLiad Databases via DTS Transfer is not recommended. Only the database data is transferred, omitting any triggers, permissions, keys, indexes, etc. and can possibly leave your new database unusable.

1. Restore the Database on the New Server

Copy the backup file from the old database server to the new database server. It doesn't need to be in the same folder where your fresh ILLData database is, just somewhere you can find it. Restore the backup file by right clicking and choosing Tasks - Restore - Database. In the options be sure to choose to have it Overwrite the existing database using the file location of the database files created using the installer on the new server.

2. Reassign the ILLiad SQL Login

Because the link to the ILLiad user has now been broken, you need to reassign the ILLiad SQL user back to the migrated database. You can do this by running the following query against the database (where ILLData is the name of your ILLiad Database):

Code Block
linenumberstrue
languagesql
USEuse ILLData
GO
ALTER USER illiad WITH login = illiad
GOexec sp_change_users_login update_one,'illiad','illiad' 

3. Swap the IP Addresses of the Old and New Servers

In order for ILLiad to connect to the new database, you will need to swap the IP addresses of the old and new servers. This will allow the client machines to connect to the database without any additional installation/changes on the client side. If you do not swap the IP addresses but instead want to connect to ILLiad with the new IP address, make sure that any references to the IP address are updated in the dbo.Customization table and in the SQL Alias Manager.

Reveal
idadditional-steps-shared-server

Additional Steps for Shared (Multi-Site) Servers

Disappear
idadditional-steps-shared-server

If you are migrating a Multi-Site Server, follow this additional steps to ensure that your data for all of your Sites is intact and accessible following the migration.

Create Logins

In the Microsoft SQL Server Management Studio, navigate to the Server-level Security section (Your Server - Security - Logins). This is NOT the same as the Security section beneath the ILLData database, which is database-specific.

Under the Logins section, manually add SQL Logins and Passwords for each of your Processing sites. You can do this by right-clicking on Logins and selecting New Login. Be sure to use Login and Password values that are identical to the ones already in use on the old system, including using the same case formatting (upper and lower case letters). 

Once the Login Name and password have been entered into the New Login form, you do not need to do anything else on this form (specifying the Default Database or Server roles, etc.). You only need to create the Logins themselves. These new Logins will be matched to the Database Specific SQL Users and Schemas that came with the migrated database by the next step. Click OK to close the New Login form.

Reassigning the ILLiad SQL Logins

Because the user links to the have now been broken, you need to reassign the SQL users back to the migrated database. You can do this by running the following query against the database (where ILLData is the name of your ILLiad Database). When more than two sites have been added, you can add those to the query by repeating the "ALTER USER" step again for each additional login. Before running the query, replace XYZ and PDQ with the Logins you created and add more lines with any additional Logins.

Code Block
linenumberstrue
languagesql
USE ILLData
GO
ALTER USER illiad WITH login = illiad
GO
ALTER USER XYZ WITH login = XYZ
GO
ALTER USER PDQ WITH login = PDQ
GO

SQL Alias Manager

Open the SQL Alias Manager (SAM) and add the new Logon Accounts. See Creating a SQL Alias Manager Profile for more details.

Testing

Test the ILLiad Client on the server to make sure that it can see the database when logged on as each Processing Site. If this fails, check the "Server" and "Database Name" values in the SAM to make sure they are pointed at the right database server (the new one) and database, and verify that the Logon Accounts information on the right side of the SAM is identical to the SQL Logins that you created on the SQL Server.

...