A collection of SQL Server-related tips for the TFS Administrator

If you run Team Foundation Server on-premise, understanding how SQL Server works on the Data Tier is extremely important. Despite the push for the cloud, there might be so many reasons why you need to stick with your on-premise installation of TFS – and the bigger the instance is the more SQL Server knowledge you will eventually need.

I am not a SQL Server expert myself, but between my past as a consultant and now being in a position where I administer a huge TFS instance meant that sooner or later I had to deal with SQL Server on a one-to-one basis. Not always the happiest of encounters to be fair 😊 but still, I learned a lot. So I thought that if you are in my position – where you might be the TFS Administrator – then hopefully a collection of notes I took over time might be handy for you.

It’s not really a tip, but something to keep in mind: given that Team Foundation Server is essentially a product where you have web services in front of a set of databases, hence if the databases have problems the whole product is down. Remember that – when something goes (very) wrong, keep in mind the Data Tier, sometimes it is silly stuff like the drive where the master database resides being full…

Also, never, ever touch the databases manually unless instructed by the Microsoft Support Team. Don’t be tempted to optimise the databases, the number of things that can go wrong is simply too high to risk being in a corrupted state or unsupported situation. Don’t do that.

The temptation of letting someone else (the IT department, a DBA, etc.) deal with the menial task of backing up your Data Tier can be very high, but if you can just use the built-in backup tool. It makes it transparent to you and takes away the hassle of creating maintenance plans.

If you have databases in Full Recovery Mode, it will take care of your Transaction Logs backup in an atomic manner – it is very important. If you take backups at different times for example, you might end up in a situation where you have identities in a Collection database which does not correlate with the Configuration database. To avoid this, you should mark your transactions as part of your backup plan.

Also don’t forget to backup your SSRS Encryption Key, otherwise you might be restoring a useless set of databases in a Disaster Recovery scenario!

To be fair it is not really the case, but it makes your life so much simpler. AlwaysOn makes Highly Available deployments a breeze, and even if you have to factor in some adjustments to your habits it is worth it every single time.

Beware though: even if you implement AlwaysOn for your Database Engine, you will not get Analysis Services for free on the same setup – that is a different deployment altogether.

This is something I experienced fairly recently – aside from the usual recommendation on where to put your databases (system or otherwise), if you have a very large database you could run into file system limitations that prevent DBCC CHECKDB from running and make you lose sleep. If you happen to experience these, it is worth knowing that not everything is lost and you might not even need to restore from a backup.

NTFS has a switch (/L) that is designed around large files, it is an excellent starting point although you need to format your drives. Another solution revolves around using ReFS instead of NTFS – it is something somehow unknown, but after running it for a while in my homelab and using it to solve a portion of this problem I can say that ReFS is a powerful “tool” (I can’t really consider a file system a tool, but for lack of a better word…) to resort to in case you find the dreaded error 665 in your logs.

I use this couple of queries since… I don’t know, ages. They help, because they show in a transparent way what is going on within a SQL Server instance (especially if you need to understand what AlwaysOn is doing) and they provide information that can help diagnosing certain errors.