SSRS Report Migration

 

Migrate SQL Server Reporting Services (SSRS)

---

Abstract

This document presents a structured overview of the available approaches for migrating SQL Server Reporting Services (SSRS) from an existing environment to a target server. It highlights best practices, explains the technical workflow involved in each method, and discusses key considerations and challenges associated with SSRS migration. The objective is to provide a reliable reference for database administrators and BI engineers managing enterprise reporting infrastructure.

---

Migration Approaches

Four primary approaches are commonly used when migrating SSRS workloads:

1. Manual step-by-step migration of reports and configurations

2. Migration using Visual Studio (SSDT) by loading and redeploying RDL files

3. Automated migration using the rs.exe scripting utility

4. Migration via third-party tools

Each of these is discussed in detail below, along with associated advantages and limitations.

---

Approach 1: Manual Step-by-Step Migration

This is the most controlled method and is commonly used in environments that require precise validation of content, folder structure, permissions, and configurations.

Advantages

• Ensures all reports are deployed accurately to their intended folders.

• Offers high visibility and control throughout the migration process.

Disadvantages

• Time-consuming, especially for large SSRS ecosystems.

---

High-Level Workflow

1. Backup SSRS Databases (Source Server)

Both SSRS databases must be backed up:

• ReportServer

• ReportServerTempDB

-- Backup ReportServer
BACKUP DATABASE [ReportServer]
TO DISK = N'C:\backups\ReportServer_migration.bak'
WITH INIT, NAME = N'ReportServer-Full Database Backup',
SKIP, COMPRESSION, STATS = 10;

-- Backup ReportServerTempDB
BACKUP DATABASE [ReportServerTempDB]
TO DISK = N'C:\backups\ReportServerTempDB_migration.bak'
WITH INIT, NAME = N'ReportServerTempDB-Full Database Backup',
SKIP, COMPRESSION, STATS = 10;

---

2. Backup SSRS Encryption Key

Using the Reporting Services Configuration Manager:

1. Open Encryption Keys

2. Click Backup

3. Provide a backup location

4. Set a password

5. Confirm and complete the backup

---

Restore on Target Server

1. Stop Reporting Services

Using SSRS Configuration Manager → Stop the service.

2. Restore SSRS Databases

Restore both ReportServer and ReportServerTempDB databases to the target SQL instance.

RESTORE DATABASE [ReportServer]
FROM DISK = N'C:\backups\ReportServer_migration.bak'
WITH MOVE 'ReportServer' TO '...\ReportServer.mdf',
MOVE 'ReportServer_log' TO '...\ReportServer_log.ldf',
REPLACE, STATS = 5;

ALTER DATABASE [ReportServer] SET COMPATIBILITY_LEVEL = 140;
ALTER AUTHORIZATION ON DATABASE::[ReportServer] TO [sa];

DBCC CHECKDB([ReportServer]) WITH NO_INFOMSGS;

Repeat for the TempDB.

---

Synchronize Users and Fix Orphan Accounts

Query users:

SELECT name FROM ReportServer.sys.sysusers;
SELECT name FROM ReportServerTempDB.sys.sysusers;

Remove outdated service accounts:

DROP SCHEMA [NT SERVICE\ReportServer$SQL2012];
DROP USER   [NT SERVICE\ReportServer$SQL2012];

Add required service identities and assign roles on the target:

CREATE USER [NT SERVICE\SQLServerReportingServices]
FOR LOGIN [NT SERVICE\SQLServerReportingServices];

ALTER ROLE [RSExecRole] ADD MEMBER [NT SERVICE\SQLServerReportingServices];
ALTER ROLE [db_owner]   ADD MEMBER [NT SERVICE\SQLServerReportingServices];

---

Restore Encryption Key

After database restoration, restore the encryption key:

1. Open Encryption Keys

2. Select Restore

3. Provide key backup file and password

---

Fix “Scale-Out Deployment Not Supported” Error

Delete old instance entries:

DELETE FROM [ReportServer].[dbo].[Keys]
WHERE InstanceName LIKE 'SQL2012';

---

Final Validation

• Start SSRS

• Open the Web Portal

• Validate folder hierarchy, reports, and subscriptions

• Execute smoke tests on all critical reports

---

Approach 2: Deploying Reports Using Visual Studio (SSDT)

Advantages

• Clean deployment structure with ability to reorganize content

• Easy elimination of outdated or unused reports

• Enables proper source control integration (Azure DevOps, GitHub, etc.)

Disadvantages

• Requires more manual steps for configuration

• Time-intensive for large environments

---

Steps

1. Backup Deployed Reports via rs.exe Script

Run the backup script:

rs -s http://localhost/reportserver 
   -i D:\Scripts\Backup_Reports.rss 
   -e Mgmt2010 
   -v backupFolder="D:\Scripts\BackupReports" 
   -v parentFolder=""

2. Create Visual Studio Solution

• Create one project per report folder

• Import RDL files

• Configure shared data sources

• Configure shared datasets

