Import between Dynamics nav databases

ariuka

Hello all,
I need an advise from our experienced and valuable members, we have a task exchange data between sql servers.
1. From server /MS SQL 2012 standard/to POS database import basic information like customer, item, sales price etc. /no updates on POS/
2. From 4-5 POS databases /MS SQL 2012 express/ to the server database import sales header and line. /no updates on server side/

We are planning to use merge replication for this task. The question is:
- Could MS SQL Replication is proper solution?
- If not, what else would be?

kriki

SQL Replication is quite difficult to implement and to maintain. To make things more difficult for NAV DB's, the datetime field in each table is used by NAV for the versioning-mechanism of records. SQL Replication uses it too. This might mess up things.

There are a lot of other options you might use.

It is best to use the Standard Edition SQL because that one has also SQL Server Agent and you can do all from 1 centralized point in which you can also implement email in case of problems.

Depending on the no. of records in the tables and their rate of changes, if the changes must be moved immediately or can wait some time, it is possible to use the MERGE TSQL command (=INSERT+MODIFY+DELETE in one statement).
If you have a lot of data, it might be more performant to create a big integer field in the table in which to keep the datestamp (https://krikinav.wordpress.com/2016/02/21/timestamp-field-in-sql-and-nav2016/) as an extra field. This way you can trace the version of record so you know it is modified or not. That way you know if you need to copy this record or not.

Using SQL Server Agent jobs you can automate the exchange of data.

kriki

SQL Replication is quite difficult to implement and to maintain. To make things more difficult for NAV DB's, the datetime field in each table is used by NAV for the versioning-mechanism of records. SQL Replication uses it too. This might mess up things.

There are a lot of other options you might use.

It is best to use the Standard Edition SQL because that one has also SQL Server Agent and you can do all from 1 centralized point in which you can also implement email in case of problems.

Depending on the no. of records in the tables and their rate of changes, if the changes must be moved immediately or can wait some time, it is possible to use the MERGE TSQL command (=INSERT+MODIFY+DELETE in one statement).
If you have a lot of data, it might be more performant to create a big integer field in the table in which to keep the datestamp (https://krikinav.wordpress.com/2016/02/21/timestamp-field-in-sql-and-nav2016/) as an extra field. This way you can trace the version of record so you know it is modified or not. That way you know if you need to copy this record or not.

Using SQL Server Agent jobs you can automate the exchange of data.