November 22, 2002 at 10:03 am
We have a situation that lends itself to bi-directional replication. Is it possible to have two publishers for one database. Each publisher publishes a subset of the data and subscribes to the data published by the other. The intersection of the two publications is a null set and the union of each publication equals the whole of the database. If I haven't been to obscure, please advise.
Thanks in advance,
Cliff
November 22, 2002 at 10:32 am
Can you elaborate on this a bit, describe the database data to me. I think you may be wanting to use updating subscribers instead. But if you are publishing different data to different tables then you should be able to do as you descibe.
November 22, 2002 at 10:56 am
quote:
Can you elaborate on this a bit, describe the database data to me. I think you may be wanting to use updating subscribers instead. But if you are publishing different data to different tables then you should be able to do as you descibe.
For example, I have a database named EXAMPLE. This database has 3 tables, TABLE1, TABLE2 and TABLE3. I am replicating between two servers, SERVER1 and SERVER2.
Can SERVER1 publish EXAMPLE.TABLE1 and EXAMPLE.TABLE2, SERVER2 publish EXAMPLE.TABLE3. Then SERVER1 subscribes to EXAMPLE.TABLE3 and SERVER2 subscribes to EXAMPLE.TABLE1 and EXAMPLE.TABLE2.
Is this doable?
Regards,
Cliff
November 22, 2002 at 1:30 pm
I Think it is doable,have you tested it?
November 22, 2002 at 1:57 pm
quote:
I Think it is doable,have you tested it?
Not yet. Thought I'd consult the gurus first. I'll post my results when I've succeeded (or failed). Would still like to hear from anyone else that may have attempted it.
Regards,
Cliff
November 22, 2002 at 2:20 pm
As long as it issn't the same tables I see no issues.
November 22, 2002 at 2:21 pm
quote:
I Think it is doable,have you tested it?
Not yet. Thought I'd consult the gurus first. I'll post my results when I've succeeded (or failed). Would still like to hear from anyone else that may have attempted it.
Regards,
Cliff
November 25, 2002 at 7:58 am
Bi-directional replication can be done on the same table but requires planning and very good controls on server specific code. A couple of methods are as follows:
1) A function/table for managing the assignment of primary key IDs. Depending upon how many servers you think you will have in the future you could either do even/odd assignment per server, negative/positive per server or ID ranges per server. The downside of this approach is that the value would have to be assigned by the client application. I have done this approach in Oracle where you can assign the starting sequence number. In that case the system will handle it if you do ranges or even/odds.
2) A Server ID column which would be part of the primary key. In this scenario Server One would always have a value of 1 and Server Two would always have a value of 2 and so on. You also could make it a character based "data source" code if you want a more descriptive value. This approach is probably the easiest to implement, the easiest to manage and the most extensible. An example of such an approach would be as follows:
CREATE TABLE t_test (
row_id NUMERIC(18) IDENTITY,
data_source_cd CHAR(2) DEFAULT 'S1' NOT NULL,
row_desc VARCHAR(20) NOT NULL,
CONSTRAINT XPK_t_test PRIMARY KEY (row_id, data_source_cd))
The above CREATE TABLE statement would be used on Server 1. An identical statement but with the DEFAULT data_source_cd set to 'S2' would be run on Server 2.
When you go to insert a row into this table you would not specify the data_source_cd value in the statement but let the table schema default handle the assignment as follows:
INSERT INTO t_test (row_desc) VALUES ('blah blah blah')
As I mentioned, any of these scenarios require good planning and knowledge by everyone working on the systems so that they don't cause problems in the future.
November 25, 2002 at 8:07 am
[font=Arial]]
Sorry, I should have elaborated further. I personally would use approach 2 as I like to be able to look at the data and know where it came from. With Merge replication you don't necessarily need to take this approach. Here is a snippet of BOL that speaks to this:
UNIQUEIDENTIFIER Column
Microsoft® SQL Server™ 2000 identifies a unique column for each row in the table being replicated. This allows the row to be identified uniquely across multiple copies of the table. If the table already contains a column with the ROWGUIDCOL property that has a unique index or primary key constraint, SQL Server will use that column automatically as the row identifier for the publishing table.
Otherwise, SQL Server adds a uniqueidentifier column, titled rowguid, which has the ROWGUIDCOL property and an index, to the publishing table. Adding the rowguid column increases the size of the publishing table. The rowguid column and the index are added to the publishing table the first time the Snapshot Agent executes for the publication.
[/font=Arial
December 8, 2002 at 4:15 am
Can bi-directional replication be established on the same table?
December 8, 2002 at 6:21 am
quote:
Can bi-directional replication be established on the same table?
Yes. With Transactional Replication you set publication with updating subscriber. With Merge it uses ROWGUID (or a uniqueidenitifier column) to identify each row uniquely so subscriber can update.
However, you cannot use Snapshot replication as it will overwrite the data completely from the other set.
See BOL Merge replication and Transactional replication for more details.
December 18, 2002 at 8:25 am
When I use the same table between two remote servers, I have a problem when I try to commit two consecutive update transactions on the same row in the same column.
For example:
1. t1 updates row i column j -->
2. t2 updates the same row i column j -->
3. both of the transactions are replicated and committed to the other server -->
4. stages 1 to 3 are in infinite loop (log reader and distribution agent are continuously delivering transactions)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply