MS Dynamics GP SQL Recplication

rwheeler23
rwheeler23 used Ask the Experts™
on
Does anyone have any updated thoughts on SQL replication for a GP database? I know years ago it was not possible due to the indentity fields used in the GP database tables.  We want actual replication as opposed to just copying the entire database. If replication is not possible how about log shipping? We want to have a second copy of the Dynamics and company database in the event the main server goes down. Maybe now with SQL Server 2005 or 2008 there are features that are available that were not available using SQL Server 2000.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Mirroring is nice option..we use this..on top it we use the mirrored db for reporting.
Commented:
Hi,

The architecture and design of GP still prevents two way replication.  DEX_ROW_IDs, document number assignments, etc. make it impossible to do two-way replication.

However, for your purposes, it sounds like log shipping would work, as you only need a warm backup.

An alternative would be to keep a copy of your latest SQL backups on a separate storage device, and also send your transaction logs to a separate storage device.

If the main server goes down, you can restore the latest backup, then restore the transaction logs.  Effectively the same thing, just manual.

Your tolerance for downtime will determine whether the 'budget' solution would work.  If you can tolerate 30-60 mins of downtime in the rare event that your production server goes down, I would personally stick with the basic restore approach.

But if you absolutely need to be back up and running in less than 15 minutes, then log shipping may be the better option.

One ancillary issue to consider is how your clients will connect to the backup server if it needs to go live.  You don't want to have to change DSNs on a bunch of client machines.

One option is to create the alternate DSN on all of your GP clients in advance, pointing to your backup server.  That way the user can just re-login and choose the second DSN.

Another ancillary issue is managing your GP user accounts.  The SQL logins on your primary server will need to be regularly transferred to your backup server--I don't know if log shipping can do that for you.

Let me know if this helps and if you have any other questions.


Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional


rwheeler23President

Author

Commented:
Both of these options are interesting. They would both shoot holes in this new DCO licensing from Microsoft. Since we would have a secondary copy of the database, I can point as many users as I want at the second copy and not have to pay MS a dime.

Anyway, where can I find instructions on how to setup Data Miirroring and Log Shipping? I am going to throw a few images up and test both of these techniques.

Commented:
Hi,

Very interesting twist on avoiding the DCO licensing!

As long as you don't need to write to GP, and can tolerate some delay in the data getting to your second server, it may be an option.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial