Azure SQL Databases – vad, hur och varför

By | november 4, 2014

Denna bloggpost förklarar först vad Azure SQL databases är och beskriver sedan ett antal tips och saker att tänka på när man använder Azure SQL databases. Om du redan känner till Azure SQL databases så kan du hoppa över början.

Det finns många fördelar med att lagra sin data i SQL databaser (naturligtvis inklusive även andra databaser än Microsoft SQL Server). Några av de viktigaste fördelarna är

  • SQL och relationsdatabaser är en standardiserad och mogen teknik. Det är relativt enkelt att integrera olika lösningar med varandra. Det finns också ett stort ”ekosystem” av bloggare, forum, utbildningar och best practices inom området.
  • De är byggda för att hantera många användare samtidigt, genom exempelvis transaktionsisolering. Det är mycket viktigt i exempelvis en webshop eller banksystem, men också i väldigt många andra system.
  • Datakvaliteten kan garanteras även om applikationer kraschar mitt i en uppdatering. Databaserna hanterar detta på ett snyggt sätt, genom referensintegritet och stöd för felhantering, så att man exempelvis inte får kundordrar som inte är kopplade till någon kund.
  • Man kan koppla ihop flera databasservrar (finns flera lösningar i SQL Server såsom exempelvis AlwaysOn Availability Groups) för att höja tillgängligheten och kapaciteten. Exempelvis om man skall göra underhåll på en databasserver så kan man låta en annan databasserver ta över under tiden.
  • Det finns mängder med säkerhetsfunktioner i databaser. Allt från kryptering till behörighetsstyrning och loggning.

Många av dessa fördelar är saker som de flesta systemutvecklare inte ens känner till och än mindre vet hur man använder. Det har mångt och mycket blivit en uppdelning i systemutvecklare som är duktiga på exempelvis C# och databasadministratörer som är duktiga på hur man konfigurerar och sköter databaser. Lite förenklat skulle man kunna beskriva det så här.

SQL database as a service

  • Systemutvecklarna använder en begränsad mängd funktioner i SQL databaserna och vill slippa behöva bry sig för mycket om installationer och sådant.
  • Databasadministratörerna sköter infrastrukturen, det vill säga installation, uppdateringar av operativsystem och databaser, säkerhet, backuper, etc. I större företag är det ofta flera heltidstjänster att sköta om alla databaser.

What is Microsoft Azure SQL Databases?

Beroende på vad man vill ägna sig åt så har man två olika val: Virtuell Maskin eller Azure SQL Database. Skillnaden är att med Azure SQL Databases så sköter Microsoft installationer, säkerhetspatchar, backuper, failover-hantering, etc. Man köper en ”databas som tjänst” istället för att hyra en server.

Azure VM vs Azure SQL database
(Klicka på bilden för en förstoring)

Virtuella maskiner med SQL Server

Microsoft erbjuder ett antal färdiga virtuella maskiner med SQL Server. Fördelen är att man kan väldigt snabbt vara igång med en databasserver i vilken världsdel man vill och med den kapacitet man önskar. Man betalar bara för den tid man ”hyr” servern.

Azure SQL virtual machine gallery

Prislistor för denna ”hyrtjänst” finns här. Obs att man betalar inga startavgifter och det är ingen uppsägningstid. Det går också att prova gratis i begränsad omfattning.

Det finns naturligtvis många andra leverantörer än Microsoft som också erbjuder virtuella maskiner som man kan köra SQL Server på. Oftast styrs valet av geografiska faktorer. Man vill att databasservern skall vara placerad så nära applikationsservrarna som möjligt (helt i samma datorhall). Om man bygger sina applikationer i Azure så har man därför oftast fördel att även köra den virtuella maskinen med databasen i Azure.

Det finns några ytterligare användningsområden för en SQL Server i molnet. Man kan använda AlwaysOn för att koppla ihop sina lokala SQL Servrar med sekundärnoder i molnet (d v s virtuella maskiner som kör SQL Server). I klartext innebär det att:

  • Om du har användare som sitter geografiskt utspridda så kan de läsa data från närmaste SQL Server. Skriva data måste däremot alltid göras till primärnoden (som bara kan finnas på ett ställe).
  • Om din primärnod (d v s din lokala SQL Server) kraschar så kan någon av sekundärnoderna i molnet ta över tills du fått ordning på allt igen. Användarna kan jobba vidare under tiden eftersom både läsningar och skrivningar fungerar.
  • Om du vet att du kommer att ha väldigt hög belastning vid en viss tid (kanske biljettsläpp till en populär konsert) kan du hyra ett antal sekundärnoder som hjälper till. Observera dock att sekundärnoderna endast går att läsa ifrån.
  • Du kanske har så hög belastning att det är svårt att ta backuper. Då kan du ta backuperna på en sekundärnod i molnet istället. Lagringen av backupen sker i så fall i molnet, vilket också kanske kan vara en fördel.

Azure SQL Databases

Microsofts databas-som-en-tjänst har bytt namn många gånger. Från allra första början hette det SQL Server Data Services. Därefter har det varit många namnbyten: SQL Azure, Windows Azure SQL databases och nu senast Microsoft Azure SQL databases. Jag har nu valt att kort och gott kalla detta för Azure SQL databases.

Om man har möjlighet att använda sig av Azure SQL Databases istället för en virtuell maskin så tycker jag absolut att man skall välja det alternativet.

  • Pris/prestanda är mycket bättre. Minimikostnaden är också mycket längre (bara cirka 5 USD per månad per databas).
  • Det är mycket mindre underhållsjobb. Microsoft sköter allt underhåll och backuper tas var 5:e minut. Man kör alltid på senaste versionen.
  • Man får garanterat hög tillgänglighet, minst 99,99% upptid.

Saknad funktionalitet i Azure SQL databases

Tyvärr finns inte all funktionalitet ifrån ”vanliga SQL Server” tillgänglig i Azure SQL Databases. Här är en lista över sådan funktionalitet som inte finns. Det jag själv saknar mest är SQL CLR och stöd för att köra SQL Agent jobb. Användargränssnittet är också långt ifrån färdigutvecklat, vilket gör att man måste lära sig skriva kommandon istället för att klicka sig omkring i ett grafiskt gränssnitt som i vanliga SQL Server. För en erfaren databasadministratör är det inget större problem, men för systemutvecklare som är ovana vid SQL Server är det nog mer irriterande.

Fulltext Search saknas. Själv har jag dock inte varit med om något fall där jag inte har kunnat ersätta det med en lösning i traditionell SQL, som dessutom har bättre prestanda än Fulltext Search. Det är ingen funktion jag själv saknar alltså, men oavsett så tror jag att i framtiden kommer man att få den i Azure SQL databases.

Skapa Azure SQL databases

För att komma igång med Azure SQL databases behöver man först logga in i Azure portalen och skapa en SQL database server.

sql_azure_new_server

Därefter kan du skapa en eller flera databaser genom portalen.

sql_azure_new_database

Databaserna finns i tre olika nivåer:

Nivå Prestanda Max storlek Pris
Basic  5 DTUs 2 GB 5 USD/månad
Standard  10 – 50 DTUs 250 GB 15 – 75 USD/månad
Premium  100 – 800 DTUs 500 GB 465 – 3720 USD/månad

Ytterligare ett sätt att växa om dessa nivåer inte skulle räcka till är att använda Federations, där man sprider ut sin data i många databaser (så kallad ”sharding”).

Om man vill kan man naturligtvis skapa databaser programmatiskt med CREATE DATABASE-kommandot istället för att gå via webgränssnittet. Då finns också några extra options som man inte når via webbgränssnittet.

Sätta upp firewall för Azure SQL databases

Innan du kan ansluta externt till dina databaser måste du sätta upp firewallreglerna. Detta konfigurerar du per server. I Azure-portalen finns det en flik för detta.

azure-db-firewall

Du bör åtminstone lägga till IP-adressen för den dator du sitter vid. Dessutom kan du välja att ge andra Windows Azure Services tillgång. Det finns inget sätt att begränsa till vissa Azure Services, men antalet hackers som försöker den vägen är förmodligen mycket färre än om databasen skulle vara åtkomlig från vilket IP-adress som helst på internet.

När du ändrat, glöm inte att klicka på spara längst ned på sidan. Det tar sedan någon minut eller så tills reglerna slår igenom.

Bygga Azure SQL databases

Management Studio har traditionellt använts för att bygga/utveckla SQL Server databaser. Det är dock ett verktyg som är mest inriktat på databasadministration. Istället rekommenderar jag att man går över till Visual Studio för databasutveckling. Det finns en plugin till Visual Studio just för detta ändamål som heter Microsoft SQL Server Data Tools. Med Data Tools kan du skapa projekt av typen ”SQL Server Database Project”.

data-tools-new-project

I Data Tools kan du välja Azure SQL Database som målmiljö i projektet (högerklicka på projektet och välj Properties).

data-tools-project-settings

Du får också en SQL Server Object Explorer där du kan ansluta till dina Azure SQL databases.

data-tools-sql-server-object-explorer

Adressen till dina Azure SQL databases är servernamn.database.windows.net. Adressen kan du även se i välkomstbilden för din databas i Azure. Observera att du inte kommer att kunna ansluta till dina Azure SQL databases om du inte skapat firewallregler (se rubriken Sätta upp firewall för Azure SQL databases).

azure-db-quickstart

I Data Tools kan du sedan bygga din databas objekt för objekt eller importera en befintlig databas. Fördelarna jämfört med Management Studio är.

  • Du kan använda ett Source Control-verktyg för att versionshantera databaserna.
  • Du kan jobba offline om du vill (när du inte har uppkoppling till din databas). Du har till och med IntelliSense medan du jobbar offline.
  • Du kan jämföra och synkronisera lokal miljö med produktionsmiljö eller testmiljö. Det finns både Schema Compare och Data Comparison.
  • Du kan väldigt enkelt importera och exportera DACPACs, som är en ”källkod” för din databas.
  • Du sparar mycket tid genom Refactoring-funktioner som exempelvis hjälper dig ändra överallt om du skulle byta namn på ett objekt.

När du skall publicera dina förändringar till produktionsmiljö så rekommenderar jag att använda Schema Compare och skapa ett change script. En viktig inställning är ”Block on possible data loss”. Om du har den ikryssad så får du ett skydd mot att oavsiktligen radera data i produktionsmiljön. Å andra sidan så måste du då gå in och redigera i dina change scripts varje gång för att tillåta vissa förändringar av tabeller med befintlig data. Missa inte att gå in på dessa inställningar för Schema Compare och gör de val som passar dig.

data-tools-schema-compare-options

Du kan också direkt ifrån Visual Studio / Data Tools öppna ett Query-fönster till dina Azure SQL databases. Det ger dig ungefär samma möjligheter som i Management Studio att köra T-SQL satser direkt.

data-tools-new-query

Point-in-time restores

Eftersom Microsoft automatiskt tar backup på alla dina databaser (både full backup, differentiell backup och transaktionslogg) så kan du ”åka tidsmaskin” och få en kopia på databasen så som den såg ut vid valfritt tidigare tillfälle (under förutsättning att Microsoft fortfarande har kvar backupen, vilket i sin tur beror på om du kör Basic, Standard eller Premium).

azure-db-point-in-time-restore

Du kan fylla i vilket klockslag som helst (på minuten) så länge det inte är äldre än ”earliest restore point”.

Denna funktion är utomordentligt värdefull eftersom du kan komma åt data som tidigare har skrivits över eller raderats från databasen. Skulle du exempelvis misslyckas med en produktionssättning så kan du backa tillbaka till hur det såg ut innan produktionssättningen.

En trevlig sak med point-in-time restore är att den återställda databasen får ett nytt namn, så att den kan samexistera med befintliga. Det gör att du i en räddningsoperation skulle kunna göra restore för lite olika tidpunkter och kombinera dem.

Connection strings

I Azure webb portalen finns en länk för att visa connection strings till databaserna. Dessa är ju nödvändiga om man skall koppla sig via applikationer mot databaserna. Exempelvis ADO.NET connection string ser ut så här:

Server=tcp:goqurw8owa.database.windows.net,1433;Database=MinAzureDatabas;User ID=johan@goqurw8owa;Password={your_password_here};Trusted_Connection=False;Encrypt=True;Connection Timeout=30;

En vanlig fråga jag har fått är varför finns ingen connection string för OLE DB?

Svaret är att Microsoft har bestämt sig för att sluta stödja OLE DB. Meningen är att man skall gå över till ODBC istället. Dock kan man på egen risk (och utan officiellt stöd från Microsoft) köra OLE DB drivrutin med en liten förändring av connection stringen.

Provider=SQLNCLI11;Data Source=tcp:goqurw8owa.database.windows.net,1433;Database=MinAzureDatabas;User ID=johan@goqurw8owa;Password={your_password_here};

Observera att det är obligatoriskt att ha med User ID och Password i connection strings. Detta eftersom Azure SQL databases fortfarande endast stödjer SQL autentisering som metod.

Prestanda

Microsoft har publicerat prestandasiffror här. En mer oberoende undersökning av prestandan i Azure SQL databases finns här.

När man tittar på prestandasiffrorna för Azure SQL databases så skall man komma ihåg:

  • Tanken bakom Azure är att man skall skala ut (parallellisera) istället för att skala upp (satsa allt på en enda kraftfull server). Detta är ett designmönster som vi kommer märka av allt mer även utanför molnet eftersom vi har slagit i taket redan när det gäller t ex CPU:ers klockfrekvens.
  • Man kan få en mycket hög prestanda i Azure SQL databases om man designar för att skala ut och använder exempelvis Federations (”sharding”). Det kräver dock som sagt att man designar sina applikationer på rätt sätt. Om man bara tar en befintlig applikation och flyttar databasen till Azure så får man istället räkna med en klart sämre prestanda, även med premium-nivå.
  • Med Azure SQL databases får du en flexibilitet att höja prestandan när det behövs och sedan sänka den igen när aktiviteten är lägre. Har man exempelvis ett data warehouse kan man höja prestandan inför att den skall laddas från källsystem och sedan dra ned den för resten av dygnet.

Överföring av data från befintlig databas

Det finns många olika sätt att flytta data från en befintlig SQL Server till Azure SQL databases.

  • Att flytta en liten databas är enkelt (under förutsättning att man inte har använt någon funktionalitet som inte stöds i Azure). Man kan exempelvis använda SQL Database Migration Wizard.
  • Större databaser är mycket svårare att flytta. För det första så ”throttlar” (stänger) Azure körningar som tar alltför lång tid eller transaktioner som blir för stora. Man måste alltså dela upp överföringen i mindre batcher och flera transaktioner.
    • Man kan använda SQL Database Migration Wizard och manuellt redigera filen SQLAzureMW.exe.config. Där finns en inställning som heter ”ChunkSize”. Tyvärr räcker inte detta till för alltför stora databaser eftersom det inte finns någon retry-logik och överföringshastigheten inte är optimal.
    • En idé jag har för hur man hanterar riktigt stora migreringar, men inte testat, är att ta en backup på databasen direkt till Azure (glöm inte att aktivera komprimering). Om man inte har SQL Server 2014 så kan man ta backupen lokalt och sedan skicka den till en BLOB-store i Azure. Därefter skapar man en virtuell maskin med SQL Server i Azure som återställer denna backup. Till sist använder man sig av SSIS-paket för att skicka data från den virtuella maskinen till Azure SQL databasen.

Kopiera en databas inom Azure SQL databases

Ett annat vanligt önskemål är att man vill kopiera en databas, till exempel produktionsmiljö till testmiljö, inom Azure. I Azure SQL databases finns ett två sätt.

  • Man använder T-SQL kommandot CREATE DATABASE AS COPY som man enklast skriver antingen i Visual Studio eller Management Studio.
  • Man klickar på Copy i Azure webbportalen.

Alternativ till SQL Agent

Med vanliga SQL Server får man ett verktyg som heter SQL Agent och som är mycket användbart för att schemalägga jobb i sina databaser. Tyvärr finns inte någon SQL Agent i Azure SQL databases. Vad kan man då göra istället?

I framtiden kommer man förmodligen att kunna använda Azure Scheduler. Tyvärr stödjer den i nuläget inte att göra databasanrop.

Kryptering

Anslutningar till Azure SQL databases är alltid krypterade med SSL så det är ingenting man behöver specificera.

Annan transaction isolation level

Azure SQL databases använder en transaction isolation level som heter READ COMMITTED SNAPSHOT. Denna är inte samma som READ COMMITTED, som är default i vanliga SQL Server. I ovanliga fall kan detta ställa till med problem för applikationer. En beskrivning av skillnaden finns här.

Viktigt att ha retry-logik

Ett vanligt problem många har med Azure SQL databases är att de då och då får ”oförklarliga fel” när de kör sina SQL-frågor mot databaserna.

Orsaken är följande. Azure SQL databases är en delad resurs som används av väldigt många användare. Därför stängs connections av många olika skäl, exempelvis idle timeout. För en vanlig SQL Server databas händer detta i princip aldrig. Därför skriver inte många någon form av kontroll eller retry-logik i sin programkod. Man utgår från att det går att ansluta en gång och sedan använda samma anslutning hur länge som helst.

Lösningen är att man behöver skriva en kontroll och retry-logik i sina applikationer. Det är inte så svårt och det finns en beskrivning här under rubriken Connection Constraints hur man kan göra. Problemet kompliceras av Connection Pooling (återanvändning av databasanslutningar), vilket gör att man absolut behöver skriva kontroll och retry-logik.

Här är ett enkelt exempel hur man kan skapa en hjälpfunktion som ansluter och kontrollerar/gör ett extra försök om det behövs.

