We recently upgraded the Import/Export Service to v3 of the DAC Framework ( http://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 http://blogs.msdn.com/b/ssdt/archive/2012/12/13/windows-azure-import-export-service-and-externa... .
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
http://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
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 http://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.