search

microsoft / DacFx

DacFx, SqlPackage, and other SQL development libraries enable declarative database development and database portability across SQL versions and environments. Share feedback here on dacpacs, bacpacs, and SQL projects.
https://aka.ms/sqlpackage-ref
MIT License
343 stars 20 forks source link

SqlPackage Exports (bacpacs) are not importable by SSMS or Azure import/export #363

Closed bryanhunwardsen closed 1 month ago

bryanhunwardsen commented 11 months ago

,Net versions installed/available on machine: PSChildName Version Release

v2.0.50727 2.0.50727.4927 v3.0 3.0.30729.4926 WCF 3.0.4506.4926 WPF 3.0.6920.4902 v3.5 3.5.30729.4926 Client 4.8.04084 528372 Full 4.8.04084 528372 Client 4.0.0.0

.Net Core versions installed/available on machine: SDK's 7.0.102 [C:\Program Files\dotnet\sdk] 7.0.401 [C:\Program Files\dotnet\sdk] Runtimes Microsoft.AspNetCore.App 3.1.28 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App] Microsoft.AspNetCore.App 3.1.32 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App] Microsoft.AspNetCore.App 5.0.17 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App] Microsoft.AspNetCore.App 6.0.13 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App] Microsoft.AspNetCore.App 6.0.16 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App] Microsoft.AspNetCore.App 6.0.22 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App] Microsoft.AspNetCore.App 7.0.2 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App] Microsoft.AspNetCore.App 7.0.5 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App] Microsoft.AspNetCore.App 7.0.11 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App] Microsoft.NETCore.App 3.1.28 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App] Microsoft.NETCore.App 3.1.32 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App] Microsoft.NETCore.App 5.0.17 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App] Microsoft.NETCore.App 6.0.13 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App] Microsoft.NETCore.App 6.0.16 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App] Microsoft.NETCore.App 6.0.22 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App] Microsoft.NETCore.App 7.0.2 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App] Microsoft.NETCore.App 7.0.5 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App] Microsoft.NETCore.App 7.0.11 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App] Microsoft.WindowsDesktop.App 3.1.32 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App] Microsoft.WindowsDesktop.App 5.0.17 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App] Microsoft.WindowsDesktop.App 6.0.13 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App] Microsoft.WindowsDesktop.App 6.0.16 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App] Microsoft.WindowsDesktop.App 6.0.22 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App] Microsoft.WindowsDesktop.App 7.0.2 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App] Microsoft.WindowsDesktop.App 7.0.5 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App] Microsoft.WindowsDesktop.App 7.0.11 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]

-Source (SqlPackage Export) Windows Server 2016 Datacenter 10.0.14393 Sql Server 14.0.20252.1 Sqlpackage zip/.net tool v162.1.167.1 (tested w/ all prior versions linked above)

-Targets Windows Server 2016 Datacenter 10.0.14393 -Sql Server 14.0.20252.1 --SSMS 18.4, SSMS 19.1 (Using Import BACPAC - local file system) --SqlPackage zip/.net tool v162.1.167.1 (tested w/ all prior versions linked above) (Using Import)

Windows 10 19045.3570 -Sql Server 16.0.1000.6 --SSMS 18.12.1, SSMS 19.1 (Using Import BACPAC) --SqlPackage zip/.net tool v162.1.167.1 (tested w/ all prior versions linked above) (Using Import)

Azure Sql Server 12.0.2000.8

Steps to Reproduce:

