Your suggestions - Data Replication

[plenderj]

Some thoughts. How does the data get to the SQL Server? What's connecting to it to insert the data? Could that same process be modified to connect to LocationA's server and update remotely via some sort of xml web service?

Or what if the updates being done in the SQL DBs in LocB & LocC were done via Stored Procedures. Then just modify the stored procedure to *also* update the data in the remote server...

[Merrion]

We use transactional replication here between local country databases and the central DB2 database in the US. IO is not a problem for data keyed by users as no amount of typists can have a significant impact on a T1 line...but it is worth remembering to turn off the replication when doing database update scripts.

[wossname]

but it is worth remembering to turn off the replication when doing database update scripts.

A hard-learned lesson maybe

[mendhak]

We use transactional replication here between local country databases and the central DB2 database in the US. IO is not a problem for data keyed by users as no amount of typists can have a significant impact on a T1 line...but it is worth remembering to turn off the replication when doing database update scripts.

I'd like your comments on the highlighted text here:

transactional - overcomes the limitations of snapshot replication by keeping track of incremental changes to a publication (taking advantage of database transaction logs), propagating them to distributor, which in turn distributes them to subscribers, shortly after they take effect (note that transactional replication starts with a single snapshot, which provides initial synchronization between publisher and subscribers). This ensures that subscribers represent an up-to-date state of publisher (degree of synchronization between the two is configurable). At the same rate, though, this places an additional load on publisher, distributor, and subscribers, and increases complexity of configuration and management. This type of replication is used in scenarios where data on subscribers needs to remain consistent with the original publication. Subscribers are typically treated as read only, although there are (relatively limited) configuration options (immediate updating subscribers and queued updating subscribers) allowing updates applied to subscribers to replicate back to the publisher. In general, though, it is recommended to handle updateable subscribers by configuring merge replication (presented next).


merge - intended for scenarios where content of a publication can be updated at multiple locations. Initial snapshot is used to synchronize remote subscribers with a publisher, but from this point on, the distinction between them becomes less clear. Changes can occur at multiple locations (all subscribers and publisher), which introduces a number of issues that need to be properly addressed, such as mutual synchronization and conflict resolution. In addition, merge replication offers some unique enhancements, such as dynamic filtering (which filters replicated data based on parameters provided dynamically by subscribers). This type of replication is used in situations that involve remote offices or mobile users working with subsets of a publication, which are rolled back into the publisher.

Any comments on Merge Replication? Or a comparison between the two? Which one would you choose in my situation? I've never worked with database replication before, which is why these concepts are entirely theoretical to me at the moment.

[mendhak]

BUMP anyone.

[Edneeis]

Merge replication would be good if LocationA is going to be updating data for all locations. If that is not the case the Merge will cause more overhead. I'd go with the Transaction Replication. It wont really be much more IO then just updating from another source and once you try it I think you'l like it.

[szlamany]

A few questions would help me...

Are location B and location C MSDE implementations on small servers? How big are the database/table entries at these two locations?

How many entries made to loc B and C in a day?

Can location A server see loc B and C server - is this over the internet or a frame?

Is is just one table - attendance - or are there demographics that need to be updated at all 3 locations?

Is this attendance process an INSERT only - new entries as they are swiped - or do they also maintain (UPDATE) existing entries.

[mendhak]

A few questions would help me...

OK.

Are location B and location C MSDE implementations on small servers? How big are the database/table entries at these two locations?

Sql Server 2000s, Enterprise Edition. They are, as I've been told, dedicated, heavy-duty servers.

How many entries made to loc B and C in a day?

Around 1000 entries per day, per location.

Can location A server see loc B and C server - is this over the internet or a frame?

A frame relay over a T-1 Line.

Is is just one table - attendance - or are there demographics that need to be updated at all 3 locations?

In total it may be around 30 tables, there are other things that need to be taken care of. The heaviest usage though, will occur on the attendance table, the rest of the tables will be used just 15% as much as the attendance table will.

Is this attendance process an INSERT only - new entries as they are swiped - or do they also maintain (UPDATE) existing entries.

Insert, Update, Delete, everything.

Inserts will be the most frequent activity, which is why I mentioned just that.

[si_the_geek]

Merge replication would be good if LocationA is going to be updating data for all locations. If that is not the case the Merge will cause more overhead. I'd go with the Transaction Replication. It wont really be much more IO then just updating from another source and once you try it I think you'l like it.

Without looking into it properly (havent even got BOL here ) that sounds best to me, but I haven't ever had a need to replicate. We tend to have most users of a DB at one site, others just put up with a slightly slow (but usable) connection. We do have a form of replication for disaster recovery purposes, but that takes the whole server rather than DB's.

I'm not sure tho how the Publish/Distribute/Subscribe setup of Transactional works, it sounds like szlamany (SQL Server guru ) has done it and doesnt think it will work in this case.

From the sounds of things the network load won't be an issue, so I'd just go with the method that works best for you in terms of the data.

[mendhak]

Thanks to all for your input(s). Points for all!!!


Or to the ones I haven't already rated.

[mendhak]

Some thoughts. How does the data get to the SQL Server? What's connecting to it to insert the data? Could that same process be modified to connect to LocationA's server and update remotely via some sort of xml web service?

It's a swipe card device. In my previous post, I thought of trying to read the data from the COM port and inserting the data locally and then via a web service.

Or what if the updates being done in the SQL DBs in LocB & LocC were done via Stored Procedures. Then just modify the stored procedure to *also* update the data in the remote server...

That's a good idea too. I could used triggers on the table and update LocationA's sql server database... make it a linked server.