Moving a Dynamics NAV database with file groups to Azure
The short story:
If you use SQL Server for your Dynamics NAV database, and if you are using multiple file groups (other than PRIMARY), as per SQL Server recommendations for better IO performance, and if you need to export the database to Azure, then you need to use the latest update (August 2016 update or later) of the Microsoft SQL Server Data-Tier Application Framework (DacFx) or SQL Server Management Studio (SSMS).
The long story (and why we wrote this blog post)
Following up with our support engineers after our blog post in December, How to export a Dynamics NAV database on SQL Server 2016, a few additional questions for the DacFx team popped up.
For large NAV databases, we recommend the use of SQL Server file groups to distribute IO load over multiple storage volumes. For more information, see the SQL Server documentation at Database Files and Filegroups.) The concept of file groups (other than PRIMARY) does not exist in Azure SQL databases, because the storage layer is abstracted away in the service.
When you export a database to a bacpac file (using sqlpackage.exe or SSMS), information about file groups is stored in the file. Until the August 2016 release of DacFx, sqlpackage.exe or SSMS did not support importing a bacpac file into Azure SQL Database, if the file was exported from a database using file groups.
A hack existed to get around this issue:
Database migration to SQL Azure using a bacpac – a blocker and a workaround
But now, this is not needed anymore: The import code in sqlpackage.exe replaces references to non-primary filegroups with PRIMARY when importing to Azure SQL Database, and all is good.
