Developing, testing with SQL Server Reporting Service (SSRS) containers

SQL Server versions 2008 to 2019, Azure SQL, RDS SQL

SSRS report development and testing is similar to application development and testing.The reports need to be constantly changed based on changes in requirements, database and application. A best practice is to keep the report files (RDL) files in a source code repository and update them regularly. While updating reports means, developers and testers need their own copies of the latest compatible databases that their updated reports run on. Then they upload the RDL files to SSRS servers. Once developers and testers finish their updates, they push the RDL files to a source code repository like Git. Then the RDL files are uploaded to SSRS servers used by analytics and reporting teams.

User acceptance testing (UAT) with SSRS containers involves the uploading of released RDL files and databases for each customer. This also requires each user to have their database and RDL files uploaded to their SSRS Server.

Analysts can work off a single SSRS server and a single database. For scale out you may need multiple SSRS servers.

Windocks orchestration, Windocks SSRS containers, and report development / testing

Windocks orchestration takes care of all of this for you so that developers, testers and reporting teams get the reports with the click of a button. Simply provide a spec to Windocks with the SQL Server databases needed, and the location of the repo for the RDL files. Windocks orchestration delivers a SQL Server container with SSRS to each developer, tester and analyst. The orchestator automatically pulls the RDL files from the Git repo and uploads them to the SSRS service in that container. The developer, tester and analyst simply clicks or touches a button and the reports are ready for them to work with using the SSRS web application or web service.

Data sources for SSRS containers

You may choose to use one of the following data sources for the SSRS container:

  • SQL Server database with the SQL Server engine in the same SSRS container
  • Data sources elsewhere (Azure SQL, RDS SQL, GoogleCloud SQL, Oracle instance, Oracle container)

Data sources in SQL Server engine in the same SSRS container

SQL refresh

In this case, you need clones of the source databases in the container and the report files (RDL) uploaded to the freshly created SSRS Report server database in the container.

Data sources elsewhere

SQL refresh

Here, you need a clone of the ReportServer and ReportServerTemp databases from elsewhere, so that the delivered SSRS container already has all the reports in it pointing to the data source database (Azure SQL, RDS SQL engine database.

Steps to start developing, testing reporting with SSRS containers

1. Install Windocks Download the Windocks Community Edition or email for a full featured evaluation edition. Provision a Windows Server VM (Server 2016, 2019, or 2022), install SQL Server (for SQL database delivery) and then install Windocks as described here. For Oracle database delivery, also install the Windocks service for Linux as described here
2. Prepare the backup files

If your data source is SQL Azure or RDS SQL instance, you need to backup ReportServerDb and ReportServerTempDB from there.

If your data source is the SQL engine in the same container, you need the backups for the source SQL Server databases. The Report server databases will be created for you and you don't need those backups. The RDL files will be automatically uploaded into the report server database for you.

Put the backup files or raw database files in a location accessible to the Windocks server and provide permissions to the machine running Windocks.

3. Provide the spec and scripts to the Windocks orchestrator to build the SSRS image Use this tutorial to create the dockerfile and the scripts needed
4. Deliver SQL Server SSRS containers with the reports ready to go The Windocks orchestrator co-ordinates different services to deliver SQL Server SSRS containers with the required databases and reports. Use a web application, command line or REST API to deliver databases on demand

Explore topics