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

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

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

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

  • Using Envoy Proxy’s PostgreSQL and TCP Filters to Collect Yugabyte SQL Statistics
  • A Guide to Container Runtimes
  • Docker vs Kubernetes: Which to Use and When?
  • GenAI: From Prompt to Production

Trending

  • Microsoft Azure Synapse Analytics: Scaling Hurdles and Limitations
  • Create Your Own AI-Powered Virtual Tutor: An Easy Tutorial
  • AI Meets Vector Databases: Redefining Data Retrieval in the Age of Intelligence
  • While Performing Dependency Selection, I Avoid the Loss Of Sleep From Node.js Libraries' Dangers
  1. DZone
  2. Software Design and Architecture
  3. Cloud Architecture
  4. SQL Server Containers With SSRS

SQL Server Containers With SSRS

SSRS containers, combined with DB cloning, provide efficient support for Development and Test, and for production reporting systems.

By 
Paul Stanton user avatar
Paul Stanton
·
Oct. 23, 18 · Tutorial
Likes (2)
Comment
Save
Tweet
Share
8.0K Views

Join the DZone community and get the full member experience.

Join For Free

SSRS support has been among the most frequently requested new features and Windocks 3.5 introduces SQL Server containers with the database engine and SSRS running as Windows Services. Windocks 3.5 supports native mode on SQL Server 2008 through SQL Server 2016, with SQL Server 2017 slated for later this year. 

Image title

Setup and Planning

Windocks installs on Windows 8.1 or 10, Pro or Enterprise editions, or Windows Server 2012 R2 or Server 2016, with SSRS support for all editions of SQL Server 2008 through 2016 (SQL 2017 support is slated for later this year). Install on a machine that has one or more SQL Server instances that can be managed by the Windocks service, which clones the instance to create containers. SQL Server containers are lightweight (~300 MB), so can be stored on the system disk, or assigned to a separate disk. Database clone mages are each a full byte copy of the data environment and should be stored on a separate disk or network attached storage. The automated Windocks install takes 10-15 minutes to complete. 

SQL Server Images and Containers

Windocks 3.5 introduces a new SQL Server image that includes the database engine and SSRS running in a container as Windows Services (mssqlreport-20XX). The earlier image provisions the database engine only, running as a SQL Server process (mssql-20XX). A SQL Server instance that includes SSRS configured in native mode supports both images. The instructions in this article work with both images provided the proper image name.

Provision an SSRS Container

Following the install the Windocks service auto-starts. Open a command prompt and enter >docker images to view available images. To create an SSRS container enter >docker create mssqlreport-20XX.   The command line returns the ContainerID and port. In this example, Windocks is configured to deliver a SQL sa password in plain text, but this can be configured to allow for no sa password or for it to be encrypted. Start the container using >docker start <containerid> using 2-3 digits of the container id to obtain a unique match. >docker ps provides a list of containers and confirms the new container is running. 

Image title

Open SSMS to access the database engine and Report Server. Use the loopback address for the engine with the container port with a comma separator (127.0.0.1,1000x). Access the Report Server using the machine name and Instance1000x (machinename\Instance1000x). Windocks supports the use of Windows authentication or SQL sa credentials.

Image title

Containers are stopped with >docker stop <containerid>, and deleted with >docker rm <containerid>.

Configuring the SSRS Container

Open the Configuration Tool in C:\Program Files (x86)\Microsoft\SQLServer\130\Tools\Binn\RSConfigTool. The SSRS container can be configured to use different service accounts, databases, email accounts, and URLs. Support for SSRS Scale-out and Power BI integration are not supported at this time.

Image title

Adding Database Clones

Database clones are read/write supporting databases accessed from a Windows Virtual Hard Drive (VHD). Clones can scale from one to scores of databases and to multi-terabytes and are delivered in seconds with less than 40 MB of storage. Clones are created from a parent VHD image that is a read-only full byte copy of the data, which is built using full or differential backups.  

A plain text configuration file (dockerfile) specifies the backups along with SQL scripts used to create the image. The resulting image is a full byte copy of the data and requires time involved in the backup restoration. The example uses four full backups located on the Windocks host. Universal file paths are used for network located backups. The parent VHD is built in the same location as the first backup listed.  

Image title

Build the image with >docker build -t <imagename>c:\path\to\dockerfile. Containers are created using >docker create <image>, or with >docker run -d <image>. A user-defined port is included using -p 10XXX and an assigned SQL sa password with -e SA_PASSWORD=”password”. User-defined ports and credentials are popular for developers working with .NET or Java applications. 

Image title

Image title

Working With the Windocks Web UI

Windocks support for standard Docker commands aligns with Microsoft’s strategy for SQL Server 2017, and with industry standardization on Docker and Kubernetes. But, developers and testers generally prefer to work with a web UI. Access the Windocks web UI with either Chrome or Firefox browser and enter “localhost” and, when the page resolves, enter the local loopback address (127.0.0.1). Remote clients use the Windocks IP address. The available images are presented, along with the option to select a subset of the data image and assign a port and sa password.

Image title

After submitting the “deliver” option the page refreshes to show the new container at the bottom of the Data Environments list, with the port and assigned sa credentials (the sa credentials are not exposed on the UI). The environment can be accessed with SSMS and the SSRS Configuration Tool as discussed earlier. 

Image title

The web UI also includes support for building clonable images. Browse to the dockerfile and scripts, highlight and select the files, assign an image name, and “build.”  

Get Started With SQL Containers, Clones, and SSRS 

Windocks 3.5  is available, and you can get started today by downloading the free Community Edition http://windocks.com/community-docker-windows. Work is underway to add user/group role-based access to the images and containers, and support for SQL Server 2017 SSRS.

Docker (software) Kubernetes sql

Opinions expressed by DZone contributors are their own.

Related

  • Using Envoy Proxy’s PostgreSQL and TCP Filters to Collect Yugabyte SQL Statistics
  • A Guide to Container Runtimes
  • Docker vs Kubernetes: Which to Use and When?
  • GenAI: From Prompt to Production

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!