Hello
j’ai eu récemment à effectuer, auprès d’un de mes clients, une opération de backup restore SQL Server d’une instance SQL Server Amazon RDS vers une instance IaaS sur une EC2 Amazon.
Rappelons qu’en mode RDS, le client n’a pas la main, ni sur les disques utilisés par l’instance SQL Server, ni même sur les backup automatiques générés par AWS.
La partie RDS
Sur Amazon RDS, une base de données sera accessible grâce aux informations du “endpoint” et du port de communication.
Dans notre exemple, nous avons créé une instance nommée “capdatasqlserver” avec pour édition SQL Server 2016 Express Edition.
Afin de pouvoir effectuer ces opérations de backup restauration rds <-> IaaS, il faudra respecter les prérequis suivants :
- Détenir un bucket S3 qui servira à stocker les fichiers de sauvegardes réalisés sur les bases
- Création d’un nouveau groupe d’options permettant d’appeler SQLSERVER_BACKUP_RESTORE. Attention, ce groupe devra être compatible avec notre version SQL Server (ici version 2016).
- Création d’un rôle IAM qui sera utilisé lors du processus de backup afin que notre instance puisse appeler l’option SQLSERVER_BACKUP_RESTORE
Bucket S3
Pour la création d’un bucket S3, nous nous connecterons avec un utilisateur ayant les stratégies adéquat pour configurer le service S3. Cette étape peut aussi bien être faite via la console Amazon ou bien via AWS CLI :
c:\ aws s3api create-bucket --bucket capdatabackup2019 --region eu-west-3 --create-bucket-configuration LocationConstraint=eu-west-3
{
"Location": "https://capdatabackup2019.s3.amazonaws.com/"
}On vérifiera la création :
c:\ aws s3api list-buckets --query "Buckets[?contains(Name,'backup')]"
[
{
"CreationDate": "2019-02-07T09:44:53.000Z",
"Name": "capdatabackup2019"
}
]
Le groupe d’options
La création d’un nouveau groupe d’options sera nécessaire pour effectuer ces opérations backup restore
la version 13.00 correspond à SQL Server 2016
Avec AWS CLI, nous utiliserons la syntaxe suivante :
aws rds create-option-group --option-group-name capdataoptionssql2016 --engine-name sqlserver-ex --major-engine-version 13.00 --option-group-description "groupe d'option pour SQL Server 2016" --region eu-west-3
On ajoute ensuite l’option SQLSERVER_BACKUP_RESTORE
La page suivante nous permettra d’ajouter l’option, choisir le rôle (ou en créer un nouveau), puis chosir le bucket (ou en créer un).
On choisit le bucket que l’on a créé au premier paragraphe (à noter que l’on aurait pu en créer un nouveau).
Le rôle IAM
En théorie, ce rôle a été créé lors de la page d’ajout de l’option. Mais vous pouvez très bien l’avoir créé en avance de phase. Dans tous les cas, ce rôle devra avoir :
une stratégie d’approbation pour les backup et restore natifs avec le JSON suivant :
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Service": "rds.amazonaws.com"
},
"Action": "sts:AssumeRole"
}
]
}Et une stratégie d’autorisation pour les backup / restore natifs.
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"s3:ListBucket",
"s3:GetBucketLocation"
],
"Resource": [
"arn:aws:s3:::capdatabackup2019"
]
},
{
"Effect": "Allow",
"Action": [
"s3:GetObjectMetaData",
"s3:GetObject",
"s3:PutObject",
"s3:ListMultipartUploadParts",
"s3:AbortMultipartUpload"
],
"Resource": [
"arn:aws:s3:::capdatabackup2019/*"
]
}
]
}Il restera ensuite à modifier l’instance SQL Server afin de pouvoir lui affecter ce nouveau groupe d’options “capdataoptionssql2016”. Ceci se fera en sélectionnant l’instance et cliquer sur “modifier”.
En mode AWS Cli, nous devrons créer le rôle avant de modifier le groupe d’options. De ce fait, nous n’aurons pas à renseigner le bucket car c’est celui qui est dans le rôle qui sera pris en compte.
2 documents JSON seront créés pour la relation d’approbation et la stratégie d’autorisation.
c:\ aws iam create-role --role-name rolebackupSQL2016--assume-role-policy-document file://Roles\Relation_Approbation.json
c:\ aws iam put-role-policy --role-name rolebackupSQL2016 --policy-name sqlNativeBackup-2019-02-07--policy-document file://Strategies\stratégies.json
On ira ensuite modifier le groupe d’options
c:\ aws rds add-option-to-option-group --option-group-name capdataoptionssql2016 --apply-immediately --options "OptionName=SQLSERVER_BACKUP_REST
ORE,OptionSettings=[{Name=IAM_ROLE_ARN,Value=arn:aws:iam::018033502850:role/service-role/rolebackupSQL2016}]" --region eu-west-3
c:\ aws rds modify-db-instance --db-instance-identifier capdatasqlserver --option-group-name capdataoptionssql2016 --apply-immediately --region eu-west-3
Sauvegarde de la base RDS
Les prérequis étant effectués, on passera à la sauvegarde de la base RDS. Nous utiliserons un client sur lequel SSMS est installé.
La connexion à notre instance RDS se fera grâce au endpoint fourni au début avec le port par défaut.
les commandes listées ci dessous seront à exécuter sur SSMS.
A noter que par défaut, il existe sur notre instance une base nommé “rdsadmin”. Celle ci est utilisée par Amazon afin de stocker des procédures spécifiques à Amazon ( un peu comme le package RDSADMIN sur une base Oracle RDS). Amazon y stocke également des tables et objets propriétaire.
Il est donc vivement conseillé de ne PAS utiliser cette base pour écrire des données métier, et de créer une autre base.
On créera une nouvelle base, nommée “capdata_db” pour illustrer notre exemple :
CREATE DATABASE [capdata_db] CONTAINMENT = NONE ON PRIMARY ( NAME = N'capdata_db', FILENAME = N'D:\RDSDBDATA\DATA\capdata_db.mdf' , SIZE = 20480KB , MAXSIZE = 512000KB , FILEGROWTH = 10%) LOG ON ( NAME = N'capdata_db_log', FILENAME = N'D:\RDSDBDATA\DATA\capdata_db_log.ldf' , SIZE = 10240KB , MAXSIZE = 204800KB , FILEGROWTH = 10%) GO
A noter que l’on ne pourra pas modifier le FILENAME, nous sommes sur RDS.!
Nous donnerons les droits, à notre utilisateur “capdata”, pour effectuer des backup et restauration sur cette base.
USE [capdata_db] GO ALTER ROLE [db_backupoperator] ADD MEMBER [capdata] GO
Backup
A partir de la, nous pourrons lancer la procédure “rds_backup_database” :
exec msdb.dbo.rds_backup_database @source_db_name='capdata_db', @s3_arn_to_backup_to='arn:aws:s3:::capdatabackup2019/backupCapdataDB.bak', @overwrite_S3_backup_file=1, @type='FULL';
On pourrait également ajouter le chiffrement avec l’option “@kms_master_key_arn=’arn:aws:kms:region:account-id:key/key-id‘,”
Le backup de la base “capdata_db” est réalisé sur le bucket s3 nommé “capdatabackup2019”
Dans notre exemple, nous faisons un backup FULL, il sera également possible de faire un backup incremental ou transaction log.
On pourra suivre l’évolution du processus avec
exec msdb.dbo.rds_task_status @db_name=’capdata_db’;
La partie on-premise / IaaS
Transfert du fichier de backup
Nous irons voir sur le bucket la présence du fichier de backup pour le transférer par la suite :
c:\ aws s3 ls "s3://capdatabackup2019/" 2019-02-07 13:44:23 2904576 backupCapdataDB.bak
On passera au transfert du fichier .bak sur un disque local de notre EC2.
Nous prenons en compte le fait que sur notre EC2, il existe une installation de SQL Server avec une instance pouvant accueuillir la nouvelle base “capdata_db”.
c:\ aws s3 cp "s3://capdatabackup2019/backupCapdataDB.bak" c:\users\manu\backupCapdataDB.bak download: s3://capdatabackup2019/backupCapdataDB.bak to ..\users\manu\backupCapdataDB.bak
avec
C:\ dir c:\users\manu Directory of c:\users\manu 02/07/2019 01:55 PM <DIR> . 02/07/2019 01:55 PM <DIR> .. 02/07/2019 01:44 PM 2,904,576 backupCapdataDB.bak 01/23/2019 02:42 PM 32 check_dispo.txt 2 File(s) 2,904,608 bytes 2 Dir(s) 11,426,086,912 bytes free
On copiera ce fichier dans le répertoire dédié aux backup SQL Server de notre instance (ici C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Backup).
Restauration
Nous pourrons restaurer notre base de données SQL Server sur notre instance locale de manière conventionnelle comme ceci se fait habituellement sur un système On-premise :
On pousse la restauration
USE [master] RESTORE DATABASE [capdata_db] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Backup\backupCapdataDB.bak' WITH FILE = 1, MOVE N'capdata_db' TO N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\capdata_db.mdf', MOVE N'capdata_db_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\capdata_db_log.ldf', NOUNLOAD, STATS = 5 GO
La base a été restaurée à partir des informations suivantes lus dans le fichier de backup
Notre base est maintenant présente sur notre instance on-premise ou IaaS
Et inversement ?
En effet, il est tout à fait possible de faire le chemin inverse, d’ailleurs cela se fait d’avantage chez de nombreux clients, à savoir, pousser un backup de base de données d’une base On-premise vers une nouvelle instance RDS.
Nous partirons du fait que le sujet des prérequis évoqués ci dessus est traité sur notre système AWS.
/!\ /!\ Attention, dans notre exemple, nous avons une instance On premise version SQL Server 2017, or sur l’instance RDS, dans notre exemple, cest une SQL 2016. Il ne sera donc pas possible d’effectuer une restauration de cette manière. Penser à mettre à jour votre instance SQL Server RDS avant.
les étapes seront alors, les suivantes :
- Effectuer un backup d’une base “On-premise/IaaS” que nous appellerons “manuelo”
BACKUP DATABASE [manuelo] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Backup\manuelo.bak' WITH FORMAT, INIT, MEDIANAME = N'backup_test_RDS', NAME = N'manuelo-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
- Transfert du fichier .bak vers notre bucket s3
C:\ aws s3 cp manuelo.bak "s3://capdatabackup2019" upload: .\manuelo.bak to s3://capdatabackup2019/manuelo.bak
On s’assure que le transfert s’est correctement effectué
C:\ aws s3 ls "s3://capdatabackup2019/" 2019-02-07 13:44:23 2904576 backupCapdataDB.bak 2019-02-07 14:33:36 3037696 manuelo.bak
- Restauration du backup sur l’instance RDS
On se connectera via SSMS sur notre instance en renseignant le “endpoint”.
Puis nous utiliserons la procédure de restauration nommée
“exec msdb.dbo.rds_restore_database”. Dans notre exemple, ce sera :
exec msdb.dbo.rds_restore_database @restore_db_name='manuelo', @s3_arn_to_restore_from='arn:aws:s3:::capdatabackup2019/manuelo.bak';
Dans cet exemple, il est également possible d’utiliser le chiffrement avec l’option
@kms_master_key_arn=’arn:aws:kms:region:account-id:key/key-id‘;
Nous pourrons également suivre le processus avec msdb.dbo.rds_task_status :
Une fois terminé
N’hésitez pas à laisser des commentaires si besoin.
Emmanuel RAMI
Continuez votre lecture sur le blog :
- SQL Server 2022 Backup / Restore via un bucket S3 sous AWS (Louis PROU) [AWSSQL Server]
- Export d’une VM d’un ESX vers une EC2 AWS (Emmanuel RAMI) [AWS]
- SQL Server : resynchroniser un login avec le user d’une base après une restauration grâce au SID (Emmanuel RAMI) [SQL Server]
- Oracle RDS : effectuer des backup RMAN en mode PaaS. (Emmanuel RAMI) [AWSNon classéOracle]
- Retrouver la requête à l’origine d’une erreur 8623 “The query processor ran out of internal resources and could not produce a query plan” (David Baffaleuf) [SQL Server]