TFS 2010 Move from SQL Server Enterprise Evaluation 2008 to SQL Server Standard 2008 – Part 2

As you should have seen from part 1, the migration of TFS, if you’re using Reporting Services and Analysis Services is a little fiddly but possible.

This second part explains the steps taken to upgrade SQL Server 2008 Enterprise Evaluation to SQL Server 2008 Standard for our TFS 2010 Installation.

I’m not going to go into the differences between SQL Server 2008 Enterprise and Standard, there are plenty of posts (incl this on StackOverflow) that can help you decide which version best suits you.

Determine SQL Version
Open SQL Server Management Studio and make a connection to the Source server database.

Create the following query:

select @@version

You will see a result similar to the following
Microsoft SQL Server 2008 (RTM) – 10.0.1600.22 (X64)   Jul  9 2008 14:17:44   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Evaluation Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (VM)

When TFS is installed on a server with SQL Server Enteprise Edition, is takes advantage of some of the Enterprise features, well one in particular, table compression. SQL Server Standard does not allow table compression, so moving (backup then restore) a database from Enterprise Edition to Standard will result in a big No-No. (I didn’t capture a screen shot of the error, but unlike many other error messages I’ve encountered over the years, it’s pretty clear what the problem is).

So, to find out which features are not compatible with SQL Standard, enter the following into a query window in SQL Server Management.
SELECT * FROM sys.dm_db_persisted_sku_features;
GO

The TFS databases that are deployed into SQL Enterpise use the table compression feature.

The following script displays the indexes and tables that use compression and generates a script that removes the compression.

SELECT DISTINCT ‘ALTER TABLE [‘ + SCHEMA_NAME(schema_id) + ‘].[‘ + NAME + ‘] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);’
FROM sys.partitions p
join sys.objects o
on p.object_id = o.object_id
WHERE o.TYPE = ‘u’
and data_compression_desc != ‘NONE’
UNION
SELECT ‘ALTER INDEX ALL ON [‘ + SCHEMA_NAME(schema_id) + ‘].[‘ + NAME + ‘] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);’
FROM sys.partitions p
join sys.objects o
on p.object_id = o.object_id
WHERE o.TYPE = ‘u’
and data_compression_desc != ‘NONE’

Alter indexes and tables removing compression

The script output lists of sql statements that can be run to remove compression indexes and tables.

Copy them into a new Query windows, review them and then execute them.

This script needs running on the following TFS 2010 databases:
TFS_Configuration
TFS_Warehouse
TFS_DefaultCollection (assuming DefaultCollection is the name of your TFS project collection).

Upgrade SQL Server 2008 from Enterprise Evaluation to Standard

Run Setup.exe on your SQL Standard.

From the left hand menu, select “Maintenance”.

Select “Edition Upgrade”
Support Rules will identify any potential problems.

Click “Next” then enter your SQL Server 2008 Standard license key.

Upgrade the server instance, the default it MSSQLSERVER.
Edition Upgrade Rules will identify any potential problems.

Click “Next”.

Click “Upgrade”.

Upgrade takes approximately 1 minute.

Ensure that all SQL Services have restarted correctly
(Start > Run > services.msc)

Validate SQL Upgrade

Open SQL Server Management Studio.

Connect to the TFS 2010 database engine.

Open a query window

Type Select @@version

Microsoft SQL Server 2008 (SP1) – 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (VM)

You can now connect to your TFS instance which now has a SQL Server 2008 Standard instance behind it.

Hope this helps.

Padda

SELECT DISTINCT ‘ALTER TABLE [‘ + SCHEMA_NAME(schema_id) + ‘].[‘ + NAME + ‘] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);’

FROM sys.partitions p

join sys.objects o

on p.object_id = o.object_id

WHERE o.TYPE = ‘u’

and data_compression_desc != ‘NONE’

UNION

SELECT ‘ALTER INDEX ALL ON [‘ + SCHEMA_NAME(schema_id) + ‘].[‘ + NAME + ‘] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);’

FROM sys.partitions p

join sys.objects o

on p.object_id = o.object_id

WHERE o.TYPE = ‘u’

and data_compression_desc != ‘NONE’