patternMajor
Why should I use Visual Studio 2010 over SSMS for my database development?
Viewed 0 times
whyssmsvisualdatabaseshoulddevelopmentforstudiouseover
Problem
Visual Studio 2010 introduces database projects and a whole array of related features that supposedly facilitate database development. I've used SQL Server Management Studio (SSMS) for many years to do my database development without issue.
- Why should I bother with VS2010 when SSMS works for me? What, specifically, does it do better than SSMS?
- But perhaps my premise is incorrect and SSMS still trumps VS for database development. If so, in what specific ways is that true?
Solution
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:
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
Context
StackExchange Database Administrators Q#7599, answer score: 27
Revisions (0)
No revisions yet.