If you want to compile sample code or write your own extensions for Visual Studio Team System 2008 Database Edition GDR (also known as DBPro or Datadude) this post will save you hours.
In Nov 08 when Microsoft shipped the GDR (General Distribution Release) for the Database Edition of VSTS. They changed the object model to make it compatible with the Visual Studio 2010, but that broke most of the existing Add-ins & sample code that you find on the net. Unfortunately the docs telling you how to fix this had errors. So you could spend hours (days) & not get anywhere. This post shows you how to get it all working quickly. By the way, in May 09 they shipped an update Visual Studio Team System 2008 Database Edition GDR R2 which included some but not all of the power tools capability.
Background for DBA’s who’ve not used DBPro.
Datadude is a tantalisingly brilliant tool with promise to solve some of our hardest problems; eg: Bulking up data, Stress Testing, Load testing, Improving TSQL code & knowing you’ve not broken it. Coordinating a team of DBA’s who’ve all changed Indexes, Stored Proc’s, Triggers etc & seeing ALL the changes between it & the Production system. Then creating the scripts to upgrade the Production system to the next release. An easy way to KNOW your app works perfectly with the next release of SQL Server & to precisely QUANTIFY the performance benefits / degradation you will get from deploying on the next SQL release OR new Hardware etc.
Note: I did not say it is perfect nor effortless in doing all / any of the above. Just that it has promise & each release it gets closer. Often that “close but not quite” capability has frustrated me (heaps) as it almost does precisely what I want. And then I couldn’t figure out how to get that last bit working hence it becomes a non-solution. The good news is :-
As this post wasn’t intended to be a product review. I’ll summarise by saying, if you’ve not seriously looked at it you should, with each release this tool gets more & more relevant to the DBA who just wants a tool & doesn’t want to “just code a bit in C# or VB”.
The Tip: The correct way to write VS DBPro Extensions
The doc that shipped with the product has a few tiny errors that guarantee you will fail. The web version does have some corrections but not they didn’t get everything so you will still fail. Below are the steps that work.
Do NOT use the namespace they recommend eg: [DatabaseServicesProviderCompatibility(DspCompatibilityCategory.None)] Instead use [DatabaseSchemaProviderCompatibility(DspCompatibilityCategory.None)]
using Microsoft.Data.Schema.UnitTesting; using Microsoft.Data.Schema.Extensibility; using Microsoft.Data.Schema; using Microsoft.Data.Schema.UnitTesting.Conditions;
For Example: The old style.
<?xml version="1.0" encoding="utf-8" ?>
<extensions assembly="<ProjectName>, Version=220.127.116.11, Culture=neutral, PublicKeyToken=<Insert Key Here>" version="1" xmlns="urn:Microsoft.VisualStudio.TeamSystem.Data.Extensions" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:Microsoft.VisualStudio.TeamSystem.Data.Extensions Microsoft.VisualStudio.TeamSystem.Data.Extensions.xsd">
<extension type="<Namespace_Name>.<Class_Name>" enabled="true" />
Is rewritten to become
<extensions assembly="<ProjectName>, Version=18.104.22.168, Culture=neutral, PublicKeyToken=<Insert Key Here>" version="1" xmlns="urn:Microsoft.Data.Schema.Extensions" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:Microsoft.Data.Schema.Extensions Microsoft.Data.Schema.Extensions.xsd">
<extension type="<Namespace_Name>.<Optional Class_Name2>" enabled="true" />
<extension type="<Namespace_Name>.<Optional Class_Name3>" enabled="true" />
eg ? System.Reflection.Assembly.LoadFrom(@"c:\YourSolutionPath\bin\Debug\yourCompiled.dll").FullName
This returns something like the following which you can just paste into your .XML file.
"ChecksumCondition, Version=22.214.171.124, Culture=neutral, PublicKeyToken=nnnnnnnnnnnnnnnn"
You can copy your files directly into [Program Files]\Microsoft Visual Studio 9.0\VSTSDB\Extensions but that is considered bad practice as you may overwrite something important.
Note: the total lack of protection in Windows XP makes this step easy. However Vista, Window 7 & Win2008 have security all over this directory, so another reason to create a “CustomExtensions” directory is to change permissions to permit a broader level of access, else you’ll need to be administrator to overcome the “Access Denied” errors.
i. Your signed assembly (.dll) – created when you built your project.
ii. YourTestConditionName.Extensions.xml – created in Step 5 above.
The rest of what you need to know is in the GDR Help file (Books Online) mentioned in Step 1.
Tip 3: A great place to get code to write your own Checksum test &/or Schema checking tests is from Jamie’s article. I highly recommend you check it out.
Apply Test-Driven Development to your Database Projects
- DatabaseUnitTesting2008_Launch.exe (174 KB) Download the Sample code form Jamie's article. This will create the CheckSum & Schema Tests. Even if you only compile & use them you will find they speed up your TSQL refactoring efforts.
Created my first custom data generator for VSTE DBPro (aka DataDude)
FIX: You cannot install the RTM version of Microsoft Visual Studio Team System 2008 Database Edition GDR if the RC version of VSTS 2008 Database Edition GDR is installed on the computer
I hope this saves you time. Remember if you create anything really cool put it up on codeplex.