I've been toying with how to structure an answer to this question since it was originally posted. This is difficult as the case for VS2010 isn't about describing the features and benefits of the tool. This is about convincing the reader to make a fundamental shift in their approach to database development. Not easy.
There are answers to this question from seasoned database professionals, with a background mix spanning DBA, developer/dba and both OLTP and data warehousing. It's not viable for me to approach this for every facet of database development in one sitting, so I'm going to try and make a case for a particular scenario.
If your project fits these criteria, I think there is a compelling case for VS2010:
- Your team are using VS2010 for application development.
- Your team are using TFS for source control and build management.
- Your database is SQL Server.
- Your team already has, or is interested in, VS automated testing.
If you're evaluating VS2010 for database development, your bible will be the Visual Studio Database Guide from the Visual Studio ALM Rangers. Any quotes that follow which don't have references will be from this document.
Off we go then...
Why is the database development process different from application development?
Data. If it wasn't for that pesky data, database development would be a doddle. We could just DROP everything on every release and forget about this troublesome change management.
The existence of data complicates the database change process because
the data is often migrated, transformed, or reloaded when changes are
introduced by application development efforts that affect the shape of
the database's tables or other data schemas. Throughout this change
process, production quality data and operational state must be
protected against changes that may jeopardize its integrity, value,
and usefulness to the organization.
What's wrong with SSMS?
It's called SQL Server Management Studio for a reason. As a standalone tool, it is impractical to manage your development efforts if you are going to follow accepted best practices.
With scripts alone, to meaningfully apply established source control practices to your development you will have to maintain both object definitions (e.g. a CREATE TABLE script) AND change scripts (e.g. ALTER TABLE) AND work hard at making sure they remain synchronised.
The chain of versions for changes to a table get pretty daft pretty quickly. A very simplistic example:
-- Version 1
CREATE TABLE dbo.Widget (WidgetId INT, Name VARCHAR(20))
-- Version 2
CREATE TABLE dbo.Widget (WidgetId INT, Name VARCHAR(20), Description VARCHAR(50))
-- Version 3
CREATE TABLE dbo.Widget (WidgetId INT, Name VARCHAR(20), Description VARCHAR(100))
By version 3, the database change script contains:
ALTER TABLE dbo.Widget ADD Description VARCHAR(50)
ALTER TABLE dbo.Widget ALTER COLUMN Description VARCHAR(100)
If the live version of this database is version 1 and our next release is version 3, the script below would be all that were needed but instead both ALTER statements will be executed.
ALTER TABLE dbo.Widget ADD Description VARCHAR(100)
5 years of 4 weeks sprints adds up to some entertaining version scripts and requires additional man-handling to minimise the impact on deployment time.
So is there anything wrong with SSMS? No, it's good for what its good for, administering and managing SQL Server. What it does not even pretend to do is assist a database developer with the (at times) very complex task of managing change.
If you are can't build every version of the database from source and upgrade to to any future version, your source control is broken. If you don't think so, ask Eric Sink.
What about SSMS + <--insert schema comparison tool-->?
This is definitely a step in the right direction and takes away much of the manual effort required to maintain scripts. But (and it's a big but), typically there are manual steps involved.
The popular schema comparison tools can be fully automated and integrated in to the build process. However, in my experience the more common practice is for a comparison to be run manually, the resulting scripts eyeballed, checked in to source control, then executed manually to deploy. Not good.
Anytime we fallible humans have to get involved in the process of build or deployment we introduce risk and we make the process non-repeatable.
If you and your team are among the few that have fully automated schema comparison and deployment, hats off to you! You guys are the most likely to be open to the benefits that VS2010 has to offer as:
- You've already accepted that manual steps are dangerous.
- You see the value of automation.
- You're willing to invest the time necessary to make it work.
If you cannot create a build in a single step, or deploy in a single step, your development and deployment process is broken. If you don’t think so, ask Joel Spolsky.
The question @NickChammas posed is looking for killer features that demonstrate why VS2010 is a game changer for database development. I don't think I can make the case on that basis.
Perhaps comically, where others see flaws in this tool I see strong reasons for adoption:
- You will have to change your approach.
- You and your team will be forced to work in a different way.
- You will have to evaluate the impact of every change, at length, in detail.
- You will be guided toward making every change idempotent.
If you are the sole DBA on a project, managing all changes to the database, this reasoning must sound ridiculous bordering on absurd. If however you think @BrentOzar has a point and one of the new rules is that Everybody's the DBA, you're going to need to control database change in a way every developer on the team can work with.
Adoption of Database Projects may require a mental shift (old habits
are hard to break) for some developers or at least a change in process
or workflows. Developers who have developed database applications
where the production database represents the current version of the
database will need to adopt a source code based approach where source
code becomes the vehicle to which change is made to databases. In
Visual Studio Database Projects, the project and the source code is
the "One Version of the Truth" for the database schema and is managed
using SCM workflows likely already being used by the developer or
organization for other portions of their application stack. For the
data, the production database remains its "One Version of the Truth"
as it should be.
We’ve arrived at the fundamental shift that's necessary to successfully adopt the VS2010 approach to database development…
Treat your database as code
No more changing the live database. Every database change will follow the same pattern as an application change. Modify source, build, deploy. This isn't a temporary change of direction from Microsoft, this is the future for SQL Server. Database as code is here to stay.
The database developer defines the shape of the object for the version
of the application, not how to mutate the existing object in the
database engine to the desired shape. You may be asking yourself: How
does this get deployed against a database that already contains the
customer table? This is where the deployment engine comes in to play.
As mentioned previously, the deployment engine will take the compiled
version of your schema and compare it against a database deployment
target. The differencing engine will produce the necessary scripts to
update the target schema to match the version you are deploying from
Yes there are other tools which follow a similar pattern and for projects outside the constraints I placed on my answer, they are worth equal consideration. But, if you are working with the Visual Studio and Team Foundation Server ALM, I don't think they can compete.
What's wrong with VS2010 database projects?
- They are not perfect. But if you are aware of the limitations and problem areas, you can workaround them.
- Complex data movements still require care and attention but are catered for with pre/post deployment scripts.
Edit: So what's your point then?
@AndrewBickerton mentioned in a comment that I've not answered the original question so I'll try and summarise "Why should I use Visual Studio 2010 over SSMS for my database development?" here.
- SSMS is not a database development tool. Yes you can develop TSQL with SSMS but it does not provide the rich IDE features of VS2010.
- VS2010 provides a framework to treat your database as code.
- VS2010 brings static code analysis to your database code.
- VS2010 provides you with the tools to fully automate the build-deploy-test cycle.
- SQL2012 and Visual Studio vNext extend the capabilities of database projects. Get acquainted with VS2010 now and you've got a head start on the next generation database development tools.