SQL Server SSRS Scale Out With Containers

Multiple web front-ends for scaling SQL Server report services is a popular deployment architecture that combines SQL Server Enterprise Edition instances with Always On availability groups. Recently released Windows SQL Server containers with SSRS support are drawing attention for new approaches to SSRS deployment and scale out. SQL Server standard edition containers are combined with database clones, providing each container a complete data environment, enabling a scalable and highly available architecture when operated behind a network Load Balancer.

Database cloning is a key aspect of the SSRS scale-out architecture, with each container operating with a complete set of databases. This article focuses on Windows SQL Server containers and Windows Virtual Hard Drive (VHD) based cloning, but the same architecture can utilize storage arrays as well. The trade-offs between VHDs and storage arrays are covered in separate sections below.

The combination of SSRS containers with database cloning is appealing for simplicity and operational savings. SSRS containers are also drawing interest as part of public cloud strategies, as SSRS containers can be integrated with AWS RDS or SQL Azure databases to provide a horizontally scalable reporting solution.

Database Cloning

Database cloning originated with storage arrays, with volume snapshots supported by a parent volume with read-only data access and Copy on Write for changes. This approach delivers large data environments in seconds but has seen limited use due to complex scripting and maintenance involved. The Windows file system does not support volume snapshots, but a similar solution is achieved with Virtual Hard Drives (VHDs). Database clones involve a parent Virtual Hard Drive that is mounted to SQL Server with source database backups restored to the VHD, creating a full byte copy of the environment. The parent VHD supports creation of clones (differencing disks), with each clone supporting read/write operation using a Copy on Write from the parent image. Creation of the parent VHD takes time to build as the backups are restored and become an immutable image. Clones are delivered in seconds, occupy <40 MB on delivery, and grow dynamically as changes are made.

Database clones can include dozens of databases, and scale to multiple terabytes. Windows VHD support was introduced with Windows Server 2008 and is proven in production support of Hyper-V. Windocks database cloning is relatively new, but VHDs are widely used in support of production SQL Server VMs, so it seems reasonable that VHD database cloning be explored for support of qualified SQL Server production use-cases.

Building a Database Clone Image

Container images are built using plain text dockerfiles. The example dockerfile uses the SQL Server 2016 SSRS image. Source databases, Report Server, and Tempdb backups are included with separate lines with paths to their location. T-SQL and PowerShell scripts can be included to address database or instance configuration.

The SSRS container delivers the database engine and report server running in a shared container as Windows Services. The report server is delivered configured with a default report server database, so to avoid database name conflicts, the report server database name in the dockerfile must include reportserverssrs in the db name, and the tempdb must include both reportserverssrs and tempdb in the database name.

Open a command prompt and build the image: >docker build -t <imagename> c:\path\to\dockerfile. The image is confirmed by running >docker images.

Images are immutable and deliver identical environments for each host in the scale-out architecture. To support SSRS reporting, the database image should be updated daily. This is accomplished with PowerShell scripts to monitor the backup folder and to automate the image build when the new backups are delivered. When the daily updated image is completed, the containers are created and pre-existing containers are deleted. This process provides effective 24 x 7 reporting.

Deliver SSRS Containers With Data

The image delivers the SSRS container and a complete set of databases, so each container (or instance) can operate independently and without resource contention. Images supporting conventional SQL Server instances use a similar dockerfile. Containers are created using >docker create <imagename>, and started by referencing the first few digits of the container ID, >docker start <containerid>.

Access the new container using Windows Auth and SQL Server Management studio using the image name (server\Instance10005).

Working With a Web UI

While the SSRS scale out operation would normally be automated, a web UI simplifies management and creation of images and containers. The image below shows the available images along with the current containers. A visual tool is also included for the selection of dockerfiles for image building. Access to the web UI includes user/group level authentication.

Load Balancers

SSRS scale-out architectures require a load balancer to distribute inbound traffic to the instances. The only difference posed in the new scale-out architecture is a need to handle distribution of traffic to SQL Server containers that share a host IP address but operate on separate ports. This is handled with an inbound Network Address Translation rule that forwards traffic to specific ports on the container host. NAT and port forwarding are supported by most enterprise load balancers.

Storage Arrays or Windows VHD-Based Database Clones?

The trade-off between VHD and storage arrays boils down to access and scalability. Virtually all SQL Server environments reside in a Windows environment and support Windows-based VHD cloning. Windows VHD clones scale to support images up to 3-4 TB when matched with high-performance SSD storage (Storage Spaces Direct can support larger images). Windows VHD cloning is the first choice of DBAs due to the ease of adoption. Storage arrays, on the other hand, provide greater image scalability and performance, easily scaling to support environments of 10-20 TB or more. When storage arrays are available and images exceed 4 TB, they are preferred.

New Options for SSRS Scale Out

Windows Virtual Hard Drives (VHDs) have been used to host production SQL Server environments for many years, and this article details how VHDs can be applied to support SSRS scale out with SQL server standard edition. SSRS containers are being adopted to address gaps in public cloud services and create new architectures for SSRS scale out. Many should also benefit from the freedom to support the use of cloned environments with both SQL Server containers and conventionally installed instances.