Update 2016-09-30: With recent versions of SqlPackage, the workaround for non-PRIMARY filegroups described below is no longer needed. When validating database during export, SqlPackage will no longer complain about non-PRIMARY filegroups. When importing a bacpac with such filegroups into Azure SQL Database, all references to them will be automatically replaced with references to the PRIMARY filegroup.
There may be other scenarios where validation blocks bacpac export, yet using dacpac+data allows a database to be imported. If you encounter any such scenarios, please post a comment on this blog.
One of the ways to migrate an on-premises database to SQL Azure today is by creating a bacpac file, and then importing it into a SQL Azure server. This is described in various documentation, e.g. in the Migration Cookbook for SQL Azure.
I was doing this recently for a customer when I ran into a problem. In the source database, a filegroup other than PRIMARY was present, and all tables were created on that filegroup following a well-known best practice. When creating the bacpac for this database, SqlPackage failed with this error:
*** Error exporting database:One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71564: The element Filegroup: [FG_NB] is not supported in Microsoft Azure SQL Database v12.
Indeed, only the PRIMARY filegroup can exist in SQL Azure databases. In this case, even though it would be perfectly fine to have all tables on the PRIMARY filegroup and not on the original FG_NB filegroup once the database was migrated to SQL Azure, SqlPackage did not allow me to create the bacpac in the first place.
Luckily, there is a workaround that Steven Green from the SSDT team has pointed out. The workaround is as follows:
Instead of creating a bacpac file, create a dacpac+data file. A dacpac+data is very similar to a bacpac, but there are a few minor differences: SQL Azure V12 bacpacs have a higher model schema version, and there is a flag that indicates whether the file is a bacpac. The important difference between them, in this case, is that when SqlPackage creates a bacpac, it validates whether the exported database fits within the feature set of Azure SQL DB, but that check is not done for a dacpac+data. Here is a sample command to create a dacpac+data:
.\SqlPackage /Action:Extract /SourceServerName:SourceSQLServer /SourceDatabaseName:SourceDB
The last two parameters are optional, but may be needed if the database contains any users for Windows authentication logins (Windows authentication is not supported in SQL Azure today), or any other server scoped elements that are not supported in SQL Azure. The SqlPackage.exe executable is found in the DacFx framework directory, e.g. in C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin. The latest DacFx framework should be used.
Create a bacpac, but from a schema-only copy of the database that does not have the custom filegroup. There are several ways to do this. One is to reverse-engineer the source database into an SSDT project, remove all references to the custom filegroup, and publish the project to a new empty database. Now, when I said that this copy of the database has to be schema-only, it wasn’t entirely correct. At least one row of data must be present in any table (it doesn’t matter which table, and it doesn’t matter what this data is). Once this empty and nearly-schema-only database exists, create the bacpac. Here is a sample command:
.\SqlPackage /Action:Export /SourceServerName:SourceSQLServer
- Now that both the dacpac+data and the bacpac files are created, it is time for a bit of surgery. As it happens, both dacpac and bacpac files are actually ZIP archives with a different extension. Changing the extension to .zip lets you open the archives in Windows Explorer (or any archive utility that supports ZIP archives, in which case it may not be necessary to change the extension at all). Open the bacpac archive, and copy the two files named model.xml and Origin.xml to a temporary location. Then, open the dacpac+data file, and replace these two files with the ones from the bacpac. Finally, change the extension of the dacpac file to .bacpac. This produces a bacpac file that can be successfully imported into a SQL Azure server.