42

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?
7
  • 2
    From the answers accumulated so far, I suspect the respondents are not using any of the VS2010 database tools in the manner intended. Nov 5, 2011 at 20:49
  • 2
    @MarkStorey-Smith - Yeah, and I will rock everyone next week with my answer. From what I've learned and used so far, VS 2010 is the tool for database development. Nov 6, 2011 at 7:15
  • Actually, you already had database projects in previous Visual Studio versions, but they were only available on the Premium editions, IIRC.
    – gonsalu
    Nov 7, 2011 at 11:29
  • 1
    Previous versions were very different. Nov 8, 2011 at 1:06
  • I use SSMS only. SSMS is fully capable of doing what needs to be done. Our server stuff doesn't need to know what's there...
    – Asken
    Nov 24, 2011 at 12:04

7 Answers 7

27
+100

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.

6
  • Thank you for updating your answer with all this detail. It is much more valuable now. Nov 23, 2011 at 2:38
  • 2
    Disagree on one file per object being cumbersome. It's not the VS2010 way, it's source control 101. You don't define multiple C# classes in a single file, do you? Nov 23, 2011 at 7:32
  • 2
    Honestly, I don't follow. Firstly, the tools manage those files for you, it isn't adding an overhead to my productivity. Secondly, tables, keys, indexes and constraints are separated because a) they are separate objects, logically and physically b) you often manipulate them in isolation e.g. dropping and recreating indexes as part of a refactor that involves data movement. Nov 23, 2011 at 10:34
  • 1
    Sweeping statements like 'the tools manage the files for you' aren't very helpful, as my observation is that plainly they don't - at least not reliably. VS2010 might work OK for a single user; it did not work very well for a team. My experience is that a MS gold partner had trouble managing a simple accounting data mart with this. It wasn't the people. Nov 23, 2011 at 11:30
  • 2
    @ConcernedOfTunbridgeWells please don't view my comments as being antagonistic or argumentative in any way, that isn't my intention. I'm very interested to hear why VS2010 isn't being adopted more widely, as I'm often making a case for teams to explore it. If you can spare the time to discuss in more detail, I'd be interested to hear your thoughts. Nov 23, 2011 at 20:27
19
+50

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.


Why VS2010?

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 the project.

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.
13
  • Kinda useful answer with some caveats: 1) You missed out on a criteria "You are developing standalone applications that are sent to client sites (ie: there are multiple copies of the same database out in the wild and new [empty] ones being created/sold all the time)" 2) You've answered why we should treat a database as code and manage develop, build, deploy cycles (which I already agree with). I don't see a compelling reason in your answer as to why we should use VS2010 as the mechanism to achieve that. +2 (thoughtful answer) & -1 (not answering the actual question) Nov 29, 2011 at 9:12
  • 2
    What "rich IDE" do you need for a SQL script?
    – gbn
    Nov 29, 2011 at 12:24
  • 1
    The benefits of automated build-deploy-test cycles are seen downstream. The automated part of a live deployment I would limit to being "hands-off" i.e. not requiring any manual steps. Nov 29, 2011 at 13:04
  • 1
    Aside from that, your arguments for integration do have some merit. How well does it work in the general case? I've had problems with it, but I dare say it can be made to work. Nov 29, 2011 at 15:59
  • 2
    @Nick I'll do it for you :-) Nov 30, 2011 at 18:52
7

VS may appear great if you don't know SSMS or have used 3rd party tools. The gaps in VS stand out if you use Red Gate tools for schema comparison etc. And the free SSMS plug-ins too.

The source control bits is misleading: what is in the production database is your reference copy. Not what the developer is using. See

3
  • 1
    I'd have to agree with this - You can do DB design/development and schema management with VS2010 but there are third-party tool chains that do this way, way better. Nov 29, 2011 at 15:57
  • 1
    Why would you take production as your reference copy? I think that's a bad practice, and probably also a sign that your source control is broken. Why shouldn't your database code belong to the development life cycle like everything else? Nov 29, 2011 at 17:46
  • @NickChammas: I mean a restored copy of production. In my current shop, what is in source control doesn't match the live DB. In my last, similar for other teams. What is deployed via a change script isn't what developers are working on...
    – gbn
    Nov 29, 2011 at 18:09
6

I use Visual Studio extensively (well, BIDS) for SSRS report design, and SSIS packages. I couldn't do either very well, if at all, in Management Studio. Visual Studio is a much more complete and integrated development environment, and it hooks into Source Control systems much better too. And that's all reflected in the price!

6

To be honest, my vote goes straight to SQL Server Management Studio for database design, development, and (obviously) administration. It is just easier to type out:

create table newTable
(
    someId int identity(1, 1) not null primary key clustered,
    ...... you get the idea
)

Then click in all the right places. SSMS is a great layout and an absolutely blast to work in. And I'm a .NET software developer by nature. But when it comes to database design and coding I'll choose SSMS 11 times out of 10.

5
  • In Visual Studio you type out your table DDL exactly the same way. Are you thinking of something else? Nov 5, 2011 at 1:31
  • 1
    @Nick I probably worded that wrong. I know you can do that in VS, but my thing is that it's nice to have a dedicated tool for that specific (and large) task at hand. I'm definitely a beast of habit, and I've made SSMS my habit. :) Nov 5, 2011 at 1:47
  • 2
    Really? The SSMS solution/project capabilities feel like they were added by an intern 2 weeks before launch. Nov 5, 2011 at 20:50
  • 1
    @MarkStorey-Smith is the question then about how SSMS doesn't really have project/solution support and that's important for letting teams work well in an IDE across the board?
    – jcolebrand
    Nov 10, 2011 at 16:41
  • 3
    A one liner would be that SSMS is a database administration tool, VS2010 is a database development tool. Nov 10, 2011 at 17:19
5

There is no best practice, but with VS 2010 database project and source code controller (VSS 2010, Subversion, etc.) you can version your database.

I recommend that you first design your database directly into SSMS. After your database is almost ready, import it into a VS 2010 database project. Once the import is done, you must always add new script into your database project to keep track of all modifications. You can use the compare feature of database project to get all changes from development server and import all those scripts directly into your project. You now just have to "commit" your change into your source code controller.

With this method, you can have a versioned database. You can spot the version of each modification and roll back your changes.

This is not the only advantage. With this kind of project, you can compare any databases to your project to script the changes and with SQL PowerShell command prompt, you can update all of your databases with the same script: this script is an XML schema of your database. It will execute only the needed commands to update your databases. You also have the possibility to make unit test in your database. An other good article for database project is available here. With the deploy feature, you can have pre-script and post-script. With those scripts you can had validation nor insert data into your system tables.

Here is a good step by step procedure for database project.

3
  • 4
    This is not how database development in VS2010 should be done, you appear to have missed the point somewhat. 1) Why one earth would you design a greenfield database in SSMS then import to VS? 2) You do not have to "always add new script into your database project" to track changes. 3) Scripts are not an "xml schema" of your database. Nov 22, 2011 at 23:33
  • Have you ever try database project? 1 - Because can you can only import your database once so if you don't want to script all do the max you can in SSMS for the first draft of database. 2- You're right you can track changes with the comparer tool of VS2010 and VS2010 will generate it for you. 3- Try database project, when you will deploy your database project you will get an XML Schema of your database to bring your production databases up to date.
    – Nico
    Nov 23, 2011 at 13:17
  • 2
    Yes, since the early and more painful versions. You would indeed import once but you synchronise many (not that you would ever have to do so, unless you continue working outside of the environment). A more accurate description of what you're referring to as an 'XML Schema' script is that the tools maintain an xml based meta-model of the database, which the command line deployment tool uses to create the commands necessary to update a target database. Nov 23, 2011 at 20:24
2

I use SSMS more than I do VS2010 because it is there when I install SQL Server. That is probably the most important thing why SSMS is used more than VS2010, IMHO.

I did also find that vendors like Red-Gate are putting out tools that integrate with SSMS and not necessarily VS2010. This can be positive in that it allows you to improve SSMS where Microsoft has not. One example of this is Red-Gate's SQL Source Control, which is an add-on to SSMS that allows you to connect SSMS up to your company's Source Control system whether it be Visual Team Foundation or what have you. VS2010 has this built-in but compared to the price of Reg-Gate's tool I just saved a bunch of money without having to buy VS2010.

I think overall it comes down to preference, you work with what you are comfortable in. If you are training a new person and you train them on VS2010 then that is going to become their preference because they know how to get around with it.

If you started playing with SQL Server 2012 you may have noticed that SSMS is getting the makeup of VS2010 slowly but surely. So eventually you may not be able to tell the difference between them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.