SQL Server Test Environment Benefits

By: Graham Okely   |   Comments   |   Related: > Testing

Problem

You keep rolling back the same SQL Server production change. Why?  Are you using your test environment properly?

Solution

Rolling back the same production change means the test or pre-production system is not being used effectively.  In this tip we will look at some reasons why a proper test environment is helpful along with how to log information about testing.

The SQL Server Test Environment

Get value from your investment in the test environment. Sometimes it is called the pre-production environment. But whatever you name it, it is the place to do your testing. And importantly, test before you deploy your change into your production or live environment.

Creating a Test Environment

If you don't already have a test environment for testing changes before moving to production, then you need to invest the time and resources to build a proper test environment.

If you do have a test environment, know that technically capable people provisioned and configured the test environment for you. It uses up IT resources like computer processing power, disk space, backup space, networking capability and security resources. Technicians have copied production data, sometimes entire computers, databases and devices and made them like production systems.  It is important to recognize a lot of time has been spent making the test or pre-production environment available to you and the vendor. People have put in effort to provide you with a test environment, a safe area to test your patches, code ideas and configuration changes. Once you have tested your change in the test environment then you are ready to promote it to production.

Refreshing the Test Environment

Refreshing the test or pre-production environment with production data should be as automated as possible. See tip 2842 by K. Brian Kelley on automated restores.  Your test or pre-production environment is not a code branch. A place where you test several coding ideas at once. Your development and other environments are for code branches. Your test or pre-production environment should reflect your production environment. It is the place to do the final testing of the code and configuration you are promoting.

Tip: test your test environment - How close is your test or pre-production to production? Automate the evaluation of that test system. Work out some important measures. Then automate measuring them and have a regular email sent to you telling you a score out of 10 for the similarity of test to production.

What is your Testing Process?

Have you automated the test process in the test environment? Are you performing code reviews? Even if some code passes your automated tests a code review can help. A fresh pair of eyes across the code might help.

The test or pre-production environment is the place to work out deployment processes as well. You should know what objects will be changed or impacted and how long it will take to deploy your change into production by using that test or pre-production environment. Sometimes there are unknowns and so the time you request to deploy your change should be increased by a safe amount based on your experiences in the process.

Once your code or configuration passes acceptance testing then you migrate it to production.

The SQL Server Production Environment

The production or live environment should be delivering value to your customers. Even if your production environment is not live yet you are requesting your DBAs and technical staff to work in a production environment. Your change uses their time and causes work to be performed in an environment where the impact of errors is much higher that the test and pre-production environment.

The production environment is not a place to test things. The production environment should be for client use so that your business income is produced.

If you promote your change from the test environment into the production environment too soon you run risks. Firstly, you can break the production environment and cause an outage. Requiring more staff time to roll back the change. Secondly, you can impact other production systems near to the system you are changing. If you only discover faults with your change in the production environment it means your test environment needs to be refreshed or you need to rethink your testing processes.

With well-planned, ITIL compliant changes you should be able to roll back the change. But rolling back the change is a red flag, a sign you did not test your change thoroughly enough. During the rollback you use resources again, staff, backup systems, disk space and more down time of the system.

Getting Value from Test Environment

Are you getting value for money from your test environment?  If not, why not? Investigate blockages and reasons your test, or pre-production environment is not serving its purpose.

Here are some things to think about:

• Are development tasks being done in test? If so, then move them back to the development environment.
• Do you have a development environment? If not, invest in building one.
• Are you refreshing the test environment automatically? Build a process that provides the same results each time and that can be automated.
• Do you have an evaluation method for confirming test is like production? Build scripts that compare objects, rows counts, etc.
• Do you know how many days old the data is in your test system? Build an alert to check and let you know how old the environment is.
• Does the supporting infrastructure have a similar processing power and SQL Server versions?  Testing on different level hardware and configurations can lead to different results.
• Do you have some typical workloads that you can run in the test environment? Are they automated? Build scripts that you can use over and over again instead of doing things manually.
• Do they report back to you a summary of the success and failures?  Make sure the results give you concrete information that can help you make decisions.
• Do you have a summary dashboard in the test environment? This is a good way to get a quick lay of the land to know the test environment is where it should be.
• Do you have a test version of the security model in the test environment?  Security is a key part of the database environment and often an issue when code is deployed to production.
• Are test accounts reapplied after a database refresh?  Again, build automated scripts to do the work for you.

Considerations on code development

• Can you build alter statements on database objects as an ALTER statement allows for permissions on that object to say in place, where to drop and create an object means the permissions require reapplying.
• Do you write your INSERTs specifying field names? It is good practice and greatly helps code reviews.

Logging and a Watch System

Do you have a logging or watch system in place in test and production?  Is the data going into SQL Server database tables? Are you reviewing the test logs prior to a deployment to production?  These are all good questions and having a system that logs changes is helpful in case there are issues.

A simple logging system

I try to use the simplest logging system I can. Inserting a row into a table is pretty normal and usual for a DBA and querying a log table is straight forward and can be automated via SSRS systems.

You can make this as simple or as elaborate as you want, but the key is to actually log the changes when they occur.

Here is a sample table structure. 

CREATE TABLE [dbo].[Log]
(  [Date_Stamp] [datetime2](7) NULL, -- The date the step was recorded
   [Instance] [nvarchar](128) NULL,  -- The instance hosting the database
   [runid] [int] NULL,               -- the test run number
   [step] [int] NULL,                -- The step in the process
   [Message] [nvarchar](max) NULL    -– The database name and everything else
)

To record the changes, I do a simple insert to add data to the log table.

insert into [dbo].[Log] ([Date_Stamp], [Instance], [runid], [step], [Message])
select 
   getdate(), 
   'TEST\APP',
   2, 
   (select max(isnull(step,0)+1) from [dbo].[Log]),
   'Stored procedure xxx was changed to do ...'

Note the step inserts an incremental number each time it runs, that is what this code does.

(select max(isnull(step,0)+1) from [dbo].[Log])

I can then run the logging code as is or in a stored procedure or whatever process works best.

Summary

• Use your test or pre-production environment to get value from your investments.
• Test your changes in your test environment before moving to production and keep your bugs out of production.
• Keep your logging simple and you will be more likely to use it more.
• Review your logs before deploying your change to production.

Next Steps

• If you need to mask sensitive data in non-production environments see this tip by Jeremy Kadlec.
• A technical process for logging SQL Server stored procedure use by Aaron Bertrand
• A more technical tip on Windows Event Log by Sadequl Hussain
• A tip by Jeffrey Yao on multiple restores.
• How to clone a database tip by Ahmad Yaseen.

About the author

Graham Okely is a SQL Server DBA and has been working with database systems since 1984 and has been specializing in SQL Server since 2007.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips