SQL Server Performance Forum – Threads Archive

intergrat multiple server’s data to central server

All, I am working on replication.I am using sql server 2005 workgroup edition for the client and the central server is 2005 enterprise edition . I am intergrating three workgroup editions’data to a single database in the central server. The databae in each client server, some of tables contain run data, some of tables have exactly same on every client and some of the tables may just have several different records from others. I have tried with Transacation replication. In the Article properties, in the destination object and under Action if name is in use I have tried ‘keep existing object unchanged’, drop existing object and create a new one’ and ‘Delete data, if article has a row filter, delete only data that matichse the filter’. With ‘keep existing object unchanged’ I am keep getting PK violation, With ‘drop exsting obejct’ I am keep getting ‘the row was not found at the subscrber’, with the row filter I have tried the following query, SELECT &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />ublished_columns> FROM [dbo].[JobTemplates] WHERE [IsHistorical] =1 and [JobTemplateGuid] in (select [a].[JobTemplateGuid] from [servername].[databasename].[dbo].[JobTemplates] a where [a].[ishistorical]=1) I am getting security violation error. Should I look at something else beside Article properties to make the scenario work. Would Transacation replication work for my scenario. Would merge replication work for this scenario? if merge replication would work, what Destination Obejct do I have to chose? Please let me know Thank you, Liang

Enable the distribution agent to skip the primary key violation errors.
Presume you have similar and relevant service packs on all the SQL instance. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Satya, Could you tell me how? I thought the distribution agent was automaticly started during the set up of the publication. Also, I would like to know if Transacational replication with updatable subscriptions
would do the Multiple publishers and central subscriber trick for me? Thank you,
Liang

Yes, distribution agent is automatically started but if it fails with the error you need to start manually or add another schedule to the job to start every few minutes… To resolve your issue…
You need to change the distribution agent profile to use "Continue on data consistency errors." profile instead of default profile in replication monitor.
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

MohammedU Thank you for the infor! I have tried what you have recommanded, the data still now showing in the Subscriber. I have checked the replication Monitior, I got the message says the data bulk into ‘databasename’. I have checked the Subscriber table, The table data just was not there. I did I do wrong? Liang

Based on my udersanding you want to copy the data from multiple servers to single server…but you are getting PK voilation due to same data on multiple servers… To avoid this I asked you to change the distribution agent profile to use "Continue on data consistency errors." profile instead of default profile in replication monitor on your publication servers. If this is not what you are doing please let me know… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

MohammedU The central subscriber model is exact scenario I would like to achieve. I have switched the distribution agent’s profile from default profile to ‘continue on data consistency error’ on every client and central server. And I have recreated the publication and suscription. The replication still stumped on the PK violation. This is what I did: 1) I left click on the local publication
2) click on the profile defaults on the distributor properties UI
3) checked the ‘continue on data consistency error’ Did I do anything wrong? Your help would be very much appreciated Thank you,
Liang

I did use this before…. Try stop and restart your replication agents or…
SQL Server Agent….and see what happens… If restart not resolved then… try using -SkipError option in distribution job…
Replication Agent Profiles
http://msdn2.microsoft.com/en-us/library/ms151223.aspx
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

stop and restart your the server agent did not help.
I do not know if I have put the -SkipError inot the correctly place, I have put -SkipError under one of Run_agents, I did not get it working. Can I put the -SkipError string into any jobs or got be a particular job? Liang

You should put it in Distribution agent jobs…
Right the distribution agent and click propertites… then sql job properties window will popup…You should add this in step2…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Under the SQL Server Agent, Those are the items I have: 1) Jobs a) Agent history clearn up<img src=’/community/emoticons/emotion-2.gif’ alt=’:d’ />istribution b) ‘server name’-‘database name’-7 c) ‘server name’-‘database name’-‘publication name’-‘subscrier sever name’-60 (this is the place where the distribution agents is) under here I have added the -Skiperror, the statement looks like this -Subscriber [SQLTEST] -SubscriberDB [bbbbbbbbbb] -Publisher [CHAMP] -Distributor [CHAMP] -DistributorSecurityMode 1 -Publication [ababababababab] -PublisherDB [Champion] -SkipErrors 2601:2627:20258 -Continuous d) distrubution clearn up<img src=’/community/emoticons/emotion-2.gif’ alt=’:d’ />istribution e) expired subscription clearn up f) reinitalize subscriptions haveing data validataion failures g) replication agents checkup h) replication monitoring refresher for districution 2)Alert 3) Operators 4) Proxies 5) error logs I have added ‘-SkipErrors 2601:2627:20598′ in everyone of those jobs, the violation still there [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />]

Only you should add to distribution agent…and make sure you restarted the agent… c) ‘server name’-‘database name’-‘publication name’-‘subscrier
sever name’-60 (this is the place where the distribution agents is)
under here I have added the -Skiperror, the statement looks like this -Subscriber [SQLTEST] -SubscriberDB [bbbbbbbbbb] -Publisher [CHAMP] -Distributor [CHAMP] -DistributorSecurityMode 1 -Publication [ababababababab] -PublisherDB [Champion]
-SkipErrors 2601:2627:20258 -Continuous Check the following…
SQL Server 2005 Books Online
Skipping Errors in Transactional Replication
http://msdn2.microsoft.com/en-us/library/ms151331.aspx
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I did that. The PK violations are still there.[<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />]

To honest with you, something else might be wrong…I did this before many times without any issues… I will advise you to open a ticket with MS…. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

MohammedU I have followed this on line book to set up the Transacational replication. http://msdn2.microsoft.com/en-us/library/aa337453.aspx Please let me know if this set up the replication correctly. Thank you,
Liang

It should setup correctly if follow the directions…
You can also check the following… too… http://www.awprofessional.com/articles/article.asp?p=599700&rl=1
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>