In a company I've recently joined we have two SQL Server instances, connected via transactional replication. I'm a developer and hadn't really come across SQL replication before so am a complete novice. I'm looking at reducing the time it takes to deploy our products to the production environment and right now rebuilding replication is the worst offender. A deployment often involves the standard SQL schema changes (new tables/indexes, modifications to existing tables/indexes) and we have bespoke SQL CLR code that is often needed. At the moment if feels little like Russian roulette in that some changes can be applied without adversely affecting replication, whilst others (custom CLR code deploy) seems to require a rebuild (or is it the stored procs that must be dropped / recreated to access it?).
As a complete beginner with replication I was wondering if there is a resource or anyone knows a definitive list of changes that will force replication to need to be rebuilt? Please don't ask why we've got replication, or SQL CLR code, neither should be needed but their removal is going to take a while and we need to get on top of the length of time deployments take first. It would just be nice to understand before a release whether it was going to need to rebuild replication!
Best Answer
To answer the question, you would need to test how many rows your change will affect (an update to a column without a where clause, or adding a column will affect all rows). A reindex will not cause replication to be "rebuilt". Can you clarify what do you mean by "rebuilt" do you mean all rows are re-replicated? or do you mean you are forced to snapshot?
To know what is actually being replicated, you can use http://msdn.microsoft.com/en-us/library/ms186983.aspx