We recently upgraded the Import/Export Service to v3 of the DAC Framework (
). This aligns the Import/Export service with what shipped with SQL Server 2012. Like all upgrades, this has brought both benefits and challenges. While overall we are seeing a significantly reduced amount of failures (both imports and exports), we are seeing some specific scenarios where we are having some trouble. To that end, I wanted to share some increased detail around a specific failure related to to the use of three-part naming, plus an alternative mechanism for doing imports and exports if you have a problem with the service.
The three-part naming problems are arising because Azure SQL Database doesn’t allow the use of external references. Fundamentally, this is because even though Azure SQL databases are grouped underneath a logical server name, there is no physical relationship between them. Unfortunately, the older version of the Import/Export code didn’t fully protect against some of these scenarios, so it wasn’t technically possible to round-trip and BACPAC file through the service in these scenarios. The primary place where we are seeing this crop up and cause trouble is when someone has used a valid three-part reference to the database (
). Technically, this is indeed valid since you are inside
. However, if you were to export some TSQL with that reference it wouldn’t be valid if you tried to import it into
. Therefore, we block this export in v3. In order to successfully complete the export, you will need to modify your TSQL to just reference
Now, that I have covered the challenge, I want to share one of the key improvements. Moving to v3 brings the benefit of being able to fully leverage the command-line interface for the DAC Framework – sqlpackage.exe. As you can see from
, sqlpackage.exe covers the full range of operations associated with moving databases between servers – both on-premises and cloud. Much like the old
(no pun intended, but if you know the reference, you are automatically dating yourself
), it does a full range of things. You can do full imports, full exports, schema imports, schema exports, incremental deployments, and more!
I will try to come back after my Christmas vacation to do a broader post, but I wanted to cover my current favorite today – the ability to do a full import or export from SQL Azure without actually using the Import/Export service. (Hint, hint – I first discovered this capability during the recent Import/Export service issue this past weekend, so you can certainly see that one its primary uses if you use Azure SQL Database is as a contingency backup mechanism).