SQL Server Performance Forum – Threads Archive
Snapshot Performance Issue
I’m new to replication and am trying to add a column to a replicated table. The column is defined as NOT NULL and cannot have a default value. The only way I have been able to add a column is to drop the subscription and publication, drop and recreate the table, recreate the publication and subscription and run another Snapshot. I know I am missing something. I can’t believe I need to run SnapShot and resend the entire database for one column change. If we were a 24×7 shop replicating a 100+ GB database, the downtime during the snapshot process would be unacceptable. Is there another way to add a NOT NULL column with no Default value to SQL Server without have to take the performance hit of an entire database Snapshot? Thanks, DaveReplication is mimicking database behaviour.
If a new column is mandatory and is being added to a table that already exists, then what values should the new column be given for those rows that already exist?
The only way round your problem is to either:
remove all the existing rows in the table then add a column
or do as you suggested and add a column with a default.
Add a default value and then remove the default. For example, if your column was a varchar, then I would add a default of blank and then remove the default.
"How do you expect to beat me when I am forever?"
When using replication, a column can only be added or dropped using stored procedures. Unfortunately, this invalidates the above solution.
This has caused problems to myself too, so if anyone has a workaround I’d be keen to know.
The problem you describe is one of the reasons I avoid replication. I don’t know of any easy work-arounds.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
If it’s possible, can you consider log shipping? Log shipping refers to backing up the log every x number of seconds/minutes and then restoring it on a seperate box.
"How do you expect to beat me when I am forever?"
Hopefully this response won’t show up twice. The forum returned an error on my first reply. Unfortunately Log Shipping is not an option, at least at this point. We have been performing replication on 5 databases for over a year. The previous DBA responsible for the replication environment has not been with the company for over 4 months. As luck would have it we have not received any requests to modify a table marked for replication, until now. I know the process I described in my original thread will work, but I hate the idea of having to run a Snapshot to re-send the entire contents of a database when only one table is affected by the change. Another thing that makes matters worse is that all 5 databases are involved with cross-database dependencies (not my doing). If I understand how SnapShot works, the Publisher and the Subscriber are locked until Snapshot completes. With cross database dependencies, the 4 untouched databases are useless until the Snapshot completes. I’m not sure the other DBA considered the implications, either that or he accepted the risk. Am I missing something?
Is it possible to run SnapShot for only the modified article?
How does everyone else handle object changes?
If I melt dry ice can I swim without getting wet? Thanks, Dave
If using Merge Replication, the snapshot does not have to be re-applied, as long as there are no changes to the Subscriber. The MergeAgent can be dropped, changes made and the Subscription recreated in no sync mode.
Sorry. I left that part out. We are using Transactional replication.
I still think you are stuck doing it the hard way.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
I am little bit oput of practice with Transaction Replication. My understanding, however, is that the new snapshot need not be applied, as long there have been no data chanegs to the Subscriber.
I have a table that has columns defined as NOT NULL without default values. If I add a default value to a column to avoid this issue, run the snapshot, and then remove the default from the Publishing site, should the replication then work correctly?
]]>