Azure SQL VM Automatic Registration and Reporting Services Images

Published 10-15-2020 07:59 AM 4,688 Views
Microsoft

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.

automatic-registration.png

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.

 

Screenshot 2020-10-15 014718.png

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.

3 Comments
Regular Visitor

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?

no-db.png

no-creds.png

Visitor

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

Visitor

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1783947%22%20slang%3D%22en-US%22%3EAzure%20SQL%20VM%20Automatic%20Registration%20and%20Reporting%20Services%20Images%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1783947%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20have%20a%20number%20of%20customers%20that%20leverage%20the%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fsql-server%2Frehost-your-sql-server-to-azure-virtual-machines-for%2Fba-p%2F1698452%22%20target%3D%22_self%22%3Ebenefits%20offered%20by%20the%20SQL%20IaaS%20extension%3C%2FA%3E%20which%20I%20had%20blogged%20about%20a%20few%20days%20ago.%20One%20of%20the%20common%20feedback%20was%20the%20effort%20required%20to%20enable%20the%20extension%20for%20multiple%20VMs%20especially%20if%20you%20were%20running%20multiple%20SQL%20Server%20on%20Azure%20VM%20instances.%20We%20have%20created%20an%20easy%20option%20for%20customers%20to%20enable%20the%20SQL%20IaaS%20extensions%20on%20all%20SQL%20Server%20virtual%20machines%20in%20an%20Azure%20subscription%20using%20a%20feature%20called%20Automatic%20Registration.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20enable%20automatic%20registration%20of%20your%20SQL%20Server%20VMs%20in%20the%20Azure%20portal%2C%20follow%20the%20steps%3A%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3E%3CP%3ESign%20into%20the%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fportal.azure.com%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%20data-linktype%3D%22external%22%3EAzure%20portal%3C%2FA%3E.%3C%2FP%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CP%3ENavigate%20to%20the%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%2F%23blade%2FHubsExtension%2FBrowseResource%2FresourceType%2FMicrosoft.SqlVirtualMachine%252FSqlVirtualMachines%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%20data-linktype%3D%22external%22%3E%3CSTRONG%3ESQL%20virtual%20machines%3C%2FSTRONG%3E%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eresource%20page.%3C%2FP%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CP%3ESelect%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EAutomatic%20SQL%20Server%20VM%20registration%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eto%20open%20the%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EAutomatic%20registration%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Epage%20as%20shown%20in%20the%20screenshot%20below.%3C%2FP%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CP%3EChoose%20your%20subscription%20from%20the%20drop-down.%3C%2FP%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CP%3ERead%20through%20the%20terms%20and%20if%20you%20agree%2C%20select%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EI%20accept%3C%2FSTRONG%3E.%3C%2FP%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CP%3ESelect%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3ERegister%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eto%20enable%20the%20feature%20and%20automatically%20register%20all%20current%20and%20future%20SQL%20Server%20VMs%20with%20the%20SQL%20VM%20resource%20provider.%20This%20will%20not%20restart%20the%20SQL%20Server%20service%20on%20any%20of%20the%20VMs.%3C%2FP%3E%0A%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22automatic-registration.png%22%20style%3D%22width%3A%20672px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F226825iA992557641205BA0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22automatic-registration.png%22%20alt%3D%22automatic-registration.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20you%20need%20to%20enable%20this%20option%20on%20multiple%20Azure%20subscriptions%2C%20then%20you%20can%20leverage%20%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2Fmicrosoft%2Ftigertoolbox%2Fblob%2Fmaster%2FAzureSQLVM%2FRegisterSubscriptionsToSqlVmAutomaticRegistration.ps1%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Ethis%20PowerShell%20script%3C%2FA%3E%20on%20GitHub%20to%20enable%20this%20option%20for%20a%20list%20of%20subscriptions.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20also%20introduced%20new%20Reporting%20Services%20virtual%20machine%20images%20for%20SQL%20Server%202016%2C%202017%20and%202019.%20Now%20customers%20have%20the%20ability%20to%20deploy%20a%20Reporting%20Services%20virtual%20machine%20(SSRS)%20using%20Standard%20and%20Enterprise%20edition%20using%20pre-configured%20image%20types%20for%20ease%20of%20deployment.%20This%20also%20introduces%20the%20choice%20to%20deploy%20a%20SSRS%20virtual%20machine%20with%20flexible%20licensing%20model%20using%20Pay%20As%20You%20Go%20pricing%20or%20leverage%20your%20Software%20Assurance%20license%20mobility%20or%20Azure%20Hybrid%20Benefit%20when%20deploying%20on%20an%20Azure%20virtual%20machine.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-10-15%20014718.png%22%20style%3D%22width%3A%20868px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F226826iF3ABA189D6A26724%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202020-10-15%20014718.png%22%20alt%3D%22Screenshot%202020-10-15%20014718.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ENow%20you%20have%20the%20ability%20to%20move%20your%20database%20engine%20and%20reporting%20workloads%20into%20Azure%20virtual%20machines%20using%20our%20pre-configured%20images%20and%20automate%20the%20entire%20process%20using%20ARM%20templates.%20This%20saves%20you%20the%20time%20to%20install%20reporting%20services%20and%20configure%20it%20separately.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1783947%22%20slang%3D%22en-US%22%3E%3CP%3ECustomers%20now%20have%20the%20ability%20to%20%3CSPAN%3Eenable%20the%20%3CSTRONG%3Eautomatic%20registration%3C%2FSTRONG%3E%20feature%20in%20the%20Azure%20portal%20to%20automatically%20register%20all%20current%20and%20future%20SQL%20Server%20on%20Azure%20VMs%20with%20the%20SQL%20VM%20resource%20provider%20in%20lightweight%20mode.%20Recently%2C%20we%20released%20SQL%20Server%20Reporting%20Services%20virtual%20machine%20images%20which%20allows%20customers%20to%20deploy%20their%20BI%20workloads%20using%20pre-configured%20VM%20images.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1783947%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerAzureVM%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2206434%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20SQL%20VM%20Automatic%20Registration%20and%20Reporting%20Services%20Images%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2206434%22%20slang%3D%22en-US%22%3E%3CP%3EAfter%20launching%20the%20VM%20I%20can%20confirm%20that%20SSRS%20and%20SQL%20Server%20are%20installed%2C%20however%20SSRS%20doesn't%20have%20an%20existing%20database%20and%20to%20create%20it%2C%20one%20must%20login%20to%20the%20SQL%20Server.%20The%20issue%20is%20that%20the%20SQL%20credentials%20aren't%20configured%20nowhere%20during%20the%20creation%20of%20the%20VM%20and%20it%20doesn't%20take%20the%20same%20VM%20admin%20account%20(neither%20through%20Windows%20Authentication%20nor%20an%20SQL%20account).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20how%20does%20one%20configure%20SSRS%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22no-db.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F263316i21C6F768966BBDB8%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22no-db.png%22%20alt%3D%22no-db.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22no-creds.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F263319i14292F7EA0666AAF%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22no-creds.png%22%20alt%3D%22no-creds.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Version history
Last update:
‎Oct 15 2020 08:00 AM
Updated by: