Migratie naar Windows Azure SQL Database (Deel 2)

In de vorige post hebben we gezien hoe je het databaseschema migreert naar Windows Azure SQL Database. Nu is het de hoogste tijd om de data in de cloud te krijgen. Op dit vlak is er weer veel mogelijk. Op de Microsoft site wordt melding gemaakt van een drietal opties:

1. SQL Server Integration Services (SSIS)
2. Bulk Copy tool (bcp)
3. Windows Azure SQL Database Migration Wizard (een community product dat niet ondersteund wordt)

Logischerwijs verwacht ik dat de meest betrouwbare oplossing bovenaan staat, dus beginnen we met SQL Server Integration Services (SSIS), of eigenlijk de SQL Server Import and Export Wizard, dat een onderdeel is van SSIS. Je kunt dit tooltje op vele manieren starten, maar ik verkies toch de vertrouwde omgeving van SQL Server Management Studio (SSMS). We vinden het onder "Tasks->Export Data" (rechtermuisklik op database). Wederom krijgen we een introductiescherm (afbeelding 1) voorgeschoteld, van waaruit we maar snel verder gaan.

Afbeelding 1. Introductiescherm van de Import and Export Wizard

Allereerst moeten we aangeven waar de data vandaan moet komen (afbeelding 2). Dat is natuurlijk onze on-premise database. We specificeren dan ook de lokale verbindingsgegevens.

Afbeelding 2. Connectiegegevens lokale database.

Vervolgens specificeren we de verbindingsgegevens van de database waar de data naar toe moet (afbeelding 3). Merk op dat de "Server Name" het hostgedeelte is van de url in de database portal van Windows Azure SQL Database. Wanneer dit alles goed is ingevuld, zul je de reeds aangemaakte database in de dropdownlijst zien verschijnen en kunnen we door naar het volgende scherm (afbeelding 4). Van hieruit moeten we een keuze maken (het zijn er maar twee deze keer):

1. Gewoon lomp alles kopiëren
2. Zelf queries samenstellen om zo meer invloed te hebben.

Nu ben ik niet zo'n held als het aankomt op het schrijven van T-SQL queries, dus de keuze is snel gemaakt. Het wordt optie 1, de lompe methode en snel door naar het volgende scherm (afbeelding 5).

Afbeelding 3. Connectiegegevens SQL Azure database

Afbeelding 4. Zelf queries bouwen of het laten doen

In dit scherm moeten we aangeven van welke tabellen we de data willen exporteren. Dat zijn in ons geval alle tabellen, dus we vinken de checkbox in de header aan. Alle tabellen zijn nu aangevinkt en geselecteerd (belangrijk voor de volgende stap). Ga nu naar "Edit Mappings" (afbeelding 6. Als je dat venster niet krijgt, heb je waarschijnlijk maar één tabel geselecteerd in afbeelding 5). In dit venster geven we aan hoe omgegaan moeten worden met reeds bestaande tabellen en/of reeds aanwezige data. De tabellen zijn bij mij nog maagdelijk schoon, dus die kunnen uit blijven. Van belang is de laatste optie: "Enable Identity Insert". Onze primary keys worden automatisch gegenereerd, we kunnen ze niet zelf opgeven. Het is wel van belang dat de primary keys gelijk blijven omdat er vanuit andere tabellen naar verwezen wordt. Deze optie zorgt ervoor dat de primary keys uit onze dump worden meegenomen in Windows Azure SQL Database en dat er geen nieuwe waardes worden gegenereerd.

Afbeelding 5: welke tabellen?

Afbeelding 6. Edit mappings.

Een "OK" en een "Next" later komen we op het nogal ruim opgezette scherm van afbeelding 7. We kunnen kiezen om de package meteen uit te voeren of niet. Eigenlijk is het geen keuze, want als je die checkbox uitzet, kom je geen stap meer verder. Bovendien, met de meet zó dicht in zicht, kan ik natuurlijk niet wachten, dus we gaan hem meteen uitvoeren ("Finish").

Afbeelding 7. Meteen uitvoeren of later

Het resultaat is nogal teleurstellend (zie afbeelding 8). Bij het kopiëren van de eerste tabel gaat het al meteen mis. Gelukkig krijg ik een enorme sliert aan fouteninformatie waarin ik ergens halverwege de volgende melding tegen kom

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ProductScore_Product". The conflict occurred in database "Intermed", table "Core.Product", column 'ID'.".
(SQL Server Import and Export Wizard)

Oh jee! Er wordt verwezen naar een record dat nog niet is geïmporteerd. 

De volgorde waarin de tabellen worden geïmporteerd is van belang. Ik ben nu veroordeeld tot het schrijven van mijn eigen queries of het uitzetten van de FOREIGN KEY constraints (die later natuurlijk weer aan moeten). Beide opties vergen veel tijd en de uitkomst is allerminst zeker. Misschien toch maar eerst op zoek naar een ander tooltje.

