Wednesday, March 28, 2012
Replication Suggestions
database on the webbox and an internal database for inter-company use. I
was given the job of replicating this database between external and internal.
I have 3 sql servers; BOX1 is external box that is used to run web frontend
and house the external backend of app, BOX2 is internal (conduit between
internal and external), BOX3 is another internal box that is used for backend
db of the internal frontend (hope this makes sense). BOX2 can only be a pull
subscription from BOX1 and somehow this has to repl to BOX3. This
application is a third party vendor app so I cannot modify the data
structure. I'm new to replication and I'm not sure what the best way to
accomplish this task. I tried transactional but I ran into a snag with 15 of
the 120 tables that do not have unique id's. Any ideas or suggestions
would be great.
Thanks in Advance,
rob
What you are trying to do is called republishing. Set up Box2 to be a
publisher to Box3. Set up Box1 to replicate to Box2. Make sure when you are
creating your articles you select the snapshot option, keep existing table
unchanged.
Regarding your problem with a lack of unique id's - I think you mean a lack
of Primary Keys. Examine the data to see if you can't put primary keys on
these tables - they are updating and deleting rows on these tables according
to some criteria used to uniquely identify them - if you can figure out what
this criteria is - make this your pk.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:C247C730-10DF-4ED1-8C2E-CA26AA593066@.microsoft.com...
>I have an application with a sql backend. My company has an external
> database on the webbox and an internal database for inter-company use. I
> was given the job of replicating this database between external and
> internal.
> I have 3 sql servers; BOX1 is external box that is used to run web
> frontend
> and house the external backend of app, BOX2 is internal (conduit between
> internal and external), BOX3 is another internal box that is used for
> backend
> db of the internal frontend (hope this makes sense). BOX2 can only be a
> pull
> subscription from BOX1 and somehow this has to repl to BOX3. This
> application is a third party vendor app so I cannot modify the data
> structure. I'm new to replication and I'm not sure what the best way to
> accomplish this task. I tried transactional but I ran into a snag with 15
> of
> the 120 tables that do not have unique id's. Any ideas or suggestions
> would be great.
> Thanks in Advance,
> rob
>
|||Like Hillary states, some refer this process as cascading from A to B, from
B to C. Works well as long as the transactions are all going in one
direction. With Regards to unique ids, you are probably refering to PKs.
Follw Hillary's advise.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uPWgfQCDGHA.1816@.TK2MSFTNGP11.phx.gbl...
> What you are trying to do is called republishing. Set up Box2 to be a
> publisher to Box3. Set up Box1 to replicate to Box2. Make sure when you
are
> creating your articles you select the snapshot option, keep existing table
> unchanged.
> Regarding your problem with a lack of unique id's - I think you mean a
lack
> of Primary Keys. Examine the data to see if you can't put primary keys on
> these tables - they are updating and deleting rows on these tables
according
> to some criteria used to uniquely identify them - if you can figure out
what[vbcol=seagreen]
> this criteria is - make this your pk.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:C247C730-10DF-4ED1-8C2E-CA26AA593066@.microsoft.com...
I[vbcol=seagreen]
to[vbcol=seagreen]
15
>
Replication solution?
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:
>
Wednesday, March 7, 2012
Replication Monitor SqlServerCE
If you were using SQLMobile (the CE version for SQL 2005), there is messaging available that will give you progress updates.|||I'm not sure I understand what your comment "messaging" is referring to. Do you have or can point me to some sample code that uses messaging? I am using SQL CE on the mobile device.|||Sorry, by messaging I mean events that you can catch within code and then react to. With SQL CE I don't believe this is happening. You can only query the replication object at the completion of the synchronisation process in order to determine what the outcome was (data sent and received)|||Thanks for your response. I did try asynchronous replication and it is working well so far. I need to implement some additional error trapping but I think I have 95% of what I need. It allows me to get a "percent of completion" during the sync process. I can then display this on the screen to my users. The database is VERY large and was taking forever to sync. I really needed some type of progress bar to show the user. Without that status update, it appeared the unit was locked up. Thanks anyway for your reponse.|||Hi,
I am also trying to get a status update on each article during the web synchronization process. Can you please update me on how to monitor the percent complete progress for the replication.
Thanks in advance.
Apurva
Replication Monitor SqlServerCE
If you were using SQLMobile (the CE version for SQL 2005), there is messaging available that will give you progress updates.|||I'm not sure I understand what your comment "messaging" is referring to. Do you have or can point me to some sample code that uses messaging? I am using SQL CE on the mobile device.|||Sorry, by messaging I mean events that you can catch within code and then react to. With SQL CE I don't believe this is happening. You can only query the replication object at the completion of the synchronisation process in order to determine what the outcome was (data sent and received)|||Thanks for your response. I did try asynchronous replication and it is working well so far. I need to implement some additional error trapping but I think I have 95% of what I need. It allows me to get a "percent of completion" during the sync process. I can then display this on the screen to my users. The database is VERY large and was taking forever to sync. I really needed some type of progress bar to show the user. Without that status update, it appeared the unit was locked up. Thanks anyway for your reponse.|||Hi,
I am also trying to get a status update on each article during the web synchronization process. Can you please update me on how to monitor the percent complete progress for the replication.
Thanks in advance.
Apurva