[SQL Server] Stretch Database : présentation et utilisation

Ce billet présente la fonctionnalité Stretch Database (ou StretchDB), qui apparaît à partir de SQL Server 2016, et quelques éléments d’utilisation.

Qu’est-ce que Stretch Database ?

Stretch Database (ou StretchDB) est une nouvelle fonctionnalité qui apparaît à partir de SQL Server 2016. Elle permet d’archiver une ou plusieurs tables d’une base de données locale (on-premise) vers une base de données « miroir » dans Azure, de façon silencieuse et transparente. D’une certaine manière, elle sert également à une base de données on-premise de profiter à distance des capacités de stockage et de performances d’Azure, sans nécessité de paramétrages supplémentaires au niveau des applications clientes.

Le schéma ci-dessous – tiré de MSDN – donne un aperçu des interactions autour d’une StretchDB :


  • Local database fait référence à une base de données on-premise.
  • Local data fait référence aux données – au sein d’une base de données avec Stretch Database activé- qui ne seront pas migrées vers Azure, suivant la configuration des tables.
  • Eligible data fait référence aux données – au sein d’une base de données avec Stretch Database activé – qui seront migrées vers Azure.
  • Remote endpoint fait référence au lieu, dans Azure, où se trouvent les données de la base de données à distance.
  • Remote data fait référence aux données – au sein d’une base de données avec Stretch Database activé – qui ont déjà été migrées vers Azure.
  • User Application fait référence à une application cliente connectée à la base de données on-premise, sachant que la migration des données est transparente, et ne nécessite pas de configuration supplémentaire, comme le résume le schéma ci-dessous :


Création et utilisation d’une stretch database

On suppose qu’une base de données et une table ont été préalablement créées, avec quelques données insérées :

USE master
GO
IF NOT EXISTS (SELECT TOP 1 1 FROM sys.databases WHERE name = 'MyStretchDB')
	CREATE DATABASE MyStretchDB
GO

USE MyStretchDB
GO
CREATE TABLE dbo.MyTable
(
	FirstName VARCHAR(50),
	LastName VARCHAR(50),
	Age INT
)
GO

USE MyStretchDB
GO
INSERT INTO dbo.MyTable(FirstName, LastName, Age)
	VALUES('Karl', 'Schwarzberg',34)
		 ,('Tom', 'Roberts',41)
                 ,('Sylvia', 'Demeer',29)
                 ,('Georges', 'Seneca',53)
                 ,('Eve', 'Martin',22);
GO

    Modus operandi de création d’une StretchDB

  • Activation préalable de l’archivage de données à distance (remote data archive) :
EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'remote data archive', 1
GO
RECONFIGURE WITH OVERRIDE
GO
  • Activation de la fonctionnalité Stretch Database au niveau d’une base de données cible.

Via l’interface SQL Server Management Studio (SSMS), cela peut se faire comme suit :

  • Clic-droit sur la base de données cible et sélection de Stretch au sein du menu contextuel Tasks.


Cela aura pour effet de lancer l’assistant d’activation de la StretchDB :


  • Spécification d’un mot-de-passe pour la clé maître destinée à sécuriser la base on-premise.


  • Sélection de la table à « stretcher ».


  • Vérification et validation de la table à « stretcher ».


  • Authentification à Microsoft Azure avec les bonnes informations d’inscription (posséder un abonnement Azure, au préalable).


Après avoir cliqué sur Sign-In…,


… et rempli les informations d’abonnement à Azure, il ne restera plus qu’à choisir le datacenter et le serveur SQL Azure (à créer automatiquement ou déjà existant) :


  • Finalisation de la configuration de la base Azure.

Outre la création d’un utilisateur pour l’accès à la base Azure, une plage d’adresses IP utilisées par la base on-premise est à autoriser par le parefeu pour permettre la communication avec la base Azure :


  • Confirmation de la configuration de StretchDB.


  • Validation de l’approvisionnement de la StretchDB sur Azure.


Au sein de l’espace de travail Azure, on peut vérifier la bonne création de la StretchDB (avec un nom généré automatiquement, mais contenant au moins le nom de la base on-premise), dans la section SQL Databases :


Un clic sur la StretchDB cible permet d’avoir des informations (et actions) supplémentaires, comme ses informations de connexion :


Nous pouvons remarquer l’adresse complète du serveur Azure (stretchserver.xxx.mystretchdb-xxx…) permettant – entre autres – de se connecter à la base distante via SSMS :


A noter qu’en T-SQL, la syntaxe de configuration de notre base exemple en tant que stretch database est la suivante :

