Development, Test and Production Environments

Previous SQL Server Deployment Architecture	Scale Up and Scale Out Next

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.

A description of each of the basic environments is given below, although many organisations will decide they need aditional environments:

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

Technical Notes

It is customary to deploy all 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.

Usage Notes

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. Case law has also shown that any regulatory failings are punished more severely if standard best practice processes have not been followed.

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 FineBuild Team © 2013 - 2021. License and Acknowledgements

Previous SQL Server Deployment Architecture	Top	Scale Up and Scale Out Next