SQL Server Performance Forum – Threads Archive
Integrating Development db changes into Production
I’m considering to approaches to integrating changes into an exisitng production dbfrom a development db. The main challenge is how to deal with schema changes that effect existing tables. Mainly adding and removing existing columns. Sometimes these changes occur to tables that are ~1 million records I realize that there are several tools out there that are capable of comparing and applying the changes(redgate and apex)
1) use alter statements
This allows me to alter the schema. The main problem with this apporach is the potential locking for the duration of the alter statement. It is also tedious to script out the alter statements by hand or even via enterprise manager. 2) create scripts
just have a create scripts that produce the target table, then set identity insert on
and bulk insert the existing data from the source table into the new table,
add indexes and constraints finally, drop the old table and rename the new table.
The crux of my question is if the overhead of maintaining the alter scripts pays off in less downtime for the db when the scripts get applied… Does anyone have any experience with either of these methods?
Redgate sql compare simply generates the alter statements for you…even on large tables, i havent found any major delays when applying the changes. I use this and tweak the SQL statements if required- quick and easy in my opinion! I havent really had any experience with option 2, but it sounds like it might introduce more opportunity for error (in my opinion)…. Ben ‘I reject your reality and substitute my own’ – Adam Savage
For any of such schema changes we normally go with scripting of new schema and then apply to the production table or else you can go with third party tools if the budget allows to do so. If the schema change is huge then you think about recreating table easily with CREATE TABLE from source server, if not you can use ALTER TABLE in order to take affect immediately during a scheduled outage. Review thishttp://www.sql-server-performance.com/gr_change_managemenet.asp article which gives more information about the proces.s Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>