April 7, 2009 at 12:03 pm
HI,
We recently added indixes as a part of the replication process from publisher to sunscriber. I see indexes at the replicated databases. MY question is, though indexes are replicated, do we need to run a re-index script again ? or re--indexed data on publisher and subscriber are same ?
Like if we restore databases, we have to re-index though indexes are also restored.
Thanks,
-VG
April 7, 2009 at 1:41 pm
no. you should not need to reindex.
you also should not need to reindex after a restore.
(unless the indexes in your backup are fragmented)
April 7, 2009 at 3:04 pm
VG (4/7/2009)
HI,We recently added indixes as a part of the replication process from publisher to sunscriber. I see indexes at the replicated databases. MY question is, though indexes are replicated, do we need to run a re-index script again ? or re--indexed data on publisher and subscriber are same ?
Like if we restore databases, we have to re-index though indexes are also restored.
Thanks,
-VG
If what you meant is that re-indexing the publisher will fix fragmentation on the subscriber the answer is no.
You need to maintain indexes there as well.
Restoring a database does not imposes the need for reindexing.
* Noel
April 7, 2009 at 3:25 pm
Hi Noel,
So, we should have a re-index job on subscriber databases as well ? We have reports running on these subsciber databases.
restoring databases I mean is, restoring the production backups to testing databases, then we usually re-build index after restoring on testing machine.
Thanks for your input.
VG
April 7, 2009 at 4:48 pm
You'd have to test, but I don't believe the replication moves indexing across. Mirroring would, since it reads the logs and sends the transaction across. I believe replication only moves insert/update/deletes across.
You also do not necessarily need the same indexes on the replicated database. Depending on what it's used for, you might have more, less, or different indexes.
April 8, 2009 at 9:40 am
Hi Steve,
We have a nightly job which brings the snapshots to the replicated databases and what I see in replicated databases,(at the subscriber) the indexes are overwritten. I tested this by creating a index for a table (at publisher) and then running a snapshot agent and deleting the index at the publisher and running the snapshot agent. But, I am not sure whether snapshot agent brings over the indexed data or I may be doing something wrong here..
Thanks,
VG
April 14, 2009 at 11:54 am
If you are using snapshot replication your indexes won't be fragmented because they are freshly created every time. If you are using transactional replication you do need re-indexing jobs on the subscriber(s).
* Noel
April 14, 2009 at 4:08 pm
Thanks Noel !!! I figured it too...
September 8, 2010 at 2:04 am
There is an option to replicate indexes (clustered/non-clustered) and you dont need to rebuild the index
September 8, 2010 at 2:18 am
This link confirms what a few other are says..
Schema options allow you to specify how attributes and objects associated with tables, such as indexes and constraints, are replicated. The behavior of a number of schema options has changed in SQL Server 2005. The next section of this topic provides more information.
September 8, 2010 at 2:46 am
kssirohi (9/8/2010)
There is an option to replicate indexes (clustered/non-clustered) and you dont need to rebuild the index
What/Where is the option (to replicate indexes)?
Please elaborate your reply.
September 9, 2010 at 7:18 am
The option must be selected as one of the properties of the article to be replicated. If you're using the GUI, right click on the article and choose "Set properties of this table article". Clustered and non-clustered indexes are two of the properties that can be configured.
Please note that it replicates the DDL of the indexes not the actual index rows. If you add a new index to the publisher, the ddl of the index will be replicated if this property is selected. This is why you must perform index maintenance on the subscriber database as well. It's possible that the non-clustered index on field A of table B is not fragmented on the publisher but might be heavily fragmented on the subscriber or vice-versa. They are separate databases, populated from different sources, i.e. the application versus the distributor, usually on separate servers and separate storage arrays and possibly with different file groups and partition schemes. They are not clones.
"Beliefs" get in the way of learning.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply