Introducing SQL Server SSRS in Windows Containers

, 2018-10-04

Windocks launched SQL Server container support in early 2016, and SSRS has been among the most frequently requested features.   So, it’s a pleasure to introduce Windocks 3.5 Beta, which features Windows containers with the database engine and SSRS running as a Windows Service.   Windocks SSRS supports native mode on all editions of SQL Server 2008 through SQL Server 2016, with SQL Server 2017 slated for later this year. 

Containers combined with database cloning is growing in popularity for modernizing dev/test support, as complete isolated environments are delivered with TB class data in seconds.   Adding SSRS support enhances dev/test support for anyone involved in SSRS development.   Scalability testing also indicates that containers offer “elastic pool” benefits similar to SQL Azure. 

Setup and Planning:

Windocks installs on Windows 8.1 or 10, Pro and Enterprise editions, or Windows Server 2012 R2 or Server 2016, and runs on any on-premise infrastructure or public cloud.   SQL Server images support delivery of the database engine or the database engine with SSRS.  SQL Server image support is provided by a dedicated local SQL Server instance that is cloned to deliver containers by the Windocks service.  Windocks requires the instance be free of user databases, and SSRS configured in native mode.   This approach yields a number of benefits including simplified image maintenance, lightweight and scalability, and containers are free as “named instances.”  Installing Windocks is a simple 10 minute automated process.

Once installed, Windocks provides configuration support for use of assigned User accounts for SSRS containers, encrypted SQL sa passwords, and encrypted passwords for other processes (user accounts used with enterprise storage systems, etc.), and data delivery to conventional SQL Server instances, as well as Microsoft’s Docker SQL Server containers. 

Creating containers with Docker commands:

Open a command prompt and enter >docker images to confirm available images.   To create a 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 to ensure a unique match.  >docker ps provides a list of containers, and confirms the new container is running.  

Open SSMS and access the database engine and Report Server, using the loopback address for the engine, and Instance1000X for the Report Server.  Windocks supports use of Windows authentication or SQL sa credentials.  PowerShell is popular for automation, and containers can be delivered running with >docker run -d.   Containers are stopped with >docker stop <containerid>, and deleted with >docker rm <containerid>.

Configuring the SSRS Container

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

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 in size, and are delivered in seconds using 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 build the image.   The image is a full byte copy of the data, and requires time involved in the backup restoration.  The example below uses four full backups located on the Windocks host.   Universal file paths would be used for network located backups.   The VHD is built in the same location as the first backup listed.  

>docker build -t <imagename> c:\path\to\dockerfile builds the image.  Containers are created using either >docker create <image> followed by >docker start <containerid>, or >docker run -d <image>.  A user-defined port is included with -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. 

The complete database engine with SSRS is delivered, along with the database environment. 

Working with a web UI

Windocks support for Docker commands is important, as it aligns with Microsoft’s strategy for SQL Server 2017, and with industry standardization on Docker and Kubernetes.   But, developers and testers generally prefer a web UI.   Open either Chrome or Firefox browser and enter localhost (for local access),  and when the page resolves enter the local loopback address (127.0.0.1).  For remote access use the Windocks host 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.

After submitting “deliver” the page refreshes to show the latest container at the bottom of the Data Environments list, with the assigned port (the sa credentials are not exposed on the UI).  Access via SSMS or other client, and SSRS Configuration tool as discussed earlier. 

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

Beta testing?   

Windocks 3.5 Beta is available today, and we invite those interested in testing and providing feedback to email support@windocks.com for access.  SSRS support is available for SQL Server 2008 through SQL Server 2016, in native mode.   Support for SQL Server 2017 is slated for later this year.   Support for Scale-Out and Power BI is not included in this release. 

Rate

5 (1)

Share

Share

Rate

5 (1)

Related content