Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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.
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:
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.
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.
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.
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:
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...
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.
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.
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:
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:
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.
@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.
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
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!
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.
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.
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.
