Voici le deuxième article consacré à la mise en place d’une solution SQL Server 2014 AlwayON Availability Group dans Azure. Les articles sont découpés selon le plan suivant :
Ce billet va traiter de la deuxième partie, “Création de l’infrastructure virtuelle”. Pour rappel, à la fin de cette première série d’articles, nous aurons l’architecture suivante :
Depuis votre portail Azure, connectez-vous à la machine virtuelle du domaine contrôleur via le bureau à distance.
Sur le bandeau de gauche, cliquez sur « Machines virtuelles ».
Sélectionnez la machine virtuelle qui porte votre domaine contôleur.
En bas de la fenêtre, cliquez sur « Connecter ».
Dans le « pop-up » qui apparaît, cliquez sur le bouton « Open ».
Une fois dans la machine virtuelle, allez dans « Server Manager ». En haut à droite, cliquez sur « Manage » puis « Add Roles and Features ».
Passez les étapes jusqu’à la rubrique « Server Roles ». Cochez la case « Active Directory Domain Services ».
Cliquez sur « Next » et allez jusqu’à la fin de l’assistant.
Une fois l’installation terminée, toujours dans le « Server Manager », en haut à droite, notez-le panneau jaune d’avertissement. Cliquez dessus et cliquez sur « Promote this server to a domain controller ».
Dans la fenêtre « Active Directory Domain Services Configuration Wizard », sélectionnez « Add a new forest ». Renseignez le champ « Root domain name ».
Cliquez sur « Next ».
Définissez le mot de passe pour le mode de restauration.
Définissez le nom NetBIOS.
Cliquez sur « Install ».
Redémarrez la machine virtuelle.
Une fois le domaine contrôleur redémarré, connectez-vous avec le compte de domaine. Dans le « Server Manager », cliquez sur le menu « Tool », puis sur « Active Directory Users and Computers ».
Dans la fenêtre « Active Directory Users and Computers », dans la partie gauche, faîtes un clic droit sur le dossier « Users ». Sélectionnez « New » puis « User ».
Définissez un compte pour réaliser les installations. Cliquez sur « Next »
Définissez le mot de passe pour le compte d’installation. Décochez la case « User must change password at next logon » et cochez la case « Password never expires ».
Créez de la même manière les comptes pour les services SQL (dans mon cas j’ai créé SQL1, SQL2, SQL3 et SQL4).
Donnez les permissions au compte d’installation (Install).
Dans la fenêtre « Active Directory Users and Computers », cliquez sur le menu « View » puis sur « Advanced Features » .
Toujours dans la fenêtre « Active Directory Users and Computers », faîtes un clic droit sur votre domaine (ici : franmer.demo.com) et sélectionnez « Properties ».
Dans la fenêtre « Properties », cliquez sur l’onglet « security » et cliquez sur le bouton « Advanced ».
Dans la fenêtre « Advanced Security Settings for… » , cliquez sur le bouton « Add ».
Dans la fenêtre « Permission Entry for… », Cliquez sur « Select Principal ».
Définissez votre compte d’installation. Dans mon cas c’est le compte « Franmer\Install ».
Une fois de retour dans la fenêtre « Permission Entry for… », cochez les cases « Allow for Read all properties » et « Create Computer objects ».
Cliquez sur « Ok » plusieurs fois pour fermer toutes les fenêtres.
Depuis le portail Azure, dans le bandeau de gauche, cliquez sur « Réseau », puis sélectionnez votre réseau virtuel.
Après vois sélectionné votre réseau virtuel, cliquez sur l’onglet « Configurer ».
Entrez un nom pour votre DNS et renseignez l’adresse IP de votre DNS. Dans mon cas c’est l’adresse 10.0.1.4.
En bas de l’écran cliquez sur le bouton « Enregistrer ».
Après avoir cliqué sur « Enregistrer » un message d’information doit vous avertir d’une opération en cours.
Redémarrez vos machines, excepté le domaine contrôleur.
Connectez-vous sur une machine virtuelle, que vous souhaitez joindre au domaine, via le bureau à distance. En bas de l’écran cliquez sur le bouton « Connecter ».
Une fois connecté, dans la console de management du server, dans la partie gauche, cliquez sur « Local Server ».
Dans la partie centrale, cliquez sur le lien « Workgroup ».
Dans la fenêtre « System Properties », cliquez sur le bouton « Change ».
Dans la partie « Member of », sélectionnez « Domain » puis renseigner le nom FQDN de votre domaine.
Cliquez sur le bouton « Ok ».
Vous devez normalement avoir un message de bienvenue dans votre domaine.
Une autre fenêtre vous demande de redémarrer. Cliquez sur « Restart Later ».
La console de management de votre serveur a dû normalement se mettre à jour.
Maintenant que vos serveurs SQL ont rejoints le domaine, nous allons créer un groupe de sécurité. Ce groupe nous permettra par la suite d’appliquer sur l’ensemble des membres de ce groupe des « policies » telles que des règles de firewall.
A partir du domaine contrôleur, depuis le « Server Manager », cliquez sur « Tools », « Active Directory Users and Computers ».
Déroulez le dossier de votre domaine et faîtes un clic droit sur le dossier « Users », et cliquez sur « New » puis sur « Group »
Dans la fenêtre « New Object – Group », donnez un nom à votre groupe et sélectionnez « Universal » dans le « Group scope ». Laissez le « Group Type » sur « Security ».
Cliquez sur « Ok ».
Votre groupe de sécurité est créé.
Faîtes un double clic sur votre groupe de sécurité.
Cliquez sur l’onglet « Members ».
Rajoutez vos serveurs SQL en utilisant le bouton « Add ».
Entrez le nom de vos machines et cliquez sur le bouton « Check Names ».
Il se peut que vos serveurs ne soient pas trouvés du premier coup.
Dans la fenêtre « Name Not Found », Cliquez sur le bouton « Object Types ».
Et cochez la case « Computeurs ».
Une fois tous vos serveurs rajoutés, cliquez sur « Ok ».
Toujours sur le domaine contrôleur, depuis le « Server Manager ». Cliquez sur « Tools » puis sur « Group Policy Management ».
Une fois dans la console « Group Policy Management », dans la partie gauche, faîtes un clic droit sur votre nom de domaine.
Dans le menu contextuel, cliquez sur « Create a GPO in this domaine, and Link it here… ».
Dans la fenêtre « New GPO », donnez un nom à votre GPO.
Votre règle apparaît dans la partie gauche de la console. Faîtes un clic droit dessus et sélectionnez « Edit… ».
La fenêtre « Group Policy Management Editor » s’ouvre.
Déroulez les dossiers jusqu’à arriver au niveau « Indound Rules ».
Faîtes un clic droit sur « Indound Rules », puis sélectionnez « New Rule… »
Dans la fenêtre « New Inboud Rule Wizard », laissez le bouton radio sur « Program ».
Dans le champ « This program path », entrez le chemin vers le fichier exécutable de SQL Server. Par défaut il se trouve :
%ProgramFiles%\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\sqlservr.exe.
Laissez sur « Allow the connection ».
Donnez un nom à votre règle. Cliquez sur « Finish ».
Répétez l’opération pour créer 2 nouvelles règles :
Dans mon exemple, ça sera respectivement les ports 55569 et 59999.
Dans la fenêtre « New Inbound Rule Wizard », sélectionnez « Port ».
Dans la partie « Protocol and Ports », laissez « TCP » puis dans le champ « Specific local ports », entrez le « futur » port de votre listener.
Dans mon exemple, ça sera le port 55569. Cliquez sur « Next ».
Laissez sur « Allow the connection » et cliquez sur « Next ».
Laissez les 3 cases cochées et cliquez sur « Next ».
Recommencez l’opération pour rajouter le port de la sonde du « Load balancer » (59999 dans mon exemple). Ensuite vous pourrez rajouter d’autres règles ici pour les propager sur vos serveurs SQL.
Une fois toutes les règles de Firewall rajoutées, fermez la fenêtre du « Group Policy Management Editor ».
Nous allons maintenant rajouter notre groupe de serveurs SQL à la « policy ».
Dans la fenêtre « Group Policy Management », dans la partie Centrale, en bas, cliquez sur le bouton « Add ».
Dans la fenêtre « Select User, Computer or Group », entrez le nom de votre group de sécurité et cliquez sur « Check Names ».
Cliquez sur « OK ».
Vous devez donc vous retrouvez avec 2 groupes de sécurités. Cliquez sur le groupe « Authenticated Users ». Cliquez ensuite sur le bouton « Remove ».
Cliquez « Ok » lors de l’avertissement :
Voilà ! Notre Groupe de « policy » est terminée.
Redémarrez vos serveur SQL afin qu’ils héritent des modifications que l’on vient de faire.
Après redémarrage, le Firewall de chacune des machines SQL a été mis à jour :
Sur les machines SQL Server, donner les droits pour le compte « Install ». Pour ce faire, depuis l’interface Modern UI, commencez à taper « Give administrative… ». Dans les suggestions, apparaît « Give administrative rights to a domain user ». Cliquez dessus.
Dans la fenêtre « User Accounts », cliquez sur le bouton « Add… ».
Rajoutez le compte d’installation. Ci-dessous une copie d’écran de mon installation :
Donnez le droit « Administrator » à ce compte. Cliquez sur « Next ».
Dans la dernière fenêtre, cliquez sur « Finish ».
Vous devez voir apparaître le nouveau compte dans la partie « Users for this computer ».
Répétez l’opération sur toutes les machines SQL.
Dans le prochain article, nous aborderons la création du Failover Cluster.
Franck Mercier
Pour tester Windows Server 2012 R2, Windows 8, SQL Server 2014 et Power BI, vous pouvez télécharger gratuitement une version d’évaluation :
Windows Server 2012 R2:
SQL Server 2014 :
Power BI :
Testez Azure gratuitement pendant un mois :
Avec Azure, il est devenu assez simple et rapide de montrer des environnements virtuels opérationnels et évolutifs en fonction des besoins. Jusqu’à maintenant, pour construire mes démonstrations AlwaysON, j’étais un peu à l’étroit sur mon Lenovo W530, même si il était équipé de 2 disques durs SSD et de 32 Go de mémoire vive. L’intérêt du IaaS Azure (IaaS : Infrastructure as a Service), est de pouvoir monter rapidement un environnement virtuel, dans lequel je vais pouvoir déployer toute une infrastructure AlwaysON. De plus, cette infrastructure sera disponible de n’importe où, de n’importe quelle machine et je pourrais la faire évoluer en fonction des besoins.
Dans cette série d’articles, nous allons voir comment mettre en place une infrastructure complète SQL AlwaysON dans Azure. Puis une fois cette infrastructure opérationnelle, nous l’étendrons vers une autre région Azure afin de paramétrer une solution de « disaster recovery ». Ci-dessous une représentation de notre infrastructure cible.
La première série d’articles va se focaliser sur la mise en place du site principal. Nous allons donc monter une infrastructure SQL avec un groupe de disponibilité sur 4 serveurs.
Nous allons donc configurer l’infrastructure suivante :
Cet article et les suivants vont traiter des points ci-dessous :
Connectez-vous sur votre portail Azure : http://manage.windowsazure.com.
En bas à gauche de l’écran cliquez sur le bouton « + Nouveau».
Sélectionnez « Service de réseau », « Réseau virtuel », « Création personnalisé ».
Dans la fenêtre du réseau virtuel, renseignez le nom du réseau et son emplacement. Cliquez sur la flèche de droite.
Pour le moment, laissez les champs du DNS vides, on y reviendra plus tard. Cliquez sur la flèche de droite.
Dans la partie adresse, renseignez les adresses que vous souhaitez réserver pour votre réseau et sous-réseaux.
Ci-dessous un exemple que j’ai utilisé pour cette infrastructure. J’ai créé 2 sous réseaux, le sous-réseau « Back » et le sous-réseau « Front ».
Cliquez sur le bouton .
Nous allons maintenant créer un compte de stockage qui contiendra les conteneurs des fichiers vhd de nos machines virtuelles.
En bas à gauche de l’écran, cliquez sur le bouton « + Nouveau ». Puis sélectionnez « Services de données », « Stockage » et « création rapide ».
Renseignez les informations de votre compte de stockage.
Création d’un service de Cloud qui « hébergera » nos machines virtuelles.
En bas à gauche de l’écran, cliquez sur le bouton « + Nouveau ». Puis sélèctionnez « Calcul », « Service de Cloud computing », « Création personnalisé ».
Dans la fenêtre « Créer un service de cloud computing », renseignez les informations de service Cloud. Au niveau de la région, sélectionnez une région qui sera la même pour votre compte de stockage et les machines virtuelles de votre site principal.
Ci-dessous, une copie d’écran de ma configuration. Sur cette copie d’écran, vous pouvez voir que j’ai utilisé un groupe d’affinité. Ce groupe d’affinité est sur l’Europe du Nord, et j’utiliserai cette région pour créer mon compte de stockage et les machines virtuelles de mon site principal.
En bas à gauche de l’écran, cliquez sur le bouton « + Nouveau ». Sélectionnez « Calcul », « Machine Virtuelle », « A partir de la galerie ».
Sélectionnez l’image « Windows Server 2012 R2 Datacenter ».
Renseignez les informations de votre domaine contrôleur. En termes de taille nous allons choisir une machine A1, ce qui sera suffisant pour notre exemple.
Cliquez sur la flèche droite.
Dans la liste déroulante « Service de Cloud Computing », Sélectionnez le service de Cloud computing que vous avez créez précédemment..
Dans le liste « Région/Groupe d’affinité/Réseau virtuel », sélectionnez le réseau virtuel créé précédemment.
Dans le liste « Sous-réseaux du réseau virtuel » sélectionnez un de vos sous-réseaux. Dans cet exemple j’ai sélectionné le réseau « Back ».
Dans la liste « Compte de stockage », sélectionnez le compte de stockage créé précédemment, Il servira à hébergé les fichiers vhd des machines virtuelles.
Cliquez sur la flèche droite .
Cliquez sur la marque de validation .
Nous allons créer une machine qui va servir de votant dans le cluster. (Même si on peut s’en passer ).
Dans la liste déroulante « Taille », sélectionnez A1 Cliquez sur la flèche droite .
Dans la liste déroulante « Service de Cloud Computing », Sélectionnez le service de Cloud computing que vous avez créez précédemment.
Sélectionnez l’image « SQL server 2014 RTM Enterprise ».
Dans la liste déroulante « Taille », sélectionnez A3 Cliquez sur la flèche droite .
Dans la liste « Groupe à haute disponibilité », créez un groupe de haute disponibilité. Ce groupe servira pour toutes les machines SQL.
Répétez l’opération pour créer les autres serveurs SQL. Pensez à bien sélectionnez le groupe à haute disponibilité pour toutes vos machines SQL.
Dans le prochain article, nous aborderons la création de l’infrastructure virtuelle.
Avec Sébastien Pertus, nous avons réalisé une vidéo présentant la création d’une base de données dont les fichiers se trouvent dans un conteneur Microsoft Azure. Cette vidéo est la septième et dernière d’une série dont les épisodes précédents sont disponibles via les liens suivants:
Windows Server 2012 :
Avec Sébastien Pertus, nous avons réalisé une vidéo présentant le déploiement d’une base de données dans une machine virtuelle Microsoft Azure. Cette vidéo est la sixième d’une série dont les épisodes précédents sont disponibles via les liens suivants:
Pour tester Windows Server 2012, Windows 8, SQL Server 2012, SQL Server 2014 CTP2 et Power BI, vous pouvez télécharger gratuitement la version d’évaluation disponible sous la forme :
SQL Server 2012 :
Evaluation SQL Server 2014 CTP2 :
Evaluation Power BI :
Avec Sébastien Pertus, nous avons réalisé une vidéo présentant les nouveautés au niveau des sauvegardes, et plus particulièrement la possibilité de sauvegarder ses bases dans un stockage Microsoft Azure. Cette vidéo est la cinquième d’une série dont les épisodes précédents sont disponibles via les liens suivants:
Avec Sébastien Pertus, nous avons réalisé une vidéo présentant les nouveautés au niveau du chiffrement des sauvegardes dans SQL Server 2014. Cette vidéo est la quatrième d’une série dont les épisodes précédents sont disponibles via les liens suivants:
Avec Sébastien Pertus, nous avons réalisé une vidéo présentant les nouveautés du “Resource Governor” de SQL Server 2014. Cette vidéo est la troisième d’une série dont les épisodes précédents sont disponibles via les liens suivants:
Avec Sébastien Pertus, nous avons réalisé une vidéo présentant le Clustered ColumnStore Index disponible dans SQL Server 2014.
Cette vidéo est la deuxième d’une série dont la première est disponible ici
Au plaisir de vous voir lors d’un IT Camp SQL 2014/Power BI.
Une à mise à jour de Power Query est disponible. Elle est disponible ici :
Voici les nouveautés apportées par cette nouvelle version.
Le connecteur aux univers SAP BO, déjà disponible en preview, est maintenant disponible dans Power Query en version “General availability”. Cette nouvelle version offrira en plus la possibilité de rajouter ou de supprimer des éléments directement depuis l’éditeur de requête :
Cette nouvelle version supporte maintenant les plages nommées ainsi que les noms des tableaux lors de l’import des données depuis un fichier Excel
les plages nommées sont disponibles dans le volet de navigation de Power Query :
Cette version de Power Query propose la possibilité de supprimer les dernières lignes d’un résultat de données. Dans les versions précédentes, cela étaient possible via le language M et l’écriture d’une fonction personnalisée. Maintenant, cette transformation est native dans Power Query.
Une nouvelle transformation permet de remplir les cases vides d’une colonne en partant des valeurs du bas du tableau. cette transformation est aussi disponible via la formule “Table.FillUp”
Après transformation “Fill Up”
Dans le menu “Insert”, une nouvelle entrée “Statistiques” est disponible. Il est maintenant possible d’appliquer des calculs directement à partir de ce menu. Cette fonctionnalité sera enrichie dans les prochains mois.
Maintenant, lors de la mise à jour des données, les anciens résultats sont conservés lorsqu’une mise à jour de la requête échoue.
Dans les options de Power Query, il est maintenant possible de paramétrer la destination de chargement par défaut des résultats d’une requête :
Lorsqu’une requête à destination d’une feuille de calcul remonte un nombre de ligne au delà de la limite supportée par Excel (1 048 576 lignes et 16 384 colonnes), un message explicite apparaît en conseillant d’utiliser le modèle de données d’Excel (Power Pivot)
Le message donne directement accès à l’option de charger les résultats dans le model de données :
Power Query conserve les données chargées dans le model, même après modification du paramètre “Load to Worksheet”. Avant cette mise à jour, Power Query remettait à zéro les résultats aussi bien dans la feuille de calcul que dans le model lorsque les options “Load settings” étaient modifiées..
Il est maintenant possible d’organiser les requêtes directement à partir du volet “Workbook Queries” par un simple click droit.
Pendant la mise à jour d’une requête, il est maintenant possible d’annuler la mise à jour soit à partir du ruban ou en cliquant en bas à droite de la fenêtre Power Query.
Le volet d’étapes supporte maintenant la possibilité d’utiliser les flèches pour naviguer dans les différentes étapes de la transformation. De plus, la touche “F2” est maintenant utilisable pour renommer les étapes.
Lors d’une sélection multiple, les éléments sélectionnés sont visibles dans le champ “Selected items”. Il est maintenant possible, avec un click droit, de retirer un élément de cette liste.
“Hekaton” est le nom de code de la technologie “In-memory”, nativement intégrée à SQL Server 2014. On peut voir Hekaton comme un moteur chargé en mémoire optimisé pour le monde OLTP (On-Line Transactional Processing). Le but de cette technologie est de bénéficier de l’évolution des configurations matérielles actuelles (mémoire vive plus importante), et de réduire de manière significative les temps de latence des opérations OLTP courantes telles que les “SELECT”, “INSERT”, “UDAPTE” et “DELETE”. Etant intégrée à SQL server 2014, cette technologie ne nécessite pas un langage différent. Le Transact SQL actuel fonctionnera avec le moteur Hekaton et bénéficiera d’extensions pour tirer pleinement parti de la technologie “In-Memory”
“Hekaton” est le nom de code de la technologie “In-memory”, nativement intégrée à SQL Server 2014. On peut voir Hekaton comme un moteur chargé en mémoire optimisé pour le monde OLTP (On-Line Transactional Processing). Le but de cette technologie est de bénéficier de l’évolution des configurations matérielles actuelles (mémoire vive plus importante), et de réduire de manière significative les temps de latence des opérations OLTP courantes telles que les “SELECT”, “INSERT”, “UDAPTE” et “DELETE”.
Etant intégrée à SQL server 2014, cette technologie ne nécessite pas un langage différent. Le Transact SQL actuel fonctionnera avec le moteur Hekaton et bénéficiera d’extensions pour tirer pleinement parti de la technologie “In-Memory”
Le moteur Hekaton a été pensé pour tirer parti de la mémoire vive disponible au sein des serveurs. Mais tant qu’à ré écrire un moteur, autant en profiter pour revoir certains principes sur le stockage des données et d’en optimiser l’accès, notamment au niveau des “lock” et des “latch”. Plus de page et d’extension: Pour les tables en mémoire, les données ne sont plus stockées dans des pages et il n’y a plus besoin de réserver de l’espace pour les extensions. Les données résident en mémoire sous forme de ligne. La structure des lignes a été modifiée et les lignes appartenant à la même valeur d’index seront liées entre elles. Nous verrons ces points plus bas. Plus de “Loch” et “Latch”: Dans SQL Server (hors Hekaton), les “lock” font partie du process d’accès aux données. Lorsqu’un accès en lecture aux données est réalisé, un “lock” partagé doit être attribué à la requête qui effectue l’opération de lecture. Dans le cas d’un accès en écriture, il est alors nécessaire d’obtenir un “lock” exclusif. En ce qui concerne la structure interne des accès, ce sont les mécanismes de “latch” qui sont invoqués. Le moteur en mémoire “Hekaton” a été prévu afin d’être libre de tous verrous. L’accès aux données utilise le principe du “Multi-Version Concurrency Control (MVCC). Pour le process d’accès interne, le moteur “Hekaton” à sa propre allocation de mémoire qui ne nécessite plus l’utilisation des “latch”
Le moteur Hekaton a été pensé pour tirer parti de la mémoire vive disponible au sein des serveurs. Mais tant qu’à ré écrire un moteur, autant en profiter pour revoir certains principes sur le stockage des données et d’en optimiser l’accès, notamment au niveau des “lock” et des “latch”.
Plus de page et d’extension:
Pour les tables en mémoire, les données ne sont plus stockées dans des pages et il n’y a plus besoin de réserver de l’espace pour les extensions. Les données résident en mémoire sous forme de ligne. La structure des lignes a été modifiée et les lignes appartenant à la même valeur d’index seront liées entre elles. Nous verrons ces points plus bas.
Plus de “Loch” et “Latch”:
Dans SQL Server (hors Hekaton), les “lock” font partie du process d’accès aux données. Lorsqu’un accès en lecture aux données est réalisé, un “lock” partagé doit être attribué à la requête qui effectue l’opération de lecture. Dans le cas d’un accès en écriture, il est alors nécessaire d’obtenir un “lock” exclusif. En ce qui concerne la structure interne des accès, ce sont les mécanismes de “latch” qui sont invoqués.
Le moteur en mémoire “Hekaton” a été prévu afin d’être libre de tous verrous. L’accès aux données utilise le principe du “Multi-Version Concurrency Control (MVCC). Pour le process d’accès interne, le moteur “Hekaton” à sa propre allocation de mémoire qui ne nécessite plus l’utilisation des “latch”
L’idée de la technologie en mémoire, est de pouvoir stocker une ou plusieurs tables en mémoire…..mais de garantir aussi la durabilité des données en cas de pannes. Pour des raisons de restauration des données, le moteur “Hekaton” stocke aussi les données sur disque. Cependant les données résident tout le temps en mémoire. Au démarrage du serveur SQL ou quand une base de données est mise en ligne, le moteur “Hekaton” lit les données se trouvant sur disque et les montent en mémoire. Dans le même temps, le moteur construit les index qui sont aussi stockés en mémoire, car ceux-ci ne sont pas stockés sur disque. Les tables “Hekaton” existent sous 2 formes : Durable ou non-durable. Le contenu des tables durables est maintenu en cas de défaillance du système. Dans le cas des tables non-durables, seul le schéma de la table est conservé en cas de défaillance. Les tables “non-durables” seront donc vides après un redémarrage. Ces 2 formes de table partagent cependant les mêmes codes pour l’atomicité, l’isolation et la consistance des transactions. La seule différence est que les tables durables possèdent un mécanisme de “log” et de “checkpoint”. (Nous verrons ce système un peu plus loin). Indexe obligatoire Lorsque les données sont montées en mémoire, la seule solution que possède le moteur “Hekaton” pour accéder aux données est l’utilisation des index. Par conséquent, chaque table en mémoire doit avoir impérativement au moins un index. Les tables durables doivent avoir une clef primaire. Les index contiennent uniquement des pointeurs vers les lignes stockées en mémoire. Structure des données Les données en mémoire ne sont plus stockées par page de 8k mais par ligne dont la structure est la suivante : une entête (Row header) et la donnée (Payload): Schéma 1 : Structure des données Entête Un entête de ligne occupe au moins 30 Octets et est composé de : Begin-Timestamp : Défini l’instant où la transaction a inséré la ligne End-Timestamp : Défini l’instant où la ligne a été supprimée StmID : au sein d’une transaction, chaque instruction SQL possède un identifiant unique (StmID). Lorsqu’une ligne est créée, le StmID est inscrit dans l’entête IdxLinkCount : Référence le nombre d’index qui pointe sur cette ligne Index Pointers : Espace réservé pour les pointeurs d’index, cet espace comprend 64 bits multiplié par le nombre d’index. Il prend donc au minimum 64 bits vu qu’il est obligatoire d’avoir au moins un index. C’est via cet “index pointer” que les lignes ayant la même valeur de “bucket” seront liées dans la même chaîne. Données La partie “Payload” du Schéma 1 contient en fait les données elle–même (les colonnes clefs et les autres colonnes), et dépend de la définition de la table. Les dlls* générées lors de la création de la table “savent” comment accéder aux données et comment les interpréter. Le moteur Hekaton, au final, ne sait rien de plus sur les données. * ( je n’en parlerai pas dans cet article, mais le moteur “Hekaton” crée des dlls pour effectuer des opérations sur les tables en mémoire. Le moteur n’accède donc pas en direct aux tables mais passe par ces dlls. Plus de détails ici) Les Index Comme nous avons vu précédemment, les index ne sont plus stockés sur disque lorsque l’on utilise une table en mémoire. Ils sont construits à la volée et résident en mémoire. Il existe 2 sortes d’index : Les “Hash index” et les “Range index”. Pour une table en mémoire, il est possible de définir jusqu’à 8 index. Hash Index (indexe de hachage) Les “Hash Index” contiennent un nombre fixe d’espace (“bucket”). Le nombre d’espace est précisé lors de la création de la table ou de l’index. Dans ces espaces, il sera stocké un pointeur vers une chaîne de lignes. Lors de la création d’un “Hash index”, il est nécessaire de définir cet espace via la commande “Bucket-count”. Cela permet de réserver la place nécessaire pour créer l’index. Attention, cela peut avoir des impacts sur la performance. Si la valeur du “bucket_count” est trop petite, de trop nombreuses lignes seront “enchainées” à partir de la même valeur d’index et cela réduira la performance générale des requêtes. Dans le cas contraire, si le “Bucket_count” est trop important, il y’a un risque d’utiliser de la mémoire vive pour rien. Il faut aussi savoir que le nombre spécifié dans le “Bucket_count” est arrondi à la puissance 2 supérieure. D’après les informations que j’ai pu avoir, l’idéal est de définir un “Bucket_count” égal au nombre de valeurs distinctes. Pour un index sur une colonne, la requête suivante peut aider : Select Count(distinct [MaColonne]) from MaTable Pour un index composite : Select count (*) from ( Select distinct MaColonne1, MaColonne2 from MaTable) Nb Les index de hachage sont plutôt performants si les requêtes contiennent des clauses d’égalité. La DMV “sys.dm_db_xtp_hash_index_stats” permet d’obtenir des informations sur les hash index. Range Index (index non-cluster) Les “range index” s’appuient sur le principe des “BW-tree”, afin de créer une structure ordonnée, permettant un scan efficace d’une large plage de données. Par exemple lors d’une requête avec une clause comme MaValeur > 30. Imaginons la table en mémoire ci-dessous et son allocation mémoire: L’index non-cluster sera de la forme ci-dessous, trié sur la colonne nom : Et les données seront liées entre elles via la partie “Index pointer” de l’entête : Ci-dessous, voici une représentation de l’index non-cluster (niveau feuille) et la correspondance avec les données. L’index pointe sur le début de la chaîne (quand il y en a une) et le pointeur d’index permet de retrouver les autres membres de la chaîne : En fouillant un peu sur les ranges index, j’ai trouvé que les “Range Index” utilisent le principe des “skip-list” pour accélérer l’accès aux données. Ci-dessous, une explication rapide de ce qu’est une “skiplist”. “Skip list” (Liste à enjambements) Un “range index” s’implémente sur ce que l’on appelle une “skip-list”. Une structure “skip-list” est une liste d’éléments. Ces éléments sont liés entre eux par ordre croissant. Certains nœuds de cette liste seront plus grands que d’autres et seront appelés “tours” (towers). Les tours contiennent des liens vers les autres tours de même taille. Ces liens permettent un passage rapide entre les différents niveaux lors de la recherche d’une information. D’où le nom de “skip list”. L’idée générale de la “skip-list” est de partir du niveau le plus haut du nœud de tête et, en restant sur le même niveau, de suivre les liens entre les nœuds de même niveau jusqu’à la valeur souhaitée, ou jusqu’à la fin du nœud si la valeur n’a pas été trouvée. Si la valeur en fin de nœud est inférieure à la valeur cherchée, tout en restant sur le même nœud on passe au niveau inférieur et on continue la recherche sur le nouveau niveau. Afin d’essayer d’être plus clair, voici un exemple. Ci-dessous une représentation d’une “skip-list” avec 10 éléments (sur un schéma simplifié) : Dans le cas d’une recherche de l’élément 12, voici les grandes étapes de recherche : En commençant par l’élément haut du nœud de la racine (root), la première flèche pointe sur le nœud 6. Donc 6 est plus petit que 12. C’est le dernier nœud de ce niveau. A partir du nœud 6, on passe au niveau inférieur A partir de ce nouveau niveau, depuis le nœud 6, on passe au nœud suivant qui est le nœud 25. 25 est supérieur à 12, donc on reste sur le nœud 6 et on passe encore au niveau inférieur. A partir de ce niveau, en étant toujours au nœud 6, on passe au nœud suivant qui est le numéro 9. 9 est inférieur à 12. On définit le nœud de départ à 9 En restant sur le nœud 9, on passe au niveau inférieur et on trouve le nœud 12. Voilà pour une présentation rapide du range index et du principe de la “skip list”. Pour plus de détail sur les skip-lists, un article est disponible ici Liens entre les données et les indexes Comme nous avons vu plus haut, le seul moyen pour retrouver les données est de passer par un index, d’où l’obligation d’avoir au moins un index. Chaque ligne est constituée d’une entête en plus des données, et cette entête possède des informations de liaison vers les autres lignes possédant la même information d’index. La figure ci-dessous, que l’on retrouve dans de nombreuses documentations, illustre comment les lignes sont liées via un index “Hash index” et un “Range index”. Une ligne de données est constituée d’un Nom et d’une Ville. Un “Hash index est créé sur la colonne Ville et un “Range index” est créé sur la colonne Nom. Un “hash index” sur la colonne Ville. Le premier “Bucket” pointe sur le nom de la ville qui commence par la lettre B. Il pointe sur l’enregistrement Susan/Bogota, ce denier pointant à son tour sur l’enregistrement suivant dont le nom de la ville commence aussi par B. Les enregistrements appartenant au même “bucket” sont donc liés entre eux. Le principe est le même pour les villes dont le nom commence par P. (Même si sur le schéma B et P sont visuellement ordonnés, il n’ y a en réalité aucun classement sur un “Hash index”). Un “Range index” (skip list) sur la colonne Nom. Dans le schéma ci-dessous, seule une liste ordonnée des index est représentée et liée aux données :
L’idée de la technologie en mémoire, est de pouvoir stocker une ou plusieurs tables en mémoire…..mais de garantir aussi la durabilité des données en cas de pannes. Pour des raisons de restauration des données, le moteur “Hekaton” stocke aussi les données sur disque. Cependant les données résident tout le temps en mémoire.
Au démarrage du serveur SQL ou quand une base de données est mise en ligne, le moteur “Hekaton” lit les données se trouvant sur disque et les montent en mémoire. Dans le même temps, le moteur construit les index qui sont aussi stockés en mémoire, car ceux-ci ne sont pas stockés sur disque.
Les tables “Hekaton” existent sous 2 formes : Durable ou non-durable. Le contenu des tables durables est maintenu en cas de défaillance du système. Dans le cas des tables non-durables, seul le schéma de la table est conservé en cas de défaillance. Les tables “non-durables” seront donc vides après un redémarrage. Ces 2 formes de table partagent cependant les mêmes codes pour l’atomicité, l’isolation et la consistance des transactions. La seule différence est que les tables durables possèdent un mécanisme de “log” et de “checkpoint”. (Nous verrons ce système un peu plus loin).
Lorsque les données sont montées en mémoire, la seule solution que possède le moteur “Hekaton” pour accéder aux données est l’utilisation des index. Par conséquent, chaque table en mémoire doit avoir impérativement au moins un index. Les tables durables doivent avoir une clef primaire. Les index contiennent uniquement des pointeurs vers les lignes stockées en mémoire.
Les données en mémoire ne sont plus stockées par page de 8k mais par ligne dont la structure est la suivante : une entête (Row header) et la donnée (Payload):
Schéma 1 : Structure des données
La partie “Payload” du Schéma 1 contient en fait les données elle–même (les colonnes clefs et les autres colonnes), et dépend de la définition de la table. Les dlls* générées lors de la création de la table “savent” comment accéder aux données et comment les interpréter. Le moteur Hekaton, au final, ne sait rien de plus sur les données.
* ( je n’en parlerai pas dans cet article, mais le moteur “Hekaton” crée des dlls pour effectuer des opérations sur les tables en mémoire. Le moteur n’accède donc pas en direct aux tables mais passe par ces dlls. Plus de détails ici)
Comme nous avons vu précédemment, les index ne sont plus stockés sur disque lorsque l’on utilise une table en mémoire. Ils sont construits à la volée et résident en mémoire. Il existe 2 sortes d’index : Les “Hash index” et les “Range index”. Pour une table en mémoire, il est possible de définir jusqu’à 8 index.
Hash Index (indexe de hachage)
Les “Hash Index” contiennent un nombre fixe d’espace (“bucket”). Le nombre d’espace est précisé lors de la création de la table ou de l’index. Dans ces espaces, il sera stocké un pointeur vers une chaîne de lignes. Lors de la création d’un “Hash index”, il est nécessaire de définir cet espace via la commande “Bucket-count”. Cela permet de réserver la place nécessaire pour créer l’index. Attention, cela peut avoir des impacts sur la performance. Si la valeur du “bucket_count” est trop petite, de trop nombreuses lignes seront “enchainées” à partir de la même valeur d’index et cela réduira la performance générale des requêtes. Dans le cas contraire, si le “Bucket_count” est trop important, il y’a un risque d’utiliser de la mémoire vive pour rien. Il faut aussi savoir que le nombre spécifié dans le “Bucket_count” est arrondi à la puissance 2 supérieure.
D’après les informations que j’ai pu avoir, l’idéal est de définir un “Bucket_count” égal au nombre de valeurs distinctes.
Pour un index sur une colonne, la requête suivante peut aider :
Select Count(distinct [MaColonne]) from MaTable
Pour un index composite :
Select count (*) from
( Select distinct MaColonne1, MaColonne2 from MaTable) Nb
Les index de hachage sont plutôt performants si les requêtes contiennent des clauses d’égalité.
La DMV “sys.dm_db_xtp_hash_index_stats” permet d’obtenir des informations sur les hash index.
Range Index (index non-cluster)
Les “range index” s’appuient sur le principe des “BW-tree”, afin de créer une structure ordonnée, permettant un scan efficace d’une large plage de données. Par exemple lors d’une requête avec une clause comme MaValeur > 30.
Imaginons la table en mémoire ci-dessous et son allocation mémoire:
L’index non-cluster sera de la forme ci-dessous, trié sur la colonne nom :
Et les données seront liées entre elles via la partie “Index pointer” de l’entête :
Ci-dessous, voici une représentation de l’index non-cluster (niveau feuille) et la correspondance avec les données. L’index pointe sur le début de la chaîne (quand il y en a une) et le pointeur d’index permet de retrouver les autres membres de la chaîne :
En fouillant un peu sur les ranges index, j’ai trouvé que les “Range Index” utilisent le principe des “skip-list” pour accélérer l’accès aux données. Ci-dessous, une explication rapide de ce qu’est une “skiplist”.
“Skip list” (Liste à enjambements)
Un “range index” s’implémente sur ce que l’on appelle une “skip-list”. Une structure “skip-list” est une liste d’éléments. Ces éléments sont liés entre eux par ordre croissant. Certains nœuds de cette liste seront plus grands que d’autres et seront appelés “tours” (towers). Les tours contiennent des liens vers les autres tours de même taille. Ces liens permettent un passage rapide entre les différents niveaux lors de la recherche d’une information. D’où le nom de “skip list”.
L’idée générale de la “skip-list” est de partir du niveau le plus haut du nœud de tête et, en restant sur le même niveau, de suivre les liens entre les nœuds de même niveau jusqu’à la valeur souhaitée, ou jusqu’à la fin du nœud si la valeur n’a pas été trouvée. Si la valeur en fin de nœud est inférieure à la valeur cherchée, tout en restant sur le même nœud on passe au niveau inférieur et on continue la recherche sur le nouveau niveau. Afin d’essayer d’être plus clair, voici un exemple. Ci-dessous une représentation d’une “skip-list” avec 10 éléments (sur un schéma simplifié) :
Dans le cas d’une recherche de l’élément 12, voici les grandes étapes de recherche :
Voilà pour une présentation rapide du range index et du principe de la “skip list”. Pour plus de détail sur les skip-lists, un article est disponible ici
Comme nous avons vu plus haut, le seul moyen pour retrouver les données est de passer par un index, d’où l’obligation d’avoir au moins un index. Chaque ligne est constituée d’une entête en plus des données, et cette entête possède des informations de liaison vers les autres lignes possédant la même information d’index.
La figure ci-dessous, que l’on retrouve dans de nombreuses documentations, illustre comment les lignes sont liées via un index “Hash index” et un “Range index”. Une ligne de données est constituée d’un Nom et d’une Ville. Un “Hash index est créé sur la colonne Ville et un “Range index” est créé sur la colonne Nom.
Un “hash index” sur la colonne Ville. Le premier “Bucket” pointe sur le nom de la ville qui commence par la lettre B. Il pointe sur l’enregistrement Susan/Bogota, ce denier pointant à son tour sur l’enregistrement suivant dont le nom de la ville commence aussi par B. Les enregistrements appartenant au même “bucket” sont donc liés entre eux. Le principe est le même pour les villes dont le nom commence par P. (Même si sur le schéma B et P sont visuellement ordonnés, il n’ y a en réalité aucun classement sur un “Hash index”).
Un “Range index” (skip list) sur la colonne Nom. Dans le schéma ci-dessous, seule une liste ordonnée des index est représentée et liée aux données :
Nous avons vu plus haut qu’il existe 2 types de table : Durable et non-durable. Dans le cas des premières, il est donc nécessaire de prévoir un mécanisme d’écriture sur disque afin de conserver les données même en cas de défaillance du serveur. Les tables “Hekaton” sont optimisées pour résider en mémoire et n’ont pas besoin d’avoir accès aux disques durs (La fameuse garantie que les données résident toujours en mémoire). Mais pour des raisons de récupération des données, il est nécessaire de rendre les données persistantes. Le moteur “Hekaton” n’utilise pas le “Buffer pool” et ne stocke pas les données sur disque sous forme de pages de 8k. En réalité, “Hekaton” ne stocke rien dans des fichiers de données, mais s’appuie sur la technologie “filestream” et stocke les données sous forme de blob (Binary Large OBject). Pour les tables en mémoire, les logs sont aussi écrits sur disque au moment où une transaction est terminée avec succès. Ce comportement est identique à celui des tables classiques (stockées sur disque ou “disk-based table”), au détail prêt que ce mécanisme est plus optimisé et que les index ne sont pas logués dans le cas des tables en mémoire. On a vu que “Hekaton” ne stocke pas les données sous forme de pages mais que les données sont éparpillées en mémoire (les données ne sont pas regroupées par page). Au moment du “Checkpoint”, les logs de transactions (ou le cache log) sont scannés et les lignes sont stockées sur disque dans des fichiers de “chekpoint”. Ces fichiers de “checkpoint” sont en fait des fichiers “FileStream” et ne servent que pour restaurer les données. Le moteur “Hekaton” possède un mécanisme appelé “Offline Chekpoint” qui lit de manière continue les logs et stocke sur disque les modifications réalisées sur les données. Il y a 3 types de fichier “Checkpoint” : Data file : Le “data file” est organisé de manière chronologique et contient uniquement une insertion d’une version de ligne pour une plage de temps donnée. Cette insertion dans le fichier est “provoquée” soit par un “INSERT” ou un “UPDATE”. Un “data file” est caractérisé par la plage de temps qu’il couvre et ne contient donc que des transactions comprises dans cette plage de temps. La taille typique de “data file” est de 128MB. (pour des serveurs avec moins de 16GB de RAM, ce fichier est souvent à 16 MB). Ce fichier peut parfois être plus grand que 128 MB car une transaction ne peut pas être stockée dans 2 “data file” différents. Delta File : Ce fichier ne contiendra que les IDs des lignes effacées. Lors de la restauration, ce fichier servira de filtre afin d’éviter de recharger la mémoire avec les lignes effacées. Un fichier “delta file” à une taille typique de 8MB (pour les serveurs de moins de 16 GB de RAM, la taille du “delta file” peut être de 1 MB). Chose importante, un “delta file” est associé à un seul “data file” Checkpoint File Directory (CFD) : Ce fichier contient les références de tous les “data file” et “delta file” C’est lors du process de “checkpoint” que les données sont écrites dans les fichiers. De plus, lorsqu’une ligne est effacée, “Hekaton” ne revient pas dans le fichier “Data file” pour supprimer physiquement la ligne, mais rajoute cette information dans le fichier “delta file”. Donc plus le temps passe, plus les fichiers “data file” risquent de se retrouver avec plus de lignes supprimées que de lignes encore “actives”. Cela peut poser des problèmes d’utilisation inutile d’espace disque et de temps perdu lors d’un process de restauration des données. Pour optimiser l’utilisation des fichiers “chekpoint”, il existe un process appelé “Merge” qui périodiquement va nettoyer les fichiers “data file” et les fusionner entre eux. Cette opération de “merge” est détaillée ici.
Nous avons vu plus haut qu’il existe 2 types de table : Durable et non-durable. Dans le cas des premières, il est donc nécessaire de prévoir un mécanisme d’écriture sur disque afin de conserver les données même en cas de défaillance du serveur.
Les tables “Hekaton” sont optimisées pour résider en mémoire et n’ont pas besoin d’avoir accès aux disques durs (La fameuse garantie que les données résident toujours en mémoire). Mais pour des raisons de récupération des données, il est nécessaire de rendre les données persistantes. Le moteur “Hekaton” n’utilise pas le “Buffer pool” et ne stocke pas les données sur disque sous forme de pages de 8k. En réalité, “Hekaton” ne stocke rien dans des fichiers de données, mais s’appuie sur la technologie “filestream” et stocke les données sous forme de blob (Binary Large OBject).
Pour les tables en mémoire, les logs sont aussi écrits sur disque au moment où une transaction est terminée avec succès. Ce comportement est identique à celui des tables classiques (stockées sur disque ou “disk-based table”), au détail prêt que ce mécanisme est plus optimisé et que les index ne sont pas logués dans le cas des tables en mémoire.
On a vu que “Hekaton” ne stocke pas les données sous forme de pages mais que les données sont éparpillées en mémoire (les données ne sont pas regroupées par page). Au moment du “Checkpoint”, les logs de transactions (ou le cache log) sont scannés et les lignes sont stockées sur disque dans des fichiers de “chekpoint”. Ces fichiers de “checkpoint” sont en fait des fichiers “FileStream” et ne servent que pour restaurer les données. Le moteur “Hekaton” possède un mécanisme appelé “Offline Chekpoint” qui lit de manière continue les logs et stocke sur disque les modifications réalisées sur les données.
Il y a 3 types de fichier “Checkpoint” :
C’est lors du process de “checkpoint” que les données sont écrites dans les fichiers. De plus, lorsqu’une ligne est effacée, “Hekaton” ne revient pas dans le fichier “Data file” pour supprimer physiquement la ligne, mais rajoute cette information dans le fichier “delta file”.
Donc plus le temps passe, plus les fichiers “data file” risquent de se retrouver avec plus de lignes supprimées que de lignes encore “actives”. Cela peut poser des problèmes d’utilisation inutile d’espace disque et de temps perdu lors d’un process de restauration des données.
Pour optimiser l’utilisation des fichiers “chekpoint”, il existe un process appelé “Merge” qui périodiquement va nettoyer les fichiers “data file” et les fusionner entre eux. Cette opération de “merge” est détaillée ici.
Durant les ITCamps, la question suivante m’a été posée : “Est-il possible de réaliser une opération de “merge” manuellement? . La réponse est oui. En voici un exemple : Pour réaliser un “merge” manuel il faut utiliser la procédure suivante : sys.sp_merge_xtp_checkpoint_files. Avant de faire le merge nous allons regarder l’utilisation de nos fichiers “chekpoint” avec la procédure sys.dm_db_xtp_checkpoint_files. SELECT checkpoint_file_id, file_type_desc, internal_storage_slot, file_size_in_bytes, file_size_used_in_bytes, inserted_row_count, deleted_row_count, lower_bound_tsn, upper_bound_tsn FROM sys.dm_db_xtp_checkpoint_files WHERE [state]=2 ORDER BY file_type_desc, upper_bound_tsn Admettons que nous souhaitons fusionner les 3 fichiers sélectionnés, nous allons utiliser le script suivant. Dans cet exemple j’utilise la base de données en mémoire “TicketReservations”: EXEC sys.sp_xtp_merge_checkpoint_files 'TicketReservations', 19487, 77807. Si on ré-exécute le script (celui avec sys.dm_db_xtp_checkpoint_files) pour regarder l’utilisation des fichiers “checkpoint”, nous pouvons voir que nos 3 fichiers ont bien été fusionnés : Ensuite il est nécessaire d’exécuter manuellement le “garbage collection” avec la procédure suivante : EXEC sp_filestream_force_garbage_collection Pour info, le trace flag 9851 permet de désactiver la fusion des “checkpoint file”
Durant les ITCamps, la question suivante m’a été posée : “Est-il possible de réaliser une opération de “merge” manuellement? . La réponse est oui. En voici un exemple :
Pour réaliser un “merge” manuel il faut utiliser la procédure suivante : sys.sp_merge_xtp_checkpoint_files.
Avant de faire le merge nous allons regarder l’utilisation de nos fichiers “chekpoint” avec la procédure sys.dm_db_xtp_checkpoint_files.
SELECT checkpoint_file_id, file_type_desc, internal_storage_slot, file_size_in_bytes, file_size_used_in_bytes, inserted_row_count, deleted_row_count, lower_bound_tsn, upper_bound_tsn FROM sys.dm_db_xtp_checkpoint_files WHERE [state]=2 ORDER BY file_type_desc, upper_bound_tsn
Admettons que nous souhaitons fusionner les 3 fichiers sélectionnés, nous allons utiliser le script suivant. Dans cet exemple j’utilise la base de données en mémoire “TicketReservations”:
EXEC sys.sp_xtp_merge_checkpoint_files 'TicketReservations', 19487, 77807.
Si on ré-exécute le script (celui avec sys.dm_db_xtp_checkpoint_files) pour regarder l’utilisation des fichiers “checkpoint”, nous pouvons voir que nos 3 fichiers ont bien été fusionnés :
Ensuite il est nécessaire d’exécuter manuellement le “garbage collection” avec la procédure suivante :
EXEC sp_filestream_force_garbage_collection
Pour info, le trace flag 9851 permet de désactiver la fusion des “checkpoint file”
Bon tout ça, c’est bien mais la question est “Comment crée-t-on une table en mémoire?”. Tout simplement avec un CREATE TABLE et quelques instructions propres aux tables en mémoires Dans un premier temps, il faut créer une base de données avec un groupe de fichier FileStream Création de la base de données : CREATE DATABASE TicketReservations2 ON PRIMARY(NAME = [TicketReservations2_data], FILENAME = 'D:\DemoHekaton\TicketReservations2_data.mdf', size=2GB) LOG ON (name = [TicketReservations_log], Filename='D:\DemoHekaton\TicketReservations2_log.ldf', size=5GB) COLLATE Latin1_General_100_BIN2; go print 'done...' go ALTER DATABASE TicketReservations2 set recovery full go Paramétrage du FileStream : use TicketReservations2 go ALTER DATABASE TicketReservations2 ADD FILEGROUP [TicketReservations2_hk_fs_fg] CONTAINS MEMORY_OPTIMIZED_DATA; ALTER DATABASE TicketReservations2 ADD FILE (NAME = [TicketReservations2_fs_dir], FILENAME = 'D:\DemoHekaton\TicketReservations2_fs_dir') to FILEGROUP [TicketReservations2_hk_fs_fg]; go Création d’une table en mémoire : CREATE TABLE TicketReservationDetail ( iteration int not null, lineId int not null, col3 nvarchar(1000) not null, -- updatable column ThreadID int not null constraint sql_ts_th primary key nonclustered hash (iteration, lineId) with (bucket_count = 10000000)) with (memory_optimized = on, durability = SCHEMA_AND_DATA) go Ci-dessus nous avons donc créé une table en mémoire avec la commande “memory_optimized = on”. De plus, cette table est durable car nous avons défini la durabilité à “SCHEMA_AND_DATA”. Si l’on ne souhaite pas avoir de durabilité alors on utilise le mot clef : “SHEMA_ONLY”. A ce stade-là, nous n’avons toujours pas évoqué les procédures stockées. Ce qui fait que pour le moment nous sommes en mode interopérabilité. C’est à dire que les tables sont en mémoire mais les procédures stockées n’ont pas bougées, et conservent leur fonctionnement originel. De plus, ces dernières sont capables, dans le mode interopérabilité, d’accéder aussi bien aux tables classiques (“disk_based table”) qu’aux tables en mémoire. Il existe un second mode qui est le mode natif, c’est à dire que tout est en mémoire, procédure stockée comprise. Cela permet d’améliorer encore les performances d’accès aux données. Ci-dessous un exemple réalisé sur ma machine de démo (Lenovo W530 avec 32Go de RAM), dans les 3 modes: En mode natif, j’obtiens un gain multiplié par 66 par rapport à une table normale (“disk-based table”). Pour plus de détail sur la technologie en mémoire incluse nativement dans SQL Server Enterprise Edition, vous pouvez consulter l’article suivant : http://msdn.microsoft.com/en-us/library/dn133186(v=sql.120).aspx Voilà pour cet article présentant rapidement les tables en mémoire de SQL Server 2014. Afin d’agrémenter cet article, nous avons réalisé une vidéo avec Sébastien Pertus: http://blogs.technet.com/b/franmer/archive/2014/04/25/vid-233-o-sql-2014-moteur-en-m-233-moire-hekaton.aspx Dans cette vidéo nous présenterons aussi l’outil AMR, dont je n’ai pas parlé dans ce billet, qui permet de récolter des informations sur l’usage de vos bases de données et vous proposer les tables qui tireraient un bénéfice de migrer en table en mémoire. Vous trouverez plus d’informations sur l’outil AMR (Analyse, Migrate and Report) dans cet article : http://msdn.microsoft.com/en-us/library/dn205133(v=sql.120).aspx Au plaisir de vous voir lors d’un IT Camp SQL 2014/Power BI. Franck Mercier Pour tester Windows Server 2012, Windows 8, SQL Server 2012, SQL Server 2014 CTP2 et Power BI, vous pouvez télécharger gratuitement la version d’évaluation disponible sous la forme : Windows Server 2012 : d'une image ISO : http://aka.ms/jeveuxwindows2012 d'un fichier VHD avec un système préinstallé : http://aka.ms/jeveuxwindows2012 SQL Server 2012 : http://technet.microsoft.com/en-us/evalcenter/hh225126.aspx Evaluation SQL Server 2014 : http://technet.microsoft.com/en-us/evalcenter/dn205290.aspx Evaluation Power BI : http://www.microsoft.com/en-us/powerbi/default.aspx#fbid=URTAc3eQRIw Testez Azure gratuitement pendant un mois : http://aka.ms/Azure0Euro
Bon tout ça, c’est bien mais la question est “Comment crée-t-on une table en mémoire?”. Tout simplement avec un CREATE TABLE et quelques instructions propres aux tables en mémoires
Dans un premier temps, il faut créer une base de données avec un groupe de fichier FileStream
Création de la base de données :
CREATE DATABASE TicketReservations2 ON PRIMARY(NAME = [TicketReservations2_data], FILENAME = 'D:\DemoHekaton\TicketReservations2_data.mdf', size=2GB) LOG ON (name = [TicketReservations_log], Filename='D:\DemoHekaton\TicketReservations2_log.ldf', size=5GB) COLLATE Latin1_General_100_BIN2; go print 'done...' go ALTER DATABASE TicketReservations2 set recovery full go
Paramétrage du FileStream :
use TicketReservations2 go ALTER DATABASE TicketReservations2 ADD FILEGROUP [TicketReservations2_hk_fs_fg] CONTAINS MEMORY_OPTIMIZED_DATA; ALTER DATABASE TicketReservations2 ADD FILE (NAME = [TicketReservations2_fs_dir], FILENAME = 'D:\DemoHekaton\TicketReservations2_fs_dir') to FILEGROUP [TicketReservations2_hk_fs_fg]; go
Création d’une table en mémoire :
CREATE TABLE TicketReservationDetail ( iteration int not null, lineId int not null, col3 nvarchar(1000) not null, -- updatable column ThreadID int not null constraint sql_ts_th primary key nonclustered hash (iteration, lineId) with (bucket_count = 10000000)) with (memory_optimized = on, durability = SCHEMA_AND_DATA) go
Ci-dessus nous avons donc créé une table en mémoire avec la commande “memory_optimized = on”. De plus, cette table est durable car nous avons défini la durabilité à “SCHEMA_AND_DATA”. Si l’on ne souhaite pas avoir de durabilité alors on utilise le mot clef : “SHEMA_ONLY”.
A ce stade-là, nous n’avons toujours pas évoqué les procédures stockées. Ce qui fait que pour le moment nous sommes en mode interopérabilité. C’est à dire que les tables sont en mémoire mais les procédures stockées n’ont pas bougées, et conservent leur fonctionnement originel. De plus, ces dernières sont capables, dans le mode interopérabilité, d’accéder aussi bien aux tables classiques (“disk_based table”) qu’aux tables en mémoire.
Il existe un second mode qui est le mode natif, c’est à dire que tout est en mémoire, procédure stockée comprise. Cela permet d’améliorer encore les performances d’accès aux données. Ci-dessous un exemple réalisé sur ma machine de démo (Lenovo W530 avec 32Go de RAM), dans les 3 modes:
En mode natif, j’obtiens un gain multiplié par 66 par rapport à une table normale (“disk-based table”).
Pour plus de détail sur la technologie en mémoire incluse nativement dans SQL Server Enterprise Edition, vous pouvez consulter l’article suivant :
http://msdn.microsoft.com/en-us/library/dn133186(v=sql.120).aspx
Voilà pour cet article présentant rapidement les tables en mémoire de SQL Server 2014. Afin d’agrémenter cet article, nous avons réalisé une vidéo avec Sébastien Pertus:
http://blogs.technet.com/b/franmer/archive/2014/04/25/vid-233-o-sql-2014-moteur-en-m-233-moire-hekaton.aspx
Dans cette vidéo nous présenterons aussi l’outil AMR, dont je n’ai pas parlé dans ce billet, qui permet de récolter des informations sur l’usage de vos bases de données et vous proposer les tables qui tireraient un bénéfice de migrer en table en mémoire. Vous trouverez plus d’informations sur l’outil AMR (Analyse, Migrate and Report) dans cet article :
http://msdn.microsoft.com/en-us/library/dn205133(v=sql.120).aspx
Evaluation SQL Server 2014 :
SQL Server 2014 est maintenant disponible en version finale. Si vous souhaitez l’évaluer vous pouvez :
Avec Sébastien Pertus, nous avons enregistré une série de vidéos illustrant certaines nouveautés majeures de cette version 2014.
Dans la vidéo d’aujourd'hui, nous allons vous présenter la technologie OLTP en mémoire.
Pour faire suite à l’article sur l’évolution de la passerelle de Power BI, voici une vidéo illustrant l’utilisation de la passerelle et la mise en place d’une mise à jour périodique pour les rapports.
Power BI est maintenant en version finale depuis début février, et certains composants ont évolués, dont la passerelle. Cet article vous présente la nouvelle version de cette dernière.
La passerelle permet de rafraichir des données à partir de nombreuses sources externes dont :
Pour illustrer cet article, j’ai publié un rapport puis j’ai paramétré une passerelle avec des sources de données. Enfin, j’ai activé la passerelle pour mon rapport.
Ci-dessous voici le rapport qui a été publié sur le site Power BI :
Afin de pouvoir mettre à jour les rapports publiés, il est nécessaire, à partir du centre d’administration de Power BI, de créer une passerelle et une ou plusieurs sources de données associées à la passerelle.
Dans le centre d’administration de Power BI, cliquez sur « passerelles » puis sur « nouvelle passerelle ». Ensuite suivez les instructions et, dans le cas d’une première installation, téléchargez et installez la passerelle sur votre serveur.
Les détails de cette partie sont disponibles ici :
Une fois la passerelle créée, il est nécessaire de lui associer une ou plusieurs sources de données. Dans la partie gauche, cliquez sur « Sources de données » puis « nouvelle source de données »
Les détails de cette partie sont aussi disponibles ici :
Une fois le rapport publié sur Power BI, lorsque vous cliquez sur les trois petits points en bas à droit de l’image du rapport, un menu contextuel apparaît. Cliquez alors sur « Planifier actualisation des données »
La fenêtre d’actualisation apparaît. A partir de cette fenêtre, vous allez pouvoir planifier la mise à jour du rapport via la passerelle. Il vous sera possible de choisir une ou plusieurs sources données à synchroniser, ainsi que la possibilité de définir une planification de rafraîchissement.
Dans le menu « historique », vous aurez le statu des mises à jour
De plus, dans le centre d’administration de Power BI, il est aussi possible de surveiller l’état des passerelles et voir lorsque des erreurs sont survenues lors des mises à jour. Ci-dessous un exemple après avoir rendu indisponible une de mes bases de données.
Pour l’exemple, je modifie une valeur directement dans ma base de données :
En passant une quantité de 6 à 456.
Pour mettre à jour le rapport depuis le site Power BI, cliquez sur « Planifier actualisation des données »
Puis dans la fenêtre de la passerelle cliquez sur le bouton « actualiser le rapport maintenant »
Après une mise à jour de mon rapport depuis mon site Power BI, les nouvelles données sont bien visibles dans mon rapport (modification au niveau des vélos)
La vidéo illustrant cet article arrive bientôt !!!
Une nouvelle version de l’add’in Power Query est disponible depuis le 5 mars 2014. Cet add-in est téléchargeable ici:
http://www.microsoft.com/fr-fr/download/details.aspx?id=39379
Cette version apporte de nombreuses améliorations dont les principales sont présentées dans ce billet
La nouvelle version de Power Query propose une navigation améliorée lors de la saisie multiple des éléments d’une source de données. Une liste résume les différents éléments sélectionnés et permet l’édition d’un des éléments directement à partir de cette liste.
Dans l’éditeur Power Query, un nouvel onglet, “Insérer” a été ajouté. Cet onglet facilitera la personnalisation des données. Par exemple, après avoir sélectionné deux colonnes ou plus, il sera possible de créer une nouvelle colonne représentant soit la somme soit le produit de ces colonnes.
Dans l’éditeur Power Query, lors de l’insertion d’une nouvelle étape, un message d’alerte apparaît avant d’effectuer l’opération.
La fenêtre d’insertion de colonne a été améliorée et propose maintenant la possibilité de nommer directement la colonne à partir de cette fenêtre. De plus, les erreurs de syntaxes, lors de la création d’une formule personnalisée, seront notifiées en bas de la fenêtre.
Une autre des améliorations vient de la sélection à partir d’un tableau Excel. Maintenant, il suffit juste de sélectionner une cellule d’un tableau pour le sélectionner entièrement. De plus, la boîte de dialogue permet de redéfinir, si besoin, la plage des cellules du tableau.
Power Query permet maintenant de différencier lorsqu’une erreur a eu lieu lors du chargement des données ou lorsque l’utilisateur annule volontairement le chargement. Dans ce dernier cas, les données chargées seront affichées dans la feuille Excel.
De plus, en cas d’erreur, il sera possible d’envoyer un formulaire détaillant l’erreur aux équipes de Microsoft afin de continuer à améliorer Power Query.
Jeu : Tentez de gagner votre livre numérique ! Tentez de gagner votre livre numérique !
Téléchargez une version d’évaluation gratuite et tentez de gagner votre e-book !
Un gagnants tous les 10 téléchargements.
Windows 8, déploiement et migration Décelez tout ce qu’il faut savoir sur le déploiement et la migration vers Windows 8.
Il est offert en partenariat avec les éditions EYROLLES, tous les 10 téléchargements d’une version d’évaluation gratuite de Windows Server 2012 R2, ou System Center 2012 R2.
Pour cet exemple, j’ai configuré la recherche « One Way outbound » comme illustré dans le schéma suivant :
Cette dernière étape va être découpée en 2 phases :
La création d’une source de résultats se fait à partir de l’administration centrale de SharePoint 2013.
Cliquez sur « Manage service applications » :
Cliquez sur « Search Service Application » :
Dans la partie gauche de la page, cliquez sur « Result Sources » :
Dans la page, « Manage Result Sources », cliquez sur « New Result Source » :
Dans la page « Edit Result Source » : dans la partie « Protocol », sélectionnez « Remote SharePoint »
Puis, dans la partie « Remote Service URL », renseignez l’adresse de votre site SharePoint Online. (Dans cet exemple : « https://bipower.sharepoint.com »)
Cliquez sur le bouton « Save ». Vous devriez voir apparaître votre source de résultats
Maintenant il reste à créer une « Query rule » associée à notre source de résultats .
Tout en restant dans l’application de service de recherche, dans la partie gauche de la fenêtre, cliquez sur « Query Rules »
La fenêtre « Manage Query Rules » s’affiche. Dans la liste déroulante « For what context do you want to configure rules », sélectionnez, par exemple, « Local SharePoint results ».
Puis cliquez sur « New Query Rule ».
Dans la fenêtre « Add Query Rule », Entrez un nom dans le champ « Rule Name »
Puis cliquez sur « Add result Block »
Dans la fenêtre « Add Result Block », dans la liste déroulante « Search this source », sélectionnez la source de résultats créée précédemment.
Cliquez ensuite sur le lien « Settings », puis définissez comment les résultats vont apparaître dans la page de recherche.
Puis, cliquez sur le bouton « Ok ». Ci-dessous un exemple de configuration :
De retour dans la fenêtre « Edit Query Rule », cliquez sur le lien « Publishing », puis cochez la case « Is Active ».
Cliquez sur le bouton « Save ».
Avant de tester la recherche, il est déjà possible de tester la source de résultats à partir de l’interface de l’administration centrale de SharePoint.
Pour ce faire, connectez-vous à votre serveur SharePoint avec un compte fédéré. Dans mon cas, je vais faire le test avec le compte « HybridFranmer ».
Dans la fenêtre « Manage Result Sources », repérez la source de résultats concernant votre SharePoint Online. Cliquez sur la flèche à droite pour dérouler le menu, puis sélectionnez « Test Source »
Si tout a été paramétré correctement, vous devriez avoir le résultat suivant :
Lors de mon premier test, j’ai eu l’erreur suivante :
Web error: System.Net.WebException: The request was aborted: The request was canceled. ---> System.ServiceModel.ServiceActivationException: The requested service, 'net.pipe://localhost/SecurityTokenServiceApplication/appsts.svc' could not be activated
Cette erreur s’est produite car mon serveur SharePoint était sous dimensionné en mémoire vive (3,5 Go). J’ai profité d’une des caractéristiques du Cloud, l’élasticité, pour augmenter la capacité mémoire de mon serveur SharePoint (je suis passé à 7 Go). le test de la source de résultats est alors tombé en marche !
Dans le cas où vous testeriez la source de résultats avec un compte non fédéré, vous obtiendrez le message : « Unauthorized »
Avant de pouvoir tester la recherche, il peut être de bon augure de réaliser d’un « crawl » complet des sources de données.
A partir de l’administration centrale de SharePoint, dans l’administration de la recherche, dans la partie gauche, cliquez sur « Content Sources ».
Localisez la source de contenu, puis cliquez sur la flèche à droite pour dérouler le menu. Cliquez sur « Start Full Crawl ».
Une fois le « crawl » terminé, effectuez une recherche avec un compte unifié. Pour faire la recherche, j’ai utilisé le compte unifié HybridFranmer, et j’ai effectué la recherche depuis le centre de recherche (que j’ai créé au préalable depuis l’administration centrale de SharePoint. Plus de détail sur le site TechNet : http://technet.microsoft.com/fr-fr/library/hh582314.aspx)
Ci-dessous le résultat. Le contenu du SharePoint Online est retourné en haut de la page (voir le paramétrage du «result block » de la « query rule »), et le contenu de SharePoint Server 2013 est retourné en bas de la page.
Si on effectue la même recherche avec un compte non unifié, la même requête de recherche retourne le résultat suivant (uniquement le contenu sur site de SharePoint 2013)
Si vous désirez aller plus loin dans la mise en place de scénarios hybrides, vous pouvez continuer la lecture en consultant les liens suivants :
Les articles précédents sont disponibles ci-dessous:
Etape 1: Configuration de l’authentification des utilisateurs
Dans cette partie nous allons utiliser le script de la fiche technique : http://technet.microsoft.com/en-us/library/dn197169.aspx
Ci-dessous, la liste des variables utilisées lors du script de configuration de la relation « server-to-server ».
$spcn="*.<public_root_domain_name>.com" $spsite=Get-Spsite <principal_web_application_URL> $site=Get-Spsite $spsite $spoappid="00000003-0000-0ff1-ce00-000000000000" $spocontextID = (Get-MsolCompanyInformation).ObjectID $metadataEndpoint = "https://accounts.accesscontrol.windows.net/" + $spocontextID + "/metadata/json/1"
$spcn="*.<public_root_domain_name>.com"
$spsite=Get-Spsite <principal_web_application_URL>
$site=Get-Spsite $spsite
$spoappid="00000003-0000-0ff1-ce00-000000000000"
$spocontextID = (Get-MsolCompanyInformation).ObjectID
$metadataEndpoint = "https://accounts.accesscontrol.windows.net/" + $spocontextID + "/metadata/json/1"
Ci-dessous une copie d’écran pour l’exemple :
Le script ci-dessous télécharge le certificat se trouvant sur site vers le SharePoint Online :
$cerPath = "<path to replacement certificate (.cer file)>" $cer = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2 -ArgumentList $pfxPath, $pfxPass $cer.Import($cerPath) $binCert = $cer.GetRawCertData() $credValue = [System.Convert]::ToBase64String($binCert); #La date de début (StartDate) doit être la date du jour. La date de fin (EndDate) doit être la date d'expiration du certificat STS. Date au format Mois/Jour/Année New-MsolServicePrincipalCredential -AppPrincipalId $spoappid -Type asymmetric -Usage Verify -Value $credValue -StartDate <start_date. > -EndDate <end_date>
$cerPath = "<path to replacement certificate (.cer file)>"
$cer = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2 -ArgumentList $pfxPath, $pfxPass
$cer.Import($cerPath)
$binCert = $cer.GetRawCertData()
$credValue = [System.Convert]::ToBase64String($binCert);
#La date de début (StartDate) doit être la date du jour. La date de fin (EndDate) doit être la date d'expiration du certificat STS. Date au format Mois/Jour/Année
New-MsolServicePrincipalCredential -AppPrincipalId $spoappid -Type asymmetric -Usage Verify -Value $credValue -StartDate <start_date. > -EndDate <end_date>
Ci-dessous, une copie d’écran pour l’exemple :
Le script ci-dessous va rajouter un « Service Principal Name » (SPN) dans le SharePoint Online
$msp = Get-MsolServicePrincipal -AppPrincipalId $spoappid $spns = $msp.ServicePrincipalNames $spns.Add("$spoappid/$spcn") Set-MsolServicePrincipal -AppPrincipalId $spoappid -ServicePrincipalNames $spns
$msp = Get-MsolServicePrincipal -AppPrincipalId $spoappid
$spns = $msp.ServicePrincipalNames
$spns.Add("$spoappid/$spcn")
Set-MsolServicePrincipal -AppPrincipalId $spoappid -ServicePrincipalNames $spns
ATTENTION !!! Notez la différence par rapport à la fiche Technet. Lors de l’exécution de la commande Set-MsolServicePrincipal, j’ai eu une « Unknown error » (pratique pour débugger !! J). J’ai corrigé le problème avec la commande suivante (en fait j’ai tout simplement passer en dur le « Service Principal Name »)
Set-MsolServicePrincipal -ServicePrincipalNames 00000003-0000-0ff1-ce00-000000000000/*.sharepoint.com -AppPrincipalId $spoappid
Pour vérification, vous pouvez exécuter les commandes suivantes :
$msp = Get-MsolServicePrincipal -AppPrincipalId $spoappid $spns = $msp.ServicePrincipalNames $spns
$spns
En résultat, vous devez avoir un SPN de la forme :
00000003-0000-0ff1-ce00-000000000000/*.<public domain name>.com
Ci dessous, une copie d’écran pour l’exemple :
Cette étape va permettre la liaison entre L’ID de l’application principale de SharePoint Online avec le Service « Application Management » de SharePoint Server 2013.
$spoappprincipalID = (Get-MsolServicePrincipal -ServicePrincipalName $spoappid).ObjectID $sponameidentifier = "$spoappprincipalID@$spocontextID" $appPrincipal = Register-SPAppPrincipal -site $site.rootweb -nameIdentifier $sponameidentifier -displayName "SharePoint Online"
$spoappprincipalID = (Get-MsolServicePrincipal -ServicePrincipalName $spoappid).ObjectID
$sponameidentifier = "$spoappprincipalID@$spocontextID"
$appPrincipal = Register-SPAppPrincipal -site $site.rootweb -nameIdentifier $sponameidentifier -displayName "SharePoint Online"
La commande PowerShell ci-dessous va permettre la définition du « Realm » de SharePoint :
Set-SPAuthenticationRealm -realm $spocontextID
Enfin, nous allons créer un « Service Application Proxy » pour Windows Azure AD.
New-SPAzureAccessControlServiceApplicationProxy -Name "ACS" -MetadataServiceEndpointUri $metadataEndpoint -DefaultProxyGroup New-SPTrustedSecurityTokenIssuer -MetadataEndpoint $metadataEndpoint -IsTrustBroker:$true -Name "ACS"
New-SPAzureAccessControlServiceApplicationProxy -Name "ACS" -MetadataServiceEndpointUri $metadataEndpoint -DefaultProxyGroup
New-SPTrustedSecurityTokenIssuer -MetadataEndpoint $metadataEndpoint -IsTrustBroker:$true -Name "ACS"
Validation de la création du proxy :
· Dans l’administration centrale de SharePoint, cloquez sur “Security”
· Dans la section « General Security » cliquez sur « Manage Trust »
· Vérifiez qu’une entrée « ACS », ou le nom que vous lui avez donné, existe
#region Replace STS Certificate
Add-PSSnapin Microsoft.SharePoint.Powershell
$pfxPath = "C:\Temp\franmerPFX.pfx"
$pfxPass = "Pass@word1"
$stsCertificate = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2 $pfxPath, $pfxPass, 20
Set-SPSecurityTokenServiceConfig -ImportSigningCertificate $stsCertificate
certutil -addstore -enterprise -f -v root $stsCertificate
iisreset
net stop SPTimerV4
net start SPTimerV4
#endregion
#region Load Modules
Add-PSSnapin Microsoft.SharePoint.PowerShell
Import-Module Microsoft.PowerShell.Utility
Import-Module MSOnline –force
Import-Module MSOnlineExtended –force
Import-Module Microsoft.Online.SharePoint.PowerShell -force
#region log to SharePoint Online
$cred=Get-Credential
Connect-MsolService –Credential $cred
#region Définition des variables
$spcn="*.SharePoint.com"
$spsite=Get-Spsite http://ITCampSP1
#region Upload STS certificate to SP Online
$cerPath = "C:\Temp\Export_IIS_Franmer.cer"
New-MsolServicePrincipalCredential -AppPrincipalId $spoappid -Type asymmetric -Usage Verify -Value $credValue -StartDate 10/23/2013 -EndDate 9/27/2014
#region Add SPN for the public domain name
#Modification par rapport à la fiche technet
#region Register the SharePoint Online application principal
#region Set the SharePoint authentication Realm
#region Configure On-premises proxy for Azure AD
Sur votre serveur SharePoint 2013, avec les droits d’administrateur local, exécutez les commandes suivantes :
enable-psremoting new-pssession
enable-psremoting
new-pssession
une copie d’écran pour l’exemple :
Durant l’exécution du script la fenêtre suivante va apparaître. Cliquez sur le bouton « Yes to All ».
Puis une autre fenêtre va s’ouvrir, Cliquez sur le bouton « Yes to All ».
A la fin de l’exécution du script vous devez avoir le résultat suivant :
Ensuite, sur le serveur SharePoint 2013, nous allons installer les outils suivants :
Exécutez PowerShell ISE en tant qu’administrateur :
Puis exécutez les commandes suivantes:
Add-PSSnapin Microsoft.SharePoint.PowerShell Import-Module Microsoft.PowerShell.Utility Import-Module MSOnline –force Import-Module MSOnlineExtended –force Import-Module Microsoft.Online.SharePoint.PowerShell –force
Import-Module Microsoft.Online.SharePoint.PowerShell –force
Ensuite, exécutez la commande PowerShell suivante pour se connecter à Office 365
$cred=Get-Credential Connect-MsolService –Credential $cred
Durant l’exécution du script, une fenêtre apparaît pour demander les informations d’identification sur Office 365 :
La suite se trouve ici !
Maintenant que la gestion des identités a été faîtes, nous allons maintenant configurer l’authentification « server to server » entre SharePoint Server 2013, SharePoint Online et Windows Azure Active Directory.
Lors de l’installation de SharePoint, un certificat STS (Security Token Services) est installé par défaut. Nous allons le remplacer par un certificat X.509. Ce certificat peut être soit un certificat auto-signé (pour réaliser des tests) soit un certificat délivré par une autorité publique. Dans cet exemple, nous allons utiliser un certificat délivré par une autorité publique (par Gandi, dans cet exemple)
Sur l’ordinateur, où le certificat public a été installé durant l’étape 1 (Dans mon cas, le DC), allez dans IIS manager :
Puis sélectionnez « Certificate Manager »
Depuis le certificat manager, exporter le certificat au format .pfx. Pour cela, il existe plusieurs méthodes :
Soit un clic droit sur le certificat à exporter et cliquez sur « Export » ; et suivez les instructions.
Soit un click droit sur le certificat à exporter et cliquez sur « view » :
Dans la fenêtre « Certificate », cliquez sur « Copy to File… »
Dans la fenêtre « Certificate Export Wizard », cliquez sur « Next »
Sélectionnez « Yes, export the private key » , puis cliquez sur le bouton « Next »
Selectionnez « Personnal Information Exchange –PKCS #12(.PFX) », puis cliquez sur le bouton « Next »
Sélectionnez « Password », et saisissez un mot de passe, puis cliquez sur le bouton « Next »
Définissez le chemin d’exportation, puis cliquez sur le bouton « Next »
Dans la fenêtre de résumé, cliquez sur le bouton « Finish »
Le certificat est exporté au format .PFX
Pour les besoins de la procédure, on aura besoin aussi du certificat au format .cer. Dans mon cas, Gandi ne permet que le téléchargement au format « .crt ».
En suivant la même méthode que précédemment, il est possible de céer un fichier « .cer » à partir du certificat en .crt.
Dans la fenêtre « Export Private Key », sélectionnez « No, do not export the private key ». Puis cliquez sur le bouton « Next ».
Sélectionez « Base-64 encoded X.509 (.CER) ». Puis cliquez sur le bouton « Next ».
Sélectionnez un chemin d’export du certificat. Puis cliquez sur le bouton « Next ».
Le certificat est exporté.
Nous avons donc les certificat sous un format .pfx et .cer.
Sur tous les serveurs de la ferme SharePoint, exécutez la commande PowerShell suivante :
$pfxPath = "<path to replacement certificate (.pfx file)>" $pfxPass = "<certificate password>" $stsCertificate = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2 $pfxPath, $pfxPass, 20 Set-SPSecurityTokenServiceConfig -ImportSigningCertificate $stsCertificate certutil -addstore -enterprise -f -v root $STScert iisreset net stop SPTimerV4 net start SPTimerV4
$pfxPath = "<path to replacement certificate (.pfx file)>"
$pfxPass = "<certificate password>"
certutil -addstore -enterprise -f -v root $STScert
Une copie d’écran pour exemple :
En cas d’erreur suivante lors de l’exécution du script :
Set-SPSecurityTokenServiceConfig : The term 'Set-SPSecurityTokenServiceConfig' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. At line:4 char:1 + Set-SPSecurityTokenServiceConfig -ImportSigningCertificate $stsCertificate + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : ObjectNotFound: (Set-SPSecurityTokenServiceConfig:String) [], CommandNotFoundException + FullyQualifiedErrorId : CommandNotFoundException
Set-SPSecurityTokenServiceConfig : The term 'Set-SPSecurityTokenServiceConfig' is not recognized as the name of a cmdlet, function, script file, or operable program.
Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:4 char:1
+ Set-SPSecurityTokenServiceConfig -ImportSigningCertificate $stsCertificate
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (Set-SPSecurityTokenServiceConfig:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException
Rajoutez la commande :
Durant l’exécution du script, la fenêtre suivante va apparaître. Cliquez sur le bouton « Yes to All »
A la fin du script, vous devez avoir le résultat suivant :
Il est possible de vérifier la bonne exécution du script en exécutant la commande suivante :
$stscertificate |fl
Vous devez normalement retrouver le « FirendlyName » de votre certificat.
La partie 7 se trouve ici !
Durant la plénière du jour 2, j’ai réalisé un rapport sur les données d’un sondage que certains d’entre vous ont sûrement rempli (http://aka.ms/J2). Ce sondage a été réalisé avec un outil que vous possédez sûrement déjà : OneDrive (anciennement Skydrive). Ce billet va détailler les étapes de la création du sondage et la récupération des informations dans Excel (avec Power Query, mais ça vous l’avez deviné !)
Connectez-vous à OneDrive. Puis cliquez sur “Créer” et “Enquête Excel”
La fenêtre ci-dessous s’affiche alors. Cette fenêtre est assez intuitive et vous permet de donner un titre et une description à votre sondage puis d’ajouter les questions voulues.
Pour chaque question, vous pourrez définir le sous-titre ainsi que le type de réponse souhaité. Cliquez sur le bouton “Terminé” pour valider vos modifications.
Ci-dessous, voici le résultat final du sondage que l’on a créé pour la plénière jour 2 des Techdays :
Après avoir terminé la création de l’enquête, un fichier Excel après sur votre OneDrive :
Ce fichier peut être consulté directement depuis le navigateur :
Votre compte OneDrive (/SkyDrive) peut être synchronisé avec votre poste de travail. Chose que j’ai fait afin de récupérer les données quasiment en temps réel.
Le fichier Excel se trouve donc physiquement sur mon disque et reste synchronisé avec les nouvelles données venant du sondage. Ceci est fort appréciable car il est possible, très facilement, de récupérer les données avec Power Query.
Avec Power Query, je récupère le fichier se trouvant dans mon dossier OneDrive (/SkyDrive) :
Après avoir récupéré les données, Excel me présente un aperçu du résultat :
En faisant un double clic sur “Enquête1”, Excel bascule dans l’éditeur de requête. Il ne reste plus qu’à mettre en forme les données. Cette partie n’est pas le sujet de ce billet.
Cependant, en ce qui concerne l’enquête, j’ai du avoir à traiter les entrées “Yes/No” des questions de type “Oui/Non”. Je m’explique, même si vous mettez “Oui/Non” comme valeur lors de la création du sondage, si aucune valeur n’est saisie par l’utilisateur, le formulaire retournera par défaut les valeurs anglaises . Il faut donc traiter ce cas avec Power Query
Une fois toutes vos transformations réalisées avec Power Query, il ne reste plus qu’à faire le rapport. Ici nous avons opté pour un rapport Power View pour son côté interactif.
Pour tester Windows Server 2012, Windows 8, SQL Server 2012 et SQL Server 2014 CTP2, vous pouvez télécharger gratuitement la version d’évaluation disponible sous la forme :
Comme mon environnement a été monté dans des machines virtuelles Azure, il est nécessaire, au niveau du DC, d’ouvrir un point de terminaison et de rajouter l’adresse publique de cette machine dans le DNS de mon hébergeur
Dans le portail Azure, dans la partie gauche, cliquez sur « Virtual machines »
Sélectionnez la machine virtuelle qui contient le domaine contrôleur et cliquez sur « Dashboard »
Dans la partie droite, « quick glance » notez l’adresse IP publique de la machine. Dans mon exemple : 137.135.167.160
En haut de l’écran, cliquez sur le menu « Endpoints»
Puis cliquez sur le bouton « Add », en bas de l’écran
Dans la fenêtre « Add Endpoint », sélectionnez « Add standalone endpoint »
Cliquez sur la flèche se trouvant en bas de la fenêtre
Dans le formulaire « Specify the details of the endpoint » :
Ci-dessous une copie d’écran avec le point de terminaison
· Au niveau du DNS de votre hébergeur, entrez l’adresse IP publique de votre domaine contrôleur
· Ci-dessous, une copie d’écran des entrées DNS de mon domaine :
La configuration de la fédération est terminée. Maintenant, pour tester l’installation, je me connecte à Office 365 avec un compte synchronisé depuis mon active directory. Dans cet exemple je vais utiliser le compte Hybridfranmer
Après avoir saisi le nom de l’utilisateur, au moment de passer à la saisie du mot de passe, je suis redirigé vers mon domaine et mon serveur AD FS pour assurer l’authentification. La fenêtre suivante s’affiche.
Dans cette fenêtre, je saisi le login et mot de passe comme si je me connectais sur mon domaine interne
Après avoir cliquez sur le bouton « Ok », je suis bien connecté au portail Office 365 avec l’utilisateur « HybridFranmer »
Maintenant que la fédération est utilisateurs est configurée, il reste encore à attribuer des licences et des droits aux utilisateurs synchronisés depuis notre active directory.
· Connectez-vous à Office 365 en tant qu’administrateur et allez dans « Office 365 admin center »
· Dans la partie gauche, cliquez sur « Users and groups »
· Cochez la case à gauche de l’utilisateur que vous souhaitez modifier
· Puis, sur la droite, cliquez sur le petit crayon pour éditer les propriétés de l’utilisateur
· Dans la partie gauche, cliquez sur « licences »
· Attribuez une licence à votre utilisateur
· Dans la partie gauche, cliquez sur « settings »
· Définissez les droits de votre utilisateur
· Cliquez sur le bouton « Save »
Voilà, la première étape, la configuration de l’authentification des utilisateurs est terminée. Maintenant, il faut paramétrer les SharePoint (sur site et Online) afin d’établir une relation de confiance entre les SharePoint.
Nous allons voir ceci dans le prochain billet: Configuration de l’authentification entre SharePoint Server 2013 et SharePoint Online. La partie 6 se trouve ici.
Nous allons passer maintenant à l’étape de la synchronisation des utilisateurs entre le domaine sur site et Office 365. A partir du portail d’Office 365 (https://portal.microsoftonline.com).
Sur le portail d’Office 365, dans « Office 365 admin center », cliquez sur « users and groups »
Dans la partie centrale de la fenêtre, au niveau de « Active Directory synchronization » cliquez sur « Set up »
Les différentes étapes sont listées dans la fenêtre « Set up and manage Active Directory synchronization ». Pour notre exemple, nous pouvons aller directement à l’étape 3
Au niveau de l’étape 3, cliquez sur le bouton « activate »
Dans la fenêtre « Do you want to activate Active Directory synchronization? », cliquez sur le bouton « Activate »
La synchronisation est maintenant activée
Cette synchronisation peut prendre du temps (entre 2h et 5 jours (si c’est trop long, n’hésitez pas à ouvrir un incident via le portail d’Office 365)). Il est possible de tester la synchronisation avec le script PowerShell ci-dessous :
Avant d’exécuter le script ; Pensez à installer le module Windows Azure AD
http://go.microsoft.com/fwlink/p/?linkid=236297
#Test de la synchronisation AD avec Office 365. False = KO $cred = Get-Credential Connect-MsolService -Credential $cred (Get-MsolCompanyInformation).DirectorySynchronizationEnabled
#Test de la synchronisation AD avec Office 365. False = KO
$cred = Get-Credential
Connect-MsolService -Credential $cred
(Get-MsolCompanyInformation).DirectorySynchronizationEnabled
· Dans mon cas, la synchronisation s’est faite au bout de 4h.
· Depuis un autre serveur que le domaine contrôleur, installez l’outil « Microsoft Directory synchronization » : à l’étape 4, cliquez sur le bouton “Download” :
Après avoir téléchargé l’outil « Microsoft Directory synchronization », installez-le sur un serveur différent du domaine contrôleur
Après l’installation, exécutez le programme
Sur la page d’accueil, cliquez sur le bouton « Next »
Dans la partie “Windows Azure Active Directory Credentials”, renseignez les informations de connexion à office 365
Cliquez sur le bouton « Next »
Dans la partie « Active Directory Credentials », renseignez un compte de domaine avec les droits d’administration sur l’active directory
Dans la partie « Hybrid Deployment », cliquez directement sur le bouton « Next »
Dans la partie, « Password Synchronization », Cliquez sur le bouton « Next » pour commencer la configuration
· Une fois la configuration terminée, Cliquez sur le bouton « Next »
Dans la partie « Finished », cliquez sur le bouton « Finish » pour commencer la synchronisation.
Une fois la synchronisation terminée, la fenêtre suivante apparaît :
Pour vérifier la synchronisation, confirmez la présence de vos comptes dans l’admin Center d’Office 365
Mais cette vérification peut aussi se faire avec l’outil « miisclient.exe », qui est installé en même temps de « Dir sync ».
Cet outil se trouve par défaut C:\Program Files\Windows Azure Active Directory Sync\SYNCBUS\Synchronization Service\UIShell
Cet outil permet d’obtenir des informations sur la synchronisation, et ce, de manière plus fine qu’une simple vérification de la présence des comptes sur SharePoint Online.
La suite de la mise en place se trouve ici !
http://www.microsoft.com/france/mstechdays/programmes/2014/fiche-session.aspx?ID=e5fb47bf-e009-4c65-b2c9-7e3120b82f8d
Bonne année à tous et au plaisir de vous voir lors d’un IT Camp SQL 2014/Power BI ou durant les Techdays!
Pour tester Windows Server 2012, Windows 8, SQL Server 2012 et SQL Server 2014 CTP1, vous pouvez télécharger gratuitement la version d’évaluation disponible sous la forme :
· Windows Server 2012 :
· SQL Server 2012 :
· Evaluation SQL Server 2014 CTP2 :
· Testez Azure gratuitement pendant un mois :
Suite à une question de ludo lm concernant mon billet sur la passerelle de Power BI, voici un article expliquant comment faire le “refresh” afin que les données soient persistantes dans le rapport. Et surtout, pour répondre vraiment à la question, pour montrer que les données sont bien stockées directement dans le fichier Excel.
Connectez vous sur votre site Power BI et ouvrez le fichier que vous souhaitez “rafraichir”. Afin de pouvoir éditer le fichier, cliquez sur le bouton en bas à droite de l’écran. “Voir le classeur en plein écran”
Une fois le fichier ouvert, cliquez sur “Modifier le classeur ” puis sur “Modifier dans Excel Web app”
Une fois le fichier ouvert en édition, cliquez sur “Données” puis “Actualiser toutes les données”
Cliquez sur le bouton “Ok” dans la fenêtre d’avertissement
Et hop ! le fichier est à jour.
Maintenant, vous pouvez fermer le fichier, voir même le navigateur. Lors de la prochaine ouverture du fichier, les données seront celles du dernier “refresh” car elles sont stockées directement dans le rapport.
Bonne année à tous et au plaisir de vous voir lors d’un IT Camp SQL 2014/Power BI.
Dans cette partie nous allons installer le rôle AD FS.
· Sur le domaine contrôleur, cliquez sur le bouton « Server Manager » :
· Dans la fenêtre « Server manager », cliquez en haut à droite sur le menu « Manage»
· Dans la fenêtre « Add Roles and Features Wizard », cliquez sur le bouton « Next »
· Dans la partie « Installation Type », sélectionnez « Role-based or feature-based installation »
· Cliquez sur le bouton « Next »
· Dans la partie « Server Selection », sélectionnez le serveur sur lequel vous souhaitez ajouter le rôle. Dans cet exemple j’ai sélectionné mon domaine contrôleur
· Dans la partie « Server Roles », cochez la case « Active Directory Federation Services»
· Juste après avoir coché la case « Active Directory Federation Services », la fenêtre suivante apparaît :
· Cliquez sur le bouton « Add Features »
· De retour dans la fenêtre « Add Roles and Features Wizard », cliquez sur le bouton « Next »
· Dans la partie « Features », cliquez sur le bouton « Next »
· Dans la partie « AD FS », cliquez sur le bouton « Next »
· Dans la partie « Role Services », cliquez sur le bouton « Next »
· Dans la partie « Confirmation », cliquez sur le bouton « Next »
· Cliquez sur le bouton « Close »
· Une fois l’installation terminée, si la fenêtre « Server Manager » n’est pas déjà ouverte, cliquez sur le bouton « Server Manager » :
· Dans la fenêtre « Server manager », cliquez en haut à droite sur le menu « Tools»
· Sélectionnez « AD FS Management »
· Dans la fenêtre AD FS, dans la partie centrale, cliquez sur « AD FS Federation Server Configuration Wizard »
· La fenêtre « AD FS Federation Server Configuration Wizard » s’ouvre
· Dans la partie « Welcome », cliquez sur “Create a new Federation Service”
· Dans la partie « Select Deployment Type », sélectionnez « Stand-alone federation server »
· Dans la partie « Federation Service Name », vérifiez la présence du certificat le nom du domaine fédéré
· Dans la partie « Summary », cliquez sur le bouton « Next »
Dans la partie « Results », à la fin de l’installation, cliquez sur « Close »
Au niveau de la fenêtre Ad FS, il reste une étape de configuration « Required : Add a trusted relying party ». Cette étape sera réalisée un peu plus tard via un script PowerShell.
Il est déjà possible de vérifier l’installation en naviguant sur la page : https://<FQDNServer>/FederationMetadata/2007-06/FederationMetadata.xml
Dans mon exemple FQDNServer = ITCampDC1 : https://ITCampDC1.itcamp.demos.com/FederationMetadata/2007-06/FederationMetadata.xml
Ci-dessous une copie d’écran du premier résultat. Ignorez le message de sécurité et cliquez sur « Continue to this website (not recommended) »
Le résultat suivant doit alors apparaître dans le navigateur :
Après avoir fait les premiers paramétrages dans notre infrastructure se trouvant sur site, nous allons continuer en paramétrant Office 365.
L’accès au portail Office 365 se fait via l’adresse : https://portal.microsoftonline.com/
Après s’être connecté en tant qu’administrateur, vous devez arriver dans la fenêtre « Office 365 admin center »
· Dans la partie gauche, cliquez sur le menu « Domains »
· Cliquez sur le lien « Add a domain »
· La fenêtre « Add a domain to Office 365 » apparaît
· Cliquez sur le bouton « Start step1 »
· Dans la fenêtre « Add a domain », « provide domain name », entrez votre nom de domaine public
· Dans la partie « Confirm ownership », sélectionnez votre fournisseur de domaine
· Si comme dans cet exemple, le fournisseur n’est pas listé, sélectionnez « General instructions »
· L’interface affiche alors toutes les instructions et information nécessaire pour réaliser les entrées dans le DNS de votre fournisseur
· Dans cet exemple, mon domaine est hébergé chez Gandi
· Dans le DNS Gandi, j’ai créé une nouvelle version de la zone DNS afin de pouvoir rajouter l’enregistrement ci-dessous :
· Après validation, voici une copie d’écran des entrées dans le DNS Gandi :
· Une fois les modifications faîtes du côté de votre DNS, cliquez sur le bouton « Done, verify now »
· Vous allez avoir la fenêtre suivante affichant « Verification DNS record not found »
· Au bout d’un moment (un peu plus d’une heure dans mon cas), la vérification du DNS sera faîte. Et vous pourrez passer à l’étape numéro 2 pour l’ajout des utilisateurs et l’affectation des licences
· Cliquez sur le lien « Add users and assigne licenses »
· Sélectionnez « I don’t want to add users right now ». Les utilisateurs seront rajoutés plus tard avec l’outil “DirSync »
· Cliquez sur le bouton « Start step 3 »
· Cette étape n’est pas nécessaire mais permet la finalisation de la procédure de connexion du SharePoint Online avec le domaine public
· Dans la partie « set domain purpose », sélectionnez « SharePoint Online »
· Suivez les instructions et cliquez sur le bouton « Next »
· Dans cette fenêtre, les informations concernant les modifications du DNS sont affichées
· Ci-dessous, une copie d’écran après ajout dans le DNS de mon domaine public
· Cliquez sur le bouton « Finish »
· Le domaine public est maintenant lié à Office 365
Jusqu’à maintenant, nous avons :
Dans cette partie de l’article, nous allons configurer AD FS pour fédérer les demandes provenant d’Office 365. Nous aurons besoins des prérequis suivants :
Pour rappel, sur le serveur AD FS, il reste encore une action à réaliser : « Add a trusted relying party », Qui peut être faîte via PowerShell.
· Dans la barre des tâches, faîtes un clic droit sur l’icône PowerShell , puis sélectionnez « Run ISE as Administrator »
· Entrez les commandes suivantes :
Connexion à Office 365
Import-Module MSOnline
Connect-MsolService
Puis connectez AD FS à Office 365 :
Set-MsolAdfscontext -Computer <AD FS server>
Convert-MsolDomainToFederated -DomainName <domain name>
· Voici la copie d’écran de ma fenêtre PowerShell
· Le Serveur AD FS est maintenant configuré pour fédérer les demandes de connexion venant d’Office 365.
· Ci-dessous une copie d’écran de la fenêtre AD FS. La dernière étape de configuration n’est plus présente dans la partie centrale de la fenêtre
Maintenant, nous allons mettre à jour les metadata. Cette mise à jour est disponible depuis la page suivante : http://gallery.technet.microsoft.com/scriptcenter/Office-365-Federation-27410bdc
· Téléchargez le script PowerShell et exécutez-le.
===============================================================================
Si vous avez un message disant :
« File C:\Users\Administrator\Downloads\3_Office 365 Federation Metadata Update Automation Installation\O365-Fed-MetaData-Update-Task-Installation.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170. + CategoryInfo : SecurityError: (:) [], ParentContainsErrorRecordException + FullyQualifiedErrorId : UnauthorizedAccess »
« File C:\Users\Administrator\Downloads\3_Office 365 Federation Metadata Update Automation Installation\O365-Fed-MetaData-Update-Task-Installation.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.
+ CategoryInfo : SecurityError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : UnauthorizedAccess »
Dans Winsows PowerShell ISE, recopiez et exécutez la commande suivante:
Ci-dessous une copie d’écran de mon ISE
Lors de l’exécution du script, il vous sera demandé le mot de passe administrateur de votre compte Office 365
Après avoir cliqué sur le bouton “Ok”, le script PowerShell continue et doit afficher “Success” :
Puis le script vous demande le mot de passe d’un compte ayant des droits d’administration sur votre domaine sur site.
Voilà pour la partie ADFS. La suite dans le prochain article !
Afin d’illustrer l’article “Power BI : Posez les bonnes questions à vos rapports avec Power Q&A”, voici une vidéo :