Learn how AFA Försäkring transformed development procedures, optimized live performance, and halved deployment time across a system of 70 servers and 400 databases managing $24 billion of assets.
“We were manually scripting out objects across different environments, which caused downtime at deployment.”
Wholly owned by Sweden's labor market parties, AFA Försäkring (AFA Insurance) provides a range of insurance policies and financial support to over four million employees in both the public and private sector. Covering incapacity for work due to sickness, work injury, shortage of work, death, and parental leave, it manages assets of approximately SEK 200 billion (US$24 billion).
Behind the scenes, five development teams manage around 70 servers and 400 databases on the Microsoft platform alone, and look after two online systems handling up to 130,000 logins per week.
Keeping all of that technology performing at optimum efficiency is the biggest challenge for Software Architect, Thomas Johansson, and his team. Already in the process of upgrading to SQL Server 2016 to take advantage of features like In-Memory OLTP and Dynamic Data Masking, he wanted to look at ways of replacing manual processes and introducing automation to database deployments.
“We introduced comparison tools initially to solve the issues around inconsistent environments.”
AFA Försäkring started working with Redgate tools originally to resolve conflicts between its development, testing, pre-production/acceptance, and production environments. Manually scripting out objects was very slow and inefficient, and some were invariably missed which caused downtime during deployments.
"The first tool we adopted from Redgate was SQL Compare," says Thomas Johansson. "It helped us create simple comparisons between the databases in their different states and automatically generate the upgrade script. Our whole process became much faster and less error-prone because SQL Compare doesn’t miss any objects."
Not long after these initial changes, Thomas and the team at AFA Försäkring realized they were missing out by not having database version control in place. While it was standard practice for application code, they felt they were lagging behind by not adopting the same approach for managing database changes.
Redgate SQL Source Control was put in place to provide rollback options, one central source of truth that all developers have access to, and ultimately a record of all the changes and a way to quickly rebuild a database.
“We had a flavor of the timesavings automation tools offer, and wanted to include deployments as well.”
Having taken the first steps to actively managing the lifecycle of their databases, the team wanted to go even further and automate the deployment process. They had already chosen the popular automation deployment tool for .NET applications, Octopus Deploy, and decided to include database deployments as well.
Redgate once again stepped in, this time with DLM Automation (now SQL Change Automation), another tool in the SQL Toolbelt, which plugs into release management tools like Octopus Deploy, TeamCity, and Team Foundation Server.
A working prototype quickly demonstrated how the solution could be rolled out across the pre-production and production environments, and the final implementation has transformed the way the development teams work. Every check-in to SQL Source Control triggers an automated continuous integration process, so any issues are caught early. If everything builds as expected, changes are automatically promoted across environments, including production.
With deployments happening in development rather than operations, it speeds up the entire process – and has freed up the production Database Administrator (DBA), who now works on optimizing performance instead of handling time-intense and error-prone manual deployments.
“We're now deploying at a much higher frequency than before, and have doubled the deployments to production.”
As well as increasing the number of deployments, the team at AFA Försäkring have seen substantial time savings, freeing up the equivalent of an additional team member.
Thanks to the SQL Toolbelt, they also work more efficiently. Providing an end-to-end solution for managing the database lifecycle, it contains all of the development and administration tools necessary to optimize SQL Server. Alongside SQL Compare and SQL Source Control, for example, the team uses SQL Prompt which provides enhanced IntelliSense-style coding and refactoring and lets developers write and format SQL much more quickly.
Now that deployments are streamlined and the DBA can spend time managing performance optimization, the team have also adopted SQL Monitor. As Thomas Johansson concludes: "SQL Monitor provides us with the information we need to make performance improvements. Perhaps more importantly, it also alerts us to any problems, so we can address them before they become issues for users."