How to Break Replication

Question

How to Break Replication

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

ramoha SSCommitted Points: 1909 More actions · Answer 1

Yes there was some issues with 2005 schema like some of the tables were owned by schema "Person" so I created a schema with the same name in 2000 and got the previous error "Incorrect syntax near line 3"

I was unable to find the sp_MSins stored procedure at my subscriber..

When I created a new replication stream using articles with dbo.object_name it worked no issues.

THanks

Razi, M.
http://questivity.com/it-training.html

ramoha SSCommitted Points: 1909 More actions · Answer 2

I found the solution for this problem:)

There are few things that I did to make it work from SQL Server 2005 to SQL Server 2000:

Note - 1. While creating the subscription DONOT use SYNC_TYPE = 'DEFAULT' this will try to overwrite the schema on the subscriber,

Change it to "replication support only"

2. Some of the create table syntax from 2005 does not work well in 2000 so make appropriate changes

Step 1: Created a subscriber on SQL Server 2005 instead of SQL 2000 then

Step 2: Copy the object creation scripts and execute the scripts with some changes for example below at the SQL 2000 subscriber

Example 1:

CREATE TABLE [Sales].[Cars](

[ProdID] [int] NOT NULL,

[ProdDesc] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Country] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[LastUpdate] [smalldatetime] NULL,

CONSTRAINT [PK__Cars__2A6B46EF] PRIMARY KEY CLUSTERED

(

[ProdID] ASC

)--WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

)ON [PRIMARY]

Example 2: Change xml data type to text or varchar(8000) etc

Step 3: Import data via SSIS or BULK_INSERT into the destination SQL 2000 database

Step 4: Do not re-initialize the subscription just let the LOG reader read the new transactions and you are all set 🙂

Regards

Razi, M.
http://questivity.com/it-training.html

Prakash Heda SSCertifiable Points: 5539 More actions · Answer 3

Excellent article, being relatively new to replication I found it very useful....

Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA

Vivien Xing SSChampion Points: 12297 More actions · Answer 4

A very good article. I booked mark it 2 weeks ago and just got a chance to read today.

For “General comments on my first two mistakes” section, I would like to limit user access to the objects/tables to avoid mistakes. The subscription is supposed to be read-only. If it allows changing, go object level permission then. Further more, data should be changed via application which is configured properly already (right server/database). Unless people change on the backend directly, this is easy to make a mistake.

This is the first thing came to my mind after I read the first 2 mistakes.

Meredith Ryan Right there with Babe Points: 749 More actions · Answer 5

Great article David!

The code snipets that dive into the distribution db really saved my bacon tonight.

thanks

Meredith

quackhandle1975 SSChampion Points: 11055 More actions · Answer 6

Nice article Dave, one for the briefcase.

Given my current client you may find some of the replication errors I am facing quite familiar. 😉

qh

[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]