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

Published Jan 15 2019 02:54 PM 239 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
Occasional Visitor

https://beverachem.com/

We guarantee FULL REFUND on every package that doesn't make it to its destination

NEMBUTAL ONLINE ,
Online Nembutal ,
Nembutal Oral For Sale ,
Nembutal Sodium Online ,
Buy Oxycodone 30 mg online,
Buy Oxycontin 40mg online,
Buy Roxicodone 30mg online,
Buy Valium (Diazepam) 10mg online,
Buy Etizolam 1mg online,
Buy Xanax 2mg online,
Buy Subutex 8mg online,
Buy Suboxone 8mg online,
Buy Ritalin 30mg online,
Buy Ritalin 40mg online,
Buy Adderall 30mg online,
Buy Quaaludes (Mandrax) 300mg onlin,
Buy Tramadol 100mg online,
Buy Hydrocodone 10/325mg online,
Buy Oxynorm 20mg online,
Buy MDMA (Ecstasy) pills online,
Buy Methadone 10mg online,
Buy Concerta 54mg online,
Buy Zopiclone 7,5 mg online,
Buy Reductil 15mg online,
Buy Fentanyl 100mcg patches online,
Buy Modafinil (Provigil) 200mg online,
Buy Pethidine 100mg/2ml online,
Buy Demerol Hcl (Meperidine) 100mg/ml,
Buy Vyvyanse 30mg online,
Buy Dilaudid 8mg online,

Visit their website for more details

%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: