Sauvegarde managée vers Azure

Lors de précédents billets, nous avons vu qu’il était possible de stocker des fichiers de sauvegarde ou bien les fichiers de bases de données et tirer un maximum d’avantages de la plateforme Azure (cout de stockage, facturation trafic sortant, pas de gestion hardware …).

Revenons un instant sur la sauvegarde. Lorsque, lors des missions que j’effectue chez mes clients, se pose la question de la stratégie de sauvegarde, j’ai un peu l’impression de jouer au grand inquisiteur avec des questions portant sur le RPO, le RTO, la granularité de restauration en fonction du point dans le temps, j’en passe et des meilleures.

Généralement, la réponse globale fournie se rapproche du “faites comme vous voulez, c’est vous l’expert SQL Server”. Certes, je suis flatté du compliment et, auréolé du titre d’expert, je m’exécute et propose une stratégie de sauvegarde (restauration ???) qui me parait adaptée au business de cette entreprise.

Et, dans certains cas, je me retrouve à avoir des sauvegardes de journal de transaction parfois ridiculement petites, parfois relativement volumineuses, due à une charge de travail et un intervalle entre 2 backup log trop importants.

Oui, je pourrais faire un effort et bâtir une stratégie un peu plus complexe avec différentes planifications en fonction des jours et des heures.

Et, dans certains cas, un peu de simplification serait le bienvenue. Et si je pouvais seulement dire que je sauvegarde une base, des bases, ou une instance, avec une durée de rétention de x jours et que je dispose de la possibilité de restaurer ma base à un point précis dans le temps. Le strict minimum nécessaire et suffisant. Les sauvegardes s’effectuant lorsque c’est nécessaire, en fonction de la charge du serveur.

Laissez moi vous présenter la sauvegarde managée.

introduit avec SQL Server 2014, le managed backup offre la possibilité de sauvegarder de manière très simple vos bases de données sur Windows Azure.

let’s go.

La première étape consiste à créer un compte de stockage, comme je l’ai fait ici. Notez que le conteneur sera créé automatiquement, du nom du serveur suivi de mssqlserver.

Ensuite, il faut créer le credential pour accéder à ce compte de stockage.

 USE [master]
GO
CREATE CREDENTIAL [AzureSQLBackup] 
WITH IDENTITY = N'sqlbackupconseilit', 
     SECRET = N'blablablabla'
GO

 

Eventuellement, vous pouvez chiffrer vos sauvegardes a l’aide d’un certificat.


--Création de la master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '##UnM0tDePasseC0mplexe!!!';
GO 

 --Création du certificat
CREATE CERTIFICATE CertificatPourSauvegardes
 WITH SUBJECT = 'Le certificat qui permettra de chiffrer les sauvegardes';
GO 

 -- Sauvegarde du certificat 
BACKUP CERTIFICATE CertificatPourSauvegardes
TO FILE = 'c:\temp\CertificatPourSauvegardes.cer'
WITH PRIVATE KEY (FILE='c:\temp\CertificatPourSauvegardes.pvk',
                  ENCRYPTION BY PASSWORD = '##UnAutrePWDintr0uvable!');
GO

Et créer la stratégie de sauvegarde :

-- Sauvegarde d'une base
Use msdb;
GO
EXEC smart_admin.sp_set_db_backup 
                 @database_name		= 'SmartBackupDemoDB' 
                ,@retention_days		= 10 
                ,@credential_name		= 'AzureSQLBackup'
                ,@encryption_algorithm	= 'AES_128'
                ,@encryptor_type		= 'Certificate'
                ,@encryptor_name		= 'CertificatPourSauvegardes'
                ,@enable_backup		= 1;
GO 

Et c’est tout !!!

image

Le managed Backup offre également la possibilité de sauvegarder l’ensemble des bases d’une instance. Il faut alors utiliser la procédure stockée sp_set_instance_backup.

Use msdb;
Go
   EXEC smart_admin.sp_set_instance_backup
                 @enable_backup		=1
                ,@retention_days		=30 
                ,@credential_name		='SmartBackupDemoDB'
                ,@encryption_algorithm	='AES_128'
                ,@encryptor_type		= 'Certificate'
                ,@encryptor_name		='CertificatPourSauvegardes';

 

Si nous créons un peu d’activité, nous allons pouvoir visualiser les sauvegardes …

-- Un peu d'activité
 Use SmartBackupDemoDB
 GO

 CREATE TABLE TestTable
 (
	ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Filler char(500) NULL DEFAULT 'Hello world !'
 )
 GO

 SET NOCOUNT ON

 INSERT INTO TestTable
 DEFAULT VALUES
 GO 10000

image

Il est également possible de visualiser ces informations au travers de vues systèmes :

-- Diagnostic des sauvegardes
Use msdb;
Go

-- Dernieres 30 minutes par defaut
EXEC smart_admin.sp_get_backup_diagnostics 

image

Pour obtenir la liste des sauvegardes disponibles, il faut utiliser la procédure smart_admin.fn_avalaible_backups.

Il est possible de visualiser les paramètre de sauvegarde au travers de la fonction système smart_admin.fn_backup_db_config.

image

Si vous souhaitez être notifié par email d’un problème lors d’une sauvegarde (il faut avoir paramétré Database mail et un profil au niveau de l’agent):

-- Notification via email
Use msdb
Go
EXEC smart_admin.sp_set_parameter 
	@parameter_name = 'SSMBackup2WANotificationEmailIds', 
	@parameter_value = 'christophe_laporte@hotmail.fr'

Evitez de faire une sauvegarde au travers de SSMS ou d’un ordre T-SQL classique au risque de briser la chaine des sauvegarde du journal (un backup database sera alors initié). Il faut impérativement utiliser smart_admin.sp_backup_on_demand. Utilisez la valeur de paramètre Database ou Log pour spécifier quel type de sauvegarde vous souhaitez utiliser.

image

-- Effectuer une sauvegarde manuelle
Use MSDB
Go
EXEC smart_admin.sp_backup_on_demand
	 @database_name = 'SmartBackupDemoDB'
	,@type = 'Database'

image

Pour la restauration, ne changez rien : utilisez management studio pour préparer vos scripts de restore, cela fonctionne très bien !

imageimage

A priori, le système fonctionne plutôt bien. Les sauvegardes ont bien l’air de se faire automatiquement. Nul doute que certain d’entre vous, seront conquis, et à vrai dire, le système parait très intéressant, mis a part pour des grosses bases, car on ne peut faire que du backup database et du backup log. Pas de sauvegarde différentielle, ni de sauvegarde fichier ou groupe de fichier.

On peut légitiment se poser la question des évènements qui déclenchent une sauvegarde. Une page du site MSDN nous donne les règle suivantes :

  • Backup Database
    • à la création de la stratégie
    • la taille du fichier journal augmente de 1GB+
    • intervalle maximum : 1 semaine
    • problème rencontré dans le chainage des backup log
  • Backup Log
    • aucune backup log présent
    • utilisation du journal de transaction > 5 MB
    • intervalle maximum : 2 heures

Vous pouvez à tout instant interrompre ou reprendre la sauvegarde managée au travers de sp_ backup_master_switch alors que fn_is_master_switch_on vous permet de savoir si votre sauvegarde est managée ou non.

Bons backups !

A propos Christophe

Consultant SQL Server Formateur certifié Microsoft MVP SQL Server MCM SQL Server 2008
Cet article, publié dans SQL Server, Windows Azure, est tagué , , . Ajoutez ce permalien à vos favoris.

2 commentaires pour Sauvegarde managée vers Azure

  1. Ping : SQL Server Backup to Windows Azure Tool | Christophe LAPORTE – Consultant SQL Server

  2. Ping : SQL Server 2014 – Managed backup | Christophe LAPORTE – Consultant SQL Server

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s