Replication solution?

We have Epicor Financials as our accounting system which uses SQL Server as
its backend DBMS and a powerbuilder front-end. For the longest time, we've
had the accounting system only in one office (Vancouver) and had our
accountants able to periodically work from home using remote desktop. Now
we've opened a 2nd office located on the other side of the contenient
(Ottawa) and access from Ottawa is really slow. It has been proposed that an
identical SQL Server in the Ottawa would make it faster for the Ottawa
accountants. And a manual coordination of emails and nightly database
restores on either server will keep information synchronized. However, this
could be very impractical and prone to human error. As well, it would be
very doubtful that requests for database restores would be kept only at
night. We have looked at options of SQL Server active-active clustering but
it is unclear how SQL Server can deal with conflict resolutions due to
network failures over a WAN. We suspect that a cheaper option might be
peer-to-peer replication. I'm not sure if it works the same way as log
shipping for redo-ing operations that are queued up before a network failure.
I don't think log shipping would do us any good since we need two active
servers. Any recommendations?
Clustering is a high-availability solution, not performance or distributed
transaction processing since the nodes are generally sitting right next to
each other.
Log Shipping would allow you read-only access to the destination
server...not write. It is a disaster recovery/poor mans reporting server
option.
Replication is your solution for distributed transaction processing...but
you may be limited by the fact that you are using 3rd party software, as in
some cases replication makes (or forces you to make) changes to the schema.
For example, you cannot replicate a table in SQL 2000 Transactional
replication that doesn't have a PK.
I'm not as familiar with PtP replication (new in 2005 I think?), but you are
most likely going to want to look into Merge (good for slow or inconsistent
connectivity situations) or transactional (good for almost always connected
scenarios), with updating subscribers.
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Alvin" <Alvin@.discussions.microsoft.com> wrote in message
news:5BB4190B-A62B-4800-850E-4DDA5C132341@.microsoft.com...
> We have Epicor Financials as our accounting system which uses SQL Server
> as
> its backend DBMS and a powerbuilder front-end. For the longest time,
> we've
> had the accounting system only in one office (Vancouver) and had our
> accountants able to periodically work from home using remote desktop. Now
> we've opened a 2nd office located on the other side of the contenient
> (Ottawa) and access from Ottawa is really slow. It has been proposed that
> an
> identical SQL Server in the Ottawa would make it faster for the Ottawa
> accountants. And a manual coordination of emails and nightly database
> restores on either server will keep information synchronized. However,
> this
> could be very impractical and prone to human error. As well, it would be
> very doubtful that requests for database restores would be kept only at
> night. We have looked at options of SQL Server active-active clustering
> but
> it is unclear how SQL Server can deal with conflict resolutions due to
> network failures over a WAN. We suspect that a cheaper option might be
> peer-to-peer replication. I'm not sure if it works the same way as log
> shipping for redo-ing operations that are queued up before a network
> failure.
> I don't think log shipping would do us any good since we need two active
> servers. Any recommendations?
|||Hi Kevin,
Interesting thought regarding your idea for merge replication but doesn't
that mean that articles are modified/published uni-directionally? I assume
that merge replication is more suited for when you are combining data from of
a hub and spoke deployment rather than everybody modifying everything
(bi-directional).
I'm not sure the procedure in either a clustered scenario or replication
scenario what is the procedure if connectivity between two offices is
disrupted.
"Kevin3NF" wrote:

> Clustering is a high-availability solution, not performance or distributed
> transaction processing since the nodes are generally sitting right next to
> each other.
> Log Shipping would allow you read-only access to the destination
> server...not write. It is a disaster recovery/poor mans reporting server
> option.
> Replication is your solution for distributed transaction processing...but
> you may be limited by the fact that you are using 3rd party software, as in
> some cases replication makes (or forces you to make) changes to the schema.
> For example, you cannot replicate a table in SQL 2000 Transactional
> replication that doesn't have a PK.
> I'm not as familiar with PtP replication (new in 2005 I think?), but you are
> most likely going to want to look into Merge (good for slow or inconsistent
> connectivity situations) or transactional (good for almost always connected
> scenarios), with updating subscribers.
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Alvin" <Alvin@.discussions.microsoft.com> wrote in message
> news:5BB4190B-A62B-4800-850E-4DDA5C132341@.microsoft.com...
>
>
|||I would use bi-directional transactional replication over peer to peer
as it is faster and you don't need the EE edition of SQL Server 2005
for this.
It is more difficult to implement and you have to tear it down and
bring it up again each time you make a schema change.
As Kevin mentions clustering is a HA solution and does not work over
the distance that you have.
If you architect your solution so that each site has a location
identifier on all tables which might encounter conflicts you will not
have a problem with being offline for any appreciable amount of time.
On Jan 8, 5:52 pm, Alvin <Al...@.discussions.microsoft.com> wrote:
> We have Epicor Financials as our accounting system which uses SQL Server as
> its backend DBMS and a powerbuilder front-end. For the longest time, we've
> had the accounting system only in one office (Vancouver) and had our
> accountants able to periodically work from home using remote desktop. Now
> we've opened a 2nd office located on the other side of the contenient
> (Ottawa) and access from Ottawa is really slow. It has been proposed that an
> identical SQL Server in the Ottawa would make it faster for the Ottawa
> accountants. And a manual coordination of emails and nightly database
> restores on either server will keep information synchronized. However, this
> could be very impractical and prone to human error. As well, it would be
> very doubtful that requests for database restores would be kept only at
> night. We have looked at options of SQL Server active-active clustering but
> it is unclear how SQL Server can deal with conflict resolutions due to
> network failures over a WAN. We suspect that a cheaper option might be
> peer-to-peer replication. I'm not sure if it works the same way as log
> shipping for redo-ing operations that are queued up before a network failure.
> I don't think log shipping would do us any good since we need two active
> servers. Any recommendations?
|||Sorry for conflict resolution inquiry, I meant the transactional replication.
"Kevin3NF" wrote:

> Clustering is a high-availability solution, not performance or distributed
> transaction processing since the nodes are generally sitting right next to
> each other.
> Log Shipping would allow you read-only access to the destination
> server...not write. It is a disaster recovery/poor mans reporting server
> option.
> Replication is your solution for distributed transaction processing...but
> you may be limited by the fact that you are using 3rd party software, as in
> some cases replication makes (or forces you to make) changes to the schema.
> For example, you cannot replicate a table in SQL 2000 Transactional
> replication that doesn't have a PK.
> I'm not as familiar with PtP replication (new in 2005 I think?), but you are
> most likely going to want to look into Merge (good for slow or inconsistent
> connectivity situations) or transactional (good for almost always connected
> scenarios), with updating subscribers.
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Alvin" <Alvin@.discussions.microsoft.com> wrote in message
> news:5BB4190B-A62B-4800-850E-4DDA5C132341@.microsoft.com...
>
>
|||The best example I can give for what merge was intended for is a Central
server with a mobile workforce in the field (like sales force) running SQL
Express/MSDE on their laptops. They are usually disconnected and synch on
the next opportunity.
Transactional is better (as Hilary mentions) for a very stable link between
two offices.
MS Clustering does not come into play in the scenario you describe, as the
nodes must be near each other an only one of them can read-write to the
database
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Alvin" <Alvin@.discussions.microsoft.com> wrote in message
news:7C9F5146-9105-4FC0-9688-9384DB1A802F@.microsoft.com...[vbcol=seagreen]
> Hi Kevin,
> Interesting thought regarding your idea for merge replication but doesn't
> that mean that articles are modified/published uni-directionally? I assume
> that merge replication is more suited for when you are combining data from
> of
> a hub and spoke deployment rather than everybody modifying everything
> (bi-directional).
> I'm not sure the procedure in either a clustered scenario or replication
> scenario what is the procedure if connectivity between two offices is
> disrupted.
>
> "Kevin3NF" wrote:
|||Hi Hilary,
Wow I just checked the Microsoft site for the implementation of
bi-directional transactional replication. It imples that you'd need to
create at least 3 stored procedures (insert/update/delete) per table. For
our case, the current accounting system has 12 databases with identical
schemas. Each database has about 1200 tables. So working out the math, if
takes me about 10 mins per stored proc, it would take me about 7 yrs?
(12 databases) * (1200 tables) * (3 sp) * (2 nodes) * (10mins) *
(1hr/60mins) * (1wk/40hrs) * (1yr/52wks)
Unless I missed something, it looks like I better start young.
"Hilary Cotter" wrote:

