Automate Azure SQL Size using Azure Automation (Manual)

Recently a customer asked me how to save cost on their Azure SQL database without moving away from DTU based subscription model. In this case this customer knows exactly at what time their database is heavily utilized, and when it’s idling. So with a script its easy to automate.

In this manual we are going to size a SQL database from S4 to S3.

Step 1: In this first step we are going to add some modules to your Automation Account. Go to modules, and click on Browse gallery

From the Gallery search for az.accounts, click on it

Next make sure to Import the module

Now browse the Gallery again, this time search for az.sql and make sure to import this module as well.

STEP 2: This next step is important. We will need to create and assign a Run As Account when you’ve chosen not to create a run as account on the setup of your automation Account. Go to Run as Account, and click on Create Azure Run As Account

Click on Create

STEP 3: Now we will need to add some variables to your automation account. These variables will need to be filled with information about your Azure SQL Database and Server. Create the following variables, and make sure that you fill them.

  • Resourcegroup
  • Servername (without database.windows.net)
  • Database

STEP 4: Now go to runbooks, and create a new runbook!

Give your runbook a name, as type select PowerShell!

In the new opened window copy and paste the code from below. Adjust the variables $Edition and $PricingTier to your needs.

 $ResourceGroupName = Get-AutomationVariable -Name "Resourcegroup"
 $ServerName = Get-AutomationVariable -Name "Servername"
 $DatabaseName = Get-AutomationVariable -Name "Database"
 $Edition = "Standard"
 $PricingTier = "S4"

 
# Keep track of time
$StartDate=(GET-DATE)
 
 
 

# Log in to Azure with AZ (standard code)

Write-Verbose -Message 'Connecting to Azure'
  
# Name of the Azure Run As connection
$ConnectionName = 'AzureRunAsConnection'
try
{
    # Get the connection properties
    $ServicePrincipalConnection = Get-AutomationConnection -Name $ConnectionName      
   
    'Log in to Azure...'
    $null = Connect-AzAccount `
        -ServicePrincipal `
        -TenantId $ServicePrincipalConnection.TenantId `
        -ApplicationId $ServicePrincipalConnection.ApplicationId `
        -CertificateThumbprint $ServicePrincipalConnection.CertificateThumbprint 
}
catch 
{
    if (!$ServicePrincipalConnection)
    {
        # You forgot to turn on 'Create Azure Run As account' 
        $ErrorMessage = "Connection $ConnectionName not found."
        throw $ErrorMessage
    }
    else
    {
        # Something else went wrong
        Write-Error -Message $_.Exception.Message
        throw $_.Exception
    }
}

  

# Getting the database for testing and logging purposes

$MyAzureSqlDatabase = Get-AzSqlDatabase -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName
if (!$MyAzureSqlDatabase)
{
    Write-Error "$($ServerName)\$($DatabaseName) not found in $($ResourceGroupName)"
    return
}
else
{
    Write-Output "Current pricing tier of $($ServerName)\$($DatabaseName): $($MyAzureSqlDatabase.Edition) - $($MyAzureSqlDatabase.CurrentServiceObjectiveName)"
}


# Set Pricing Tier Database

# Check for incompatible actions
if ($MyAzureSqlDatabase.Edition -eq $Edition -And $MyAzureSqlDatabase.CurrentServiceObjectiveName -eq $PricingTier)
{
    Write-Error "Cannot change pricing tier of $($ServerName)\$($DatabaseName) because the new pricing tier is equal to current pricing tier"
    return
}
else
{
    Write-Output "Changing pricing tier to $($Edition) - $($PricingTier)"
    $null = Set-AzSqlDatabase -DatabaseName $DatabaseName -ServerName $ServerName -ResourceGroupName $ResourceGroupName -Edition $Edition -RequestedServiceObjectiveName $PricingTier
}
 




# Show when finished

$Duration = NEW-TIMESPAN –Start $StartDate –End (GET-DATE)
Write-Output "Done in $([int]$Duration.TotalMinutes) minute(s) and $([int]$Duration.Seconds) second(s)"
 

Use the menu to Save your runbook, use the Test pane to review the output of your PowerShell script. When ready Publish your runbook!

STEP 5: Last step is to create a schedule. From your workbook go to Schedules, and Add an schedule.

Create a new schedule based on your requirements/needs.

Click create to finalize the process. Now go back to your SQL database. When the change is happening, you should see a update line like below that shows that the pricing tier is being updated!

Azure SQL update statistics (Manual)

I recently run into a case where I needed to update statistics of an Azure SQL Database because of poor performance and deadlocks. Preventing disruptions is key, so it is important to do something about it. With a simple script we can update the statistics easaly.

Why should I update statistics?

SQL Server statistics are essential for the query optimizer to prepare an optimized and cost-effective execution plan. These statistics provide distribution of column values to the query optimizer, and it helps SQL Server to estimate the number of rows. The query optimizer should be updated regularly. Improper statistics might mislead query optimizer to choose costly operators such as index scan over index seek and it might cause high CPU, memory and IO issues in SQL Server. We might also face blocking, deadlocks that eventually causes trouble to the underlying queries, resources.

The script

Just execute the following query on your database and you should be good to go! Keep in mind, depending on your database this might take a while. During this script your database will get slow, but will remain online.

SET NOCOUNT ON
 GO

 DECLARE updatestats CURSOR FOR
 SELECT table_schema, table_name  
 FROM information_schema.tables
        where TABLE_TYPE = 'BASE TABLE'
 OPEN updatestats

 DECLARE @tableSchema NVARCHAR(128)
 DECLARE @tableName NVARCHAR(128)
 DECLARE @Statement NVARCHAR(300)

 FETCH NEXT FROM updatestats INTO @tableSchema, @tableName

 WHILE (@@FETCH_STATUS = 0)
 BEGIN
    SET @Statement = 'UPDATE STATISTICS '  + '[' + @tableSchema + ']' + '.' + '[' + @tableName + ']' + '  WITH FULLSCAN'
    EXEC sp_executesql @Statement 
    FETCH NEXT FROM updatestats INTO @tableSchema, @tableName
 END

 CLOSE updatestats
 DEALLOCATE updatestats
 GO
 SET NOCOUNT OFF
 GO

Azure SQL configure Azure AD user authentication (Manual)

When moving your applications to the cloud, it makes sense to start using Azure Services to get the best service, highest availability (SLA) and worry free maintenance provided by Azure. The next step is to use Azure AD identities with Azure SQL Database.

Schematic overview of Azure SQL with AAD integration, and optionally synced from on-premise AD.

Within a few steps you will have Azure AD user authentication setup.

Continue reading “Azure SQL configure Azure AD user authentication (Manual)”