USE MyStretchDB
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD='<password>'
GO
USE& master
GO
ALTER DATABASE MyStretchDB
	SET REMOTE_DATA_ARCHIVE =
		ON(SERVER=<server_name>;
			,CREDENTIAL=<db_scoped_credential_name>)

GO

Avec diverses configurations d’accès à effectuer (firewall, authentification,…), par la suite. Plus de details ici: https://msdn.microsoft.com/en-us/library/mt163698.aspx.

Activation/désactivation du stretching pour une table spécifique

Si, au cours de la configuration de la stretch database, une table spécifique n’a pas été « stretchée », il suffit d’activer son stretch en T-SQL comme suit (exemple avec une table MyTable) :

USE MyStretchDB
GO
ALTER TABLE MyTable
	ENABLE REMOTE_DATA_ARCHIVE WITH ( MIGRATION_STATE = ON
)
GO

Pour une désactivation, il suffit de remplacer ENABLE par DISABLE.

Concernant SSMS, l’activation se fait comme ci-dessous :


La sélection de Disable au lieu d’Enable permettra de désactiver le stretch de la table cible.

On peut d’ailleurs noter que d’autres options existent : mise-en-pause de la migration automatique des données, migration annuelle des données…

Test de fonctionnement du stretching d’une table

La stretch table sur Azure, a pour particularité de contenir une colonne supplémentaire nomenclaturée comme suit : batchID-<object_id>, où <object_id> correspond à l’ID de la table appartenant à la table locale (on-premise). On peut d’ailleurs le vérifier en consultant le contenu de la stretch table (exercice qui permet, au passage, de constater que les données de la table on-premise ont bien été migrées vers la stretch table sur Azure) :

USE MyStretchDB
GO
ALTER TABLE MyTable
	ENABLE REMOTE_DATA_ARCHIVE 
		WITH (MIGRATION_STATE = ON)
GO


Après y avoir chargé quelques données supplémentaires sur la table on-premise

USE MyStretchDB
GO
INSERT INTO dbo.MyTable(FirstName, LastName, Age)
	VALUES ('Philippe', 'Zolt',46)
		  ,('François', 'Barbin',28)
		  ,('Jessica', 'Arst',21)
GO

… voici le contenu de la stretch table sur Azure :


Comme on peut le noter à juste titre, chaque ligne est rattachée à un identifiant correspondant à son batch d’origine.

La DMV permet d’avoir une traçabilité de la quantité de données migrées, avec quelques informations subsidiaires :

SELECT *
FROM sys.dm_db_rda_migration_status
WHERE migrated_rows>0
GO


Sauvegarde et restauration

Les opérations de sauvegarde et de restauration sur une base on-premise
« stretchée » peuvent être réalisées comme sur une base de données classique. Toutefois, il faut garder à l’esprit qu’un backup d’une base « stretchée » ne prend pas en compte les données migrées vers Azure.

Pour dépasser cette limitation, il suffit de réautoriser l’accès aux données de la base « stretchée », via les informations d’accès spécifiées durant la configuration du stretch. Et cela, en utilisant la procédure stockée système sys.sp_reauthorize_remote_data_archive :

EXEC sys.sp_reauthorize_remote_data_archive
	(@azure_username =N'<username>',
	 @azure_password =N'<'password>')
GO

Plus de details ici: https://msdn.microsoft.com/en-us/library/dn934993.aspx.

Principales limites

Toutes les tables ne peuvent être candidates au stretch. La liste ci-dessous (non-exhaustive et susceptible d’être mise-à-jour suivant les évolutions futures de StretchDB) listent quelques limitations :

  • Les ordres UPDATE et DELETE, ainsi que les opérations CREATE INDEX et ALTER INDEX.
  • Les tables répliquées, ainsi que celles optimisées en mémoire.
  • Les tables utilisant des fonctionnalités comme FILESTREAM, Change Tracking ou Change Data Capture.
  • Les tables ayant des types de données tels que hierarchyid, timestamp, sql_variant, XML, geography,… ainsi que des colonnes qui sont concernées par la fonctionnalité Always Encrypted.
  • Les tables contenant des contraintes CHECK, DEFAULT ou des clés étrangères.
  • Une variété d’indexes non-supportés : indexes columnstore en cluster, indexes spatiaux, indexes full-text, indexes XML,
    et les vues indexées référençant la ou les tables cibles.

Pour plus d’informations, vous pouvez faire un tour ici.

A noter qaprès, ue le Stretch Database Advisor permet d’identifier les bases de données et des tables d’une base de données pouvant être candidates au stretching vers Azure. Plus de détails dans ce billet d’Aaron Bertrand en guise d’exemple d’utilisation.

Pour aller plus loin…

Vous pouvez garder un œil ici, pour tout sujet relatif aux StretchDBs.

Publicités

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