SQL Server chez les clients – Les nouveautés SQL Server 2014

  • Article

SQL Server 2014 est sorti le 1er avril 2014 avec une série de nouvelles fonctionnalités, pour une nouvelle version de la plateforme de données résolument orientée vers la haute performance, le cloud et l’analyse perspicace de tous types de données.

Introduction

SQL Server 2014 répond aux enjeux métiers des entreprises en fournissant :

  • De la performance pour les projets critiques grâce au moteur « in memory » couvrant maintenant l’OTLP, le datawarehouse et la BI !

  • Encore plus de disponibilité du service et de la sécurité

  • Une évolution vers des plateformes hybrides grâce à la possibilité de combiner du « On Premise » et du « Cloud » dans la même architecture, permettant par la même des gains financiers

  • Une analyse perspicace de tous les types de données disponibles sous de nombreuses formes et permettant une restitution extrêmement pertinente à travers les outils classiques tels qu’Excel

 

In-Memory

Une des nouveautés les plus importantes de SQL 2014 est sans doute le moteur de base de données relationnelle en mémoire, issue du projet « Hekaton ».

Cette nouveauté finie de compléter la chaine du « In-memory » initié par SQL Server 2008 R2 avec PowerPivot (moteur Vertipaq), puis avec SQL 2012 et le moteur xVelocity (qui complète et remplace Vertipaq).

Pour un bref rappel, le moteur xVelocity est en réalité une famille de moteurs composée de :

xVelocity in-memory analytics (remplaçant de Vertipaq) : Ce moteur d’analyses en mémoire est porté par Analysis Services en mode tabulaire ainsi que par PowerPivot. Ce moteur compresse les données et les stocke en colonnes.xVelocity memory columstore index : Compresse et stocke les index en mémoire et en colonnes afin d’améliorer les performances lors d’exécution des requêtes.

xVelocity memory columstore index, dans la version SQL 2012, est très utile pour les datamart ou datawarehouse qui ne nécessitaient pas de mise à jour régulière.

En effet, l’activation de cette fonctionnalité ne permettait plus l’écriture dans la table sur laquelle l’index était activé. Cette limitation est levée avec SQL 2014 !

SQL 2014 fournie maintenant nativement une solution complète « in-memory » (OLTP, DataWarehouse et BI), totalement intégrée à la solution.

Cette intégration permet l’utilisation de la technologie « in-memory » sans être obligé de réécrire les applications existantes.

 

Améliorations Haute Disponibilité / Disaster Recovery

AlwaysOn - Availability Group 

  • Augmentation du nombre de réplicas à 8 (2 en mode synchrone)

  • En cas de déconnexion du réplica principal, de perte du quorum, les réplicas secondaires restent disponibles en lecture

  • Extension d’un groupe de disponibilité vers Azure (Hybrid cloud).

    SQL 2014 permet l’intégration de la fonctionnalité AlwaysON groupe de disponibilité, avec les machines virtuelles hébergées dans le service infrastructure de Windows Azure (IaaS).

    Autrement dit, vous pouvez avoir une solution SQL AlwaysON groupe de disponibilité, avec une partie de votre infrastructure sur site et un ou plusieurs serveurs secondaires hébergés dans le nuage Windows Azure.

    Ce type de solution permet d’étendre les scénarios de « disaster recovery » à moindres coûts et aussi de « décharger » l’utilisation du serveur primaire, en exécutant par exemple, les tâches de sauvegardes et de reporting sur les secondaires hébergés dans le cloud.

Failover Clustering Instances

  • Support des disques Cluster Shared Volume (CSV) à partir de Windows Server 2012  (uniquement pour le moteur de bases de données relationnelles)

    Les avantages :

    • Plus de limitation due au nombre de lettres de lecteurs

    • Installation de plusieurs instances SQL Server sur le même disque

    • En cas de perte de connexion du nœud avec le stockage, redirection vers un autre nœud du cluster en s’appuyant sur un mécanisme de reprise propre à SMB

    • Bascule plus rapide car les opérations de démontage/montage des disques ne sont plus nécessaires

  • Il n’est plus nécessaire de disposer d’un compte d’installation disposant de droits pour créer des comptes d’ordinateur dans l’AD, simplifiant ainsi l’administration et les procédures d’installation.

 

 

Nouveautés principales du moteur

  • Un nouveau moteur d’estimation des cardinalités est présent dans SQL Server 2014, permettant de fiabiliser l’estimation des cardinalités et d'être plus prédictible sur les performances.

  • Mise en place de statistiques au niveau des partitions, permettant d’affiner l’évaluation de l’accès à de nouvelles données dans une table partitionnée.

  • Opérations « OnLine »

    • Les partitions de tables partitionnées peuvent maintenant être reconstruites individuellement.

    • L'option ONLINE = ON contient maintenant une option WAIT_AT_LOW_PRIORITY qui vous permet de spécifier la durée pendant laquelle le processus de reconstruction doit attendre les verrous nécessaires.

      Cette option permet également de configurer la fin des processus de blocage liés à cette instruction de reconstruction.

  • La base TempDB est caractérisée notamment par un mécanisme de logging particulier en comparaison des autres bases de données, permettant d'assurer un meilleur niveau de performance.

    Avec SQL Server 2014, les pages de données d'objets temporaires peuvent être allouées, utilisées et libérées sans nécessiter d'écriture sur disque, ce qui permet un gain de performance significatif notamment sur : 

    • La création d’index avec l’activation de l'option 'sort in tempdb'

    • Les opérations de bulk insert dans des tables temporaires.

  • L’instruction SELECT INTO en SQL Server 2014 bénéficie maintenant d’un plan d’exécution permettant la parallélisation de l’opération.

 

Index columnstore cluster

Un index columnstore cluster permet d'améliorer les performances des requêtes de type datawarehouse (sum, count, group by, union, not in…) et d’apporter une compression de données de par son mode de stockage.

Quelques comparaisons d’exécution  :

Gain d’espace : Quelques comparaisons avec une table de 100 millions de lignes

Note : ALTER INDEX … REBUILD possède maintenant une nouvelle option de compression des données COLUMNSTORE_ARCHIVE qui compresse encore plus les partitions constituées d'un index columnstore. 

 

Tables en mémoire optimisées

Cette fonctionnalité permet d’optimiser particulièrement les courtes transactions OLTP en donnant la possibilité de placer des tables en mémoire afin :

  • De dépasser les limitations causées par le mécanisme de verrouillage interne (verrou de type latch), permettant ainsi d’augmenter le nombre de "batch request" par seconde drastiquement (record atteint à plus de 250 000)

  • De contourner les contentions I/O en passant au « in memory »

  • D’optimiser les accès en utilisant des procédures stockées compilées nativement.

 

Extension dans le cloud

SQL Server 2014 s'ouvre au Cloud à travers :

  • La fonctionnalité AlwaysON Availability group qui permet d’avoir un replica asynchrone dans Windows Azure (Iaas)

  • Les backups qui peuvent être effectués dans Windows Azure (Blob Storage Service)

  • La possibilité d'attacher une base relationnelle à des fichiers de données placés dans Windows Azure (Blob Storage Service)

 

Utilisation des disques SSD

SQL Server 2014 permet l’utilisation de disques SSD pour l’extension du « buffer pool » (data cache + plan cache).

 

 

Optimisations du gouverneur de ressources pour le contrôle des E/S physiques

Le gouverneur de ressources vous permettait de spécifier des limites sur la CPU et la mémoire. Dans SQL Server 2014, il est maintenant possible de spécifier une limite sur les I/Os en utilisant les nouveaux paramètres MIN_IOPS_PER_VOLUME et MAX_IOPS_PER_VOLUME pour un pool de ressources spécifique.

 

 

Sécurité et délégation des rôles

SQL 2014 permet une gestion plus fine des droits et permet, entre autre, de créer des rôles administrateurs ayant des restrictions sur des données sensibles. Ce type de rôle donne une agilité supplémentaire dans la gestion des bases, tout en donnant des droits administrateurs à des utilisateurs n’étant pas « sys admin » et en restreignant l’accès aux données sensibles et personnelles.

3 nouvelles permissions seront disponibles : “CONNECT ANY DATABASE”, “IMPERSONATE ANY LOGIN” et “SELECT ALL USER SECURABLES”.

Exemple d’utilisation :

-- Création d’un login

CREATE LOGIN LoginDemo WITH PASSWORD='strongpassword'

-- Création d’un rôle avec limitation des accès

CREATE SERVER ROLE MyLimitedAdmin AUTHORIZATION sysadmin 

-- Ajout de LoginDemo dans le rôle nouvellement créé.

ALTER SERVER ROLE MyLimitedAdmin ADD MEMBER LoginDemo

-- Ajoût de la permission CONTROL SERVER au rôle. Cette permission permet la gestion des bases données au niveau de l’instance

GRANT CONTROL SERVER to MyLimitedAdmin

-- Cependant, les accès seront restreints, et le rôle ne pourra voir aucune donnée utilisateur mais il permettra la réalisation des tâches d’administrations courantes..

DENY SELECT ALL USER SECURABLES to MyLimitedAdmin

 

Pour aller plus loin

Tester et évaluer SQL Server 2014 J : https://technet.microsoft.com/en-US/evalcenter/dn205290.aspx

 

 

L'expertise Microsoft Consulting Services au service de ses clients

Les consultants et architectes de l’équipe MCS SQL/BI sont formés pour mettre en œuvre des projets engageant SQL Server 2014.

MCS travaille déjà étroitement sur plusieurs projets avec des clients dans les domaines de la Banque, Assurance ou Telecom.

Pour plus d’informations sur les offres packagées Microsoft Consulting Services, rendez-vous sur https://www.microsoft.com/france/services

Plus d’informations sur les blogs « SQL Server chez les clients ».

 

Hervé MARIE, Senior Consultant Data Insight, Microsoft Consulting Services

Après une expérience de 9 ans en tant que responsable technique de compte en développement, j’ai rejoint MCS en 2006 pour me spécialiser en SQL Server afin d’accompagner nos clients dans la mise en œuvre d’architecture hautement disponible, d’architecture SQL & BI et des projets de migration Sybase.