Indexes and Replication

Question

Indexes and Replication

Noman Tariq

SSCommitted

Points: 1750
More actions
May 10, 2006 at 1:17 pm

#112747

Hi all,
Please guide me... im going to create indexes on some tables which are involved in the replicaion..
will there be any effect on my replication or not??
look forward for the guidence.
Regards,
Noman

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Robert Davis One Orange Chip Points: 28027 More actions · Answer 1

It depends. Adding indexes always has some negative impact on performance of inserts and updates (of which replication does a lot). So it depends on the index.

The main thing to be aware of with adding indexes to replicated tables is that the indexes do not get replicated, so you have to add the indexes on each server separately if you want the other servers to have the indexes.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Noman Tariq SSCommitted Points: 1750 More actions · Answer 2

Yes that is I want.. but can u guide me again ..

how should i can not get indexes in the replication.

Thanx alot.

Noman

Robert Davis One Orange Chip Points: 28027 More actions · Answer 3

Your question isn't clear. What exactly do you need guidance with?

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Noman Tariq SSCommitted Points: 1750 More actions · Answer 4

you wrote that i should be aware that these indexses should not the part of replication. this is my questions how can i do this, bcz my table is in replication but i want the indexes on one server seperatly. can i do it without any problem in replication??

Thanks for your quick response.

Noman

Robert Davis One Orange Chip Points: 28027 More actions · Answer 5

I did not say that they "should not" be part of replication, I said that they "are not" part of replication. Indexes will only be on the server that you put them on. If you want it on all servers, you have to put it on all servers. If you want it on only 1 server, then only put it on 1 server.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Noman Tariq SSCommitted Points: 1750 More actions · Answer 6

Okay!!

You mean if i create an index on Server1 it will not take any effect on the replication.??

Thanks alot.

Noman

NeverDie Old Hand Points: 306 More actions · Answer 7

I've found that indexes don't get replicated with SQL Server 2005 Transactional Replication that's set up as a No Sync Publication.

I haven't tested for a Full Sync Publication though.

Does anyone know if indexes are replicated with SQL Server 2005 Transactional Replication that's set up as a FULL Sync Publication?