Monday, 20 August 2018

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.

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.

Thursday, 9 August 2018

How VSTS Sync Migrator is going to change then way you migrate to VSTS

Like I said in my last post, I really enjoy using VSTS Sync Migrator for Work Item migrations.
There are a few reasons why I believe this tool stands out from the rest, and it is not just because of its complexity - in a nutshell, you can use it not just for tip migrations, but to actually filter and sanitise what you are importing into your target TFS or VSTS.

Firstly, you can run each processor (call them steps if you want) independently. That is very important when it comes to understanding what each one of them does. You don't really want to use something that starts, does stuff and then fails with an enormous log file.

Each processor is extremely specialised and usually backed by a WIQL - again, quite complex sometimes but extremely powerful and flexible.

You can also run multiple instances of the Migrator, targeting different Team Projects in VSTS - having them side by side isn't usually a problem.

Then, you have a really powerful capability for shaping your data in the best possible way. By "shaping" I mean "mapping": field to field, replacing values, even replacing values via Regular Expressions or mapping different Work Item Types.

This can enable all sorts of scenarios where you can change a Process Template, or make your very verbose customised form much more readable by merging or moving around fields' data.

Eventually, no ancillary item is left behind, including Work Item Queries (which carry a huge business value IMHO) and commit information. You can link commits to Work Items even if you migrated the repository with a different name.

It takes a while to get all the bits right - there are lots of options, but the documentation is quite good and it will easily guide you through. Fellow MVP Mohamed Radwan also recorded a quick demo of how to use it.

Now, onto more VSTS migrations 😀