Azure SQL VM Automatic Registration and Reporting Services Images

[Amit Banerjee]

We have a number of customers that leverage the benefits offered by the SQL IaaS extension which I had blogged about a few days ago. One of the common feedback was the effort required to enable the extension for multiple VMs especially if you were running multiple SQL Server on Azure VM instances. We have created an easy option for customers to enable the SQL IaaS extensions on all SQL Server virtual machines in an Azure subscription using a feature called Automatic Registration. 

To enable automatic registration of your SQL Server VMs in the Azure portal, follow the steps:

1. Sign into the Azure portal.

2. Navigate to the SQL virtual machines resource page.

3. Select Automatic SQL Server VM registration to open the Automatic registration page as shown in the screenshot below.

4. Choose your subscription from the drop-down.

5. Read through the terms and if you agree, select I accept.

6. Select Register to enable the feature and automatically register all current and future SQL Server VMs with the SQL VM resource provider. This will not restart the SQL Server service on any of the VMs.

If you need to enable this option on multiple Azure subscriptions, then you can leverage this PowerShell script on GitHub to enable this option for a list of subscriptions.

We also introduced new Reporting Services virtual machine images for SQL Server 2016, 2017 and 2019. Now customers have the ability to deploy a Reporting Services virtual machine (SSRS) using Standard and Enterprise edition using pre-configured image types for ease of deployment. This also introduces the choice to deploy a SSRS virtual machine with flexible licensing model using Pay As You Go pricing or leverage your Software Assurance license mobility or Azure Hybrid Benefit when deploying on an Azure virtual machine.

Now you have the ability to move your database engine and reporting workloads into Azure virtual machines using our pre-configured images and automate the entire process using ARM templates. This saves you the time to install reporting services and configure it separately.

[pedrosanchez]

After launching the VM I can confirm that SSRS and SQL Server are installed, however SSRS doesn't have an existing database and to create it, one must login to the SQL Server. The issue is that the SQL credentials aren't configured nowhere during the creation of the VM and it doesn't take the same VM admin account (neither through Windows Authentication nor an SQL account).

So how does one configure SSRS?

[BretKnoll]

When I run the SQL 2019 Reporting Service Template a VM is created with Reporting Services. However, I do not see the SQL Serer Engine. The only sql service I see is

PS C:\Users\vm-sqlrs-testAdmin> Get-Service | Where-Object{$_.Name -like "*SQL*"}

Status Name DisplayName
------ ---- -----------
Running SQLServerReport... SQL Server Reporting Services

[BretKnoll]

I figured out my confusion. There are multiple templates for SSRS Standard, Enterprise and Developer as stand-alone VMs. This assumes second VM exists or is created to host the SSRS catalogs.  The good news is there are additional templates that include SQL 2019, SSRS 2019 on a Windows 2019 server.  I hope my confusion helps someone in the future. -- Good luck

[John_Joyner]

I turned the feature on and nothing happened. Existing Azure VMs running SQL Server were not discovered and no extension was installed. How can we troubleshoot this feature?

[vdunna]

Hello Everyone,

We have a requirement to migrate SSRS to azure from on prem. We have placed reporting server databases in Managed instance and configuring reporting services from Azure VM. SQL Server Version in Azure VM is 2016. We are not able to configure SQL Server Reporting Services from Azure VM. 

Do we have any issue with above mentioned configuration?. 

Getting below error.

ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: Cannot get edition information from catalog server., Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ;