SQL Source Control wrongly detecting a 2008 db as 2014?

freecell1

Two of our developers recently got new machines and are running SQL Server 2014, but everyone else has SQL Server 2008 R2. We are experiencing a strange phenomenon:

1) RedGateDatabaseInfo.xml in subversion shows DatabaseVersion of 10 (correct - that's SQL Server 2008 R2 db).
2) Developer on a SQL 2014 box checks in a change to the db using SSC.
3) The DatabaseVersion gets updated to 12 (SQL 2014) in svn as part of the check-in.
4) This causes build system issues since our shared environments are 2008 R2 and also don't all have the newest RedGate tools.

The compatibility level on the database in question is set to 2008 R2 on the new dev boxes. So why is SSC detecting the db as a 2014 db? Our workaround is to manually re-edit the XML file after every check-in from the newer boxes, but that's a pain.

Thanks!

Gareth_B

Hi freecell1, thanks for your post.

It seems that setting the compatibility level of a database doesn't change the database version that gets reported by SQL Server, just the behaviour of the database in question. Running

SELECT @@VERSION

on the database on your developers SQL 2014 instance should confirm this.

In your case, I suspect the database is identifying itself as database version 12, although it's compatible with database version 10.

One of our teams is currently working on improved identification of database versions & behaviour at the moment, which will hopefully yield a chance to report the compatibility level in the way you'd like.

That will be a little way off though, so I'm afraid I'd have to suggest you continue with your workaround for the time being.

If your developers commit frequently, it may be worthwhile trying to automate that fix-up step. Running a local script to update the RedGateDatabaseInfo.xml before commit could make it less of a pain, or you may be able to create an SVN pre-commit hook to reject commits with the wrong DatabaseVersion (therefore keeping your build safer). I think a pre-commit hook could automatically fix-up the DatabaseVersion for you, but that's not a recommended thing to do with SVN so I couldn't recommend it.

freecell1

Hi Gareth -

I might be misunderstanding the use of DatabaseVersion.

SELECT @@VERSION

gives the SQL Server version while

SELECT compatibility_level
FROM sys.databases WHERE name = 'MyDatabase';

@version gives a result like "Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) Jun 11 2012 16:41:53 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)" but the Redgate XML is storing values like 10 or 12 which look like db compat versions.

In the meantime, I am going to upgrade the Redgate tools on the build server so they recognize db version 12 - it's possible this will be sufficient for our needs.

Thanks,

John