Migrate an older NAV Database to BC Cloud… in only one step…

What if you have a customer running on NAV2017 or older… it happens right? Nothing to be ashamed of…

And they want to go to the cloud, off course they want. But that is expensive because you need to migrate them to Business Central Spring 2019 first right?

Right?

Nah… maybe not…

To understand where I want to go with this post you need to understand how the cloud migration is being done at Microsoft.

This is done using a mega-generic script hosted by Microsoft in Azure Data Factory. You connect to your on-prem database via the integration runtime.

The only thing important in this, is understanding that the whole migration is done at T-SQL level. It’s one big Transact SQL Script.

Now, we all know that the NAV schema has not been changed that much since 1985. A customer is still a customer and an item is still an item.

What if we “fool” the integration runtime into thinking that it is looking at a Spring 2019 database instead of a NAV2017 database?

All you have to do is run this script and it will think it is a new database…

USE [master]
ALTER DATABASE [NAV2017] SET COMPATIBILITY_LEVEL = 130
ALTER DATABASE [NAV2017] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 10 MINUTES)
GO
USE [NAV2017]
GO
CREATE TABLE [dbo].[$ndo$tenantdatabaseproperty](
[versionno] [int] NULL,
[collation] nvarchar NULL,
[applicationversion] nvarchar NOT NULL,
[percompanyschema] [tinyint] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[$ndo$tenantdatabaseproperty] ADD DEFAULT (N'11.0.19394.0') FOR [applicationversion]
ALTER TABLE [dbo].[$ndo$tenantdatabaseproperty] ADD DEFAULT ((1)) FOR [percompanyschema]
INSERT INTO [dbo].[$ndo$tenantdatabaseproperty]
([versionno]
,[collation]
,[applicationversion]
,[percompanyschema])
VALUES
(1250
,'Latin1_General_100_CI_AS'
,'14.0.46358.0'
,1)
ALTER TABLE [dbo].[Company]
ADD [Display Name] nvarchar ;
update [Company] set [Display Name] = [Name]
CREATE TABLE [dbo].[Intelligent Cloud](
[timestamp] [timestamp] NOT NULL,
[Primary Key] nvarchar NOT NULL,
[Enabled] [tinyint] NOT NULL,
CONSTRAINT [Intelligent Cloud$0] PRIMARY KEY CLUSTERED
(
[Primary Key] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Intelligent Cloud Status](
[timestamp] [timestamp] NOT NULL,
[Table Name] nvarchar NOT NULL,
[Company Name] nvarchar NOT NULL,
[Table Id] [int] NOT NULL,
[Synced Version] [bigint] NOT NULL,
[Blocked] [tinyint] NOT NULL,
CONSTRAINT [Intelligent Cloud Status$0] PRIMARY KEY CLUSTERED
(
[Table Name] ASC,
[Company Name] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Can you go into production with this? Probably not, you need to fill in a lot of blanks and work on customizations with per-tenant extensions.

But it will save you dozens or even hundreds of hours preparing the database on-prem first and only then allowing the customer to see “their” data in cloud.

1 Comment

  1. Thanks for the great post! I will definitely have to test that script for our customer who is running older NAV version.

    Like

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.