Share via

ARM Templates and SQL Server Reporting Services

  • Article

 

One of the Azure features I have in my list to try is the ARM templates and what better example than configuring SQL Server Reporting Services in a two machines configuration, one machine with Reporting Services and the second machine with SQL Server for the Reporting Services databases.

(Disclaimer: I work in the SQL Server Reporting Services Team , but this is a personal side project)

I tried to use as much of the existing infrastructure as possible so I took the existing SQL Server images in the azure gallery, I found that you can query those with the following Powershell commands

Get-AzureRmVMImageOffer -Location "westus" -Publisher "MicrosoftSQLServer" | Select Offer

Get-AzureRmVMImageSku -Location "westus"-Publisher "MicrosoftSQLServer" -Offer "SQL2014SP1-WS2012R2" | Select Skus

The first command return the list of available images and the second the skus available for those images.

Then I started authoring my ARM template using Creating and deploying Azure resource groups through Visual Studio article and the guidelines from Authoring Azure Resource Manager templates.

It uses extensively Desired State Configuration and the xSQLServer module.

All this end up in my git repo for Reporting Services ARM Templates , click on it to explore it, one of the templates documentation is below.

When it is done you will have SSRS available in the dns you specified in dnsNameForPublicIP , it will become something like

YourDNS.westus.cloudapp.azure.com/reports

You can either deploy it using the UI (see the big Deploy to Azure button below) or using powershell script provided in the git repo ,  you will need the template file locally and the parameters file to run that command the syntax is

.\Deploy-AzureResourceGroup.ps1 -ResourceGroupName 'YourResourceGroup' -ResourceGroupLocation 'westus' -TemplateFile '\Templates\SSRS-Catalog\AzureDeploy.json' -TemplateParametersFile 'ParametersSample.param.dev.json'

 

Create a new SSRS Server with a SQL catalog (2 Machines)

This template creates two new Azure VMs, each with a public IP address, it configures one VM to be an SSRS Server, one with SQL Server mixed auth for the SSRS Catalog with the SQL Agent Started. All VMs have public facing RDP and diagnostics enabled , the diagnostics is stored in a consolidated diagnostics storage account different than the vm disk

Click the button below to deploy

By Default it will create the SQL machines using the image SQL2014SP1-WS2012R2 and the Enterprise sku, the full list of available images and their SKUs can be obtained running

 Get-AzureRmVMImageOffer -Location "westus" -Publisher "MicrosoftSQLServer" | Select Offer
Get-AzureRmVMImageSku -Location "westus"-Publisher "MicrosoftSQLServer" -Offer "SQL2014SP1-WS2012R2" | Select Skus

For example

  • sqlImageVersion: SQL2014SP1-WS2012R2 sku: Enterprise
  • sqlImageVersion: SQL2016CTP3.3-WS2012R2 sku: Evaluation

For CTP Versions of SQL the only SKU available is Evaluation

It contains a modified version of xSQLServerRSConfig that supports machines that are non domain join and uses SQL authentication for connection SSRS with the database based on the DSC package https://www.powershellgallery.com/packages/xSQLServer/1.4.0.0.

It contains the DSC scripts from https://sqlvmgroup.blob.core.windows.net/singlevm/PrepareSqlServer.ps1.zip used by the Azure marketplace to create SQL Machines

 

This posting is provided "AS IS" with no warranties, and confers no rights

Comments

  • Anonymous
    August 04, 2016
    Awesome.A variation on this would be deployment of a two machine SSRS farm with a single SSRS Catalog DB