In cases when a database change needs to be made there are some good rules that when used can save you a lot of time down the road.
- Run changes via scripts –
- Scripts add for precise execution and help remove human error when it comes to typos.
- When creating scripts use a naming convention to indicate order of code execution, this will help ensure that objects are created in the correct order.
- Roll Back Scripts should accompany roll scripts.
I like to number my scripts using two digits, and leaving gaps in numeric sequence, for example:
10 – MyTableChanges.sql
20 – MyProcedureChanges.sql
30 – MyViewChanges.sql
Then I will have roll back scripts formatted with a special character as a prefix.
~10 – MyTableChanges.sql
~20 – MyProcedureChanges.sql
~30 – MyViewChanges.sql
- Backup before and after release
- Any time a changes is made to the database it is always a good idea to run a backup before the change and then again after the change. If using Enterprise Edition database snapshots are a great option here for a quick backup.
- Keep a history of the scripts run and the results (Source Control is a great option if available)
- Keeping a history of the scripts can provide options when trouble shooting if issues arise.
- Scripts should be run in a test environment before deployment.
- Scripts and all other database changes should be tested before rolled to a production server.
- Considerations for replication should be given before making any table changes.
- Is the table replicated, if so is the replication configured to allow for DDL changes to be replicated. If so consideration should be given to rebuilding the replication.
- Know how much data will be adjusted during the change, if there are a large number of updates that will occur because of the change consideration should be given to rebuilding the replication.
- Test plan to verify changes have been made.
- Once changes have been made, post release testing should be completed to ensure desired results were achieved.
There are many other considerations, such as change control or object naming conventions that are also consider best practice, however following these simple recommendations you could save a lot of time and avoid unneeded frustration.