ARM Templates and SQL Server Reporting Services
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.
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
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'
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
- 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 http://www.powershellgallery.com/packages/xSQLServer/220.127.116.11.
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