[SQL 2014/Azure] backup SQL database in Azure

[Simke]

Medetweakers,

Graag had ik jullie ervaring geraadpleegd voor een vraag waar ik niet direct het beste antwoord op vind.

Mijn vraag:
Een klant van ons gebruikt Navision waarbij de database op een SQL 2014 draait.
Graag hadden we deze als backup ook bewaard in Azure, momenteel heeft de klant al een Azure backup draaien voor de data op de server.

Ideaal zou dus zijn dat er 1 up-to-date backup is waarbij de nieuwe waarden/records gewoon toegevoegd worden aan de backup.

Relevante software en hardware die ik gebruik
Navision op SQL 2014 (on premise)
Azure subscription aanwezig met azure backup files/folders

Wat ik al gevonden of geprobeerd heb
Na veel zoeken heb ik denk ik 3 mogelijke oplossingen, alleen weet ik niet welke de beste is door gebrek aan ervaring met SQL en backup.

1: bestanden gewoon bij de azure backup steken en elke nacht laten mee backuppen.
Voordeel; geen extra configuratie
Nadelen: telkens bestanden volledig uploaden (gaat lang duren en duurder worden)

2: azure backup server.
Voordeel: nog niet volledig uit
Nadelen: extra server nodig (kan niet op DC geïnstalleerd worden), geen idee of het een append is van de data of hoe de werking ervan is.
Naar ik meen gelezen te hebben blijft de initiele kopie lokaal staan?

3: backup vanuit SQL server naar URL
Voordeel: ingebouwde backupfunctionaliteit
Nadelen: geen idee van werking/restore.
Ik heb deze oplossing bekeken op een lokale test-installatie en je hebt de keuze tussen full en differential waarbij de full telkens de volledige database aan de backupfile toevoegt, niet ideaal dus.
Differential voegt dan telkens enkel de wijzigingen toe aan de backup file?

Hoe lossen jullie dit op of wat is volgens jullie de beste aanpak in deze?

Thx voor de input.

Simke

[Question Mark]

Wat wil je bereiken met die backup?

Je geeft heel veel technische zaken/oplossingen aan, maar ik ben eerst even benieuwd naar het probleem of functionele eis die je wilt oplossen.

[Simke]

Question Mark schreef op donderdag 16 maart 2017 @ 12:00:
Wat wil je bereiken met die backup?

Je geeft heel veel technische zaken/oplossingen aan, maar ik ben eerst even benieuwd naar het probleem of functionele eis die je wilt oplossen.

Goeie vraag

De klant heeft met zijn verzekeringsmaatschappij een soort van verzekering betreffende hard- en software en blijkbaar is het een groot verschil als de data kan gerecupereerd worden in geval van brand/schade/diefstal en de lokale backup zou vernietigd zijn.

Dus enigste reden waarom backup:

alles kunnen terugzetten zonder dataverlies. Als dit al mogelijk is op deze manier met SQL

[Question Mark]

Dan is de hamvraag even hoeveel dataverlies dan nog wel acceptabel is...

Is één offsite backup per 24 voldoende (en dus 24 uur dataverlies), of moet dit minder zijn? En hoelang mag een recovery maximaal duren?

[Simke]

Question Mark schreef op donderdag 16 maart 2017 @ 15:41:
Dan is de hamvraag even hoeveel dataverlies dan nog wel acceptabel is...

Is één offsite backup per 24 voldoende (en dus 24 uur dataverlies), of moet dit minder zijn? En hoelang mag een recovery maximaal duren?

24u verlies is aanvaardbaar, recovery is enkel moest er iets groot gebeuren dat zowel de server als de nas niet meer bruikbaar is...

[CMD-Snake]

Ga eerste eens bepalen hoeveel dataverlies je maximaal kan hebben. SQL Server kan heel simpel, maar ook heel granulair back-ups maken (full back-up, differentieel en log). Afhankelijk van hoeveel dataverlies er mag zijn moet dit ingesteld gaan worden.

SQL Server 2014 kan naar Azure BLOB storage een back-up maken net als naar een ander medium zoals een harde schijf of een tape. Een restore is niet veel anders vanuit Azure.

Je kan dus je hele back-up schema alles naar Azure laten sturen. Dat is denk ik dan de makkelijkste route als het gaat om de database back-ups. Je kan ook in de Azure BLOB storage een hele back-up set managen vanuit SQL Server.

Als ik het zo lees is uploaden naar Azure gratis. Enkel de storage en het downloaden betaal je voor.

Simke schreef op donderdag 16 maart 2017 @ 12:34:
alles kunnen terugzetten zonder dataverlies. Als dit al mogelijk is op deze manier met SQL

Dat kan, SQL Server kan met nagenoeg nul dataverlies werken. Ligt alleen aan hoeveel geld je te besteden hebt.

De simpelste route met een enkelvoudig uitgevoerde SQL Server is een uitgebreid back-up schema. Bijvoorbeeld elke nacht een full backup. Elke middag een differential back-up en bijvoorbeeld tussen kantoor uren elke 5 minuten een log back-up maken. Je krijgt dan:

full back-up (middernacht) - log back-ups (elke 5 min vanaf bijv 8 uur) - differential back-up (12.00) - log back-ups (elke 5 min tot 18.00) en dan begint het weer van voor af aan.

3: backup vanuit SQL server naar URL
Voordeel: ingebouwde backupfunctionaliteit
Nadelen: geen idee van werking/restore.
Ik heb deze oplossing bekeken op een lokale test-installatie en je hebt de keuze tussen full en differential waarbij de full telkens de volledige database aan de backupfile toevoegt, niet ideaal dus.
Differential voegt dan telkens enkel de wijzigingen toe aan de backup file?

Differential is enkel de verschillen sinds de laatste full of differential back-up. Je kan een hele chain maken van differential back-ups, maar als die chain verbroken wordt heb je een levensgroot probleem. Restore is dan niet meer mogelijk. Stel je maakt eventjes een full back-up om een testomgeving van nieuwe data te voorzien dan is je chain met back-ups in Azure kapot. (Tip: gebruik in dit geval de copy-only back-up!).

Je kan ook met meerdere back-up schema's werken. Eentje die lokaal een uitgebreid schema aanhoudt (full back + differential + logs) en bijvoorbeeld eentje die een keer per week of per maand een full back-up naar Azure stuurt, die zou ik dan wel een copy-only versie maken om je lokale chain niet te slopen.

Wat de beste oplossing is moet je bepalen aan de hand van de eisen en beperkingen die je opgelegd krijgt.

[Simke]

CMD-Snake schreef op zondag 19 maart 2017 @ 13:12:
Ga eerste eens bepalen hoeveel dataverlies je maximaal kan hebben. SQL Server kan heel simpel, maar ook heel granulair back-ups maken (full back-up, differentieel en log). Afhankelijk van hoeveel dataverlies er mag zijn moet dit ingesteld gaan worden.

SQL Server 2014 kan naar Azure BLOB storage een back-up maken net als naar een ander medium zoals een harde schijf of een tape. Een restore is niet veel anders vanuit Azure.

Je kan dus je hele back-up schema alles naar Azure laten sturen. Dat is denk ik dan de makkelijkste route als het gaat om de database back-ups. Je kan ook in de Azure BLOB storage een hele back-up set managen vanuit SQL Server.

Als ik het zo lees is uploaden naar Azure gratis. Enkel de storage en het downloaden betaal je voor.


[...]


Dat kan, SQL Server kan met nagenoeg nul dataverlies werken. Ligt alleen aan hoeveel geld je te besteden hebt.

De simpelste route met een enkelvoudig uitgevoerde SQL Server is een uitgebreid back-up schema. Bijvoorbeeld elke nacht een full backup. Elke middag een differential back-up en bijvoorbeeld tussen kantoor uren elke 5 minuten een log back-up maken. Je krijgt dan:

full back-up (middernacht) - log back-ups (elke 5 min vanaf bijv 8 uur) - differential back-up (12.00) - log back-ups (elke 5 min tot 18.00) en dan begint het weer van voor af aan.


[...]


Differential is enkel de verschillen sinds de laatste full of differential back-up. Je kan een hele chain maken van differential back-ups, maar als die chain verbroken wordt heb je een levensgroot probleem. Restore is dan niet meer mogelijk. Stel je maakt eventjes een full back-up om een testomgeving van nieuwe data te voorzien dan is je chain met back-ups in Azure kapot. (Tip: gebruik in dit geval de copy-only back-up!).

Je kan ook met meerdere back-up schema's werken. Eentje die lokaal een uitgebreid schema aanhoudt (full back + differential + logs) en bijvoorbeeld eentje die een keer per week of per maand een full back-up naar Azure stuurt, die zou ik dan wel een copy-only versie maken om je lokale chain niet te slopen.

Wat de beste oplossing is moet je bepalen aan de hand van de eisen en beperkingen die je opgelegd krijgt.

Eerst en vooral thx voor het uitgebreide antwoord!

Ondertussen verdere info zitten opzoeken en dit lokaal bekijken en het wordt waarschijnlijk een backupplan in SQL dat 2 x per week een full backup neemt naar azure.
Dataverlies als zijnde een paar dagen is aanvaardbaar in de veronderstelling dat de azure backup pas aangesproken gaat worden als er zich echt een groot probleem gesteld heeft (brand o.i.d.).
Dan zijn een paar dagen dataverlies "niets" in vergelijking met de andere zorgen van dat moment. Dat is te reproduceren.

Alleen de copy-only optie ben ik nog niet uit. Klopt het als er verder geen sql-backups gebeuren dat dit niet van belang is?
Momenteel zitten de mdf en ldf files wel in de full server backup maar in hoeverre dit specifiek voor sql een 100% betrouwbare backup is weet ik niet.
Mss moet ik maar een extra backup plan maken in SQL die eerst een lokale backup maakt?

Thx, Simke

[Meekoh]

Simke schreef op woensdag 22 maart 2017 @ 09:51:
[...]
Alleen de copy-only optie ben ik nog niet uit. Klopt het als er verder geen sql-backups gebeuren dat dit niet van belang is?
Momenteel zitten de mdf en ldf files wel in de full server backup maar in hoeverre dit specifiek voor sql een 100% betrouwbare backup is weet ik niet.
Mss moet ik maar een extra backup plan maken in SQL die eerst een lokale backup maakt?

Het simpelweg backuppen van de mdf en ldf files is zeker niet 100% betrouwbaar. Ik raad je dat echt af. Er vind namelijk geen afstemming plaats tussen de backup software die je windows disk backupt en SQL server over op dat moment lopende transacties. Stel je backup software backupt de Disk (met mdf en ldf file) midden in een grote transactie, dan heb je een grote kans dat de data die dan gebackupt is corrupt of in ieder geval niet meer consistent is.

Het idee achter Copy-Only is dat de chain van Transactie log backups of Diff backups niet onderbroken wordt. Ik zal het proberen grafisch weer te geven.
Stel de Chain ziet er zo uit, 1 Full backup en daarna 3 Transactie log backups:
Full -> Tlog ->Tlog ->Tlog
Als je dan een restore naar het laatste moment in tijd wil doen, moet je eerst de Full restoren en dan de Tlog backups in volgorde.
Als je na de 3e Tlog backup weer een Full backup doet, dan heb je eigenlijk de Tlog backups laten "vervallen":
Full -> Tlog ->Tlog ->Tlog -> Full
De Opvolgende Tlog Backups beginnen dus vanaf die 2e Full backup.
De tweede Full backup moet je dus gaan bewaren! Als je bijvoorbeeld alleen een backup nodig hebt omdat bijv. een developer lokaal een kopietje van de database nodig heeft en je wilt niet die backup permanent gaan bewaren (en dus de originele chain intact laten) dan gebruik je een Copy-Only Backup. Copy-Only onderbreekt de chain niet.

[CMD-Snake]

Simke schreef op woensdag 22 maart 2017 @ 09:51:
Momenteel zitten de mdf en ldf files wel in de full server backup maar in hoeverre dit specifiek voor sql een 100% betrouwbare backup is weet ik niet.

Dat is geen correcte manier om een database te back-uppen. Als je via het mechanisme van SQL Server een back-up maakt dan zorgt SQL Server ervoor dat de back-up consistent is, dus geen openstaande transacties. Als je een kopie maakt van de .mdf en .ldf bestanden dan weet je totaal niet wat de stand van zaken is bij een herstel.

Simke schreef op woensdag 22 maart 2017 @ 09:51:
Mss moet ik maar een extra backup plan maken in SQL die eerst een lokale backup maakt?

Je kan twee full back-ups achter elkaar maken. Eentje naar Azure en dan eentje lokaal. Ik zou die lokale als laatste doen zodat die meteen een nieuwe back-up chain kan beginnen.

[Simke]

Meekoh schreef op woensdag 22 maart 2017 @ 10:27:
[...]

Het simpelweg backuppen van de mdf en ldf files is zeker niet 100% betrouwbaar. Ik raad je dat echt af. Er vind namelijk geen afstemming plaats tussen de backup software die je windows disk backupt en SQL server over op dat moment lopende transacties. Stel je backup software backupt de Disk (met mdf en ldf file) midden in een grote transactie, dan heb je een grote kans dat de data die dan gebackupt is corrupt of in ieder geval niet meer consistent is.

Het idee achter Copy-Only is dat de chain van Transactie log backups of Diff backups niet onderbroken wordt. Ik zal het proberen grafisch weer te geven.
Stel de Chain ziet er zo uit, 1 Full backup en daarna 3 Transactie log backups:
Full -> Tlog ->Tlog ->Tlog
Als je dan een restore naar het laatste moment in tijd wil doen, moet je eerst de Full restoren en dan de Tlog backups in volgorde.
Als je na de 3e Tlog backup weer een Full backup doet, dan heb je eigenlijk de Tlog backups laten "vervallen":
Full -> Tlog ->Tlog ->Tlog -> Full
De Opvolgende Tlog Backups beginnen dus vanaf die 2e Full backup.
De tweede Full backup moet je dus gaan bewaren! Als je bijvoorbeeld alleen een backup nodig hebt omdat bijv. een developer lokaal een kopietje van de database nodig heeft en je wilt niet die backup permanent gaan bewaren (en dus de originele chain intact laten) dan gebruik je een Copy-Only Backup. Copy-Only onderbreekt de chain niet.

Nogmaals bedankt voor de uitleg.

Niets van develop, kopietjes nodig, testen, ...

Full copy van database doen vanuit SQL en in slechtste geval terugzetten.

Gecombineerd met:

CMD-Snake schreef op donderdag 23 maart 2017 @ 19:14:

[...]

Je kan twee full back-ups achter elkaar maken. Eentje naar Azure en dan eentje lokaal. Ik zou die lokale als laatste doen zodat die meteen een nieuwe back-up chain kan beginnen.

Dan heb ik zowel een correcte offline als een online backup.

Thx om mee te denken

[Killah_Priest]

Meekoh schreef op woensdag 22 maart 2017 @ 10:27:
[...]

