Migrating SQL Server Reporting Services to Amazon RDS for SQL Server: A Guide by Chanci Turner

Learn About Amazon VGT2 Learning Manager Chanci Turner

If you’re considering transitioning your Microsoft SQL Server Reporting Services (SSRS) workloads to Amazon Relational Database Service (Amazon RDS) for SQL Server, it’s essential to migrate your existing reports and SSRS content before launching your workloads. Unlike traditional setups, directly moving a Report Server database to serve as the SSRS database in Amazon RDS is not permitted, necessitating a careful, item-by-item migration. While handling a few items may seem straightforward, the process can become cumbersome and complex with larger volumes. However, by adhering to the guidelines in this post, you can streamline the process of migrating SSRS content to Amazon RDS for SQL Server.

In this article, we’ll explain how to transfer your existing SSRS content to an Amazon RDS for SQL Server instance using a PowerShell module. The content eligible for migration includes reports, data sources, folders, and all other SSRS-related items. You can source SSRS content from any SSRS instance, including another RDS instance configured with SSRS. We will also cover how to manage items with data source credentials to ensure your connections function correctly post-migration. Lastly, we’ll guide you through configuring item permissions after the migration process.

Prerequisites

Before migrating SSRS items, ensure you have the following:

1. An Amazon RDS for SQL Server instance with SSRS enabled. For setup instructions, refer to the Configuring Microsoft SQL Server Reporting Services on Amazon RDS for SQL Server guide. This database instance will serve as your target for migrated items.
2. A domain user account with access to SSRS on the RDS instance you’ve created. For more details, see the section about connecting to the Report Server web portal in the aforementioned configuration guide.
3. A client instance, which can be any computer meeting these requirements:
   • Must be domain-joined to the same Active Directory (AD) domain as your RDS instance, or to an AD domain that has a forest trust with your RDS domain. The domain user specified in the previous prerequisite must be present in the AD.
   • Should have access to the SSRS items you wish to migrate.
   • Needs PowerShell version 3.0 or higher installed. You can verify the installed version by running the command $PSVersionTable in a PowerShell console.

Once you have these prerequisites in place, you can begin deploying SSRS items from your SSRS instance to Amazon RDS using PowerShell.

Extracting SSRS Items

To extract your SSRS items, follow these steps:

1. On your client instance, install the ReportingServicesTools module. To check if it’s installed, open a PowerShell console as an administrator and run:

Get-Module -ListAvailable -Name ReportingServicesTools

If the module is not found, you can install it using:

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 existing SSRS instance.
3. Download your items into a designated folder on your client instance using the ReportingServicesTools module in PowerShell. The example code below downloads all items from your 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

Make sure to replace https://my-on-prem-ssrs-endpoint with your actual SSRS instance endpoint and adjust the destination path as needed.

Uploading Extracted Content to Your SSRS RDS Instance

To upload the extracted content, do the following:

1. Use the ReportingServicesTools module to connect to your RDS for SQL Server instance with SSRS enabled. The example code below establishes a connection:

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

Substitute the endpoint with that of your RDS for SQL Server DB instance, ensuring that the port matches your SSRS configuration.

2. (Optional) Create a folder in your RDS instance for the uploaded items:

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

3. Upload the items to the RDS instance:

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

Adjust the paths accordingly to reflect where your items are stored.

Handling Data Sources

The migration method described here does not transfer any credentials stored in data source objects or reports with embedded data sources. In SSRS on Amazon RDS for SQL Server, you can configure data sources similarly to on-premises setups. You should see four options for managing credentials. If you choose any option other than “Using the following credentials,” no modifications are necessary. However, if you want to retain credentials, you must re-enter them for each data source or embedded data source. Make sure to test the connection to confirm it works before saving.

By following these steps, you can effectively migrate your SSRS content to Amazon RDS for SQL Server, ensuring a smooth transition and maintained functionality. For additional related resources, check out this insightful blog post by Kelly Will on mentoring here. Also, if you’re navigating the complexities of Medicare, you might find helpful information from SHRM valuable. If you’re interested in career opportunities, consider exploring this Learning Trainer role.