SNESSUG 4/14/2010
Tonight’s Southern New England SQL Server Users group is sponsored by Idera. Our presenter is Scott Abrants of Iron Mountain. He’s talking about deploying databases using Visual Studio Team System:Database Edition. We have a good turnout with 12 people (yeah, we’re small).
Scott’s presentation was a lot of fun and very informative. He’s very involved with automating his deployments to a fare-thee-well. He really has Visual Studio dancing and singing. It was a very thorough overview of the VSTS:DBE soltuion. Other user groups should be jealous that we got to see this presentation.
Southern New England SQL Server Users Group
Tomorrow, Wednesday April 14th, is the next SNESSUG meeting. We’re going to get a great presentation from Scott Abrants on using Visual Studio Team System for database deployments. I saw Scott presenting this at SQL Saturday:Boston to a packed room. If you didn’t get to see it then, come on down to Rhode Island tomorrow evening. You won’t be sorry.
Visual Studio Team System Article
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.
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.
VSTS:DB Composite Projects
I’ve seen several searches go by from people who are looking for how to create composite projects. Here’s a quick run-down. In the example, I’m going to create a main database project called BaseDB, a server project called MyServer, and a composite project that combines the two called Comp. This project is just another database project as you can see in Figure 1:
New Database Project
Once the new project is created, you need to right click on the References object in the Solution Explorer. Figure 2:
From the pop-up menu select Add Database Reference. The window in Figure 3 will pop up:
Add Database Reference
You can select the other projects for a dynamic view into their changes, or for a static look you can select a .dbschema file. Unfortunately, the .dbschema file is very static. You won’t get changes as they’re made. Usually I use the projects.
You can try to affect Database Reference Variables for the project, but usually this isn’t necessary. If there are indirect references you can suppress them. Again, this isn’t something I’ve had to use so far.
Once you’ve created the references to other projects or .dbschema files, you can see these on the References tab of the project Properties as shown in Figure 4:
You now have a composite project. There are a number of other options you should consider. For example, how are the deployments done. One common approach that I’ve taken is to have the base database do a full rebuild of the database and then have the other two projects do incremental deployments. You’ll need to make sure that all the same Configurations are set up between the projects so that the stuff like the connection strings are the same between projects. You’ll want to make sure that the base project and the composite project are deploying to the same database name.
There’s more, but that should get you started.
Incremental Deployments using Visual Studio Database Edition GDR
I’m stuck.
I’ve been advocating that our company use composite projects for our deployments using the VSTSDBE GDR (Visual Studio Team System Database Edition, General Distribution Release for those not instantly geeky). In a nutshell, VSTSDBE offers two mechanisms for deployment across multiple environments. Both of these work wonderfully well for automation when you are doing a full tear-down and rebuild. When you’re doing incremental deployments, they both fail.
Option 1: Use SQL Command variables to set environment specific variables such as file location, etc., and post-deployment scripts to set security. This works. It’s the method we used prior to the GDR. Unfortunately, security and other environment specific information is hidden inside scripts rather than visible to a given configuration directly within the VS interface.
Option 2: Create a composite project. It stores the common objects, the stuff on it’s way to production, in one project, and the environment specific stuff, such as security, in a second project. Some of the Micrsoft guys are even suggesting this approach. You can then store everything inside of configurations and in project objects, visible to the VS gui, easy to maintain, easy to build…
Ah, but there’s the rub. Easy to build when you’re rebuilding every time. Incremental changes require a database comparison between the project and the database. Ah, but which project. In a composite environment, it has to deploy each project independently. What happens when a change requires a data loss? You have to create that script manually. Fine, but how do you now get it into the rest of the build in an automated fashion?
I’m stuck.
I finally punted and posted it on the MSDN Forums.
Still no Joy in Mudville: GDR RC0
The guys at Microsoft tried to recreate the problem I was having and were unable to. It probably means I’ve got a bad uninstall of CTP17 or something. Since I’m running on a virtual it won’t affect me in the future since I can toss it, but that doesn’t help for the PASS Presentation tomorrow. So, no demo at the end of the presentation, just a bit of tap dancing. It’ll be fine, but I would that it were otherwise.
Home of the Scary DBA 