Het simpelweg backuppen van de mdf en ldf files is zeker niet 100% betrouwbaar. Ik raad je dat echt af. Er vind namelijk geen afstemming plaats tussen de backup software die je windows disk backupt en SQL server over op dat moment lopende transacties. Stel je backup software backupt de Disk (met mdf en ldf file) midden in een grote transactie, dan heb je een grote kans dat de data die dan gebackupt is corrupt of in ieder geval niet meer consistent is.

Niet helemaal waar, hiervoor is Volume Shadow Copy gekomen : juist om dit scenario te voorkomen, hoewel dit risico altijd zal blijven bestaan is deze kans erg klein. Sql server heeft ook gewoon een vss writer waardoor deze afstemming er dus wel is.

(Even los van de discussie of het wel verstandig is of niet).

[CMD-Snake]

Killah_Priest schreef op maandag 27 maart 2017 @ 18:35:
Sql server heeft ook gewoon een vss writer waardoor deze afstemming er dus wel is.

(Even los van de discussie of het wel verstandig is of niet).

Het is niet geweldig dat systeem. Pakketten als TSM haken aan op die VSS writer, maar je moet niet vragen waar je mee eindigt. Uit ervaring weet ik dat je soms ook met een gare back-up eindigt.

De klassieke manier vanuit SQL Server is de veiligste naar mijn mening.

[Simke]

Killah_Priest schreef op maandag 27 maart 2017 @ 18:35:
[...]


Niet helemaal waar, hiervoor is Volume Shadow Copy gekomen : juist om dit scenario te voorkomen, hoewel dit risico altijd zal blijven bestaan is deze kans erg klein. Sql server heeft ook gewoon een vss writer waardoor deze afstemming er dus wel is.

(Even los van de discussie of het wel verstandig is of niet).

Door Volume Shadow Copy dacht ik dat de backup van de sql files sowieso goed zou zijn...

CMD-Snake schreef op maandag 27 maart 2017 @ 22:50:
[...]


Het is niet geweldig dat systeem. Pakketten als TSM haken aan op die VSS writer, maar je moet niet vragen waar je mee eindigt. Uit ervaring weet ik dat je soms ook met een gare back-up eindigt.

De klassieke manier vanuit SQL Server is de veiligste naar mijn mening.

... maar de backup vanuit SQL zal idd wel iets veiliger zijn

Weer bijgeleerd dus

[Meekoh]

Killah_Priest schreef op maandag 27 maart 2017 @ 18:35:
[...]


Niet helemaal waar, hiervoor is Volume Shadow Copy gekomen : juist om dit scenario te voorkomen, hoewel dit risico altijd zal blijven bestaan is deze kans erg klein. Sql server heeft ook gewoon een vss writer waardoor deze afstemming er dus wel is.

(Even los van de discussie of het wel verstandig is of niet).

Dan nog is er een risico op een gare database omdat de ldf en de mdf file uit sync lopen in je backup.
Ze worden namelijk niet exact op hetzelfde tijdstip gebackupt vanuit het oogpunt van het Filesystem.
In de SQL Engine gebeurd namelijk veel meer op het moment dat er via SQL een backup gemaakt wordt. Iets waar VSS of je Filesystem backup niets mee doet

[Killah_Priest]

Meekoh schreef op dinsdag 28 maart 2017 @ 10:26:
[...]

Dan nog is er een risico op een gare database omdat de ldf en de mdf file uit sync lopen in je backup.
Ze worden namelijk niet exact op hetzelfde tijdstip gebackupt vanuit het oogpunt van het Filesystem.
In de SQL Engine gebeurd namelijk veel meer op het moment dat er via SQL een backup gemaakt wordt. Iets waar VSS of je Filesystem backup niets mee doet

Daarom zeg ik dus ook dat dit risico altijd zal blijven bestaan. In veel gevallen zal een backup gewoon goed gaan, maar op grote business critical databases zou ik hier niet op vertrouwen uiteraard (ook het feit dat problemen met VSS writers oorzaak nummer 1 is van het falen van backups in Windows).