Database Conversion for a Dynamics NAV database stored in Azure


One of the nice new features of Microsoft Dynamics NAV 2016 is the ability to place a Microsoft Dynamics NAV database in Azure SQL Database rather than the traditional method of storing it using an on-premises SQL Server instance. For more information, watch this video.

So, what happens after you move your Dynamics NAV database to Azure SQL Database and the time comes to apply a cumulative update (CU), and the CU involves a database conversion process, which CU6 did (see “Note” in the Overview section of the KB article for CU6)?

You might expect that you could simply open the Dynamics NAV database with the Dynamics NAV development environment and it would convert the database. After all, this is how it has worked for on-premises installations since the dawn of time. However, this process will not work when the Dynamics NAV database resides in Azure SQL Database. If you try to open the database in the with the new version of the development environment, you will get the following warning message:

navblog_AzureSQLDB_conversion

The only way to proceed in this scenario is to copy the database to an on-premises SQL Server, do the database conversion there, and then upload the database into Azure SQL Database again.

You can use the Export Data-tier Application feature in SQL Server Management Studio (SSMS) to download your Azure SQL Database to a *.bacpac file which can then be restored into an on-premise SQL Server instance with the Import Data-tier Application feature of SSMS. Once you have the database in an on-premise instance you can convert it using the Dynamics NAV development environment in the usual way. After that you can re-deploy the Dynamics NAV database to Azure SQL Database again.

If the relevant Dynamics NAV database is very large, you may find it easier to re-configure the database to be multitenant, and then move the application objects into a separate database, which will be quite small and therefore easier to download and convert. The tenant database(s) that contain the business data will not need to be downloaded to an on-premises instance of SQL Server to complete the database conversion process – only the application database.

You will only have to do this when applying a cumulative update that requires a database conversion. You can determine if this will be required by referring to each of the KB articles for all CUs that has been released between the build you are upgrading from and the target cumulative update build. If any of these CUs require a database conversion, and your database is hosted in Azure SQL Database, then you’ll need this workaround.

This process may change in the future, but for now, this is the way you can do a database conversion for a Dynamics NAV database in Azure SQL Database.

Please feel free to provide feedback about this or any other feature of Dynamics NAV via the MS Connect web site.

 

EMEA Microsoft Dynamics NAV Support Team

Comments (6)

  1. Great article, but why is this necessary, what’s the technical reason for this? I know that on Azure SQL you cannot drop the primary key on a table, but if that the case, it would be the same issue with a multi-tenant setup?

    /Erik

  2. It is somewhat strange that this article doesn’t reveal the reason for this behavior.

    A couple of weeks ago I had a discussion with Microsoft about this issue. They told me that the reason is that the development client wants to set the database to single user mode before performing a technical upgrade. Unfortunately this is not supported by SQL Azure. (Ever noticed the behavior with SQL on premise? Sometimes the conversion is not possible because the database is accessed by other users or processes.)

    One could argue that the development environment could at least check the server instance table to see if no server instances are currently running. That would prevent upgrading while users are still able to log in. But that wouldn’t stop others to access the database using the development environment. Nevertheless, that is something that can be controlled and in my opinion should be left to the responsible people. Please don’t care too much, Microsoft! There are many other ways to screw up the system…

    Having said that, I really love multi-tenancy. Even with single tenant environments. And I can recommend to everyone to go with multi-tenancy, especially on Azure.

    1. If that’s the case, it should be pretty easy for MS just to test that there are no service tiers connected instead.

      I wonder if I can patch ndbcs.dll to replace the “SET SINGLE_USER” and “SET MULTI_USER” statements with something to allow the inplace upgrade 🙂

      1. JakeEdwards says:

        I’m still running into issues switching to multi tenancy — tries to run “ALTER DATABASE [DATABASE_DATA] SET SINGLE_USER WITH NO_WAIT” on Azure SQL which doesn’t work.

        Almost need that patch Erik 😉

        1. When executing the ‘Mount-NAVTenant’ Cmdlet in Azure don’t use the ‘OverwriteTenantIdInDatabase’ option, as this can trigger the ‘SET SINGLE_USER’.

  3. Cheikh Faye says:

    Great article which emphasizes the growing role of azure .

Skip to main content