Parallélisme SSIS par package

Comment prioriser et paralléliser l’exécution de packages d’un projet SSIS stocké dans le catalogue ? Dans cet article, nous allons détailler une méthode de parallélisme SSIS qui soit paramétrable pour contrôler les éléments suivant :

  • Ordonnancement d’alimentation par entrepôt de données
  • Ordonnancement des packages au sein d’un même entrepôt de données
  • Activation/Désactivation d’entrepôt(s) de données et de package(s)
  • Nombre de packages SSIS exécutables simultanément en mode synchrone

parallelism

1) Configuration du catalogue SSIS

Notre catalogue SSIS contient le dossier myFolder avec l’environnement DEV.

catalogue_ssis

Des variables liées à l’environnement DEV ont également été créées :

catalogue_ssis_variables

  • varCompletionPortThread : Nombre de threads en mode asynchrone autorisés dans un pool d’exécution (ex : opération I/O ans attente de process complet)
  • varWorkerThread : Nombre de threads en mode synchrone autorisés dans un pool d’exécution
  • varSSISCSCatalog : Chaine de connexion vers le catalogue SSIS
  • varSSISCSParam : Chaine de connexion vers la base de paramétrage pour le parallélisme de notre projet

2) Création d’une base de paramétrage

Dans un premier temps, nous allons créer une base de données dédiée au paramétrage, Param_SSIS, avec les objets suivants :

Table SETUP_PACKAGE_TYPE : liste des entrepôts de données à alimenter

  • Package_Type
    • STG : Données brutes et temporaires
    • ODS : Données historisées
    • DWH : Données transformées
  • Package_Type_Order : Ordre d’alimentation pour chaque entrepôts de données
    • L’alimentation des entrepôts de données se fait en cascade, STG alimente ODS et ODS alimente DWH avec potentiellement des transformations entre chaque zone de stockage
  • Package_Type_Enable : Entrepôt désactivé ou pas
    • Il n’est pas toujours nécessaire de recharger tous les entrepôts de données

Table SETUP_PACKAGE : liste des packages à exécuter

  • Package_Type : Entrepôt de données concerné
  • Package_Projet : Projet SSIS associé au package
  • Package_Order : Ordre d’exécution du package
    • Les packages qui seront exécutés simultanément (en fonction du nombre de threads maximum autorisé dans le pool) auront le même Package_Order
  • Package_Enable : Exécution désactivée ou pas
    • Ces packages ne sont pas exécutés pour effectuer un chargement partielle

Vue vw_SETUP_PACKAGES_ORDER : Pools d’exécution des packages à exécuter. Il est primordiale de définir la clause ORDER BY  [Package_Type_Order], [Package_Order] lors de l’appel à cette vue pour obtenir la liste des packages à exécuter comme nous le verrons plus tard.

Le résultat de la vue ordonnée : renvoie tous les pools d’exécution par entrepôt de données & packages

param_ssis_view

3) Création d’un projet SSIS nommé « SSISParallelism »

Les paramètres de projet à créer ont sensiblement les mêmes noms que les variables de l’environnement de DEV de notre catalogue SSIS (et les mêmes valeurs) pour des raisons pratiques afin de faciliter leur mapping par la suite.

ssis_project_param

3.1 Master package

Pour piloter l’exécution de plusieurs packages depuis SSIS, nous allons créer un master package dans le même projet que les packages enfant. A l’arrivée, il ressemblera à ça :

master_package

Deux chaines de connexion sont présentes :

  • 1 connexion de type ADO.NET qui pointe vers le catalogue SSIS. Ce type de connexion fournit une couche d’abstraction permettant d’accéder aux attributs de la connexion depuis une tâche de script (C#).
  • 1 connexion de type OLEDB qui pointe vers notre base Param_SSIS (OLEDB) qui stocke tout le paramétrage du parallélisme lié à notre projet SSIS.

Il y a d’autres différences entre une connexion de type ADO.NET et OLEDB telle que la manière de passer des paramètres d’entrée à une requête que nous verrons par la suite. A noter que ces connexions sont aussi mappées sur les paramètres de projet qui porte le même nom.

Pour la cuisine interne du master package, les variables à créer sont les suivantes :

2016-07-09 09_33_36-SSISParallelism (Running) - Microsoft Visual Studio (Administrator)

  • SSISFolder, SSISProject, SSISEnvironment : Ces variables stockent les informations de contexte d’une exécution. Si le le package Master est exécuté depuis le catalogue SSIS, la valeurs de ces variables seront déduites. Le cas échéant, si le package master est exécuté depuis Visual Studio, les variables conserveront les valeurs affectées par défaut. Les informations de contexte seront par la suite exploitées pour l’exécution des packages enfant.
  • PackagesPools : Contient la liste des pools d’exécution ordonnancés par entrepôt de données et par packages, sous la forme d’une variable de type Object. C’est le résultat de l’appel à la vue vw_SETUP_PACKAGES_ORDER avec la clause ORDER BY qui va bien.
  • PackagesList : Cette variable est alimentée dans une boucle Foreach Loop Container en parcourant la variable parente PackagesPools, résultat de l’itération. 1 pool contient la liste des packages enfant à exécuter ayant un ordonnancement identique pour un même entrepôt de données. 1  occurrence = 1 pool d’exécution

3.2 Tâche SQL : Récupération du contexte d’exécution du master package

On tente de déduire le contexte d’exécution du package master : Visual Studio ou Catalogue SSIS ? Dans le cas où le master package est exécuté depuis le catalogue SSIS, on récupère les informations de contexte qui nous intéressent en passant System::ServerExecutionID en paramètre à une requête qui interroge le catalogue. Le cas échéant, on cré artificiellement un recorset avec les valeurs par défaut des variables ci-dessous passées aussi en paramètre d’entrée de la requête :

  • FolderName
  • ProjectName
  • EnvironementName

tache_get_context

La requête qui interroge le catalogue :

Paramètres d’entrée de la requête :

parameter_in

Récupération de la sortie de la requête en respectant l’ordre des colonnes de la clause SELECT (Result Name = position de la colonne)

parameter_out

3.3 Tâche SQL : Récupération des pools d’exécution des packages enfants

On va cette fois interroger notre repository pour obtenir la liste des pools d’exécution des packages enfant à partir de la vue et on consigne le dataset dans une variable de type Object appelée User::PakagesPools.

tache_get_package_pool2

La requête qui interroge le repository :

Paramètre d’entrée de notre requête :

task2_param_in

Récupération de la sortie de la requête

task2_param_out

3.4 Création d’un conteneur de type For Each

On va itère la liste des pools de packages à exécuter ordonnancés par entrepôt et packages (1 pool = n packages à exécuter) en récupérant le résultat de la vue créée plus haut

for_each1

Récupération de la sortie ligne à ligne de l’objet PackagesPools

3.5 Création d’un conteneur For Each

for_each2

3.6 Création d’une tâche de script C# à l’intérieur du conteneur For Each

Cette tâche C# est chargée de l’exécution des packages enfants via l’itération des pools dans la boucle For Each. Le script prend en entrée les variables et paramètres de projet en lecture seule.

script_c

  • User::PakagesList (dataset ligne à ligne = 1 pool à traiter)
  • User::SSISEnvironment
  • User::SSISFolder
  • User::SSISProject
  • $Project::WorkerThreads : 10
  • $Project::CompletionPortThreads : 0

Nous allons également intégrer quelques assemblies supplémentaires dans notre script C# pour gérer l’accès au catalogue et exécuter un pool de packages enfant.

assemblies

Assembly Chemin
Integration Service C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.IntegrationServices.dll
SMO C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll
SFC C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Sdk.Sfc\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Sdk.Sfc.dll

Ce script C# contient entre autre :

  • 1 méthode qui vérifie les informations liées au catalogue
  • 1 méthode qui exécute nos packages enfants en vérifiant à intervalle régulier leur statut d’exécution : en cours, succès, échec
  • Notre connexion au catalogue SSIS de type ADO.NET ré exploitée par nos méthodes
    • Attention au nom de la connexion, en cas de changement il faudra le répercuter dans le script C#

  • Nos paramètres liés au parallélisme

  • Notre pool d’exécution à traiter PackageList, qui stocke une liste de packages enfant à exécuter avec un séparateur (,)

Voici le script en version complète :

Notre projet SSIS est enfin finalisé et prêt à être déployé dans le catalogue SSIS. Entre temps, nous y avons intégré nos packages enfant.

projet_ssis

3.7 Déploiement du projet SSIS dans le catalogue

deploy

4) Exécution depuis le catalogue SSIS

Il reste à compléter le configuration de notre projet SSIS au sein du catalogue en mappant nos paramètres de projet aux variables de l’environnement DEV.

exec

Le master package de notre projet est prêt à être exécuté !!!!

ex

Notre rapport d’exécution de notre projet SSIS :

report_ssis

Si nous avions exécuté notre master package depuis Visual Studio, voici la sortie que nous aurions obtenue :

2016-09-22-11_40_10-ssisparallelism-microsoft-visual-studio-administrator

L’intégralité du projet est disponible en téléchargement : SSISParallelism.zip

Cet article a fait l’objet d’une session à l’édition francophone du 24 heures PASS, elle sera prochainement disponible en ligne.

Merci à Sophie Bismuth consultante MS qui a initié le projet et y a largement contribué.

Et roule ma poule !

View post on imgur.com

Cortana Analytics

10 juin : DCube, la société qui m’emploie, m’a détachée une journée pour que je puisse assister à l’atelier Cortana Analytics chez MS animé par Emilie Beau, Romain Casterès et Yassine Khelifi.

Cortana Analytics, Kezako ?

C’est un ensemble de services dont la finalité est de faire de l’analyse prédictive depuis Azure, en d’autres termes d’identifier des patterns pour anticiper des comportements et des usages. Voici la catalogue de services qui composent la suite Cortana Analytics (non exhaustif), en bleu ceux que nous avons exploités pendant l’atelier :

  • Data Factory : ETL, Extract Transform and Load data
  • Data Catalog : Catalogue de métadonnées pour décrire des référentiels (Data Stewart)
  • Event Hub : Concentrateur d’événements
  • Data Lake : Traitement de données par job en U-Sql (langage hybride C#/TSQL) pour interroger des données (ex : fichiers) sur une architecture distribuée
  • SQL Data Warehouse : Base de données permettant le traitement de gros volumes de données structurées ou pas
  • HDInsight : Couche Microsoft par dessus Hadoop
  • Machine Learning : Analyse prédictive
  • Stream Analytics : Moteur de traitement d’événements en temps réel

Cortana Analytics en image :

2555.CAS

Hadoop pour rappel est une architecture distribuée open source développée en java permettant le stockage, le traitement et l’analyse prédictive de données en accord avec la règle des 3V :

  • Volume : données générées par des entreprises ou des personnes dont la croissance est exponentielle
  • Vélocité : vitesse de génération, de capture et d’analyse des données
  • Variété : format de données divers, structurée et non structurée

Première partie de l’atelier

On est accueillis avec le petit déjeuner, pas trop nombreux, on se présente à tour de rôle et on démarre le TP tranquillement. On nous fournit une solution Visual Studio toute faite à déployer avec un tutoriel pour paramétrer des services Azure. Pour résumer, on publie une application C# dans Cloud Service en mode Worker (tâche de fond) qui interroge en boucle une API JCDdecaux mettant à disposition un flux Json sur la disponibilité des Vélibs et on envoie les infos sous forme d’évènements à un récepteur EventHub. Ces évènements alimentent un rapport Power BI et une base Azure SQL Database en sortie via un job Stream Analytics.

Fin de la première mi-temps, plus concrètement le projet prend cette forme en fin de matinée :

2016-07-04 23_08_45-SQL Saturday Paris 2016 #510 - Velib & Cortana Intelligent Suite

Nous avons un rapport qui affiche les infos de disponibilités des Vélibs en temps réel par arrondissement, pour un emprunt ou une restitution. On pourrait s’arrêter là….

2016-07-05 21_21_14-Microsoft Power BI

Deuxième partie de l’atelier

La session reprend mais avec un nouvel intervenant, très discret jusqu’à maintenant, le Data Scientist, Yassine Khelifi, qui est en charge de l’analyse de « données massives » (Big data). Pour résumer, les premiers mots que Yassine a prononcé, ne sont ni papa, ni maman mais une formule mathématique. Le gars a aussi inventé le principe de la turbine pour les centrales nucléaires quand il était petit, avec son biberon ! Le temps d’apprendre à marcher pour se rendre à l’INPI, à peine arrivé au guichet, grosse déception, un autre type avait déposé le brevet avant lui.

Le but ultime de cet atelier n’est pas la BI traditionnelle basée sur des moyennes, des min. et max. depuis le cloud Azure, mais la prédiction. Et c’est là qu’intervient Machine Learning destiné à l’apprentissage automatique avec la mise en place d’algorithmes ! C’est dans le service du même nom dans Azure que les Data Scientist vont créer leurs modèles prédictifs et générer des prédictions avec le langage R, à partir de formules mathématiques.

Voici quelques informations issues de ce blog sur le Machine Learning : http://www.jeveuxetredatascientist.fr/quest-ce-que-le-machine-learning/

Le Machine Learning se décompose en 2 étapes : une phase d’entrainement (on apprend sur une partie des données) et une phase de vérification (on teste sur la seconde partie de données).

Nous aurons donc 3 phases : la Représentation / l’Évaluation / l’Optimisation. La phase de représentation consiste à trouver le modèle mathématique le plus adapté. Il existe un nombre important de modélisations. Enfin, l’évaluation mesure l’écart entre le modèle et la réalité des données de tests. L’optimisation vise à amenuiser cet écart.

Nous pouvons dénombrer 3 méthodes basiques :

  • Classification : modélisation de plusieurs groupes de données dans des classes existantes. Par exemple : la classification des cactus, la tendance d’un parti politique…
  • Clustering: ressemble à la classification mais ce ne sont pas des classes connues. Typiquement un système de recommandation de films.
  • Régression: les données sont liées à d’autres données numériques par une corrélation (une droite, une courbe, une tendance).

Machine Learning en pratique :

  • Voiture autonome de Google
  • Classification des emails dans gmail
  • Traduction en temps réel de Skype
  • Détection de fraude dans le monde de la banque
  • Reconnaissance faciale
  • Reconnaissance vocale Siri d’Apple

Machine Learning illustré :

Strip-Le-bot-qui-dratgue-650-final

Merci à Gillou pour cette illustration : http://www.commitstrip.com/fr/2016/07/05/bot-bot/

Une fois que le modèle prédictif est défini, on va secouer le tout avec Azure Data Factory : donc récupérer les données en entrée à partir de Azure SQL Database, appliquer le modèle prédictif en R sur ces données et enfin consommer le résultat/prédiction dans un rapport Power BI en sortie.

Fin de la seconde mi-temps, on en est là :

2016-07-05 13_43_12-SQL Saturday Paris 2016 #510 - Velib & Cortana Intelligent Suite

Mais qu’est-ce qu’on obtient à l’arrivée ? Sur la base d’un modèle prédictif, un rapport qui prédit la disponibilité des Vélibs dans chaque station, pour un emprunt ou une restitution. Bref, on n’affiche plus l’état des stations à un instant T, on le prédit.

2016-07-05 21_14_52-Vélib & Cortana Analytics – Deuxième partie _ Big Data France

Conclusion

L’atelier nous a donné une bonne vision d’ensemble des services rendus dans Azure sur la partie BI, en sachant que plus globalement la plateforme évolue très rapidement. C’est quand même bien pensé : tout est fait pour nous maintenir dans le cloud, un peu à l’image d’Office365. Vous n’avez plus à vous soucier de rien sauf du coût. Cela étant quand il s’agit de confidentialité des données, certaines entreprises ne peuvent pas avoir recours au cloud publique. La prise en charge du langage R OnPremise sur SQL Server 2016 est donc un atout supplémentaire. Au cours de cet atelier, on a été bousculé mais dans le bon sens du terme. La BI traditionnelle prend un sacré coup de vieux et la prédiction est un autre métier. En toute honnêteté, je serai incapable de commenter les modèles prédictifs que Yassine le Data Scientist nous a décrit (voir sources), un peu comme le jour où j’ai tenté d’expliquer la BI à mon mon chat, c’est peine perdue :

Sources

L’ensemble de la solution a été détaillé, y compris les analyses prédictives :

Il y a eu également une session au SQLSat 2016 le 25 juin animée par les mêmes acolytes, Romain Casterès et Yassine Khelifi, les slides sont disponibles à cette adresse :

Blog Data Scientist  :

http://www.jeveuxetredatascientist.fr/quest-ce-que-le-machine-learning/

G.O du club Cortana Analytics

power

Emilie Beau, c’est un des Power Rangers (techdays 2014), à vous de deviner lequel au jeu des différences… Toujours à la rescousse des plus faibles sur Twitter @BIwithEmilie.

picture-1185-1437412748

Romain Casterès, fan de Ti-punch et Frankie Vincent à ses heures perdues et régulièrement sur Twitter @PulsWeb.

Pas de dossier sur Yassine Khelifi (jamais une amende, pas une beuverie, une photo honteuse sur Facebook, rien)… MAIS vous pouvez le suivre sur Twitter @datashinobi.

BRAVO à tous les 3 !

Keyser Söze au SQLSat 2016

Le SQLSat a pris fin hier après un mois de labeur côté GUSS et oui ça s’organise ! Mais il y a bien heureusement les volontaires sur qui on peut compter, à savoir les étudiants de Supinfo mais aussi Grégory Boge, un ancien du board du GUSS, le même qui a développé Kankuru.

Cette année, je co-anime deux sessions suite à un bug sur le site du SQLSat vraisemblablement :

Accessoirement, si à l’époque où je débutais dans la data, on m’avait dit « un jour tu finiras en Sombrero, collier à fleurs, bilingue SQL à faire des blagues avec un Jedei de la requête », Arian est aussi un des fondateurs du GUSS… Bref, le SQLSat c’est aussi l’occasion de rencontrer d’autres DBA… des DBA Excel pour la BI (merci à mon pote Aiman Fajri d’être passé) et de partager nos expériences en mode détente. A l’arrivée on m’attribue le badge N°13 que je refuse net dans un premier temps « Ça porte malheur, j’en veux pas ! » avant de me raviser, car cette bévue j’en suis à l’origine dans la mesure où j’étais en charge de la création des badges du SQLSat.

La requête d’attribution des N° de badge :

Le résultat :

badge_sqlsat2016

L’événement démarre. Entre deux sessions, il y’a les copains SQL qu’on retrouve, comme Gilles Ducassou et puis les nouveaux, ou plus exactement ceux qu’on ne connait pas encore… Je ne sais plus qui m’a dit « Attend tu ne connais pas Nicolas Souquet ?!?! » et si on n’extrapole : « Si tu ne connais pas Nicolas Souquet, tu as raté ta vie de DBA ! ». Indépendamment du fait qu’il ait rédigé plusieurs articles assez pointus sur la partie moteur et participé à la rédaction de l’ouvrage SQL Server 2014 Développer et administrer pour la performance, le gars, c’est le Keyser Söze de la base de données. On a retrouvé sa trace en Thaïlande et après ça, plus rien… C’est un mythe !

Les sessions s’enchainent, parallèlement on peaufine les nôtres jusqu’à la dernière minute. Une des rares sessions à laquelle j’ai pu assister, pour ne pas dire la seule, est celle de David Barbarin, locks latches et spinlocks, le gros kif pour un DBA ! Les sessions manquées : celle de Chrissi Lemaire consacré à DBA Tool et celle de Romain Casteres sur Cortana.

Nous décidons de clôturer l’évènement dans un pub tout près, histoire de faire redescendre la pression, c’est le cas de le dire. Keyser Söze est dans la place… Quand tout ce petit monde se retrouve au distributeur avant d’atterrir au Financier, Greg, Romain, David, Gilles et les autres (on dirait le titre d’un film de Claude Sautet), je tente une blague douteuse. « Donner argent pour famille syrienne, si fou plaie ». Je l’ignore encore mais cette blague s’adresse à notre intéressé. On rit, on se présente et là je réalise « Ha mais c’est toi Nicolas ! ». On finit la soirée dans un restaurant réunionnais, 96 Rue Daguerre, 75014 Paris, à conclure entre autres que la plupart des DBA sont bien des gens méchants qui appartiennent au moins à l’une de ces catégories :

Voilou, c’est aussi ça les SQLSat, vivement l’année prochaine ! Entre les deux, il y a également les JSS 2016…

usual_suspect

PS : http://retirezmoiphotoshop.tumblr.com/

Générer un jeu de données aléatoire

Pour des tests, il peut y avoir la nécessité d’inclure un jeu de données factices. La requête ci-dessous permet de générer une table avec différents types de données générées aléatoirement :

  • INT
  • TINYINT dans un intervalle
  • BIT
  • VARCHAR
  • DATETIME
  • DATETIME dans un intervalle
  • NUMERIC

Vous l’aurez compris, le TOP permet d’interagir sur le nombre de lignes à renvoyer.

Purge le fichier de transaction d’une base de données en miroir

Un problème reçurent est la croissance non maîtrisé du fichier de transaction sur une base en miroir quand il n’est pas ou peu sauvegardé. Une mesure d’urgence consiste à sauvegarder le fichier de transaction (dans le vide, d’où l’emplacement à NULL) jusqu’à ce que le nombre de fichiers journaux virtuels (VLF) actifs soit réduit à néant, donc à 0. Une fois cette opération réalisée,  la réduction du fichier de transaction sera possible.

Mais rendons à César ce qui est à César, voir cet excellent article dont je me suis largement inspirée si vous voulez avoir plus de détails sur le fichier de transaction et les VLF :

http://blog.developpez.com/mikedavem/p8044/sql-server-2005/architecture/pourquoi_la_commande_shrinkfile_ne_redui

Parallélisme en Powershell

L’industrialisation et la performance sont deux sujets qui reviennent souvent dans l’informatique.

Voici la base d’un script Powershell permettant de paralléliser l’exécution d’une tâche sur plusieurs machines. Le principe est le suivant, il y a 3 étapes à retenir :

  • On indique le nombre de tâches maximum en parallèle :
  • On parcourt le fichier computer.txt dans lequel on a consigné des noms de serveurs, ligne à ligne. Ça pourrait tout aussi bien être un DataTable.
  • Sur chacun de ces serveurs, on exécute une commande lambda. On aurait pu exécuter un autre script Powershell en cascade.

Voici le script complet qui permet de gérer le parallélisme.

 

Réplication – Initialisation par sauvegarde/restauration d’un abonné

Dans cet article, nous allons créer une publication transactionnelle et initialiser un abonné à partir de la sauvegarde d’un autre abonné. Cette méthode est appropriée dans les cas suivants :

  • N bases abonnées, strictement identiques au niveau structure
  • Personnalisations importantes des bases abonnées (ex : index)

Attention : cette méthode implique de suspendre toutes les synchronisations au même moment pendant toute la durée de l’initialisation d’un abonné. L’objectif étant de partir d’une base abonnée susceptible de contenir des personnalisations importantes (ex : index, vues indexées) et d’industrialiser la création/initialisation d’autres abonnés à partir de ce modèle.

NB : Dans cet exemple, nous partons du principe que le distributeur est déjà configuré. Dans tous les scripts, nous avons recours à des paramètres de template pour faciliter le remplacement de valeurs telles que le nom de la base publiée, etc.

param

1) Création d’une BDD à répliquer

En premier lieu, sur l’instance A, nous allons créer une BDD lambda avec une table contenant un jeu de données générées aléatoirement :

2) Activer la publication sur la base à publier

Activer la publication de la BDD créée précédemment, comme suit :

3) Créer une publication transactionnelle

Créer une réplication transactionnelle sur la BDD lambda, en spécifiant le nom de la publication.

4) Création de l’agent de capture de la publication

Création d’un agent de snapshot sur la publication créée à l’étape d’avant :

5) Ajout d’un article

Ajout d’un article dans la publication, en l’occurrence la table créée précédemment :

6) Création de la base de données, futur abonné

On se connecte sur l’instance qui hébergera une copie de la base de données publiée qui deviendra abonnée par la suite.

7) Création de l’abonné 1

Une fois la base de données secondaire créée, nous passons à la création de l’abonnement avec les options par défaut.

8) Initialisation de l’abonné 1

8) Vérification de la synchronisation de l’abonné 1

Juste pour la forme, on vérifie que la base abonnée a bien été synchronisée en comparant le nombre de lignes présente de chaque côté, respectivement sur la base de données publiée et la abse de données abonnée :

8) Arrêt de l’agent de lecture

On se connecte sur l’instance qui héberge la distribution et on stoppe purement et simplement l’agent de lecture, qui correspond à un  job dont le nom est récupéré dynamiquement.

9) Vérification de la synchronisation de l’abonné 1

A intervalle régulier, on vérifie que la liste des transactions en attente de réplication, ce qui correspond à la colonne Undelivcmdsindistdb. Il faut qu’elle soit à 0 pour tous les abonnés de la publication concernée (1 abonné par ligne). Dans l’exemple  suivant, il n’y a qu’un abonné mais dans l’absolu, il pourrait y en avoir plusieurs.

10) Sauvegarde de l’abonné 1

Il n’y a plus aucune transaction en attente de réplication, tout abonnés confondu s’il y en a plusieurs. Nous sauvegardons l’abonné pour la création et l’initialisation d’une autre base abonnée.

11) Restauration sur une future base abonné 2

Une fois la sauvegarde effectuée, nous procédons à la création/restauration d’une futur base de données abonnée, en spécifiant l’option KEEP_REPLICATION. Elle permet de conserver les objets tels que les PS liées à la réplication lors d’une restauration, typiquement :

  • sp_MSins_dboRandomData
  • sp_MSdel_dboRandomData
  • sp_MSupd_dboRandomData

12) Création de l’abonné 2

Une fois la base de données 3 restaurée, nous passons à la création de l’abonnement en spécifiant que nous ne souhaitons pas de synchronisation avec l’option @sync_type = ‘none’.

Les étapes 11 et 12 seront renouvellées autant de fois que d’abonnés supplémentaires à créer. Pour rappel, cela implique de suspendre la réplication pour la totalité des abonnés déjà présents. Si la réplication a été réactivée entre temps et qu’on souhaite ajouter d’autres abonnés par la suite, dans ce cas, il faudra repartir de l’étape 8 puis répéter les étapes 11 & 12 à hauteur du nombres d’abonnés à ajouter.

13) Injection de nouvelles données sur la base de données publiée

14) Démarrage de l’agent de lecture

On se connecte sur l’instance qui héberge la distribution et on démarre l’agent de lecture stoppé précédemment.

15) Vérification de la synchronisation des abonnés 1 & 2

A intervalle régulier, on vérifie que toutes les bases de données abonnées ont bien été synchronisées en comparant le nombre de lignes présentes de chaque côté :

DELETE patterns

Problématique récurrente chez les développeurs :

On a une table qui contient 500 000 000 000 (…) lignes sur laquelle on doit impérativement lancer une purge. Comment on procède pour ne pas pénaliser la PROD ?

Les questions à se poser :

  • Est-ce que la volumétrie à conserver est supérieur à la volumétrie à purger ?

delete1

  • Est-ce qu’il y a des dépendances ?

dependencies

  • Est-ce que l’indexation est suffisamment couvrante ?

index

Création d’une base test

En premier lieu, on cré une base lambda contenant 2 tables, dont une dans laquelle on injecte des données générées aléatoirement, celle à purger.

Options de la purge

Nous utiliserons 3 patterns permettant de gérer les éléments suivant :

  1. Appliquer une rétention (@RetentionDays d’où découle @MaxDateToDelete)
  2. Limiter le traitement dans le temps (@MaxDurationMinutes d’où découle @MaxDurationTime)
  3. Supprimer les données par paquet pour limiter les effets du locking (@DeletePackageSize)
  4. Faire une pause entre chaque paquet de données à supprimer dans le même esprit que le point précédent (WAITFOR DELAY)
  5. Recourir à la commande SWITCH en fonction du volume de données à purger

Pattern 1

La volumétrie à conserver est supérieur au volume de données à purger et il n’y a aucune dépendance (FK) liée à la table à nettoyer

Pattern 2

La volumétrie à conserver est supérieur au volume de données à purger et il y a des dépendances (FK) liée à la table à nettoyer. On va donc passer par une table temporaire pour isoler les données à purger et les matcher avec les dépendances de la table principale. La création de la base n’inclue pas ces dépendances (RandomDataDepend1, RandomDataDepend2, RandomDataDepend3), on va donc faire comme si elles existaient…

Pattern 3

La volumétrie à conserver est inférieur au volume de données à purger et il n’y a pas de dépendances (FK) liée à la table à nettoyer. On aura recours à un SWITCH qui contrairement à une idée reçue n’est pas réservé aux tables partitionnée. Le principe est de transférer la totalité des données de la table principale vers une autre et de procéder à un INSERT des données à conserver dans la table principale. Le but étant de diminuer le volume de données à traiter et donc la durée de traitement.

Attention : La structure des tables source et destination doit être strictement identique et la destination doit être vide.

L’option INSERT_IDENTITY est utilisée dans le cas présent pour forcer l’insertion dans une colonne IDENTITY.

Une réindexation pourra être lancée juste après la purge.

Pour conclure, cette logique de suppression peut aussi s’appliquer pour une mise à jour en masse.

Réplication – Initialisation par sauvegarde avec édition de la réplication

Dans cet article, nous allons créer une publication transactionnelle et initialiser un abonné à partir d’une sauvegarde. Cette méthode est appropriée dans les cas suivants :

  • Structure identique sur les bases publiée et abonnées
  • Volumétrie à répliquer importante

Pour finir, nous verrons comment rendre cette réplication éditable car la prise en compte de nouveaux articles, entre autres, n’est pas native sur l’abonné lorsqu’il a été initialisé par sauvegarde..

