Transactional Replication 2005 -- sql server replication

georgeg
7/4/2006 5:30:02 AM

Paul,
I am publishing to a subscriber that becomes a publisher. What should I do?
Here are the details.
Publisher: GYS subscribes to GYS_SUB, then GYS_SUN publishes to GYS_HIST.

Thanks
--
George Gopie

[quoted text, click to view]

georgeg
7/4/2006 8:06:01 AM

Hilary,
I am trying to accomplish transactional replication from publisher a to
subscriber b and then use subscriber B as a publisher to subscriber c. I
changed the Properties to Keep Existing object unchanged and this seems to
work, but now I am getting
Violation of PRIMARY KEY Constraing, Cannot insert duplicate key ...Error
2627. I tried to set the distribution agent property skiperror 2627, but the
problem continues. I know in sql server 2000 sp pk1 solve this issue, but now
I am using 64 bit sql server 2005.
Thanks
--
George Gopie

[quoted text, click to view]

georgeg
7/4/2006 9:34:02 AM

Paul,
I am trying to accomplish transactional replication from publisher a to
subscriber b and then use subscriber B as a publisher to subscriber c. I
changed the Properties to Keep Existing object unchanged and this seems to
work, but now I am getting
Violation of PRIMARY KEY Constraing, Cannot insert duplicate key ...Error
2627. I tried to set the distribution agent property skiperrors 2627, but the
problem continues. I know in sql server 2000 sp pk1 solve this issue, but now
I am using 64 bit sql server 2005 sp1.
Can I drop the table from the publication and subscriber and then readd it
using
sp_droparticle stored procedure? Would this work or I need to redo the
entire replication process?

Thanks, Paul, you and Hilary are the most helpful people.

--
George Gopie

[quoted text, click to view]

Paul Ibison
7/4/2006 2:01:32 PM

If you have servers A -> B -> C then if they are set up in the order A,B,C
ie the publication from A->B is created before B->C there is no issue. In
your case it is the other way round, so you'll need to do a nosync
initialization (couple of articles on
http://www.replicationanswers.com/Articles.asp). This could be interesting,
if you are using identity columns as they'll need setting up on Server A if
they aren't there already. Apart from that it should be ok. You won't be
able to propagate schema changes or use tracer tokens (SQL 2005) but these
are usually relatively minor restrictions.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

georgeg
7/4/2006 3:17:01 PM

I restarted the setu of replication and all seems well. Then after the doing
bulk copies and some other stuff, I get the following error:

Explicit value must be specified for Identity column in table
'ProductUnitAudit' either when IDENTITY_INSERT is set to ON or when a
replication user is inserting into a NOT FOR REPLICATION identity column.

Thanks,oce again Paul and Hilary.

--
George Gopie

[quoted text, click to view]

Paul Ibison
7/4/2006 5:59:39 PM

Hmmm. This PK error shouldn't be possible, assuming the data on A, B and C
is synchronized at startup. Any idea how it has occurred? This is worrying
as it implies that B is being edited, which is disallowed - only the final
server to be set up (A) can be edited and B and C must be teated as RO. If
the editing of B is temporary, you'll have to wait till you have complete
control of the system then prevent access, backup the database and restore
on A then set up the nosync initialization. Remember that after that B and C
must be RO to the users.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Hilary Cotter
7/4/2006 9:20:30 PM

Try the continue on data consistency errors profile.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

[quoted text, click to view]

Paul Ibison
7/5/2006 12:00:00 AM

The server A should have identity columns, while B and C shouldn't have the
identity attribute.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

georgeg
7/5/2006 4:28:02 AM

Paul,

I do not understand this. My server A is the publisher production database,
and server B has the subscription database. I have not yet setup B as a
publisher to C as a subscriber. The B abd C databases are on the same server.
I looked at the table that I am having the problem with and there is a column
called Id datatype Idkey(nvarchar(50) on the production DB and the same
column on the subscriber DB is the same datatype. Is This the probelm? Can I
modify the table on the subscriber DB and change the column ID to datatype
nvarcahr(50).

Thanks again,
--
George Gopie

[quoted text, click to view]

georgeg
7/5/2006 4:39:01 AM

Paul,

I just learned that several tables had USER Defined datatypes on the
production databases. Is this going to be a problem for replication? If so,
how can I replicate the IDkey column datatype?
--
George Gopie

[quoted text, click to view]

georgeg
7/5/2006 6:47:02 AM

How can I transactionally replicate tables have columns where the primary
column identity is set to true and the column is incremented in the table. In
order words the Table properties are as follows:

AuditKey

Identity True
Identity seed 1
Identity increment 1

--
George Gopie

[quoted text, click to view]

Paul Ibison
7/5/2006 2:59:34 PM

OK - this makes sense.
On the Destination Object tab there is the option to convert user defined
datatypes to base data types. Alternatively you could add a pre-snapshot
script which adds the user-defined type to the subscriber before the table
gets added.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Paul Ibison
7/5/2006 2:59:41 PM

OK - apologies - somehow your earlier descriptions led me down the path of
thinking that B and C were already set up :)
I see the user defined datatype in there and have answered this on the other
thread.
Cheers,
Paul Ibison

[quoted text, click to view]

georgeg
7/5/2006 3:25:01 PM

Paul,

I have done a test with the exact steps with all the necessary
configurations with the production database restored on a sqlserver 2005 32
bit sp 1 and it worked fine. No errors. There were no active transactions
when i di this. also, the subscriber was configured on the same server as the
publisher.

The error is happening on a 64bit sql server 2005 sp1. The publisher and
distrbutor is on the same server. The subscription is on another server with
same version of sql server 2005. Also transactions are active when I am doing
this.

Steps:
1. Configure a distributor
2.Create a new publication
3. wait for the snapshot agent to finish all the tables. During this time
teh log reader is running transactions.

4. Create a new subscription.
5. I have created a new distributor agent.
6. After running for an hour it gives the error, about the identity column
insert., and starts over.

Does anyone know if there is a bug in sql server 2005 64bit running on
windows 2003?
Do you think the active transactions are cauing this issue.

I am at a lost in solving this isssue.

Thanks
--
George Gopie

[quoted text, click to view]

Raymond Mak [MSFT]
7/5/2006 4:22:18 PM

Hi George,

User-defined datatypes (TSQL & SQLCLR) should be handled automatically by
SQL2005 snapshot processing, please let us know if they aren't replicated
with the snapshot.

Thanks much,

-Raymond

[quoted text, click to view]

Paul Ibison
7/5/2006 11:47:23 PM

George,
sorry - as this error is reproducible and doesn't occur on 32-bit SQL Server
I'd raise a PSS call and see if there is a hotfix available.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

georgeg
7/6/2006 2:22:01 PM

I opened a PSS with Microsoft and come to find out there is a bug in sql
server 2005 with columns with identity type not for replication. I thought
that by just modify the property of the column by setting NOT FOR REPLICATION
to No, it would be OK. The Engineer and I found that when the snapshot is
created all is Well. As soon as the subscriber is created and the
synchronization agent started the NOT FOR REPLICATION is reset to yes on both
publisher and subscriber. The work around so far is to modify all the sch
files that are created when the snapshot is completed. That is, I remove the
NOT FOR REPLICATION on the Identity column. Then I create the subscription
and this seems to work fine. Now after syncronization agent runs for aabout
anh hour I get new error:

Cannot insert explicit value for identity column in table
'ProductUnitProperty' when IDENTITY_INSERT is set to OFF. (Source:
MSSQLServer, Error number: 544

So I set the INDENTITY_INSERT TO ON for this table. I am still waiting to
see what will happen.
--
George Gopie

[quoted text, click to view]

Raymond Mak [MSFT]
7/6/2006 3:57:29 PM

Hi George,

First of all, did you experience any issues with user-defined data types?

In general, the identity NFR property setting has to match between publisher
and subscriber, and by removing it in the .sch scripts, you have created a
mismatch between the publisher and the subscriber which directly leads to
the new identity insert problem that you just saw. Since I don't know which
identity NFR bug that PSS mentioned and what exactly do you plan to
accomplish (do you plan to have updates outside of replication at B and C?
Do any of subscribers publish back to any of your publishers?), my best
guess is that you can try to make sure that identity NFR is disabled at all
your publishers and subscribers. Unfortunately, setting up a publication
through the SQL2005 SSMS will guarantee that identity NFR is enabled for you
at the publisher even though you didn't enable it on your table in the first
place. I can think of the following two ways to handle this:

1) Create your publications and articles by calling system stored procedures
directly and make sure that you have @identitymanagementoption set to 'none'
for your articles and your publications do not allow queued or immediate
updating subscriptions. You can try enabling the 0x04 schema option if you
want to have identity property preserved at your subscribers although I am
not 100% sure that doing so will not enable the identity NFR property at the
publisher.
2) Call the undocumented sp_identitycolumnforreplication to disable identity
NFR property after you have set up your publication but before the snapshot
is generated.

