Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2002
    Posts
    53

    Question Unanswered: Problems with Snapshot Replication

    Hello,

    We are using Snapshot replication to move data for our Data Warehouse from a server which performs the loading to the production reporting server. We are currently using Sql Server 2005 in both environments. The loading server is setup as the Distributor and Publisher and the production reporting server is setup as the subscriber. However, the replication is not automated instead we only want it to run if the load process is successful. To do this we did not set a schedule for replication but added the jobs that the Replication Wizard created to our own Sql Agent job which manages the load. I have listed the below commands that the sql agent is running for replication in the order in which they are ran.

    1) -Publisher [SRVLOADER] -PublisherDB [PRESENTATION] -Distributor [SRVLOADER] -Publication [SRVREPORTER] -DistributorSecurityMode 1

    2)-Subscriber [SRVREPORTER] -SubscriberDB [PRESENTATION] -Publisher [SRVLOADER] -Distributor [SRVLOADER] -DistributorSecurityMode 1 -Publication [SRVREPORTER] -PublisherDB [PRESENTATION]

    In our own job we run each of these jobs using the EXEC SP_START_JOB() procedure. Each job listed above runs under their own PROXY account.

    Finally, we are experiencing an intermittent problem where the Agent will report it is unable to Copy data into "X" table, with "X" being any one of the tables in the reporting database. This has only ever occurred during the automation process and not from manually running each agent job. This problem also might not occur for a couple of days and then might occur 2 or 3 days in a row, which has led us to question the dependability of this approach.

    In closing I would like to ask if there is a problem with the way we have set it up? Something we could do to improve the process so it becomes more reliable? Any advice would be greatly appreciated.
    KDK

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The fact that the issue is intermittent would seem to rule out a security problem. I have to wonder if maybe it is a time-out issue with the connection, which would vary with the size of the tables and the amount of activity and make the problem difficult to replicate.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2002
    Posts
    53
    Hi Blindman,

    Thank you for responding. One of the primary concerns I have is that the only way you can tell the process is failing is by opening the replication monitor. The SP_START_JOB() procedure never reports a failure even though the process is failing.

    The only other thing I thought could be happening, although I haven't been able to witness it, is that the first process:
    1) -Publisher [SRVLOADER] -PublisherDB [PRESENTATION] -Distributor [SRVLOADER] -Publication [SRVREPORTER] -DistributorSecurityMode 1

    is rebuilding the files used in the bulk load process.

    Is it possible that this process might not be completing before the 2nd process starts? The second job is actually pushing the data.

    I am kinda at my wits end on this one. Any further ideas or alternatives would be greatly appreciated.
    KDK

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I wish I had more experience with replication to help you out, but I have only implemented it a few times.
    I'd suggest you try the standard trouble shooting techniques. Try breaking the process down into smaller pieces (such as replicating half the tables) and see if you can't isolate the issue.
    Also, replication is fairly complex both internally and externally. So anything you do out of the ordinary is suspect. This comment from your post would raise warning flags with me:
    Quote Originally Posted by KrustyDeKlown
    However, the replication is not automated instead we only want it to run if the load process is successful. To do this we did not set a schedule for replication but added the jobs that the Replication Wizard created to our own Sql Agent job which manages the load.
    What happens if you kick off the job manually rather than having it triggered by the load process?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Sep 2002
    Posts
    53
    If I manually go in and start the two jobs it has always worked. When manually starting the jobs I run the same 2 process in the same order. The jobs should not be running as me since they are set to run under a proxy account. So I believe that shouldn't be the issue.

    To me I THINK the issue lies with the way that the Sql Jobs are invoked because they never report a failure even though one is occurring. I can only assume at this point that when I invoke sp_start_job that it may be running asynchronously. Once again just an assumption. Unfortunately BOL doesn't state whether it runs asynchronously or not, it just states it returns a 1 or a 0.
    KDK

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are you logging the output of the job? Unfortunately, this is really a pain in the ass to do with 2005. It was easy with 2000, but it is one of several things Microsquish boloxed up with the latest version.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •