MSDN Blog on Data Management in VSTS:DB
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.
Database.sqlpermissions
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:
<PermissionStatement Action=”GRANT”>
<Permission>EXECUTE</Persmission>
<Grantee>UserRole</Grantee>
<Object Name=”dbo” Type=”SCHEMA”/>
</PermissionStatement>
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.
MSDN Magazine: Article on VSTS:DB
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.
Finally, the GDR is Released
From the Data Dude himself.
This is great news. I’ve been working with the CTP’s for several months now, telling the other DBA’s on my team that they had to wait until it was completely ready for release. It’s been a long wait, but I’m sure it’s worth it.
My congratulations to the team. I met several of you at the PASS Summit. I really apreciate the work you’ve put into this great tool. It really makes a difference in how we develop and deploy databases. The changes in the GDR are making a great utility even better. Thanks Mr. Drapers. Thanks also to Jamie Laflen, especially for helping validate some of the ideas I presented at PASS. Thanks to all the rest of the team, whose names I don’t recall, especially the guy who took so long to explain to me what the “literal” was for in the reference page. Excellent work everyone. You guys should be proud.
VSTSDBE GDR RC0: No Joy in Mudville
A few posts ago I outlined the problems I was having getting the RC0 of the GDR to deploy appropriately. I was successful in a deployment, so I thought I was done. Yesterday I went to do a run through of the presentation for PASS and my deploy failed. Despite the fact that I’m running on the same machine with the same project that worked before. I’ve now gone back through the whole process again, but I’m getting nothing but failures. It’s totally hosed. At this point, barring a miracle, I’m going to present the process and then explain that I can’t demo it because of failures.
I don’t know if it’s just a configuration issue with my virtual machine or something flawed in the release. Either way, I’m at a standstill and completely bummed. I’m going to try recreating things, but I suspect that I’m going to have to assume a failure at the conference and not run the demo.
I posted the issue and the error messages I’m getting over at the MSDN Forum for VSTSDBE
VSTSDBE RC0 Post 3
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!
Composite Projects in DBPro
I know the name is supposed to be Visual Studio Team System Database Edition, but DBPro, like Data Dude, slides off the tongue and the other doesn’t. Anyway…
I’ve been working with CTP 17 since it came out this week. So far, knock wood, no issues. I was trying to work out how to get multi-platform security working in some manner other than post-deployment scripts. Everything I tried failed. I finally posted a question on the MS forum. It was suggested that I use composite projects… What?
A little research later and I’m off and running. Now, with the ability to chain projects together, you can provide two projects, one with all the structures, and the other with only specialized differences, such as you might find in a development server.
I built a copy of Adventureworks (I love having a free test database to play with) in one database project. I created a server project and added a couple of logins to it. I then created a second database project. To this new project I added references back to the other two. I added a few security changes, to reflect, for example, a dev deployment to a server that isn’t going to have the same settings on security as the production server.
Now when I run build, all three projects get built, if needed. Deploy launched all three and the final result showed the aggregation of the projects.
I’m loving this. The improvements this creates in terms of process for tear-down and rebuild of the database, as well as enabling a decent process around incremental builds too. Now, if they would just release it already.
PASS Summit
It looks like this year is going to be pretty good. They’ve expanded the program. If you’re not going, you should consider it.
Personally, I’m pretty excited. I’m presenting our build and deployment process using Visual Studio Team System Database Edition. I’ve got the first half of the presentation down cold because it’s how we’ve been doing our deployments for a couple of years now. The second half concerns me a bit. I’m showing the new release that works with SQL Server 2008. Unfortunately that new release isn’t done and I keep having to update my code and slides as the new versions come out with different functionality. I’m more than a little bit nervous about that part of the presentation. I wish there was a way to bounce it off Gert Drapers before I gave it. Maybe I’ll fire off an email to him after they finish coding. I’d do it sooner, but I’m not about to distract the man that’s making a tool I use so much.
Speaking of which, the new version, CTP 16, has a great addition. There’s an executable, I assume distributable without license, that allows for installs of VSTSDB projects without having a full Visual Studio install. This tool is going revolutionize, again, database development and deployment. You have to love it!
VSTS 2008 Database Edition GDR: Final First Impression
I finally finished my initial set of tests with the GDR CTP release. It’s great! They’ve solved so many of the deployment problems that we had been experiencing that it’s now hard to wait until they actually release the product. The bad news is, it completely changes my presentation at PASS. I’m still going to cover 2005/2008, but now I’m going to cover the GDR as well. I have one hour to hit both processes… That’s not going to be easy.
Anyway, they’ve broken everything down to either work locally only or as part of the larger project. It all gets checked into source control. It all comes back out. No manual processes (like checking in the .user file) necessary.
Thank you Gert Drapers, wherever you are.
VSTS 2008 Database Edition GDR: Still More First Impressions
Following on to my adventures in creating multi-environment deployment processes with the new version of Data Dude (DBPro, VSTS Database Edition, whatever we’re calling it this week).
I’ve create a new configuration, copying all the settings from the Debug configuration. I’m adding a new Deployment configuration file and making a change. The deploy worked. Woo hoo! Now to get really funky. I’ll create a new “Sql command variables file:” and add a variable for setting the data directory. Now to deploy and… Urk! Failed. It’s not recognizing my variable.
Now I’m stuck. I’ve checked the syntax. It’s right. I double checked it all and reran deploy. Now it works… Color me confused. Whatever. Successful test. Time to create another configuration, simulating a QA server… Got that working too. I don’t know what went south yesterday, but it’s all working now. Now to quantify it all and begin to create some standards.
I’m also going to check this all into source control to see how the connection information is stored. In 2005/2008 (not GDR), the connection info was stored in the .user file. We finally started checking our .user files in with the rest of the project, to the abject horror of every MS developer that heard about it. More on this tomorrow.
Home of the Scary DBA 