Use Visual Studio 2010 over SSMS for the database development

Actually I was a bit underwhelmed with VS2010, to be honest. I think an old-school create table script and files for stored procedures are easier to work with. If you need schema management then you can get Redgate SQL Compare Pro for a few hundred dollars.

If you really need a database modelling tool then Powerdesigner or even Erwin does a much better job, although they're not particularly cheap.

Although I prefer SSMS I've used both. Some pros and cons:

• SSMS has a user interface that 'just works' well for SQL development. Just building and using creation scripts is much more convenient than the hoops VS2010 forces you to jump through. Much, much more flexible (+ SSMS).

• VS2010 has basic schema management (i.e. difference/patch script generation) (+ VS2010). However it's not all that good and has some flaws. For example it matches constraints on name. If you have check or default constraints on a column without naming them, SQL Server generates a random name behind the scenes. This will confuse VS2010 if you install the script on a different machine as the constraints may have different names. Redgate is cheaper and better. (+ VS2010, but flawed).

• VS2010 is really clumsy - you need to have one file for each table or other DB object. Essentially you have to do things the VS2010 way, which is quite cumbersome. (- VS2010)

• VS2010 Is also somewhat fragile and source control integration is flaky. Even on a simple database every table, constraint, stored procedure, index and other database object is its own file. Files get added to the project all the time, much faster than a typical programming project with (say) C#. With optimistic concurrency it has a tendency to silently drop files from the project as check-ins get out of sync. Even with good team discipline the feedback from the UI about status is very poor. This would be a disaster on a complex model. (-VS2010 - I'd almost consider that a show-stopping flaw for a large project).

• SSMS comes with SQL Server - can't beat the price (+ SSMS).

• VS2010 still does not have a proper repository like (say) PowerDesigner or Oracle Designer. You can't easily query the data model without installing it into a database. (- VS2010).

On the whole, I would rate VS2010 about a B-. It was clumsy on a relatively simple database project with two fact tables and around 15 dimensions.

The largest data model I ever did was for a court case management system, which had around 560 tables. I would not recommend VS2010 for a project that size (I did that on Oracle Designer). Essentially it's trying to be clever and the paradigm doesn't really work all that well. You're better off with a best-of-breed modelling tool like PowerDesigner or just using create table scripts by hand.

SSMS is simple and reliable but manual. You have pretty much infinite control over how you want to manage the model. Combine it with a schema manager such as Redgate SQL Compare and maybe a decent modelling tool such as PowerDesigner and you have a much better package than VS2010.

Summary I'm not sure I could quote any killer features or benefits apart from (perhaps) integration with VS solutions containing other projects. If you already have VS2010 premium or ultimate then you get a somewhat flawed database development tool thrown in with your .Net tool chain. It will integrate DB projects into your VS solution, so you can use it to make sproc deployment scripts at least.

However, VS has no modelling tool to speak of, so PowerDesigner or even Erwin is better on that count. Redgate's schema management is much better and SQL Compare Pro is quite cheap (about £400 IIRC). IMHO SSMS works much better for T-SQL development but you can certainly do it with VS2010.

VS2010 premium isn't much cheaper than a best-of-breed database modelling tool and VS2010 ultimate is at least as expensive. At the expense of tight integration with your VS project you could probably do better with third party tools.

An alternative

I guess one shouldn't slag off VS2010 too much without suggesting at least one alternative and outlining its pros and cons. For the purposes of this I'll assume a large project. Although I mainly do A/P work these days I have been involved in a 100+ staff year project where I did the data model (and some development work) and a couple of others in the 10 staff-year range, where I mainly worked as an analyst or a developer. Mostly I work on data warehouse systems these days but the larger projects were mainly applications. Based on my experiences with various tools, here are some suggestions for an alternative tool chain:

• VS2010 professional or higher. You may or may not want to use project management features of premium or ultimate.
• Subversion, AnkhSVN and TortoiseSVN - Better than TFS any day and plays nicely with VS. It's also quite amenable to farming off local repositories for concurrent development workstreams.
• SSMS for T-SQL development - Project management and SC integration not so good but works well for DB development work.
• VS2010 DB project for tracking sproc files - slightly clumsy if you're using SSMS but works OK. It will also generate deployment scripts.
• PowerDesigner - Way better at modelling a database and managing DB schema items. It also does UML if you want to get heavily into MDA. If you want to drive your DB design from an object model you might consider Sparx EA instead. It does the best job of Meta CASE (extensible meta model) of any CASE tool I've seen, although its database modelling leaves something to be desired.
• SQL Compare pro - Use this to generate DB patch scripts or to test manual patch scripts (see 1 below).
• Framemaker - Much more stable and better groupware features than Word if you have multiple analysts working on a spec. It also supports conditional inclusion, so you can have versioned releases of a spec with WIP changes hidden off. MIF and MML make it fairly easy to integrate API docs and data dictionaries into the spec documents. It's quite useful to do this as you can then cross-reference them in the spec. Textual label anchors make cross-references stable across re-imports. You can also use TCS to single-source the document to PDF, HTML and CHM output.
• Open-source issue tracker - Many good open-source ones (e.g. TRAC, Bugzilla to name a couple I've used). Open-source ones are easier to modify or integrate into a custom workflow and you can't beat the price.
• NUnit or other testing tools - whatever automated testing tools are most appropriate to your requirements.
• Anything but MS project - Considered harmful. MS project is very inward looking and forces project plans into a model that does not effectively represent uncertainty, risk or dependencies on stakeholders or other third parties (see 2 below).

Pros: Better database modelling and schema management than VS2010, better version control system, easier to customise the build and project work flow, better management of specs and documentation.

Cons: More effort to integrate tools, limited DB integration into build process.

Assumptions: Assumes that control of change/release process in more important than automated or tightly integrated release management for DB schemas. Also assumes that automated DB schema management is not 100% reliable.

Not terribly high tech or slickly integrated, but for a complex project you're probably more interested in control than cute automated features. I'd argue that you're better off with a set of best of breed tools and whatever home brew build and test scripting is necessary to integrate them. Just try to keep the build (a) relatively simple to understand and (b) fully automated.

1. QA on DB patches. On a large schema you may want to have a manual patching process for live systems, particularly if the patches involve data migration. For example, it may be desirable to have roll-forward and roll-back scripts to support backing out a change if necessary. In this case you will want to have a facility to test that the patch actually works properly. If you manage the database schema in a repository you can test the scripts by setting up before databases and generating a reference database from the repository. Running the patch script on the before database should synchronise it with the repositiry model. A schema compare tool can be used to test this.

2. I've done a lot more data warehouse and integration work than bespoke application development lately, so I encounter this more often than a development team will in most cases. However, project management tools and methodologies do a very poor job of managing external stakeholders. In an integration project such as a data warehouse I really want to see a project management tool that really pushes external dependencies (i.e. ones I don't control) in the face of programme management. On any non-trivial integration project the external dependencies are by far the biggest drivers of wasted time.