SQL Server Configuration for Team Foundation Server
Here are a collection of information and links related to how to configure SQL Server for Team Foundation Server.
SQL Server Features Used by TFS:
• Data Engine
• Full Text
SQL Server Edition:
Preferred Enterprise Edition (TFS 2013 will take advantage of Enterprise features)
• Online re-indexing
• Index compression
• Query optimization
• SSAS perspectives
• SSRS Data driven deployments
Set Collation to case insensitive, accent sensitive and not binary
Configuration Best Practices:
No extra steps needed at the instance level, basic config check
SQL Server Backups for TFS
TFS needs to have synchronized SQL Database backups. To run backups from SQL Agent jobs instead
of from TFS, you will have to run Marked transactions on each TFS database.
To run Backups from TFS use:
Permissions break down and extra details
If you do not use marked transactions to guarantee common recovery points for all databases that Team Foundation uses, you must make sure to back up this database last and restore it before all other databases. Otherwise, you might not be able to restore the deployment.
Description of all databases:
If you don’t install any SQL Server instances on the TFS server, you must at least install the SQL Server feature Client Tools Connectivity on TFS.
SharePoint for TFS
SQL Server Reporting Services
TFS only works with Native mode.
Upgrade a SQL Standard Edition to Enterprise Edition Actions Needed
If you upgrade the edition of SQL Server that TFS uses you will need to run a stored procedure on
each collection database to enable compression for the current collections (New collections will
have the feature automatically enabled)
EXEC prc_EnablePrefixCompression @Online = 1, @disable = 0
Downgrade from SQL enterprise Edition to Standard Edition Actions Needed
Blog post of the steps needed (lists indexes and tables with compression enabled)