PASS Data Community Summit logo

PASS Data Community Summit 2023

Get all the latest announcements direct to your inbox

Snapper’s Sweet Source Control

Corrin Lakeland's busy role as a Data Manager includes supervising the work of all database development. Although he was already a keen user of SQL Compare, the arrival of SQL Source Control came as a godsend to him. He explains why.

Snapper is a payment system in New Zealand for quick transactions using contactless smartcards. It’s quite like Oyster in London except also servicing other low value payments such as for buying newspapers or coffee, or paying for a taxi. Snapper has created a brand new payment category, based on small value payments. Snapper uses KSCC (Korean Smart Card Company) for a lot of backend systems but over the last year it has started developing internal capabilities. My role as Data Manager is to look after all data at Snapper. This principally involves managing the data warehouse but also covers sending data to KSCC and a few smaller databases written in MySQL and Oracle.

I am the only database specialist employed by Snapper, though most of the time I have a contractor working for me. Because KSCC look after the central system, Snapper has not needed many staff, though this is changing as we bring more services back in-house. I perform all check-ins myself in order to ensure that all code is reviewed before going into production.

Coming from a programming background I’d always been uncomfortable that my databases were not managed in version control. When I joined Snapper, our database development work tended to happen on the test server because it was easier. Also, the migration of changes to production was much more painful and so was batched and only released when major milestones were reached. I found the data warehouse’s documentation did not match the system that was running in production. Before I had SQL Source Control, I could not put my hand on my heart and say that I could trust the data in the system because I knew what code transformed it.

With SQL Source Control, I know I can push changes out as soon as they pass testing. Because I have taken ownership of performing all check-ins, the developers haven’t noticed very much change and they have just performed their work as usual. I did receive some comments that I seem to be checking their work more effectively since I started using it and catching more bugs. This is because the tool highlights differences and so I pick up on changes that the developers forgot they had made. Basically, using SQL Source Control has saved Snapper a considerable amount of time (and therefore money) and also lead to a higher standard of work. It is rather like having SQL Compare on steroids.

Some benefits are like those of using SQL Compare to manage source code:

  1. I can do my development on a clean system and be absolutely certain that at the end of the job I can migrate all my work to testing
  2. I am much more willing to make sweeping design changes because I know that if it doesn’t work out I can go back to where I was without worrying about database backups
  3. I used to have all the important scripts manually managed in source control using Tortoise but frequently I’d make a change to the script and forget to save it to version control. SQL Source Control gives me absolute confidence that the code in production matches the code I reviewed. Nobody can make a change in production and forget to document it without me noticing.

Over and above these benefits, there are some that are unique to SQL Source Control:

  1. Another benefit which I’ve been really happy with is the ability to edit the database outside SQL Server.
    I’ve got excellent editors with very powerful find/replace features but they only work on files. SQL Source Control has enabled me to perform changes wherever it is easiest. Sometimes it’s easy using sp_rename in the database, other times it’s easier to use find and replace in a text editor. For instance I needed to validate that the primary key of every table matched the design standards and my T-SQL is not up to doing this in the database. Without SQL Source Control I would’ve had to do this one table at a time but with it I was able to perform the validation and correction on files and then commit the changes straight back to the database.
  2. SQL Source Control links to Subversion. The programmers at Snapper also use Subversion and this has meant that I have been able to directly integrate the database into a tool they know well. As a result whenever one of them asks for information about something the database does I have been able to point them at code using an interface they are already comfortable working in.
  3. Along similar lines, Snapper uses Jira for Subversion hosting. As a result I can have a user submit a change request or bug online in Jira, resolve that bug in dev and then mark that bug as closed using my commit message. Uses can even track the migration of the revision which closed their bug from development through testing to production. All because SQL Source Control integrates into the tools that Snapper already uses.
  4. Another surprise benefit has been the learning process for me about writing reliable T-SQL. Coming from Oracle my knowledge of T-SQL and MS SQL is weak and I learned a lot by reading and reviewing the change scripts that SQL Source Control generated.
  5. If I find an approach I’m taking in development isn’t working well, I can easily roll back to an older version where everything was going well and try a different approach. Sure, I back up production but I don’t back up the development machines and anyway, it’s the code I want to revert rather than the data. Similarly I don’t comment out code any more – it’s a piece of cake to get old code out of version control so there’s no need to clutter my scripts with how things used to be.

What SQL Source Control has enabled me to do is to catch every single little change I make in development so I can be certain I’m making the same changes on test and haven’t missed any. All those times in the past where a developer has supplied a change from their machine but forgotten to include an index or similar, and so it’s been rejected in pre-production – they just can’t happen any more.

SQL Compare also offers this function. I view SQL Compare as having all the essential functionality and SQL Source Control as having lots of great extra features over and above SQL Compare but nothing critical.

I’d recommend SQL Source Control to anyone with a similar task to mine. It has become an invaluable addition to my tool belt. It’s like SQL Compare, saving me hours in generating top quality change scripts but even better with history, integration into development tools and generally matching how I’m used to working.