April 25, 2005 at 2:07 pm
Here's our scenario...
We have two databases on two separate servers at two facilities. One of the servers (Facility1) is running SQL Server 2000 Standard Edition and the other server (Facility2) is running SQL Server 7.0 Standard Edition. The databases are fairly identical, with the exception of a few customizable fields (thanks to this third party application). The people at Facility1 want to be able to report on Facility1 and Facility2 data side-by-side.
The facilities are connected with a T1, and we've seen reports over the T1 on this data took a long time.
We're looking into ways for Facility1 to be able to report easily without causing too many problems.
One of the methods we thought of is possibly replicating Database1 (at Facility1) and Database2 (at Facility2) into a third database (Database3, at Facility1). We were wondering if there were other options besides replication.
I would've thought of log shipping, but we've got a SQL Server 7.0 in there and neither of those servers are Enterprise Edition (which is where log shipping is supported).
Am I on the right track? Are there other things we can look into besides replication to achieve our goal of combining Database1 and Database2 into a third database?
April 26, 2005 at 6:33 am
Just an additional note:
The tables we'd be interested in combining/replicating are in a third party application. This particular application doesn't use primary keys on their tables.
April 26, 2005 at 9:31 am
Why you are not implementing a custom log shipping is almost trivial.
April 26, 2005 at 9:56 am
Log shipping will not work because the server requires an exlusive lock on the database to apply transaction logs. If you have people in the database generating reports, then the server will not be able to get the lock that it requires and the transaction log applications will fail.
A replication solution will work and would be easy to implement using a third database as you suggest. You will need to create primary keys on the tables that you want to replicate. Create an identity column in each table in both source databases, but use different identity seeds on the tables that you want to merge together in the destination database. Be sure to make the seed difference larger than the max number of rows that you expect to get in each table.
(Another seeding alternative would be to use an odd seed for one table and an even seed on the other and set the identity increment to 2.)
After creating the primary keys on the table that you want to replicate, it will be trivial to complete the replication set up.
April 26, 2005 at 11:45 am
Thanks for the ideas, Robert! I think that might help shed some light on this a bit more for me - gives me some sense of direction. Thanks again!
April 26, 2005 at 2:53 pm
Robert & Sarah,
You might consider a variation used frequently in the MMORPG world. Instead of playing magic with the seeds, add a column called something intuitive like "SourceLocation" that contains either facility1 or facility2, depending on where the row came from. The primary key in database three then becomes the combination of SourceLocation and the existing identity. The positive aspect of this approach is that you can now do reporting for a specific facility a bit more easily, as well as supporting multiple facilities as the organization grows. The cost is a bit more overhead during the replication. The solution based on odd/even seeds results in a challenging situtation if you need to add a third facility in the future. Likewise, if you select an offset range for the identifiers you run into the situation where the "lower" range will eventually collide with the "upper" range.
I am not suggesting that one approach is necessarily better or worse than the other. Simply offering an alternative for consideration.
Have a good day
Wayne
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply