# Migrating to New SQL Server (new Database)

This guide covers migrating Analysis Server database connection, configuring it to a new SQL Server, and creating its new databases. This migration ensures that you exported all the configurations from the previous installation to the newly created database.

# Prerequisites

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

Gather screenshots of the following web interface pages:

# Instructions

Follow these instructions to successfully accomplish the migration:

Make sure to install the required version and edition of the SQL Server. Please see the SQL Server Software Packages section as a guide.
Make sure to set up 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.
Once you are done setting up the new SQL Server installation in the machine where Analysis Server is installed, reconfigure the database connection by following the instructions in the Install – [2] Set the Database section.

Make sure to provide the correct hostname and instance where the SQL Server is hosted.
To set up the database and some configurations, follow the instructions in the Post-Install Configuration section. This will create and deploy a new database in the new SQL Server and configure the most important configurations. Please use and be guided by the screenshots taken from the Prerequisites section to properly set some of the configurations.
If Email Settings is configured in the previous installation, accomplish the following instructions:
1. Go to the Email Settings page of the new Analysis Server installation
2. Apply the configuration from the screen shot taken in the Prerequisites section for Email Settings page. Use the instructions in the Email Settings section as guide.
If Cost, Application Cost, or Storage Cost is configured in the previous installation, accomplish the following instructions to migrate the configurations:
1. Connect to the SQL database of the old Analysis Server installation using SQL Server Management Studio (SSMS).
2. Right-click the database name and select Task > Generate Scripts.
Analysis Server Migration: Generate Scripts
1. The Generate Scripts dialog will be displayed. Click Next.
Analysis Server Migration: Generate Scripts Dialog
1. Choose the Select specific database objects option.
2. Expand the Tables option and select the table counterpart if the page is configured:
  - GlobalApplicationPrice - Cost
  - CostCatalog – Application Cost
  - StorageCost – Storage Cost
  Click Next.
Analysis Server Migration: Generate Scripts Tables
1. Click the Advanced button. The Advanced Scripting Options will be displayed.
2. Under the General options, look for Types of data to script, then select Data only. Click OK.
3. Select the Open in New Query Window option. Click Next.
Analysis Server Migration: Generate Scripts Advanced
1. Review the configurations, then click Next.
Analysis Server Migration: Generate Scripts Review
1. Wait until the process is finished. Click Finish. A new query window will open containing INSERT SQL queries.
Analysis Server Migration: Generate Scripts Completed
1. Connect to the SQL database of the new Analysis Server installation using SQL Server Management Studio (SSMS).
2. Click the New Query button in the upper-left corner of the window. This will open a new query window.
3. Copy the generated script from step j. Replace the [database_name] in the query at the start of the script with the newly created database name.
  USE [database_name]
4. Click the Execute button in the upper left corner of the window. This will execute the query and insert data from the old Analysis Server installation to the new installation.
Analysis Server Migration: Executing Generated Scripts
1. Once successful, go to the pages of the new Analysis Server installation and verify that there is data displayed.

If there were created dashboards in the Dashboard page in the old Analysis Server installation, follow these instructions to migrate all the created dashboards and subscriptions:

Connect to the SQL database of the old Analysis Server installation using SQL Server Management Studio (SSMS).
Right-click the database name and select Task > Generate Scripts.

Analysis Server Migration: Generate Scripts

The Generate Scripts dialog will be displayed. Click Next.

Analysis Server Migration: Generate Scripts Dialog

Choose the Select specific database objects option.
Expand the Tables option and select the following related tables to Dashboard:
- dbo.AdhocDashboard
- dbo.AdhocDashboardFolder
- dbo.PortletInstances
- dbo.PortletSubscriptions
Click Next.

Analysis Server Migration: Generate Scripts Tables

Click the Advanced button. The Advanced Scripting Options will be displayed.
Under the General options, look for Types of data to script, then select Data only. Click OK.
Select the Open in New Query Window option. Click Next.

Analysis Server Migration: Generate Scripts Advanced

Review the configurations, then click Next.

Analysis Server Migration: Generate Scripts Review

Wait until the process is finished. Click Finish. A new query window will open containing INSERT SQL queries.

Analysis Server Migration: Generate Scripts Completed

In the generated query, please remove the following lines:

AdhocDashboard

Default Dashboard
INSERT [dbo].[AdhocDashboard] ([Id], [Name], [Description], [Owner], [LayoutId], [Publish], [FolderId], [ProductFilter], [FeatureFilter], [IsDefault], [PackageFilter], [Type], [StartDateFIlter], [EndDateFilter]) VALUES (1, N'Default Dashboard', N'', N'PortalAdmin', 2, 1, 1, NULL, NULL, 1, NULL, 0, NULL, NULL)

AdhocDashboardFolder

