Friday, March 30, 2012

Question about snapshot article defaults and indexes

I have recently been comparing the size of the source and replicated
tables and realized that indexes are not being replicated. The GLOBAL
(Applies to all) article defaults for the snapshot are set as follows:
Copy objects to destination
Indexes for primary keys are always copied
Include declared referential integrityCHECKED
Clustered indexesCHECKED
Nonclustered indexesCHECKED
User triggersNOT CHECKED
Extended propertiesNOT CHECKED
CollationNOT CHECKED
I these defaults should transfer the indexes correctly so I had to look
for another culprit. We have a script which creates three tables daily
so I decided to look at the article defaults for those tables and found
that only the 'Include declared referential integrity box is CHECKED.
QUESTION: What is the stored procedure where these variables can be
set? Is it sp_addarticle and if so what fields?
If we need to use the replicated db what would have been the
implication of not having the proper indexing. Performance?
to set them use the schemaoption parameter of sp_addarticle.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

No comments:

Post a Comment