Managing database scripts in your solutions

I usually create a solution folder in Visual Studio and put my DB scripts in them. I always use at least this set of scripts:

Drop model
Create model script
User functions
Stored procedures
Static data (lookup tables)
Test data (not deployed)

Then I simply combine them and run against an SQL Server so I'm able to recreate the whole DB in a single step (by combining these scripts into a single one and executing it).

Anyway. I've never used projects in either:

Visual Studio or
SQL Management Studio

I've tried creating SQL Server 2008 Database Project in Visual Studio 2010, but I'm somehow overwhelmed by all the possible server settings (which I prefer to stay default as set on the server anyway). So I'm a bit confused: Should I use this project template or should I just do the same thing I always did?

What do you use and why? What are advantages I may benefit from by using either?

If I were you I would continue to do it the way you are doing it. In fact I do! The advantages of having the actual .sql files right there in a folder for you to use/edit/look at in my opinion are far better than the advantages you get by using a DB project. DB Project would be used if you were doing something like Storage Reports, were you have to communicate with like 8 databases and compare then to 8 different databases and save result sets etc... Now don't get my wrong there are advantages of Database Projects, I just don't think they are actually doing much help when you have such a simple setup that works already.

Advantages of the SQL Server 2008 Database Project in VS10:

Not having to switch back and forth from your current client you use to communicate with your SQL server.
Decent Data and Schema compare tools.
Gives you a one-click way to reverse engineer a database into source control, and keep it up to date.
You can compare projects to physical databases and vice-versa. (This makes it pretty easy to keep your database up to date, no matter where you make change it: file system database project, or in the physical database itself)
If the current tool your using is not specifically tailored to SQL Server, this one is.
Extremely helpful if you need to do unit tests directly on the database without using abstractions.

One thing I lack in my design is robustnes. the DROP script sometimes isn't able to drop the DB so the whole pack of scripts fails. I suppose these **SQL Server Projects** are more robust and are able to **deploy** and **re-deploy** as well without a problem. One other things I've noticed is that I get Code Analysis with these project types that may be helpful. Maybe... And FYI: I'm actually not doing any reporting in terms of this DB project... So you may be as well right.

Robert Koritnik 2010-10-19 17:36:12

I guess what I was trying to say is, if you just need the database to work and its not the core of your application, I would stick to what you are doing. If the database plays a large role in your application, it would be worth to learning database projects. Don't get me wrong SQL Server Projects have a lot of advantages, I guess the question is it worth it for your project.

Jacob Nelson 2010-10-19 18:21:50

That's fine jacob. Could you please provide some of the advantages of using project templates (in your answer) so I won't have to search/check/test them myself and probably miss out a few as well as consume too much time doing that? I'll +1 your answer anyway because you've provided valuable info already.

Robert Koritnik 2010-10-19 18:47:56

Edited my answer. Link is to a question that kind of relates to your question, got some of my advantages from there (haven't used it in a while and needed a refresher myself).

Jacob Nelson 2010-10-19 20:07:12

Thanks jacob...

Robert Koritnik 2010-10-20 08:26:07

Which of my methods do you consider complicated? The one I use now is rather simple, project template requires writing scripts as well. This tool lets you do DB design in SQL Server Management Studio and this tool just creates and stores scripts for you. It just means, you have to use a second tool for DB. In my case I can do both in VS.

Robert Koritnik 2010-10-20 06:54:49

Sorry - I was a little unclear. I was referring to the complexity of the visual studio project generally in response to your comment about being overwhelmed. You make a good point about being able to use your methodology in VS, which I'm guessing is important to you? One advantage of SQL Source Control is its ability to sync from a folder of scripts to a target database. VS integration is something that we have on the SQL Source Control roadmap, but not until next year. The majority of users we speak to spend most of their database development time in SSMS.

David Atkinson 2010-10-20 16:03:34

ansaurus

tags:

views:

answers:

Managing database scripts in your solutions

related questions