Default Dashboard Folders
INSERT [dbo].[AdhocDashboardFolder] ([Id], [Name], [Owner], [IsPublic], [FolderOrder]) VALUES (1, N'Public Dashboards', N'SVG\jplanas', 1, 1)
INSERT [dbo].[AdhocDashboardFolder] ([Id], [Name], [Owner], [IsPublic], [FolderOrder]) VALUES (2, N'My Dashboards', N'SVG\jplanas', 0, 2)
INSERT [dbo].[AdhocDashboardFolder] ([Id], [Name], [Owner], [IsPublic], [FolderOrder]) VALUES (3, N'Default', N'SVG\jplanas', 1, 3)

PortletInstances

Default Portlet Instances
INSERT [dbo].[PortletInstances] ([Id], [DashboardId], [PortletId], [Owner], [Label], [Threshold], [RSS], [IM], [Panel], [ProductFilter], [FeatureFilter], [AlertUsers], [IsHtml], [Flood], [CustomTemplate], [Title], [Subtitle], [Message], [IsAlertCustom], [OrderInPanel], [UOM], [Height], [PackageFilter], [OfflineData], [InstanceCondition], [CustomFilter1], [CustomFilter2], [IsSubsAlertCustom], [SubsCustomTemplate], [SubscriberTitle], [SubscriberSubtitle], [SubscriberMessage], [Source], [Schedule], [Start], [DataCheck], [ReportType], [TimeZone]) VALUES (1, 1, 1, N'PortalAdmin', N'Approaching Max: Max Utilization >= 90 %', 90, NULL, NULL, N'secondContainer', NULL, NULL, 0, 1, 0, NULL, NULL, NULL, NULL, 0, 0, N'%', 10, NULL, 0, N'Max Utilization >=', NULL, NULL, 0, NULL, NULL, NULL, NULL, 0, N'Immediate', NULL, N'every 5 minutes and 10 seconds', 1, NULL)
INSERT [dbo].[PortletInstances] ([Id], [DashboardId], [PortletId], [Owner], [Label], [Threshold], [RSS], [IM], [Panel], [ProductFilter], [FeatureFilter], [AlertUsers], [IsHtml], [Flood], [CustomTemplate], [Title], [Subtitle], [Message], [IsAlertCustom], [OrderInPanel], [UOM], [Height], [PackageFilter], [OfflineData], [InstanceCondition], [CustomFilter1], [CustomFilter2], [IsSubsAlertCustom], [SubsCustomTemplate], [SubscriberTitle], [SubscriberSubtitle], [SubscriberMessage], [Source], [Schedule], [Start], [DataCheck], [ReportType], [TimeZone]) VALUES (2, 1, 4, N'PortalAdmin', N'License Expiration: Expiration <= 60 Days', 60, NULL, NULL, N'firstContainer', NULL, NULL, 0, 1, 0, NULL, NULL, NULL, NULL, 0, 1, N'Days', 10, NULL, 0, N'Expiration <=', NULL, NULL, 0, NULL, NULL, NULL, NULL, 0, N'Immediate', NULL, N'every day at 7:00:10', 1, NULL)
INSERT [dbo].[PortletInstances] ([Id], [DashboardId], [PortletId], [Owner], [Label], [Threshold], [RSS], [IM], [Panel], [ProductFilter], [FeatureFilter], [AlertUsers], [IsHtml], [Flood], [CustomTemplate], [Title], [Subtitle], [Message], [IsAlertCustom], [OrderInPanel], [UOM], [Height], [PackageFilter], [OfflineData], [InstanceCondition], [CustomFilter1], [CustomFilter2], [IsSubsAlertCustom], [SubsCustomTemplate], [SubscriberTitle], [SubscriberSubtitle], [SubscriberMessage], [Source], [Schedule], [Start], [DataCheck], [ReportType], [TimeZone]) VALUES (3, 1, 6, N'PortalAdmin', N'License Daemon Down: Minutes >= 10', 10, NULL, NULL, N'thirdContainer', NULL, NULL, 0, 1, 0, NULL, NULL, NULL, NULL, 0, 2, N'Minutes', 10, NULL, 0, N'Minutes >=', NULL, NULL, 0, NULL, NULL, NULL, NULL, 0, N'Immediate', NULL, N'every 5 minutes and 10 seconds', 1, NULL)

Removing these entries avoid conflicts with the entries in the newly deployed database.

Connect to the SQL database of the old Analysis Server installation using SQL Server Management Studio (SSMS).
Click the New Query button in the upper-left corner of the window. This will open a new query window.
Copy the generated script from step j with modifications from step k. Replace the [database_name] in the query at the start of the script with the newly created database name.
```
USE [database_name]
```
Click the Execute button in the upper left corner of the window. This will execute the query and insert data from the old Analysis Server installation to the new installation.

Analysis Server Migration: Executing Generated Scripts

Once successful, go to the Dashboard page of the new Analysis Server installation and verify that there is displayed.

If a new SQL Server Reporting Services is also set up and Reporting Services is previously configured, reconfigure the Reporting Services in the Analysis Server installation. Use the instructions in ConfigureAll to reconfigure Reporting Services to the new SQL Server.

# Verification

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

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.
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.
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.