The need to complement SQL Azure with Reporting Services is common, and most use IaaS VMs with named report server instances today.  This approach is well understood, but scaling, managing, and updating VMs and instances can be challenging.   This article introduces a new approach that uses Windows SQL Report Server containers.  The article is based on Windocks containers, and others have written on Microsoft’s SQL Server containers for SSRS support which could employ the same methods.   Full disclosure, I am a principal at Windocks.

SSRS containers integrated with SQL Azure databases are delivered fully configured in seconds, and simplify management with improved scalability and resource efficiency.  Images deliver configured containers in seconds that are easily updated.  Containers are two to three times more resource efficient than VMs, enabling scores of containers per VM.  Combining SSRS containers with Kubernetes management should extend SSRS investments with a scalable cloud-native architecture.

Preparation

Each connection to a SQL Azure database requires a SQL login with the DBO role on the source database.   The SQL Azure connection string is incorporated into the SSRS report and published to a report database.  The report server database, temp database, and associated symmetric encryption key are backed up, to support the container workflow.

The Windows Server is configured for Report Server in native mode, with updates to the webconfig, RsReportServer, and rsrvpolicy files for forms auth and other configurations.  The SQL logins required for SQL Azure are created.   The SQL Server instance is added to the Windocks configuration, which clones the SQL instance to deliver SSRS containers.   Each SSRS container runs as a Windows Service, and interoperates with Report Service Configuration tool, and Windows Auth if needed.   Alternatively, the webconfig and other files can also be delivered into each container at run time.

SSRS containers are configured to run using either a domain or service account, using the same account for all SSRS containers, or set individually for each container.   Credentials used to run report server containers are also provided “logon as a service” rights in the Local Security Policy.

Build SSRS images and containers

SSRS images include databases and scripts defined by a dockerfile.   The dockerfile below includes the Report Server database and Temp backups that include the SQL Azure connection string.   Multiple Report Server databases can be included, with databases selected for use at run time (more on this below).   Once built, the image will deliver SSRS containers with mounted report server databases, and with the symmetric encryption key restored.

The image is built using >docker build -t <imagename> c:\path\to\dockerfile.  The SQL rskeyrestore script runs at container creation to restore the symmetric encryption key using xp_cmdshell and the rskeymgmt utility.

SSRS containers are produced with >docker create <imagename>, with parameters to select the report server database to be configured, and provide the SQL Azure login and password.   In this example the SSRS containers run using a configured account or service credential, but the credential can also be declared using parameters for individual containers.

-e SSRS_Reporting_DB_Name=”ReportServer”

-e Ssrs_User_Name=”test”

-e Ssrs_User_Password=”Pa55word!!”

The container is started (>docker start <containerid>) using a few digits of the containerID.

The container is delivered configured with the assigned RS database and credentials, and is ready for use.

Updating container images

Images are updated by building with new Full or Differential backup, or database files, and is completed in a minute or two given the most size of report server backups. When an updated image is ready for production the existing container is deleted and replaced with the updated container.  If the source database supports scale out, the updated container could be deployed in a blue/green type deployment common with containers

Resilient containers and SQL Server updates

A container resiliency service detects pending server reboots, and stops containers and restarts them following the reboot.   This simplifies management and provides a behavior similar to “automatic/on” properties of conventionally installed instances.  Where uninterrupted reporting is needed, two or more container hosts are used behind a load balancer.

SQL Server updates are applied to the SQL Server instance managed for container delivery, without affecting existing containers.   Once the SQL Server instance is updated, containers are updated by simply provisioning new containers from existing SSRS images.

Kubernetes

Kubernetes is becoming a defacto standard for scalable cloud-native management of .NET and .NET Core applications, and it’s desirable to employ common cloud services where possible.   Running SSRS containers on Kubernetes should be the preferred approach for scalable SSRS support for SQL Azure.  The solution outlined in this article can be deployed and managed through Kubernetes workflows today.

Containers are maturing for enterprise use

SQL Server containers are entering their third year of use with a growing set of capabilities.   Microsoft features SQL Server 2019 Linux containers for high availability, and the new support options for SQL Server Report Server have proven themselves for both public and private cloud use.   Visit www.windocks.com to learn more about SSRS and Kubernetes support with Windows SQL Server containers.