Context: We are using Azure DevOps release pipelines leveraging SqlPackage to automate the export of an on-prem database as a bacpac into Azure Blob storage to enable manual (Azure UI) and eventually automated import of the bacpac into Azure Sql Server instances.

  1. SSMS Task: Export Data Tier Application (Windows Server) Export to Azure Blob (Must be done in SSMS 18, this feature is currently broken in SSMS 19.1) Export to Local File System => AZ Copy to Blob

  2. Import from Blob to Azure Sql Server

    • Import Database (from blob) UI (works)
    • az sql db import (from blob) (works) (These base cases would tend to indicate no underlying issues with the source database)

  3. Windows Server => SqlPackage => Export to local file system

  4. Windows Server, Windows 10 => SqlPackage => Import from local file system

    • Targets
    • -Local Sql Server (Works)
      • Azure Sql Server (Works) (These base cases would tend to indicate there is no corruption with the SqlPackage generated bacpac)

  5. Windows Server, Windows 10 => SSMS (any version) => Import Data Tier Application (FAILS w/ Error Message below)

  6. Azure Sql Server

    • Import Database (from blob) UI (FAILS w/ Error Message below)
    • az sql db import (from blob) (FAILS w/ Error Message below)

Error Message from SSMS Import of bacpac that SQLPackage can import but SSMS cannot:

Could not load package from 'C:\MyDatabase.bacpac'. (Microsoft.SqlServer.Dac)

Program Location:

at Microsoft.SqlServer.Dac.BacPackage.Load(String fileName, DacSchemaModelStorageType modelStorageType) at Microsoft.SqlServer.Management.Dac.DacWizard.CreateDatabaseOnTargetWorkItem.DoWork() at Microsoft.SqlServer.Management.TaskForms.SimpleWorkItem.Run()

===================================

File contains corrupted data. (Microsoft.Data.Tools.Schema.Sql)

Program Location:

at Microsoft.Data.Tools.Schema.Sql.Build.SqlPackage.Artifact.InitializefromFile(FileInfo fileInfo, FileMode mode, FileAccess access) at Microsoft.Data.Tools.Schema.Sql.Build.SqlPackage.Open(FileInfo fileInfo, FileAccess access) at Microsoft.Data.Tools.Schema.Sql.Build.SqlPackage.Open(String file, FileAccess access) at Microsoft.SqlServer.Dac.FilePackageSource.Microsoft.SqlServer.Dac.IPackageSource.OpenSqlPackage() at Microsoft.SqlServer.Dac.DacPackage.ContainsExportedData(IPackageSource packageSource) at Microsoft.SqlServer.Dac.BacPackage..ctor(IPackageSource packageSource) at Microsoft.SqlServer.Dac.BacPackage.Load(String fileName, DacSchemaModelStorageType modelStorageType)

===================================

File contains corrupted data. (WindowsBase)

Program Location:

at MS.Internal.IO.Zip.ZipIOLocalFileBlock.Validate(String fileName, ZipIOCentralDirectoryBlock centralDir, ZipIOCentralDirectoryFileHeader centralDirFileHeader) at MS.Internal.IO.Zip.ZipIOLocalFileBlock.ParseRecord(BinaryReader reader, String fileName, Int64 position, ZipIOCentralDirectoryBlock centralDir, ZipIOCentralDirectoryFileHeader centralDirFileHeader) at MS.Internal.IO.Zip.ZipIOLocalFileBlock.SeekableLoad(ZipIOBlockManager blockManager, String fileName) at MS.Internal.IO.Zip.ZipIOBlockManager.LoadLocalFileBlock(String zipFileName) at MS.Internal.IO.Zip.ZipArchive.GetFile(String zipFileName) at MS.Internal.IO.Zip.ZipArchive.GetFiles() at System.IO.Packaging.ZipPackage.ContentTypeHelper..ctor(ZipArchive zipArchive, IgnoredItemHelper ignoredItemHelper) at System.IO.Packaging.ZipPackage..ctor(String path, FileMode mode, FileAccess access, FileShare share, Boolean streaming) at System.IO.Packaging.Package.Open(String path, FileMode packageMode, FileAccess packageAccess, FileShare packageShare, Boolean streaming) at Microsoft.Data.Tools.Schema.Sql.Build.SqlPackage.Artifact.InitializefromFile(FileInfo fileInfo, FileMode mode, FileAccess access)

