A new approach for using SQL Server Report Services (SSRS) with AWS RDS and Azure SQL source databases.
AWS RDS and SQL Azure are popular SQL Server services, but SSRS support has been limited to IaaS VMs (since this article AWS RDS has added SSRS support). This article introduces SSRS containers for reporting on AWS RDS or SQL Azure source databases. This article outlines steps for AWS RDS, but roughly the same steps apply to SQL Azure databases as well.
SSRS containers offer distinct advantages of conventional named instances. Containers are delivered in seconds and can scale to 50 or more containers per VM, are easily versioned and updated, and scale out as needed. SQL containers modernize enterprise workloads, and bring SQL Server into a microservices and cloud-native architecture.
AWS RDS SQL Server databases are managed via a SQL login, and the RDS SQL connection string is included in the report server report and published to the report server and temp databases. The report server databases and symmetric encryption key are backed up to support creation of SQL container images.
The Windocks SQL Server container host is configured to support SSRS in native mode, with forms auth and SQL logins as needed. SSRS containers are configured to run using an AD account, service or local account, or these credentials can alternatively be applied individually to containers at run time.
A dockerfile specifies the SQL Server base image and the report server and temp database backups. The databases are restored to a local Windows Virtual Hard Drive, which is cloned for each container created from the image. A SQL script is included that restores the symmetric encryption key at container creation. Multiple Report Server databases can be included, with databases configured for use at run time (more on this below).
The image is built using >docker build -t c:\path\to\dockerfile. Commands included below the environment variable ENV are run at container creation. The rskeyrestore script restores the symmetric encryption key using xp_cmdshell and the rskeymgmt utility. Once built, the image supports horizontal scale out, delivering containers with the same report server databases, but with different assigned container ports.
SSRS containers are produced with >docker create , with parameters to select the report server database to be configured, and the SQL login and password. In this example the SSRS containers run using a globally configured account, 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 using a few digits of the containerID.
The container is delivered configured with the assigned RS database and credentials, and is ready for use.
Images are updated with new report server database backup, or files, and is completed in a minute or two given the small size of report server databases. 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 a blue/green type deployment could be used.
A resiliency service detects pending server reboots to stop containers gracefully, and restarts them following the reboot. This provides a support similar to “automatic/on” properties of conventionally installed SQL Server 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.
SSRS containers simplify management, and improve scalability, but EKS is expected to be preferred for scaling and managing SSRS container environments for larger environments, along with .NET and .NET core applications. The solution outlined in this article can be deployed and managed on Kubernetes workflows today.
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 are proven for both public and private cloud use. To explore use of SSRS containers contact us for a free evaluation here.
We are excited to release SSRS support for SQL Server containers, running as a native Windows Service. In this article we take a detailed look at the...
In this article we examine the use of SSRS containers combined with database cloning as a new approach to SSRS scale out, and one that supports use...
In this article we take a look at Windocks new support for creating SQL Server database clones that are delivered to Microsoft's official Docker SQL...