maandag 22 april 2013

AdWords Automatiseren (Inleiding)

Bij commerciële websites werken veel mensen. Die mensen moeten betaald worden. Daarnaast komen er nogal wat kosten bij, zoals hostingkosten, huisvestingskosten, inrichtingen en ga zo maar door. Ook die kosten moeten ergens van worden betaald. Die inkomsten komen van banners op de site, die irritante dingen zorgen ervoor dat jij gratis van de dienst gebruik kunt maken. In de regel geldt dat, hoe irritanter de banner, hoe meer die oplevert. Wat levert zo'n banner nu eigenlijk op? Bij BesteProduct heb ik banners gezien die acht euro opleveren als erop geklikt wordt. En dat is dan nog een kleine site. Wat zou dan een banner op de drukst bezochte website ter wereld opleveren? Dat moeten ze bij Google ook gedacht hebben, vandaar dat ze zijn uitgegaan van het principe "wat de gek ervoor geeft". Daarom hanteren ze een biedingsysteem waarbij geldt, hoe meer je dokt, hoe meer je vooraan komt te staan. Dat betekent dus op pagina één van de zoekresultaten.
Om die hoge prijs te rechtvaardigen is het natuurlijk wel handig dat jouw advertentie ook bij de doelgroep aankomt, dus als iemand naar een carburateur zoekt, hij niet geterroriseerd wordt met reclame over waterbestendige mascara, neushaartrimmers en eeltraspen. Dit is dus precies waar AdWords goed is, het laten zien van advertenties op basis van wat de gebruiker zoekt. (afbeelding 1)


Afbeelding 1. De advertentieblokken op Google waarop je met Adwords kunt bieden. Die op pagina 1 zijn het duurst.

Het gaat er dus om dat je advertenties definieert die alleen tevoorschijn komen als de gebruiker op bepaalde zoekwoorden zoekt. Dit klinkt eenvoudig en dat is het ook. Helaas heeft Google besloten om er een enorm oerwoud aan constructies en kreten omheen te gieten die voor een marketing expert ongetwijfeld bekend in de oren klinken, maar voor een leek als ik toch moeilijk te duiden zijn. Toch zal ik proberen een hele oppervlakkige introductie van dit systeem te geven. Let wel, dit is een marketingsysteem. Er zit gruwelijk veel meer in dan hier beschreven. Gelukkig heeft Google een uitstekende help geschreven voor de belangstellenden.

Wanneer je een AdWords account hebt aangemaakt, kun je campagnes definiëren. Binnen zo'n campagne vind je advertentiegroepen. Zo'n advertentiegroep is een verzameling van gerelateerde advertenties en zoekwoorden. In afbeelding 2 zie je dat schematisch weergegeven.


Afbeelding 2. Globale structuur van een AdWords account (bron: Google)
Als campagne kun je bijvoorbeeld denken aan camera's. Advertiegroepen zouden dan worden: Digitale camera's, spiegelreflex camera's en camcorders.

Wanneer je op AdWords inlogt, kom je op het welkomsscherm van afbeelding 2. 


Afbeelding 3. Het welkomsscherm van Google AdWords.

Hier vind je veel informatie over hoe het allemaal werkt en zelfs een filmpje voor als je geen zin hebt om die lappen tekst door te nemen. We gaan meteen door naar "Create your first campaign" hetgeen ons naar het scherm in afbeelding 3a voert. Meteen is het bingo. Een stortvloed aan kreten en een dreigende melding dat deze account opgeheven is, maar dat mag de pret niet drukken.


Afbeelding 3a. 
Afbeelding 3b.

Laten we eerst maar een rustig bekijken wat we hier allemaal zien. 
  1. Campaigne name. De naam van de campagne. Bijvoorbeeld Camera's
  2. Type. Bevat een aantal standaard instellingen afhankelijk van het gekozen netwerk. Je hebt drie opties
    1. Search Network. Hierbij wordt jouw advertentie getoond op zoekmachines zoals Google, Maps, Images, maar ook AOL
    2. Display Network. Dit zijn sites die Google Ads laten zien (door gebruik te maken van AdSense of DoubleClick). Dat kunnen ook mobiele apps zijn.
    3. Beide opties.
  3. Network: Hiermee kun je de hierboven gekozen opties wat verfijnen.
  4. Locations. In welke landen wil je jouw advertentie getoond hebben.
  5. Languages. In welke talen.
  6. Bidding and Budget. Hier stel je in hoe en hoeveel je wilt bieden. Bij Budget geef je aan hoeveel je per dag wilt besteden en bij de Bidding Options geef je aan waarop je wilt bieden. Je hebt de volgende opties:
    1. CPC (Cost-Per-Click). Wat gaat het je kosten als een bezoeker daadwerkelijk op jouw advertentie klikt. Kun je handmatig instellen of je laat Google de meest optimale bieding bepalen.
    2. CPM (Cost-Per-Thousand impressions). Hierbij betaal je een bedrag voor iedere duizend keer dat jouw advertentie wordt getoond.
    3. CPA (Cost-Per-Acquisition). Hierbij geef je aan hoeveel je bereid bent te betalen voor een bepaalde actie op jouw website. Je kunt bijvoorbeeld denken aan een aankoop als gevolg van de advertentie.
    4. Laat AdWords maar bepalen om zo het maximaal aantal kliks te krijgen.
Nadat de campagne is opgeslagen, begin je met het definiëren van een advertentiegroep. Dit is niets anders dan specificeren van een logische naam waaronder je al jouw advertenties groepeert (afbeelding 4). 


Afbeelding 4. Het definieren van een advertentiegroep en een advertentie.


Vervolgens maak je een advertentie aan. Hier definieer je de koptekst, twee beschrijvende regels, de weergave url en de link naar de website (afbeelding 5). 


Afbeelding 5. Een advertentie, zoals die typisch aan de rechterkant van Google Search verschijnt.


In de teksten kun je gebruikmaken van een jokertekst. Op deze postitie wordt dan een gedefinieerd zoekwoord geïnjecteerd. Deze jokertekst ziet er als volgt uit:

{keyword: zoekwoord}

De accolades zijn hierbij verplicht. de sleutel "keyword:" neem je letterlijk op. Het "zoekwoord" is een zoekwoord uit jouw collectie van zoekwoorden. Stel je hebt de volgende zoekwoorden: Rode shirts, witte shirts en blauwe shirts en je hebt de volgende tekst gedefinieerd:

De goedkoopste {keyword: Shirts} van Nederland

Wanneer een gebruiker zoekt op "rode shirts" zal de volgende tekst in de advertentie verschijnen:

De goedkoopste rode shirts van Nederland.

Ook met "keyword" kan nog wat gespeeld worden. Hierbij kun je variëren in het gebruik van hoofdletters. Bijvoorbeeld:

{Keyword: Shirts}: De goedkoopste Rode shirts van Nederland
{KeyWord: Shirts}: De goedkoopste Rode Shirts van Nederland
{KEYword: Shirts}: De goedkoopste RODE shirts van Nederland
{keyWORD: Shirts}: De goedkoopste rode SHIRTS van Nederland

(Zie Google help voor meer informatie)


Tenslotte geef je één of meerdere zoekwoorden op die deze advertentie moeten triggeren (afbeelding 6). Dit kan op grofweg drie manieren:
  1. Breed zoeken. Hierbij wordt het opgegeven zoekwoord in een groter geheel gezocht. Zo zal het zoekwoord rode matchen met "rode schoenen", "rode shirts" en "prijs rode auto's".
  2. Zoeken op woordgroep (aangegeven met aanhalingstekens). Hierbij zoek je naar een exacte woordgroep. Bijvoorbeeld "Rode schoenen" zal matchen met "rode schoenen" en "Veel rode schoenen in de uitverkoop"
  3. Exact zoeken (aangegeven met blokhaken). Hierbij wordt jouw advertentie alleen getoond wanneer het zoekwoord exact matcht. Bijvoorbeeld [rode schoenen] zal matchen met "rode schoenen" en "rodde schoenen".
Daarnaast kun je aan zoekwoorden een url naar jouw relevante site koppelen, zodat Google meteen de juiste link kan presenteren als op jouw zoekwoord gezocht wordt.

Afbeelding 6. Opgeven van de zoekwoorden
Het is mooi om dit alles met de hand in te voeren, maar als je veel variabele data hebt, is het wellicht handig om de aanmaak van deze campagnes te automatiseren.
Gelukkig biedt Google hiervoor een api aan waarmee je campagnes kunt inschieten. Dat is dan ook meteen de volgende opdracht: Het automatiseren van AdWords waarover ik volgende week of de week daarop zal berichten (de api is niet eenvoudig).

woensdag 17 april 2013

Backups van Windows Azure SQL Database

Hoe veilig is jouw data in de cloud eigenlijk? Dat hangt helemaal af van wat je onder veilig verstaat. Veiligheid kan betrekking hebben op de mate waarin derden over jouw data kunnen beschikken. Nou, dan ben je in de cloud niet goed af, tenminste, als de cloudprovider een Amerikaans bedrijf is (en dat is Microsoft). Na de aanslagen van 9/11 heeft George W. Bush besloten om de USA Patriot Act (United and Strengthening America by Providing Appropiate Tools Required to Intercept and Obstruct Terrorism) in te stellen. Deze act stelt de Amerikaanse overheid in staat om data van de systemen van Amerikaanse bedrijven op te vragen. Als jouw data toevallig op een Amerikaans systeem staat, dan kan de overheid die dus ook inzien. Het maakt daarbij niet uit waar in de wereld die data staat. Kort samengevat, als jouw data in Windows Azure draait, ook al staat het datacentrum fysiek in Nederland, dan kan de Amerikaanse overheid die data inzien. Dit geldt dus ook voor andere cloudproviders zoals Google en Amazon. Deze act is in 2011 door Barack Obama nog eens met vier jaar verlengt. Je zit er dus tot 2015 aan vast.
In dit artikel wil ik het hebben over een heel andere vorm van veiligheid, namelijk in welke mate mijn data beschermd is tegen hardware failures, datacorruptie en andere rampen.

Wanneer je in Windows Azure SQL Database een database aanmaakt, worden er eigenlijk drie databases aangemaakt. Eén is de primaire replica. Dit is de database waarop jij jouw werkzaamheden uitvoert. Daarnaast zijn er twee secundaire replica's waar je verder niets mee doet. Wanneer de primaire replica eruit vliegt, zal onmiddelijk een secundaire replica worden ingeschakeld. Zo'n secundaire replica is een exacte kopie van de primaire. Wanneer er een transactie plaatsvindt op de primaire replica, zal die transactie pas slagen als de transactie op één secundaire replica ook gelukt is (Quorum Based Commit). Daarnaast houdt Windows Azure SQL Database ook nog eens kopieën van de afgelopen veertien dagen bij. Helaas heb jij aan die duplicaten niet zoveel, ze worden alleen intern gebruikt mocht het eens helemaal uit de klauwen lopen.

Het komt er dus op neer dat je zelf moet zorgen voor backups, maar hoe doe je dat eigenlijk in Windows Azure SQL Database? Daar zijn een aantal strategieën voor bedacht:
Een kopie maken van de database is de meest eenvoudige. Er is maar één statement voor nodig en, niet geheel onbelangrijk, hij is volledig consistent met de originele database. Wijzigingen tijden de kopieeractie worden meteen gerepliceerd naar de kopie. Nadeel is dat je een of meerdere databases in de cloud krijgt en dat kost weer centen. Er zijn twee situaties denkbaar:
  1. Een kopie naar een database op dezelfde server
  2. Een kopie naar een database op een andere server.
Een kopie maken naar dezelfde server is één statement in Sql Azure

CREATE DATABASE DatabaseB AS COPY OF DatabaseA;

Daarvoor moet je wel eerst op de master database inloggen (afbeelding 1)
Afbeelding 1. Inloggen op de master database
Een kopie van de database naar een andere server werkt nagenoeg hetzelfde. Je logt eerst in op de master database van de bestemmingsserver en dan voer je het volgende commando in:

CREATE DATABASE DatabaseB AS COPY OF servernaam.DatabaseA

Bijvoorbeeld:

CREATE DATABASE DatabaseB AS COPY of diid89wef.DatabaseA

Je kunt er ook voor kiezen om de backup via een backup package (BACPAC) te laten verlopen. Hiervoor kun je het beste eerst een kopie van de database maken zoals hierboven beschreven. Op deze manier krijg je een consistente backup. Daarna ga je naar de Azure Portal en selecteer je de gekopieerde database waarvan je de BACPAC maakt. Vervolgens klik je op de "Export" knop onderin het beeld. Dat tovert het schem in afbeelding 2 tevoorschijn.
Afbeelding 2. Het export scherm om de BACPAC te genereren.
Op dit scherm specificeer je eerst de naam van de BACPAC. Standaard geeft Windows Azure jou al een suggestie. Vervolgens moet je de naam van een blob storage specificeren. Dit is de storage waarin de BACPAC wordt geplaatst. Eventueel kun je een nieuwe container (een logische verzamelnaam voor gelijksoortige blobs) specificeren. Tenslotte de credentials opgeven en je bent klaar om te gaan. Wanneer de BACPAC naar de blob storage is gekopieerd, kun je hem met een Azure Storage Browser, zoals CloudBerry, CloudXplorer, Cloud Storage Studio of gewoon vanuit Visual Studio 2012, downloaden. Je kunt hem natuurlijk ook in de blob storage laten staan om, indien nodig, meteen te restoren want dat zul je toch vanuit de storage moeten doen.

De laatste methode om backups te maken gaat via SQL Database Migration Wizard, of liever gezegd met de tools SqlAzureMWBatchBackup en SqlAzureMWBatchUpload. Dit zijn twee command-line tooltjes die je dus ook in een batch programma kunt laten draaien. De commando's zijn vrij eenvoudig: 

SQLAzureMWBatchBackup -S SERVER.database.windows.net -U "username" 
                                       -P "password" -D Database 
[-S target server name] 
[-U username] 
[-P password] 
[-D source database name] 
[-o output process results file] 
[-O output directory for all files] 
[-T trusted connection] 

Het mooie is dat alles ook in de configuratiefile kan worden opgenomen waardoor het starten nog eenvoudiger wordt. Om de database weer te herstellen gebruik je SqlAzureMWBatchUpload.exe. Het commando ziet er als volgt uit:

SQLAzureMWBatchUpload -S SERVER.database.windows.net -U "username" 
                                      -P password -D Northwind -d -s 1 
[-S target server name] 
[-U username] 
[-P password] 
[-D target database name] 
[-i TSQL input file] 
[-o output file] 
[-e database edition (web or business)] 
[-s database size (1, 5, 10, 20, 30, 40 or 50)] 
[-T trusted connection] 
[-d drop existing database]

Ook deze schakelopties kunnen in een configuratiebestand worden opgenomen.

Ofschoon een backup maken vanuit Windows Azure SQL Database (nog) niet mogelijk is, zijn er tal van alternatieven. Een kopie maken van de database is een veilige methode die ook wijzigingen tijdens het backup proces nog meeneemt. Nadeel is dat je hiervoor wel een extra database in de cloud krijgt en dat kost geld. Wel kun je van zo'n kopie een BACPAC maken die je vervolgens downloadt. Het vergt wel wat gepeuter om die BACPAC van uit de storage te krijgen. SqlAzureMW blijkt wederom een veelzijdig tooltje te zijn dat ook backups kan maken. Het uitvoeren vanaf de command-line is een groot voordeel waardoor de backupscommando's in een script kunnen worden ondergebracht om vervolgens, via de scheduler, op gezette tijden te worden uitgevoerd.

Update: Eindelijk is een automatische exportfunctionaliteit aan Windows Azure toegevoegd: http://blog.pasit.nl/2013/08/windows-azure-sql-database-backup.html.

dinsdag 9 april 2013

Entity Framework met een Sharded Database

Wanneer we met enorme databases werken, lopen we onherroepelijk tegen problemen aan. Deze problemen zijn meestal hardware gerelateerd. Nu is dat niks nieuws en er bestaan heel wat oplossingen met allemaal hun voor- en nadelen. Tegenwoordig is het sharden van de database een populaire benadering. Dit houdt in dat je grote tabellen in stukken hakt (horizontale partitionering, dwz op rij gesplitst) en die stukken vervolgens op verschillende databaseservers plaatst. Dit brengt natuurlijk nieuwe problemen met zich mee (of uitdagingen, zoals managers dat noemen). Denk bijvoorbeeld aan een join over twee tabellen en die tabellen staan nu net in verschillende database-instanties. Die uitdaging los je dan weer op door ervoor te zorgen dat gerelateerde data binnen dezelfde database-instantie komen te staan. Om de data te kunnen splitsen, moet je een bepaald criterium bedenken waarop je wilt splitsen. Je kunt denken aan beginletters, bijvoorbeeld namen die met een "A" beginnen in shard A en namen die met een "B" beginnen in shard B enz. In ieder geval moet je een veld uit de te sharden tabel kiezen op basis waarvan gesplits kan worden en een criterium, dat bepaalt naar welke shard de data weggeschreven moet worden of uitgelezen. Zo'n veldkeuze en een criterium slaat men dan op in een shard key. Een goede shard key definiëren is niet eenvoudig en moet zorgvuldig overwogen worden.

In Windows Azure SQL Database is er al een voorziening voor sharding aanwezig. Ze noemen het daar alleen federation. Een federation in Windows Azure SQL Database bestaat uit een root federation en een aantal federation members. Die root federation is het centrale aanspreekpunt van de federation en bevat informatie over de federation key (shard key, distributed key), welke federation members (shards) er zijn en wat de status van die members is. De federation key beschrijft hoe de database gesplitst moet worden over de members. Daarvoor is een unieke identiteit nodig die in de te splitsen tabel als veld opgenomen moet zijn. Vaak zul je die identiteit als primary key en/of foreign key inzetten. Hij moet in ieder geval uniek zijn over alle federation members. De keuze voor de federation key is geen eenvoudige. Je zult goed moeten onderzoeken welke tabellen groot gaan worden en hoe je gerelateerde data wilt ophalen (joins over members is niet mogelijk). Uiteindelijk geef je bij de creatie van de tabel op of die gespitst moet worden met het statement FEDERATED ON (FederationKey = TabelKolom). Alle tabellen die dit statement niet hebben, worden als referentietabel aangemerkt en zullen met een splitsing in zijn geheel worden meegenomen op alle members. Dat maakt joins over die tabellen in ieder geval eenvoudiger, maar de keerzijde is dat die referentietabellen niet gerepliceerd worden, dus als je op één member een nieuw record aanmaakt, zal die  niet automatisch op de andere members meekomen. En dat zuigt behoorlijk.

Over hoe je zo'n federation op zet is al een blog geschreven. In dit artikel gaan we bekijken hoe je met Entity Framework (6 alpha release 3) tegen zo'n federation praat. In dit artikel gebruik ik een federation (Product) met "demo" als root federation en daaronder vier federation members (afbeelding 1).


Afbeelding 1. Vier federation member met ranges van 0 - 99999, 100000 - 199999, 200000 - 299999 en 300000 - 399999

Als datamodel heb ik drie tabellen (Brand, Product en ProductGroup) en een link tabel (ProductGroupProduct). Om die in Entity Framework te laden, moet je naar een specifiek federation member gaan (afbeelding 2)


