Is SQL Server 2005 Express good enough for development

Question

At the company i develop, developers typically run databases on their laptops. We also have an instance of a SQL Server 2005 and SQL Server 2000 servers running as virtual machines, under an MSDN license.

I am not a MSDN guru and not really familiar with the ins-and-outs of MSDN, so the my questions are as follows.

Would each developer be able to install an MSDN version of the SQL Server, for development purposes on his or her laptop, if we have enough subscriptions? In the midst of uncertainty would we be able to instead use SQL Server Express for our development purposes, later migrating the databases to our 'central development' database servers once we have made all the changes to the databases we require? Are there any major differences between SQL Server Express vs SQL Server Enterprise in terms of functionality.

Answer 1

I don't remember being any functional differences between the express version and the full version. There is just data base size limitations and how much it can handle. I could be wrong, but that is what i remember.

Answer 2

You will need "SQL Server Express with Advanced Services" if you want to use full text indexing or reporting services in the application you're developing. Otherwise they're functionally equivalent to full versions.

There are limits on database size, CPU cores used and memory usage, but that shouldn't generally be a problem. The limits for the current version are 4GB disk per database, 1 CPU core, 1GB RAM.

That being said, SQL Server Profiler is a very valuable tool not available in the Express editions. It would be my primary reason for installing the developer edition of SQL Server.

Answer 3

If your MSDN subscription permits I'll recommend that your devs install the developer edition of either SQL 2005 or SQL 2008 as that version includes the SQL profiler which will make it easier for the devs to pinpoint SQL bottlenecks.

Answer 4

SQL Server Express is sufficient for production for many small apps (especially for replacing Access etc).

The real thing is: your developers should use the same (or similar) as you intend to use in production, to avoid any last minute "not supported" / "works differently" surprises.

If you intend deploying to express, then develop against express.

If you intend deploying to enterprise, then develop against, well, developer edition, which is the same thing really.

Size is a factor, too though; even though I deploy against enterprise, I use express on my laptop (when working on the train etc) for space reasons. But I always go back to my main desktop (with more disk/memory, and a VM configured like my production platform) to perform validation etc.

Answer 5

Looking at it from a corporate view only... some thoughts...

Developing against server installs (virtual, real, whatever) means that you're developing against something resembling production or UAT. I think it also encourages best practice and some discipline.

The Express edition has limits that means you can't replicate the data or conditions you have in production. Not one of the databases I work on would run in the Express limits.

If I remove data so I can run locally, then I have no indication of how my 1000 row queries will perform against when I have 200 million in production. And forget abut partitioning, indexed views and other good stuff too.

I've seen many 3rd party apps that require SQL Server to be on the web server, or a shared SQL/app box, or need sysadmin rights to run. To me this always looks no thought was given to the target audience and developed locally.

The advantage of server installs is capacity and performance. How are you going to tune query on your 200 million row/50 GB database, deal with the effects or parallelism etc

Saying that: developer edition can also be installed locally on your laptop.

Finally, in many shops, local SQL Server installs are disallowed. Back in my 100% DBA days, I used to hunt them down...

Answer 6

If your main concern is cost, the buy a Developer Edition for each developer. At $49 (fourty-nine) you get a full fledged enterprise Edition functionality, with tools and SQL Agent and Fulltext Search and SSIS and BI and all the works. Is only restricted for development use by license. And it actually may be cheaper than buying an MSDN subscription or Technet subscription.

Developing against Express Edition can is not feasible if you develop a product that requires in the deployment Standard or Enterprise edition functionality. Your developers will not be able to use some features (SQL Agent jobs, SSIS, BI (OLAP, DM), Report Builder, full Replication, Mirroring and so on), will not be able to test against large loads of data, will not have the management tools (SSMS) at their disposal.