Sizing an Elastic Pool eDTU and Storage resources use Graphical runbook

This runbook sizing an elastic pool set Maximum DTUs utilized by all databases in the pool  . And set Maximum storage bytes utilized by all databases in the pool. You can look up the available characteristics and tiers on https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool   This runbook takes the following parameters:   ResouceGroupName: The name of the resource group that the…


Deadlock analysis for SQL Azure Database

If you have ever had a deadlock in Azure SQL Database you know they can be frustrating to get more information about what is causing the issue. Recently working on a case I built a few queries to help analyze the deadlocks you are having and drill down to the database objects that are locked…


Lesson Learned #26: How to change the definition of table triggers at the same time

Today, we have been working a very interesting case, when our customer is migrating their database to Azure SQL Database. We have found an incompatibility issue, because all triggers have an error handler with this syntax: raiserror @errno @errmsg to inform the error happened, but, this syntax is not supported for Azure SQL Database. To fix this…


Connect from Power BI to Azure SQL Database using AAD authentication

Currently Power BI doesn’t support AAD authentication when connecting to Azure SQL Database, but using Microsoft ODBC Driver 13 it is possible to do it. You can download the driver from here (ODBC Driver 13.1) and install it. After installing the driver, you can connect to the Azure SQL using ODBC connector. Select ODBC and…


Database scale error: The database cannot proceed with pricing-tier update as it has memory-optimized objects

You cannot scale SQL Azure databases from Premium to Standard with Memory optimized objects. You will get the error: Database scale operation from Premium P1: 125 DTU, 500 GB to Standard S2: 50 DTU, 250 GB failed for <database name>. Error message: The database cannot proceed with pricing-tier update as it has memory-optimized objects. Please…


Lesson Learned #25: Export/Import Azure SQL Database using Azure File Service?

In some situations, we need to import or export our Azure SQL Database using SQLPackage, but, unfortunately, either source and destination file we cannot specify a blob storage, in case that we want to save the file in this storage. For this reason, we have to use a local temporal folder for uploading/downloading the file depending on our needs. However,…


Exported database from Azure SQL failed to be imported to Azure SQL or to local SQL Server

Symptoms:  When using blob auditing on Azure SQL Server or Database, after you export database to .bacpac file if you import the database to another Azure SQL DB that already have blob auditing enabled at the server level you may get this error message: Could not import package. Error SQL72014: .Net SqlClient Data Provider: Msg…


Quickly test connectivity from Azure Website to SQL DB

In case you have an Azure Website and you need to confirm that your website can reach SQL Database service here is a quick and simple way to test it: 1) navigate to Kudu (it can be accessed at https://{yourwebsitename}.scm.azurewebsites.net ) (Kudu is the engine behind git/hg deployments, WebJobs, and various other features in Azure…