NB : Dans cet exemple, nous partons du principe que le distributeur est déjà configuré. Dans tous les scripts, nous avons recours à des paramètres de template pour faciliter le remplacement de valeurs telles que le nom de la base publiée, etc.

parameter

1) Création d’une BDD à répliquer

En premier lieu, sur l’instance A, nous allons créer une BDD lambda avec une deux tables contenant chacune un jeu de données générées aléatoirement :

2) Activer la publication sur la base à publier

Activer la publication de la BDD créée précédemment, comme suit :

3) Créer une publication transactionnelle

Créer une réplication transactionnelle sur la BDD lambda, en spécifiant le nom de la publication.

Notez que l’option @allow_initialize_from_backup est à true.

NB : Pour une publication déjà existante, on pourra modifier la publication comme suit après coup :

4) Création de l’agent de capture de la publication

Création d’un agent de snapshot sur la publication créée à l’étape d’avant :

5) Ajout d’un article

Ajout d’un article dans la publication :

6) Désactiver la purge des transactions sur le distributeur

Sur l’instance de distribution, désactiver le job de purge des transactions de la réplication. Le nom du job varie suivant le nom de la base de distribution utilisée par la publication.

7) Sauvegarde de la BDD publiée

Une fois la réplication configurée, lancer une sauvegarde de la base publiée :

8) Restauration sur l’abonné

Sur l’instance B, celle qui héberge la futur base abonnée, lancer une restauration à partir de la sauvegarde créée précédemment :

9) Création de l’abonné

Lors de la création de l’abonné, il y a deux paramètres à retenir dans la création de l’abonné :

  • @sync_type = initialize with backup
  • @backupdevicename = chemin de la sauvegarde utilisée précédemment

NB : On peut très bien s’appuyer sur un plan de sauvegarde existant incluant les logs pour l’initialisation de l’abonné (by-pass de l’étape de sauvegarde complète) mais la dernière sauvegarde de logs à restaurer sur l’abonné doit être la plus récente possible. Le cas échéant, au moment de l’initialisation de l’abonné, on obtiendra le message d’erreur suivant :

Retry the operation again with a more up-to-date log, differential, or full database backup

10) Réactiver la purge des transactions sur le distributeur

Sur l’instance de distribution, réactiver le job de purge des transactions de la réplication. Comme indiqué précédemment, le nom du job varie suivant le nom de la base de distribution utilisée par la publication.

11) Vérification de la réplication

Sur la base publiée, on relève le nombre de lignes sur l’article répliqué :

Qu’on compare avec la base abonnée :

!!! FIN !!! Enfin presque…

Mais qu’en est-il lorsqu’on ajoute un article dans la publication par la suite, après une initialisation par sauvegarde ? RIEN, Il ne se passe rien, aucune données ne remonte sur l’abonné. Il va falloir modifier quelques paramètres pour rendre la synchronisation de l’abonné fonctionnelle.

12) Propriétés de la publication

Nous allons donc modifier 3 propriétés sur la publication :

  • allow_anonymous = false, cette option est indissociable de celle qui nous intéresse, voir ci-dessous
  • immediate_sync = false, cette option permet de passer en mode asynchrone pour générer un snapshot différentiel  (remontée de l’article ajouté sur l’abonné)
  • allow_initialize_from_backup = false, cette option permet de revenir à une initialisation par snapshot de l’abonné

13) Synchronisation automatique de l’abonné

La petite touche « Brico Dépôt » : la synchronisation de l’abonné devra être modifiée en réalisant un UPDATE dans une table système liée à la réplication. Cette mise à jour consiste à rendre la synchronisation de l’abonné automatique, comme suit :

La publication et l’abonné sont désormais prêt à recevoir de nouveaux articles !

14) Ajout d’un article

L’ajout d’article ne diffère en rien (table RandomData2), on procède comme à l’accoutumée avec un refresh de l’abonné et la génération d’un snapshot différentiel en prime :

15) Vérification de la réplication

Sur la base publiée, on relève le nombre de lignes sur l’article répliqué en seconde noce :

Qu’on compare avec la base abonnée :

Optimiseur de requête

L’optimiseur de requête est le composant en charge de trouver le moyen le plus efficace pour exécuter une requête. Lors de ce webcast, nous allons décortiquer le processus d’optimisation et évoquer le nouvel estimateur de cardinalités sous SQL Server 2014 au travers d’exemples.