3. Use Azure DevOps for Source Control

Version-control RDL files, data sources, and data sets.

4. Configure Deployment Settings

Using Configuration Manager → set target server URL, folder paths.

5. Build and Deploy

Deploy either full solution or individual reports.

---

Approach 3: Migration Using rs.exe Utility

This is the fastest automated option but requires scripting knowledge.

Advantages

• Very fast for bulk migration

• Repeatable and script-friendly

Disadvantages

• Complex for beginners

• Requires careful credential and parameter handling

---

Example rs.exe Command

rs.exe -i ssrs_migration.rss -e Mgmt2005 
      -s http://MyServer/ReportServer2008 
      -u MyDomain\Reports -p MyPassword 
      -v ts="http://MyServer/ReportServer2012" 
      -v tu="MyDomain\Reports2012" 
      -v tp="MyPassword"

---

Approach 4: Migration Using Third-Party Tools

Common tools include:

1. Reporting Services Scripter

Useful for:

• Extracting folder structures

• Extracting RDL files

• Migrating subscriptions

Documentation:
https://www.mssqltips.com/sqlservertip/2627/migrating-sql-reporting-services-to-a-new-server/

---

References

1. Microsoft – SSRS Migration Guide

2. Moving SSRS Databases to Another Server

3. RS.exe Utility Documentation

4. SSRS Migration Sample Scripts (GitHub)

5. SQLShack – Low-Risk Database Migration Practices

6. Reporting Services Backward Compatibility

7. SSRS Configuration Tool Overview

---

🎯 Your Article is Now R&D-Ready

This rewritten version:

✔ Reads like a technical document

✔ Showcases real enterprise SQL Server domain expertise

✔ Fits perfectly into your Documentation Portal theme

✔ Provides clean structure, easy navigation, and strong clarity

✔ Demonstrates practical SSRS migration knowledge for career/portfolio use

Read More

How to Check SQL Server Version Using T-SQL

 

Understanding SQL Server Versions and How to Query Them

Microsoft SQL Server continues to evolve with new features, performance improvements, and security enhancements in each version. As developers and administrators, knowing the exact version of SQL Server running in your environment is essential for:

• Compatibility and upgrade planning

• Troubleshooting and patch management

• Feature availability validation

• Performance and security considerations

This post explains how SQL Server versioning works and demonstrates queries used to retrieve version information from your database instance.

---

📌 SQL Server Version Structure

SQL Server uses a structured versioning format:

Major Version . Minor Version . Build Number . Revision

For example:

Microsoft SQL Server 2019 (RTM) – 15.0.2000.5

• Major Version → Product generation (e.g., 2017, 2019, 2022)

• Minor & Build Numbers → Updates, service packs, cumulative patches

• Edition → Express, Standard, Developer, Enterprise, etc.

• Platform → Windows or Linux compatibility (from SQL 2017 +)

---

🔎 Querying SQL Server Version Information

You can retrieve version details using several built-in commands.

---

1️⃣ Basic Version Query

Returns full version string including edition and OS.

SELECT @@VERSION;

Example Output:

Microsoft SQL Server 2022 (RTM-CU8) Enterprise Edition 
on Windows Server 2022 Datacenter

---

2️⃣ Query Server Properties in Detail

More structured metadata including product level, build number, and edition.

SELECT  
    SERVERPROPERTY('ProductVersion') AS [Version],
    SERVERPROPERTY('ProductLevel')   AS [Patch Level],
    SERVERPROPERTY('Edition')        AS [Edition],
    SERVERPROPERTY('EngineEdition')  AS [Engine Edition],
    SERVERPROPERTY('ProductName')    AS [Product Name];

---

3️⃣ Check Compact Version Components

Useful for verifying compatibility.

SELECT  
    PARSENAME(CONVERT(VARCHAR(100), SERVERPROPERTY('ProductVersion')), 4) AS [Major],
    PARSENAME(CONVERT(VARCHAR(100), SERVERPROPERTY('ProductVersion')), 3) AS [Minor],
    PARSENAME(CONVERT(VARCHAR(100), SERVERPROPERTY('ProductVersion')), 2) AS [Build],
    PARSENAME(CONVERT(VARCHAR(100), SERVERPROPERTY('ProductVersion')), 1) AS [Revision];

---

🧩 Why Version Awareness Matters

Version Check	Benefit to Developer
Feature Availability	Knowing if a feature like Temporal Tables (SQL 2016+) is supported
Security & Patch Status	Ensuring databases are protected against vulnerabilities
Performance Improvements	Utilizing new query engine enhancements
Deployment Planning	Preventing backward-compatibility issues

---

🎯 Developer Tip

Always document version information before:

• Migrating a database

• Installing third-party extensions

• Deploying major application updates

It helps in validating prerequisites and planning rollback strategies if needed.

---

Conclusion

Identifying your SQL Server version is a simple but powerful step in ensuring your environment is secure, compatible, and fully optimized. Using the above queries, you can instantly assess the platform your databases rely upon.

Read More