NAV2016 on Azure SQL Database

vremeni4

Hi,

It would be nice if someone could confirm these findings.
I wanted to deploy a customer's NAV 2016 CU1 database (1.5GB in size, with 10 companies) on the Azure SQL Database. The customer is based in East Asia, so I created an Azure SQL Server in that location. The next bit was to deploy the database on the Azure SQL Server.

1. I tried with the option from SQL Studio Manager, "Deploy Database to Windows Azure".
After some time I got following error message

Unable to reconnect to database: Timeout expired.  The time-out period elapsed prior to completion of the operation or the server is not responding. (Microsoft.Data.Tools.Schema.Sql)

I increased the timeout in SQL Studio Manger and tried again but again the same message.
I created a VM machine in East Asia and I tried from there and the same error message.
So I gave up on this option.

2. Export Data-tier application or BACPAC file, this option seems to be very popular in Microsoft presentations so I thought let's give it a go. I started the process and very quickly I got following error message

Validation of the schema model for data package failed.
Error SQL71561: Error validating element [dbo].[DeleteActiveSession]: Trigger: [dbo].[DeleteActiveSession] has an unresolved reference to object [NAV W1 2016 ].[dbo].[Session Event]. External references are not supported when creating a package from this platform.
Error SQL71561: Error validating element [dbo].[RemoveOnLogoutActiveSession]: Trigger: [dbo].[RemoveOnLogoutActiveSession] has an unresolved reference to object [NAV W1 2016].[dbo].[Session Event]. External references are not supported when creating a package from this platform.
 (Microsoft.SqlServer.Dac)

The database used was upgraded at some time in the past from NAV2009 to NAV2013R2 and then to NAV 2016 CU1.
In NAV 2013 R2 the table Session Event has two triggers defined as
[NAV W1 2016].[dbo].[Session Event] which is not external reference but still BACPAC process did not like it,
so I changed the two triggers by running

ALTER TRIGGER [dbo].[DeleteActiveSession] ON [dbo].[Session Event] ...
ALTER TRIGGER [dbo].[RemoveOnLogoutActiveSession] ON [dbo].[Session Event] ...

This resolved this issue but my luck did not last very long. The next error message was

Error SQL71564: The element User: [NT AUTHORITY\NETWORK SERVICE] has property AuthenticationType set to a value that is not supported in Microsoft Azure SQL Database v12.

After some investigation, I realised that NETWORK SERVICE does not exist on the Azure SQL Server hence the error message. I deleted the entry for NETWORK SERVICE from the database users.

Started BACPAC export again, and I got another error message

Error SQL71564: The element Filegroup: [Data Filegroup 1] is not supported when used as part of a data package (.bacpac file).

It is funny that all databases until NAV 2016 were created with three files
MDF - file that stored structure of the tables,
NDF - file that stores table data. The NDF file is setup by default to go to Filegroup 1.
LDF - the transactional log file.

I think it is a bug that when you create a new NAV 2016 database it will create a NDF file on the Filegroup 1, which means you cannot deploy it with BACPAC export.

To remove a fielgroup is a very painful process, so I created a new database with MDF and LDF files only (no filegroups) and copied the data.

So I tried the BACPAC export again, and I got another error message

Error SQL71564: Element Index: [dbo].[Co_ Cr$Payment Buffer].[$1] has an unsupported property FillFactor set and is not supported when used as part of a data package.

After some investigation I found out that Azure SQL server does not support indexes where Fillfactor, Sort in Temdb or Pad index parameter is set.
The problem is that you cannot remove it either as SQL Server does not allow you to set these parameters to 0, and only 0 is accepted. So if you have an SQL Server where Fillfactor is set you won't be able to deploy this database with BACPAC on Azure.

4. SQL Database Migration Wizard (SQLAzureMW)
After some research I found this program that should help to migrate the database on Azure SQL Server.

http://sqlazuremw.codeplex.com/
https://azure.microsoft.com/en-gb/blog/migration-cookbook-now-available-for-the-latest-azure-sql-database-update-v12/

Indeed the program works very well but it took 16 hours to copy 1.5GB database, which makes it almost useless.

5. So I thought I will do what I saw in Microsoft presentation, as that seems to work
- I restored NAV 2016 Demo database on the SQL Server where Fillfactor is not set
- I used BACPAC to export the Demo database to file.
- Then I imported the BACPAC file on the Azure SQL server (it took 2 hours for demo database to be deployed on Azure SQL Server)
- I started NAV development environment and used the Azure SQL Server name ru01hikdvh.database.windows.net
to connect to it. (use database authentication)
At this stage I got this error message

Microsoft Dynamics NAV
---------------------------
The NAVInvest2016CU1 database on the llxfvdguik.database.windows.net server requires a Per Database license to be opened on SQL Azure.

