Snapshot Meta Data Problems

Snapshot Meta Data Problems

• richardpc

  Hi Sean,
  
  Again as I previously asked in another thread: is this on SQL Server 2000 or SQL Server 2005
  I tried on SQL Server 2005 and the distrib agent resumed from where it left off.
  This is a new feature in SQL Server 2005. SQL Server 2000 did not have this feature. So if you are running on SQL Server 2000, you will see that distrib will apply everything everytime after interruption.
  
  

• Frode G

  Hi Sean,
  
  There is no sp that can check the similarity of schemas of the articles for you.
  
  Another thought of doing this, could be to first create the table, indexes etc on one site and script them off. Then utilize this script file to create the tables on the other site. That way your schemas will match and this has to be done only once. Thereafter, unless you modify any of the sites manually, the schemas should continue to match.
  
  The distrib agent should pick up from where it left off. If it applied 5 of the 40 articles, it should skip articles 1 through 5 and then start applying from article 6 onwards. Are you seeing anything different
  
  

• NitinVRaj

  No, by default the distrib picks up from where it left. It actually skips parts that it has already done.
  As I said, if I ave 20 articles, and the schema for the first 5 are applied at which point, say the distrib agent is interrupted, when rerun again, it skips the first 5 articles schema application and then proceeds with the 6th article.
  
  For eg: I had a push subscription to a publication with 20 articles. I interrupted it and when I reran again, I see the following:
  
  2005-09-27 17:23:27.851 Skipping file 'tbl2_13.sch' because it has already been
  delivered for a previous article or by a previously interrupted snapshot.
  2005-09-27 17:23:27.867 Skipping file 'tbl20_14.sch' because it has already been
   delivered for a previous article or by a previously interrupted snapshot.
  2005-09-27 17:23:27.882 Skipping file 'tbl3_15.sch' because it has already been
  delivered for a previous article or by a previously interrupted snapshot.
  2005-09-27 17:23:27.914 Applied script 'tbl4_16.sch'
  2005-09-27 17:23:27.945 Applied script 'tbl5_17.sch'
  2005-09-27 17:23:27.992 Applied script 'tbl6_18.sch'
  
  I hope you are also talking about snapshot schema script application. If not, please clarify.
  
  

• Marcin Wojas

  HI Mahesh
  
  That is very odd, where do you see this output Did you use verbose logging on the job
  
  I will be trying to replicate this from our QA to test environemnts so that i can really play around and see what is happening.
  I am sure mine started from the begining everytime
  
  You seem to know alot about replication, i thought i knew a bit myself until i started to try these wierd and wonderful replication ideas.
  
  I have struglled to find a good resource dedicated to replication.
  
  Do you know where there is a good white paper or anything that is dedicated to Sql Server 2000 replication and really covers it inside out I have been looking for ages without success
  
  

• Sudhakar J

  Hi Mahesh
  
  This is Sql Server 2000, sorry should have stated that long ago, that would explain the distribution agent.
  Thank You very much on the advice and all the help so far.
  
  

• xFors

  Hi Sean,
  I am not sure if you can replicate successfully when your table structures are not matching. However you can use the drop table option and then setup the permissions using @post_snapshot_script.
  
  Please take a look at @pre_snapshot_script and @post_snapshot_script in sp_addpublication in BOL. Using these, you can potentially verify the structure of the table and then setup permissions on the table as required (using sps created by you and called in these scripts).
  
  [ @pre_snapshot_script= ] 'pre_snapshot_script'
  Specifies a pointer to an .sql file location. pre_snapshot_script is nvarchar(255),with a default of NULL. The Distribution Agent will run the pre-snapshot script before running any of the replicated object scripts when applying a snapshot at a Subscriber. The script is executed in the security context used by the Distribution Agent when connecting to the subscription database.

  [ @post_snapshot_script= ] 'post_snapshot_script'
  Specifies a pointer to an .sql file location. post_snapshot_script is nvarchar(255), with a default of NULL. The Distribution Agent will run the post-snapshot script after all the other replicated object scripts and data have been applied during an initial synchronization. The script is executed in the security context used by the Distribution Agent when connecting to the subscription database.
  
  Also, look at sp_addarticle parameter:
  [ @creation_script = ] 'creation_script'

  Is the path and name of an optional article schema script used to create the article in the subscription database. creation_script is nvarchar(255), with a default of NULL. Creation scripts are not run on SQL Server Mobile  Subscribers.

  
  
  

• Carl Perry - Microsoft

  Hi Mahesh
  
  Well now that i have sorted out the schema problems you are right they should hopefully stay in sync from now onwards.
  No everytime the distribution agent failed and i restarted it it started from the first article again It was driving me crazy because everytime i had to wait over an hour to encounter an error..
  Is there a setting to check this as my distribution agents definately start from the beginning when applying a snapshot
  
  

• ashishmanasvi

  Hi Mahesh
  
  Thank you for the assistance.I am aware of these options, my main concern if that i need to check before i do snapshot replication that the 400 articles invloved have exactly the same schema before i start the snapshot.
  
  I have written scripts that check number of columns etc, but it is getting complicated.
  
  I was wondering if there is an sp or something can compares if tables are identical
  
  I cannot drop the articles becuase they are being transactionally replicated from the snapshot subscriber.
  
  Do you have any answers on starting the distribution agen from the last successful article, or must it start from the beginning everytime
  
  Bottom line is that i need to make sure tables on 2 database are identical before starting the snapshot, not sure how to best accomplish this
  
  Thanx
  
  
• Snapshot Meta Data Problems