Seamless Migration: On-Premise Report Server to Azure SQL Managed Instance

Introduction

The migration of an on-premise report server to Azure SQL Managed Instance requires strategic planning and meticulous execution. This transition offers numerous benefits, including scalability, reliability, and reduced maintenance overhead. In this blog, we'll explore the essential steps involved in migrating an on-premise report server to Azure SQL Managed Instance, ensuring a seamless transition for your organization.

 

Understanding Azure SQL Managed Instance

Before diving into the migration process, let's briefly understand Azure SQL Managed Instance. It is a fully managed platform as a service (PaaS) offering from Microsoft Azure, providing near-complete compatibility with on-premise SQL Server. Managed Instance offers features like automatic patching, automated backups, and built-in high availability, making it an attractive option for hosting SQL Server workloads in the cloud.

 

Pre-Requisites

1. Azure SQL Managed Instance
2. SQL Server User Account – Using to connect Azure SQL Managed Instance
3. Azure Virtual Machine

 

Configure Azure SQL Managed Instance

1. Go to Azure Portal and search for Azure SQL Managed Instance.
2. Set up the username and password, it will require connecting from SSMS and SSRS later.
3. Set up the required configuration.
4. Create the Azure SQL.
5. Create a new database (optional).
6. Open SSMS and verify the instance connection with SQL Server Authentication by entering a username and password of #2.
7. If it’s connecting successfully then we have configured Azure SQL Managed Instance correctly.

 

Configure Azure Virtual Machine

1. Go to Azure Portal and search for Virtual Machine.
2. Select the Windows Operating System and set up the required configurations.
3. Create a Virtual Machine and connect via RDP.

 

Install SSRS (SQL Server Reporting Services) in Azure VM

1. Connect your Azure VM using RDP.
2. Download the 2022 SSRS installer - Click here to download
3. Launch the installer of 2022 SSRS.
4. Choose Install Reporting Services and click Next.
5. Choose the appropriate Edition to match your licensing. Once selected choose Next.
6. Now you will want to accept the license and click Next.
7. Choose Install Reporting Services Only and click Next.
8. Change the Installation Location to a path of your choice, if you would like, then click Install.
9. Open Report Server Configuration Manager and click on Connect.
10. Start the Report Service if it’s not started.

 

Connect On-Premises SQL Server

1. Connect to your on-premises SQL Server.
2. Take a backup of your ReportServer and ReportServerTempDB databases.
3. After successfully backup of both databases, upload it to Azure Blob Storage.

 

Connect Azure SQL Managed Instance in SSMS

1. Connect your Azure SQL Managed Instance with your credentials.
2. Generate SAS Token to access Azure Blob Storage account.
3. Create new Credentials in SQL Managed Instance.

CREATE CREDENTIAL [AZURE BLOB URL WITH CONTAINER/FOLDER]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = 'SAS TOKEN'
;
GO

4. Restore ReportServer and ReportServerTempDB Databases

RESTORE DATABASE ReportServer
	FROM URL = 'AZURE BLOB URL OF DATABASE BACKUP FILE'
;
GO

RESTORE DATABASE ReportServerTempDB
	FROM URL = 'AZURE BLOB URL OF DATABASE BACKUP FILE'
;
GO

5. Delete old record from ReportServer.dbo.Keys table based on MachineName or InstanceName. (DELETE ReportServer.[dbo].[Keys] WHERE MachineName = 'OLD MACHINE NAME')
6. To view all subscriptions in the new server execute the below query.

DECLARE @OldUserID uniqueidentifier
DECLARE @NewUserID uniqueidentifier
SELECT @OldUserID = UserID FROM dbo.Users WHERE UserName = 'OLD SERVER NAME WITH USER'
SELECT @NewUserID = UserID FROM dbo.Users WHERE UserName = 'NEW SERVER NAME WITH USER'
UPDATE dbo.Subscriptions SET OwnerID = @NewUserID WHERE OwnerID = @OldUserID

7. Restart SQL Server Reporting Service.
8. Open the Report Server in the browser to verify all the Reports and Subscriptions.

 

Configure SSRS (SQL Server Reporting Services) in Azure VM

1. Connect your Azure VM using RDP.
2. Open Report Server Configuration Manager and click on Connect.
3. Start the Report Service if it’s not started.
4. Go to Database and click on Change Database.
5. Choose existing database option and click on Next.
6. Enter the database connection information of Azure SQL Managed Instance, Test the connection and click on Next. – IMPORTANT
7. Inside credentials, choose SQL Server Credentials option and, enter username and password of  Azure SQL Managed Instance and click on Next.
8. Please verify the SQL Server Instance Name and other details in Summary and click on Next.
9. Click on Finish.
10. In Report Configuration Manager and select Web Service URL, then click Apply.
11. Go to Web Portal URL, then click Apply.
12. Go to E-mail Settings, update your email settings to send report subscription emails.
13. Open browser and enter your report server Web Portal URL.