Replication from SQL to NATIVE ?

humdidump

Hi!

I have one short (I hope :-) ) question. We are going up from a native to a SQL Server with our database this summer. Actually we are working with replicator 4.22 in the native database.

=> When we start working with SQL Server it`s not possible to go back to native (if there where some errors, etc.). Because of this we have one, perhaps crazy idea to be on a safer side the first weeks: When we work with sql we still keep the replicator running and replicate the values to our native database (backup server). This means in simple words: Make replication from sql database dynamics nav to native database dynamics nav. Is it possible? :-)

Thanks for your help!

Best Regards from Germany, bavaria!

Florian

David_Singleton

I can't imagine why you would need to go back, but if you did, then just create a backup and restore to Native. What advantage is there doing it this way?

humdidump

Hi David, Thanks for your reply!

The Problem is that our database is over 300GB and to make a restore this takes too long. So for example we have the worstcase on tuesday afternoon for me it`s not possible to make the restore till the next day :-(

You know what i mean?

Thanks, David!
Regards,
Florian

David_Singleton

So why would you want to go back?

I assume you have completed all testing, and are certain that SQL performs as you need it to, so what's the problem?

humdidump

For 99% you`re right. But what`s goin`on if the worstcase came in? Over 500 people can not work. So for me it`s easier to have rollback-plan ;-) And the replicator i think can be one of them. Or you have several ones?

You understand what i mean?

Thanks 4 u help, David,
regards!

DenSter

I think you are putting your time into the wrong technology. Why not set up a fall back SQL Server and use log shipping, or set up a cluster? No 3rd party external tools involved, it's all part of SQL Server tools.

By the way, when you move to SQL Server, you don't need to take NAV backups anymore. Restoring a 500GB database from a SQL Server backup takes a fraction of what restoring a NAV backup takes, it will be as fast as your hardware can transfer the file. I've seen a 350GB database be restored in less than 20 minutes.

I agree with David that initially you should investigate whether a regular backup strategy will be sufficient for your needs. For instance, you can set up daily full backups, and transaction log backups every 15 minutes. If your production machine would go down it would be very easy to get your database back up with a maximum of 15 minutes data loss, and sometimes you can even save the active log file. Using SQL Server backup files this would not have to take hours.

humdidump

Hi DenSter,

I understand all you write and we have a backup strategy with log shipping, mirroring and saving the transaction protocols. The only problem i have, and i know that it`s less than one percent, is, whats going on when i have problems with the functionallity of the sql server <-> our database?! Perhaps something in our big system is not working correctly (we have made many changes in the code), and so on. And because of this perhaps it`s possible to work on with the replicator, thats my idea :-)

You know what i mean?

Thanks on both :-)

DenSter

I'd say you'll have to make your code work on your SQL Server database. I would not want to have to rely on 2-way replication between two SQL Servers, let alone a SQL Server and a C/SIDE database. Your production database should be the only database involved in active transactions.

By the way, what is this replicator that you are talking about?

David_Singleton

humdidump wrote:

Hi DenSter,

I understand all you write and we have a backup strategy with log shipping, mirroring and saving the transaction protocols. The only problem i have, and i know that it`s less than one percent, is, whats going on when i have problems with the functionallity of the sql server <-> our database?! Perhaps something in our big system is not working correctly (we have made many changes in the code), and so on. And because of this perhaps it`s possible to work on with the replicator, thats my idea :-)

You know what i mean?

Thanks on both :-)

If that is the reason, then personally I do not think you are ready to switch over to SQL. Do more testing, and when you feel confident, then you can switch over.

You know there are a lot of NAV/SQL experts that do these types of conversions for a living. You would be well served, and doing the right thing for your customer to call one of them for help, so that you can be sure.

DenSter

David Singleton wrote:

If that is the reason, then personally I do not think you are ready to switch over to SQL. Do more testing, and when you feel confident, then you can switch over.

Again I agree with David. You should not switch to SQL Server until you have verified that ALL functionality works on that platform.

humdidump

Hi guys,

followed the information about the problem from the solution center:

"Yes you can replicate SQL based Navision with Native. If you are using update-add-delete specs running sequential read=source+dest.you may however run into some errors because sql and native returns records in different order. We have tried to minimize this issue by using a new lookup function implemented in repnt.exe v 4.22.15. (Included in the 4.22.20 version available in the download section."

Regards,
Florian

garak

if you switch from native to sql check before all your customized code and read the topics in the "SQL Perfomance section".

With the replication from SQL to Native you can, for example do it with the following:

SQL Database on the one site (is it a NAV system or a other system?) and a sql database on you site. Between both you enable the replication. With a Client or a NAS on the native db you check via ADO and timer or event if there are new recs in the sql database in your sql system. If yes, you import the recs in your database and start the (if needed) process to handle the data (like create Orders).

This was the solution that is have use by a customer with a WebShop (SQL) and a native database and he needed all the orders from webshop / send Data to webshop.

Regards

humdidump

Thanks Garak for your option to do this.

I`ve get one solution statement from a solutioncenter:

"Yes you can replicate SQL based Navision with Native. If you are using update-add-delete specs running sequential read=source+dest.you may however run into some errors because sql and native returns records in different order. We have tried to minimize this issue by using a new lookup function implemented in repnt.exe v 4.22.15. (Included in the 4.22.20 version available in the download section."

DenSter

Well there you have it, it's some sort of 3rd party product, so you would probably even need it for whatever functional purpose that was purchased. We never did get an answer:

DenSter wrote:

By the way, what is this replicator that you are talking about?

humdidump

DenSter wrote:

By the way, what is this replicator that you are talking about?

[/quote]

The Replicator is a standard data integration solution that offers universal and unique data exchange between two or more Navision databases such as Navision, Navision Financials, Navision Attain, MBS-Navision and Microsoft Dynamics NAV. We are working with it since five years and through different version!

More to read: http://www.databackbone.com/domsite/backbone2.nsf/content/mbok6m9fjj

DenSter

I had never heard of this add-on. It certainly is not part of standard NAV though. Good to see that your NAV partner is helping you out, and if they don't know an answer I would also see if I can get some help from the add-on developer directly, perhaps get someone to explain if they have a version ready for NAV2009.

db

As I know Replicator uses CFront (maybe it has been changed, as I tried it long time ago, but i don't think so). If you migrating yo SQL with all Replicator C/AL stuff, Replicator should work on SQL too. All what you shoud do switch CFront form native to SQL CFront.

But it indeed waste of time, you can fix all functionality bugs on the fly in the worstcase and it is better than cancel migration and get second chance.

David_Singleton

Actually this is the first ever Replicator for Navision (from Kloster Import), it started out as an interface between lotus notes and Navision (DOS version). Very solid products with many years of experience.

I would have no hesitation recommending the product, I just don't think that this is the right approach in this case.

ara3n

You can also use LS Retail Data director, but as mentioned above it may not be a solution to this thread.

humdidump

Hi guys,

followed the information about the problem from the solution center:

"Yes you can replicate SQL based Navision with Native. If you are using update-add-delete specs running sequential read=source+dest.you may however run into some errors because sql and native returns records in different order. We have tried to minimize this issue by using a new lookup function implemented in repnt.exe v 4.22.15. (Included in the 4.22.20 version available in the download section."

Regards,
Florian

SDalietos

Ls Retail Data Director can Replicate from Native to SQL and vise versa. It can also replicate among different versions.

The above setup is located at Distribution Location F99001649.
Ls Retail Toolbox, Data Director, and Fin Plugin must be installed.