Your suggestions - Data Replication

[mendhak]

Here's a little situation which may have several different solutions, but I would like to hear (read) the solutions that you put forward.

We have offices in LocationA, LocationB, LocationC.

The SQL Server at LocationB will contain information about the office at LocationB.

The SQL Server at LocationC will contain information about the office at LocationC.

Now, LocationA, this is a little different. It needs to contain information about LocationA, and in addition, all information about LocationB and LocationC.

Any changes that occur in the database tables at LocationB and LocationC are required to be sent to LocationA as well. If not immediately, then say, within a few seconds or a maximum of say... 1 minute.

The first thing that came to my head was SQL Server Transactional Replication. But from the description I read,

Transactional replication offers a more flexible solution for databases that change on a regular basis. With transactional replication, the replication agent monitors the publisher for changes to the database and transmits those changes to the subscribers. This transmission can take place immediately or on a periodic basis.

It seems to be rather IO intensive.

Any other suggestions to achieve this? Or if you agree with Transactional Replication, then why? Whatever you think, I'd like to read it.

[mendhak]

A little more background info:

The "information" that I keep referring to is the attendance data. Employees use their swipe cards to move around the building, entering it, leaving it, etc. This is what we use for attendance data.

Another idea I had was to somehow access the COM PORT of the swipe card device. Intercept the data received from it, update the local database and at the same time pass it to a web service that would send it to LocationA's servers.

[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]

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.

[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.

[plenderj]

Something that not many people know, but Frame Relay is designed for transmitting large chunks of data, and isn't optimised for small amounts...

[szlamany]

I have to ask - why have 3 servers? Is it for fail over protection?

We have a large school district - 2 high schools - 3 middle schools - 10000 kids. We do class room attendance at the 2 high schools and they all post to a single server at the board-of-education office.

But if you are married to this concept - one of the first thoughts that come to mind is to have a replicated copy of LOC B and LOC C on the LOC A server - so that LOC A has 3 databases - it's own master DB (which I'm kind of guessing is the LOC A purpose) and a mirror image of the LOC B and C database.

Doesn't really seem possible to me that any replication that's available will give you a B update A and C update A process. I would think that would have to be homegrown.

Can things happen like Loc B and Loc C both "create" the same "new" person - and somehow you have to merge those two "new" records into Loc A?

Your requirement to UPDATE EVERY MINUTE or so is a pretty heavy requirement - giving the odd nature of 2 satellite servers having only partial info and a main server needing to be the merge of all three.

[mendhak]

Well, I can be a little more specific. It's for our offices at Tokyo (LocationA), Sendai (LocationB) and Osaka (LocationC).

The reason I'm looking at all of this is because there's a requirement to have the database at LocationA should contain attendance information regarding the other two offices. Each office should have its own attendance info, of course.

[szlamany]

From all the posts I've read here on this thread this seems to say it all...

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.

Since Location A is not read-only, then transactional replication is not an option - right? Seems MERGE is the only option you have available for your needs - short of homegrowing your own.

The first thing that comes to mind with any kind of replication is the length in time of open transactions - hope you've kept them as short as possible.

If MERGE isn't going to work for some reason - since I have no actual experience with it I don't know - then homegrowing a solution is your only other option.

[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.