Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Windocks SQL Server Containers on Windows

DZone's Guide to

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.

· Database Zone ·
Free Resource

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

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.

New Windocks Service Arch 750 x 530

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.

SSRS image 1 750 x 445

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.

SSRS image 2 750 x 400

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.

SSRS image 6 750 x 510

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.

SSRS image 3 750 x 230

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.

SSRS image 4 750 x 420SSRS image 5 750 x 450

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.

SSRS image 7 750 x 430

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.

SSRS image 8 750 x 257

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

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

Topics:
database ,tutorial ,containers ,sql server ,ssrs container ,windocks

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}