Error Messages from Azure UI Import or az cli import

The package file provided was not able to be opened. Please run SqlPackage locally to ensure that the file is not corrupt and please ensure that it uploaded to Azure Storage completely.

Based on where the stack trace in the error, a web searches show a possible issue that might be related whereby: ZipIOLocalFileBlock validation reports corrupted file while System.IO.Compression does not

Lack of useful documentation and useful error reporting has not allowed me to conclude if there is a resident issue with the source database at fault, a database/server/source/target version or framework mismatch combability issue, or if the failing use cases noted above are representative of bugs in sqlpackage or ssms/azure cli api.

Did this occur in prior versions? If not - which version(s) did it work in?

This was not a regression (for us) as this is the first attempt at executing the above work flows that are failing. All SqlPackage/SSMS versions used are detailed above

(DacFx/SqlPackage/SSMS/Azure Data Studio)

bryanhunwardsen commented 11 months ago

Tracking down the 140 version of SqlPackage has mitigated the error detailed in the OP. It appears a compat issue between standard and core versions of sqlpackage and their mating compatability to current ssms/azure implementations or bacpac import. It is still unclear if this is an actual bug, or a poorly handled compatibility issue. It is none the less problematic that core versions of this product produce (apparently valid) bacpacs that cannot be imported via ssms or azure api's.

SeenaAugusty commented 11 months ago

Hi @bryanhunwardsen This issue resides in the System.IO.Packaging library and we have opened an issue https://github.com/dotnet/runtime/issues/94899

zijchen commented 11 months ago

There are a few compatibility issues with packages generated from .NET Core and the .NET Framework version of DacFx. The recommendation is to use the same version for both import/export. SSMS uses the .NET Framework so if you would like to use it for import, please use the sqlpackage.exe installed from the MSI.

Worth noting that SSMS doesn't always use the latest version of DacFx so there might be some issues with using an older version for import. Ideally we recommend just using the same sqlpackage for both operations.

bryanhunwardsen commented 11 months ago

There are a few compatibility issues with packages generated from .NET Core and the .NET Framework version of DacFx. The recommendation is to use the same version for both import/export. SSMS uses the .NET Framework so if you would like to use it for import, please use the sqlpackage.exe installed from the MSI.

Worth noting that SSMS doesn't always use the latest version of DacFx so there might be some issues with using an older version for import. Ideally we recommend just using the same sqlpackage for both operations.

@zijchen, We use SqlPackage to automate the export of an on prem db followed by azopy to Azure Storage. We need to automate the import of the bacpac from Azure Storage into an Azure Sql Server. This cannot be done with SqlPackage(to the best of my understanding) as it does not support Azure Blob as a source target for import. Until then, we are forced to rely on the Import implementation exposed via the AZ Cli (Short of re-copying the bacpac to an Azure VM whereby it can be targeted for Import via a version matched SqlPackage installation - This work-around is sufficiently un-desired as to be only a last case scenario). It would be preferable for the bug fix, so that we may avail ourselves of the current development benefits: in our case the Core version is operating 3x faster on export and import than MSI version, if for no other reason.

dzsquared commented 8 months ago

Updating here to acknowledge that this has been added to documentation as a known issue:

image

We are working towards a solution such that bacpacs created with the SqlPackage CLI and other .NET applications can be imported by .NET Framework variants (including the Import/Export capabilities of the Azure portal, Azure PowerShell, and SSMS). Because of the dependency on a fix in .NET, I wanted to be transparent that leveraging a workaround is advised. I'm happy to chat 1:1 with folks on specifics to your environment to find the least ugly solution. (you can reach me on this topic at drskwier@microsoft.com)

llali commented 3 months ago

this is fixed in .NET SDK 8.0. Please install the latest runtime v8.0.7 and install sqlpackage with dotnet tool. dotnet tool install -g Microsoft.SqlPackage if you import database with sqlpackage now, the new package can be exported using sqlpcakge that is installed with the MSI