SSIS Junkie : Datadude

Considerations when starting a new SSDT database project

jamiet — Thu, 21 Mar 2013 10:58:03 GMT

As you may have realised from much of my blogging over the past year or so I’m an advocate of using SSDT database projects for building database solutions on SQL Server. I have been using SSDT database projects a lot in that time and have come up with a checklist of things to consider when starting a new SSDT database project and I’ll be detailing that checklist below. I strongly advise you to consider making decisions about these items before you even write a scrap of code as invariably it will be more difficult to change later, especially if you have already deployed your database.

In no particular order here is my checklist:

Folder Structure

By default SSDT will not provide a folder structure for new projects in which to store all your script files so you might want to consider setting one up yourself. I recommend not trying to create a large hierarchy of folders to start with as this will evolve as you go about building your database. Here’s a simple starter for ten:

Filegroups

If you don’t understand the importance of filegroups then read Thomas Larock’s recent blog post DATABASE FILEGROUPS: JUST LIKE SEATBELTS BUT WITH LESS CHAFING. Quite often your organisation will stipulate policies for what filegroups you should be using and I highly recommend that you set up filegroups in your database projects to match those policies sooner rather than later. If your organisation does not have any such policies then perhaps think about defining them yourself, Thomas lists a few considerations that may influence your decisions:

separation of system data from user data
larger indexes may benefit from their own filegroup
archival of data can benefit from using dedicated filegroups as this will reduce backup maintenance tasks

If you follow my suggested folder structure above then put your filegroups into the “Storage” folder:

Above all, specify your default filegroup especially as many DBAs won’t be happy about you putting objects into the PRIMARY filegroup (which is the default). This is done by right-clicking on the project and selecting “Properties..” When there hit “Database Settings…” and set your default filegroup on the “Operational” tab:

If you intend to use the filestream feature of SQL Server then you can set the default filestream filegroup here too.

Collation

If you take only one piece of advice from this blog post make it this: set your collation before you write a single line of code (just trust me on this, OK). You’ll find this in Project Properties->Project Settings->Database Settings->Common

Again, find out if your organisation has any guidance on database collation.

Specify Target platform

SSDT allows you to deploy a project to SQL Server 2005, 2008, 2012 or Azure so it stands to reason that you should specify which you are intending to deploy to. You’ll find this in Project Properties->Project Settings:

Default Schema

If you follow the premise that “anything that SQL Server picks as the default is probably wrong” then you should probably think about changing the default schema which, by default, will be [dbo]. SSDT will create all new objects in the default schema (unless otherwise specified).

Generally I’m of the opinion that not accepting SQL Server’s defaults is a good idea if only because it forces you to think about these things and be aware of them; in the case of schemas it forces you to think about security and who should have GRANT or DENY permissions and on what.

Specify the default schema in Project properties->Project Settings:

Review other database settings

I’ve mentioned the most important database settings that you should look to change however you should also glance over the rest of the defaults that SSDT chooses for you to verify that they are valid for your project. Again, these are in Project Properties->Project Settings->Database Settings. a few I’d call out as being especially worthy of your attention are:

Auto update stats (on by default)
Recovery model (FULL by default)
Transaction Isolation (RCSI off by default)
Service broker (disabled by default)

Code Analysis

SSDT always checks the syntax of your database code, that’s one of the main justifications for using SSDT database projects. However what it does not do (not by default anyway) is check whether the code you write might be considered good code. That’s what Code Analysis is for, your code gets checked to see if it adheres to well-understood good practices for SQL Server database development. For example, Code Analysis will check for use of “SELECT*” which is generally regarded as a bad thing.

Code analysis is turned off by default. I recommend that you head into Project Properties->Code Analysis and turn it on. Moreover I recommend that you check all the boxes under “Treat Warning as Error” – this forces developers to address issues that get raised by Code Analysis.

Big Labels

Add a banner to your Pre-Deployment script as I describe in Big label generator. This may seem rather unnecessary but it takes about 30 seconds and believe me, at some point you’ll be glad you did!

The demo project that I took some of the above screenshots from is downloadable from http://sdrv.ms/ZW0gNt and might provide a useful template for your own SSDT database projects.

If you have any more suggestions for my checklist please add them to the comments below!

@Jamiet

SSDT database projects were not always going to output T-SQL…

jamiet — Wed, 13 Feb 2013 09:15:18 GMT

Interesting the stuff you pick up from reading forums…

“The BATCH seperators [in SSDT deployment scripts] are used to provide scoping when resolving object shapes. SSDT or VSDB scripts for that matter serve a different purpose, they provide the blueprint of the model you want, and do not represent how it will be used to construct the target site. We choose TSQL as our DSL, at first we wanted to use a different representation, but user research in 2005 told us loud and clear users wanted to use TSQL as their DSL. In order to make the compiler work, we had to impose some simplifications, one is a more frequent use of BATCH seperators to indentify object scopes.”

by Gert Drapers (former lead of the SSDT product team) at http://social.msdn.microsoft.com/Forums/en-US/ssdt/thread/47d4124b-82cd-48f9-8ba7-c2ae4c73cbcf/

Personally I’m glad that they chose to use T-SQL as it makes debugging easier but I’d be interested to know what else was on the table!

@Jamiet

Which version of SSDT Database Projects do I have installed?

jamiet — Fri, 08 Feb 2013 21:15:52 GMT

SQL Server Data Tools (SSDT) is released on a very regular cadence (note that I’m talking about the database projects part of SSDT, this is nothing to do with SSIS/SSAS/SSRS). Since the first release in March 2012 there have been releases in September 2012, November 2012, & December 2012. I personally have found it difficult to keep track of not only which particular version I am using but also that which are my colleagues are using – quite simply I didn’t know how to discover the current version number. I assumed that the answer would lie within Visual Studio 2012 in the Help->About Microsoft Visual Studio menu option, when I checked there I saw this:

A version number – 11.1.21208.0. What does that mean though? To understand more we have to go to Add/Remove programs:

There are many different items listed there, let’s look at them in turn:

Microsoft SQL Server 2012 Data-Tier App Framework. This is fairly self-explanatory if you know what the Data-Tier App Framework (aka DACFx) is. If you don’t, see the notes at the end of this blog post*. The reason that you see two entries in the list above is due to my having the 32bit & 64bit versions installed – the fact that there is no differentiation between the two in the title is a bug.
Microsoft SQL Server 2012 Express LocalDB. LocalDB is the small server-less version of SQL Server that now gets deployed with SQL Server proper. LocalDB also gets installed with SSDT if you do not already have it, but you should consider it part of SQL Server itself.
Microsoft SQL Server Data Tools 2010. This was described to me as the “installation chainer” – it is the installation component that manages SSDT and its dependencies. I think of it as the SSDT shell within Visual Studio 2010.
Microsoft SQL Server Data Tools 2012. This was described to me as the “installation chainer” – it is the installation component that manages SSDT and its dependencies. I think of it as the SSDT shell within Visual Studio 2012. Note that the version number is the same as in the Help->About screenshot at the top of this article – that is because they are one and the same.
Microsoft SQL Server Data Tools – Database Projects – Web installer entry point. A stub project that gets installed when you install SQL Server. Its the SSDT database project entry point.
Microsoft SQL Server Data Tools – enu (10.3.21208.0) & Microsoft SQL Server Data Tools – enu (11.1.21208.0). These are the things we’re interested in. These *are* SSDT database projects, for Visual Studio 2010 & Visual Studio 2012 respectively. The version numbers are those for the December 2012 release; note that the build numbers are the same (X.X.21208.X), only the major and minor numbers are different.
Microsoft SQL Server Data Tools Build Utilities – enu (10.3.21208.0) & Microsoft SQL Server Data Tools Build Utilities – enu (11.1.21208.0). Everything you need to do a headless (i.e. without having Visual Studio installed) build/deploy of an SSDT database project.

So at the end of all that we have determined the following version numbers for SSDT database projects are:

Release	Visual Studio 2010 version number	Visual Studio 2012 version number
September 2012	10.3.20905.0	11.1.20905.0
November 2012	10.3.21101.1	11.1.21101.1
December 2012	10.3.21208.0	11.1.21208.0

I do not know the version numbers for the March 2012, September 2012 & November 2012 releases (if anyone out there does know please let me know in the comments) however I shall update this blog post for future releases. September 2012 and November 2012 releases now added!

I have requested that any blog post to announce future releases should contain the version numbers. Let’s hope that happens.

@Jamiet

* DACFx is described here as:

The Microsoft® SQL Server® 2012 Data-Tier Application Framework (DACFx) is a component which provides application lifecycle services for database development and management for Microsoft® SQL Server® and Windows Azure SQL Databases.
DacFX supports various database deployment and management scenarios for SQL Server and Windows Azure SQL Databases including extracting/exporting a live database to a DAC package, deploying a DAC package to a new or existing database, and migrating from on-premise SQL Server to Windows Azure. This functionality is exposed via the DACFx managed API. DACFx can target SQL Server 2005 SP4, 2008 SP1, 2008R2, 2012, and Windows Azure SQL Databases. DACFx also provides the command-line utility SqlPackage.exe for creating and deploying .dacpac and .bacpac packages. DACFx supports scenarios provided by SQL Server client tooling including SQL Server Data Tools and SQL Server Management Studio 2012.

Responses to SSDT questions

jamiet — Fri, 11 Jan 2013 22:22:24 GMT

My recent article Get to Know SQL Server 2012's SQL Server Data Tools prompted two questions to come to me via email and in the interests of sharing knowledge via search engines I thought I would answer them here rather than by simply replying by email (I hate that so much useful information gets trapped inside closed inboxes).

Question: How would you handle cases below, our main database has many objects already, we are using redgate source control to keep things under sourcecontrol but ssdt would be awesome.
Our database has some invalid stored procs etc, unfortunately we do not want to drop them, underlying objects either don't exists or has changed etc
There are linked servers or code referencing other objects stored in the database
There are staging table named automatically in the database and we do not want to include them in the project and not drop them or touch them in a deploy

JT: The overall question here seems to be “how do we prevent SSDT from DROPping sprocs and staging tables that are in the target database but are not in the SSDT project?” Fortunately SSDT has an option within it to allow exactly what the questioner requires. When an SSDT project is published one has the option to set some properties relating to that publish. One of the settings is “DROP objects in target but not in project”:

If that option is left unchecked (which is the default) then no objects will get removed from the target database.

Question: At my previous shop, I done a proof of concept with one of our databases, but never got it implemented as VS2012 (with SSDT) had not been released yet. Well, fast forward 9 or so months, and at my new shop (which is extremely agile) I've been tasked with setting up our db projects with SSDT so for the immediate short term goals/objectives we can;

Allow new or existing developers to be able to easily get the latest databases from source control downloaded from TFS, built and deployed onto our local sandbox db environments. This ideally will be done via a powershell script or something of the sort.
Be able to employ C.I. so that every night a database deployment is done to our system test servers.

3 days into it, and I've wrestled with 3 of our databases, reverse engineering them into TFS and getting them built etc. My question is, what are the best practices or guidelines for;

Allowing only Windows authentication for the local deploys to the sandbox environment. i.e. is it best to have a script with all our team user logins in a script? How would the project recognise a team member which will create a login for them so once the project is deployed they can login to their local db?
Using a 1-touch/click action (preferably outside of VS2010) to get latest, build project & deploy to local sandbox
Same as above but to a system test server

JT: There are quite a few questions there. Firstly I’ll say that its great to hear of sandboxed environments and Continuous Integration (CI) being used – I openly advocate these techniques in my blog post Implementing SQL Server solutions using Visual Studio 2010 Database Projects – a compendium of project experiences.

Now, taking each question in turn:

Windows Auth for local deploys. I’m not sure if there’s any recognised best practise here but if developers are working on local sandboxes I would assume they would have sysadmin rights so I’m not sure there’s a need to have a script of team logins. Perhaps I have misunderstood the question in which case please clarify in the comments below. I will say this though – I think its better to have a Windows group that you can move all of your devs in and out of rather than having to modify a script each time a user joins your team.
1-click get latest, build & deploy. Scripting your build & deploy is something else I advocate in the aforementioned blog post:

Building and deploying your datadude projects within Visual Studio can become a real time hog; in my experience its not unusual for deployments to take many minutes and your Visual Studio environment will be unavailable for further development work during that time. For that reason I recommend investing some time in writing some msbuild scripts that will build and deploy your project(s) from the command-line.

I’ll go further here and say that the script with which you use to build and deploy to your sandbox should be the same script as which you use for your production environment and everything in between. The script should take a parameter value to name the environment and then deploy appropriately.
See 2.

Hope that is useful. Any questions? Please put them in the comments.

@Jamiet

“Add as object” or “Add as script”? [SSDT]

jamiet — Fri, 04 Jan 2013 14:15:46 GMT

In SQL Server Data Tools (SSDT) one way to create a new object is to right click on a folder in Solution Explorer and point to Add. Doing so will display this menu:

A number of options are offered for creating a brand new object (“function”, “table”, “stored proc” etc…) and one of them is “Script”. If your preference is to handcraft the DDL for every database object without resorting to a GUI then you may choose to use the “Script” option because, after all, every file is just a DDL script – why not write the thing from scratch? There is however one fundamental difference between the Script” option and all the other options that you should be aware of.

For the screenshot below I added a script and handcrafted a simple CREATE TABLE statement:

Notice that the table is defined in the dbo schema but it is not appearing in SQL Server Object Explorer under the dbo schema node. Why is that? The answer is actually pretty simple. If I select Script1.sql in Solution Explorer and hit F4 the properties window appears and we see this:

The Build Action property of the file is set to “None” which means that the file does not get included in a build and hence any objects defined within that file will not appear in our database. This is the default behaviour when you Add as script whereas the default behaviour when you explicitly add an object is to set Build Action=Build. That is the fundamental difference between adding a script and adding a specific object.

This is one of those nuances of SSDT that is an easy problem to spot when you know about it but can cause you to spend hours hunting around for a solution if you do not (believe me, I’ve been there). Be aware of it!

@Jamiet

SQL Server Data Tools does support required variables

jamiet — Sun, 12 Feb 2012 11:58:24 GMT

Over the past few years of using datadude (aka DBPro aka Visual Studio Database Projects) I have fallen prey to a peculiar little nuance – if you forget to supply a value for a sqlcmd variable then it will simply use a default and often that is not the desired behaviour. Hence why yesterday I submitted the following suggestion to http://connect.microsoft.com/sqlserver/feedback :

Specify sqlcmdvars properties as "required to be overridden”

In my current place of work I am responsible for maintaining our datadude projects and we have another team that is in charge of deployments. Hence, when we place new properties into the sqlcmdvars file I need to tell the deployment team what values to supply for that property per environment (dev, systest, uat, prod).
Unfortunately lack of communication/human error occasionally creeps in and, for whatever reason, no value gets supplied for some property at deployment time. If this is the case the default value as specified in the sqlcmdvars file gets used instead - invariably this will be the wrong value. I would like a mechanism within SSDT of preventing this from ever happening.

One simple way to prevent this would be to specify that a sqlcmdvars property is *required* to be overridden during the deployment. In other words, never use the default. if an override is not supplied at deployment time then the deployment should fail.
Note that this stipulation should only be in place when deployment occurs from the command-line - if deploying from Visual Studio the default should be allowed (simply because Visual Studio doesn't provide a way to specify anything other than the default value supplied in the sqlcmdvars file).

https://connect.microsoft.com/sqlserver/feedback/details/724366/ssdt-specify-sqlcmdvars-properties-as-required-to-be-overridden#details

It transpires that this requested feature is already available in the forthcoming SQL Server Data Tools (SSDT) as I shall now demonstrate. This screenshot shows the project properties of a SSDT project where we define the sqlcmd variables, I have defined a variable called $(Id_value):

In SSDT the nomenclature for deploying a project is “Publish”, a function that can be found by right-clicking on a project in Solution Explorer:

Selecting that brings up the Publish dialog.

Notice how the “Publish” button is greyed out – that it because I have not supplied a value for $(Id_value); supplying a value enables the Publish button and I can go ahead and publish my project. In other words, SSDT insists that I supply a value for that variable – exactly as I requested in my Connect submission.

The same is true if I use the command-line publishing tool sqlpackage.exe. The following command does essentially the same thing as the above depicted Publish dialog:

>sqlpackage.exe /TargetDatabaseName:MyDB /TargetServerName:".\RC0" /Action:Publish /SourceFile:"TestRequiredSqlCmdVars.dacpac"

Executing that gives me an error:

Publishing to database 'MyDB' on server '.\RC0'.
*** Missing values for the following SqlCmd variables:
'Id_value'.

Here’s a screenshot showing the same:

In order to supply a value for the variable from the command-line you need to use the /v: switch like so:

>sqlpackage.exe /TargetDatabaseName:MyDB /TargetServerName:".\RC0" /Action:Publish /SourceFile:"TestRequiredSqlCmdVars.dacpac" /v:Id_value=1

As you can see, publish was successful!

So there you go, using SSDT you’ll no longer be able to fall prey to the problem I highlighted at the top of this blog post. In a later blog post I’ll show how you CAN supply a default value if you want to and also how you can override for your local environment.

@jamiet

Thoughts on Test Driven Database Development

jamiet — Thu, 26 Jan 2012 08:30:00 GMT

Test-Driven Development (TDD) is a software development practise that has been around for a few years. Wikipedia describes it as:

Test-driven development (TDD) is a software development process that relies on the repetition of a very short development cycle: first the developer writes a failing automated test case that defines a desired improvement or new function, then produces code to pass that test and finally refactors the new code to acceptable standards. Kent Beck, who is credited with having developed or 'rediscovered' the technique, stated in 2003 that TDD encourages simple designs and inspires confidence.

http://en.wikipedia.org/wiki/Test-driven_development

Since 2003 TDD practises have seen refinements such as Behavior-Driven Development and Uncle Bob's Three Rules of TDD, all the while TDD has pretty much become an accepted way of developing quality software. Accepted that is everywhere outside of the database development arena and that is the arena in which I spend my working life. TDD simply has not, in my opinion, caught on with database developers like it has our appdev brethren and I was reminded of this yesterday when Atul Thakor asked on Twitter:

anyone done TDD for database development and would they recommend it?

https://twitter.com/#!/atulthakor/status/161886007929733120

To which my answer was an emphatic:

(1) yes & (2) absolutely, yes
https://twitter.com/#!/jamiet/status/161894215217987585

I'll use this blog post to expand on that outside of 140 characters.

In October 2010 I undertook a mini-project for the client I was working for at the time (a bank) where a colleague and I were tasked with building the database portion of a system that would support reconciliation of our ETL processes. It was a nice piece of work in that it was small, well-scoped, time-bound, greenfield, did not have any external dependancies and had a technically savvy product owner. We sat down at the start and decided that this was an ideal opportunity to trial TDD as a method of developing a database; I would write the failing tests and my colleague would make the tests pass. We came up with some guiding principles and, although we didn't know it at the time, they were pretty close to Uncle Bob's three rules.

I used Visual Studio 2010's database unit testing framework¹ to write my tests and have them run as part of our Continuous Integration (CI) build (see Setting up database unit testing as part of a Continuous Integration build process). I would write the tests, check-in, the CI build would fail and my colleague would "get latest" in order to see what code he had to write to stop the build from failing. To cut a long story short the use of TDD was considered to be a great success; we shipped a working system on time/on budget and moreover, even though I didn't write a scrap of code that went into production I have never had more confidence that a system I was involved in building worked as intended. That's quite a statement. My confidence stemmed from the fact that as the test author I was ultimately responsible for ensuring that the system did what it was supposed to; I could qualify my confidence by pointing at our CI build and highlighting the number of tests that were passing and how that number had steadily increased as the project progressed.

By the time the project had finished the database consisted of (if memory serves me correctly) 6 tables and about 10 stored procedures or functions (so yes, very small). To test that we had roughly 70 tests that were getting run up to 20 times a day. The project had taken about two months from start-up to final delivery - you can make your own opinions as to whether you consider that prompt or tardy but our product owner was happy and that's pretty much all that counted as far as I was concerned.

Since that project I have moved onto other clients and at each one I have always extolled the use of database unit testing; we haven't always practised TDD but at each one we have been writing database unit tests and in the future I suspect that a client's willingness (or lack thereof) to use database unit testing will be a major factor in influencing whether we end up working together or not.

Are you a database developer doing database unit testing or perhaps even TDD? Let me know in the comments, I'd love to hear about others' experiences.

@jamiet

¹Yes, that linked-to article from 7 years ago is the best one I could find to describe what Visual Studio's Database Unit testing Framework actually is - sort it out Microsoft!

UPDATE: I have just remembered that Jamie Laflen has written an excellent whitepaper entitled Apply Test-Driven Development to your Database Projects that goes into much more detail about how to achieve database TDD using Visual Studio than I have here. Well worth a read.

Implementing SQL Server solutions using Visual Studio 2010 Database Projects – a compendium of project experiences

jamiet — Sun, 01 Jan 2012 18:13:00 GMT

Over the past eighteen months I have worked on four separate projects for customers that wanted to make use of Visual Studio 2010 Database projects to manage their database schema.* All the while I have been trying to take lots of notes in order that I can write the blog post that you are reading right now – a compendium of experiences and tips from those eighteen months. I should note that this blog post should not necessarily be taken as a recommendation to actually use database projects in Visual Studio 2010 – it is intended to be useful for those of you that have already made the decision to use them; having said that, I do make recommendations as to actions I think you should take if you have made that decision.

First let’s be clear what we’re talking about here. Visual Studio Database Projects have been known by a few different names down the years, some of which you may be familiar with. If you have ever heard the terms datadude, DBPro, teamdata, TSData or Visual Studio Team System for Database Professionals then just know that all of these terms refer to the same thing, namely the project type highlighted below when starting a new project in Visual Studio:

From here onwards I am going to refer to Visual Studio Database projects and all the features therein simply as datadude because that’s a popular colloquial name (and is also much quicker to type). Know also that at the time of writing the features that I am talking about here are currently undergoing some changes ahead of the next release of SQL Server (i.e. SQL Server 2012) in which these features are mooted to be delivered under a new moniker - SQL Server Developer Tools (SSDT).

OK, with all those preliminaries out of the way let’s dig in.

Continuous Integration

Continuous Integration (CI) is a development practise that has existed for many years but in my experience has not been wholly embraced by the database community. The idea behind CI for databases is that every time a developer checks-in a piece of code be it a stored procedure, a table definition or whatever, the entire database project is built and then deployed to a database instance. Microsoft provide a useful article An Overview of Database Build and Deployment that goes some way to explaining how to setup your CI deployment.

CI is one of the fundamental tenets that underpins a lot of the things I talk about later in this blog post and hence gives rise to my first recommendation when using datadude:

Recommendation #1: Use Source Control and implement a Continuous Integration deployment

In an earlier draft of this blog post I outlined in detail the CI configuration from one of the aforementioned projects. Its not suitable for inclusion at this point in the current draft but I still think there is some useful information to be gleaned so I have included it below in “Appendix – An example CI configuration”.

Composite Projects

Each of the four aforementioned projects were brownfield projects meaning that each already encompassed some established, deployed, databases and they wanted to bring those databases under the control of datadude. Each project had thousands of objects across multiple databases and in this situation it is very likely that some of the stored procedures, views or functions will refer to objects in one of the other databases. The way to resolve those references is to use database references however once you have applied all of your database references it is still very possible that you will run into a situation where code in database A refers to an object in database B while at the same time database B refers to an object in database A. This is depicted in the following figure:

Here we have a view [DB1]..[View2] that selects data from [DB2]..[Table1] and a view [DB2]..[View1] that selects data from [DB1]..[Table1]. Datadude does not allow a database reference from [DB2] to [DB1] if there is already a database reference from [DB2] to [DB1] and hence will return an error akin to:

SQL03006: View: [dbo].[View1] has an unresolved reference to object [DB1].[dbo].[Table1].

We have the proverbial chicken-and-egg problem, [DB1] can’t be created before [DB2] and vice versa. This problem is solved by using Composite Projects (not to be confused with Partial Projects) which allow you to split objects that are intended to be in the same database over multiple datadude projects. I could go over how you set one of these things up but there’s really no need because there is a rather excellent walkthrough on MSDN at Walkthrough: Partition a Database Project by Using Composite Projects; the reason for me mentioning it here is to make you aware that composite projects exist and of the problem that they solve. If you are introducing datadude into a brownfield project then it is highly likely that you are going to require composite projects so learn them and learn them good.

One important last note about composite projects is to answer the question “How does the tool know that the multiple projects refer to the same database?” The answer is given at the walkthrough that I linked to above; namely, it says:

“Do not specify server variables and values or database variables and values when you define references in a composite project. Because no variables are defined, the referenced project is assumed to share the target server and database of the current project.”

So now you know! To put it another way, if you reference one project from another and don’t tell datadude that the two projects refer to different databases then it assumes they refer to the same database.

Code Analysis

Datadude provides the ability to analyse your code projects for code in stored procedures and functions that it considers to be inferior and highlight it – this feature is called Code Analysis. Note that Code Analysis will not highlight code that is syntactically incorrect (datadude does that already, which may well be considered its core feature), it highlights code that is syntactically correct but may be considered defective when executed. Specifically Code Analysis will highlight the following perceived code defects (click through on the links for explanations of why these are considered code defects):

Use of SELECT *
Use of @@IDENTITY rather than SCOPE_IDENTITY()
NCHAR & NVARCHAR fields whose maximum length is 1 or 2
Deprecated join syntax
An output parameter that might not be assigned to
Casts that could result in data loss (in my experience this is the one that shows up most often – particularly in inherited code)
Use of special characters in object names
Use of reserved words
Use of sp_ prefix for stored procedures
Use of unindexed columns in IN predicates
Use of patterns beginning with a wildcard in a LIKE predicate
Code that would not use an existing index that might actually be beneficial
Use of ISNULL() on nullable columns
Deterministic function calls in a WHERE predicate

In my opinion the best aspect of Code Analysis is that it can be run as part of your Continuous Integration deployment meaning that if anyone checks in some deficient code, BOOM, your CI deployment fails and the developer is left red-faced. Nothing else has increased the code quality on my projects quite like running Code Analysis as part of a CI deployment.

Hopefully I have convinced you that turning on Code Analysis is a good idea. If you agree then head to the project properties and check the box labelled Enable Code Analysis on Build. I also recommend checking the Treat warnings as errors boxes otherwise you’ll find that undisciplined developers will simply ignore the warnings.

N.B. Incidentally if you have time I highly recommend that you go and read the blog post I linked to there – Discipline Makes Strong Developers by Jeff Atwood. I’ve read many thousands of blog posts in my time and that is the one that has influenced me more than any other.

Turning on Code Analysis on a greenfield project is a no-brainer. On a brownfield project its not quite so easy – on a recent engagement I moved a single database into datadude and turned on Code Analysis which immediately found over two thousand perceived code defects. I generally abhor the use of that famous maxim if it aint broke, don’t fix it in our industry but on occasions like this you may be well advised to heed that advice and leave well alone for fear of breaking code that does what it is supposed to (no matter how inefficiently it does it). Instead you do have the option to suppress Code Analysis warnings/errors:

I advise using Code Analysis suppression sparingly. Recently I discovered that one of the developers on my team had decided it was OK to simply suppress every error that was thrown by Code Analysis without first investigating the cause. I was not amused!

Recommendation #2: Turn on Code Analysis

Realising the value of idempotency

An operation is considered idempotent if it produces the same result no matter how many times that operation is applied; for example, multiplication by a factor of one is an idempotent operation – no matter how many times you multiple a number by one the result will always be the same.

Idempotency is a vital facet of database deployment using datadude. Datadude tries to ensure that no matter how many times you deploy the same project the state of your database should be the same after each deployment. The implication here is that during a deployment datadude will examine the target database to see what changes (if any) need to be made rather than simply attempting to create lots of objects; if all the objects already exist nothing will be done. In my opinion this is the single biggest benefit of using datadude – you don’t have to determine what needs to be done to change your database schema to the desired state, datadude does it for you.

If I have convinced you about the value of idempotency within datadude then you should also realise that the same rigour should be applied to data as well. Datadude provides Post-Deployment scripts that allow you to deploy data to your schema however there is no inbuilt magic here – datadude will simply go and run those scripts as-is, it will not try and comprehend the contents of those scripts. What this means is that you, the developer, are responsible for making your Post-Deployment scripts idempotent and the easiest way to do that is to employ the T-SQL MERGE statement.

T-SQL’s INSERT is not sufficient as it will work once and thereafter fail as it will be attempting to insert already inserted data; this gives rise to my third recommendation:

Recommendation #3: When running your deployment in a test environment, run it more than once.

No-brainer Recommendations

I consider Code Analysis and Idempotency to be so important that I called them out as dedicated headlines. In this section I’ll outline some additional simple measures that you can undertake and which will, if employed correctly, have a profound effect on the success of your datadude projects.

Putting a build number into the DB

I find it is very useful to maintain a log of deployments that have been made to a database and my chosen method is to use a Post-Deployment script to insert a value into some table. Here’s the definition of the table I use for this:

CREATE TABLE [dbo].[DeployLog] ( [BuildId] NVARCHAR(50) , [DeployDatetime] SMALLDATETIME , CONSTRAINT PK_dboDeployLog PRIMARY KEY ([DeployDatetime]) )

In my Post-Deployment script I will use:

INSERT [dbo].[DeployLog]([BuildId],[DeployDatetime]) VALUES ('$(BuildId)',GETDATE());

to insert a row into that table during every deployment. $(BuildId) is a variable defined in the .sqlcmdvars file of my project:

Here is what we see inside that file:

The $(BuildId) variable has been defined with a default value of UNKNOWN and hence subsequent deployments from Visual Studio will result in the following:

On first glance that might not seem particularly useful however it comes into its own if you are doing CI deployments (see recommendation #1) because each build in a CI environment will result in a new build identifier. The following command-line call to vsdbcmd.exe is how deployments are generally done using datadude, note the presence of the /p:BuildId switch:

..\Tools\VSDBCMD\vsdbcmd.exe /Action:Deploy /ConnectionString:"Data Source=.;Integrated Security=True;Pooling=False" /p:BuildId="some-value" /DeployToDatabase:+ /ManifestFile:.\FinanceDB\sql\release\FinanceDB.deploymanifest

Your CI tool should be able to replace “some-value” with an identifier for the current build (that’s outside the scope of this blog post but any CI tool worth its salt will be able to do this) – when the deployment executes that value will then make its way into your [dbo].[DeployLog] table and you will have a self-maintaining history of all the deployments (datetime & build identifier) that have been made to your database.

Recommendation #4: Maintain an automated history of your deployments

Use Schema View

It is natural to navigate through all of the objects in your database project using Solution Explorer however datadude provides a better mechanism for doing just that – the Schema View window.

Schema View provides a logical view of all the objects defined in your database project regardless of which file they may be defined in. That is very useful for many reasons, not least because it makes it easy to locate whichever object you are after – that’s advantageous if multiple objects are defined in the same file. Moreover if some files have property BuildAction=”Not In Build” (see later) they won’t show up in Schema View (this is a good thing by the way). Schema View is also the place that operations such as refactoring and dependency analysis are launched from.

Some people think that it is important that the name of each file in a datadude project should accurately reflect the object defined within. I disagree; object renames mean that maintaining the filenames becomes laborious and having the Schema View means you never have to use the filenames to navigate your project anyway.

One final reason to use Schema View is the External Elements button:

Toggling this button on means that objects in referenced projects show up in the project that they are referenced from (this is particularly useful if you are using Composite Projects). Note in the following screenshot how the object [dbo].[t1] in project Database2 appears in the [dbo] schema of Database3 – that’s because Database3 has a reference to Database2.

For those reasons my fifth recommendation is:

Recommendation #5: Use Schema View in preference to Solution Explorer

You will still need Solution Explorer to navigate files that do not contain database objects (e.g. Post-Deployment scripts) but ordinarily you should spend most of your time interacting with Schema View.

Make liberal use of PRINT statements in Pre/Post-Deployment Scripts

When you deploy a datadude project datadude will take care of telling you what it is up to. For example, the following screenshot shows the output from deploying the already discussed [dbo].[DeplogLog]:

Of course it only does this for objects that it knows about and that doesn’t include anything in your Pre or Post \deployment scripts so you need to take responsibility for outputting pertinent information from those scripts. Here I have amended the script that inserts into [dbo].[DeployLog]:

SET NOCOUNT ON; INSERT [dbo].[DeployLog]([BuildId],[DeployDatetime]) VALUES ('$(BuildId)',GETDATE()); PRINT CAST(@@ROWCOUNT as NVARCHAR(5)) + N'rows inserted into [dbo].[DeployLog], BuildId=$(BuildId)';

This gives us much more useful output:

Adding PRINT statements to your Pre & Post Deployment scripts is so easy it really is a no-brainer and hence my next recommendation is:

Recommendation #6: Any action in a Pre or Post-Deployment Script should use PRINT to state what has been done

Output variable values in your Pre-Deployment script

This is in the same vein as the previous bullet-point – output as much information as is possible. In this case we’re talking about outputting the values of all variables that are stored in the .sqlcmdvars file; first, a reminder of what’s in that file:

Here is the contents of my amended Pre-Deployment Script:

PRINT 'DefaultDataPath=$(DefaultDataPath)'; PRINT 'DatabaseName=$(DatabaseName)'; PRINT 'DefaultLogPath=$(DefaultLogPath)'; PRINT 'BuildId=$(BuildId)';

And the resultant output:

This is the sort of simple amendment that will pay off in spades later in your project (especially if you are supplying many values from the command-line) and again, its so easy to do it there really is no reason not to. Just remember to update your Pre-Deployment script whenever you add new variables to .sqlcmdvars.

Recommendation #7: Output the value of all variables in your Pre-Deployment script

One Object Per File

Datadude doesn’t restrict what can go in a file, for example the following file, “t.table.sql”, defines three objects; a table, a primary key and a view:

Even though they’re all defined in the same file they show up in Schema View separately (one of the aforementioned benefits of using Schema View):

That said, just because you can doesn’t mean that you should. I prefer to go for one object per file for the simple reason that its easier to track the history of an object via Source Control. Moreover, if an object is no longer required then it is a simple change to just remove the file containing that object from the build (see “Don’t delete anything from your project” later) as opposed to editing a file to remove all traces of an object.

Recommendation #8: Each database object should be defined in a dedicated file

Time your Pre and Post Deployment Scripts

Its always useful to know where time is spent when doing deployments, in my experience the majority of time spent is in the Post-Deployment script (your mileage may vary of course). An easy win is to output the time taken to run your Pre and Post Deployment scripts. Adapt your Pre-Deployment script so that it looks something like this:

DECLARE @vPreDeploymentStartTime DATETIME = GETDATE(); PRINT '****************Begin Pre-Deployment script at ' +CONVERT(VARCHAR(30),GETDATE(),120) + '***********************'; /*Call other scripts from here using SQLCMD's :r syntax Example: :r .\myfile.sql */ PRINT 'Pre-Deployment duration = ' + CONVERT(VARCHAR(5),DATEDIFF(ss,@vPreDeploymentStartTime,GETDATE())) + ' seconds'; PRINT '****************End Pre-Deployment script at ' +CONVERT(VARCHAR(30),GETDATE(),120) + '***********************';

then do similar for your Post-Deployment script. When you deploy your output will include the following:

Note the lines:

    ****************Begin Pre-Deployment script at 2011-12-31 20:00:34***********************
    Pre-Deployment duration = 0 seconds
    ****************End Pre-Deployment script at 2011-12-31 20:00:34***********************


    ****************Begin Post-Deployment script at 2011-12-31 20:00:34***********************
    Post-Deployment duration = 0 seconds
    ****************End Post-Deployment script at 2011-12-31 20:00:34***********************

In this particular case its not all that useful to know that the deployment took 0 seconds but if and when your deployments snowball to many minutes it will be useful to know how long your scripts are taking at which point you can investigate further by timing each individual step in your Pre and Post Deployment scripts.

Recommendation #9: Time your deployments and output the timings

Use sqlcmdvars and the command-line as much as possible

Hardcoding any value into a piece of code is a fraught practise; you should assume that values previously thought to be constant may not be so in the future. You can protect yourself from future changes by storing all literal values as variables in your .sqlcmdvars file. Sure, you can supply default values for those variables but you have the added advantage that they can be overridden from the command-line when deploying using vsdbcmd.exe. Moreover, if you have values that are hardcoded in multiple places in your code then specifying those values in .sqlcmdvars ensures that your code adheres to the principle of DRY. Lastly, if values are stored in the .sqlcmdvars file then you can output them at deploy time (see recommendation #7).

Recommendation #10: All literal values should be stored in your .sqlcmdvars file

Every developer gets their own development database

In most SQL Server development shops that I’ve been on all developers work against a single centralised development database. To me this is an antiquated way of working because its possible that work one person is doing can conflict with that of someone else, I find it much better for every developer to work in isolation and then use the CI deployment to check that one’s code is not in conflict with anyone else’s. Datadude supports (nay encourages) this way of working with the notion of an Isolated Development Environment:

Every developer should configure their isolated development environment which, typically, would be their local instance. And so to my next recommendation:

Recommendation #11: Every developer should use the Isolated Dev Environment settings in order to author their code

Incidentally, if every developer has their own development database and you are following my earlier recommendation to use a [DeployLog] table then you can track how often a developer is bothering to deploy and test their code. On a recent project we used this evidence in a (ahem) discussion with a developer who tried to convince us that he was testing his code sufficiently even though he was repeatedly causing the CI deployment to fail.

Don’t delete anything from your project

When projects are no longer required in your database then intuitively it makes sense to remove the file containing that object from the datadude project, I would however like to suggest a different approach. Rather than removing a file just change the Build property to “Not in Build”:

This has the advantage that your project maintains some semblance of history of what objects have been removed from your database – that can be useful to anyone inheriting your code in the future.

Recommendation #12: Use “Not in Build” to remove an object from your database

Build and Deploy your datadude projects outside of Visual Studio

Building and deploying your datadude projects within Visual Studio can become a real time hog; in my experience its not unusual for deployments to take many minutes and your Visual Studio environment will be unavailable for further development work during that time. For that reason I recommend investing some time in writing some msbuild scripts that will build and deploy your project(s) from the command-line. Here are some examples that you can adapt for your own use, firstly a script to build a solution:

<?xml version="1.0" encoding="utf-8"?>

<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003"
          DefaultTargets="Build">

< ItemGroup >
    
    <Config Include="Debug" />
    <Config Include="Release" />
</ ItemGroup >
<Target Name="Build">
    <Message Text="Building %(Config.Identity) configuration..."/>
    <MSBuild Projects=".\Lloyds.UKTax.DB.sln" Properties="Configuration=%(Config.Identity)" />
</ Target >
</ Project >

and secondly a script that will deploy a datadude project:

<?xml version="1.0" encoding="utf-8"?>

<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003"
          DefaultTargets="Build;Deploy">
< PropertyGroup >
    
    <Configuration>Debug</Configuration>
    <DevServer>Data Source=GBS0039182\GLDDEV01;Integrated Security=True;Pooling=False</DevServer>
</ PropertyGroup >

< ItemGroup >
    <ProjectToBuild Include="SolutionBuild.proj" />
</ ItemGroup >


< ItemGroup >
    <DbProj Include="username">
      <DbName>MyDB</DbName>
      <ProjectName>MySoln.MyDB</ProjectName>
      <OutputPath>\%(ProjectName)\sql\$(Configuration)\\cf1 </OutputPath>
      <DeployConnStr>Data Source=localhost;Integrated Security=True;Pooling=False</DeployConnStr>
    </ DbProj >
</ ItemGroup >

<Target Name="Build">
    <MSBuild Projects="@(ProjectToBuild)" />
</ Target >

<Target Name="Deploy">
    
    <Message Text="USERNAME=$(USERNAME)" />
    <Message Condition="'%(DbProj.Identity)'==$(USERNAME)" Text="Deploying:
     Project%09%09%09: %(DbProj.ProjectName)
     DbName%09%09%09: %(DbProj.DbName)
     From OutputPath%09%09: %(DbProj.OutputPath)
     To ConnStr%09%09: %(DbProj.DeployConnStr)
     By%09%09%09: %(DbProj.Identity)"
     />
    <Exec Condition="'%(DbProj.Identity)'==$(USERNAME)" Command=""$(VSINSTALLDIR)\vstsdb\deploy\vsdbcmd.exe"
            /Action:Deploy /ConnectionString:"%(DbProj.DeployConnStr)" /DeployToDatabase+
            /manifest:".%(DbProj.OutputPath)%(DbProj.ProjectName).deploymanifest" /p:TargetDatabase=%(DbProj.DbName)
            /p:Build="from cmd line"" />
</ Target >
</ Project >

Writing these scripts may appear to be laborious but they’ll save you heaps of time in the long run.

Recommendation #13: Build and deploy to your development sandbox using scripts

UPDATE: Upon reading this blog post Mordechai Danielov wrote a follow-up in which he published a useful script that builds a series of projects using Powershell. Its at building your database solutions outside of Visual Studio.

Useful links

Over the years I’ve collected some links to MSDN articles that have proved invaluable:

Datadude bugs

Like any substantial piece of software datadude is not without bugs. Many of the issues I have found are concerned with the datadude interpreter not correctly parsing T-SQL code, here’s a list of some bugs that I have found down the years:

Some of these bugs were reported a long time ago and may well have been fixed in later service packs.

Previous datadude blog posts

I have blogged on datadude quite a bit in the past:

Summing up

This has been an inordinately large blog post so if you’ve read this far – well done. For easy reference, here are all the recommendations that I have made:

Use Source Control and implement a Continuous Integration deployment
Turn on Code Analysis
When running your deployment in a test environment, run it more than once
Maintain an automated history of your deployments
Use Schema View in preference to Solution Explorer
Any action in a Pre or Post-Deployment Script should use PRINT to state what has been done
Output the value of all variables in your Pre-Deployment script
Each database object should be defined in a dedicated file
Time your deployments and output the timings
All literal values should be stored in your .sqlcmdvars file
Every developer should use the Isolated Dev Environment settings in order to author their code
Use “Not in Build” to remove an object from your database
Build and deploy to your development sandbox using scripts

I really hope this proves useful because its taken a good long while to get it published If you have any feedback then please let me know in the comments.

Thanks for reading!

@jamiet

* When I started writing this blog post the first sentence was “Over the past six months I have worked on two separate projects for customers that wanted to make use of Visual Studio 2010 Database projects to manage their database schema.” as opposed to what it is now: “Over the past eighteen months I have worked on four separate projects…” Yes, that’s how long its taken to write it!

Appendix – An example CI configuration

As stated above, an earlier draft of this blog post included full details of the CI configuration from one of the projects that I have worked on. Although it may repeat some of what has already been said I have included that text below.

==============================================================

Introduction

This project has invested heavily in using a Continuous Integration (CI) approach to development. What that means, succinctly, is that whenever someone checks-in some code to our source control system an automated build process is kicked off that constructs our entire system from scratch on a dedicated server. CI is not a new concept but it is fairly rare that anyone applies the same rigour to their database objects as they do to so-called “application code” (e.g. the stuff written in .Net code) and on this project we have made a conscious decision to properly build our databases as part of the CI build.

Datadude employs a declarative approach to database development. In other words you define what you want database schema to look like and datadude will work out what it needs to do to your target in order to turn it into what you have defined. What this means in practice is that you only ever write CREATE … DDL statements rather than IF <object-exists> THEN ALTER …ELSE CREATE … statements which is what you may have done in the past.

Here’s our CI environment setup:

SubVersion (SVN) is being used for source control
Hudson, an open source CI server, is being used to orchestrate our whole CI environment. It basically monitors our SVN repository and when it spots a checked-in file, kicks off the CI build
Our CI scripts (the stuff that actually does the work) are written using msbuild
We have 2 msbuild scripts:
- Build.proj which is responsible for:
  - Compiling all our .Net website code
  - Building/Compiling our datadude projects (every datadude project file is a msbuild-compliant script)
- Deploy.proj which is responsible for:
  - Restoring latest database backups from our production environment into our CI environment
  - Deploying our built datadude projects on top of those restored backups
  - Build a folder structure to hold all the artefacts that get deployed
  - Creating folder shares
  - Moving SSIS packages into folder structure
  - Deploying SSRS reports to our SSRS server
  - Deploy our Analysis Services cube definitions to our Analysis Server
Both Build.proj and Deploy.proj get executed by our CI build

Building datadude projects

Datadude makes it very easy to build datadude projects in a CI environment because they are already msbuild-compliant; its simply a call to the MSBuild task, passing in the location of the solution file as an argument. We use the Release configuration (although there is no particular reason for you to do the same – purely your choice):

That’s it! The output from a datadude build includes a number of files but the most important one is a .dbschema file which is an XML representation of all the objects in your database.

Deploying the output from a built datadude project

This is a little more difficult. We *could* simply use the MSBuild task to call our deployment script as we do for build script (see above) but the problem with that is that there are many pre-requisites (including datadude itself) and we don’t want to install Visual Studio and all the assorted paraphernalia onto our various environments. Instead we chose to make use of a command-line tool called VSDBCMD.exe to deploy datadude projects. VSDBCMD does basically the same job as what happens if you were to right-click on a datadude project in Visual Studio and select “Deploy” i.e. It compares the output of a build (A) to the target database (B) and works out what it needs to do to make B look like A. It then produces a .sql script that will actually make those requisite changes, then goes and executes it.

The difficulty comes in VSDBCMD.exe having its own list of file dependencies that are listed at MSDN article How to: Prepare a Database for Deployment From a Command Prompt by Using VSDBCMD.EXE, thankfully a much smaller list then if we were using the MSBuild task.

Some of those files, namely:

Sqlceer35en.dll
Sqlceme35.dll
Sqlceqp35.dll
Sqlcese35.dll
System.Data.SqlServerCe.dll

get installed with SQL Server CE. We bundle along the x86 & x64 installers for SQL Server CE along with all the rest of our deployment artefacts and then, as part of Deploy.proj, install them like so:

That takes care of some of the dependencies but we still have to take care of:

DatabaseSchemaProviders.Extensions.xml
Microsoft.Data.Schema.dll
Microsoft.Data.Schema.ScriptDom.dll
Microsoft.Data.Schema.ScriptDom.Sql.dll
Microsoft.Data.Schema.Sql.dll
Microsoft.SqlServer.BatchParser.dll

as well as the actual VSDBCMD.exe file itself. Quite simply we keep those files in SVN and then bundle them along with all our deployment artefacts (I won’t show you how we do that because its out of the scope of this post and besides if you’re at all proficient with msbuild then you’ll know how to do that and if you’re not, well, why are you reading this?)

==============================================================

Workaround for datadude deployment bug - NullReferenceException

jamiet — Mon, 21 Nov 2011 08:56:00 GMT

I have come across a bug in Visual Studio 2010 Database Projects (aka datadude aka DPro aka Visual Studio Database Development Tools aka Visual Studio Team Edition for Database Professionals aka Juneau aka SQL Server Data Tools) that other people may encounter so, for the purposes of googling, I'm writing this blog post about it. Through my own googling I discovered that a Connect bug had already been raised about it (VS2010 Database project deploy - “SqlDeployTask” task failed unexpectedly, NullReferenceException), and coincidentally enough it was raised by my former colleague Tom Hunter (whom I have mentioned here before as the superhuman Tom Hunter) although it has not (at this time) received a reply from Microsoft. Tom provided a repro, namely that this syntactically valid function definition:

CREATE FUNCTION [dbo].[Function1]
()
RETURNS TABLE
AS
RETURN (
    WITH cte AS (
    SELECT 1 AS [c1]
    FROM [$(Database3)].[dbo].[Table1]
   )
   SELECT 1 AS [c1]
   FROM cte
)

would produce this nasty unhelpful error upon deployment:

C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.TSqlTasks.targets(120,5): Error MSB4018: The "SqlDeployTask" task failed unexpectedly.
System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.Data.Schema.Sql.SchemaModel.SqlModelComparerBase.VariableSubstitution(SqlScriptProperty propertyValue, IDictionary`2 variables, Boolean& isChanged)
   at Microsoft.Data.Schema.Sql.SchemaModel.SqlModelComparerBase.ArePropertiesEqual(IModelElement source, IModelElement target, ModelPropertyClass propertyClass, ModelComparerConfiguration configuration)
   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareProperties(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, ModelComparisonChangeDefinition changes)
   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithoutCompareName(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, ModelComparisonResult result, ModelComparisonChangeDefinition changes)
   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithSameType(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, ModelComparisonResult result, Boolean ignoreComparingName, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, Boolean compareFromRootElement, ModelComparisonChangeDefinition& changes)
   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareChildren(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, Boolean parentExplicitlyIncluded, Boolean compareParentElementOnly, ModelComparisonResult result, ModelComparisonChangeDefinition changes, Boolean isComposing)
   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithoutCompareName(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, ModelComparisonResult result, ModelComparisonChangeDefinition changes)
   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithSameType(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, ModelComparisonResult result, Boolean ignoreComparingName, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, Boolean compareFromRootElement, ModelComparisonChangeDefinition& changes)
   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareChildren(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, Boolean parentExplicitlyIncluded, Boolean compareParentElementOnly, ModelComparisonResult result, ModelComparisonChangeDefinition changes, Boolean isComposing)
   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithoutCompareName(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, ModelComparisonResult result, ModelComparisonChangeDefinition changes)
   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithSameType(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, ModelComparisonResult result, Boolean ignoreComparingName, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, Boolean compareFromRootElement, ModelComparisonChangeDefinition& changes)
   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareAllElementsForOneType(ModelElementClass type, ModelComparerConfiguration configuration, ModelComparisonResult result, Boolean compareOrphanedElements)
   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareStore(ModelStore source, ModelStore target, ModelComparerConfiguration configuration)
   at Microsoft.Data.Schema.Build.SchemaDeployment.CompareModels()
   at Microsoft.Data.Schema.Build.SchemaDeployment.PrepareBuildPlan()
   at Microsoft.Data.Schema.Build.SchemaDeployment.Execute(Boolean executeDeployment)
   at Microsoft.Data.Schema.Build.SchemaDeployment.Execute()
   at Microsoft.Data.Schema.Tasks.DBDeployTask.Execute()
   at Microsoft.Build.BackEnd.TaskExecutionHost.Microsoft.Build.BackEnd.ITaskExecutionHost.Execute()
   at Microsoft.Build.BackEnd.TaskBuilder.ExecuteInstantiatedTask(ITaskExecutionHost taskExecutionHost, TaskLoggingContext taskLoggingContext, TaskHost taskHost, ItemBucket bucket, TaskExecutionMode howToExecuteTask, Boolean& taskResult)
   Done executing task "SqlDeployTask" -- FAILED.
Done building target "DspDeploy" in project "Lloyds.UKTax.DB.UKtax.dbproj" -- FAILED.
Done executing task "CallTarget" -- FAILED.
Done building target "DBDeploy" in project

It turns out there are a certain set of circumstances that need to be met for this error to occur:

The object being deployed is an inline function (may also exist for multistatement and scalar functions - I haven't tested that)
That object includes SQLCMD variable references
The object has already been deployed successfully

Just to reiterate that last bullet point, the error does not occur when you deploy the function for the first time, only on the subsequent deployment.

Luckily I have a direct line into a guy on the development team so I fired off an email on Friday evening and today (Monday) I received a reply back telling me that there is a simple fix, one simply has to remove the parentheses that wrap the SQL statement. So, in the case of Tom's repro, the function definition simply needs to be changed to:

CREATE FUNCTION [dbo].[Function1] () RETURNS TABLE AS RETURN --( WITH cte AS ( SELECT 1 AS [c1] FROM [$(Database3)].[dbo].[Table1] ) SELECT 1 AS [c1] FROM cte --)

I have commented out the offending parentheses rather than removing them just to emphasize the point.

Thereafter the function will deploy fine. I tested this out on my own project this morning and can confirm that this fix does indeed work.

I have been told that the bug CAN be reproduced in the Release Candidate (RC) 0 build of SQL Server Data Tools in SQL Server 2012 so am hoping that a fix makes it in for the Release-To-Manufacturing (RTM) build.

Hope this helps

@jamiet

Building a database installer with WiX, datadude and Visual Studio 2010

jamiet — Thu, 10 Feb 2011 21:45:17 GMT

Today I have been using Windows Installer XML (WiX) to build an installer (.msi file) that would install a SQL Server database on a server of my choosing; the source code for that database lives in datadude (a tool which you may know by one of quite a few other names). The basis for this work was a most excellent blog post by Duke Kamstra entitled Implementing a WIX installer that calls the GDR version of VSDBCMD.EXE which coves the delicate intricacies of doing this – particularly how to call Vsdbcmd.exe in a CustomAction. Unfortunately there are a couple of things wrong with Duke’s post:

Searching for “datadude wix” didn’t turn it up in the first page of search results and hence it took me a long time to find it. And I knew that it existed. If someone else were after a post on using WiX with datadude its likely that they would never have come across Duke’s post and that would be a great shame because its the definitive post on the matter.
It was written in October 2009 and had not been updated for Visual Studio 2010.

Well, this blog post is an attempt to solve those problems. Hopefully I’ve solved the first one just by following a few of my blogging SEO tips while writing this blog post, in the rest of it I will explain how I took Duke’s code and updated it to work in Visual Studio 2010.

If you need to build a database installer using WiX, datadude and Visual Studio 2010 then you still need to follow Duke’s blog post so go and do that now. Below are the amendments that I made that enabled the project to get built in Visual Studio 2010:

In VS2010 datadude’s output files have changed from being called Database.<suffix> to <ProjectName>_Database.<suffix>. Duke’s code was referencing the old file name formats.
Duke used $(var.SolutionDir) and relative paths to point to datadude artefacts I have replaced these with Votive Project References http://wix.sourceforge.net/manual-wix3/votive_project_references.htm
I commented out all references to MicrosoftSqlTypesDbschema in DatabaseArtifacts.wxi. I don't think this is produced in VS2010 (I may be wrong about that but it wasn't in the output from my project)
Similarly I commented out component MicrosoftSqlTypesDbschema in VsdbcmdArtifacts.wxi. It wasn't where Duke's code said it should have been so am assuming/hoping it isn't needed.
Duke's ?define block to work out appropriate SrcArchPath actually wasn't working for me (i.e. <?if $(var.Platform)=x64 ?> was evaluating to false) so I just took out the conditional stuff and declared the path explicitly to the “Program Files (x86)” path. The old code is still there though if you need to put it back.
None of the <RegistrySearch> stuff is needed for VS2010 - so I commented it all out!
Changed to use /manifest option rather than /model option on vsdbcmd.exe command-line. Personal preference is all!
Added a new component in order to bundle along the vsdbcmd.exe.config file
Made the install of the Custom Action dependent on the relevant feature being selected for install. This one is actually really important – deselecting the database feature for installation does not, by default, stop the CustomAction from executing and so would cause an error - so that scenario needs to be catered for

I have made my amended solution available for download at: http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20110210/InstallMyDatabase.zip It contains two projects: the WiX project and the datadude project that is the source to be deployed (for demo purposes it only contains one table).

I have also made the .msi available although in order that it gets through file blockers I changed the name from InstallMyDatabase.msi to InstallMyDatabase.ms_ – simply rename the file back once you have downloaded it from: http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20110210/InstallMyDatabase.ms%5E_ .You can try it out for yourself – the only thing it does is dump the files into %Program Files%\MyDatabase and uses them to install a database onto a server of your choosing with a name of your choosing - no damaging side-affects. I will caveat this by saying “it works on my machine” and, not having access to a plethora of different machines, I haven’t tested it anywhere else. One potential issue that I know of is that Vsdbcmd.exe has a dependency on SQL Server CE although if you have SQL Server tools or Visual Studio installed you should be fine. Unfortunately its not possible to bundle along the SQL Server CE installer in the .msi because Windows will not allow you to call one installer from inside another – the recommended way to get around this problem is to build a bootstrapper to bundle the whole lot together but doing that is outside the scope of this blog post. If you discover any other issues then please let me know.

Here are the screenshots from the installer:

And once installed….

Hope this is useful!

@jamiet

Generate multiple SqlCmdVars files in your database projects

jamiet — Wed, 19 Jan 2011 20:24:59 GMT

Earlier today I was doing a little work using datadude/DBPro/Visual Studio Database Tools/pick your name and had a need to write a Powershell script that I think might be useful to other folks so I’m sharing it here.

Often when you’re putting together database projects you will have a need for multiple .sqlcmdvars files – one for each environment that you are deploying to. It can be a real pain in the neck maintaining multiple .sqlcmdvars files because you need to make sure that:

each file has the same list of sqlcmd variables in it
the variables that need to be different per environment have got the correct values in them

If you have multiple environments and multiple projects then this can become a real headache to maintain – indeed, the project that I am working on at the moment has got 192 sqlcmdvars files. You can trust me that that is pretty excessive however this problem is still one that many people are going to encounter.

So, when you have 192 occurences of the same problem it seems logical to write a script to carry out the repetitive task of editing those files and that is exactly what I did today. My Powershell script requires that you maintain ONE .sqlcmdvars file per project and will then take care of generating the others and also replacing environment-specific variable values where appropriate. You need to supply a list of a projects, a list of environments and, for each environment, a value for each variable that you want to change. the example below has only one project, three environments (thus three .sqlcmdvars files) and one environment-specific variable. If you have anything more complex than that then the script is very easy to change and is self-explanatory.

Here’s a screenshot of my demo project:

Note the following:

There are three sqlcmdvars files {development.sqlcmdvars,production.sqlcmdvars,test.sqlcmdvars}
development.sqlcmdvars contains a variable called $(EmailAddress) that is not in either of the other two
My Powershell script is stored as a solution file

Here is the script:

#This script copies the contents of each <defined>.sqlcmdvars into all the other .sqlcmdvars files for that project
#It should live in the root folder of your solution
#If your sqlcmdvars files are under source control don’t forget to check them out
cls
$masterSqlCmdVars = "development"    #Edit these two
$variableName = "EmailAddress"        #values accordingly

$DbArray = ('20110119 Test Sqlcmdvars Generator') #List all your .dbproj files
foreach ($Db in $DbArray)
{
    #List of environments and the property values for each respective environment
    $EnvArray = (
                    ('test','test@example.com'),
                    ('production','production@example.com')
                )
    foreach ($Env in $EnvArray)
    {
        #STEP 1 - copy the file
        $EnvName = $Env[0]
        $VarValue = $Env[1]
        $source = "$Db\Properties\$masterSqlCmdVars.sqlcmdvars"
        $destination = "$Db\Properties\$EnvName.sqlcmdvars"
        "Copying $source to $destination"
        copy $source $destination

        #STEP 2 - edit with new variable values
        $xml = [xml](Get-Content $destination)
        $root = $xml.get_DocumentElement();
        foreach ($property in $root.Properties.ChildNodes)
        {
            if ($property.PropertyName -eq $variableName)
            {
                $property.Propertyvalue = $VarValue
            }
        }
        "Saving modified XML to $destination"
        $destination = resolve-path $destination
        $xml.Save($destination)
    }
}

It has an outer loop for all the projects and an inner loop for each environment. On each iteration it copies the contents of development.sqlcmdvars into $EnvName.sqlcmdvars and then updates the file with the new value for $(EmailAddress). If you have more variables that you need to edit then its not a great hardship to adapt the script accordingly.

Upon completion our test.sqlcmdvars and production.sqlcmdvars files have been edited as so:

That’s it. Pretty simple. I won’t go deep into explaining the script because if this is useful to you you will probably want to do that yourself. This demo project, including the Powershell script, is available for download at http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20110119/20110119%20Test%20Sqlcmdvars%20Generator.zip.

@Jamiet

UPDATE: I have discovered an alternative method courtesy of Robert Robelo and I like this one because it uses XPath which to me feels like a purer way of doing this – I can’t explain why so don’t ask! Here’s is Robert’s code which achieves the same as above:

cls
$masterSqlCmdVars = "development"    #Edit these two
$variableName = "EmailAddress"        #values accordingly

$DbArray = ('20110119 Test Sqlcmdvars Generator') #List all your .dbproj files
foreach ($Db in $DbArray)
{
    #List of environments and the property values for each respective environment
    $EnvArray = (
                    ('test','test@example.com'),
                    ('production','production@example.com')
                )
    foreach ($Env in $EnvArray)
    {
        #STEP 1 - copy the file
        $EnvName = $Env[0]
        $VarValue = $Env[1]
        $source = "$Db\Properties\$masterSqlCmdVars.sqlcmdvars"
        $destination = "$Db\Properties\$EnvName.sqlcmdvars"
        "Copying $source to $destination"
        copy $source $destination
        
        [xml]$xml = Get-Content $destination
        $nsMgr = New-Object Xml.XmlNamespaceManager $xml.NameTable
        $prefix = 'ns'
        $uri = 'urn:Microsoft.VisualStudio.Data.Schema.Package.SqlCmdVars'
        $nsMgr.AddNamespace($prefix, $uri)
        $xpath = "//${prefix}:Property[./${prefix}:PropertyName='$variableName']"
        $node = $xml.SelectSingleNode($xpath, $nsMgr)
        $node.PropertyValue = $VarValue
        $xml.Save($destination)
        Get-Content $destination
    }
}

I have also updated the downloadable file at http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20110119/20110119%20Test%20Sqlcmdvars%20Generator.zip with Robert’s code.

Querying Visual Studio project files using T-SQL and Powershell

jamiet — Mon, 17 Jan 2011 21:11:00 GMT

Earlier today I had a need to get some information out of a Visual Studio project file and in this blog post I’m going to share a couple of ways of going about that because I’m pretty sure I won’t be the only person that ever wants to do this. The specific problem I was trying to solve was finding out how many objects in my database project (i.e. in my .dbproj file) had any warnings suppressed but the techniques discussed below will work pretty well for any Visual Studio project file because every such file is simply an XML document, hence it can be queried by anything that can query XML documents.

Ever heard the phrase “when all you’ve got is hammer everything looks like a nail”? Well that’s me with querying stuff – if I can write SQL then I’m writing SQL. Here’s a little noddy database project I put together for demo purposes:

Two views and a stored procedure, nothing fancy. I suppressed warnings for [View1] & [Procedure1] and hence the pertinent part my project file looks like this:

  <ItemGroup>
    <Build Include="Schema Objects\Schemas\dbo\Views\View1.view.sql">
      <SubType>Code</SubType>
      <SuppressWarnings>4151,3276</SuppressWarnings>
    </Build>
    <Build Include="Schema Objects\Schemas\dbo\Views\View2.view.sql">
      <SubType>Code</SubType>
    </Build>
    <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procedure1.proc.sql">
      <SubType>Code</SubType>
      <SuppressWarnings>4151</SuppressWarnings>
    </Build>
  </ItemGroup>
  <ItemGroup>

Note the <SuppressWarnings> elements – those are the bits of information that I am after.

With a lot of help from folks on the SQL Server XML forum I came up with the following query that nailed what I was after. It reads the contents of the .dbproj file into a variable of type XML and then shreds it using T-SQL’s XML data type methods:

DECLARE @xml XML; 
SELECT @xml = CAST(pkgblob.BulkColumn AS XML) 
FROM   OPENROWSET(BULK 'C:\temp\QueryingProjectFileDemo\QueryingProjectFileDemo.dbproj' -- <-Change this path! 
                   ,single_blob) AS pkgblob 
                   
;WITH XMLNAMESPACES( 'http://schemas.microsoft.com/developer/msbuild/2003' AS ns) 
SELECT  REVERSE(SUBSTRING(REVERSE(ObjectPath),0,CHARINDEX('\',REVERSE(ObjectPath)))) AS [ObjectName] 
       ,[SuppressedWarnings] 
FROM   ( 
       SELECT  build.query('.') AS [_node] 
       ,       build.value('ns:SuppressWarnings[1]','nvarchar(100)') AS [SuppressedWarnings] 
       ,       build.value('@Include','nvarchar(1000)') AS [ObjectPath] 
       FROM    @xml.nodes('//ns:Build[ns:SuppressWarnings]') AS R(build) 
       )q

And here’s the output:

And that’s it – an easy way of discovering which warnings have been suppressed and for which objects in your database projects. I won’t bother going over the code as it is fairly self-explanatory – peruse it at your leisure.

Once I had the SQL above I figured I’d share it around a little in case it was ever useful to anyone else; hence I’m writing this blog post and I also posted it on the Visual Studio Database Development Tools forum at FYI: Discover which objects have had warnings suppressed. Luckily Kevin Goode saw the thread and he posted a different solution to the same problem, one that uses Powershell. The advantage of Kevin’s Powershell approach is that it is easy to analyse many .dbproj files at the same time. Below is Kevin’s code which I have tweaked ever so slightly so that it produces the same results as my SQL script (I just want any object that had had a warning suppressed whereas Kevin was querying specifically for warning 4151):

cd 'C:\Temp\QueryingProjectFileDemo\'
cls
$projects = ls -r -i *.dbproj
Foreach($project in $projects)
{
  $xml = new-object System.Xml.XmlDocument
  $xml.set_PreserveWhiteSpace( $true )
  $xml.Load($project)

  #$xpath = @{Start="/e:Project/e:ItemGroup/e:Build[e:SuppressWarnings=4151]/@Include"}
  #$xpath = @{Start="/e:Project/e:ItemGroup/e:Build[contains(e:SuppressWarnings,'4151')]/@Include"}
  $xpath = @{Start="/e:Project/e:ItemGroup/e:Build[e:SuppressWarnings]/@Include"}
  $ns = @{ e = "http://schemas.microsoft.com/developer/msbuild/2003" }

  $xml | Select-Xml -XPath $xpath.Start  -Namespace $ns |Select -Expand Node | Select -expand Value
}

and here’s the output:

Nice reusable Powershell and SQL scripts – not bad for an evening’s work. Thank you to Kevin for allowing me to share his code.

Don’t forget that these techniques can easily be adapted to query any Visual Studio project file, they’re only XML documents after all! Doubtless many people out there already have code for doing this but nonetheless here is another offering to the great script library in the sky. Have fun!

@Jamiet

This blog post was mentioned on This Week on Channel 9. Fast forward to 10m49s.

Maintaining version history in your database using Visual Studio 2010

jamiet — Tue, 19 Oct 2010 19:26:14 GMT

I have written a few blogs lately explaining how my current project is employing the use of datadude (aka the database development tools in Visual Studio 2010) in order to manage our database code, deployment of that code and also testing of it. In this blog post I’m going to share a little technique that we use in order to store a version history of our deployments. Note that this assumes a working knowledge of datadude so if you don’t know what the terms Post-Deployment script, SQLCMD variables, Continuous Integration & msbuild refer to then maybe this blog post is not for you!

Need a table

Firstly, we need a table to store our version history, in our case we have called it [BuildVersion] and it looks like this:

                     
       CREATE TABLE [dbo].[BuildVersion]         
(         
    [BuildVersion] NVARCHAR(20) NOT NULL,         
    [Deployed]     DATETIME NOT NULL         
)

Need a project variable

We have a SQLCMD variable declared as part of our project that is intended to hold a version number. In datadude SQLCMD variables are (by default) declared in a file called Database.sqlcmdvars:

In our case we have a variable called ‘BuildVersion’ that we default to the value of “Unknown”

Need to populate the table

We use the value in our ‘BuildVersion’ variable in order to populate our [BuildVersion] table and we do that within a Post-Deployment script using the following simple code:

INSERT [dbo].[BuildVersion]([BuildVersion], [Deployed]) VALUES ('$(BuildVersion)', GETDATE()) --$(BuildVersion is a SQLCMD variable declared within this project)

And of course we need to tell our mechanism what the build number is

We need to make sure that $(BuildVersion) has a value in it. As we are deploying our database as part of a continuous integration build (leveraging msbuild) we can pass in a value from the msbuild script. Here’s the code that we use to do that:

<Exec Command="..\VSDBCMD\vsdbcmd.exe /Action:Deploy /ConnectionString:"Data Source=$(Server);Integrated Security=True" /DeployToDatabase:+ /ManifestFile:"..\MyDB.deploymanifest" /p:BuildVersion="$(BuildLabel)"" />

The important bit for what we are discussing herein is that last underlined part:

/p:BuildVersion="$(BuildLabel)"

The /p directive is used to populate a named variable (in our case BuildVersion) with a value. In our case than value is taken from an msbuild property which, in our case, is referred to by $(BuildLabel). [Note that managing msbuild properties is outside the scope of this blog post.]

Wrap-up

That’s pretty much all you need. If it all hangs together correctly then [BuildVersion] will contain a nice history of all your deployments like so:

Hope this is useful! I suspect the same technique will work in earlier versions of datadude but I don’t have any to hand so can’t find out.

@Jamiet

Enforcing naming conventions using database unit testing

jamiet — Tue, 05 Oct 2010 22:42:17 GMT

My naming convention obsession

Anyone that has ever worked with me will tell you that I am a stickler for naming conventions. I have a somewhat obsessive reputation for it; I can’t help it – I seem to have a deep seated uncontrollable desire to ensure that every object in my database(s) is/are named consistently (is there anyone else out there equally as obsessive?).

I have tried various techniques down the years to try and enforce naming conventions but none of them really worked. I’ve got scripts that alter object names (such a script is in my script library in fact) but these are only any use if they actually get run, they don’t actually enforce the conventions – that’s a manual step. I’ve thought about using Policy-Based Management (PBM) to enforce naming conventions but given I’m a developer and not a DBA that’s not something that is readily available to me and besides, using PBM to enforce naming conventions is reactive rather than proactive if you are developing the code on a machine where the policies are not enforced.

Another option I looked into using was Team Foundation Server (TFS) check-in policies; these are policies that can be applied to artefacts when they get checked-in to TFS’s source control system. This option really appealed to me because the naming conventions could be enforced during check-in (i.e. very very early) and didn’t require DBA intervention. In practice though enforcing naming conventions using TFS check-in policies has a few sizable issues:

Its not easy. It would require you to parse the file that was getting checked-in, decide what sort of object is defined in the file, and then check the name of the object based on things like object name, schema, etc...
TFS check-in policies are not installed on the TFS server, they are installed on the development workstations. This means there is a dependency and, even though the source code for the check-in policies can be distributed with your application source code, I didn’t really like this.
You’re relying on each developer to enforce the check-in policy and with the greatest will in the world….that aint gonna happen. Its too easy to turn them off.
There is the obvious dependency on using TFS, not something every development shop uses even in the Microsoft space.

Database unit testing to the rescue

No, a better solution was needed and I came up with one in the shape of automated database unit testing. I have spoken recently about how I have become a big fan of database unit testing (see my post Experiences from writing sp_CascadingDataViewer – DB unit testing and code distribution) and being able to enforce naming conventions is one very good reason for that. Enforcing naming conventions using automated unit tests has a number of advantages:

They can be written against the metadata of the objects themselves (i.e. by querying SQL Server’s system views) so there’s no parsing that needs to be done.
They can be employed as part of a Continuous Integration (CI) process and run as a build verification test (BVT). Someone checks-in an object that violates the naming convention? Bang: broken build!
Developers can’t circumvent the tests.
Nothing needs to be installed on the development workstations. The tests live wholly as part of your source code.
Not dependent on use of a particular source control system

Hence I have written some unit tests that enforce the following naming conventions:

Check constraints must be of the form CK_<schemaName><tableName>_XXX
Column names must begin with a capital letter
Column names cannot contain underscores
Default constraints must be named DF_<schemaName><tableName>_<ColumnName>
Foreign keys must be of the form FK_<parentObjectSchema><parentObject>_REF_<referencedObjectSchema><referencedObject>XXX
Non-unique clustered keys must be of the form IXC_<schemaName<TableName>_<Column><Column><Column>…
Non-unique non-clustered keys must be of the form IX_<schemaName><TableName>_<Column><Column><Column>...
Unique clustered keys must be of the form IXUN_<schemaName><TableName>_<Column><Column><Column>…
Unique non-clustered keys must be of the form IXUN_<schemaName><TableName>_<ColumnColumnColumn>...
Primary keys must be of the form PK_<schemaName><tableName>
Stored procedure names should not contain underscores
Stored procedure names must begin with a capital letter
Table names must not contain underscores
Table names must begin with a capital letter

I’m not stating that you should agree with these naming conventions (I don’t necessarily agree with them myself – they were defined before I arrived on my current project), the point here is that all of these rules can be enforced and its very easy to do it. Here’s the code for the unit test that enforces the primary key naming convention:

   /*PK name is PK_<schemaName><tableName>*/       
   SET NOCOUNT ON       
   DECLARE @cnt    INT;       
        
   SELECT  *       
   INTO    #t       
   FROM    (       
           SELECT  OBJECT_NAME(c.[parent_object_id]) AS [TableName],OBJECT_SCHEMA_NAME(c.[parent_object_id]) AS [SchemaName],c.*       
           FROM    [sys].[key_constraints] c       
           INNER JOIN [sys].[tables] t        
               ON  c.[parent_object_id] = t.[object_id]       
           LEFT OUTER JOIN sys.extended_properties ep       
               ON  t.[object_id] = ep.major_id       
               AND ep.[name] = 'microsoft_database_tools_support'       
           WHERE ep.[major_id] IS NULL       
           AND c.[type] = 'PK'       
           )q       
   WHERE   [name] <> N'PK_' + [SchemaName] + [TableName]       
        
   SET     @cnt = @@ROWCOUNT;       
   IF  (@cnt > 0)       
   BEGIN       
           DECLARE @msg NVARCHAR(2048);       
           SELECT  @msg = '%d Primary Keys do not conform to naming convention (PK_<schemaName><tableName>):' + STUFF((SELECT ', ' + [name] FROM #t a FOR XML PATH('') ),1,2,'')       
           FROM    (       
                   SELECT  1 AS [Id],*       
                   FROM    #t t       
                   )q       
           GROUP   BY q.[Id]       
           SELECT @msg       
           RAISERROR(@msg,11,1,@cnt);       
   END

Essentially all it does is pull all of the primary keys out of [sys].[key_constraints], checks to see what the name should be, then if it finds any that violate the naming convention raise an error containing the names of all the primary keys in question. Here’s the error obtained when running the test against [AdventureWorks] (I’ve highlighted the pertinent bit):

Test method Prs.SchemaTests.NamingConventions.PrimaryKeys threw exception:
System.Data.SqlClient.SqlException: 70 Primary Keys do not conform to naming convention (PK_<schemaName><tableName>):PK_ErrorLog_ErrorLogID, PK_Address_AddressID, PK_AddressType_AddressTypeID, PK_AWBuildVersion_SystemInformationID, PK_BillOfMaterials_BillOfMaterialsID, PK_Contact_ContactID, PK_ContactCreditCard_ContactID_CreditCardID, PK_ContactType_ContactTypeID, PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode, PK_CountryRegion_CountryRegionCode, PK_CreditCard_CreditCardID, PK_Culture_CultureID, PK_Currency_CurrencyCode, PK_CurrencyRate_CurrencyRateID, PK_Customer_CustomerID, PK_CustomerAddress_CustomerID_AddressID, PK_DatabaseLog_DatabaseLogID, PK_Department_DepartmentID, PK_Document_DocumentID, PK_Employee_EmployeeID, PK_EmployeeAddress_EmployeeID_AddressID, PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID, PK_EmployeePayHistory_EmployeeID_RateChangeDate, PK_Illustration_IllustrationID, PK_Individual_CustomerID, PK_JobCandidate_JobCandidateID, PK_Location_LocationID, PK_Product_ProductID, PK_ProductCategory_ProductCategoryID, PK_ProductCostHistory_ProductID_StartDate, PK_ProductDescription_ProductDescriptionID, PK_ProductDocument_ProductID_DocumentID, PK_ProductInventory_ProductID_LocationID, PK_ProductListPriceHistory_ProductID_StartDate, PK_ProductModel_ProductModelID, PK_ProductModelIllustration_ProductModelID_IllustrationID, PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID, PK_ProductPhoto_ProductPhotoID, PK_ProductProductPhoto_ProductID_ProductPhotoID, PK_ProductReview_ProductReviewID, PK_ProductSubcategory_ProductSubcategoryID, PK_ProductVendor_ProductID_VendorID, PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID, PK_PurchaseOrderHeader_PurchaseOrderID, PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID, PK_SalesOrderHeader_SalesOrderID, PK_SalesOrderHeaderSalesReason_SalesOrderID_SalesReasonID, PK_SalesPerson_SalesPersonID, PK_SalesPersonQuotaHistory_SalesPersonID_QuotaDate, PK_SalesReason_SalesReasonID, PK_SalesTaxRate_SalesTaxRateID, PK_SalesTerritory_Territor...

I am currently including these tests inside a C# test project inside Visual Studio 2010. Visual Studio has a rather nice feature that allows you to link to artefacts in other projects and hence we can host our single test class containing all of these tests in one place and link to it from whichever test project we want (typically you will have a test project per database) thus following the DRY principle. Here I show the dialog that demonstrates adding a link to an existing test class:

And how it appears in the project. Note that NamingConventions.cs exists in both test projects but one is just a link to the other:

Wrap-up

I’m not sure my colleagues are too happy about these new tests given that they’re now breaking the build more often but nonetheless I think they realise the value (I guess I’ll find out tomorrow when they read this!!!) All-in-all its working very well for us and I’m now a very happy bunny knowing that naming conventions are being enforced and will continue to be so with zero effort from here on in. I have made the test class that contains all of the tests that I detailed above available on my SkyDrive at http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20101005/MIControlTest.zip. If you want to use it you should simply be able to drop it into an existing C# database test project and away you go (change the tests to suit your naming conventions of course though).

Hope this helps. If it does please let me know, I’d really love some feedback on this.

@Jamiet

Experiences from writing sp_CascadingDataViewer – DB unit testing and code distribution

jamiet — Sat, 18 Sep 2010 16:41:55 GMT

Its now been a month since I introduced sp_CascadingDataViewer and I wanted to take the opportunity to talk about a couple of my experiences while writing it.

SQL Server needs packages

Version 1 of sp_CascadingDataViewer is an 899 line stored procedure (view the code at changeset 58999), that’s a lot of code and much of it is repeated. I would have loved to have hived off different parts into dedicated functions but I couldn’t do that because my aim was to keep everything in a single stored procedure thus making it as easy as possible for someone to add it to their SQL Server instance. That is an unfortunate trade-off that we have to make with SQL Server.

Oracle has a solution to this problem called packages. A package is ostensibly a collection of stored procedures and functions that can be distributed and installed as a single unit. That makes them extremely portable and would be an ideal mechanism for me to distribute a collection of stored procedures and functions that make up Cascading Data Viewer.

SQL Server needs something equivalent to Oracle packages.

You need to be doing automated database unit testing

sp_CascadingDataViewer wouldn’t exist without automated database unit testing in Visual Studio. Period.

With something as complicated as sp_CascadingDataViewer there were many many combinations of code, schema and data that could cause is to break. I had to deal with all those combinations and have a way of ensuring that any changes that I made did not break something else and automated testing was the means with which I did that.

I wrote the code for sp_CascadingDataViewer in Visual Studio rather than SQL Server Management Studio (as I do for all my T-SQL code these days) which meant that my code and the tests lived in the same solution. At the time of writing that solution includes 30 tests and all are available in the source code on Codeplex:

With a simple key chord (CTRL+R, CTRL+A) I could deploy my code and run all my tests against it – that’s an incredibly powerful mechanism and I actually find it to be very productive method of development even if you have many tests that need to be run.

Writing sp_CascadingDataViewer switched me on to the value of automated database unit testing and I now advocate its use wherever I go.

@jamiet