I've got a few topics I'd like to go over:
1. Our SSIS Package design/infrastructure.
2. Database Projects in Microsoft Visual Studio.
But I think I'll go with #2. Database Projects in Microsoft Visual Studio. Since you looked at the title before reading this, I'm sure you felt a little prophetic and instinctively knew I would choose #2. Perhaps I should mix things up a bit and go with #1 just to kill your buzz... Cue Debbie Downer music.
SQL Server 2008 Database Projects in Microsoft Visual Studio 2010 are great. Maybe I should have said that with a bit more umph, or at least used a better word than "great", like "wonderful" or "magnificent", but I think great will suffice. It's not like it does my job for me, but it does make my job a lot easier.
So what's the point of Database Projects? Why do I want to migrate from something that I know works, like maintaining change scripts between releases, to something that is new to me? And my answer is, forcefully, because you don't have to maintain change scripts between releases. Did you get that? You don't have to maintain change scripts between releases. I feel like I should say it again, because I know you are still 4 sentences behind thinking, "why change what I've been doing?". I need to blog about change sometime. Why do people hate change so much. I digress..
If you've finally heard me, you are probably skeptical. "You mean I don't have to meticulously add alter statements to a release script and hope everyone is documenting their database changes?" Yes. "I don't have to keep telling my developers to 'Update the change script when you make db changes'?" That is correct. "How is that possible?" Uh, that's what this blog is about, keep reading.
In Database Projects, I'll refer to them as DB Projects from here on out, you build your database the way you want it to be. You write the create table, create procedure, add index statements, make sure the project builds, and check in your code to a source code repository like TFS. As database changes are requested or needed, you update the DB Project itself, incrementally. If you need to add a column to a table, you simply add a column to a CREATE TABLE definition. You don't need to check if it already exists, just add the column. If you come from a developer background, this will seem normal to you because this is how application development works. If your background is in the db world, this is probably a tough pill to swallow. You don't have to be mindful of what the destination database will look like. You don't have to keep in mind that this database project will be deployed to 5 different servers. All you need to know is what the database should look like when you are done.
So how does it work behind the scenes? I mentioned earlier that your db project has to build, so what happens during a build.
- Syntax is checked across the entire database project.
- All references in the database, i.e. SELECT * FROM [database].[table] are verified.
- Proper data type conversions are checked.
- A .dbschema file is generated. The .dbschema file is basically an xml file that contains the structure or schema of the database you intend to deploy. If you open the file in notepad or some other text editor of your choice, you will find definitions for users, stored procedures, tables, etc..
"But I still don't see where change scripts are magically appearing."
After a build, the next step is to deploy the db project to a database on a server somewhere. This can be done both from the command line, and from the VS IDE. Essentially what happens is this:
- The .dbschema file that was generated during the build is compared against a newly created .dbschema file of the database you are trying to deploy to.
- Microsoft's deployment engine generates a delta script which contains all of the changes necessary to make the deployment database match exactly to the db project.
- If you have the Deployment Action property in the project property pages set to create script and deploy changes, then the script that was generated in step 2 will be applied to the destination database immediately by the deployment engine.
Before I go further, a very necessary assumption needs to be made. The database project, is, and will remain, exactly what you want your database to look like at any given time. It is the Gospel. It is the Holy Grail. It is the Truth. No existing database, production or development, supersedes the db project. It is the blueprint for which all of your databases must come into alignment with. If you deviate from this, you will wake up screaming at 3 in the morning because of the nightmare you just had, and that's only if you can actually fall asleep.
I say this because if you look at the database script that is generated, it may contain DROP Table statements if someone has created tables manually rather than through the database project. It will remove columns, indexes, schemas, etc... during the deployment process if those columns, indexes, and schemas do not exist in the db project. In other words, check your deployment script before deploying... "But you said I didn't have to maintain change scripts anymore." That's correct, no more meticulous maintenance of them, but reviewing the generated change scripts is a must. I suppose this is why db projects are "great", but not "Magnificent".
"So what do I do if someone makes changes manually to our development server behind my back?"There are two options, the first of which is probably something someone unfamiliar with db projects would recommend. The second, is neat.
1. Manually change the db project itself by changing your object definitions.
2. Use Visual Studio's Schema Compare option.
If you want to spend a lot of time, choose option 1. If you want Visual Studio to do the heavy lifting, choose option 2, which is what I'm about to explain.
To use VS's Schema Compare, you need to add a Schema Compare object to your db project. Right-click the project and choose Add->Schema Compare.
This will open a dialog box asking you to pick a source and a target (destination). A source and a target can be any combination of the following: a .dbschema file, a database project, or an actual database. Since you are trying to re-sync your db project to development to incorporate some naughty developer's manual changes, you would want your source to be the development database, and your target to be your db project. At this point you can choose additional options, like what types of objects to ignore during the compare, but typically I just continue on.
Visual Studio's deployment engine will create a delta script that could be used to make your target(db project) into alignment with your source(the development server). If you don't see the delta/change script, there are two small icons in the toolbar that look like scrolls from Lord of the Rings. One opens the window to display the change script, and one updates the change script as you modify your requirements through the user interface. It also gives you a nice user interface that can be filtered to show you missing objects, different objects, equal objects, objects marked as skip, and objects not marked as skipped. You will probably see a left and right panel which contain your source and destination, and then a tree of objects. Now you go through each object and choose to Update, Drop, or Skip it if there is a difference. Something David and I found out this past week, is that rather than manually choosing an action for each difference, you can right-click on the root folder of the objects and choose an action there. This can be great if you are only wanting to change one thing when there are hundreds of differences. In this case, if you followed the assumption I gave earlier, you should have very few changes to propagate to your db project. But this same technique can be followed to generate your deployment scripts manually, where you may not want to deploy everything at once. NOTE: This is also how you will create your db project to begin with. "You mean I don't have to type it all in?" That is correct. The Schema compare will do it all for you. I think you can also import a .dbschema file to do the same thing, but I have not tried this.
Once you have chosen what to change, there is an option on the toolbar, forgive me for not knowing the name of it, but it is something along the lines of "Execute Deployment Script". This will cause your target to match your source based on the changes you have made to your deployment script through the user interface.
A couple of final thoughts to leave you with and then a link at the end to help you learn more about database projects in Visual Studio 2010.
- Database projects can be setup with unit tests. I haven't done this. I don't know much about it, but I like the idea, and I'll probably get into it later and will perhaps blog about it when the time comes.
- Database projects can be setup in TFS to automatically build on a schedule, after each checkin, through continuous integration, etc.. This is good because now we can ensure consistent quality over time, and have a backup of previous builds that we can always fall back on. Old .dbschema files that are generated during scheduled builds can be the target of a Schema Compare to migrate the database structure back to a solid state should any issues arise.
Well, it's time to head to work, so take a look at the link below, and let me know what you think,
Useful link(s) of study:
You'll probably want to navigate to the Documentation tab and grab the
- Visual Studio 2010 Database Projects Guidance Document_(xps) file
- Visual Studio 2010 Database Projects HOL Documentation_(xps_pdf_docx)
I would hlink them, but I'm afraid the links might die. I don't want to turn into a hospice for links on my blog.