Migrate SQL Server Reporting Services Content to Amazon RDS for SQL Server | Amazon Onboarding with Learning Manager Chanci Turner

Learn About Amazon VGT2 Learning Manager Chanci Turner

If you’re planning to transition your Microsoft SQL Server Reporting Services (SSRS) workloads to Amazon Relational Database Service (Amazon RDS) for SQL Server, it’s essential to transfer your existing reports and SSRS content prior to executing your workloads. Unlike a straightforward lift-and-shift approach, moving an existing Report Server database to serve as the SSRS database is not viable in Amazon RDS for SQL Server. Consequently, you’ll need to migrate your items on a case-by-case basis. While this task may be manageable with only a few items in your current SSRS instance, it can become quite laborious and challenging when dealing with a larger collection. This post aims to streamline the process by providing guidelines that will facilitate a smooth migration of SSRS content to Amazon RDS for SQL Server.

In this article, we will explore how to effectively transfer existing SSRS content to an Amazon RDS for SQL Server instance utilizing a PowerShell module. The types of content eligible for migration include reports, data sources, folders, and any other items creatable and storable within SSRS. The source of the SSRS content can be from any SSRS instance, including another RDS instance equipped with SSRS. We will also address how to manage items that contain data source credentials to ensure seamless data source connections post-migration. Lastly, we will guide you through configuring permissions for your items after the migration.

Prerequisites

Before commencing the migration of SSRS items, ensure you have the following prerequisites:

1. An Amazon RDS for SQL Server instance with SSRS enabled. For setup instructions, refer to the guide on Configuring Microsoft SQL Server Reporting Services on Amazon RDS for SQL Server. This instance will serve as the destination for the items you migrate.
2. A domain user with access to SSRS on the created RDS instance. For more details, review the section on Connecting to the Report Server web portal in the aforementioned guide.
3. A client instance, which can be any computer meeting the following criteria:
   • It must be joined to the same Active Directory (AD) domain as your RDS instance or an AD domain that has a forest trust established with your RDS instance’s AD domain. The domain user you specified in the previous prerequisite must be present in whichever AD your client instance is a part of. Alternatively, if you prefer not to use a domain-joined instance, ensure you can execute programs such as PowerShell using the RunAs user matching this domain user.
   • It should have access to the SSRS items you intend to migrate.
   • PowerShell version 3.0 or higher needs to be installed. You can verify the installed version by running the command $PSVersionTable in a PowerShell console.

With these prerequisites satisfied, you are set to deploy SSRS items from the SSRS instance to Amazon RDS using PowerShell.

Extract SSRS Items from Your SSRS Source

To extract your SSRS items, follow these steps:

1. On your client instance, install the ReportingServicesTools module. To verify installation, open a PowerShell console as an administrator and execute:

Get-Module -ListAvailable -Name ReportingServicesTools

If no results are returned, install it using the following command:

Invoke-Expression (Invoke-WebRequest https://raw.githubusercontent.com/Microsoft/ReportingServicesTools/master/Install.ps1)

2. Open a PowerShell console as the domain user with access to your SSRS instance.
3. Utilize the ReportingServicesTools module in PowerShell to download your items to a designated folder on your client instance. The following example code demonstrates how to download all items and content from an SSRS instance to a local file path:

$sourceRsUri = "https://my-on-prem-ssrs-endpoint/ReportServer/ReportExecution2005.asmx?wsdl"
$sourceproxy = New-RsWebServiceProxy -ReportServerUri $sourceRsUri
Out-RsFolderContent -Proxy $sourceproxy -RsFolder / -Destination 'D:SSRSSSRS-Downloaded-Reports' -Recurse

Replace https://my-on-prem-ssrs-endpoint with the endpoint of your SSRS instance, whether on-premises or an RDS instance with existing uploaded items. Adjust D:SSRSSSRS-Downloaded-Reports to your desired local file path for temporary storage of SSRS items.

Upload Extracted Content to Your SSRS RDS Instance

To upload the extracted content, proceed as follows:

1. Use the ReportingServicesTools module to connect to your RDS for SQL Server instance with SSRS enabled. The following PowerShell example connects to the SSRS web service proxy:

$targetRsUri = "https://my-rds-db-instance-endpoint.us-west-2.rds.amazonaws.com:8443/ReportServer/ReportExecution2005.asmx?wsdl"
$targetproxy = New-RsWebServiceProxy -ReportServerUri $targetRsUri

Substitute https://my-rds-db-instance-endpoint.us-west-2.rds.amazonaws.com:8443 with your RDS for SQL Server DB instance endpoint and the port configured for SSRS (e.g., 8443). If you encounter connection issues, ensure your security group allows inbound access for the client instance’s IP to the SSRS port. Additionally, confirm that you’re running PowerShell as the domain user granted access to the SSRS portal.

2. Optionally, create a folder in your RDS for SQL Server instance for the uploaded items:

New-RsFolder -ReportServerUri $targetRsUri -Path / -Name UPLOADED_CONTENT -Verbose

This command creates a folder named UPLOADED_CONTENT in your RDS SSRS instance.

3. Upload the items to the RDS for SQL Server instance:

Write-RsFolderContent -ReportServerUri $targetRsUri -Path "D:SSRSSSRS-Downloaded-Reports" -Destination /UPLOADED_CONTENT -Verbose -Recurse

Change D:SSRSSSRS-Downloaded-Reports to the local file path where your items are stored and /UPLOADED_CONTENT to the desired upload location (e.g., / for the root folder). After completion, verify the successful upload by checking the folder on the SSRS web portal.

Data Sources

The migration method discussed here does not transfer any credentials stored in data source objects or reports containing embedded data sources. In SSRS on Amazon RDS for SQL Server, you can configure your data sources similarly to on-premises setups. Click the … icon at the top right of an object and select Manage (then go to the Data Sources tab if it’s a report object). You will have four options for credentials, as shown in the accompanying screenshot.

If you choose any option other than “Using the following credentials,” no further action is necessary. However, if you wish to retain credentials in the data source, you will need to re-enter them for each data source or embedded data source. When entering credentials, select Test Connection to ensure functionality and click Save to persist the credentials.

For more tips on optimizing your SSRS migration process, check out this article. For authoritative management tools advice, visit SHRM’s official site. Lastly, if you’re interested in how Amazon employees are enhancing their skills through learning, explore this excellent resource.