Should we use Visual Studio 2010 for all SQL Server Database Development?

Question

Our company currently has seven dedicated SQL Server 2008 servers each running an average of 10 databases.

All databases have many stored procedures and UDFs that commonly reference other databases both on the same server and also across linked servers.

We currently use SSMS for all database related administration and development but we have recently purchased Visual Studio 2010 primarily for ongoing C# WinForms and ASP.NET development.

I have used VS2010 to perform schema comparisons when rolling out changes from a development server into production and I'm finding it great for this task.

We would like to consider using VS2010 for all database development going forward but as far as I understand, we would have to set up ALL databases as projects because of the dependencies on linked servers etc.

My question is, do you have any experience using VS2010 for database development in a similar environment? Is it easy to use in tandem with SSMS or is it a one way street once VS2010 projects have been set up for all databases?

Can you make any recommendations/impart any experience with a similar scenario?

Thanks,

Luke

Answer 1

I don't see any reason why you couldn't use SSMS in tandom with VS 2010 - actually, for some operations like CREATE DATABASE, you'll have to - you cannot do that from VS.

VS is probably a pretty good database dev environment for 60-80% of your cases - but I doubt you'll be able to completely forget about SSMS. But again: the Visual Studio database projects are really nothing other than collections of SQL scripts - so I don't see why anyone would say you have to "go all the way" with VS if you start using it that way..... it just executes SQL scripts against a SQL database - you can and might need to still use SQL Server Management Studio for some more advanced tasks.....