Scenario: TFS 2010 has been installed onto a SQL Server 2008 Enterprise Evaluation in the production environment (not by me!). Additionally, we’re using Sql Server Reporting Services and SQL Server Analysis Services along with the excellent Agile ScrumforTeamSystem template from EMC Consulting (formerly Conchango). Note: No Sharepoint. I’ve been tasked with migrating everything from SQL Server 2008 Enterprise Evaluation to SQL Server 2008 Standard.
Plan: Migrate everything to a new server, one that will act as backup, and then upgrade the existing SQL Server 2008 Enterprise Eval to SQL Server 2008 Standard. Note: we didn’t have the budget to pay for a SQL Server 2008 Enterprise license.
Risk Overview: SQL Server Enterprise Evaluation, as its name suggests, is an evaluation version of the software. It will expire after 180 days. Once this evaluation period has elapsed, access to the databases will be denied. You’ll need to migrate the data (*.mdf) and the log files (*.ldf) to a new data server and attach them.
Important Disclaimer: The following steps were followed and worked for me in my controlled environment. Everything was backed up, appropriate VM’s snapshotted etc. You follow these steps at your own risk!
High-level Migration Plan:
- Detach databases from source TFS.
- Backup databases from source SQL Server 2008 Ent Eval.
- Reattach databases to source TFS.
- Restore appropriate databases to destination SQL Server 2008 Ent Eval (yes, Ent Eval in backup server).
- Install the Scrum Template (ScrumForTeamSystem).
- Reconfigure Reporting Services on destination server.
- Reconfigure Analysis Services on destination server.
- Begin TFS Installation in destination server.
- Point TFS to the Reporting Services on destination server.
- Attach TFS projectCollections in TFS Admin Console.
- Ensure that TFS projectCollections are marked as Scrum in ScrumForTeamSystem.
- Source TFS – Remove SQL Server Enterprise features from TFS databases.
- Upgrade (yes, it’s called upgrade) SQL Server 2008 Enterprise Evaluation to SQL Server 2008 Standard.
- Open Team Foundation Server Administration Console.
- Select Team Project Collections (from left hand menu)
- Select a Team Project Collection.
- At the bottom right of screen, select “Detach Collection.” (The Detach Team Project Collection) screen will open.
- Optional – enter a message.
- Click “Verify.” There will be a warning. The warning detail will show (I’m pararphrasing) that SQL Server migration may cause a problem.
- Click “Detach.”
- Repeat the above for each Team Project.
- Open SQL Server Management Studio (Start > All Progs > Microsoft SQL Server 2008 > SQL Server Management Studio).
- Backup the following databases. (How to Backup)
TFS_DefaultCollection (assuming your TFS Project Collection is DefaultCollection)
TFS_Analysis (you’ll need to open an Analysis Services Connection in SQL Server Mgmt Studio to get to this). Unlike databases, the analysis services database will backup to one of it’s preconfigured storage locations, e.g. C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Backup.
- Open the Team Foundation Server Administration Console.
- Attach the Team Project collections that you detached earlier in step 8. As this is production, I need minimal impact, so I’m setting it back to it’s original state.
- Open SQL Server Reporting Services (Start > All Progs > Microsoft SQL Server 2008 > Configuration Tools > Reporting Services Configuration Manager).
- Backup the Reporting Services Encryption Key (How to Backup).
Copy the databases from the above to the destination server and restore them. (How to Restore).
Restoring the TFS_Analysis database is a little different. (How to restore Analysis Services DB). In short, copy the TFS_Analysis.abf to (C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Backup\). Then open up a connection to the Analysis Services in SQL Mgmt Studio, and run
<Restore xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine“><File>C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Backup\Tfs_Analysis.abf</File>
Install Team Explorer 2010.
Install the Scrum Template (ScrumForTeamSystem).
Configure SQL Server Reporting Services.
Start > All Progs > Microsoft SQL Server 2008 > Configuration Tools > Reporting Services Configuration Manager).
Enter the Reporting Services Web Services URL (if this is a clean install, just click Apply and go with defaults).
Database – Choose an existing report server database.
Click Next enter the appropriate credentials and click Finish.
Restore the Reporting Services (RS) Encryption Key.
Go to the source server and copy the RS encryption key that you backed up in step Source – 15 above to the destination server.
In RS, restore the encryption key.
Restore Reporting Services Encryption Key
On the destination server, go to Http://localhost/Reports/
Reporting Services URL on Destination Server
Click on Tfs2010OlapReportDS. The screen will change displaying, amongst other things, the source server connection string. Change this to the destination server.
Repeat the above for the Tfs2010ReportDS link.
Open SQL Server Mgmt Studio and connect to the local database server.
Add the Analysis Server User to SQL Server.
CREATE LOGIN [Domain\AnalysisServicesUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
In SQL Server Mgmt Studio, open a connection SQL Server Analysis Services.
Expand the databases, and you should see the database Tfs_Analysis.
Expand the database, go to Data Sources > Tfs_AnalysisDataSource. Change the connection string so that it now points to the destination server.
TFS Installation on Destination Server
Start the TFS 2010 Installation.
Select “Advanced Configuration”.
Database – assuming it’s correct, leave it as it is.
Enter the TFS service account details.
Untick Reporting Services configuration.
Untick Sharepoint Configuration.
Untick Create a new Team Project Collection.
Click “Verify” and then “Configure.”
Once complete, the TFS Admin Console will open. Select “Reporting” from the left hand menu.
Tick Use Reporting.
Warehouse tab – Enter the db server (destination server) and then TFS_Warehouse as the database name.
Analysis Services tab – Enter the db server and then TFS_Analysis as the database. Enter the domain\ReportingServicesUser and password.
Reports tab – Enter the server, then tick populate URL’s. These should appear as http://destinationServer:80/ReportServer and http://destinationServer:80/Reports
Enter the account details domain\ReportingServicesUser and password. The default path should be /TfsReports
Click “Start Jobs.”
Select Team Project Collection and click “Attach” (on right of screen).
Enter the db name, select the Team Project Collection you wish to attach.
Click “Next”, “Verify” and then “Attach.”
There will be warnings about Report location already being used, ignore this warning.
Process Warehouse and Analysis Services (optional).
On the destination server, go tohttp://localhost:8080/tfs/TeamFoundation/Administration/v3.0/WarehouseControlService.asmx?op=ProcessAnalysisDatabase
Enter Full into the textbox, then click Invoke.
You should now be able to connect you Team Foundation 2010 TFS Server to the destination server and its project collections…
Next – Upgrade the SQL Server 2008 Enterprise Eval to SQL Server 2008 Standard.