I wrote up an article on how we’re configuring & deploying databases to disparate systems using a combination of database projects, server projects and compound projects in conjunction with configurations that has been published over at SQL Server Central. Please click over & read it.
Another excellent post over on Barclay Hill’s blog. This time he’s showing how to use pre- and post- deployment scripts to manage data when deployment might result in data loss. I am jazzed for part 2 of this set to see how what I should have been doing all along.
Raise your hand if you think this is a real pain in the bottom method for editing user permissions? Yeah, me too. Visual Studio Team System Database Edition is far to fine a tool to make us edit XML to set database user permissions. A co-worker has posted a change request on MS Connect. Connect works really well as long as people vote for what you report. I’ve seen several things change in SQL Server or get fixed primarily because of the reports in Connect. So if doing this:
<Object Name=”dbo” Type=”SCHEMA”/>
Makes you crazy and you would rather type this:
GRANT EXECUTE ON SCHEMA :: dbo TO UserRole ;
Then click on the link and get the word in front out to Microsoft.
Jamie Laflen and Barclay Hill have published an article in MSDN Magazine outlining the new features in in VSTS:DB GDR. The description of the intent and use of the server project is extremely enlightening. I wasn’t aware of the master.dbschema files available for use within a project. Luckily I haven’t needed them yet. Another thing I wasn’t aware of, if you use the refactoring tools, say rename a table, not only does it save you a lot of typing, but the project will remember that the table was renamed and instead of dropping and recreating it in the next deployment, it will issue SP_RENAME. I’m spreading that word to my team right now. Another good point is that you can make a configuration to work only on your local machine by setting the “Configuration deployment settings for:” to “My isolated development environment.”
Great article guys. Thanks for putting it out there.
Second round of testing. Instead of associating with a project, I tried creating a reference to a .dbschema file. Same error. This time, I’m going to clear out everything. I tried creating it initially on top of the code from CTP 17.
So, another chance to try out the reverse engineer process of “Import Database Schema.” Works great. New AdventureWorks database inside Visual Studio with a tested deployment faster than you can spit.
Created a new server project. Added a login, just to give it something to do. It deployed fine.
On to the compound project. Let’s see what happens. Just to see, I did a build and deploy before I added references or objects or anything. It worked great. Whatever that means. I’m taking it a step at a time, to try to see where it breaks, if it breaks. So, I’ve added just the Adventureworks database project (not the .dbschema file). Trying a build & deploy now. Build went through. No issues. Deploying… Damn. I’m hitting a collation error. However, that’s good because I never got that far before… It worked! On to adding the server as a second reference and then deploying that… Whoops, there it is. Failure. Remove, reset and redeploy without the Server project.
Time for some more experimentation…
It looks like I was getting an error in the server project. I rearranged a few things and now I have the whole thing working again.
There is joy in Mudville!
I’ve downloaded and started the install. The first screen I’m seeing is not filling me with confidence. The executable that is currently running is called “DBProRepair.exe” However, the installed completed without any issues.
Just for giggles, I tried opening my PASS presentation (created on CTP 17). If it worked, I wouldn’t have to recode. Ah, well, no joy. Not that I expected any. Oh, but the Conversion Report that comes up afterwards is very informative.
Reverse engineer against the AdventureWorks2008 database went off without a hitch. All the objects imported into the database with no issues too. So far, so good. Created all the projects. No issues. I’m configuring for a deployment now. We’ll see how that goes. Builds worked fine. I forgot to change the deploy action the first time I did a deployment. Second time… Frell!
The compound deployment is failing. I had something configured wrong… I’ll post when I get it figured out.