SQL Server Performance

problem replicating schema changes

Discussion in 'Performance Tuning for SQL Server Replication' started by fritz101, Jan 22, 2004.

  1. fritz101 New Member

    I'm using the following process to make schema changes to replicated databases (transactional replication, local distributor, push subscription, all servers are SQL 2000 with SP3):



    • drop the subscription to the table

    • drop the article so we can make changes to it

    • make schema changes

    • recreate the article

    • refresh the subscription

    • reinitialize the subscription to the changed article

    • run the snapshot agent for this publication, which I do in Enterprise Manager

    commands being used:

    sp_dropsubscription @publication='pubname', @article='articlename', @subscriber='all'
    sp_droparticle @publication='pubname', @article='articlename', @subscriber='all'
    eg alter table
    sp_addarticle @publication='pubname', @article='articlename', @sourcetable='tablename', @subscriber='all', @force_invalidate_snapshot = 1
    sp_refreshsubscriptions @publication='pubname'
    sp_reinitializesubscription @publication='pubname', @article='articlename', @for_schema_change= 1, @subscriber='all'


    For testing, I've even copied over a backup of a production database and performed all these steps successfully.
    The snapshot agent creates a snapshot of the changed article (and any articles related by a foreign key) by creating scripts, bulk copying data, and posting snapshot commands into the distribution database. The distribution agent then applies the scripts and bulk copies the data.

    In my test environment, everything works great. For some reason, on the production servers, everything works up until the changes are replicated. The scripts never get picked up by the distribution agent and applied at the subscriber (and of course no bulk copying takes place), therefore my schema changes never replicate. Its as if the distribution agent is unaware that a snapshot needs to be applied, whereas on my test servers, during snapshot generation, the distribution agent states that its waiting for the inital snapshot of article "articlename".

    Any ideas on where to start looking for the problem?
    Thanks!
  2. satya Moderator

    You can monitor the process using PROFILER on Production and Test environment to assess the information.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. satya Moderator

    You can monitor the process using PROFILER on Production and Test environment to assess the information.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page