Tag Archives: SQL

Creating a SQL Database V12 Server in Azure

Few days ago my friend Business Solutions (Dynamics NAV) MVP Tharanga Chandrasekara came up with an interesting question. Creating a logical server for SQL Azure DB (PaaS) in the old Azure Service Management Portal is possible but why can’t we do that in the new Azure Resource Manager Portal. To find out what is happening I tried exploring the SQL PaaS option in the ARM portal.

When we create a new SQL database in the ARM portal we can create a logical server along with it as below.

SQL V12 1

But somehow when we checked two days back there was no Add button under SQL servers. We have tried the same thing in several Azure Subscriptions but there was no luck.

SQL V12 3

But today I checked again the same thing in one of my subscriptions and could see the Add button and could create a server without any problem.

SQL V12 2

Nothing out of the ordinary was mentioned in any forum as well and Tharanga has posted a question in User Voice. We were hoping the PG can shed some light into this. Whether it was a glitch on certain subscriptions or actually missing feature until now.

This led me to explore how to do this in ARM using PowerShell.

  • First you need to install the new Azure PowerShell module to start with. You can refer this to understand how to do so.
  • Then you can execute below cmdlets in Azure Powershell to login to your Azure Subscription and choose the exact subscription (if you have many Azure subscriptions under one account)

Add-AzureRmAccount
Select-AzureRmSubscription -SubscriptionId <Subscription ID>

  • Not all resources in ARM are available in all regions so it is always better to check whether the V12 database servers are available in the region you were planning to deploy.

(Get-AzureRmLocation | where-object {$_.Name -eq “Microsoft.Sql/servers” }).Locations

  • Next step is to create  a resource group in your desired region. I chose East US.

New-AzureRmResourceGroup -Name “jcbv12sql-RG” -Location “East US”

  • Then you can create the SQL V12 server and add firewall rules to allow any connections from outside Azure.

New-AzureRmSqlServer -ResourceGroupName “jcbv12sql-RG” -ServerName “jcbv12svr01” -Location “East US” -ServerVersion “12.0”

New-AzureRmSqlServerFirewallRule -ResourceGroupName “jcbv12sql-RG” -ServerName “jcbv12svr01” -FirewallRuleName “exrule1” -StartIpAddress “<First IP Address>” -EndIpAddress “<Last IP Address>”

Microsoft Azure new SQL IaaS configuration experience

Happy New Year to all of my blog readers.

2016 is going to be an exciting year as we wait for the newest releases of Azure Stack, Windows Server & System Center from Microsoft. In my new year post I’m going to share some happy news for all Azure Ninjas out there working on IaaS.

If someone asks me what Microsoft Product makes my Azure deployment most complex I’d answer SQL server. The reason is SQL server being a awesome product needs an extra DBA care. When you are provisioning a SQL server VM in Azure you need to think about IOPs, Connectivity, Backups, Security first hand and how to provide the same level of experience as of a  on-premises data center.

Microsoft Azure team understood the pain us system administrators face when it comes to SQL server configuration and came up with a set of new configuration options for SQL VMs in Azure Resource Manager Deployment Model. In order to use the new configuration experience you need to create a VM in new Resource Manager deployment model and it supports any version of SQL server that Azure marketplace offers.

Simplified Connectivity

SQL IaaS in Azure (1)

In the classic model in order to configure SQL server connectivity from on-premises using SQL Server Management Studio (SSMS) you had to first Remote Desktop to the VM, open the SQL Server port in Windows firewall, enable SQL Server Authentication, and to allow inbound connectivity had to create a Public Azure endpoint for the VM. The new experience allows you to do all of that in the portal itself during the provisioning time and you can select whether this SQL server can only be contact from the VM itself or within the Virtual Network.

Automated Patching & Backup

SQL IaaS in Azure (3)

Another pain that IT Pros encounter with SQL server is patching. The new automated patching capability allows the administrators to define a maintenance window at their convenience during the VM provisioning itself. So if your customers need to take off the burden of patches for their SQL VM instances in Azure this is a life saver.

SQL IaaS in Azure (4)

What about backup then? The new Automated backup feature allows administrators to automatically backup all databases in SQL Server and it is not enabled by default  as different workloads can have  different backup requirements. You can retain these backups up to 30 days and even encrypt them.

Storage Optimizations

Be it on-premises or cloud the most important thing in SQL server instance is storage. Previously in Azure classic deployment model we had to attach the required number of data disks to provide the IOPs and throughput manually and stripe the SQL files across the disks or create a Storage Pool to divide the IOPs or throughput across them.  The new deployment model has all of these included in to provisioning by enabling us to specify the required IOPs, throughput and VHD size within the allowable limit of the VM instance size. The cool thing is when you tweak these settings Azure automatically changes the number of data disks using  Windows storage spaces. So you no longer have to worry about calculations.

