TFS 2010 Database setup

Question

I am setting up TFS 2010 using an existing Active/Passive Clustered SQL Server 2008R2 Std. server.  I need the ability to use the reporting feature.  I plan to setup a new instance just for TFS.

Questions about setting up the new instance:

- Since SQL Reporting Service does not run in a cluster mode, should I install SQL reporting on the TFS application server (app server will have plenty of available resources to handle this load)?  I would prefer not to run the reporting service on one node since that would prevent me from taking down the Active SQL node and running our SQL cluster on the 2nd node.  Or can I run Reporting Service on both SQL nodes and configure TFS to use both reporting servers?

- If I install Reporting Services on the application server, do I only need to install the Database Service on the SQL cluster or does it also need Analysis Service?  If no Analysis Service on the SQL Cluster, should it be installed on the application server?

- If I run Reporting Services (and maybe Analysis Services) on the application server, does that require me to purchase additional SQL licenses?

- If I run Reporting Services (and maybe Analysis Services) on the application server, do I install that as a separate SQL install, or does the TFS installation take care of this piece?

Answer 1

Hello,

Running SSRS on the AT server is generally a good idea as it is essentially a web application returning requests from HTTP.  Keeping all of your HTTP/ End user access traffic together is a good way to partition the servers and protect security.

SSAS is a data tier item as it will be accessed by the web tier directly (from SSRS and SharePoint Excel Services if you are using Enterprise addition.)  SSAS will be needed for most of the reports to run and in an ideal world, I would keep it of the AT server since I don't like having data stored on a publicly accessable server.  All of that said, many people do just that and it is a valid configuration.

Microsoft licensing is a bit arcane at times, but my expectation is that it would need an additional license (Kathy or John, want to comment?)  Remember that TFS comes with a dedicated license of SQL Standard that can be used for TFS.  If you use that license on your AT server and use existing licenses on your cluster, you are probably OK. 

For the install, I would generally do them seperately, but you could make a case for doing the stand-alone install, migrating it to the new data tier, and then doing the data tier repair.  I find that people do multi-tier installs more often than they do migrations so if you need help, there are better materials out there on the install.  That, and you won't ahve to wonder if something got changed in the migration.  I also HATE moving SP databases because it is painful (for me) so I prefer the more complicated install.

Hope this helps.

---

Ryan Hanisco, PMP, PMI-ACP, CISSP
MCSE, MCTS:TFS 2010, SQL 2005
RyanHanisco.com

Answer 2

Thanks Ryan for taking the time to reply to my situation...

To make sure I understand your comments:

- I will install SQL Server Reporting service only on the TFS application server.  This will happen before the TFS application is installed.  During the TFS install, I will not select the option to install any database services.

- I will configure a new SQL instance on the SQL cluster with only the Database and Analysis Services.

- Your comment about the licensing is very helpful.

Answer 3

I would follow the instructions for the multiple server installation in the TFS Install Guide.  That will get you through this, but yes, you do understand,

Your DBA or IT department may not want you to install another service (SSAS) onto the cluster if it is a mission-critical resource.  In that case it is valid to put that on the AT server or another box.  Just know that you are accepting some risk to your data security when you put any kind of data repository on a web accessable server.  If this is normal where you are or you will never open it to the public, then putting it on the AT server has a much lower threat level.

---

Ryan Hanisco, PMP, PMI-ACP, CISSP
MCSE, MCTS:TFS 2010, SQL 2005
RyanHanisco.com