So I changed (In NAV -> Alter database -> set "Save licence in Database") it in the Demo database and I repeated the whole process. This time I was able to connect to Azure SQL database from NAV development environment.

- I imported a FOB that contained objects from the customers database.
Then I used "Export to Data file" and "Import from Data file" in NAV to import the data and companies in the database.

Again no luck, I was not able to import all 10 companies in one go as the process failed with a time-out error message. After that the database was not usable as I would get following error message

Microsoft Dynamics NAV
---------------------------
The operation could not complete because a record in the Company table was locked by another user. Please retry the activity.

---

So it looks like a locking was activated and there was nothing one can do to resolve it.
The only way to fix this issue was to use restore database at point in time available on Azure SQL server.
First annoying bit was that you are not allowed to overwrite existing database. You can only create a new database.
The restore process did not work all the time; for some strange reason it depends on the time selected for the database to be restored. Sometimes the database gets restored sometime it just freezes and it never gets restored.

- the only way to resolve the issue was to repeat the same process but this time I imported one company after another.

Bottom line, NAV on Azure SQL Database works theoretically but not in a real life situations.
It is a nice concept/prototype and I guess and at some point in the future it will be good.

Until Microsoft is not able to produce a tool that would allow users to copy a backup of a database on the Azure and to use that backup to create the database on Azure, everything else is just goofing around.
Existing tools like BACPAC, deployment to Azure and so on are just Mickey mouse tools which do not really work in real life NAV environment.

Thanks for your confirmation.

mdPartnerNL

wow, thx for this info.

I have been playing a bit with it and for a new customer it looks ok, didn't get a backup of existing customer working too. so in the end, did you use the navdata or sql restore?

vremeni4

I used navdata to restore the database that weas the only way.

Thanks.

mdPartnerNL

I knew it

Tharanga_Chandrasekara

Check this tool : https://sqlazuremw.codeplex.com/
It is not Microsoft fully supported tool, but it works nicely.

vremeni4

Thanks Tharanga_Chandrasekara

If you read my post you will see that I tested that tool too.

The tool works fine but it is very slow, especially if you have many companies (10 companies in this case). Just exporting the database (1.5Gb) took 16 hours.
The tool is scripting everything every object, and after that it uses BCP command to export the data too.
To spend 2-3 days to deploy a simple NAV db on Azure with this tool, is just pointless.

Thanks.

pdj

Sorry to resurrect this old thread, but is there really no usable solution to this issue yet?

I reported the issue below to Microsoft last week (PartnerSource) and still have not heard from them :-(

Unable to deploy a NAV 2016 DB to Azure
NAV2016 CU8 (build 46045)
We are following this guide:
https://msdn.microsoft.com/en-us/library/dn951458(v=nav.90).aspx?f=255&MSPPError=-2147217396

In step 3 we get an error saying Azure SQL does not support multiple file groups, and we have to reduce the number of file groups to 1 before deploying. The database is a standard NAV database with the two default file groups: “PRIMARY” and “Data Filegroup 1”.

We have then created a new NAV database with a single file group, and tried to copy the data to this new database using the Export-NAVData and Import-NAVData powershell cmdlets using this guide:
https://msdn.microsoft.com/en-us/library/dn789595(v=nav.90).aspx?f=255&MSPPError=-2147217396

However, we are unable to import the .navdata file into the new database, unless we only restore a tiny Cronus database. Our database is 50 GB, which we don’t consider to be huge, but we keep getting errors trying to import it. We have tried multiple setups:
1) Directly to Azure SQL: This approach keeps failing due to timeouts and is very slow.
2) Directly to an OnPrem dedicated physical SQL Server with SSDs and heavy duty hardware: After 6 hours it reached 13% and failed due to high disc space usage. It turned out the target database had expanded to more than 350 GB, even though the soource database is only 50 GB.

Please tell us how get a 50 GB NAV database with 2 file groups deployed to Azure SQL with a reasonable time consumption, thank you.

pdj

I now got a reply from Microsoft. In short:

1) Yes, it doesn't work very well.
2) You could try this "hack": https://blogs.msdn.microsoft.com/dfurman/2015/03/25/database-migration-to-sql-azure-using-a-bacpac-a-blocker-and-a-workaround/
3) You could try to ensure all tables have a clustered index identical to the NAV primary key, and try again.
4) You could wait for CU9, which adds a new parameter to the Import-NAVData cmdlet called CommitPerTable.

We have already wasted too much time on the issue, and have now provisioned a new Azure VM with a proper SQL2016 server and not the crippled Azure SQL.

JasminkaT

Just to put those answers back in their context

1) Yes, Azure SQL doesn't support bacpacs with multiple filegroups:
https://azure.microsoft.com/en-us/documentation/articles/sql-database-transact-sql-information/

