Bennetts is one of the UK’s leading motorcycle insurance brokers. Founded in 1930, they now provide defaqto 5* Rated cover for thousands of UK motorcyclists, from those with CBTs to experienced riders.
With rapid developments in technology, the legacy data system was due for an efficient and rationalized upgrade. The team were tasked with modernizing the process and implementing the latest technology to stay ahead.
Introducing SQL Toolbelt Essentials added guardrails to manual processes. Standardizing code structure and formatting, adding code quality best practices, and swift rollbacks to resolve errors before affecting customers.
There are now limited errors, a reduction in deployment failures and processes have been streamlined. With increased productivity and efficiency, the team can apply their focus on modernizing the entire legacy database.
Bennetts is one of the UK’s leading motorcycle insurance brokers. Founded in 1930, they now provide Defaqto 5* Rated cover for thousands of UK motorcyclists, from those with CBTs to experienced riders.
Bennetts are based in Peterborough with their customer service and contact centre based in Coventry. They provide competitive quotes for Defaqto 5 Star Rated insurance cover, using a large panel of leading underwriters to offer a wide range of schemes.
Bennetts embrace innovation and use the latest digital technology to pioneer processes, so their customers can get more value from their insurance policy and more from motorcycling. The Platform Engineering Team, led by Ryan Hird, design and write the code, bringing these processes to life.
The IT Division is responsible for developing both the applications and the database that allow Bennetts to provide insurance quotes to hundreds of thousands of riders every year. Over 90 applications access the database estate, many of which are either customer-facing or utilized daily by the contact centre teams. As a result, deployment failures and database downtime could directly affect organizational performance.
350,000quotes created per year90 applications accessing the database200,000+customers“It was a painful process of throwing my code over the wall and hoping for the best with copy and paste.”
Ryan Hird, Technical Lead
The legacy database needed to be modernised. At around 1TB in size and with a de-normalized structure, managing deployments to this database was complex and time consuming, as were the initial processes.
Releases were predominantly ad hoc but, as many of the applications hook directly into the database, they required SQL code changes. The rate of change was limited by the application, meaning that deployments would take 2-3 weeks for a successful release, and for those that failed, there would be a further delay to find the cause, apply the fix and re-release.
The fully manual process was subject to error. As Ryan comments: “We could spend a fortnight testing deployment code to get to the point where I was happy with it, but as deployments were managed by a different team, somebody would essentially copy and paste the SQL script from a Word document to deploy it.”
Not only was there a wide margin for error, but Microsoft Word would often affect the script formatting. As the deployment team did not work with the applications, they could not always anticipate the impact of changes when deployed.
A major part of the challenge, Ryan explained was that “We didn’t know what we didn’t know.” With deficient database processes the team had no certainty in knowing if a script had been checked into source control, or which environments it was in. Everything required manual checking. When deployments failed, it was simply too late: the incorrect code had already been shipped to the live database.
The only action the team could take was to manually create rollback scripts to reverse the change, but first they had to meticulously compare the testing environment against the deployed scripts, to identify the discrepancies. The error could be as small as a space added or a missing quote, so this painstaking and laborious task could take days, during which time the coding error could be affecting performance of the live database.
“When something goes wrong, you don’t have the time in that instant to do product research and get yourselves out of the hole – you’re stuck fixing it manually.”
Ryan Hird, Technical Lead
Ryan and the team knew they had to bring in third-party tools to assist them. Already familiar with Redgate, having introduced SQL Clone to provision lightweight database copies, in seconds, to Bennetts’ developers for use in development and testing, next was SQL Toolbelt Essentials, a collection of industry-standard tools for SQL Server development.
After initial testing, Ryan and his team identified which tools were most suitable and proceeded with their selection. By adding guardrails, such as standardized code formatting and baking best practices into code quality, the accuracy and productivity of code reviews improved rapidly. Personal coding and formatting styles were also unified so the only changes showing are those the team want to see. With potential errors or issues identified and corrected in testing, they did not make it to production as a failed deployment.
Automatic rollback scripts, templates and comparison functionality enabled the team to build in an insurance backup to protect against a deployment failure. This enabled the team to swiftly and easily troubleshoot problems and perform a full rollback, with the ability to compare both the data and schema of the test and deployed scripts in minutes, to locate and reverse the error with minimal impact to the live database.
When something goes wrong, there is a shortage of time in that instant for product research – a manual fix is the only solution. As Ryan concludes: “Even if you don’t think you need it, if you do get into a mess, it’s your own insurance policy.”
“By having the tools on hand to solve issues that arise, you can get ahead of the problem rather than playing catchup.”
Ryan Hird, Technical Lead
The new cleaner processes, prompted and enabled by the introduction of SQL Toolbelt Essentials, mean fewer errors, an improved code quality, consistency across environments and fewer deployment failures. These processes and the efficiencies they have created have positively affected development on the application side. The changes have enabled faster and more accurate application deployments.
These tools have become so integral to the current processes, Ryan believes many of the team would be surprised to learn they are a separate product. The database development process is much neater with improved organisation, ensuring that as the application development process has become easier, the database is not a bottleneck to the overall platform development.
With the productivity and efficiency gains made, Ryan and the team can focus their efforts on their programme of development, to transform the legacy database into a microservices-style infrastructure. “We now have better change processes, where we baked in all the best practices to distribute out and help us evolve.”
SQL Toolbelt Essentials is a set of ten industry-standard SQL Server tools that work together to standardize every aspect of database development. It enables teams to save time, increase code quality, and gain greater confidence in more frequent releases. Try SQL Toolbelt Essentials for free at www.red-gate.com/products/sql-toolbelt-essentials/
Since joining Bennetts more than 11 years ago, Ryan outlined that Bennetts have always had some iteration of Redgate tools. Now, however: “The evolving process has led us to make far better use of many of the tools in different use-cases. It is great to select a tool we have off the shelf as we need it.”
The team started with SQL Prompt, and the combination of tools within SQL Toolbelt Essentials provided added business benefit. The bundle brought together SQL Prompt, SQL Compare, Data Compare, and SQL Search, the tools most frequently used by Ryan and the wider IT Division.
Some developers use specific tools more than others, though some would be surprised to learn SQL Prompt is a separate product, and potentially not know how to develop SQL without this tool.
As Ryan explains: “Those who have the most experience across the full range of tools within SQL Toolbelt Essentials have gained this knowledge through using the tools at their disposal to easily overcome challenges.”
Across the IT Division at Bennetts, SQL Prompt is used all day, every day, while the use of SQL Compare and Data Compare is more ad hoc, for environment comparisons and generating rollback scripts. Ryan elaborated further on the specific use-cases for SQL Prompt, SQL Compare and Data Compare.
For Ryan, using SQL Prompt is like muscle memory. He can start writing code, then by typing SSF and utilising the top ten dropdown feature, it can pre-populate and fill in the list of table names. As he admits: “I have had a couple of instances where I have completed machine rebuilds and have not yet installed SQL Prompt and feel lost without it there.”
Using SQL Prompt to help write queries has created a significant productivity gain for Ryan and the wider IT team. When writing SQL code, the team may only know a partial column name and not which table it sits in. Within Bennetts’ database, table naming conventions can be complex because they are based on a prior COBOL system where everything has eight characters with odd prefixes. Rather than having to open the full table definition, SQL prompt puts that information at their fingertips to auto-populate as scripts are created, saving time and increasing overall productivity.
Formatting has also been a big win for the team with SQL Prompt. With items such as table names pre-populated, and the writing and case style becoming consistent for coding across the division, code reviews are now far easier. Ryan explains that in previous code reviews, a line containing different cases would be highlighted when comparing scripts, which became distracting. “Character for character, that line has changed, though fundamentally it is still doing the same thing. So, where SQL Prompt pushes consistency, you only get the changes and the differences you want to see. Consequently, the quality of code reviews has also seen a step-change in improvement.”
Ryan outlines this is not only through the tool’s ability to help him, and the team write quicker, more accurate and consistently formatted code, it is also through features like snippets. “SQL Prompt has made the entire database development process much neater with improved hygiene and processes.”
The snippets feature has additionally enabled the team to standardize and speed up the data correction process. For example, a developer will create a SQL script to make an update, and need to ensure key details are included, such as the ticket number and the name of the developer writing the code.
Rather than reverting to the days of copy and paste, information is stored in a SQL Snippet. Developers can now simply add their coding change in the middle of the snippet with confidence it will give them all the rollback information required. Ryan explains this extends beyond time saving benefits which boost productivity: “It adds guardrails around the process to make it a lot safer.”
The SQL comparison Tools within SQL Toolbelt Essentials have been key in creating new cleaner processes, enabling Ryan and the team to reduce errors, save time and increase speed of delivery.
The comparison tools enable the team to revert bad SQL migrations quickly and easily. Should bad code be introduced, or a bad configuration value, clones can be compared against to bring good code back in. An issue arose with quote vs double quote in testing scripts, where SQL Compare and SQL Data Compare located the issues in minutes and created the rollback script to apply. As Ryan explains: “Without the compare tools, we would have had to manually check scripts and individual changes to see what was applied and where. Utilising these tools, save us a lot of time.”
The team had a serious situation where a full table had disappeared from the production environment. For a 1TB database this would historically have been painful to restore, but now able to utilise the previous day’s copy of production and, using SQL Compare and SQL Data Compare, the team were able to quickly restore and repair the issue.
SQL Data Compare also enables the team to efficiently synchronise table data between environments, and to replicate live issues. Bennetts offer several optional add-on policy benefits for their customers, and sometimes it is uncovered that pricing is different across environments. The team want to download pricing data from the live environment to recreate the issue in the test environment, without having to copy the entire database.
With SQL Data Compare, rather than a full backup of the database, the individual tables which are of concern can be copied, and matched to the live system. Ryan advises: “This massively increases the speed of development with no need to manually run rollback scripts. We can point SQL Data Compare at a few databases and save the output.”
The role of Redgate’s comparison tools in reducing bad deployments that affect production for Ryan and the wider Bennetts IT Division, ensures any impact to both internal users and customers is minimal, and removes any negative consequence ensuring a positive customer experience and the protection of company revenue.
Bennetts introduced SQL Toolbelt Essentials to add guardrails to manual processes. They standardized code structure and formatting, baked best practices into code quality, and ensured swift rollbacks to resolve errors before they affected customers.
Bennetts is one of the UK’s leading motorcycle insurance brokers. Founded in 1930, they now provide defaqto 5* Rated cover for thousands of UK motorcyclists, from those with CBTs to experienced riders.
With rapid developments in technology, the legacy data system was due for an efficient and rationalized upgrade. The team were tasked with modernizing the process and implementing the latest technology to stay ahead.
Introducing SQL Toolbelt Essentials added guardrails to manual processes. Standardizing code structure and formatting, adding code quality best practices, and swift rollbacks to resolve errors before affecting customers.
There are now limited errors, a reduction in deployment failures and processes have been streamlined. With increased productivity and efficiency, the team can apply their focus on modernizing the entire legacy database.
“It was a painful process of throwing my code over the wall and hoping for the best with copy and paste.”
Ryan Hird, Technical Lead
Bennetts is one of the UK’s leading motorcycle insurance brokers. Founded in 1930, they now provide Defaqto 5* Rated cover for thousands of UK motorcyclists, from those with CBTs to experienced riders.
Bennetts are based in Peterborough with their customer service and contact centre based in Coventry. They provide competitive quotes for Defaqto 5 Star Rated insurance cover, using a large panel of leading underwriters to offer a wide range of schemes.
Bennetts embrace innovation and use the latest digital technology to pioneer processes, so their customers can get more value from their insurance policy and more from motorcycling. The Platform Engineering Team, led by Ryan Hird, design and write the code, bringing these processes to life.
The IT Division is responsible for developing both the applications and the database that allow Bennetts to provide insurance quotes to hundreds of thousands of riders every year. Over 90 applications access the database estate, many of which are either customer-facing or utilized daily by the contact centre teams. As a result, deployment failures and database downtime could directly affect organizational performance.
“When something goes wrong, you don’t have the time in that instant to do product research and get yourselves out of the hole – you’re stuck fixing it manually.”
Ryan Hird, Technical Lead
The legacy database needed to be modernised. At around 1TB in size and with a de-normalized structure, managing deployments to this database was complex and time consuming, as were the initial processes.
Releases were predominantly ad hoc but, as many of the applications hook directly into the database, they required SQL code changes. The rate of change was limited by the application, meaning that deployments would take 2-3 weeks for a successful release, and for those that failed, there would be a further delay to find the cause, apply the fix and re-release.
The fully manual process was subject to error. As Ryan comments: “We could spend a fortnight testing deployment code to get to the point where I was happy with it, but as deployments were managed by a different team, somebody would essentially copy and paste the SQL script from a Word document to deploy it.”
Not only was there a wide margin for error, but Microsoft Word would often affect the script formatting. As the deployment team did not work with the applications, they could not always anticipate the impact of changes when deployed.
A major part of the challenge, Ryan explained was that “We didn’t know what we didn’t know.” With deficient database processes the team had no certainty in knowing if a script had been checked into source control, or which environments it was in. Everything required manual checking. When deployments failed, it was simply too late: the incorrect code had already been shipped to the live database.
The only action the team could take was to manually create rollback scripts to reverse the change, but first they had to meticulously compare the testing environment against the deployed scripts, to identify the discrepancies. The error could be as small as a space added or a missing quote, so this painstaking and laborious task could take days, during which time the coding error could be affecting performance of the live database.
“By having the tools on hand to solve issues that arise, you can get ahead of the problem rather than playing catchup.”
Ryan Hird, Technical Lead
Ryan and the team knew they had to bring in third-party tools to assist them. Already familiar with Redgate, having introduced SQL Clone to provision lightweight database copies, in seconds, to Bennetts’ developers for use in development and testing, next was SQL Toolbelt Essentials, a collection of industry-standard tools for SQL Server development.
After initial testing, Ryan and his team identified which tools were most suitable and proceeded with their selection. By adding guardrails, such as standardized code formatting and baking best practices into code quality, the accuracy and productivity of code reviews improved rapidly. Personal coding and formatting styles were also unified so the only changes showing are those the team want to see. With potential errors or issues identified and corrected in testing, they did not make it to production as a failed deployment.
Automatic rollback scripts, templates and comparison functionality enabled the team to build in an insurance backup to protect against a deployment failure. This enabled the team to swiftly and easily troubleshoot problems and perform a full rollback, with the ability to compare both the data and schema of the test and deployed scripts in minutes, to locate and reverse the error with minimal impact to the live database.
When something goes wrong, there is a shortage of time in that instant for product research – a manual fix is the only solution. As Ryan concludes: “Even if you don’t think you need it, if you do get into a mess, it’s your own insurance policy.”
The new cleaner processes, prompted and enabled by the introduction of SQL Toolbelt Essentials, mean fewer errors, an improved code quality, consistency across environments and fewer deployment failures. These processes and the efficiencies they have created have positively affected development on the application side. The changes have enabled faster and more accurate application deployments.
These tools have become so integral to the current processes, Ryan believes many of the team would be surprised to learn they are a separate product. The database development process is much neater with improved organisation, ensuring that as the application development process has become easier, the database is not a bottleneck to the overall platform development.
With the productivity and efficiency gains made, Ryan and the team can focus their efforts on their programme of development, to transform the legacy database into a microservices-style infrastructure. “We now have better change processes, where we baked in all the best practices to distribute out and help us evolve.”
SQL Toolbelt Essentials is a set of ten industry-standard SQL Server tools that work together to standardize every aspect of database development. It enables teams to save time, increase code quality, and gain greater confidence in more frequent releases. Try SQL Toolbelt Essentials for free at www.red-gate.com/products/sql-toolbelt-essentials/
Since joining Bennetts more than 11 years ago, Ryan outlined that Bennetts have always had some iteration of Redgate tools. Now, however: “The evolving process has led us to make far better use of many of the tools in different use-cases. It is great to select a tool we have off the shelf as we need it.”
The team started with SQL Prompt, and the combination of tools within SQL Toolbelt Essentials provided added business benefit. The bundle brought together SQL Prompt, SQL Compare, Data Compare, and SQL Search, the tools most frequently used by Ryan and the wider IT Division.
Some developers use specific tools more than others, though some would be surprised to learn SQL Prompt is a separate product, and potentially not know how to develop SQL without this tool.
As Ryan explains: “Those who have the most experience across the full range of tools within SQL Toolbelt Essentials have gained this knowledge through using the tools at their disposal to easily overcome challenges.”
Across the IT Division at Bennetts, SQL Prompt is used all day, every day, while the use of SQL Compare and Data Compare is more ad hoc, for environment comparisons and generating rollback scripts. Ryan elaborated further on the specific use-cases for SQL Prompt, SQL Compare and Data Compare.
For Ryan, using SQL Prompt is like muscle memory. He can start writing code, then by typing SSF and utilising the top ten dropdown feature, it can pre-populate and fill in the list of table names. As he admits: “I have had a couple of instances where I have completed machine rebuilds and have not yet installed SQL Prompt and feel lost without it there.”
Using SQL Prompt to help write queries has created a significant productivity gain for Ryan and the wider IT team. When writing SQL code, the team may only know a partial column name and not which table it sits in. Within Bennetts’ database, table naming conventions can be complex because they are based on a prior COBOL system where everything has eight characters with odd prefixes. Rather than having to open the full table definition, SQL prompt puts that information at their fingertips to auto-populate as scripts are created, saving time and increasing overall productivity.
Formatting has also been a big win for the team with SQL Prompt. With items such as table names pre-populated, and the writing and case style becoming consistent for coding across the division, code reviews are now far easier. Ryan explains that in previous code reviews, a line containing different cases would be highlighted when comparing scripts, which became distracting. “Character for character, that line has changed, though fundamentally it is still doing the same thing. So, where SQL Prompt pushes consistency, you only get the changes and the differences you want to see. Consequently, the quality of code reviews has also seen a step-change in improvement.”
Ryan outlines this is not only through the tool’s ability to help him, and the team write quicker, more accurate and consistently formatted code, it is also through features like snippets. “SQL Prompt has made the entire database development process much neater with improved hygiene and processes.”
The snippets feature has additionally enabled the team to standardize and speed up the data correction process. For example, a developer will create a SQL script to make an update, and need to ensure key details are included, such as the ticket number and the name of the developer writing the code.
Rather than reverting to the days of copy and paste, information is stored in a SQL Snippet. Developers can now simply add their coding change in the middle of the snippet with confidence it will give them all the rollback information required. Ryan explains this extends beyond time saving benefits which boost productivity: “It adds guardrails around the process to make it a lot safer.”
The SQL comparison Tools within SQL Toolbelt Essentials have been key in creating new cleaner processes, enabling Ryan and the team to reduce errors, save time and increase speed of delivery.
The comparison tools enable the team to revert bad SQL migrations quickly and easily. Should bad code be introduced, or a bad configuration value, clones can be compared against to bring good code back in. An issue arose with quote vs double quote in testing scripts, where SQL Compare and SQL Data Compare located the issues in minutes and created the rollback script to apply. As Ryan explains: “Without the compare tools, we would have had to manually check scripts and individual changes to see what was applied and where. Utilising these tools, save us a lot of time.”
The team had a serious situation where a full table had disappeared from the production environment. For a 1TB database this would historically have been painful to restore, but now able to utilise the previous day’s copy of production and, using SQL Compare and SQL Data Compare, the team were able to quickly restore and repair the issue.
SQL Data Compare also enables the team to efficiently synchronise table data between environments, and to replicate live issues. Bennetts offer several optional add-on policy benefits for their customers, and sometimes it is uncovered that pricing is different across environments. The team want to download pricing data from the live environment to recreate the issue in the test environment, without having to copy the entire database.
With SQL Data Compare, rather than a full backup of the database, the individual tables which are of concern can be copied, and matched to the live system. Ryan advises: “This massively increases the speed of development with no need to manually run rollback scripts. We can point SQL Data Compare at a few databases and save the output.”
The role of Redgate’s comparison tools in reducing bad deployments that affect production for Ryan and the wider Bennetts IT Division, ensures any impact to both internal users and customers is minimal, and removes any negative consequence ensuring a positive customer experience and the protection of company revenue.
