Dependency Injection in SQL Monitor

Michael Clark
Jan 9, 2019 · 7 min read
Picture source (Licence under CC BY 2.0)

In this blog post we talk about converting SQL Monitor’s base service to use DI.

Moving to DI has been a substantial task that improves the architecture of SQL Monitor and provides a number of significant benefits. These include making it faster to add components, faster to fix problems and easier to test larger systems. It has also revealed the hidden architecture behind the base service.

At the end we will share some of the things we learnt going through this process.

It’s faster to add new components

In SQL Monitor we sample data from servers. If we want to sample new data we have to add new components. Since moving to DI we’ve found that it is faster to add new components because there is less plumbing and also less chance to make a mistake. You get to focus more on the sampling itself which is more interesting.

It’s faster to fix problems

We had a regression since releasing the DI changes because we removed retry logic on startup. This retry logic was complex and rebuilt a large portion of monitor if any exception occurred on startup. We felt it wasn’t necessary. Since releasing we received a support query from a customer who was having trouble because sometimes SQL Monitor started faster than SQL Server. Without retry logic this meant that SQL Monitor failed to start and they had to restart it manually in Windows Services.

As part of moving to DI we had to rationalise our initialization code, splitting new-ing up and runtime code like scheduling jobs. This revealed the architecture of SQL Monitor and meant that we knew exactly where to fix this problem. We were able to support the specific customer need to retry the initial connection in a single location instead of having to recreate the whole of SQL Monitor.

It’s easier to understand SQL Monitor’s startup

FullBaseMonitor.cs constructed most of the dependencies in SQL Monitor. It did dependency injection “the hard way” by manually new-ing up dependencies. However, it mixed new-ing up code with runtime code, had strange cyclic dependencies and we couldn’t understand it. It needed taming.

Without DI, changing the signature of a class means that you have to touch many files, and requires piping it through the entire application. With DI, if you add a new dependency you just have to register the class and add it to the constructor of the class that uses it. This means less files changed and less merge conflicts.

With DI, it forces a clear separation between registering classes in the application, resolving the entry point and running it.

We tested the “untestable”

One of the hard things about writing tests in SQL Monitor, and in fact any relatively large system with multiple components, is that you either have to mock out components or somehow connect in a real instance of that component. With DI you can much more easily construct a real system with exactly the same construction as the shipped product.

There are some parts of SQL Monitor that are completely untested like the integration between different components. We do not verify that sampled data is stored and triggers alerts but this is the core of SQL Monitor.

Some basic attempts have been made to test sample data collection and these previously required duplicating a large amount of FullBaseMonitor to construct the test dependencies. As time went on, these dependencies began to differ from FullBaseMonitor which brings into question the validity of the test.

With DI, we have less duplication in tests, we can use the same newing up, replacing some modules with test equivalents. It gives us the possibility to test SQL Monitor all at once. We can mock out components easily without having to change lots of code or pass in constructor functions. Example could be using samplers with fake data, or freezing time.

Example — Improving backup tests

This year SQL Monitor introduced a Backups dashboard under its new Estates section. Unfortunately SQL Monitor did not support this via its ORM so this required a lot of specialized SQL to gather data from more than one table. When the team added this dashboard they felt the SQL itself was untestable because connecting all the different components together to test real data was not feasible — to test real data against a real repository.

In the original version the production SQL for Max RPO calculation was not being run by the test, by instead overridden so that we could test the rest of the code. This meant the most substantial part of the SQL was not being tested. We also hooked into the code via an internal method GetMaxRpoByDatabase which didn’t test the public interface.

public void GetMaxRpoByDatabase_WhenOneBackupInWindowAndOneOutsideAndLatestBackupHasWorstRpo_ThenUseLatestBackupFinish()
var now = new DateTime(2018, 02, 10);
var windowStartDate = now.AddDays(-BackupsRepository.MaxRpoWindowLength);
var lastBackupFinished = windowStartDate.AddDays(1);
var maxRpoBaseQuery = $@"
SELECT 1 AS DatabaseId, utils.DateTimeToTicks('{lastBackupFinished:s}') AS FinishDate
SELECT 1 AS DatabaseId, utils.DateTimeToTicks('{windowStartDate.AddDays(-1):s}') AS FinishDate
var maxRpoByDatabase = GetMaxRpoByDatabase(now, maxRpoBaseQuery);
var result = maxRpoByDatabase.SingleOrDefault(r=>r.DatabaseId == 1);
Assert.That(result, Is.Not.Null);
Assert.That(result.From, Is.EqualTo(lastBackupFinished));
Assert.That(result.To, Is.EqualTo(now));

With DI, we adapted the Backups tests to load the relevant modules and insert samples directly into the database. That meant we could simplify the existing tests and increase test coverage of the production code in a way that gives us more confidence about the code our users will run. We now call GetLatestByDatabase which matches the public interface. For instance, we will now catch SQL syntax errors in the Max RPO calculation SQL statement.

public void GetLatestByDatabase_WhenOnlyOneBackupAndInWindow_ThenMaxRpoShouldBeNowMinusBackupFinish()
var backupFinished = s_DateTime.AddDays(-BackupsRepository.MaxRpoWindowLength + 0.25);

var databaseBackupInfo = GetLatestByDatabase();
Assert.That(databaseBackupInfo.MaxRpoFrom, Is.EqualTo(backupFinished));
Assert.That(databaseBackupInfo.MaxRpoTo, Is.EqualTo(s_DateTime));

Recipe for DI-ing SQL Monitor

Step 1 — Present plan to team

We presented an initial plan and technical direction to the team in July 2018. We decided to focus on one service, the base service. With two teams working on SQL Monitor we decided we should pair between the teams to share knowledge.

Step 2 — Pick a DI container

We chose the Autofac DI container. Why Autofac? It could be any DI framework but Autofac is widely adopted, well documented with best practices and we have used it before successfully at Redgate.

Step 3 — Add Autofac to SQL Monitor

We kicked off an initial PR to get Autofac into SQL Monitor and a couple of initial dependencies registered.

Step 4 — Create regions for registration/initialisation

DI requires registration code to be separated from initialization code that uses dependencies. The source file was initially a mix of both. To separate these two concerns we created a Registration region and Initialisation region at the top of the file. The goal then is to move as much code as possible to registrations in the Registration region and as little code as possible to the Initialisation region.

Step 5 — Register all dependencies

We followed a repeatable process to migrate to DI. This allowed us to pause and continue the work with different people. We used Resharper as much as we could to reduce human error.

  1. Register the next dependency
  2. Put anything that can’t be registered into the Initialisation region
  3. Replace references to that dependency to resolve it from DI
  4. Run build and make sure the base service starts without errors
  5. Repeat until all dependencies are registered

Step 4 — Extract modules

DI allows you to use modules to group dependencies. We extracted a module for each component (e.g. alerting).

Step 5 — Reduce repetition

We reduced repetition by providing extension methods for SQL Monitor-specific concepts. Secure components respect the current logged in user’s access to monitored entities — we introduced .AsSecure to create a new secure component and .WithSecure to use a secure component.

We also used .WithParameter to simplify calls to .Register into .RegisterType, overriding one parameter instead of specifying all of them.

Things we learned

Get the team onboard

Moving to DI is a team decision and has big implications for the future of the product.

Start with a small achievable goal

We decided to focus on the base service. We broke that down more to focus on just getting Autofac running then injecting a couple of initial dependencies. It made the whole process less daunting.

Incremental changes/One-by-one

Splitting one big change into multiple smaller changes meant the DI registrations were easier to review, conflicted less with other work, and helped us to isolate errors if they occurred. We registered dependencies one-by-one and ran the program before each commit.

Avoid feeling the need to use all the power/Use a limited vocabulary

DI containers provide a lot of power and it’s easy to get baffled and confused especially if you are new to it. We simplified the container to its most basic registration syntax so that we were able to work together more easily and use search/replace to automate some code transformations.

When you increase your vocabulary you might simplify the code slightly at the expense of requiring more knowledge and having less ability to replace a lot of code at once.

Using one type of scope

DI containers allow you to scope registrations so that you either get the same dependency or a new one each time you resolve an object. We opted to use SingletonScope everywhere to maintain the original behaviour of sharing all dependencies. We can then review these usages later to determine the best scope.

Read more

Ingeniously Simple

How Redgate build ingeniously simple products, from…