TECHNICAL DOCS

SQL Server Reporting Service (SSRS) containers for SQL Server, SQL Azure, and RDS SQL

Windocks delivers multiple SQL Server SSRS containers running on the same machine - on-prem, or cloud VM. Two scenarios are typical for developing and testing SSRS reports with containers. In both scenarios, your reports are available on container delivery.

  • Scenario 1: SSRS working with data sources in the same container, with .rdl files copied from storage or pulled from a Git repo and auto uploaded to the container SSRS.
  • Scenario 2: SSRS containers working with remote source databases (another server, Azure SQL, RDS SQL, etc.), based on report server databases from the other server cloned to SQL SSRS containers.

In addition, you can deliver SQL Server, SSRS, SSIS, and SSAS in one container.

 

 

1. Same Container

For scenario 1: Create the dockerfile below: (see the sample windocks\samples\ssrsrdl for the scripts, Rss files). Then build the image using the web app, REST API or with
docker build -t imagename path\to\directory\containing\dockerfile

After image build, create the SSRS containers using the web app, REST API or with:
docker run -d imagename
 

FROM mssqlall-2019

# Source database
SETUPCLONING FULL customerdata c:\windocks\dbbackups\customerdatafull.bak

COPY replaceDataSource.ps1 .

# Rdl file can be in a git repo or you can build it into the image by putting it in the same directory as dockerfile when you build
# To put it in the image, uncomment the line below
COPY test.rdl .

COPY upload.rss .
COPY uploadRdlFile.bat .

ENV USE_DOCKERFILE_TO_CREATE_CONTAINER=1

# If RDL files are in a git repo
# Step 1. Install the git program on the machine running Windocks
# Step 2. Create a script manifest file such as manifest.txt with each line as a path to a SQL script to run, the path is relative to the git repo home (See the example in this directory)
# Step 3. Set up your git repo if not already there (For private repos it has to be a Git over SSH repo)
# Details on setting up a git repo are here https://git-scm.com/book/en/
v2/Git-on-the-Server-Getting-Git-on-a-Server
# Step 4. Push your manifest.txt to the git repo to your branch
# Step 5. See the GIT CREDENTIALS section below for how to setup the private key. For public repos, just the git clone command is sufficient

# When container is created, the RUN command below clones the git repo to a directory in the container named homedirforrepo
# RUN "C:\Program Files\Git\cmd\git.exe" clone https://github.com/WinDocks/ssrsrdl.git homedirforrepo

# This is the format for SSH secure git repo (commented)
# RUN "C:\Program Files\Git\cmd\git.exe" clone securedvmhostname:/path/to/git/repo.git homedirforrepo

# If you use git branches
# STARTENV_GIT_BRANCH_ENDENV is a variable name for the git branch you want - the variable value is specified at container create time
# When container is created, changes the container's git repo code to the branch specified at container create time
# The convention for these run time variable names is to begin with STARTENV_ and end with _ENDENV

# RUN git -C homedirforrepo checkout STARTENV_GIT_BRANCH_ENDENV

RUN powershell.exe $ContainerDir\replaceDataSource.ps1 $ContainerDir\test.rdl LAPTOP-DA9D6LTM\INSTANCE$ContainerPort

RUN RUN_ON_CONTAINER_START uploadRdlFile.bat $ContainerPort $ContainerDir

# ***** README ******
# Use this dockerfile to do database script development for releases

# 1. GIT REPO STEPS
# Put your RDL files in the git repo

2. GIT CREDENTIALS
# Git repos that require authentication
# You must have a git repo which supports SSH and SSH private key - Email support@windocks.com if you need help
# cd C:\Windows\System32\config\systemprofile
# Copy the private key privatekey.pem to C:\Windows\System32\config\systemprofile
# Create a file named config (no extensions) and enter the following with your ip address and user name for the git repo:
# Host securedvmhostname
# Hostname 142.4.299.87
# IdentityFile C:\Windows\System32\config\systemprofile\privatekey.pem
# User yourSshUserName

 

 

2. Remote source data

For scenario 2: Create the dockerfile below: (see the sample windocks\samples\ssrsWithSqlAzure for the scripts and files). Then build the image using the web app, REST API or with
docker build -t imagename path\to\directory\containing\dockerfile

After image build, create the SSRS containers using the web app, REST API or with:
docker run -d imagename

FROM mssqlall-2019

# Change to your SQL version above

# Setup the report server database and the report server temp database
SETUPCLONING FULL ReportServerDb path\to\reportserverdbbackup.bak
SETUPCLONING FULL ReportServerTempDB path\to\reportservertempdbbackup.bak

# Backup the symmetric key from wherever the report server database was created
# Use Report Server Configuration tool or run the backupSymmetricKey.sql script against the original instance
# RUN path\to\sqlcmd.exe -S instanceOnWhichReportServerDbWasCreated backupSymmetricKey.sql

# You may want to copy the symmetric key to a secure location, the COPY below copies it into the image
COPY path\to\key .

# Anything above this is run for once at image build time at create time
ENV USE_DOCKERFILE_TO_CREATE_CONTAINER=1
# Anything below this is run for each container at create time

# Run the sql script for each container that restores the symmetric key

RUN restoreSymmetricKey.sql

# If your report server database has been used by other SSRS instances, then delete those keys from the Keys table in the report server Db
# Use a script or do it manually
# Don't delete the key for this SSRS instance though

# ***** README ******
# 1. If you already have report server db and temp db, Get a backup of the report server db and the report server temp DB
# 2. Note the exact name of the report server db, say ReportServerDb
# 3. Get a backup of the key and save it (use UI rsconfig tool or command line rskeymgmt)
# 4. docker build -t rsimage path/to/dockerfile
# 5. docker create -e SSRS_Reporting_Db_name="ReportServerDb" rsimage # The full command is docker create -e SSRS_SQL_Engine_Name_Comma_Port="MachineName,1433" -e SSRS_Reporting_Db_Name="ReportServerDb" -e Service_User_Name="Domain\user1" -e Service_Password="pass" -e Ssrs_User_Name="sa" -e Ssrs_User_Passsword="pass" yourimagename
# 6. docker start container id
# 7. Either use the script restoreSymmetricKey.sql as shown above or Open Report server configuration tool to restore the symmetric key
# 8. In SSMS open the report server db the Keys table and delete all keys except the one for this SSRS container

 

 

Delivering SQL Server, SSRS, SSAS, and SSIS in one container

To just deliver a container with SQL Server 2016 with SSRS, SSAS and SSIS, run the command below, use the Windocks web application or use the Windocks REST API.

docker run -d mssqlall-2016

This delivers a SQL Server container with SQL Engine, SSRS, SQL Agent, analysis service (SSAS) and integration service (SSIS). Run the same command to get multiple containers running on the same machine. For other versions of SQL Server, simply replace 2016 with the version you want. 

Extend CI/CD to your data layer