Lesson Learned #57: Bacpac export process doesn’t include the automatic statistics

Hello Team, Today, I worked on a performance case that our customer claimed about a performance issue with a copied database from other one. The source database was working as they expected but the new database didn’t. Our customer mentioned that the database are identical. Working on this issue I found one item that represents the copied database hasn’t…


Lesson Learned #53: Why do you need to use dm_operation_status dynamic management view?

Hello, This week I worked in a new issue that remembered me the importance to use the dynamic management view called dm_operation_status in every operation that I’m doing with any database either Azure SQL Database or Azure SQL Managed Instance. As most probably you know every database creation, modification, deletion, copy , etc.. is asyncronous process,…


Lesson Learned #50: Adding PK led to transaction log full error

Hello, It is common to have this type of cases where our customer has millions of rows in a table and when they need to add a column and they reached the error transaction log full. Depending on the Azure SQL Database tier you have two important factors to be aware of for having this process completed: Transaction Log Size and…


Extended Events: Azure Storage Account Types Matter

I recently had a client approach me on an error that they persistently encountered when attempting to start an extended event session in the Azure SQL Database service.  The SqlException error message consisted of the following:   Msg 25602, Level 16, State 1, Line 178 The target, “0DCF1903-7977-40A7-B7A8-339AA9D8D7F0.package0.event_file”, encountered a configuration error during initialization. Object…


How to create a native PowerShell app registration to connect to Azure SQL DB

How to create a native app registration This guide assumes that you already own an Azure Subscription, an Azure Active Directory properly setup and your PowerShell environment properly set for running scripts.  You might want to refer to this article first: https://blogs.msdn.microsoft.com/azuresqldbsupport/2018/10/26/azure-sql-database-token-base-authentication-with-powershell/ Problem summary:  There might be a time when we you will want to…


Database Copy may cause Azure SQL Database to run out of transaction log space

Summary: This article describes how a Copy Database operation may cause the source database to run out of transaction log space. The client applications may then encountered the error 9002: “The transaction log for database ‘xxxxx’ is full due to ‘AVAILABILITY_REPLICA'”. This description is derived from an actual support case.   Scenario: Each day between…


Tips & Tricks : What you need to do to create SQL Login to authenticate to Azure SQL DBs members in Auto-Failover Group

Sometimes we found it is tricky to setup SQL Login account other than default built-in SQL Admin and using them to allow SQL application access to login to Azure Server and Databases members in Auto-Failover Group! First I would like to briefly summarize Auto-Failover Group feature introduced to Azure SQL DB as a part of…


Lesson Learned #49: Does Azure SQL Database support Azure Active Directory connections using Service Principals?

The answer is Yes!. Just finishing a service request following the instructions placed on this URL  but with a limitation: Service Principal of the Managed Service Identity is not currently supported.  It is supported if you register an application in Azure portal > Azure Active Directory > Application registration.   Just to mention that there it not…


Lesson Learned #47: Do you have connectivity issues to Azure SQL DB from your PC?

Often, we are working on with service requests that our customers are facing connectivity problem to Azure SQL Database from their office or from home. Time ago, my colleague Raghda and I, recorded this video, that you could watch in our YouTube channel to verify the main issues that may prevent a successful connection to Azure SQL…