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…


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…


Lesson Learned #19: How to obtain the deadlocks of your Azure SQL Database?

In Azure SQL Database we have already running an Extended Event that captures the deadlocks without any additionally action for customer side. In this post we are going to learn how to obtain the deadlocks, how to obtain the deadlock graphic and how to reproduce a deadlock for testing.   How to obtain the deadlocks  …


ARM template – turning on blob auditing

Azure SQL Database Auditing is a popular feature which helps to track events on the databases. Recently Blob Auditing was introduced, at the time of writing this post it is still a preview feature.The advantages of blob auditing over table auditing are higher performance and higher granularity. To turn on blob auditing you can use…


Sample: Joining tables from different Azure SQL Databases

Abstract: The Elastic Database Query feature allows you to perform cross-database queries to access remote tables. It is a great feature if you plan to send straight-forward queries with well-defined Where clauses to the remote database. But as soon as you need to join a remote table with a local table, you are in for…


Lesson Learned #13: SQL Server instance in use does not support column encryption

Today, I worked in a support case that our customer is using the following components: Always Encrypted, Azure Key Vault Microsoft JDBC 6.1. When they connect to local application to their Azure SQL Database, everything works fine. After their code is deployed to Azure App Service they got the error: com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server instance in use does not…


Lesson Learned #11: Connect from Azure SQL DB using an external table where the source of the data is a SQL Datawarehouse

One of our customer tries to connect from Azure SQL DB using an external table where the source of the data is a SQL Datawarehouse database. This first question was if there is supported or not and I received the confirmation from Azure Product Team that there is not supported and they are working on…


Fix identities after migrating through SQL Replica

One of the most common migration procedures to SQL Azure is by configuring replication from your previous environment to your brand new SQL DB: https://msdn.microsoft.com/en-US/library/mt589530.aspx   This is a very nice migration process, as it allows original database to be available in Production until very few moments before SQL Database goes live. However, if your…


Lesson Learned #9: sp_execute_fanout was deprecated and replaced by sp_execute_remote

Within Cross-Database Queries we have the option to run a procedure using parameters using sp_execute_fanout. This execution could be a Transact-SQL statement on a single remote Azure SQL Database or set of databases serving as shards in a horizontal partitioning scheme. From time ago, if you try to execute sp_execute_fanout you will have an error that this…