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.
SQL Server always wins – be prepared for it – and never, ever touch the databases
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.
Use the TFS Administration Console built-in backup tool if you can
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!
High Availability means AlwaysOn!
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.
Keep an eye on your drives
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.
Remember to check what is going on
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.
