Alternatives to replication

(0) Share

Report

Posted on by Community Member

We currently use replication to get data from our GP SQL server to another SQL server so we can use it for our CRM program. The problem with this is that replication alters the table structure so we can no longer run a program like CDA so we can archive our companies.

We looked at log shipping, but my DBA is telling me there is a delay between the time data is written to the GP server and the time the logs are applied to our CRM SQL server and that delay is too long for our needs.

Are there any other options that we should look at?

What's the smallest time delay we can have with log shipping?

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

Suggested answer

John Lowther 5,122 on at

Like (1)

Report
Hey Carl,

Other options you could check into include but not limited to, are Mirroring or Snapshots.

However, In My Opinion (IMO) I prefer Log Shipping to these.

Here are the reasons.

1) Odds are you are already making a transaction log backup at some interval, thus there is no more overhead on the production SQL Server, whereas replication would add some overhead, maybe not much, but sometimes every little bit counts.

2) The action of restoring your transaction log backups to another SQL Server validates that you good transaction log backups.

3) If your transaction log backup files are stored on a SAN and not somewhere on the Production SQL Server then the only overhead in on the Backup SQL Server when you restore the transaction log backups, no additional overhead on the Production Server. If the transaction log backup files are somewhere on the Production SQL Server (which I hope they are not for other reasons). Then the only overhead will be the network bandwidth when either; copying the transaction log backup files to another location, or the network bandwidth when restoring them on your Backup SQL Server directly from the Production Server’s local Backup location.

4) In the catastrophic case where your Production SQL Server completely dies, you can:

Re-restore the last transaction log backup using the (RESTORE WITH RECOVERY) option

Point the clients to you Backup SQL Server

Login to Dynamics GP and change their passwords or see this post. In addition, you can check out this post.

You are now back up and running in almost no time.

Some important notes about switching your Backup SQL Server to Production mode, (1) while it is still a Backup SQL Server, long before anything happens to Production go ahead and transfer the SQL logins to the Backup SQL Server. There are lots of post on how to that, so let us skip listing them here. (2) If you have the SSRS Reports, Excel Refreshable Reports installed you will need to redeploy them, but depending on your SQL Server that does not take incredibility long to do.

That only leaves us with the time delay issue. Most of which depends on your hardware, and how often you make transaction log backups, the shortest time that I know for a SQL job schedule is one (1) second. Therefore, if your hardware can support it, you could in theory, make a transaction log backup every second and restore it every second and your backup SQL Server would be only a second or two behind.

However, the bigger question is; Do the users, or in your case the CRM program, really need to be that close in time? Would anyone really notice if the two SQL Servers were 5 minutes apart? On the other hand, even if the two SQL Servers were 15 minutes apart, would anyone notice? In most cases, not all, and you may be the exception, making a transaction log backup and restoring it somewhere else every 15 minutes is unnoticeable by the common user.

Of course, all of this is totally IMO, and my name is most not “The Almighty One”, and always remember, what works in one case does not work for all cases. Probably why there are multiple options out there. ;)

Hope some of this helps,

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Here are some statements from my DBA, what do you think?

-----

I saw in MS documentation somewhere that you could go as low as 30 seconds between log shipments. However, when the target system is reading the updates it is unavailable for any other queries, and when it has any other connections it will not update until all connections are dropped. (all connections to the database being updated)

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report

Hi,

Have you talked to Professional Advantage about this? For the benefit of forum readers, Prof Advantage is the ISV for Company Data Archive (CDA).

In what way does your replication alter the table structure?

Kind regards,

Leslie

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report
Hi Again,

You may already have this KB, but just in case, is this the replication procedures you are following?

Description of the requirements to run replication, clustering, log shipping, and database mirroring together with Microsoft Dynamics GP

On This Page

INTRODUCTION

More information

Replication

Clustering

Log shipping and database mirroring

Properties

Give Feedback

INTRODUCTION

This article describes the requirements to run replication, clustering, log shipping, and database mirroring together with Microsoft Dynamics GP.

Back to the top | Give Feedback

More information

Replication

The snapshot and the transactional replication are the only two types of replication that can be implemented together with Microsoft Dynamics GP.

Microsoft Dynamics GP requires one-way replication. Two-way replication cannot be implemented together with Microsoft Dynamics GP because the Setup tables retain the next Document Number value when you create accounting transactions.

Replication can be configured together with Microsoft Dynamics GP databases if you follow the following rules:

When you select a table for replication, disable the option to add a unique identifier to the table. Otherwise, the table structure is modified. The modification of the table structure causes problems in Microsoft Dynamics GP.

Note Most Microsoft Dynamics GP tables have a DEX_ROW_ID column for the unique identifier. This column provides a unique identifier that can be used together with replication.

Before you upgrade Microsoft Dynamics GP, remove replication from the instance of Microsoft SQL Server. Otherwise, problems will occur during the upgrade process.

When choosing the tables to replicate, please keep in mind replicating transaction tables may slow down certain processes in Microsoft Dynamics GP. Once replication is configured, test out all processes in Microsoft Dynamics GP to ensure you are comfortable with performance.

For more information about the configuration of replication, contact the Microsoft SQL Server Support Team at 800-936-5800.

Clustering

You can run Microsoft Dynamics GP in a single-instance clustering environment and in a multiple-instance clustering environment if there is a one-to-one relationship in an instance of SQL Server.

For more information about the configuration for a clustering environment, contact the Microsoft SQL Server Support Team at 800-936-5800.

Log shipping and database mirroring

You can run Microsoft Dynamics GP in an environment in which log shipping and database mirroring are enabled.

For more information about the configuration for log shipping or for database mirroring, contact the Microsoft SQL Server Support Team at 800-936-5800.

Before you upgrade Microsoft Dynamics GP, remove database mirroring from the Microsoft Dynamics GP databases. Otherwise, problems will occur during the upgrade process.

Note If you are using Replication, Log Shipping, or Database Mirroring and users have to access Microsoft Dynamics GP on the secondary server, the passwords for the users will no longer be valid. To reset the password, follow these steps:

Log on to Microsoft Dynamics GP as the sa user.

Follow the appropriate step:

In Microsoft Dynamics GP 10.0, point to Tools on the Microsoft Dynamics GP menu, point to Setup, point to System, and then click User.

In Microsoft Dynamics GP 9.0, point to Setup on the Tools menu, point to System, and then click User.

Click the lookup button next to User ID, and then select the appropriate user.

In the Password box, enter a new password, and then click Save.

Back to the top | Give Feedback

Properties

Article ID: 926490 - Last Review: February 28, 2013 - Revision: 5.0

Applies to

Microsoft Dynamics GP 2013

Microsoft Dynamics GP 2010

Microsoft Dynamics GP 10.0

System Manager-+'-[*

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I have talked to profad a few years ago and they said they didn't support replication (or something along that line)

However, my DBA thinks that one of the things you mentioned (about disabling the option to add a unique identifier to the table) will take care of our problem. I'll let you know

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report

Hi Carl,

Did you get your replication working??

Leslie

Was this reply helpful? Yes No
Suggested answer

John Lowther 5,122 on at

Like (0)

Report

Hey Carl,

Sorry it has taken me so long to reply, but in your reply.

------

I saw in MS documentation somewhere that you could go as low as 30 seconds between log shipments. However, when the target system is reading the updates it is unavailable for any other queries, and when it has any other connections it will not update until all connections are dropped. (all connections to the database being updated)

------

On Log shipping every 30 seconds, I have never really tried that mainly because I have never found it necessary.

I have to admit I am curious as to why it has to be this often. Could you please expand on this?

Additionally, you are correct in that while the target is reading the updates (restoring) it is unavailable for other queries and will not update with connections to the target database.

In my link on the previous post to "Log Shipping”, there was another link to how to get the data from the target database to a warehouse database. In my case, I was combining four companies, but that does not always have to be the case. The direct link to that post is community.dynamics.com/.../creating-an-enterprise-data-warehouse-edw-for-consolidated-business-intelligence-cbi-step-5.aspx

To sum everything up I never actually allow anyone to touch the target (restored) databases. What I do is as soon as a Restore finishes I copy (usually using SQL Code, but not always) whatever tables I need into a warehouse database. I can then just expose the copied tables or create warehouse tables, that I can expose, which are sometimes better for BI purposes.

Hope some of this helps,

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Leslie,

We tried over the weekend and my DBA says he was not able to find the option you mentioned in the replication settings. Is this an enterprise-only feature?

Was this reply helpful? Yes No