Also you can select between any of the below three storage optimization method for your SQL VM depending on your workloads.

  • General is the default setting and supports most workloads.
  • Transactional processing optimizes the storage for traditional database OLTP workloads.
  • Data warehousing optimizes the storage for analytic and reporting workloads.

SQL IaaS in Azure (2)

For the automation geeks you can use the Azure Resource Manager templates to make it even more automated for larger deployments. Considering the amount of effort and time taken previously for SQL IaaS VM configuration in Azure the new deployment experience offers much more hassle free one time setup for SQL workloads.

SQL 2012 Cluster not monitored in SCOM 2012 R2

The hardest part of a SCOM deployment is proper configuration of the monitoring environment. Most of the times system administrators forget to follow the Management Pack Guide for a particular application and spends enormous time to troubleshoot what went wrong in their deployment. Today we are going to explore how to resolve such a scenario associated with SQL 2012 Management Pack.

Following alert has been raised by SCOM indicating there aren’t sufficient permissions to monitor a SQL 2012 cluster. The error says cannot login to database.

SQL 2012 Error 1The SQL Server Monitoring Account Run As profile contains the necessary action accounts for the SQL Management Pack.

SQL 2012 Error 2By default this Run As profile will use Default Action Account for SQL MP. But in this case the default action account doesn’t have database privileges for the SQL cluster. So the solution is to create a separate Run As account for SQL which maps to a domain account with database rights (you can use an account with db_owner in SQL as long as it doesn’t jeopardize your security or else you can use db_reader) and then add it to the SQL Run As profile.

SQL 2012 Error 3This error is result of tightened access model in SCOM which needs you to properly configure Run As accounts with proper rights assignment to different monitored workloads in SCOM.

Error 31551 in SCOM Data Wareshouse

In my recent encounter with a SCOM 2012 R2 Deployment I have faced a strange issue with Data Warehouse database. I was getting below error after initial deployment (few days passed) and health status of management servers always display as critical.

Failed to store data in the Data Warehouse. The operation will be retried. Exception ‘SqlException’: Login failed for user ‘scom_admin’

Error 31551 1In my case scom_admin is the default action account. After spending sometime on isolating the issue I’ve discovered the culprit as an incorrectly configured Run As profile.

The Data Warehouse SQL Server Authentication Account by default uses the SQL Server authentication. In my case I have incorrectly specified my default action account as the Run As account for this profile. This should be the sa account for servers hosting Data Warehouse DB not a domain account. After removing the Run As account credentials for this Run As profile the health status of the management servers were green again and the error has stopped prompting.

Error 31551 2

SCOM 2012 R2 Installation woos | Error with SQL 2008 R2 Cluster

I’ve been working with a SCOM project for a while now. Few days back I faced a strange issue while doing the installation.

SCOM SQL 2008 R2 ErrorThe setup was like below.

Product Version
Operations Manager Setup SCOM 2012 R2
SQL Server (Always on Active-Active cluster) Microsoft SQL Server 2008 R2 SP3 10.50.6000.34 (X64)
OS (Management Server) Windows Server 2012 R2 Update 1
OS (SQL Cluster) Windows Server 2008 R2

Everything seemed normal and correct. All the firewall ports have been configured correctly. I have done following to isolate the issue.

  1. Tried to connect to a SQL Server 2012 cluster and it was successful.
  2. Verified the Management Server OS version.

The only culprit was the SQL Server version . As per official TechNet article SP3 is not listed in supported SQL versions. See here for the full article.

System Center 2012 R2 component SQL Server 2008 R2 SP1 Standard, Datacenter SQL Server 2008 R2 SP2 Standard, Datacenter SQL Server 2012 Enterprise, Standard (64-bit) SQL Server 2012 SP1 Enterprise, Standard (64-bit) SQL Server 2012 SP2
Operations Manager Data Warehouse
Operations Manager Operational Database

Operations Manager Reporting Server

I have tried to perform same on a test environment which had a SQL Server 2008 R2 SP3 Server with Windows Server 2012 R2 and was able to successfully connect to the SQL server. Even it’s not stated SQL Server 2014 also works fine to host the Operational Database & Data warehouse database.

The issue was with Operating System of the SQL Server Cluster. For SCOM 2012 R2 All server OS must be Windows Server 2008 R2 SP1 or above.

System Center 2012 R2 server-side component Windows Server 2008 Windows Server 2008 SP2 Windows Server 2008 R2 Windows Server 2008 R2 SP1 Windows Server® 2012 Standard, Datacenter Windows Server® 2012 R2 Standard, Datacenter
Operations Manager Management Server
Operations Manager Data Warehouse
Operations Manager Gateway Server
Operations Manager Web Console
Operations Manager Operational Database
Operations Manager Reporting Server

As you can see in above table and and this TechNet reference the server OS hosting any of the SCOM components should be 2008 R2 SP1 or above. Once we applied Service Pack 1 to the SQL Cluster host OS the setup was successfully completed without any issue.