How to: Move an Agility Database from one MS SQL Server to Another

Moving only the database

Agility application

There are a few important things to know about moving the database to another machine.

When you run the installer and select Upgrade and choose a different SQL Server, the installer will look on that new server for a DB that is the SAME name of the one that the instance is using already. If there is not one with the same name already, the installer will create a new DB, which is something you probably don't want. So there are two processes that you can follow and both will work as long as you do these step by step and do not shortcut anything.

Option 1 (if the DB and Instance are going to keep the same name)

1. Make a backup of the database on the original server. Take that .bak file over to the new SQL Server and Restore it*. It is important that you use the same name as the original database.
2. Run the Agility Installer for the build and edition you are running now
3. Select Upgrade
4. The next page is where you select the instance that you are moving. Click that instance and then click Next.
5. Select the authentication mode and click Next
   Note: The next page is the important one. This is where you enter the NEW SQL Server information.
   
6. Enter your SQL Server info here and when you click Next it will look for the DB on that server that has the name of the Original database. This is why you MUST name it the same. If you do not, it will create a new database.
7. Step through the rest of the install wizard and let it run. Once it finishes, your instance will point to the new database location.

Option 2 (if you are renaming ANY part of the instance, like the URL, DB name, or anything like that)

1. Make a backup of the existing instance's SQL Server database and keep it in a safe place.
2. Run the Agility Installer
3. Select Uninstall and remove that instance. Keep the database.
4. Restore your DB backup to the new SQL Server location. Name it what you want. We recommend the same name as before but in this option, it does not matter.
5. Once that DB is restored, run the installer again for the Edition and build that you want to install. Note that this is a good time to Upgrade your instance if you are on an unsupported build.
6. Select Install a New Instance
7. Name the instance the new name, or use the name on the existing instance
8. Select the authentication mode and click Next
   Note: The next page is the important one. This is where you enter the NEW SQL Server information.
   
9. Enter your SQL Server info here and when you click Next it will ask you for the database. Select Attach to an existing database and then choose the database that you restored in the dropdown.
   
10. Step through the rest of the install wizard and let it run. Once it finishes, your instance will point to the new database location.

Datamart

There is no need to copy the Datamart database over. All it is, after all, is a de-normalized copy of the Lifecycle database that is configured in a reporting-friendly model. So, you do not need to backup and restore the datamart DB. You will have to run the installer in order to set up the new location of the Analytics DB though.

1. Backup the Analytics database and restore it to the new location. Use the same name that you used in the original location.
2. Run the Datamart installer and select Install a New Instance.
3. Enter the new location of the Lifecycle database that you used above in the Lifecycle section, and select that specific database on the next page.
4. Next, you are asked where the Datamart DB will be. Enter the new location's server name and login info. On the next page, tell it to create a new database.
5. The next page will ask for the Analytics DB that you just restored in the earlier step. Enter the server name and login and hit Next.
6. Here's the important part: Change the radio button to Attach to an Existing Database, and then click the dropdown to select the DB you restore for Analytics.
7. Click Next through the rest of the steps and the installer will run and complete.

Analytics

As you have already moved the database, this is the easy part. You will need to uninstall the existing Analytics, though.

1. Start the installer for Analytics. Select Uninstall
2. Select the one you are working with and uninstall it. Make sure you uncheck the part where it asks if you want to delete the database.
3. Run the installer and select Install a New Instance.
4. Step through the installer, and where it asks what Analytics metadata database to use, enter the appropriate info. Select the database you restored and connected to during the Datamart install.
5. Finish the installer and test the setup from Lifecycle.