SQL Server services migration procedure

Products

• IT Analytics

Issue/Introduction

Can the SQL Server services for IT Analytics (ITA) be migrated to a new server or servers, either as part of an environment reconfiguration or in the course of upgrading operating systems and system resources?

Environment

Release : 2.9.1

Component : Microsoft SQL Server

Resolution

It is possible to migrate the SQL Server service used by ITA, but this is an undocumented and unsupported procedure; as such, if engineering assistance becomes necessary, a new installation will be required.

The following procedure specifically addresses the splitting of a single tier deployment into a two-tier deployment, with the ITA application remaining in place and SQL Server services being moved to a new server.

To move SQL, SSRS, and SSAS while keeping the ITA application in place, this procedure should serve as a guide:

1. Install SQL Server with Analysis Services (SSAS) and Reporting Services (SSRS) on the new server
2. From the current ITA console, uninstall the cubes (ITA Console > Settings > Cubes Installation)
3. Point ITA to the new SSAS server (ITA Console > Settings > Servers > Analysis Server Settings)
4. Re-install the ITA cubes (ITA Console > Settings > Cubes Installation)
5. Create a backup of the SSRS database following the procedure provided by Microsoft here
6. Restore the SSRS database on the new server per Microsoft's instructions
7. Point ITA to the new SSRS server (ITA Console > Settings > Servers > Report Server Settings)
8. Register custom reports with ITA (ITA Console > Reports > Right-click and select Register Reports)
9. Create a backup of the ITA SQL database following the procedure provided by Microsoft here
10. Restore the ITA database on the new server per Microsoft's instructions
11. Edit the connection string in the ITA web.config file to point to the new server:

    %SystemDrive%\Program Files\Bay Dynamics\IT Analytics Server\Web\web.config

12. Point ITA to the new SQL Server Agent (ITA Console > Settings > Servers > SQL Server Agent Settings)
13. Create a new processing job or jobs pointing to the new cubes (ITA Console > Settings > Processing Jobs)
14. Process the cubes

Broadcom engineering has reviewed this procedure and confirmed that it should work, but it is not officially supported and should be tested in a development environment before attempting it in production.