Windocks SQL Server Containers on Windows
Take a look at a tutorial that explains the Windocks SQL Server container on Windows. Also explore the setup and planning.
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 the Windocks 3.5 Beta release introduces SQL Server containers with SSRS running as a Windows Service. SSRS supports native mode on SQL Server 2008 through SQL Server 2016, with SQL Server 2017 slated for later this year. SSRS containers combined with database cloning provide efficient support for Development and Test, and for production reporting systems, and runs on any on-premise infrastructure or public cloud.
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 images 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 (mssql-20XX) provisions the database engine running as a SQL Server process. A SQL Server instance with 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 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 of the ContainerID 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 a comma separator and port (127.0.0.1,1000X). Access the Report Server using the machine name and Instance1000X (machinename\Instance1000X). Windocks supports either Windows authentication or SQL sa credentials.
PowerShell is popular for automation, and containers can be delivered running with >docker run -d imagename. Containers are stopped with >docker stop ContainerID, and deleted with >docker rm ContainerID.
Configuring the SSRS Container
Open the Configuration Tool for the SQL release you're working with. For SQL Server 2016: 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 URL’s. Support for SSRS Scale-out and Power BI integration are not supported at this time.
Combining SSRS Containers With 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 restore process. The example below 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 imagename, or with >docker run -d imagename. 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 simpler web UI. Access the Windocks web UI with either Chrome or Firefox browser and enter “localhost” and when the page resolves to enter the local loopback address (127.0.0.1). Remote clients use the Windocks host IP address. The available images are presented, along with the option to select a subset of the data image and to 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 image building. Browse to the Dockerfile and scripts, highlight and select the files, assign an image name, and “build.”
Published at DZone with permission of Paul Stanton, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.