This project has moved. For the latest updates, please go here.

Previous SQL Server Administration SQL Client Tools Next

Development, Test and Production Environments

A fundamental best-practice principle for SQL Server is to use separate servers for Development, Test and Production use. This is to cover both separation of responsibilities governance issues and server stability and performance issues.

If the same server is used for both non-Production and Production use, the organisation risks damage to its reputation if a Production issue is caused by a Development process running on the same server. The functions of each environment are listed below:

Environment Description of Use
Production Reserved for data and applications that serve the Business end-user community
Test End to End regression and integration testing, prior to deployment in Production
Development Development of new and updated databases and applications

It is customary to deploy Development and Test servers as Guest machines in a virtual environment. Licences for non-Production use are available for nominal cost or no cost from both Microsoft and all other responsible vendors. There should therefore be no Business or Technical reasons to run both non-Production and Production on the same server.

Some small organisations can safely combine Development and Test on the same SQL instance by timesharing these functions. However, many organisations will have multiple non-Production environments to meet the requirements of specific aspects of the Development and Test process.

A non-Production environment should not contain the same data as the Production environment. There are a number of reasons for this:
  • Development staff have no Business case to access identifiable personal data. In many countries, legislation prohibits such access.
  • Production data does not contain all the 'edge-cases' that need to be tested prior to deployment.
  • Copies of Production data take more storage space and cost than is required to properly test software before deployment.

All organisations should aim to set up automated testing for new and changed databases and applications. When testing is automated it is repeatable and auditable. This significantly reduces the risk of bugs affecting Production, and when this does happen a new test can be added to the test suite to prevent the problem recurring. Because of this, automated testing significantly reduces the time and cost of making sure that new and changed code meets Production quality standards.

In addition to pre-production automated testing, some organisations will also schedule automated regression testing to regularly run in the Production environment, with alerts being raised if the testing fails. This fills a number of functions:
  • Provide an early alert if the Production system is being affected by outside issues
  • Provide a known sequence of events to assist troubleshooting if an alert is raised
  • Confirm through not hitting problems that the application meets Production quality requirements

Copyright © 2013 - 2014 Edward Vassie. License and Acknowledgements
Previous SQL Server Administration Top SQL Client Tools Next

Last edited Oct 8, 2014 at 10:22 AM by EdVassie, version 2