DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Cloud Database Services Compared: AWS, Microsoft, Google, and Oracle
  • What Developers Need to Know About Table Partition Pruning
  • Rebuild System Databases in SQL Server
  • 10 Database Optimization Best Practices for Web Developers

Trending

  • Revolutionizing Financial Monitoring: Building a Team Dashboard With OpenObserve
  • It’s Not About Control — It’s About Collaboration Between Architecture and Security
  • How Large Tech Companies Architect Resilient Systems for Millions of Users
  • AI’s Role in Everyday Development
  1. DZone
  2. Data Engineering
  3. Databases
  4. SQL Server SSRS Scale Out With Containers

SQL Server SSRS Scale Out With Containers

This article focuses on Windows SQL Server containers and Windows Virtual Hard Drive (VHD) based cloning.

By 
Paul Stanton user avatar
Paul Stanton
·
Feb. 05, 19 · Tutorial
Likes (2)
Comment
Save
Tweet
Share
14.0K Views

Join the DZone community and get the full member experience.

Join For Free

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.

Image title

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.

Image title

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

Image title

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>.

Image title

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

Image title

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.

Image title

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.

Container sql Database engine

Opinions expressed by DZone contributors are their own.

Related

  • Cloud Database Services Compared: AWS, Microsoft, Google, and Oracle
  • What Developers Need to Know About Table Partition Pruning
  • Rebuild System Databases in SQL Server
  • 10 Database Optimization Best Practices for Web Developers

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!