Standardisering av changelog

Abstract

Viktigheten av å kunne vise til gamle data blir viktigere og viktigere jo større et system blir og jo flere det er som bruker systemet. En bør ha muligheten til å kunne hente data fra backup om feil skulle inntreffe. Det er også interessant å kunne vise hvordan en verdi endret seg, av hvem og når. Dette vil kunne invitere til mer samarbeid mellom kollegaer, og mindre trykk på support.

Westcon og WIN benytter seg av SQL-Server 2016, hovedfokuset for å finne en løsning til å lagre og vise historiske data ble derfor naturlignok knyttet til SQL-Server 2016. Mulige løsninger ble funnet og undersøkt for de mulighetene de hadde og hvordan de passet inn i kravene til det Westcon ønsket at WIN skulle vise brukerne av WIN.

Løsningene som ble undersøkt var Change Tracking, Change Data Capture, Temporale tabeller og en løsning med bruk av triggere. Westcon har en omfattende backup løsning der det lages backup for hver dag, hver uke og hver måned. Fokuset for endringsloggen som nevnes i denne rapporten var derfor å finne en løsning som kunne brukes til å vise brukere de siste endringer på et skjermbilde. Hvem endret verdien, hva var den gamle verdien, hva er den nye verdien og når ble endringen gjort. WIN er også et system som er i konstant utvikling, løsningen måtte kunne håndtere dette.

Change Tracking lagrer unna primærnøkkel til raden og hvilken operasjon som ble utført på raden, men ingen info om gammel og ny verdi. Change Data Capture lagrer unna, enten de kolonnene en ønsker å følge, eller hele raden i en historisk tabell. Løsningen fanger derimot ikke opp nye kolonner i tabeller og passer derfor dårlig i et system som WIN, der endringer skjer fra uke til uke.

Denne rapporten fokuserte derfor på Temporale tabeller, som ble lansert i SQL-Server 2016 og en løsning med bruk av triggere. Det ble utført tester på begge løsningene med innsetting, oppdatering og sletting av data på testtabeller med hver unik løsning implementert. Etter dette ble tider og lagringsvolum sammenlignet for å gi et bilde på hvordan hver løsning presterte.

Det en fant var at begge løsninger var relativt like i utførelses tid, men litt overraskende så hadde historietabellen til løsningen med triggere fra 2-10 ganger høyere lagringsvolum enn for temporale tabeller. Dette selv om en i løsningen med triggere bare lagret unna data i 13 kolonner, hvor kun 2 var av datatype nvarchar(max). Temporale tabeller lagret hele rader ved hver oppdatering og sletting. Flere av kolonnene som ble endret var av type nvarchar(max).

Enkeltheten med å vise dataene fra løsningen med triggere fremfor løsningen med temporale tabeller er en av grunnene til at dette ser ut til å bli løsningen WIN kommer til å bruke. Tidsreiseperspektivet med temporale tabeller er derimot en interessant funksjonalitet, og om det kan lages et view som presterer bra og viser kun endrede kolonneverdier, så er dette en løsning som vil undersøkes videre.

The importance of being able to show historical data becomes more important the larger the system becomes and the larger the userbase becomes. One should have the opportunity to retrieve data from a backup if an error should occur. It is also interesting to show how a value changed, by whom and when. This could lead to more cooperation between coworkers and lessen the need for support.

Westcon and WIN use SQL-Server 2016, the main focus for finding a solution to store and show historical data was therefore tied to SQL-Server 2016. Different solutions were found and investigated for the possibilities they had and how they fit to what Westcon wanted WIN to show to the users.

The solutions that were examined were Change Tracking, Change Data Capture, Temporal Tables and a solution based on the use of triggers. Westcon already has a backup solution where a backup is created every day, week and month. The focus for the changelog that is mentioned in this report is therefore to find a solution that could be used to show the latest changes on a table to the users. Who changed the value, what was the old value, what was the new value and when was the change done. WIN is a system that is in constant change, the solution therefore had to be able to handle that.

Change Tracking stores the rows primary key and what operation that was done on the row, but no information of the old or new value. Change Data Capture stores either, the columns selected for tracking or the entire row in a historical table. The solution does not adapt when new columns are added to a table. Therefore it is not suitable for WIN, which changes from week to week.

This report focused om Temporal tables, which was introduced in SQL-Server 2016 and a solution base don the use of triggers. Tests where executed on both solutions with inserts, updates and deletion of data on test tables, where each of the unique solutions were implemented. How each solution performed in terms of time and increase in storage volume were compared, this to give a picture on how they performed in comparison to each other.

It was found that both solutions performed similarly in terms of time, but a bit surprising was the fact that the storage volume of the history table of the trigger based solution increased with a factor of 2-10 times higher than that of the temporal tables history table. This happened even though the history table for the trigger based solution only had 13 columns, where only two where of the datatype nvarchar(max). Temporal tables stored entire rows on every update and delete, many of the columns that were changed were of the datatype nvarchar(max).

The simplicity of showing the data with a solution based on triggers as apart from temporal tables, is one of the reasons why this solution seems to be the preferred one for WIN. The time travel perspective with temporal tables is a interesting functionality and if a view that shows changed column values that also performs well, this is a solution that will be investigated further.