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. 

Windocks recently released Windows SQL Server containers with SSRS support, which is drawing attention for new approaches to SSRS deployment.   One approach combines SQL Server standard edition containers with database clones to provide each container a complete data environment.   The same approach can also be used with standard edition SQL Server instances.   Full disclosure, I am a co-founder of Windocks, and this article describes a SSRS scale out architecture being adopted by Windocks customers.   The architecture, however, is not limited to Windocks and can be supported with a variety of tools described in this article. 

Database cloning is a key aspect of the SSRS scale out architecture, with database clones providing each container a complete set of databases.  Two or more VMs operated behind a load balancer delivers a highly available and scalable reporting service.  This article focuses on Windows SQL Server containers and Windows Virtual Hard Drive (VHD) based cloning, but the same architecture can support SQL Server Linux containers or conventional instances (Windows or Linux).   Redgate SQL Clone, for example would support SQL Server instances.   Other options include the use of storage arrays instead of Windows VHD based clones.   The trade-offs between SQL containers and instances, and 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 becomes 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 and Redgate SQL database cloning are 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 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 forward traffic to specific ports on the container host.   NAT and port forwarding are supported by most enterprise load balancers.

SQL Server Containers or Instances? 

Container adoption is growing rapidly with industry-wide adoption for DevOps and modernization strategies.   Microsoft emphasizes SQL Server containers on linux, and the strategic role of containers for modern IT and development.     Both SQL containers and conventional instances are well supported for this SSRS scale out architecture. 

Security:  SQL Server instances deliver established security for on-premise and public cloud.   Windocks SQL Server containers are widely adopted among financial services, and have evolved to address the stringent security needed.   Security includes compatibility with Active Directory and Windows Authentication, and containers are in-fact conventional SQL Server instance (named instance), and support existing encryption systems, include encrypted secrets, and support operation as a secure “SQL Server sandbox.” 

Public Cloud and on-premise:   on the public cloud SQL Server is typically used as part of AWS RDS or SQL Azure services.  SQL Server containers provide support for automated and horizontally scalable solution, and are generally preferred for CI/CD and DevOps strategies.  For on-premise use Windocks supports use of existing storage arrays and VHD clones, with database clone delivery to containers and instances, providing freedom to work with both containers and instances. 

SQL Services:  this article highlights new opportunities created by SQL Server containers with SSRS, database engine, and SQL Browser support.  SQL Server containers, however, do not offer feature parity with instances with support for SSIS, SSAS, Replication, and other services.   Where the full stack of SQL Server services is important SQL Server instances are used. 

Database cloning with Windows VHD or Storage Arrays?

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 significantly 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, with some added complexity involved in coordinating use with storage infrastructure teams.  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 use of cloned environments with both SQL Server containers and conventionally installed instances. 

You can get started today in exploring these new architectures by downloading the free Windocks Community Edition.