3) Setting SQLIndex property of the key to something widely different then the NAV key was always a risky business. However you are free to chose so for any reason. This will not cause any import to fail. But it might affect database growth while using import-navdata.

4.) CU 9 (released today) addresses a timeout issue that might occur if using import-navdata on a large company.

pdj

1) Which is used by at least 95% of all NAV SQL databases. It also doesn't support all kinds of properties someone might have sat during optimization or troubleshooting. Some are not even possible to remove again (i.e. FillFactor) You will therefore most likely always need to do an export/import into a new clean database. Just looking at the list with all the unsupported functions doesn't help much. How am I supposed to know what is used by NAV?

3) Again, commonly used during performance optimization / lock reduction. For example in the deprecated Document Dimension table. We still fear our 50gb database grew too big for the 500gb Azure SQL we had, and therefor indirectly was the reason it failed.

4) If the solution is just the new parameter, then it isn't a usable solution if you have more than one large company in the database. It could easily end in a corrupt DB and you would have to start all over. That is not a risk we would have during a go live weekend. (CU9 is not yet published, so I haven't been able to see the documentation)

I still think Azure SQL could be great for NAV, but we just need the proper tools or scripts to prepare our NAV databases for it. They would have to be fast and reliable, and currently what we found were neither. It should be possible to make a script moving all objects from one filegroup to the other, and delete the unused filegroup and data file. It should also be possible to examine a database and list issues and an easy way to solve the typical issues.

robintheswede

You can delete all instanced of TEXTIMAGE_ON [PRIMARY], because storage disk space should be handled by Microsoft (just as backup also are taken care of automatically). I've tried this and it solved my errors

However, I am not 100% sure yet, whether the data will be moved correctly with bacpac utility or not in later steps after this change. I will try to verify this next week.

I recommend everyone to read this chapter about migration to Azure SQL, as some answers and suggestions are provided there: http://geo.edu.al/cloud/wp-content/uploads/2013/12/DB_002.pdf

NicolaiH

I know this is an old thread but I still struggle with this every week. I just wanted to tell that I have tried running the Import-NAVData cmdlet with -CommitPerTable, and it has made no difference, the import still fails after 99 percent completion. I used a 734 MB navdata file for testing. Fails after 43 minutes, with or without CommitPerTable. Only alternative is exporting to Azure SQL (bacpac), but that takes forever, and I would guess is impossible for any larger database. I can confirm that Import-NAVData does indeed work when importing to Azure SQL, but only on very small databases.

The message I get when it's failing is:

Import-NAVData : A connection to SQL server is no longer usable.
This could be caused by one of the following reasons:
* The server has been shut down manually or because of an error.
* The SQL server connection settings are not correct.
* A network failure has occurred.
* A hardware failure has occurred on the server or on your computer.
Try to connect again later or contact your system administrator.
At line:1 char:18
+ ... re-Command {Import-NAVData -DatabaseServer xxxxxx.database.window ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (0:Int32) [Import-NAVData], NavCSideException
    + FullyQualifiedErrorId : agidonsql2.database.windows.net\OestfynTest,Microsoft.Dynamics.Nav.Management.Cmdlets.ImportNavData

kennie

Hi vremeni4,

I have just tried to repro your problems with moving a database with non-PRIMARY filegroups to Azure SQL database using bacpac.

The problem with failed imports due to use of file groups have been solved. Moving a database with this configuration now works like a charm (I used this version of sqlpackage: 13.0.3370.2)

I also touched base with the DacFx dev team, and they confirmed that the import code in sqlpackage replaces references to non-primary filegroups with primary when importing to Azure SQL DB.

Best wishes,
Kennie Nybo Pontoppidan
Program Manager, NAV Server backend team

JasminkaT

Hi Nicolai, we have recently addressed a Azure sql timeout issue when running import-navdata on larger data files. Latest CU + -commitpertable (and setting sql command timeout to high value, default 30 mins) should help with this.

kennie

Hi Robin (robintheswede), Peter (pdj) and vremeni4

The issue with uploading bacpacs from databases using non-primary file groups has been resolved. See this blog post:

https://blogs.msdn.microsoft.com/nav/2017/02/14/moving-a-dynamics-nav-database-with-file-groups-to-azure/

Best wishes,
Kennie Nybo Pontoppidan

kennie

Hi vremeni4

I just checked with the DacFx team on the issue with bacpac export not allowing exporting users that are associated with Windows Auth logins. That's been fixed and should now work. Please make sure that you are using the latest update of DacFx.

Best wishes,
Kennie Nybo Pontoppidan

vremeni4

Hi Kennie,

It is nice to see that Microsoft at last looked at all these issues, which I reported 2 yeas ago. (2015-12-11)

I will give it another try with the latest CU and NAV 2017.
I hope the result won't be as frustrating as my first test.

Thanks for your help.