Have Geek Brain

How to manage sql structure updates

How do people recommend managing database structure updates?

eg. added new functions that require modifying existing tables, adding new ones, etc.

Is there a good piece of software or is it a case of writing type statements ALTER

.

+1


a source to share


4 answers


While there is software that can analyze the differences between the two schemas (RedGate SQL Compare) and generate the required change scripts, I prefer to write my own database change statements. Thus, I have complete control over what changes - nothing more, nothing less. Make changes to the Install.sql script or something for each version of the database so you can just run that script and update the database.

This makes it easier to migrate changes from dev to test to production.



See: Deploying SQL Server Databases from Test to Live

+4


a source


If you don't want to write all the alter statements, SQL Server Management Studio has a GUI for working with all these types of things. You can use the GUI and then look at the script it generates and branch out from there if you need some sort of quick hybrid approach.



+1


a source


I'm going to add one thing. It is not enough to write an alter table statement to change the structure of a table. If you change the structure of a table, you better be sure to know exactly what other views, functions, tables, stored procedures, triggers, SSIS packages (DTS) (for SQL Server) and dynamic code from applications will change before you run it. ... If you are not entirely sure what other objects might be affected, you are not ready to modify the table. I've seen too many business critical functions break because someone hapahazardly changed the table structure without considering who else was using that structure. If you are planning on making structural changes to your database, I suggest you familiarize yourself with refactoring the database before you do so.

Here's a good book to start with: http://www.amazon.com/Refactoring-Databases-Evolutionary-Addison-Wesley-Signature/dp/0321293533

+1


a source


You do not specify the scope of your application or the number of developers, etc., so it is a bit difficult to make any recommendations. However, if your development consists of multiple parallel projects and multiple developers, and you are copying from development to production, then I would recommend the following:

  • implement 3 "areas": dev, qa, production.
  • work out all changes in dev, create all changes in scripts, use something like cvs to track changes for all objects
  • When the changes are ready and tested, run your scripts in qa, this will confirm your scripts and installation procedure.
  • when ready to run scripts and setup during production

Note: qa is almost identical to production except for applied changes pending their final production setup. dev contains any work changes, additional debugging garbage, etc. You can periodically restore a production backup to qa and dev to resynchronize them (just make sure all developers are aware of this and plan accordingly) because (depending on the number of developers) they (production vs qa vs dev) will start to experience more differences over time.

0


a source






More articles:


All Articles