I’m very excited by to see the pubic preview of the Azure Key Vault service.

A blog has been started to cover the technical aspects and can be found here http://blogs.technet.com/b/kv/

One of the only things I’ve been speaking about in conferences recently has been compliance and encryption. https://www.sqlbits.com/Sessions/Event12/Achieving_PCI_with_SQL_Server-Dont_be_the_next_Sony

The most complex part of the the demos I do in that session is the key management story. Key management in SQL is possible but its complicated because keys are managed in the same store as the data and you have the challenge that a sysadmin has access to everything in SQL Server including the keys. That means duality is very difficult.

I’m working on a demo for how to use the Azure Key Vault instead of the using SQL Servers key management so keep posted.

If you are interested in trying it your self you can look at the technet documentation “Extensible Key Management Using Azure Key Vault (SQL Server)

Posted by simonsabin | 1 comment(s)

Was today directed to a nice little site giving the differences between different database systems. Its a nice succinct table of features


There is however an issue having yes and no answers to many of the features. A lot of them are grey, i.e. in SQL server you don’t have before triggers but you have instead of triggers.

The problem with this is also it gets out of date quickly. Although the fact that SQL Server has had user defined types based on CLR for almost a decade suggests the writer isn’t a SQL Server person.

It also believes that you can’t return the results of an OUTPUT clause as a result set.

So as with much content on the web, take it as guidance but if you are really looking for a feature then go check the relevant forums/official documentation.

Posted by simonsabin | 2 comment(s)

There are always those problems that you hit and you feel like you are hitting your head against a wall. This was one of those situations.

Running builds in TFS and we were wanting to update the build version and checkin the result.

Issuing two simple commands to checkout and checkin the file were failing with

“Unable to determine the workspace. You may be able to correct this by running 'tf workspaces /collection:TeamProjectCollectionUrl'.”

Now I know that TFS doesn’t work well if the workspaces are screwed and so started looking into the problem.

Firstly we had just upgraded to TFS 2013 and a new TFS server. This has all sorts of problems with workspaces and solution files as the source control settings are cached and also in the solution and project files. Agghhhh where’s git when you want it.

The caching is easily solved by removing the cached files this can be done by removing

“%LOCALAPPDATA%\Microsoft\Team Foundation\<version>\Cache”

Where <version> is the version of TFS you are using TFS 2013 has a version of 5.0.

Note: you won’t be able to remove the folder if VS is running (on a dev machine) or the Team Foundation Server Build Service Host is running (on a build machine)

I tried this and still no luck.

After some bing juice, I came across some comments in http://blogs.msdn.com/b/granth/archive/2008/07/22/tf-exe-unable-to-determine-the-workspace.aspx that alluded to the PATH pointing to the wrong version of TF.exe

I changed my script to point to the explicit TF.exe and hey presto it worked.

On examining the PATH environment variable I saw that indeed it was pointing to the previous version of TFS tools.

“C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE”

Not sure why the install of TFS 2013 didn’t change the path, probably some back compatibility stuff. Suffice to say, what out if you have multiple versions of VS and TFS installed on your build and dev machines.

Are you trying to build a SQL Server database project and getting
The imported project "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found. Confirm that the path in the <Import> declaration is correct, and that the file exists on disk.
We had this recently when trying to build one SSDT solution but not when building another.
Checking the build agent the error was correct that file didn’t exist. But later versions did.
A nice feature of TFS is that on clicking the error in build it takes you to the exact point in the sqlproj file that the error relates to.


Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
You will see that the path is made up of a number of components. Importantly $(VisualStudioVersion) defines the folder it looks for the targets file in.
Where is this property set?
We did some digging and found that the solution files differed.
The one that worked looked like this
Microsoft Visual Studio Solution File, Format Version 12.00
# Visual Studio 2012
and the one that failed looked like this,
Microsoft Visual Studio Solution File, Format Version 11.00
# Visual Studio 2010
As I#m sure you’ve done we put 2 and 2 together and figured that $(VisualStudioVersion) was being set by VS based on the solution file version.
We changed the solution file that wasn’t working to point to Visual Studio 2012 and hey presto it all worked.
On putting this blog post together I did a bit of binging and you will find this http://blogs.msdn.com/b/webdev/archive/2012/08/22/visual-studio-project-compatability-and-visualstudioversion.aspx which explains exactly the behaviour.
This means that if you have multiple versions of Visual Studio installed then you could end up with odd behaviour with automated builds.
There are a few things that are bad IMHO
1. there is no way in the IDE (that I can find) to see that the solution is targeted to Visual Studio 2010 and not Visual Studio 2012
2. no way to change it without editing the solution
3. the fact this property is in the solution and not the project.
Options you have as described in the blog post
1. Put <VisualStudioVersion>11.0</VisualStudioVersion> in a property group in your msbuild file
2. Put <VisualStudioVersion>11.0</VisualStudioVersion> in a property group in your sqlproj file
3. Pass as a property when calling msbuild  i.e. /p:VisualStudioVersion=11.0
4. Create a new solution with a header matches the version of VS you want to use
5. Change the solution so the header matches the version of VS you want to use
Posted by simonsabin | 3 comment(s)

Are you looking to go to SQLBits on the cheap. If so then we have arranged some great value accommodation.

£39 per night includes a full english breakfast at the Priorslee Rooms which is a 5 minutes from the venue.

For more details on accommodation options available visit the SQLBits accommodation page

Posted by simonsabin | with no comments

If you’ve been reading the wires about SQL Azure you will have seen a change to the tiers that are being offered. Thsi is all so that you can get a more predictable performance level from the platform and also to enable Azure to manage the resources.

You now have 3 categories basic, standard and premium, and within those you have some other levels. This enables you to start your service at a low level and then as required request more capacity (through the portal or API).


For details on the performance aspects of the levels you can read here


Something to note is that the switch between levels may not be instant. This depends on what level you are moving from and to and the capacity available on the physical servers your DBs are currently residing on.

“An SLO change for a database often involves data movement and therefore many hours may elapse before the change request completes and the associated billing change becomes effective. Data movement occurs for changes when upgrading downgrading a database and may also occur when changing performance level of the database.”

If you’re data has to be moved then for a 150GB it could take 2 days to make the transition, 17 Hours for a 50GB DB, the rule is as follows

3 * (5 minutes + database size / 150 MB/minute)

For the exact details go to http://msdn.microsoft.com/library/azure/dn369872.aspx

As a note I’ve created a test DB as a standard edition. I was able to increase and decrease the level instantly. I was also able to increase the the database to a premium database instantly. This would have been due to there being capacity on the servers that the database resides. You cannot rely on this behaviour, it may or may not be instant.

Posted by simonsabin | with no comments

It was sad to hear today about computermanuals.co.uk closing down after a period of administration.

Whilst I do love books, the access to technical information, of high quality on the internet and accessible on your PC does mean the printed technical book does look to be going the way of the dinosaur.

The silver lining is that you can get some books really cheap in their closing down sale


Posted by simonsabin | 3 comment(s)
Our next SQLBits event in Telford in July has topped the number of registrations for the Friday and we are still over 2 months to go. This is going to be our biggest regional SQLBits ever by a long way both in terms of attendees and sessions.
I’m currently in the planning for the Friday evening party and it will be a special event.
If you care about data and work with SQL Server the SQLBits XII is an event that you do not want to miss.

Its not too late to register but I’ve been told that accommodation is in short supply so make sure you make your booking soon.

Posted by simonsabin | with no comments
File:10base2 t-piece.png 

My career started at a company where we hardly had email, the network was a 10base2 affair with cables running all around the office. You used floppy disks and the thought of a GB of data was absurd. You had to look after every byte and only keep what you really needed.

Whilst the cost of the spinning disks gradually falls the cost and size of flash storage continues to plummet.

The new Crucial SSD is £380 for 1TB

I can now keep 128GB of data on a SD card the size of my finger. It only costs $50 a month to store 1TB of data in Azure ($61.29 on Amazon S3 Europe)

This brings long with it a whole host of problems.


Its too cheap

Whilst before you had to manage your mailbox, your desktop, your database because you ran out of space.

Now its cheaper to keep data than it is to get rid of it,

Electronic wastesSo what?

Keeping data around leads to many problems, and considering the cost of the media is only a small part of the cost of maintaining the data

1. You have to manage it, i.e. Backups

2. You have to secure it.

3. The more you have of it the more risk there is of loosing some of it.

4. When in a database having more data affects your query performance.

5. Can your employees find the data they need, is it a game of find the needle in the hay stack,

6. Most data protection acts state you should only keep data for as long as you need it.

7. You are also liable to give customers copies of the data you have on them. Do you know all the data you have on someone. Even those notes the sales person wrote about someone in a onenote notebook.

Its just like the blob

The reason that this is a problem for many organisations is that its never something that is on the list of priority things when a company starts or a project starts. Its never a problem as there is always enough space when things start. However as time goes by the problem gets bigger and bigger until it becomes a problem.

When it becomes a problem its then such a big thing to deal with, to do the work, change attitudes, implement policy, that no one really has the appetite.

What are you doing about it?

Do you think about what you are storing and decide if you really need to store it?

Do you have a data retention policy?

Do you have a data deletion policy?

What about a data archive policy?

Are you actively reviewing what data you are holding?

Do your IT guys really push back when a team says we need 1TB of storage for project X?

Do your developers have data retention in their definition of done?


Deal with it sooner rather than later or it will be just too big to digest.

Posted by simonsabin | with no comments

Data is a huge part of what we do and I need someone that has a passion for data to lead our SQL team.

If you’ve got experience with SQL and want to lead a team working in an agile environment with aggressive CI processes.

Do you have a passion about data and want to use technology to solve problems then you are just the person I am looking for

The role is based in London working for on of the top tech companies in Europe.

Contact me though my blog or linkedin (http://uk.linkedin.com/in/simonsabin), if you are interested.

Posted by simonsabin | with no comments
More Posts Next page »