# Migrating to New SQL Server (backup and restore)

This covers the migration of Analysis Server database to a newer version or a new instance of SQL Server through the backup and restore method.

# Prerequisites

Take note of the following information in the old Analysis Server machine:

1. Take note of the following information:

   • SQL Server (Database Engine and Analysis Services) with instance
   • Deployed databases names (SQL and OLAP)

   Use the instructions in the Configuring Database Connection Strings section to see and take note of the SQL Server connection properties of the Analysis Server.

2. Back up the SQL Server database. Use the instructions in the Microsoft Learn article – Create a Full Database Back Up (Using SQL Server Management Studio) to properly back up the SQL Server database.

If Reporting Services is configured and planned to be migrated, back up the ReportServer and ReportServerTemp databases using the instructions from the article.

# Instructions

Follow these instructions to successfully accomplish the migration:

1. Install the required version and edition of the SQL Server. Please see the SQL Server Software Packages section as a guide.

2. Set the necessary permissions of the Service Account in the SQL Server for a smooth deployment of databases. Please see the Service Account and Administrative Group section as a guide.

3. Restore the database backed up from the Prerequisites section. Use the instructions in the Microsoft Learn article – Restore Database Backup Using SSMS to restore the database to the new SQL Server.

4. Once the restoration is done, go to the machine where Analysis Server is installed. Reconfigure the database connection by following the instructions in the Install – [2] Set the Database section. Provide the correct hostname where the SQL Server is hosted. If you changed the database name upon restoration, provide the correct name of the database.

5. If Reporting Services is also restored, reconfigure the settings using the Reporting Service Console in the machine where Analysis Server is installed. Execute the following command:

   Example

   OpeniT.Server.Rs.Console.exe configureall /uri:http://\<new_sqlserver\>

   Where:

   <new_sqlserver>
   The new SQL Server where the Report Server is restored.

   If more parameters are necessary, use the instructions in the ConfigureAll section to properly reconfigure the Reporting Services settings.

   To make the subscriptions work in the new SQL Server and Reporting Services database, create RSExecRole. Use the instructions in the Microsoft Learn article – Create RSExecRole to create the necessary permissions. Choose the most suitable method provided in the article.

# Further Verification

Once all important configurations from the old Analysis Server installation are migrated, follow these steps to verify that the migration is successful:

1. Access the Analysis Server web interface and navigate through each of the pages. This is to make sure that the migration did not break any page and that the migrated configurations are still present. Use the instructions in the Accessing the Web Interface section. Compare the screenshots taken in the Prerequisites section to validate the configurations.
2. Run the scheduled job in the Task Scheduler and check if there are errors encountered in the logs. This is to make sure that the processing will be successful in the succeeding executions. Use the instructions in the Running the Scheduled Job in Task Scheduler Manually section.
3. If Reporting Services is configured, please access any report template in the Home page. Make sure that it redirects to the Reporting Services Web Portal, and the report template can be generated.