Afbeelding 8. :-(

Bulk Copy Utility (bcp) is een handig tooltje om grote hoeveelheden data over te pompen van de ene database naar de andere. De syntax ziet er als volgt uit:


bcp {[[database_name.][schema].]{table_name | view_name} | "query"}
    {in | out | queryout | format} data_file
    [-m max_errors] [-f format_file] [-x] [-e err_file]
    [-F first_row] [-L last_row] [-b batch_size]
    [-d database_name] [-n] [-c] [-N] [-w] [-V (70 | 80 | 90 )] 
    [-q] [-C { ACP | OEM | RAW | code_page } ] [-t field_term] 
    [-r row_term] [-i input_file] [-o output_file] [-a packet_size]
    [-S [server_name[\instance_name]]] [-U login_id] [-P password]
    [-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]


Dat biedt perspectief. Ik kan per tabel kopiëren, maar dat maakt de migratie wel héél bewerkelijk. Ik ben waarschijnlijk een dag (of langer) bezig om alles in de juiste volgorde te krijgen. Bovendien ziet die enorme waslijst aan schakelopties er angstaanjagend uit. Snel door naar de volgende optie.

Windows Azure SQL Database Migration Wizard is een tool, dat door de community is gemaakt. Het feit dat dit tooltje een vermelding krijgt op de Microsoft site, doet vermoeden dat het goed werkt. We gaan eens een poging wagen.

Na het starten van SQLAzureMW.exe krijgen we het scherm in afbeelding 9. Het is duidelijk dat deze applicatie voor meer doeleinden ingezet kan worden,  maar ik ben alleen geïnteresseerd in de optie "Analyze / Migrate". Selecteer hier de optie "Database" en op naar het volgende scherm (afbeelding 10). 

Afbeelding 9. Startscherm van SQLAzureMW

Afbeelding 10. Verbinding maken met de lokale database.

Hier wordt de verbinding naar de on-premise database opgegeven. In de groep "Database" heb je de mogelijkheid om een lijst van alle databases in de master op te vragen (aanbevolen) of een specifieke database te selecteren. Na het maken van de connectie kom je uit op het scherm in afbeelding 11. Hier selecteer je de te exporteren database en vervolg je je weg naar het scherm in afbeelding 12.

Afbeelding 11. Selecteer de te migreren database

Afbeelding 12. De opties

Hier kun je aangeven wát je van de database wilt exporteren. Het ziet er allemaal even bruikbaar uit dus ik pak de bovenste optie (script all database objects). Nu is het belangrijk om even op de "Advanced" toets te drukken. Het geeft je de mogelijkheid om aan te geven hoe en wat er geëxporteerd moet worden (afbeelding 13). Het tooltje blijkt ook het schema van de database te kunnen exporteren. Jammer dat ik dat niet eerder wist. Hoe dan ook, mijn schema is al geïmporteerd, dus bij "Script Table/ Data" specificeer ik "Data Only"

Afbeelding 13. Nog meer opties.

Een paar kliks verder begint het programma een aantal .dat bestanden te genereren. Zolang de teksten groen blijven is er niks aan de hand (afbeelding 14). Opmerkelijk is dat de bulk copy (bcp) tool gebruikt wordt. Deze applicatie doet precies dat werk dat mij ervan weerhield om bulk copy in de eerste plaats te gebruiken.

Afbeelding 14. De databestanden zijn gegenereerd.

Tenslotte moeten we nog aangeven naar welke database de data moet (afbeelding 15). Dit gaat op dezelfde manier als bij de lokale databse. Let op het username veld. Het format ziet eruit als login@azureserver. Mocht je toch alleen je login specificeren, is het tooltje handig genoeg om er zelf het @-gedeelte eraan toe te voegen.

Afbeelding 15. De verbindingsgegevens van de target database.

Vervolgens moet je nog aangeven naar welke database in de cloud de data moet en daarna begint de importfase (afbeelding 16).

Afbeelding 16. Het uploaden van de data in de cloud

Alles ging goed. De data staat nu in de cloud en ik heb niets in de database hoeven voor te bereiden. Windows Azure SQL Database Migration Wizard is zijn vermelding op de Microsoft site meer dan waard.

We hebben nu drie manieren gezien om data in Windows Azure SQL Database te krijgen.
De import/export wizard van SSIS is te gebruiken, maar je moet wel rekening houden met de foreign key constraints (en wellicht ook andere constraints). De constraints bij voorkeur even verwijderen en later weer toevoegen.
De bulk copy (bcp) geeft je optimale controle over je export/import proces. Het is wel erg veel werk om die commando's voor te bereiden en om de volgorde van importeren te bepalen.
Windows Azure SQL Database Migration Wizard maakt gebruik van bcp en bepaalt de import volgorde. Erg handig tooltje en met afstand de winnaar!