Visual Studio database projects support database post-deployment scripts you can use to make additional modifications to database or to insert some test data. In my work I use database projects and post-deployment scripts very often. In this posting I will introduce you how to use post-deployment scripts to make dealing with database and data easier for developers.
Life-cycle of post-deployment script
Post-deployment script, like any other thing under development, has its own life-cycle. The following diagram shows how the process goes.
Steps are as follows:
- Developer makes modification to database, by example developer adds new table and some new relations.
- System code is modified to reflect changes in database.
- Modifications are tested to make sure that functionality is not broken.
- Data for new table is added to post-deployment script.
- Database deployment with post-deployment script is run on developer machine to make sure that database deployment works. Of course, it’s only good idea to try also code out after changes to post-deployment script.
Post-deployment script changes mostly with database but there is one exception – over time test data is replaced by real or at least close-to-reality data and after these changes post-deployment script must be tested again.
Post-deployment script must be up to date
If post-deployment script is not updated with database then soon it will live its own life and it gets more and more out from synch with database. As post-deployment script doesn’t work anymore then developers start keeping their current copy of database and make only structural updates there. Soon it turns to fear against database deployments as more and more issues raise when database must be deployed again. And, of course, after every deployment all data must be inserted to database again.
Up to date post-deployment script has some serious benefits:
- Developers are more brave when working with database,
- Deployment and data related errors come out early.
- All developers have up to date version of database and test data – they don’t waste time to fix problems related to some earlier version of database that is deprecated.
- It is easy to deploy database with test data also to other environments like test server, continuous integration server etc.
- Typical problem of customer – do I have to insert this data again after update – is solved once and forever.
What if some guys refuse to use database scripts?
Do they also refuse to write quality code? I mean how they can do their job if the only way to get up to date database is to deploy this database to their development machines?
If they refuse to use last version of database and data then the quality of their code comes down, they spend a lot of time on fighting with newer codebase and their current database incompatibility problems and soon they probably understand that they get badly stuck this way.
Okay, even if they move fast there’s still problem – other team members move way faster with their tasks.
Post-deployment script in practice
I’m using database projects with post-deployment scripts almost in all active projects. Post-deployment script helps me to be sure that developers work on with latest data and if latest data introduces problems then they can focus on solving these problems because these problems have high appearing probability on test and staging environments.
Usual post-deployment script managing activities are these:
- Make sure database deployments with data succeed.
- Solve all database deployment problems ASAP – don’t stop developers work.
- If customer has added new important data to test database then migrate this data to post-deployment script.
These steps doesn’t waste your time a lot and same time you keep development smoothly running.
Getting started
Actually database projects and post-deployment scripts are not something complex and huge. Play with them a little bit and try to use them with some sample application to get better idea how to use them. Of couse, if you have DBA in your team then also tell him what you are planning and find out together how he can support developers work.
In the beginning it’s all new for team – give them some time to get used with new tools and if possible then organize them small in-house training with practical examples. This way you can get also valuable input from developers that makes whole process easier for everybody.
Conclusion
Visual Studio database projects that provide fresh data to developers have worked for me very well. Although it’s not something you can start right on minute it still has a lot of benefits you don’t want to miss. Making life with database and data easier for developers helps them save time for coding and also guys who are responsible for deployments are happy because they can do everything way faster than before.