> I would use bi-directional transactional replication over peer to peer
> as it is faster and you don't need the EE edition of SQL Server 2005
> for this.
> It is more difficult to implement and you have to tear it down and
> bring it up again each time you make a schema change.
> As Kevin mentions clustering is a HA solution and does not work over
> the distance that you have.
> If you architect your solution so that each site has a location
> identifier on all tables which might encounter conflicts you will not
> have a problem with being offline for any appreciable amount of time.
> On Jan 8, 5:52 pm, Alvin <Al...@.discussions.microsoft.com> wrote:
>

Replication of Great Plains Data

Hi All,

We have the requirement to replicate financial data to Aus from the UK, however I dont know if Replication is the best solution around?

Reason why I ask this, is that to create the publication, there are in excess of 10000 articles, which takes forever and a day to create, then when setting up the push subscription, this takes equally as long.

DB's physically range between 100MB and 2GB. Link to Aus is 2MB E1.

The accounts server isnt the most powerful of beasts (HP DL380, 1x1.4Ghx CPU) and with 4 DB's to setup and replicate, it's going to take some time.

With this in mind, I would also be looking to script out the publication should there be any failures and put it into SourceSafe, however this would also take a vast amount of time.

I've thought about using Log Shipping, however I dont know if there are any better ways
?

Thoughts appreciated.

Steve

To determine a solution, you need to describer the rest of your business requirements, such as latency, allowable down time, etc.

|||

Thanks Greg;

Allowable downtime is like most places; none really. Latency, in terms of network, it's not the best, 300ms, but data latency, as long as it's there for the start of the next working day, then there isnt really a problem.

Cheers

|||I suggest reading this High Availability topic in Books Online first. If it doesn't answer any of your questions, then post back here.|||

Good technologies there, trouble is, we are using SQL2000 for our Accounts server.

We dont plan to move to SQL2005 for a while yet, so that rules out DB Mirroring.

Only other option I see are Replication (Which I want to use, but having issues with it) and Log Shipping, but the trouble with this is that the servers are on different, non-trusted domains, which could make that a little tricky?

Thanks for your help.

|||

try using separate a reporting tool over the web. send me an email and i will explain. tony.w@.lycos.co.uk

Replication of Great Plains Data

Hi All,

We have the requirement to replicate financial data to Aus from the UK, however I dont know if Replication is the best solution around?

Reason why I ask this, is that to create the publication, there are in excess of 10000 articles, which takes forever and a day to create, then when setting up the push subscription, this takes equally as long.

DB's physically range between 100MB and 2GB. Link to Aus is 2MB E1.

The accounts server isnt the most powerful of beasts (HP DL380, 1x1.4Ghx CPU) and with 4 DB's to setup and replicate, it's going to take some time.

With this in mind, I would also be looking to script out the publication should there be any failures and put it into SourceSafe, however this would also take a vast amount of time.

I've thought about using Log Shipping, however I dont know if there are any better ways
?

Thoughts appreciated.

Steve

To determine a solution, you need to describer the rest of your business requirements, such as latency, allowable down time, etc.

|||

Thanks Greg;

Allowable downtime is like most places; none really. Latency, in terms of network, it's not the best, 300ms, but data latency, as long as it's there for the start of the next working day, then there isnt really a problem.

Cheers

|||I suggest reading this High Availability topic in Books Online first. If it doesn't answer any of your questions, then post back here.|||

Good technologies there, trouble is, we are using SQL2000 for our Accounts server.

We dont plan to move to SQL2005 for a while yet, so that rules out DB Mirroring.

Only other option I see are Replication (Which I want to use, but having issues with it) and Log Shipping, but the trouble with this is that the servers are on different, non-trusted domains, which could make that a little tricky?

Thanks for your help.

|||

try using separate a reporting tool over the web. send me an email and i will explain. tony.w@.lycos.co.uk