Development environments are typically an afterthought. Give our developers some data that somewhat resembles our production system and hammer away on it. Far too often these environments are overlooked due to the fact that they aren’t customer facing servers and don’t necessarily hold business critical information. Despite this fact, development servers are incredibly important as they are the key to efficient work and well performing production code. In this article I’d like to discuss some strategies for administering a successful development environment that will cut development time and ensure properly functioning and performing code.
- Refresh your development databases nightly.
Working with out of date data is one of the main culprits in poorly performing stored procedures. As tables get larger and as indexes update, queries can go from adequately performing to dismally performing very quickly. By working with up to date data, you are far more likely to properly design search queries to match the actual spread and size of your production tables. The easiest way to perform this refresh is to simply restore your nightly backup files to your production environment. This gives you a simple way to get up to date data and also gives you another way to test your backup files and disaster recovery planning. The only way to guarantee that your backup files are valid is to restore them, therefore this step will provide you with a valuable piece of your disaster recovery architecture.
- Utilize a version control system.
Putting a development database under version control gives you two things. By forcing a developer to check out a stored procedure before he begins working on it ensures that two developers will not overwrite one another’s work. Version control also gives the developers a recovery point, so to speak, that they can revert back to should a bug arise in a stored procedures. Implementing a version control system is not enough though. Policies should be put in place forcing periodic check in’s of stored procedures to ensure that consistent versioning takes place. A version controlled environment where stored procedures are checked out for weeks at a time is useless. There are several different flavors of version control and many CVS systems are freely available on the web. Implementing version control does not have to be expensive or complicated.
- Performance tune your code throughout the development process.
If you wait until your stored procedures move into a staging environment to performance tune your code, you may end up with problems so large that you end up rebuilding your code completely. By consistently performance tuning your code you can isolate performance issues as they arise and the product that you move into staging will be much less likely exhibit any problems. This development tuning does not have to extremely complicated and can be performed by simply watching profiler and perfmon as the stored procedures run and monitoring things like disk reads and cpu counters. For successful performance tuning, please ensure that you have followed step 1 in this article, that you are running on up to date production information.
By following these three steps you will successfully be able to cut down on development time and cut down on performance and functionality problems as your sql and application code migrates from development to staging and finally into production.
