Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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

share|improve this answer
    
So, if I understand you correctly replication can be affected by simply the number of rows that could be affected by an UPDATE statement, or DELETE, etc. So if we used a test environment to check our deployments we want it to be a true representation of PROD data otherwise we might appear that replication doesn't need to be rebuilt on one environment, but when we get to PROD it hits some row threshold and decides we need to rebuild replication there? –  Paul Hadfield Dec 24 '12 at 11:14
    
There is no concept of "rebuilding" a replication automatically if a threshold is reached, a replication simply aims to synchronize two tables, so if the number of changes made is large, it will take a longer time to synchronize the changes. So if you want to measure the time taken to synchronize, make the changes in test and observe the time taken to synchronize on replication monitor.. –  Rohan Dec 24 '12 at 11:51
    
OK, thanks for the clarification, I'm looking for the changes that cause the subscription to be re-initialised. As an example we had a script that added a null-able column to an existing table - that ran fine, but the part that dropped and reloaded a CLR assembly error'd with a message that said replication needed to stopped and rebuilt/re-initialised. It's the changes that that cause this that we are interested. Is it even possible to change tracked options so that the CLR code isn't even tracked by replication –  Paul Hadfield Dec 24 '12 at 11:56
    
Aah, looks like you might have a code replication? Can you explain what does the CLR do? is it a part of the replication? –  Rohan Dec 24 '12 at 12:10
    
The SQL CLR is holding the application's custom code (see en.wikipedia.org/wiki/SQL_CLR for more information, I can't gather from your response if you do know what the CLR is, or just what our code in the CLR is doing). Basically our CLR code is your typical .NET solution/project including some .NET code based stored procedures, triggers and functions. Just to be clear, it's not ideal but it is inherited code and we have to live with it for the short/mid term. –  Paul Hadfield Dec 24 '12 at 12:14
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.