SQL Server Containers With SSRS

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. 

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. 

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.

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.

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.  

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. 

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.

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. 

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.