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.
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: More First Impressions
Because I was having so many problems yesterday after I tried doing everything at once, creating multiple deployments to multiple servers with a variety of different settings… Anyway, it didn’t work. So today I went back to square one. I created a blank project. Yesterday I used the schema compare to capture the database, but today I right clicked on the project and found “Import Database Objects and Settings…” which did it all for me. Yee Ha!
Anyway, after getting all the objects in, I started trying to deploy. This time I got errors on FK’s. It somehow changed the order on the compound keys columns. While editing, I found that if I messed up the syntax and then saved the file, I didn’t get an instantaneous error. That bummed me out. I got used to that in the original Visual Studio. Anyway, I fixed all the FK errors and now I got the deploy to work. I’m now going to walk through creating custom configurations one step a time to see where the deployment stops working.
Microsoft Connect and DBPro Enhancement
I’ve mentioned it before and I think it’s worth mentioning again, Microsoft Connect really works. I’ve seen bugs and enhancements listed there receive enough attention that they were in the next release or service pack of the product in question.
That brings me to DBPro. We use DBPro for all our database development. It’s a great tool. However, it’s still a bit to geared toward the individual user and not the team, despite it’s moniker (Visual Studio Team Edition for Database Professionals). One thing that really does bother me is how it stores some settings, such as Target Connection in the .user file within a project. This means that each individual sets the connection for the project each time they check it out after another user has had it. Another option is to actually check in the .user file with the project, checking it out and overwriting your own .user file each time you need to work on the project. This apparently gives the MS guys fits, but it’s what we’re doing and it works. However, I’d like to see these project level settings be stored with the project. So I’ve put forward a Connect Enhancement Request. Here’s the link. Please, go and and vote for it, rate it high. The more votes we can get on this, the more likely it’ll be in the next SP or the next release. Thanks.
Be Afraid
I mean me, not you. I’ve been accepted to present at PASS. I’m jazzed and totally freaked at the same time. I put in two abstracts, one based on my book, “Dissecting SQL Server Execution Plans” and the other based on an article I wrote published at SQL Server Central, “Deploying with DBPro to Multiple Environments.” Why then am I freaked? I’ve spent a year delving into execution plans. I won’t say I’m an expert, but I’m comfortable. I’ve been using DBPro for two years now, but I’m hardly eating and breathing it on a daily basis. Well, I wasn’t. From this point forward I’ll be neck deep in it daily. Watch for posts on this topic.
Unit Testing Database Code
Steve Jones, in his daily email from SQL Server Central, supplied a link to a terrific article in the MSDN magazine. Apply Test-Driven Development to your Database Projects. It was written by Jamie Laflen who is a Tech Lead on the DBPro team. I’ve written a couple of articles on database unit testing, but none of them touch this. It’s the new gold standard for introducing the topic to beginners (and lending a helping hand to experienced users). It’s worth a read, or even two. I’m going to go back and review it a couple of times with Visual Studio open and a project running.
No word yet on new virtuals. I’m going to work on another set of tests that I have to run. More on that later.
Home of the Scary DBA 