Ajarn Mark Caldwell Blog

Bringing Business Sense to the IT World…

Separate Development, Testing, and Live Data

“You DO have a separate development environment, don't you?”  I've made a comment like that a few times here on the blog, and to an experienced DBA it seems so obvious, but let's talk about it just to be sure (and for those people just starting their careers).

Here's the gist of the statement:  you don't want to be doing development work in your live (sometimes called production) database because sometimes things go wrong, and that would be bad.  So what do you need?  Well, if you can afford it, separate servers for one or more development environments, one or more testing environments and your live environment would be great.  But even if you're strapped for cash (like the now-defunct dot-com I worked at) you should at least have separate dev/test/live databases on your one server.  Or in SQL 2000 you might want to run multiple instances, although I'm not sure the effect on licensing of that approach.

Okay, so you understand you don't want to mess up your live data by doing dev work there, but why a separate test database?  To test your deployment process, and to make sure your system works with “real” data and not just your dev data.  It's not unusual for a developer to manually manipulate the data in his dev environment in order to specifically test some feature.  I have seen this lead to situations where an application worked fine in the dev environment but when released to a live look-alike (QA) it failed miserably.  I have also seen, on numerous occasions, where a SQL script was left out of the deployment package and the whole upgrade failed.  If you went straight from dev to live, your live system is now down while you scramble to figure out what the missing piece is, and sometimes scripts need to be run in a certain order due to dependencies and now you'll have to restore your live system from backup (you DO backup your database, don't you?  Especially right before you apply updates.  And you've checked to make sure it was good?) and start the deployment process all over again.  If this is done in a test environment, the impact is greatly reduced.  Your test environment should be easily recoverable to a known good version (of both schema/code and actual data).

Legacy Comments


Tara
2004-02-26
re: Separate Development, Testing, and Live Data
You do not need a separate license to run an additional instance. The license covers the default instance and all other named instances installed.

graz
2004-02-26
re: Separate Development, Testing, and Live Data
You do need a license for echo instance of Standard Edition installed. Enterprise Edition and Developer Edition don't need per instance licenses.

Tara
2004-02-26
re: Separate Development, Testing, and Live Data
Damn, I never knew that. I've been working on Enterprise Edition for so long I didn't realize that licensing was different (except for the cost of course).

Karl
2004-02-27
re: Separate Development, Testing, and Live Data
Our database process is a little screwed in my opinion. We have a dev, test, stage and live environment for our database. The lower down you go, the more screwed things always are. We have a single test database, but multiple applications using it. Different developers (I'm one of them) want different things on test. Some want live data so that QA can test fixes to bugs that were reported on live. Some want test data so that they can test new features. Some use test as a staging server. Obviously things get far worse on dev

Most of the problems seems to be keeping everythign in synch. It's often suggested that to fix the dev problem, we use an MSDE instance on each developers machine. However, what if two people edit the same sproc and want it promoted to test/stage/live? One will overwrite the other? How does one developer get latest versions of schema and sproc? Or latest version of data. Or data from a week ago because a bug was reported against specific data.

As a developer, almost all these issues are resolved for our code using some type of source control. We've yet to find a good solution for SQL stuff. Are there any guides or tools that actually work in the real world out there? Something I can give our DBA and hopefully have him implement it??

karl

crazyjoe
2004-02-27
re: Separate Development, Testing, and Live Data
We use source control software for stuff at my company. Admittedly, it's a bit primitive, but to lock down SCHEMA and SP and other code, we check the SQL scripts into the source control, and when they are implemented, we encrypt them so people can't just go and edit the ones in production (or test, or stage), they need to check them out.

Now, your data issues are another matter....with each new instance you add the possibility that someone is coding on an out-of-date database somewhere.

AjarnMark
2004-02-27
re: Separate Development, Testing, and Live Data
Joe, I like your idea of encrypting the sprocs to prevent people from making changes directly in Enterprise Manager and forcing them to use the scripts. Simple, yet effective.

crazyjoe
2004-02-27
re: Separate Development, Testing, and Live Data
Thanks! When we switched to using source control here, the boss said that we would script stuff and put it in PVCS (our source control software). I said that was pointless for SQL, unless you did something additional to prevent people from changing the procs via ALTER commands or through EM.

It doesn't protect tables, though, which could lead to problems. Thankfully right now I am the only DBA so most changes have to come through me anyhow, but its still not optimal.

To accomplish complete source control on the DB by revoking all create, drop, alter, etc permissions and making all changes come through a turnover control area (for development mind you, not QA or staging) would really hobble the developers, so we haven't done that. Hopefully it won't become necessary, but it probably will, and they will do it once I fully transitionito the developer arena and will be forced to beg for updates that I used to be able to do myself :)

AjarnMark
2004-02-27
re: Separate Development, Testing, and Live Data
Yeah, Joe, that's one of the reasons I'm an advocate of refreshing your dev system periodically. You restrict permissions on the QA and Live systems so somebody has had a chance to review scripts and ensure that only scripts are used to change those systems. Then the occasional refresh of Dev will clean out any of those wayward manual updates and if somebody says, "Hey, I needed that." the answer is, "Hey, where's your script?".

essjay007
2004-08-20
re: Separate Development, Testing, and Live Data
Can anyone please give me an idea of what are the major risks of using a single server for dev/test/live environments.

thanks.

AjarnMark
2004-08-20
re: Separate Development, Testing, and Live Data
Well, essjay007, the risks vary depending on whether you're using just a single instance or multiple instances of SQL Server, but basically, the concern is that anything you do in the dev or test environments has the potential to damage your live system. Specifically, anything that might down the server or the SQL service, or issuing a long-running query (like an accidental cross join on large tables) might kill the performance of the live server by sucking up all the resources. Then, of course, there's the risk of hardware issues will take out all of your environments instead of just one.