Visual Studio 2010 database projects can help you. No, seriously.
Over the past year or so I’ve been using VS 2010 database projects off and on to deal with SQL Server 2008 schema management. I can’t honestly say that it’s been a painless transition, and convincing other developers to use it is, well, as close to a fools errand as you can get. That being said, there are a lot of great reasons to use these things.
In this post I’ll go into some of the reasons you should use database projects.
First of all, what the heck are they?
VS 2010 database projects attempt to bring in everything that is in SQL Server Management Studio into Visual Studio and create a fully integrated SQL development experience. You can create a database project and pull in an existing database, or you can tee one up from scratch and use Visual Studio to create everything you would normally do in SSMS.
Either way, the result is all of your SQL objects are sitting on your file system, nicely integrated into a convenient Visual Studio project that exists within a Visual Studio solution. It’s a pretty decent setup, really. Take a look at the screen shot below:
Showing the stored procedures list in the database project solution explorer.
As you can see, everything is laid out in the solution explorer in a fairly straight forward structure. Each schema has it’s own folder, and all tables, views, procedures, UDFs, etc end up within the schema folder. With only a few exceptions, each SQL object will have it’s own separate file on the file system. This particular database project was populated by importing a SharePoint 2007 content database, and low and behold is riddled with build warnings … 🙂
What I’ve found most useful is to import existing databases into Visual Studio. Setting up a new database in Visual Studio from scratch is a bit too clunky. In all honesty: you’ll probably spend most of your time editing your database in SSMS, then pulling your changes into the database project.
So why then use two tools (SSMS and VS)? Well, there are some really great features built in to the Visual Studio database project that make a fairly compelling case for using them along side SSMS. Some of my favorites are below.
Why Use Database Projects?
I don’t suppose “because I told you to” is good enough?
One of my favorite features is build-time checking and validation of the SQL objects in your solution. For example: if a stored procedure is calling a view that no longer exists, the compiler will tell you.
Here’s a super simple example:
VS data projects will compile your SQL objects and check for errors. This is an example of a datatype error. Adding this type to your project will remove the error.
The compiler checks for quite a lot of the every day SQL errors you’ll run into when developing SQL schemas:
- References to missing tables, views, UDFs, columns and the like
- Cross database references (these are frowned upon)
- Functions with missing/incorrect parameters
The compiler doesn’t check for everything, though. For example, let’s say you have a procedure that inserts a new record into a table, the values of which come from several procedure parameters. If the parameter is improperly typed, the compiler won’t necessarily catch this.
Additionally, if there is a procedure that references a #TEMP table in a procedure that is created by another, a boat load of build warnings pop up. This is difficult to factor out when building your solution. Case in point:
A ton of warnings will come up if this #TABLE ref is not created in the procedure.
Incidentally: even if you choose not to use database projects in the future, this is a super way to validate your existing schemas. How many views and procedures reference tables/columns or other objects in your database that no longer exist? Import your schema into a new Visual Studio project and find out. You might be surprised at what you find.
Schema Comparison: FINALLY
Yes, there is now a schema comparison tool built in to Visual Studio. Wait … don’t get too excited. While it does it’s job well enough, it doesn’t hold a candle to Red Gate’s SQL Compare. Here’s the real cost/benefit analysis for you: do you have licenses to Visual Studio edition that includes database projects? Then VS schema compare is free. Red Gate, however, will still set you back another $500. Don’t get me wrong, they’ll happily take your money. And given Red Gate’s stellar usability, I wouldn’t blame you for whining to the pointy haired boss about purchasing a license, anyway.
You can find this tool under the Data menu. It works about the same as Red Gate’s comparison tool or any other: you compare one database to another, see what’s different, and it can manage the upgrade/downgrade scripts auto-magically. Or you can tell it to generate an upgrade script that can be stored in the ‘scripts’ folder of your project. Some additional cool things you can do:
- Compare Data Project to Data Project.
- Compare Data Project to Database, and vise versa.
- Set a plethora (yes, I said plethora) of comparison options.
- Choose which SQL objects to ignore. I commonly ignore Users, SQL files and Database Options.
Here’s a screen cap of the comparison tool:
The schema comparison tool is comparing two completely unrelated databases. Typically not something you’ll do, but it shows a lot of differences.
Source Control Your Database Along With Your Solution, Or Else
Is your database schema checked in to source control? No? Well now you have one less excuse. Creating a database project in your solution and keeping it up to date practically gives schema source control away for free, especially if you’re using TFS. If you’re using SVN, you could use AnkhSVN (though I haven’t tried it myself), and Tortoise SVN works as well. As mentioned before, you can always shell out cash money for other solutions … *cough* Red Gate *cough*. But why?
As previously mentioned, you’re probably going to continue to use SSMS to do most of your database editing. This has worked for me in the past, and I’ve utilized the Schema Comparison tool to pull changes back in to the database project with relative ease.
If you’re using SVN, here’s a tip: SVN ignore the following file extensions and folders:
- sql and obj folders: These are analogous to the bin and obj folders of any other code solution in VS.
- .dbmldb: This is a file used to cache the database model for build performance. It gets big, and is a per-user file. Don’t check it in.
- .schemaview: Again, this is a per-user file and shouldn’t be checked in. It’s used by the schema viewer tool.
I love this tool. It’s arguably one of the most useful feautres. It’s similar to the Solution Explorer, though it’s strictly for browsing your database. It includes a lot of sweet little tools, like this ‘View Dependencies’ option:
Want to view what objects are dependent on a view? Open Schema Viewer, right click and hit View Dependencies. Done deal.
Click on this little gem, and you’ll get a list of all objects that depend on the “Docs” view:
This lists all objects that reference the “Docs” view.
There is also another folder that shows all of the objects that are referenced by the Docs view. Some of the usual caveats with this tool: if you have build errors, it’s possible the objects with errors fall out of the referenced by section. Also, dependencies in dynamically generated SQL will not show up here. All in all, the view dependencies is an excellent tool that can help you with refactoring your database or just help you trace down the dependency tree when debugging.
Speaking of refactoring:
Need to rename a SQL object? No problem. Right click in the schema viewer, go to Refactor, and hit Rename.
This is the good stuff. I particularly like “Rename” (which I do all of the time), and “Move to Schema”, which as you work on a database over the course of a year comes in super handy.
While I’ve only scratched the surface on database projects, its easy to see some of the gains to be had by using them. I haven’t gotten into some of the issues, however, and will post on them at some point. In the mean time, if you haven’t already, I encourage you to test one out.