Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bmassey
Starting Member
22 Posts |
 Posted - 2008-02-01 : 21:42:32
|
At my company, we used to produce a deployment document listing all new and changed objects. Then, the DBA would copy each object over, one at a time. As you might guess, this was a very time consuming and error prone process. Now, we are having the dev team create deployment scripts that are passed on to QA and Production.We have purchased licenses for Redgate SQL Compare for the DBA team and me. In addition, we have purchased SQLDBDiff software for our Team Leads and a few developers. These tool sets have been very helpful in determining database differences. However, we have been very cautious and in some cases, hesitant to use these tools to generate our deployment scripts.I’m now in the process of putting together a Best Practices document for writing deployment scripts and am looking for some assistance. I’ve spent a decent amount of time browsing the net and have not had much luck. I’ve found plenty of general SQL best practices but little directly towards writing deployment scripts, which I have found to be very different than basic, day-to-day SQL coding. Here is what I have so far: - Run SQL comparison to determine differences between the two databases
- Create objects in the correct order (ex: table should be created before stored proc that references table)
- Don’t rely on the SysDepends table
- Use CREATE for new objects and ALTER for existing objects
- Don’t drop and recreate objects due to loss of object permissions
- Use ALTER statement to rename objects
- Don’t use sp_rename for procedures, views, triggers or UDF’s. Sp_rename only changes the object name in system tables; it doesn’t change the actual code used to script the object
- Scripts must be approved by DBA
Along with the deployment script, we are also moving towards creating roll-back scripts, which has also been an adventure.With that said, does anyone have any best practices to add to the list I have so far? Does your company use a similar process for deploying database changes? Do you create scripts at the end of development or gradually build the script as changes are made throughout the development life cycle? |
|
|
|
|
|
|
|
Deployment scripting Best Practices - Any input?