Bidirectional Transactional replication

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

blazedtrails - 28 Jul 2008 22:37 GMT

Hi All
Do any one know where to get good Knowledge resources for writing and
implementing stored procedures for bidirectional transactional replication.

I f any body has one implemented recently can you spare the steps involved
and the best practises associated with this type of replication.

Regards

Reply to this Message

Hilary Cotter - 29 Jul 2008 04:01 GMT

Here is something I put together.

Creating and dropping bi-directional transactional replication

Connect to the first publisher using SQL Server Management Studio.

Expand the Replication Folder, and expand the Publications folder. Locate the publication you wish to drop and right click on it and select generate scripts.

Select the option to create and save the file with a descriptive name.

Wait until the publication is scripted out click OK, and then close out of the dialog.

Drop the subscription by expanding the plus sign to the left of your publication, clicking on it and selecting delete.

Click Yes, to delete your subscription. Once this is complete, right click on your publication and select delete.

Accept all prompts to delete your publication.

Repeat this entire process on the other publisher.

Once this is done, make all necessary schema changes on both sides.

Now edit the two publication scripts that you have. They will look something like this”

exec sp_addsubscription @publication = N'ttt', @subscriber = N'F005996', @destination_db = N'Tina', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0

You must modify them to add the @loopback_detection=’true’ parameter as below

exec sp_addsubscription @publication = N'ttt', @subscriber = N'F005996', @destination_db = N'Tina', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0, @loopback_detection='true'

Save the scripts and then run them on both sides.

Now, you need to create the replication stored procedures. To do this connect to the publisher in SSMS, in the menu items Tools and Options, navigate to the below option:

Make sure Maxiumum number of characters displayed in each column is 8192. Then open up a new query window and issue the below in your publication database:

sp_scriptpublicationcustomprocs 'XXXXX'

This will create the replication stored procedures you need. Copy them, paste them in a new query window and execute them. Do this on both sides.

Start up the distribution agents and you are ready to go!

> Hi All
> Do any one know where to get good Knowledge resources for writing and
[quoted text clipped - 4 lines]
>
> Regards

Reply to this Message

blazedtrails - 29 Jul 2008 21:43 GMT

Thanks Hilary

That was a good start for me..

I could not find one sp_scriptpublicationcustomprocs IN sql 2000.Do we
havce one

Is the Bidirectional trnsactional replication the best alternative to an
Active/Active clustering for a low cost implementation?.

> Here is something I put together.
>
[quoted text clipped - 48 lines]
> >
> > Regards

Reply to this Message

Hilary Cotter - 30 Jul 2008 12:16 GMT

It should be in the master database in SQL 2000.

Do a

select name from sysobjects where name like '%custom%' to find it.

To answer your second question - what are your goals?

Basically Active Active is where you can failover two SQL Servers to
run on one machine at any one time. Your clients will be redirected.
There is minimal data loss and outage.

With replication you will have to manually redirect your clients,
however latencies can be all over the place and your exposure to data
loss can be very large. Also you need to worry about identity
elements.

On Jul 29, 4:43 pm, blazedtrails
<blazedtra...@discussions.microsoft.com> wrote:

> Thanks Hilary
>
[quoted text clipped - 56 lines]
>
> > > Regards

Reply to this Message