SQL Server DTS

Platform voor data-driven applicatie ontwikkeling

In dit artikel wil ik aan de hand van een op de praktijk gebaseerd scenario de mogelijkheden van de Microsoft SQL Server Data Transformation Services (DTS) bespreken. In de eerste paragraaf worden het scenario en de gestelde eisen beschreven. De rest van het artikel gaat in op hoe de oplossing is geïmplementeerd en de daarbij gemaakte keuzes.

Wat is het scenario?

Het basisprincipe van mijn scenario is dat er een tweetal databases zijn die data voeden aan een derde database, de rapportage database. In de rapportage database wordt data samengevoegd, er vindt een denormalisatie slag plaats en er worden statistieken toegevoegd.

Fig. 1: Schematische weergave scenario

Waarom is er gekozen voor een oplossing met een aparte rapportage-database? Omdat het in dit scenario grote aantallen gegevens betreft, is het noodzakelijk om iets te doen met de performance van de queries. Deze zijn geoptimaliseerd in de rapportage-database. Dit resulteert onder andere in een versimpeling van het database schema.

Zoals hierboven genoemd, betreft het databases die grote hoeveelheden data bevatten. Deze data moet in beperkte tijd kunnen worden overgezet van de twee productie-databases naar de rapportage-database. Een andere belangrijke eis is, dat de rapportages op geen enkele wijze de productie mogen verstoren. Tenslotte is er de eis dat de eindgebruiker zelf in staat moet zijn om met een query tool selecties uit te voeren.

Waarom wordt DTS gebruikt in dit scenario?

Om dit scenario te implementeren is er gekozen voor DTS. Naast DTS zijn de volgende alternatieven bekeken:

·          .NET Applicatie

·          Stored Procedures

In het geval van een .NET applicatie was het noodzakelijk om .NET expertise te gebruiken voor implementatie van het scenario, terwijl het uitsluitend om databasebewerkingen gaat. Daarnaast is de consequentie van .NET dat er impliciet wordt gekozen voor een row-based verwerking. Dit kan tot performance problemen leiden.

De keuze voor stored procedure(s) leidt tot veel code in een beperkte taal als Transact SQL (T-SQL). Het gevolg hiervan is een onoverzichtelijk proces, waardoor onderhoud in de toekomst bemoeilijkt wordt.

Uiteindelijk is de keuze dus gevallen op DTS. Met name de volgende aspecten zijn doorslaggevend geweest:

·          Performance: met DTS is het mogelijk om set-based verwerkingen uit te voeren.

·          DTS geeft schalingsmogelijkheden: een deel van het DTS proces kan op een andere server draaien dan de databaseserver.

·          DTS biedt standaard schedulingsmogelijkheden.

·          In de DTS designer is een helder, visueel beeld van de structuur van het proces te verkrijgen.

·          Het scenario kan volledig worden geïmplementeerd door iemand met een DBA-achtergrond.

·          DTS biedt goede mogelijkheden om het geheel configureerbaar te maken (flexibiliteit).

Wat is DTS?

Onder de naam Data Transformation Services levert Microsoft een grafische ontwikkeltool en programmeerbare objecten voor data-driven oplossingen. DTS is een onderdeel van SQL Server, maar runtime kan een DTS applicatie draaien met of zonder SQL Server. Dit geeft de mogelijkheid om met DTS een programma te schrijven dat als een aparte applicatie kan draaien of als onderdeel van een volledige (.NET) oplossing.

Kort gezegd is het met DTS mogelijk om in stappen data te verplaatsen tussen verschillende data sources en als onderdeel van dit proces data transformaties uit te voeren.

Een aantal scenario’s waarbij DTS toegepast kan worden zijn:

·          Conversie van de ene databaseomgeving naar de andere

·          Historische data aanmaken

·          Consolidatie van gegevens uit verschillende datasources

·          Vullen van een datawarehouse

Vanuit de Enterprise Manager kun je DTS applicaties bouwen met de DTS Package Designer. Daarnaast worden de DTS objecten bijvoorbeeld ook gebruikt door de SQL Server Import/Export wizard.

Fig. 2: Export wizard package in de DTS designer

Een DTS applicatie bestaat uit een of meerdere DTS packages. Een DTS package is een verzameling van connecties, taken en workflow.

De tasks die je definieert in je package doen het werk. Er is een aanzienlijk aantal standaard taken beschikbaar in DTS.

Tabel 1: DTS tasks

Het is belangrijk om de juiste task te kiezen voor iedere stap in het scenario. De keuze van de task kan een keuze voor een row-based of set-based bewerking of een T-SQL of VB-script statement inhouden. Bovendien bepaalt de keuze voor de task ook of deze wordt uitgevoerd in het SQL Server proces of in het DTS proces.

Connecties definiëren de bron en bestemming van de data. Een connectie kan door meerdere tasks worden gebruikt, maar er kan maar 1 task tegelijkertijd gebruik maken van een specifieke connectie. Een connectie is gebaseerd op OLEDB of ODBC.

Om de volgorde te bepalen van het uitvoeren van de tasks heb je de beschikking over drie workflow constraints:

·          On Success (groene pijl)

·          On Failure (rode pijl)

·          On Completion (blauwe pijl)

De workflow geeft de relatie aan tussen de verschillende taken en bepaalt de volgorde waarin de taken worden uitgevoerd.

Hoe is DTS ingezet?

De oplossing is gebouwd als een DTS package, die als een op zichzelf staande applicatie wordt gedraaid. Zoals weergegeven in figuur 3 is de package is opgedeeld in 4 fases: configuratie, initialisatie, overzetten gegevens, en clean-up. Per fase wil ik ingaan op de functionaliteit en de gebruikte tasks.

Fig. 3: Het complete scenario

1. Configuratie

Als eerste fase in het proces wordt de package geconfigureerd, dat wil zeggen dat de variabele gegevens worden ingelezen en worden toegekend aan de betreffende connectie en task properties. De informatie over de te gebruiken SQL Servers en databases wordt ingelezen vanuit een INI file. Met deze waarden worden globale variabelen in de package geconfigureerd, alsook de DataSource en Catalog properties van de drie gebruikte connecties gezet.

Fig. 4: INI file configuratie in de dynamic properties task

Een globale variabele in DTS definieer je op package niveau. De variabele is bruikbaar in alle tasks in de package, bijvoorbeeld in een script of als parameter in een T-SQL statement. De in stap 1 van deze fase geïnitialiseerde globale variabelen worden gebruikt in stap 2 van deze fase, de Active Script task MaakSQLStatement. Voorbeeldcode 1 is een fragment uit deze task waarin de AddLinkedServer variabele wordt gevuld met een SQL statement waarmee een Linked server wordt geconfigureerd. In stap 3 van deze fase wordt deze globale variabele dan weer toegekend aan de SQLStatement property van de Execute SQL task die wordt uitgevoerd in stap 4 van het scenario.

“Voorbeeldcode 1: VB script in ActiveX Script task

Bijna alles in een DTS package kun je dynamisch, tijdens runtime, configureren. Maar zoals uit dit relatief eenvoudige scenario al blijkt, zijn hier wel verschillende stappen voor nodig. Configuratie is eigenlijk altijd een combinatie van Dynamic properties task(s), ActiveX Script task(s) en globale variabelen.

2. Initialisatie

In het tweede deel van het proces wordt de SQL Server omgeving klaargemaakt voor het proces van het overhevelen van de gegevens van de productie omgeving naar de rapportage omgeving. De linked server wordt gecreëerd. Deze maakt het mogelijk om in stap 8 (zie figuur 6) met behulp van SQL statements een update uit te voeren op de data in de rapportage-database met gegevens uit de productie-database.

De tabel in de rapportage-database die de gedenormaliseerde data bevat wordt in stap 5 (zie figuur 6) leeggemaakt door het uitvoeren van een T-SQL TRUNCATE TABLE statement. Gezien de complexiteit van het bijhouden van wijzigingen is ervoor gekozen om deze tabel iedere keer in zijn geheel over te halen in plaats van alleen de wijzigingen te verwerken.

3. Overzetten gegevens

Het daadwerkelijke overzetten van de gegevens van de productieomgeving naar de rapportageomgeving gebeurt in een drietal stappen in de derde fase van het proces. Allereerst worden de referentiegegevens gekopieerd, daarna worden de berichten overgezet naar de rapportage-database en als laatste worden de gerelateerde gegevens vanuit de productie-database bijgezocht.

De rapportage-database is geïmplementeerd volgens een stermodel. Dat wil zeggen dat de basis tabel waarin alle berichtgegevens staan middels codes is gekoppeld aan een aantal referentietabellen. De keuze voor een stermodel is gemaakt om de grootte van de rapportage-database te beperken. Deze redundante omschrijvingen worden nu slechts eenmaal opgeslagen. Het aantal rijen in deze tabellen is zo klein, dat dit op de performance van de queries weinig of geen effect heeft.

Het overzetten van de referentiegegevens gebeurt met behulp van de Copy SQL Object task. Hiermee kan in één keer een selectie van tabellen worden gekopieerd vanuit de productie-database naar de rapportage-database. Bovendien kun je in deze task aangegeven dat de eventueel al bestaande tabel eerst moet worden verwijderd.

Het overhalen van de berichten is de volgende stap. Hiervoor wordt een Transform Data task gebruikt. De keuze voor deze task komt voort uit het gegeven, dat er een aantal kolommen is waarvoor per rij moet worden bepaald wat de waarde wordt en dit niet of niet eenvoudig in een T-SQL statement te coderen is.

De basis van deze task is een query die de gegevens uit een aantal gerelateerde tabellen uit de berichten-database selecteert. Deze gegevens worden in gedenormaliseerde vorm opgeslagen in één tabel in de rapportage-database. De meeste van de transformaties uit figuur 5 zijn eenvoudige copy column transformaties, maar enkele transformaties zijn gedefinieerd door middel van script, zoals bijvoorbeeld de SetBron transformatie.

Fig. 5: Transformaties van de berichten

Nadat de berichtgegevens zijn overgehaald, worden hieraan gegevens toegevoegd vanuit de productie-database. Ook dit is een denormalisatieslag. Deze gegevens zijn niet direct in de query opgenomen, omdat dit om een 16-tal kolommen gaat waarbij per kolom 3 tot 7 tabellen moeten worden geraadpleegd. Dit is gewoonweg niet in één statement te definiëren. Het SELECT statement zou zo complex worden dat dit niet meer onderhoudbaar is. Daarnaast was de performance niet acceptabel als gevolg van het enorme product aan gegevens dat wordt gecreëerd door het noodzakelijke gebruik van LEFT OUTER JOINS.

Behalve het opnemen van alle gegevens in 1 query is het ook mogelijk om de gewenste functionaliteit te realiseren door gebruik te maken van lookups in het transformatieproces. Een lookup is een geparametriseerd select statement dat je kunt gebruiken in een ActiveX script om een kolom te vullen.

Voorbeeldcode 2: Lookup in ActiveX script transformatie

In dit scenario ging het dus om een 16-tal lookups. In eerste instantie is dit scenario op deze manier geïmplementeerd. Dit was snel gedaan, en het levert overzichtelijke code op in het package. Bij testen bleek echter dat de verwerking van een miljoen berichten meer dan 24 uur duurde en dat was niet acceptabel. Door de lookups te vervangen door ‘gewone’ T-SQL UPDATE statements in een Execute SQL task is de verwerkingstijd teruggebracht naar minder dan 2 uur.

Voorbeeldcode 3: T-SQL UPDATE statement stap 8

4. Cleanup

Nu de gegevens overgezet zijn wordt in stap 9 de linked server die speciaal voor dit scenario gedefinieerd is, weer verwijderd.

Het package uitvoeren

Om de DTS applicatie uit te voeren moet het package worden gestart. Dit kun je op  verschillende manieren doen, waarvan de belangrijkste de volgende zijn:

·          Handmatig vanuit de DTS designer

·          Handmatig vanuit de Enterprise Manager

·          Scheduled vanuit de Enterprise Manager

·          Handmatig vanuit DTSRUN

·          Scheduled als Windows task vanuit DTSRUN

·          Geautomatiseerd vanuit een (.NET) applicatie

In dit scenario wordt het package op 3 verschillende manieren gestart. Tijdens de ontwikkeling wordt de DTS designer gebruikt om het package te testen. Dit heeft als voordeel dat je ook iedere stap afzonderlijk kunt uitvoeren en dus testen.

In productie wordt het package gescheduled als een Windows task waarbij de DTSRUN utility automatisch wordt gestart met de juiste parameters. Voor ad-hoc situaties wordt het package handmatig gestart met DTSRUNUI. DTSRUN is een applicatie die wordt meegeleverd met DTS die het mogelijk maakt om DTS packages losstaand van SQL Server te starten. Er bestaat een DTSRUNUI met een user interface en een DTSRUN zonder user interface.

Fig. 6: Handmatig starten van een package met DTSRUNUI

Wat gebeurt er als het fout gaat?

De foutafhandeling is in dit scenario op een eenvoudige, maar afdoende manier geïmplementeerd. Het gehele package draait als 1 transactie. Dat betekent dat ofwel alle stappen uit het package uitgevoerd zijn, ofwel geen enkele stap uit het package is uitgevoerd. Daarnaast is op package niveau gekozen voor de opties:

·          Fail package on first error

·          Write completion status to eventlog

Fig. 7: Package executie met fout

In figuur 7 zie je dat de stappen 1 t/m 7 succesvol zijn uitgevoerd, stap 8 een fout oplevert en stap 9 niet is uitgevoerd. Dit scherm geeft de status weer van het proces. Door de package-configuratie wordt het uitvoeren van het package beëindigd, zodra er een fout optreedt en wordt de transactie teruggedraaid. De meldingen worden opgeslagen in een log-file. Dit log-file wordt dynamisch aangemaakt op basis van een locatie en een naam die in de eerste fase van het proces worden gedefinieerd. In voorbeeldcode 4 zie je de gelogde melding naar aanleiding van de fout uit figuur 7. Uit dit fragment wordt ook duidelijk dat je in dit log meer informatie kunt vinden, zoals de tijdsduur van iedere task.

Voorbeeldcode 4: Errorlog fragment

Je kunt transactiegebruik en error-logging op een veel gedetailleerder niveau per stap definiëren, maar voor dit scenario is dat niet nodig.

Wat zijn de voor- en nadelen van het gebruik van DTS?

Het is qua performance altijd beter om een T-SQL statement te gebruiken in een Execute SQL task dan om een Transformation task te gebruiken. Niet alleen verleidt de Transformation task tot het gebruik van VB script, maar ook is het row-based verwerkingskarakter van deze task slecht voor de performance. De enige uitzondering hierop is indien de Transform Data task wordt geïmplementeerd als een enkele copy column transformatie in combinatie met de use fast load optie. Dit resulteert in een bulk insert T-SQL statement.

Mocht je een oplossing moeten bouwen die voornamelijk bestaat uit data bewerkingen en heb je SQL Server tot je beschikking, kijk dan eens naar DTS!