Afbeelding 2. Het connectievenster met servernaam en een lijst van databases. Demo is de federation root en die vier andere "system" met die enorme vloek erachter zijn de federation members.
Als je het goed hebt gedaan, zie je de bekende Entity Data Model Wizard (afbeelding 3) van waaruit je selecteert welke tabellen je in jouw entity model wilt hebben.


Afbeelding 3. Entity Data Model Wizard.
In dit voorbeeld kies ik alle tabellen hetgeen leidt tot afbeelding 4.


Afbeelding 4. Het resultaat van het betere klikwerk.
Nu moet je niet vergeten de connection string aan te passen, zodat die verwijst naar de root federation. In dit voorbeeld zet ik dus de Initial Catalog naar "demo". Verder haal het stuk "MultipleActiveResultSets=True" eruit. Federation ondersteunt geen MARS (de mogelijkheid om meerdere datareaders open te hebben). Wanneer we data uit de database willen trekken, zullen we eerst moeten specificeren in welke federation member die data staat. Dit doe je met een speciaal sql-statement:


USE FEDERATION [Product] ([ProductKey] = val) WITH FILTERING = OFF, RESET
GO


Waarbij Product de naam van de federation is, ProductKey de federation key en val een specifieke waarde in de range van een member (als de waarde 153789 is, zal de tweede federation member worden aangesproken omdat die waarde in zijn range valt. Zie afbeelding 1). Voordat we een query kunnen afvuren, zullen we dus eerst dit statement moeten uitvoeren. Wellicht is het dan handig om die key waarde mee te geven als argument aan de constructor van de object context (gegenereerde class in Entity Framework, die in dit artikel "Entities" heet). Hiervoor maken we een partial class Entities aan.

public partial class Entities
{
    public void ChangeFederation(int keyValue, bool filtering = false)
    {
        DbConnection con = this.Database.Connection;
        if (con.State != System.Data.ConnectionState.Open)
        {
            con.Open();
        }
        DbCommand com = con.CreateCommand();
        com.CommandText = string.Format("USE FEDERATION Product(ProductKey = {0}) WITH RESET, FILTERING = {1}", keyValue, filtering ? "ON" : "OFF");
        com.ExecuteNonQuery();
    }

    public Entities(int keyValue) : this()
    {
        ChangeFederation(keyValue);
    }
}

De methode ChangeFederation zal op basis van een gegeven key het Federation statement uitvoeren. Deze methode wordt in de constructor aangeroepen. Hieronder zie je hoe je met deze aangepaste context omgaat. Het resultaat is in afbeelding 5 weergegeven.



static void Main(string[] args)
{
    Entities context = new Entities(0);
    var query = context.Products.Include(p => p.Brand).Take(10);
    foreach (Product product in query)
    {
        Console.WriteLine("{0, -8} {1, -10} {2}", product.ID, product.Brand.Name, product.Name);
    }
    Console.WriteLine("-------------------------------------");

    context = new Entities(100000);
    query = context.Products.Include(p => p.Brand).Take(10);
    foreach (Product product in query)
    {
        Console.WriteLine("{0, -8} {1, -10} {2}", product.ID, product.Brand.Name, product.Name);
    }
    Console.WriteLine("-------------------------------------");

    context = new Entities(200000);
    query = context.Products.Include(p => p.Brand).Take(10);
    foreach (Product product in query)
    {
        Console.WriteLine("{0, -8} {1, -10} {2}", product.ID, product.Brand.Name, product.Name);
    }
    Console.WriteLine("-------------------------------------");

    context = new Entities(300000);
    query = context.Products.Include(p => p.Brand).Take(10);
    foreach (Product product in query)
    {
        Console.WriteLine("{0, -8} {1, -10} {2}", product.ID, product.Brand.Name, product.Name);
    }
    Console.WriteLine("-------------------------------------");

    Console.ReadLine();
}

Afbeelding 5. Het resultaat van de listing hierboven. De getallen in de eerste kolom zijn de waarden van de federation key.
Wanneer je data wilt toevoegen, zul je eerst een nieuwe unieke federation key moeten genereren (identity kolommen zijn in een federation niet bruikbaar). Hiervoor zul je een strategie moeten bedenken hoe dat aan te pakken. Je zou kunnen denken aan een tabel in de federation root, waarin je het laatst uitgegeven ID bijhoudt. Dat zou je ook in de blob storage kunnen doen. Er zijn modules op de markt die dit werk voor jou kunnen doen, zoals SnowMaker of RustFlakes.
Eenmaal een nieuw ID gegenereerd, werkt het eigenlijk op dezelfde manier zoals dat je in Entity Framework gewend bent.

private static void InsertProduct()
{
    int newID = 350000;

    Brand acme = new Brand
    {
        ID = 8000,
        Name = "ACME",
        WebSite = "www.acme.com"
    };

    Product p = new Product
    {
        ID = newID,
        Name = "Explosive device",
        FamilyID = newID,
        IsAvailable = true,
        CreationDate = DateTime.Now
    };

    p.Brand = acme;

    Entities context = new Entities(newID);
    context.Products.Add(p);
    try
    {
        context.SaveChanges();
    }
    catch (Exception e)
    {
        Console.WriteLine(e.Message);
    }
}

Let wel op dat "Brand" een referentietabel is die op alle federation members in zijn geheel aanwezig is. Dat nieuwe merk staat echter alleen op de laatste federation member omdat die daarop is aangemaakt. Hij wordt niet gerepliceerd. Dat zul je zelf moeten doen.

Stel dat ik nu een specifiek product wil zoeken. Hoe pak je dat aan? Er zit dan niets anders op dan in alle federations te grasduinen. Helaas weet je met schaalbare oplossingen nooit precies hoeveel members er zijn. Gelukkig is die informatie wel in de root database aanwezig. Die informatie kun je eruit trekken met het volgende sql-statement:

SELECT * FROM sys.federation_member_distributions

Het levert het  resultaat op in afbeelding 6


Afbeelding 6. Het resultaat van de query over sys.federation_member_distributions
Gewapend met deze informatie kunnen we de partial Entities uitbreiden met de volgende methode:

public List<int> GetFederationMembers()
{
    List<int> lowerRanges = new List<int>();
    DbConnection con = this.Database.Connection;
    if (con.State != System.Data.ConnectionState.Open)
    {
        con.Open();
    }
    DbCommand com = con.CreateCommand();
    com.CommandText = "select range_low from sys.federation_member_distributions";
    DbDataReader reader = com.ExecuteReader();
    while (reader.Read())
    {
        lowerRanges.Add(reader.GetInt32(0));
    }
    reader.Close();
    return lowerRanges;
}

Nu we de minimumwaarden van de federation members hebben, kunnen we door de members itereren en op iedere member de zoekfunctie uitvoeren (bij voorkeur parallel)


public static List<Product> FindGlobalProduct(Expression<Func<Productbool>> filter)
{
    Entities context = new Entities();
    List<int> members = context.GetFederationMembers();
    ConcurrentBag<Product> results = new ConcurrentBag<Product>();
    Parallel.ForEach(members, member =>
    {
        Entities ctx = new Entities(member);
        foreach (Product p in ctx.Products.Include(p => p.Brand).Where(filter))
        {
            results.Add(p);
        }
    });

    return results.ToList();
}

De aanroep van deze functie gaat er dan als volgt uitzien:


private static void TestFindGlobalProduct()
{
    List<Product> prods = FindGlobalProduct(p => p.Name.StartsWith("EOS"));
    foreach (Product p in prods)
    {
        Console.WriteLine("{0, -10} {1}", p.Brand.Name, p.Name);
    }
}

Het resultaat van dit alles is in afbeelding 7 te bewonderen.


Afbeelding 7. Het resultaat van een zoekactie over alle federation members.
Zoals inmiddels duidelijk moge zijn, is het werken met sharded databases geen sinecure. Je zult in de eerste plaats goed moeten opletten welke tabellen je wilt splitsen en hoe je dat gaat doen, welke referentietabellen neem je mee en hoe ga je die tabellen synchroniseren? Wanneer je jouw database eenmaal goed hebt opgezet, heb je wel een hele schaalbare oplossing waarbij de grootte van de tabellen in ieder geval geen bottleneck meer zullen vormen.
Ook Entity Framework is goed te gebruiken op sharded databases, maar je moet wel wat voorzieningen inbouwen om het benaderen van de shards te vereenvoudigen. Helaas vervalt bij sharded databases wel een van de basisprincipes van Entity Framework: Een software-ontwikkelaar moet zich niet bezighouden met database structuren. Dat is voor database beheerders. Bij sharded databases is die kennis voor een ontwikkelaar weer onontbeerlijk.

dinsdag 2 april 2013

Sharding Windows Azure SQL Database

Windows Azure SQL Database is een database in de cloud. Het is een goedkoop alternatief voor een peperdure on-premise SqlServer database. De licenties voor alleen SqlServer kunnen kostbaar zijn en daarbij komen dan ook nog eens de hostingkosten en het onderhoud daaraan. Vaak kun je bij je hostingprovider zo'n SqlServer huren (voor een web editie kan dat al gauw 30 euro in de maand zijn, maar dat hangt natuurlijk van de provider af). Voor Windows Azure Sql Database betaal je ongeveer 20 euro in de maand voor 5GB en 170 euro voor 150GB. Dat is dan ook meteen het maximum. Momenteel kan Windows Azure Sql Database maar 150GB groot zijn. Voor veel bedrijven vormt die beperking een belemmering om over te gaan naar Sql Azure. Ik vrees dat die limiet niet eens het grootste probleem zal vormen. In tegenstelling tot cloud services, waar je in drukke periodes in no-time een paar instanties erbij zet, laat Sql Azure zich niet zo eenvoudig opschalen. Jouw Windows Azure Sql Database draait op een machine met 8 Cpu's, 32GB RAM en 12 harddisks en daar zul je het mee moeten doen. Klinkt ruim voldoende, maar die machine moet je wel delen met andere gebruikers (ik geloof vier). Om ervoor te zorgen dat een gebruiker niet alle resources opeist, heeft men er een Engine Throttling ingehangen. Wanneer jij te veel resources gebruikt, word je gewoon afgeknepen. Dit kan zich beperken tot het blokkeren van inserts of updates tot een complete blokkade van alle connecties. Hiervoor krijg je dan een error code (40501) terug met een specifieke reden, waarna je het nogmaals mag proberen. Je kunt je dus afvragen of die 150GB ooit haalbaar is.

Uit dit verhaal zou je kunnen concluderen dat Windows Azure Sql Database voor grote databases knap waardeloos is, maar deze limieten heb je bij traditionele on-premise databases ook. Uiteindelijk zit je aan de grenzen van de hardware. Ook hier zou je een flexibele schaling willen, maar voor databases is dat niet zo eenvoudig. 
Een techniek die tegenwoordig veel gebruikt wordt is Database Sharding. Bij Sharding wordt een enorme tabel in stukken geknipt. Dit knippen gebeurt horizontaal, dus op basis van rijen. Denk bijvoorbeeld aan een wereldwijde klantentabel met miljoenen (linkedin, facebook) records. Je kunt zo'n tabel in stukken hakken door klanten per werelddeel te groeperen in hun eigen tabel. Je krijgt dus een tabel KlantenEuropa, KlantenAzie enz. Dit is overigens geen sharding. Dit is gewoon horizontale partitionering (afbeelding 1), een techniek die al een tijdje bestaat waarbij je de indexgrootte reduceert en dus sneller kunt zoeken. Sharding gaat een stap verder en plaatst die partities op verschillende databaseservers (afbeelding 2). Er komen ook geen tabellen zoals KlantenEurope, maar gewoon Klanten. Binnen hun serverinstantie zijn ze tenslotte tóch uniek. Met deze benadering heb je de voordelen van de traditionele horizontale partitionering plus de extra resources omdat zo'n partitie op een eigen machine kan draaien. Zo'n database-instantie, met zijn eigen tabelfragment, noemt men een shard.


Afbeelding 1. Traditionele horizontale partitionering op basis van leeftijd. De User tabel is in stukken geknipt, maar leeft wel binnen dezelfde database-instantie (bron www.cubrid.org).

Afbeelding 2. Dezelfde horizontale partitionering, maar nu zijn de partities op verschillende database-instanties geplaatst. Dit is Sharding (bron www.cubrid.org).
Dit klinkt allemaal heel mooi en aardig, maar in de praktijk loop je met dit soort grappen tegen een aantal beperkingen aan zoals:
  1. In welke database staat de data die jij zoekt?
  2. De ene shard wordt enorm (klanten uit Azië) terwijl een andere shard klein blijft (klanten uit Australië)
  3. Hoe ga je overweg met joins?
  4. Hoe zit het met gegenereerde primary keys?
Een aantal van deze problemen kan ondervangen worden door gebruik te maken van een shard key. Zo'n key beschrijft eigenlijk de kolom waarop je wilt sharden, bijvoorbeeld de kolom werelddeel in de klantentabel, maar het zou ook de leeftijd kunnen zijn, een datum of een getal. Wat je ook kiest, je zult een criterium moeten bepalen op basis waarvan de sharding plaatsvindt. Je kunt hierbij denken aan de volgende schema's:
  1. Range. Denk hierbij een leeftijd (personen tussen 10 en 20 jaar) of datum (geboren tussen van 1980 tot 1990).
  2. Key. Hierbij kun je denken aan een bepaalde wiskundige operatie. Stel je hebt als shard key een integer getal gekozen. Je kunt nu sharden op basis van even of oneven getallen. Beter is het om modulo te gebruiken (met een modulo bepaal je de restwaarde van een deling. 6 % 5 = 1, 7 % 5 = 2,  8 % 5 = 3, 10 % 5 = 0, 11 % 5 = 1, enz). Op basis van die restwaarde weet je naar welke database je moet schrijven. In dit rekenvoorbeeld zou je dus vijf shards krijgen.
  3. Houdt een lookup tabel bij van je shard keys waarin je aangeeft in welke shard je de data moet zoeken.
Het joinen van tabellen is bij sharding lastig omdat deze joins over databases gaan. Je zou dat probleem kunnen ondervangen door gerelateerde data in dezelfde shard te plaatsen (een klant met zijn orders in dezelfde shard). Helaas zul je niet om handmatig bijlezen heen komen, immers de producten die bij een order horen kun je niet sharden. Voordeel is wel dat je die lookup query parallel kunt uitvoeren.
Het wordt ook vervelend bij sql-funties zoals AVG() (gemiddelde). Hierbij zul je het gemiddelde van iedere shard afzondelijk moeten bepalen en de resultaten zien te combineren om tot het eindresultaat te komen.
Het valt allemaal niet mee, maar als je het goed inricht en de juiste shard key kiest, heb je wel de ultieme schaalbare oplossing.

