Carly Meichen

2 February 2017

Streamlining database deployments with SQL Compare

When I attended PASS Summit recently, I sat down with Chris Yates, Assistant Vice President and Database Administration Manager of Republic Bank, to understand more about their day-to-day development tasks and how Redgate tools have helped improve their development practices. This is their story.

“In a previous life, SQL Server database comparisons
and deployments were run using manually compiled scripts.”

Headquartered in Louisville, Republic Bank has grown to become one of the largest Kentucky-based banks and one of the most stable community banks in North America, with banking centers in a number of states.

With $3.3 billion in assets, Republic is dedicated to meeting the needs of consumers, small business owners, and large corporations, offering a full range of competitive products and a highly personalized service.

Republic strives to be the most technically advanced community bank and highly values IT and technology within the business. To support this, Chris Yates, Assistant Vice President and Database Administration Manager, manages a team of four DBAs, who are responsible for 1,600 databases and over 52 production servers – and growing.

Together with the rest of the IT department, they are akin to a software company, looking for the next innovative, bleeding-edge tech or practice to adopt.

There is an ongoing project to move to SQL Server 2016, for example, and new features like Query Store are already being utilized. There are also plans to incorporate Always Encrypted functionality, and take advantage of AlwaysOn Availability Groups.

“SQL Compare cut down deployment times from
one hour and a quarter to less than 15 minutes.”

As Republic Bank approaches the 1,000-employee mark, 10% of people in the business are in IT and it is of strategic importance the bank stays ahead of the curve and delivers value to the business and customers quickly.

A good example is database deployments which used to involve writing scripts by hand, which were then reviewed by multiple people. This was hugely time-consuming and error-prone, and often meant one person in the team became a bottle-neck for delivery.

To resolve the issue, Chris Yates and his team introduced Redgate SQL Compare. The industry standard for comparing SQL Server schemas and creating deployment scripts in minutes, it replaced the manual process with one that was streamlined, stable, and far more reliable.

Chris Yates particularly liked the support that came with the software, which they could call on whenever they had a question or query. “The Redgate support team are excellent and really attentive,” he says. “And that’s to anybody in the bank. They’re very knowledgeable, and we always get a quick response, which is refreshing.”

SQL Compare was joined by SQL Data Compare, which compares and deploys SQL Server database contents, and they are now the de facto comparison and deployment tools in use. The tools are used daily, releases can also be managed remotely, and deployments times have been significantly reduced.

“We’ve freed up the DBA team by 70-80%. That’s the ROI,
and the leadership team can understand that too.”

Republic are now releasing database changes over 500 times a year and, with SQL Compare, the process has become standard working practice. All the database code is in version control and, once changes from different development branches are merged, SQL Compare is used to perform the comparison and generate the deployment script for review.

Reviewing all of the code in one place is much easier, and reduces both complexity and the risk of error, resulting in far less system downtime. Because SQL Compare makes it easy for anyone in the team to pick up a release, run it, and move on, everyone in the team is freed up to spend time on other valuable tasks.

It makes tracking down deployment issues easier too. From time to time, the team will be asked ‘Did this get released?’ or ‘I’m missing something’. With SQL Compare, they save all of the scripts, so they have an audit trail they can go back through and check what was deployed, and when.

Following the success of SQL Compare, the team at Republic Bank have also adopted other Redgate tools from the SQL Toolbelt across the DBA, Development, BI and QA processes, including SQL Prompt, SQL Search, and SQL Doc.

“We want to extend DevOps to the database, with a fully
automated build, test, and deployment pipeline.”

Even with the advances and advantages they’ve already seen, Chris Yates and the team at Republic Bank aren’t finished yet. Successfully establishing a semi-automated process for database deployments puts them in a great position to take the next steps towards full database automation.

“We’ve started it,” says Chris Yates, “and implemented automation on the application side, but we’re not over the hump on the database side. This will further streamline the process for the DBAs, and give us a one stop shop all the way through. The business welcomes it. Our Director of Innovation is a huge supporter.”

Chris is also excited about Redgate’s new tool, SQL Clone, for provisioning production-like environments for realistic testing and troubleshooting. This will be useful for the developers as well as DBAs, who will be able to develop and test against isolated, realistic environments, without wasting disk space and taking hours to provision.

If you’d like to find out how to perform database comparisons and create accurate deployment scripts in minutes rather than hours, try SQL Compare free for 14 days.

You can also compare and deploy database contents quickly, simply, and with zero errors, with SQL Data Compare.