public static System.Data.SqlClient.SqlConnection AcquireConnection()
{
    // Skapa connection
    var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["NamnPåConnectionString"].ConnectionString;
    var conn = new System.Data.SqlClient.SqlConnection(connectionString);
    conn.Open();

    // Testa connection
    var cmd = new System.Data.SqlClient.SqlCommand("select @@version;", conn);
    try
    {
        cmd.ExecuteNonQuery();
        // Om det gick bra, returnera
        return conn;
    }
    catch (System.Data.SqlClient.SqlException)
    {
        // Om det gick fel, gör ett nytt försök efter 100 millisekunder
        System.Threading.Thread.Sleep(100);
        conn.Open();
        cmd.ExecuteNonQuery();

        // Returnera om det gick bra på andra försöket, annars har vi fått en ny exception
        return conn;
    }
}

Här finns ytterligare ett mer omfattande exempel.

Användarhantering och behörigheter

Säkerhet och behörighet i Azure SQL databases fungerar på liknande sätt som i vanliga SQL Server. Här finns några riktlinjer. En jämförelse finns här.

Det finns inget grafiskt användargränssnitt för att administrera användare. Istället måste man skriva SQL-kommandon i Management Studio, Visual Studio eller annat liknande verktyg.

Logins skapas med

-- Du måste vara ansluten till master-databasen för att köra detta
CREATE LOGIN login1 WITH password='';

För att ett login skall få tillgång till en databas behöver man skapa en användare

-- Du måste vara ansluten till önskad databas för att lägga till en användare
CREATE USER login1User FROM LOGIN login1;

För att användaren skall få behörighet att göra någonting i databasen måste den tilldelas en roll eller få behörighet till specifika objekt. Rollerna och behörigheterna är samma som i vanliga SQL Server. Exempelvis kan vi ge en användare läsrättigheter till hela databasen genom

EXEC sp_addrolemember 'db_reader', 'login1User';

En utförlig beskrivning av behörighetshanteringen finns här.

Defragmentering av index

Som erfarna användare av SQL Server vet så behöver man defragmentera indexen med jämna mellanrum. Annars går databasen allt långsammare.

Även i Azure SQL databases behöver man defragmentera index. Man kan kontrollera hur fragmenterade indexen är med hjälp av en DMV.

SELECT 
	object_name(t1.object_id) AS tabell
	,t1.index_id
	,t1.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(
       DB_ID(N'Databasnamn')
      ,OBJECT_ID(N'Tabellnamn')
      ,NULL
      ,NULL
      ,NULL) AS t1

Om fragmenteringen är för hög (större än 10-20% som tumregel) så defragmenterar man genom att bygga om indexet. Det går inte, som i vanliga SQL Server, att göra en reorganize.

alter index all on tabellnamn rebuild with (online=on)

Man kan utelämna ”with (online=on)” om man vill. Då går det snabbare att bygga om indexet, men tabellen blir oåtkomlig under tiden.

Prestandaoptimering

Den vanliga Profilern i SQL Server går inte att ansluta till Azure SQL databases. Microsoft har heller inga planer på att stödja den. Istället har man ett antal andra alternativ.

  • Exekveringsplaner. Man har tillgång till både estimerad och actual Execution Plan precis som i vanliga SQL Server.
  • Extended Events. Dessa ger information på en mycket detaljerad nivå om vad som händer inne i databasen. I nuläget finns det 5 fördefinierade sessioner. Man kan inte skapa sina egna.
  • DMV:er (Dynamic Management Views). Det finns ett antal vyer och funktioner i Azure SQL databases som ger information om ”hur databasen mår”, tips på saknade index, information om aktiva transaktioner och lås, mm.

En utförligare beskrivning av prestandaoptimering av Azure SQL Databases finns här och här.

Webbmanagement

Alla Azure SQL databases kan administreras via ett webbgränssnitt. Adressen är https://servernamn.database.windows.net. Naturligtvis måste man där först ange sina loginuppgifter för att komma in.

azure-sql-database-webadmin

Man kommer sedan vidare till admingränssnittet där man kan jobba med databasen.

azure-sql-database-webadmin2

I webbgränssnittet kan man

  • Skapa nya tabeller eller redigera strukturen på befintliga
  • Redigera innehållet i tabeller
  • Skapa och redigera vyer och stored procedures
  • Köra valfria T-SQL kommandon och få resultatet presenterat

Själv föredrar jag dock Visual Studio eller Management Studio, som är oerhört mycket kraftfullare än detta webbgränssnitt. Dock kan webbgränssnittet vara användbart för mig i situationer där man inte har någon som helst möjlighet att koppla upp sig via mer kraftfulla verktyg.