Azure SQL Database Import/Export Service - Change always brings both challenges and benefits
We recently upgraded the Import/Export Service to v3 of the DAC Framework (https://technet.microsoft.com/en-us/library/ee210546.aspx). 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 ( [myprimarydatabase].[dbo].[name] ). Technically, this is indeed valid since you are inside [myprimarydatabase] . However, if you were to export some TSQL with that reference it wouldn’t be valid if you tried to import it into [mysecondarydatabase] . Therefore, we block this export in v3. In order to successfully complete the export, you will need to modify your TSQL to just reference [dbo].[name] .
One of the Import/Export Program Managers, Adam Mahood, has recently posted a full explanation of this scenario and walks through how to use SQL Server Data Tools to help ferret out the location of these three-part references. You can see his full post at https://blogs.msdn.com/b/ssdt/archive/2012/12/13/windows-azure-import-export-service-and-external-references.aspx.
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 https://msdn.microsoft.com/en-us/library/hh550080(v=VS.103).aspx, sqlpackage.exe covers the full range of operations associated with moving databases between servers – both on-premises and cloud. Much like the old sledge-o-matic (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).
If you don’t already have SQL Server Data Tools installed, you can install them from https://msdn.microsoft.com/en-us/data/hh297027. Once you have the binaries installed, you can use the command-line below to do an export:
"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /a:Export /ssn:yourserver.database.windows.net /sdn:"your database to export" /su:"yourdbuser" /sp:"your password" /tf:"bacpac file to create on local disk"
Here’s a screenshot of what happens with the above command:
In addition, I can import (technically create in this case since I am not doing an incremental deploy) a database in a similar fashion):
"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /a:Import /tdn:"your database to create" /tp:"your password here" /tsn:"yourserver.database.windows.net" /tu:"yourdbuser" /sf:"bacpac on local disk"
Here’s the output:
Voila! A nice easy roundtrip!
As I said before, I will try to come back over the holidays to cover some of the incremental deployments, but in the meantime hopefully this gives you some sense of the power of sqlpackage.exe.
Comments
Anonymous
December 17, 2012
I wish there was a parameter to mention the size while restoring a b/d acpac file on an azure server @nitinsalgarAnonymous
December 20, 2012
Nitin - Can you please explain a bit more why you think such a parameter would help?