I have to admit that I don’t use the Project and Solutions feature enough in my day to day work, but I do use it when I put together a blog post or presentation for a SQLSaturday or User Group. By putting all the code for the demos in a Project I don’t have to go searching the file system to find a demo, I don’t have to have the code pre-loaded into SSMS, and I can stay in SSMS throughout the demos. Here’s my project for my Default Trace presentation:
As you can see it provides a single place for all your scripts. You can also add the Project/Solution to your source control product of choice (I don’t have one installed on my personal laptop at this time). I will be working with this feature more to have projects for my database scripts, maintenance scripts, and my favorite queries in addition to using it for presentations and blogs.
I’d love to hear how you are using projects and solutions in SSMS.
I use them A LOT. They need significant work, I know, but they serve the purpose. I also use them in my presentations and I teach how to use them in my college classes.
ReplyDelete- Buck
I use the heck out of Projects / Solutions. Unless it's just a very simple couple of queries I generally store them in a project of some kind. Every time I need to do database changes I store them in a project so I can have change control, and be able to separate the tasks into nice atomic chunks (individual SQL files). I then coordinate everything using a "Master" sql file which I run in sqlcmd mode.
ReplyDeleteI come from a developer background though, have used versions of visual studio way back to Visual Basic 1.0/ and VB MS-DOS, so the paradigm of project/solution is ingrained into me. It just feels natural, so perhaps that's why I use them so much.
I definitely think projects are underused. I know I need to be more proactive in using them.
ReplyDeleteRobert,
I definitely think the developer background makes a difference. Ever thought of doing a presentation on how you use projects? I know I'd be interested in seeing how you use it for change control.
I try to use solutions and projects for all of my SQL work. I believe all your DB objects (procs, functions, views, tables, etc.) should be under source control. Doing development this way is beneficial on so many fronts. The best way I have found is to always do development work from the source file and never do it using the "modify" menu option that scripts it from the DB. Always work from the source file and "compile" changes to the DB.
ReplyDeleteI have the solution represent the DB and then a project for each sql object type. So 1 project for stored procs, one for functions, one for views and so on for each object type. And finally I have a project that is for each developer for them to maintain any of there ad-hoc scripts and such. Do this for each DB. With all these things under version control it gives the developer the benefits derived from using a version control system. It just requires the descipline to use it. It is always worth it when you can view the history and changes things have gone through that help in your day to day development work.
My only issue is that the solution explorer for SSMS is so lacking in functionality as compared to other Visual Studio solution/projects. Even Visual Studio's DB solution/project stink. And the 2 are not compatible. It is as if MS doesn't really want you to use them. But since this is what comes out of the box I use it.
The other thing I have done to go along with this is create a custom scripting application to script out any DB I work on to get it into source control. Creating separate files for each object. Of course this can be done from SSMS, "generate scripts" but some aspects are not very customizable for how scripts are created but in some cases others are. And with SSMS you have to set them every time you do this which can be very cumbersome. The custom scripting app is setup to run with everything already set. So I just run it every so often to check that the developers are updating the source file in the version control systems. This ensures everything gets in there. Although it is a bit of manual work.
ReplyDeletehere is a little tutorial how to do this:
http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/
FYI - The above tutorial I note is only to show how to use SSMS to script out things from the DB. It isn't about the other points I mentioned above. I thought it might be useful to those who haven't used the feature.
ReplyDeleteAdam,
ReplyDeleteThanks for the comments. Great tips and a good way to do it I think. I know that Source Control for DB's is a touchy subject, but one we need to deal with. I definitely need to do more with it.