Azure SQL Database Import/Export Service - Change always brings both challenges and benefits

Published Jan 15 2019 02:54 PM 362 Views
Microsoft
First published on MSDN on Dec 14, 2012

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 ), 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 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.

1 Comment
%3CLINGO-SUB%20id%3D%22lingo-sub-317470%22%20slang%3D%22en-US%22%3EAzure%20SQL%20Database%20Import%2FExport%20Service%20-%20Change%20always%20brings%20both%20challenges%20and%20benefits%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-317470%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Dec%2014%2C%202012%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EWe%20recently%20upgraded%20the%20Import%2FExport%20Service%20to%20v3%20of%20the%20DAC%20Framework%20(%20%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fee210546.aspx%22%20title%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fee210546.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttp%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fee210546.aspx%20%3C%2FA%3E).%20This%20aligns%20the%20Import%2FExport%20service%20with%20what%20shipped%20with%20SQL%20Server%202012.%20Like%20all%20upgrades%2C%20this%20has%20brought%20both%20benefits%20and%20challenges.%20While%20overall%20we%20are%20seeing%20a%20significantly%20reduced%20amount%20of%20failures%20(both%20imports%20and%20exports)%2C%20we%20are%20seeing%20some%20specific%20scenarios%20where%20we%20are%20having%20some%20trouble.%20To%20that%20end%2C%20I%20wanted%20to%20share%20some%20increased%20detail%20around%20a%20specific%20failure%20related%20to%20to%20the%20use%20of%20three-part%20naming%2C%20plus%20an%20alternative%20mechanism%20for%20doing%20imports%20and%20exports%20if%20you%20have%20a%20problem%20with%20the%20service.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThe%20three-part%20naming%20problems%20are%20arising%20because%20Azure%20SQL%20Database%20doesn%E2%80%99t%20allow%20the%20use%20of%20external%20references.%20Fundamentally%2C%20this%20is%20because%20even%20though%20Azure%20SQL%20databases%20are%20grouped%20underneath%20a%20logical%20server%20name%2C%20there%20is%20no%20physical%20relationship%20between%20them.%20Unfortunately%2C%20the%20older%20version%20of%20the%20Import%2FExport%20code%20didn%E2%80%99t%20fully%20protect%20against%20some%20of%20these%20scenarios%2C%20so%20it%20wasn%E2%80%99t%20technically%20possible%20to%20round-trip%20and%20BACPAC%20file%20through%20the%20service%20in%20these%20scenarios.%20The%20primary%20place%20where%20we%20are%20seeing%20this%20crop%20up%20and%20cause%20trouble%20is%20when%20someone%20has%20used%20a%20valid%20three-part%20reference%20to%20the%20database%20(%20%3CEM%3E%5Bmyprimarydatabase%5D.%5Bdbo%5D.%5Bname%5D%20%3C%2FEM%3E).%20Technically%2C%20this%20is%20indeed%20valid%20since%20you%20are%20inside%20%3CEM%3E%5Bmyprimarydatabase%5D%20%3C%2FEM%3E.%20However%2C%20if%20you%20were%20to%20export%20some%20TSQL%20with%20that%20reference%20it%20wouldn%E2%80%99t%20be%20valid%20if%20you%20tried%20to%20import%20it%20into%20%3CEM%3E%5Bmysecondarydatabase%5D%20%3C%2FEM%3E.%20Therefore%2C%20we%20block%20this%20export%20in%20v3.%20In%20order%20to%20successfully%20complete%20the%20export%2C%20you%20will%20need%20to%20modify%20your%20TSQL%20to%20just%20reference%20%3CEM%3E%5Bdbo%5D.%5Bname%5D%20%3C%2FEM%3E.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EOne%20of%20the%20Import%2FExport%20Program%20Managers%2C%20Adam%20Mahood%2C%20has%20recently%20posted%20a%20full%20explanation%20of%20this%20scenario%20and%20walks%20through%20how%20to%20use%20SQL%20Server%20Data%20Tools%20to%20help%20ferret%20out%20the%20location%20of%20these%20three-part%20references.%20You%20can%20see%20his%20full%20post%20at%20%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fb%2Fssdt%2Farchive%2F2012%2F12%2F13%2Fwindows-azure-import-export-service-and-external-references.aspx%22%20title%3D%22http%3A%2F%2Fblogs.msdn.com%2Fb%2Fssdt%2Farchive%2F2012%2F12%2F13%2Fwindows-azure-import-export-service-and-external-references.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttp%3A%2F%2Fblogs.msdn.com%2Fb%2Fssdt%2Farchive%2F2012%2F12%2F13%2Fwindows-azure-import-export-service-and-external-references.aspx%20%3C%2FA%3E.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ENow%2C%20that%20I%20have%20covered%20the%20challenge%2C%20I%20want%20to%20share%20one%20of%20the%20key%20improvements.%20Moving%20to%20v3%20brings%20the%20benefit%20of%20being%20able%20to%20fully%20leverage%20the%20command-line%20interface%20for%20the%20DAC%20Framework%20%E2%80%93%20sqlpackage.exe.%20As%20you%20can%20see%20from%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fhh550080(v%3DVS.103).aspx%22%20title%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fhh550080(v%3DVS.103).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttp%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fhh550080(v%3DVS.103).aspx%20%3C%2FA%3E%2C%20sqlpackage.exe%20covers%20the%20full%20range%20of%20operations%20associated%20with%20moving%20databases%20between%20servers%20%E2%80%93%20both%20on-premises%20and%20cloud.%20Much%20like%20the%20old%20%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DgasAFyonmmI%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Esledge-o-matic%20%3C%2FA%3E(no%20pun%20intended%2C%20but%20if%20you%20know%20the%20reference%2C%20you%20are%20automatically%20dating%20yourself%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F67442i8F836A37CE3720EC%22%20%2F%3E)%2C%20it%20does%20a%20full%20range%20of%20things.%20You%20can%20do%20full%20imports%2C%20full%20exports%2C%20schema%20imports%2C%20schema%20exports%2C%20incremental%20deployments%2C%20and%20more!%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EI%20will%20try%20to%20come%20back%20after%20my%20Christmas%20vacation%20to%20do%20a%20broader%20post%2C%20but%20I%20wanted%20to%20cover%20my%20current%20favorite%20today%20%E2%80%93%20the%20ability%20to%20do%20a%20full%20import%20or%20export%20from%20SQL%20Azure%20without%20actually%20using%20the%20Import%2FExport%20service.%20(Hint%2C%20hint%20%E2%80%93%20I%20first%20discovered%20this%20capability%20during%20the%20recent%20Import%2FExport%20service%20issue%20this%20past%20weekend%2C%20so%20you%20can%20certainly%20see%20that%20one%20its%20primary%20uses%20if%20you%20use%20Azure%20SQL%20Database%20is%20as%20a%20contingency%20backup%20mechanism).%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIf%20you%20don%E2%80%99t%20already%20have%20SQL%20Server%20Data%20Tools%20installed%2C%20you%20can%20install%20them%20from%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Fdata%2Fhh297027%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttp%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Fdata%2Fhh297027%20%3C%2FA%3E.%20Once%20you%20have%20the%20binaries%20installed%2C%20you%20can%20use%20the%20command-line%20below%20to%20do%20an%20export%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CEM%3E%20%22C%3A%5CProgram%20Files%20(x86)%5CMicrosoft%20SQL%20Server%5C110%5CDAC%5Cbin%5Csqlpackage.exe%22%20%2Fa%3AExport%20%2Fssn%3Ayourserver.database.windows.net%20%2Fsdn%3A%22your%20database%20to%20export%22%20%2Fsu%3A%22yourdbuser%22%20%2Fsp%3A%22your%20password%22%20%2Ftf%3A%22bacpac%20file%20to%20create%20on%20local%20disk%22%20%3C%2FEM%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EHere%E2%80%99s%20a%20screenshot%20of%20what%20happens%20with%20the%20above%20command%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F67445iDE8767DB05AF78F0%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIn%20addition%2C%20I%20can%20import%20(technically%20create%20in%20this%20case%20since%20I%20am%20not%20doing%20an%20incremental%20deploy)%20a%20database%20in%20a%20similar%20fashion)%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CEM%3E%20%22C%3A%5CProgram%20Files%20(x86)%5CMicrosoft%20SQL%20Server%5C110%5CDAC%5Cbin%5Csqlpackage.exe%22%20%2Fa%3AImport%20%2Ftdn%3A%22your%20database%20to%20create%22%20%2Ftp%3A%22your%20password%20here%22%20%2Ftsn%3A%22yourserver.database.windows.net%22%20%2Ftu%3A%22yourdbuser%22%20%2Fsf%3A%22bacpac%20on%20local%20disk%22%20%3C%2FEM%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EHere%E2%80%99s%20the%20output%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F67447i2A0FFD896B11D897%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EVoila!%20A%20nice%20easy%20roundtrip!%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAs%20I%20said%20before%2C%20I%20will%20try%20to%20come%20back%20over%20the%20holidays%20to%20cover%20some%20of%20the%20incremental%20deployments%2C%20but%20in%20the%20meantime%20hopefully%20this%20gives%20you%20some%20sense%20of%20the%20power%20of%20sqlpackage.exe.%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-317470%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Dec%2014%2C%202012%20We%20recently%20upgraded%20the%20Import%2FExport%20Service%20to%20v3%20of%20the%20DAC%20Framework%20(http%3A%2F%2Ftechnet.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-317470%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 15 2019 02:54 PM
Updated by: