How to get Database Edition Power Tools to work on Visual Studio Team System 2008 Database Edition GDR
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.
Summary
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 :-
- The platform is very extensible, in many cases you can write the bits you need without too much effort/code. And there are some nice Codeplex projects (DBPro extensions) becoming available.
- It does do a great job on the Use Cases they target & each release it gets better. I would highly recommend you look at it for :-
- Comparing Data: eg: what rows are the Same / Different / Deleted / Inserted between these two tables. Very handy to have confidence that your new improved version of your stored proc is doing the same as the old slow version in production. Especially handy if there are triggers & other code User Defined Functions & CLR code involved.
- Comparing Schema: After many DBA’s have all worked on different objects, how can you be confident that all the changes have been put into the script to upgrade the UAT or Production system to the next release of your app.
- Unit Testing: If you are starting from scratch & mainly using stored procedures that don’t use Temp Tables, this works great. But for legacy systems & especially where you want to use these tests for Load testing / Performance comparisons, it is so close but misses by a mile. I have some good ideas on extensions that will make this much easier so if I get it working I’ll post it on codeplex.
- Load testing: Possible but time-consuming & inflexible. There is a Codeplex project; SQL Load Test that takes a SQL Trace & turns it into a Unit Test. Not perfect but dramatically reduces the work to make this a financially viable idea. Two others I’ve not used but look interesting are T.S.T. the T-SQL Test Tool & Managed Code Interface for T-SQL Test Tool
- Data Generation: It does a brilliant job for trivial tables ie: Reference or Lookup tables & easy to randomly create new rows by picking real values from a production source. It does do Parent – Child tables. The potential to extend this to very complex distributions seems quite high. I wouldn’t suggest it to creating the really hard “data & time dependant” transactional tables ie: Where you are Inserting, then updating & finally deleting rows depending on values in other tables. eg: the sort of data you’d keep in a Hospital Patient Scheduling system, where you need to generate appointments for patients but only after they’ve been admitted & before they’ve been discharged.
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.
Open the Documentation VSTS 2008 Database Edition GDR Documentation (or go the the web How to: Upgrade a Custom Test Condition from a Previous Release)
Go to the section called “How to: Upgrade a Custom Test Condition from a Previous Release”
When you get to step: “To add an extension compatibility attribute” there is an ERROR.
Do NOT use the namespace they recommend eg:
[DatabaseServicesProviderCompatibility(DspCompatibilityCategory.None)]Instead use
[DatabaseSchemaProviderCompatibility(DspCompatibilityCategory.None)]Change your references as the doc states. Remove any references relating to Microsoft.VisualStudio.TeamSystem.Data & replace with its new equivalent Microsoft.Data.Schema. You may also find any / all of the following namespaces handy. Depending on what you are writing.
using Microsoft.Data.Schema.UnitTesting;
using Microsoft.Data.Schema.Extensibility;
using Microsoft.Data.Schema;
using Microsoft.Data.Schema.UnitTesting.Conditions;A similar namespace rename needs to happen to the deployment script to register your extension. Again most of the samples I saw on the net & in Books Online were pre-GDR. You need to change Microsoft.VisualStudio.TeamSystem.Data.Extensions to Microsoft.Data.Schema.Extensions in a number of places.
For Example: The old style.
<?xml version="1.0" encoding="utf-8" ?>
<extensions assembly=" <ProjectName> , Version=9.1.0.0, Culture=neutral, PublicKeyToken= <Insert Key Here> " version="1" xmlns="urn:Microsoft.VisualStudio.TeamSystem.Data.Extensions"
xmlns:xsi="https://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" />
</extensions>
Is rewritten to become
<?xml version="1.0" encoding="utf-8" ?>
<extensions assembly=" <ProjectName> , Version=9.1.0.0, Culture=neutral, PublicKeyToken= <Insert Key Here> " version="1" xmlns="urn:Microsoft.Data.Schema.Extensions"
xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="urn:Microsoft.Data.Schema.Extensions Microsoft.Data.Schema.Extensions.xsd"><extension type=" <Namespace_Name> . <Class_Name> " enabled="true" />
</extensions>
Tip 1: You can also register multiple Test conditions from the one project by adding extra Extention lines.
<?xml version="1.0" encoding="utf-8" ?>
<extensions assembly=" <ProjectName> , Version=9.1.0.0, Culture=neutral, PublicKeyToken= <Insert Key Here> " version="1" xmlns="urn:Microsoft.Data.Schema.Extensions"
xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="urn:Microsoft.Data.Schema.Extensions Microsoft.Data.Schema.Extensions.xsd"><extension type=" <Namespace_Name> . <Class_Name> " enabled="true" />
<extension type=" <Namespace_Name> . <Optional Class_Name2> " enabled="true" />
<extension type=" <Namespace_Name> . <Optional Class_Name3> " enabled="true" />
</extensions>
Tip 2: To get the 1st line, PublicKeyToken …etc. Run the following command from within VS Command Window
? System.Reflection.Assembly.LoadFrom(@"FilePath\yourCompiled.dll").FullName
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=1.0.0.0, Culture=neutral, PublicKeyToken=nnnnnnnnnnnnnnnn"Then create your own subdirectory to hold your CustomExtensions make it a child of [Program Files]\Microsoft Visual Studio 9.0\VSTSDB\Extensions\
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.
The two files you need to copy into the location you just created are :-
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)
I hope this saves you time. Remember if you create anything really cool put it up on codeplex.
Dave
Technorati Tags: SQL Server,Visual Studio DBPro,Visual Studio Team Suite for Database Professionals GDR
Comments
Anonymous
May 10, 2010
Hi Dave. Thank you for this article. However, I am having a very tough time trying to convert the db pro extension here: http://dbprogenerators.codeplex.com/ Can you help us out please? Thank you very much!Anonymous
October 22, 2010
I noticed that one of the above links is no longer there. Here is an alternate to the same article. msdn.microsoft.com/.../cc164243.aspx Database Unit Testing with Team Edition for Database Professionals