AWS Database Blog

Run SQL Server Reporting Services reports against Babelfish for Aurora PostgreSQL

Enterprises running their application workload on Microsoft SQL Server want to migrate to license-free databases like Amazon Aurora PostgreSQL-Compatible Edition but they rely on SQL Server Reporting Services (SSRS) for analytics and dashboards. Customers are looking for options where they can migrate application databases to Aurora PostgreSQL-Compatible and continue to use SSRS.

AWS provides tools and services like AWS Schema Conversion Tool (AWS SCT) and AWS Database Migration Service (AWS DMS) to migrate from SQL Server databases to Aurora PostgreSQL-Compatible Edition, but the migration can be time-consuming and resource intensive. You can use AWS SCT to migrate the schema and convert all SQL and DDL code to the PostgreSQL dialect, but the overall amount of required work to port the application typically remains high.

Babelfish for Aurora PostgreSQL is a capability for Amazon Aurora PostgreSQL-Compatible Edition developed using the PostgreSQL extension framework that enables Aurora to understand commands from applications written for Microsoft SQL Server. Babelfish for Aurora PostgreSQL understands T-SQL, Microsoft SQL Server’s SQL dialect, and supports Microsoft’s Tabular Data Stream (TDS) communication protocol, so applications that were originally written for SQL Server can now work with Aurora with fewer code changes, keeping the same drivers and SQL code. As a result, the effort to migrate applications running on SQL Server is reduced, leading to faster and more cost-effective migrations.

In this post, we examine a use case in which you’re migrating your Microsoft SQL Server database to Babelfish for Aurora PostgreSQL and you have SSRS reports using the application databases as source. We show you how to modify your SSRS reports to work with your databases after they have been migrated to Babelfish for Aurora PostgreSQL.

Babelfish endpoint

As you create an Aurora PostgreSQL-Compatible cluster with the Babelfish option enabled using the AWS Management Console, you have the option to select a PostgreSQL port (default 5432) and a TDS port (default 1433). After you create the cluster, you can find endpoint names on the Connectivity & security page. An Aurora PostgreSQL-Compatible cluster with the Babelfish option enabled listens to the TDS and PostgreSQL port. We call the combination of the endpoint name and TDS port the Babelfish endpoint.

Migration options

SSRS is a Windows service that must run on a server licensed for Microsoft SQL Server. Babelfish doesn’t support report server system databases (ReportServer and ReportServerTempDB). Therefore, you need to maintain the SSRS setup and associated licenses if you plan to run on premises or on Amazon Elastic Compute Cloud (Amazon EC2). If you have multiple SSRS deployments, you have the option to consolidate report server system databases in a single SQL Server instance to save costs. SSRS  varies by edition and we recommend selecting the right one for your use case. Based on your migration path, you can have different migration scenarios:

  • Migrate your on-premises SQL Server workload to Babelfish – You can continue to use your on-premises SSRS report server, associated report server databases, and point reports to the Babelfish endpoint over AWS Direct Connect. If the report latency isn’t within acceptable limits, we recommend hosting your SSRS instance on Amazon EC2 or Amazon Relational Database Service (Amazon RDS) for SQL Server. The following diagram illustrates the before and after of this scenario.
  • Migrate your SQL Server workload on Amazon EC2 to Babelfish – You can continue to use your Amazon EC2 SSRS setup and point reports to the Babelfish endpoint. In this process, you can downsize your Amazon EC2 instance to help save costs. If you want a managed SSRS instance, you can migrate your SSRS running on Amazon EC2 to Amazon RDS for SQL Server. The following diagram shows the before and after of this scenario.
  • Migrate your Amazon RDS for SQL Server workload to Babelfish – You can use SSRS with Amazon RDS for SQL Server and point reports to the Babelfish endpoint. In this process, you may be able to downsize your Amazon RDS for SQL Server instance to help save costs. The following diagram shows the before and after of this scenario.

Solution overview

For our use case, we take the scenario in which our SQL Server database engine and SSRS are running on Amazon EC2, and see how we migrate the user database to Babelfish and point SSRS reports to the Babelfish endpoint. The following diagram illustrates our architecture.

This solution has the following characteristics:

  • SSRS continues to run on the Amazon EC2 server.
  • A SQL Server database instance on Amazon EC2, which is hosting the ReportServer and ReportServerTempDB, continues to host them.
  • The user database schema and data are migrated to Aurora PostgreSQL-Compatible using the Babelfish endpoint.
  • The SSRS reports data source is updated with the Babelfish endpoint in the connection string
  • The application connection string is updated to point to the Babelfish endpoint.
  • The Amazon EC2 instance compute size may be downgraded to help save costs in both compute and the SQL Server license.

Prerequisities

To test this scenario, you must have the following prerequisites:

Update the SSRS data source

Let’s update the SSRS data source to the Babelfish endpoint.

  1. Open your report URL using the browser and navigate to the DATA SOURCES folder if you’re using a shared data source, to update the connection information.
  2. Navigate to the Properties pane of the data source that you want to update with the Babelfish endpoint and look for the Connection section.
  3. Keep Type set to Microsoft SQL Server.
  4. For Connection string, replace your Amazon EC2 SQL instance name with the Babelfish endpoint.

If you haven’t changed the database name between your source and target, you can keep the same Initial Catalog value. If your Babelfish endpoint is listening to some TDS port other than 1433, you need to change it in your connection string as well.

  1. In the Credentials section, enter the user name and password that you created for your Babelfish enabled Aurora PostgreSQL-Compatible cluster and database.
  2. Choose Test connection to validate your data source.
  3. After a successful connection, choose Apply.

Validate the solution

Now you can validate your reports. Navigate to PAGINATED REPORTS and open the report you want to test. Confirm that the report dashboard is loading successfully and can retrieve data using the Babelfish endpoint.

Clean up

To remove all the components created by this solution and avoid future charges, complete the following steps:

  1. Sign in to the console.
  2. Choose the Region where your Amazon EC2 instance and Aurora PostgreSQL-Compatible cluster instances reside.
  3. On the Amazon RDS console, choose Databases.
  4. Select the Aurora PostgreSQL-Compatible cluster instances.
  5. On the Actions menu, choose Delete.
  6. On the Amazon EC2 console, choose Instances.
  7. Select the Amazon EC2 instance.
  8. On the Instance state menu, choose Terminate instance.

Summary

In this post, we demonstrated different migration scenarios and how to run your SSRS reports pointing to a Babelfish endpoint. Try out Babelfish for Aurora PostgreSQL and run your SSRS reports pointing to a Babelfish endpoint as you migrate your SQL Server database to Babelfish.

If you have any comments or feedback, please leave them in the comments section.

About the Author

Rajib Sadhu is Senior Database Specialist Solutions Architect with over 15 years of experience in Microsoft SQL Server and other database technologies. He helps customers architect and migrate their database solutions to AWS. Prior to joining AWS, he has supported production and mission-critical database implementation across financial and travel & hospitality industry segments.