Complex Microsoft Dynamics 365 Business Central upgrades can be turned around faster and meet shorter timelines with the help of powerful features in SQL. For upgrade activities and administrative checks and fixes, the speed benefits of SQL are far too great to ignore.
Here we take you through some useful techniques for expediting a BC upgrade project.
Fenwick recently completed an upgrade of a heavily modified NAV 2009 database into Business Central for a customer in the waste management industry.
It was a complicated project involving multiple companies. Our normal methods were working but were running longer than our small window of time would allow.
I made use of three great (and unknown!) features of SQL to make sure we could complete the upgrade within an acceptable timeframe:
- Revert to a previous state instantly with database snapshots
- Recover and reconcile data between databases with cross-server queries
- Query complex table and column names across many apps, using the system catalogue
Some important caveats…
Although SQL can be a powerful ally, I do not recommend working with BC data through SQL for all purposes. You must be careful and aware when editing data. Also be aware that SQL writes are not suitable for system integration. However, for upgrade activities and administrative checks and fixes, the speed benefits of SQL are too great to ignore!
1. Database Snapshots
A ‘snapshot’ in Microsoft SQL is a read-only copy of a database. It is like a regular database, but comes with these advantages:
- The snapshot can be created from another database in near real time
- It contains all the data of the source database
- You can connect to and read data from the snapshot just like any other database
- You cannot modify the database
- The source database can be reverted to the snapshot state much faster than a traditional backup and restore for a large database
The speed of creating the snapshot and reverting to the original database is extremely useful. I used it while working on data update routines for the BC upgrade project.
We simply took a snapshot, ran BC upgrade routines, and checked the resulting data. We quickly reverted to the original state, adjusted the routine, and retried it.
Bottom line: we saved a lot of time waiting for the database to backup and restore, and we gave ourselves a safety net while we performed critical data migration steps.
The technique is underused because unfortunately there is no support for it in the SQL Management Studio (you cannot see the snapshots in the database list or create a snapshot through the GUI). You must work with the snapshots through SQL commands. To help you apply the common tasks see here are some useful scripts:
An example of reverting to a database snapshot
There is one downside to this feature: if the snapshot exists, then changes to the source SQL database carry a little extra overhead. For this reason, the snapshot should only be kept for a limited period to avoid affecting performance for users.
Database snapshots are not available in Azure SQL unfortunately, but Azure does offer other fast ways of copying a database.
2. Linked Servers and Cross-database Queries
Did you know that in SQL you can connect to databases on another server to read and write data? Not only can you query another server, but you can also join two tables in different databases.
In the context of a Business Central upgrade project, this technique is very useful for cross-checking rows from the original (pre-upgrade) database and recovering data if there are issues. With this technique at your disposal, there is no need to waste time creating custom imports or exports when things go wrong.
First you must configure what is called a ‘Linked Server’. In SQL Management Studio, you can create a Linked Server by right clicking on Server Objects > Linked Server > New Linked Server. Here is a script to create a Linked Server with standard options.
With a Linked Server established, you may freely query tables on other servers by using the object notation: [ServerName].[DatabaseName].dbo.[TableName].
Here are some examples:
An example of joining tables across databases
If you come across a collation mismatch when you run a cross-database query (the database collation may change during the BC upgrade process) then you will receive an error like this:
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_100_CI_AS" in the equal to operation.
The COLLATE keyword can be used to overcome the mismatch and still effectively join data between the servers. Here is an example.
Setting up authentication between linked SQL servers can be subtle. Windows authentication generally works between SQL servers but sometimes domain misconfiguration will cause errors. If there’s a problem with Windows authentication, it’s possible to configure the server link to use a specific SQL login through the SQL Management Studio interface.
Create Linked Servers – Microsoft Documentation
3. Easily Find Table and Column Names
In recent years, the SQL table names which Business Central creates have become a lot longer and unmemorable, making it hard to type out queries manually. You can use SQL Management Studio to browse the table list, but this can be slow work given the number of Business Central tables.
A far better method is to get familiar with reading the metadata tables, as you can easily list the table and column names that you need.
These tricks aren’t hard to pick up. Here are some scripts to try out:
- Search table names – this script is easy to remember and type out fast
- Search table names (detailed) – includes the app name and publisher which has contributed the table
- List table columns – will list columns across all table extensions, including the app name and publisher
- List app names and publishers
Example of querying column metadata across multiple apps and table extensions
sys.tables – Microsoft Documentation
sys.columns – Microsoft Documentation
Conclusion
As Business Central specialists we tend to focus on BC-centred solutions to our problems. But don’t forget that SQL is a mature platform with many great features. By picking up the right techniques in SQL, lengthy tasks become simpler. Finally, if you have a Business Central or SQL-related issue you’d like to find more about, by all means, reach out to me.
