SQL Server Performance

intergrat multiple server's data to central server

Discussion in 'SQL Server 2005 Replication' started by LiangZhu1, Apr 12, 2007.

  1. LiangZhu1 New Member

    All,<br /><br />I am working on replication.I am using sql server 2005 workgroup edition for the client and the central server is 2005 enterprise edition . <br /><br />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.<br /><br />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,<br /><br /> SELECT &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />ublished_columns&gt; FROM [dbo].[JobTemplates] WHERE [IsHistorical] =1 and [JobTemplateGuid] in (select [a].[JobTemplateGuid] from [servername].[databasename].[dbo].[JobTemplates] a where [a].[ishistorical]=1)<br /><br />I am getting security violation error. <br /><br />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<br /><br />Thank you,<br />Liang <br /><br />
  2. satya Moderator

    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.
  3. LiangZhu1 New Member

    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
  4. MohammedU New Member

    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.
  5. LiangZhu1 New Member

    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
  6. MohammedU New Member

    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.
  7. LiangZhu1 New Member

    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
  8. MohammedU New Member

    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.
  9. LiangZhu1 New Member

    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
  10. MohammedU New Member

    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.
  11. LiangZhu1 New Member

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

    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.
  13. LiangZhu1 New Member

    <br /> I did that. The PK violations are still there.[<img src='/community/emoticons/emotion-6.gif' alt=':(' />]
  14. MohammedU New Member

    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.
  15. LiangZhu1 New Member

  16. MohammedU New Member

Share This Page