Having said the above, I can't help but feel that you probably need identity
NFR on all your servers for things to work properly. As such, I am hoping
that you can give more details on the kind of problems that you were seeing
when you have identity NFR enabled everywhere (You mentioned unique key
violations, is that during the initial bulk load of snapshot data or while
incremental changes are replication? Did it happen when you have drop,delete
or truncate as the article pre-creation command? Did the subscriber tables
start off empty or do you want to have them non-empty in the first place?
Did you encounter the unique key violation for an update at the subscriber
performed outside of replication?)

Hope that helps,

-Raymond

[quoted text, click to view]

Paul Ibison
7/7/2006 12:00:00 AM

What Raymond suggests (no "2") can be achieved using the following script:

use published database

go

select 'sp_identitycolumnforreplication ' + cast(objid as varchar(100)) +
',0' from sysarticles

inner join syspublications

on sysarticles.pubid = syspublications.pubid

where syspublications.name = 'your publication name'

Replace "published database" and "your publication name". This script will
generate a script that you can then run in SSMS. I'll rewrite as a cursor
later on.

HTH,

Paul Ibison

georgeg
7/7/2006 2:49:02 AM

Raymond,
I am new to sql server 2005. All I am trying to do is make transactional
replication from production database as the publisher and subscribe it to
another database on another sever.

Theris no problem with User Defined data Types. I thought this was my problem.

steps:
1. Create the distributor on the publisher server
2. Create the local publisher. I only check off all the tables as my
articles and I set some of the properties to true. e.g foreign key, clustered
indexes, non clustered indexes, user triggers, etc. As soon as I finished
the publication, the snapshot agent runs and successfully creates the
snapshot. During this time, I noticed the Log Reader is also running.
3. I then create the subscriber, and the synchronization process starts.
4. I use Replication Monitor to see whats happening,after about an hour or
so, I get the errors about explicit value....which I mentioned at the
beginning of this thread.
5. The bug as per MS is in document 908711. This is the exact error I am
getting.

I really need some assistance. I do not understand how to do these steps you
mentioned.
Create your publications and articles by calling system stored procedures
directly and make sure that you have @identitymanagementoption set to 'none'
for your articles and your publications do not allow queued or immediate
updating subscriptions. You can try enabling the 0x04 schema option if you
want to have identity property preserved at your subscribers although I am
not 100% sure that doing so will not enable the identity NFR property at the
publisher.
2) Call the undocumented sp_identitycolumnforreplication to disable identity
NFR property after you have set up your publication but before the snapshot
is generated.

thanks,
--
George Gopie

[quoted text, click to view]

georgeg
7/9/2006 10:36:02 PM

Paul,Hilary, and Raymond,
I continue to have problems setting up this replication. Even Microsoft
engineer is having problems with IDENTITY Columns. We ttok a downtime to do a
backup and restore method, and now I am getting Error
The row was not found at the Subscriber when applying the replicated
command. Error 20598
Thanks for all your help.
--
George Gopie

[quoted text, click to view]

Paul Ibison
7/10/2006 12:00:00 AM

George,
I'm interested in investigating this myself and then I can see more easily
the issue. Assuming it's not too big, please can you zip up and send over a
copy of your database (NDA assumed). My email address is Paul . Ibison @
Replicationanswers.com (no spaces).
Rgds,
Paul Ibison

georgeg
7/14/2006 6:06:03 AM

Paul and Team,

Thanks very much for your input to this issue.

The problem is resolved by setting Copy User triggers to FALSE for all the
identity NOT FOR REPLICATION property tables. So, I set the Copy User
triggers to False for all Tables. However, I need the Copy User Triggers for
all other (200)tables to be replicated. Can I just run the Create Trigger
T-SQL on the subscriber database, without causing problems for the
synschronization? or I need to use sp_changearticle and change the publisher
for each table as follows:

sp_changearticle [ [@publication= ] 'publication' ]
[ , [ @article= ] 'article' ]
[ , [ @property= ] 'property' ]
[ , [ @value= ] 'value' ]
[ , [ @force_invalidate_snapshot = ] force_invalidate_snapshot ]
[ , [ @force_reinit_subscription = ] force_reinit_subscription ]
[ , [ @publisher = ] 'publisher' ]

--
George Gopie

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server replication

sql server replication : Transactional Replication 2005