In Windows Azure SQL Database zit reeds een goede voorziening voor sharding, alleen gebruiken ze daar de naam federation. Een shard wordt een federation member en de shard key wordt een federation key. Sql Azure gebruikt een aparte database die bijhoudt welke federations er leven. Deze database noemt men de federation root.
Om een federated database op te zetten, begin je met het aanmaken van een gewone database in de Azure Portal (Zie eerdere blog). Wanneer je inlogt op de database en naar "Administration" gaat, kun je een nieuwe federation aanmaken (afbeelding 3). In het volgende venster (afbeelding 4) moet je de federation key specificeren. Je begint met een naam, vervolgens bepaal je wat voor een type de federation key is. Tenslotte moet je het sharding criterium opgeven. Momenteel weinig keus, we hebben alleen RANGE.

Afbeelding 3. Een nieuwe federation aanmaken

Afbeelding 4. De federation key definiëren
In Windows Azure SQL Database wordt nu de database met de federation root (fedtest) aangemaakt en een federation member. In de Azure portal kunnen we die federation ook zien (afbeelding 5)


Afbeelding 5. De federation root (fedtest) en een federation member (system-bd1c... de onderste).
Wanneer we vanuit de database portal naar de federation root gaan, krijgen we een overzicht van alle federation members (shards) die op dat moment aanwezig zijn (afbeelding 6). Hier heb je ook een overzicht van het verbruik van de diverse federation members. Daarnaast heb je ook toegang tot de diverse federation members en kun je, indien nodig, de database opschalen.


Afbeelding 6. Het overzicht van de federation members.
Allereerst gaan we maar eens de database aanmaken. Dat doe je door via het context menu van een federation member, de optie New Query te kiezen. Het leidt tot het venster in afbeelding 7. Bovenin zie je alvast een belangrijk (gegenereerd) statement staan:


USE FEDERATION (fed_brand = -1) WITH FILTERING = OFF, RESET
GO


Dit statement zorgt ervoor dat de queries nu naar de eerste (en vooralsnog enige) federation member gaan. (fed_brand = -1) geeft aan dat we de federation member met RANGE minimum -1 willen aanspreken, FILTERING = OFF geeft aan dat we alle federation keys willen aanspreken (Met waarde ON krijg je alleen het record, behorende bij de bij fed_brand gespecificeerde waarde, -1 in dit geval). RESET om de connectie te resetten.


Afbeelding 7. Een query die naar de eerste federation member gestuurd wordt.
Vervolgens komen er een aantal standaard DDL-statements die eindigen met een speciaal commando:

FEDERATED ON (BrandID = [ID])

De betekenis van dit statement is tweeledig: het geeft aan dat de data gesplitst kan worden en welk veld als federation key moet worden gebruikt (ID in dit geval). Bij de creatie van de producttabel zie je nog zo'n statement, maar nu op de foreign key van de producttabel. Ook deze tabel wordt nu in de splitsing meegenomen. Het zal ertoe leiden dat alle merken met hun bijbehorende producten in dezelfde federation member komen te staan (herinner je dat een join over shards niet mogelijk is).
Wanneer de tabel goed gevuld is, kun je hem gaan splitsten (afbeelding 8). Bij "Split" geef je aan vanaf welke waarde er gesplitst moet worden.


Afbeelding 8. Splitsten vanaf ID=4000
Uiteindelijk heb ik nu drie federation members aangemaakt zoals te zien is in afbeelding 9.


Afbeelding 9. De federation root (fedtest) met drie federation members.
We kunnen nu queries afvuren op de diverse federation members. Wel zullen we altijd eerst moeten aangeven op welke member de query uitgevoerd moet worden. Dit doen we via het statement USE FEDERATION ... Dus om een merk met een ID groter of gelijk aan 4000 te kunnen opvragen, specificeren we eerst dat we de federation member willen hebben waar 4000 in de range valt (mag overigens een willekeurig getal in die range zijn, dus 5387 was ook goed).

USE FEDERATION (fed_brand = 4000) WITH FILTERING = OFF, RESET
GO
SELECT * FROM Core.Brand

Deze query zal alle merken vanaf ID = 4000 terug geven. Als je FILTERING=ON zou specificeren, zou je alleen het merk met ID=4000 krijgen.

Sharding is een interessante techniek om schalen van databases mogelijk te maken. Windows Azure SQL Database heeft met federations een fraai initiatief gelanceerd, dat zeker perspectief biedt. Laat onverlet dat sharding een lastige techniek is om te gebruiken. Het vereist een goede keuze voor de sharding key. (Mijn keuze voor de primary key van Brand bleek niet zo'n juiste te zijn. Omdat de grote merken al in het begin van de tabel zaten, leverde dat ongebalanceerde shards op. Te veel producten in de eerste shard).
De volgende keer bespreek ik hoe je met Entity Framework de data uit een federated database trekt.