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:
>