What's the best SQL Server setup for a development environment?

Question

Hi,

We are a small team using Visual Studio 2008 for development and SQL Server 2008 Standard in the production servers. We are seting up a new development lab and I am wondering about SQL Server versions/editions and configuration.

Visual Studio 2008 comes with a development license of SQL Server 2005, but since we are only targetting SQL Server 2008 for production, I don't think we should install SQL Server 2005 on the development workstations, right? Would you suggest skipping the bundled SQL Server 2005 and going for, perhaps, SQL Server 2008 Developer? Or maybe SQL Server 2008 Express?

Or would you recommend not installing SQL Server on the development workstations at all, and doing all the development against a development server?

Your views are appreciated. Thanks!

Answer 1

SQL Server 2008 (one of the server editions) on a server

I've found it enforces some discipline in SQL Server security etc because you aren't running in local "god" mode. However, I'm a developer DBA so I have a different take on DB development to more client focused developers.

I'd also definitely use same version and service pack end to end. It's madness not to, frankly.

Edit:

• SQL Express is limited in some ways, eg CPU, memory, database size etc. If you're writing a query,you want to make sure it will run in production on 10 million rows that you can't support locally

• A non-prod server side SQL Server install can be dirt cheap, depends on your licensing model. Developer edition has the advantage that it can run on client OSes: I've changed the first line.

Answer 2

I'm yet to find any issues using SQL Express 2008 for development and would recommend that fully. Definitely stick with the same version as your live system though, or you could be asking for niggly bugs on live that you are unable to replicate on test. :)

Individual workstation installs are my preference. If you have a central server as a DB, it would mean that breaking changes to the DB are trickier to make when working as part of a team. If this isn't an issue - then a central server could still be OK.

Answer 3

I would choose Sql Server 2008 Express on local machines. The reason I would prefer that over Developer is that the Developer edition has the same functionality as Enterprise edition, and so it would be easy to build something in the development lab that you suddenly find yourself unable to deploy because that feature was cut from Standard.

Then, I would also have staging servers with a Sql Server 2008 Standard, that will match your production servers as closely as possible.

Answer 4

If you have the option of having a development server with a separate schema for each developer, that would probably be the best option. Always keep your development environment as close to your production environment as possible to keep bug reproduction as simple as possible.

Answer 5

What is your production running? Express, Enterpris, Standard?

Your development server should be Express if production is Express, Developer if production is non-Express. The idea is that there are differences between the editions, in terms of how certain features are available. Eg. you may turn on PAGE compression in development and conclude there is no data storage space issue, but the production runs Standard edition that does not support page compression. Or your queries benefit from indexed viewes, but again the production runs Standard edition, that does not support it. Or the opposite, your development spends time and effort to optimize a query on the development Express servers, but the produciton is running Enterprise and can use a simple indexed view to solve the issue.

The biggest set of differences is between Express and other editions. What you need, in the end, depends mostly on the type of product(s) you develop and the kind of features you'll be leveraging in your product.

Also you should consider the need to set up repro scenarios in test/development for problems reported by production.

Answer 6

I'm going to recommend SQL Server 2008 Express like many of the other answers. I will, however, make two additional recommendations:

• Keep your DB schema in version control. Developers will be able to easily apply schema updates from other developers to the development databases running on their machine. This would be done whenever you check out your source code to make sure you're running the latest DB schema.

• Add a staging server in the middle that exactly mirrors your production environment.

Answer 7

One per developer. If for no other reason than it makes sure that you have schema management that works (we do which is one of the reasons that for my team per developer tends not to be an issue).

Ok, if the schema never changes or if the developers are never responsible for changing the schema then maybe a central server will work. Providing they don't then break the content because they didn't quite get that change right... not as if they'd ever want to roll the database back and try something again is it?

Yes, you still need a dev database (ideally that matches your live system) on a dev server for testing and other qa and quite possibly a demo instance to go with the live instance but my experience, for what little its worth, is that everyone's life is easier with their own database.

Answer 8

Thanks gbn and all. From your answers it seems that I really need to use the same versions in production and development, so I am dropping the bundled SQL Server 2005 that comes with Visual Studio 2008.

I also like Joel Coehoorn's comment that Express should be better than Developer for development workstations, given that our production environment is Standard and Developer has a larger feature set. Thanks for pointing this out.

Finally, a few answers seem to point in the direction of using a development database server that mirrors the production environment in addition to the development workstations. This is reasonable. I think we will do that.

Many thanks all. I wish I could vote up your answers, but I've just joined and can't do that until I get more reputation points!

Answer 9

I think a development server is the way to go, one set up exactly like the production server. Using local databases tends to create unnecessary problems. First, they frequently only have a small subset of data and thus programmers may write bad code which will never run on prod due to timeouts. Second, I think it is harder to keep all the local databases exactly the same and thus you may get problems where the right hand didn't know what the left hand was doing. Third, there are times when the environment itself creates an issue, but writing code on a local box, you will only run into these issues when you move to the prod server which is a very bad time to find them.