Sécurité dynamique dans les cubes SSAS avec SQL Server 2012

Avec l’ouverture des données Power BI Self-Service vers les utilisateurs (vu dans plusieurs de mes missions) les métiers veulent gérer eux même les droits ou périmètres sur de nombreux utilisateurs finaux (>1000).

En effet dans certains domaines, nous avons une forte contrainte de confidentialité des données contenues dans les cubes SSAS. Les juridictions qui doivent s’appliquer peuvent être changées avec des cadences variables allant de la journée, à des cadences inférieures (temps réel). Pour cela, l’ensemble de la sécurité mise en œuvre est stocké dans la source de données du cube, qui détermine qui peut voir quoi.

A travers cet article, nous allons voir comment rendre dynamique une sécurité de cube SSAS basée sur l’appel à une procédure stockée.

Lire la suite

[70-461] Querying Microsoft SQL Server 2012 – Avril 2014

Suite au passage de la certification 70-461 « Querying Microsoft SQL Server », voici mon compte rendu.

Concernant le contenu de l’examen c’est la même chose que ce qu’on dit mes collègues. Pour ne pas faire doublon je vous renvoie vers leurs posts :

 

Les questions m’ayant posées le plus de problèmes sont celles portant sur la différence entre une procédure stockée et une UDF (User-Defined Function).

L’UDF et les procédures stockées sont toutes deux des objets SQL Server qui stockent une ou plusieurs instructions T-SQL sous un seul nom, prenant la forme d’une routine exécutable. Même si vous pouvez mettre en œuvre la même fonctionnalité ou une similaire en utilisant soit une UDF ou soit une procédure stockée, le code sera significativement différent selon la technique que vous choisissez.

Les avantages de l’utilisation des procédures stockées ou des fonctions sont les suivants :

  • Vous pouvez créer la procédure ou la fonction une fois, la stocker dans la base de données et l’appeler autant de fois que vous le voulez dans votre programme.
  • Les procédures stockées et les fonctions Transact-SQL définies par l’utilisateur réduisent le coût de compilation du code Transact-SQL en mettant en cache les plans pour les réutiliser au cours d’exécutions répétées. Elles n’ont donc pas besoin d’être ré-analysées et ré-optimisées à chaque utilisation, ce qui réduit nettement les durées d’exécution.
  • Les commandes d’une procédure ou d’une fonction sont exécutées comme un seul lot de codes. Cela peut réduire considérablement le trafic réseau entre le serveur et le client, car seul l’appel pour exécuter la procédure ou la fonction est envoyé sur le réseau.

 

Voici les principales différences entre les fonctions et les procédures stockées :

  • Une UDF doit renvoyer un seul et unique jeu de résultat. Une procédure stockée peut quant à elle retourner une valeur ou même plusieurs jeux de résultats ou voir même ne rien retourner du tout.
  • Vous pouvez utiliser une UDF directement dans une instruction SELECT ainsi que dans les clauses ORDER BY , WHERE ou FROM, tandis que vous ne pouvez pas utiliser une procédure stockée dans une instruction SELECT.
  • Une UDF ne peut pas utiliser une fonction non déterministe (qui retourne un résultat différent avec les mêmes paramètres d’entrée) comme GETDATE(), NEWID(), ou RAND(), alors qu’une procédure stockée peut utiliser ces fonctions.
  • Une UDF ne peut pas modifier les variables d’environnement du serveur; une procédure stockée peut .
  • Une UDF arrête toujours exécution du code en cas d’erreur, alors qu’avec une procédure stockée on peut continuer à l’instruction suivante en utilisant le code de gestion d’erreur approprié.
  • Une UDF peut être appelée à partir de procédure alors qu’une procédure ne peut pas être appelée à partir de l’UDF.
  • Une procédure ne peut pas être utilisée dans une instruction SELECT alors qu’une fonction peut être intégrée dans une instruction SELECT.

 

Il existe plusieurs types de fonctions définies par l’utilisateur :

  • Fonction scalaire :Les fonctions scalaires définies par l’utilisateur retournent une valeur de donnée unique dont le type est défini dans la clause RETURNS. Une fonction scalaire incluse ne contient pas de corps ; la valeur scalaire est le résultat d’une instruction unique. Le corps d’une fonction scalaire à instructions multiples, défini dans un bloc BEGIN…END, contient une série d’instructions Transact-SQL qui retournent la valeur unique. Le type de retour peut être n’importe quel type de données à l’exception de text, ntext, image, cursor et timestamp.
  • Fonctions tableLes fonctions table définies par l’utilisateur retournent un type de données table. Une fonction table incluse ne contient pas de corps ; la table est le jeu de résultats d’une instruction SELECT unique.
  • Fonctions inlineLes fonctions définies par l’utilisateur inline sont un sous-ensemble de fonctions définies par l’utilisateur qui retournent un type de données table. Les fonctions inline peuvent offrir une fonctionnalité équivalente à celle des vues paramétrées.
  • Fonctions systèmeSQL Server fournit de nombreuses fonctions système que vous pouvez utiliser pour effectuer diverses opérations. Elles ne peuvent pas être modifiées.

 

 

 

70-461: Querying Microsoft SQL Server 2012

Vous trouverez ci-dessous quelques points utiles pour ceux qui souhaitent passer la certification 70-461

Il y’avait 2 types de questions :

–        Les QCM : un ou plusieurs choix possibles pouvant aller jusqu’à 8 choix !

–        Les constructions de batch : création d’une requête par drag and drop

Plusieurs sujets retombaient sur de nombreuses questions:

  • Utilisation des fonctions d’ordre : rank(), dense_rank(), ntile().
  • Rendu XML des requêtes FOR XML. Bien connaître la subtilité entre FOR XML RAW vs AUTO vs PATH avec l’option ELEMENTS ou pas.
  • Gestion d’erreurs : utilisation de @@ERROR, XACT_STATE(), @@TRANCOUNT
  • Les triggers surtout l’utilisation de « instead of »

Eléments qui tombent dans une ou deux questions :

  • Incrémentation automatique avec cycle.
  • Utilisation de SCHEMABINDING

Eléments qui m’ont posés problèmes :

  • A priori l’opérateur « + » est mieux que la somme …
  • « NOT IN » équivalent à « <> ALL »
  • Différence entre UNION et UNION ALL, le dernier garde les doublons

Voilà pour mon retour,

Bon courage aux prochains !

Ezra

Certification 70-457 : Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1

Il s’agit d’un examen réservé aux personnes déjà certifié SQL Server 2008 qui regroupe les deux examens suivants :
– 70-461 : Querying Microsoft SQL Server 2012
– 70-462 : Administering Microsoft SQL Server 2012 Databases
Il comporte 50 questions décomposées en 2 ensembles :
– 25 questions pour la partie développement (70-461)
– 25 questions pour la partie administration (70-462)
Concernant la première partie, vous trouverez toutes les informations sur le post suivant :
http://mcnextpost.com/2014/03/28/certification-ms-querying-sql-server-2012-70-461/
Concernant la deuxième partie, j’ai relevé différents points d’attention
– Les différents mécanismes de réplication (Snapshot, Transactional, etc.)
– Le principe du Database Mirroring
– Les procédures de sauvegarde (Backup Differential, etc.)
– L’installation complète d’une plateforme SQL Server (Database Mail, Cluster, etc.)
– Le « Always On »
– Les différents outils de monitoring (Performance monitor, SQL Server Profiler, etc.)
– L’audit des instances SQL Server (Server Audit, Policy, etc.)
– La création de jobs avec l’agent SQL Server
Une certification pas si simple pour les personnes ne pratiquant pas l’administration SQL Server au quotidien.

Certification MS Querying SQL SERVER 2012 70-461

Suite à mon passage de la certification 70-461 voici un « petit » récapitulatif des parties qu’il est nécessaire de réviser pour obtenir la certification :

CTE :

  • Revoir les notions de requêtes récursives
  • Avantage sur les curseurs

XML (bloc de 5 questions) :

Savoir effectuer des requêtes XML à l’aide des opérateurs suivants :

  • Query()
  • Value()
  • Exist()
  • Nodes()
  • Modify()

Effectuer des requetes a partir des données SQL server en utilisant les fonctions:

  •   FOR XML RAW
  •   FOR XML AUTO
  •   FOR XML EXPLICIT
  •   FOR XML PATH

Connaitre les fonctions  :

  • GROUPING SET / ROLLUP/ CUBE
  • LAG / LEAD
  • UNBOUNDED PRECEDING (FIRST/LAST)

Utiliser la fonction MERGE :

  • When matched then
  • When not matched
  • When matched by source

Savoir utiliser et connaitre les différences entre :

  • PARSE / TRY PARSE
  • CAST / TRY CAST
  • CONVERT / TRY CONVERT

Connaitre les niveaux de transactions :

  • READ UNCOMMITED
  • READ COMMITED
  • SERIALIZABLES
  • REPEATABLE READ
  • XACT_STATE ()
  • @@TRANCOUNT

Savoir utiliser les vues revoir notamment les notions suivantes :

  • Vues indexés
  • Shemabinding
  • VIEW_METADATA
  • Encryption
  • CHECK_OPTION

Les index

  • Clustered index
  • Non clustered index
  • Reoganize
  • Rebuild
  • Fill factor

Connaitre Les fonctions :

  • RANK/DENSE_RANK/NTILE (bloc de 5 questions)
  • LAG/LEAD
  • ROWNUMBER

Connaitre l’ensemble des notions de triggers

  • Instead of
  • For update …

Schéma de base de données (bloc de 5 questions)

  • PK
  • FK
  • Contraintes
  • Newid()
  • newSequentientialID()

Connaitre les objets séquences SQL SERVER

  • NEXT VALUE
  • CYCLE

Avoir de très bonne notions de requetage SQL.

L’examen est composé de la façon suivante :

  • QCM 50 %
  • Requête à remettre en ordre sous forme de « Drag & Drop » 25 %
  • Etude de cas 25 %

Bien évidement toutes ces informations sont données à titre indicatives, néanmoins je vous invite à bien réviser les parties indiquées.

Migration vers SQL 2014

  • Mercredi 12 février : 17h45-18h30
  • Salle : 251
  • Audiences : Professionnels de l’IT
  • Thèmes : Infrastructure des systèmes d’information
  • Intervenant Hoda EL HADDAD (Microsoft France), Pascale Doz (Pascal Doz Consulting)
  • Niveau : Intermédiaire (200)

Migration de bases hétérogènes vers SQL Server 2014 : – Comment maîtriser la migration de vos bases vers SQL server 2014 – Quels sont les outils Microsoft ? – Quels bénéfices en tirer d’une telle migration à court et moyen terme ? – Quelle démarche adopter pour minimiser les risques liés à la migration des applications ?

1.     Origine de la migration :

  • Nouvelles fonctionnalités
  • Evolution de l’architecture
  • Support
  • Baisse des couts des éditeurs
  • Amélioration des performances avec l’arrivée de L’IN Memory
  • Haute disponibilités
  • Gestion de la BI amélioré et Gestion de la partie BIG DATA
  • Déploiement dans le cloud

2.     L’apport de SQL 2014 :

  • Amélioration des performances avec l’arrivée de L’IN Memory
  • Haute disponibilités
  • Gestion de la BI amélioré et Gestion de la partie BIG DATA
  • Déploiement dans le cloud

3.     Les étapes nécessaires à une migration

1. Inventaire (Microsoft Assetement and planning MAP)

  • Appli
  • Infrastructure des données

2. Disponibilité / indisponibilité pendant la phase d’upgrade (Distributed / replay Réplication / intégration Services)

  • Retour en arrière
  • Gestion de la performance
  • Liste des composants à migrer

3. Choix des méthodes

  • In place
  • Side by Side

4. Exécution de la migration

4.     Démo : Migration SQL SERVER 2000 => 2014 les étapes clés

Pour réaliser une migration de SQL server 2000 vers 2012 ou 2014 il est nécessaire de passer par une étape intermédiaire : SQL 2005 / 2008 / 2008 R2

migration

Les principales origines de cette étape palier sont :

  • la compatibilité des package DTS sont possible jusqu’en 2008 R2
  • les nouvelles normes SQL qui ont changé certaines requetés ce qui les rends incompatible avec les dernières versions.

Dans notre démo nous avons pu suivre les étapes nécessaires pour une migration SQL Server 2000 vers 2014 :

Nous avons utilisé l’outil SQL server 2008 upgrades advisor qui permet de vérifier la compatibilité des bases présente sur l’instance SQL 2000 vers la base SQL 2008.

Ainsi nous obtenons le résultat suivant :

 upgrade advisor

Source : msdn

Une fois les anomalies levé et corrigé la migration vers la base 2008 peut avoir lieu par l’intermédiaire d’un simple backup restore.

Cette étape passé, nous avons augmenté les compatibility level des bases afin d’être sûr que le code utilisé soit compatible, puis nous avons mis en place des traces sur l’ancienne instance et sur la nouvelle instance afin de comparer les différences de performance.

Pour le passage de SQL server 2008 R2 vers 2014 nous avons utilisé l’outil MAP Microsoft Assessment and planning toolkit  pour vérifier la compatibilité des données et connaitre l’impact de la migration sur SQL Server 2014.

Pour ce faire, nous avons mis en place  des traces de simulation sur SQL 2014  avec de la génération de charge (Distributed Replay Utility) pour vérifier que les requêtes fonctionne et que les performances serait bonne sur la nouvelle instance.

Après vérification, nous avons constaté qu’il n’y avait aucune contre-indication concernant la migration, nous avons donc effectué un backup restore permettant d’effectuer la migration.

5.     Démos Migration Oracle => SQL Server 2014

Pour réaliser cette démo, Pascale Doz a utilisé Microsoft Migration Assistant afin vérifier la compatibilité entre les bases à migrer. Après cette assurer de la compatibilité et avoir corrigé les quelques anomalies rencontré, l’outil permet de réalisé la migration quasi automatiquement sous réserve d’avoir créé une base au préalable dans la nouvelle instance SQL Server 2014.

6.     Conclusion :

Cette session permet d’effectuer rapidement un tour sur les étapes importantes d’une migration, mais ne reflète pas complètement les problèmes rencontrés lors d’une migration :

–        Problématique de sécurité

–        Problématique de temps de correction des anomalies pour répondre aux nouvelles normes SQL ou aux problèmes de collation

–        …

Lien utile : http://technet.microsoft.com/fr-fr/magazine/gg454217.aspx

Vidéo

70-467 – Designing Business Intelligence Solutions with Microsoft SQL Server 2012 – Novembre 2013

La préparation

Comme pour l’examen 70-466, il n’existe pas de training kit officiel. Le programme couvre de l’infrastructure aux différentes couches BI ( SSAS, SSRS, SSIS, MDS/DQS, intégration à SharePoint,… ). Etant intéressé par l’infrastructure, j’ai pu pratiquer Always On et la réplication mais pas dans le cadre de production.  Je ne peux donc que conseiller d’étudier les aspects d’authentification et de délégation et les aspects d’architecture lié à la BI.

L’examen

A peine une pause et j’enchainais sur cet examen que je redoutais et je ne fût pas déçu. Cet examen comporte 2 études de cas avec 10 questions chacun et 30 autres questions.

Les « case study » décrivent l’architecture d’une société et les options envisagées pour la faire évoluer ( cas d’une migration à 2012) et les problèmes rencontrés. Les questions sont assez large, de la validation de choix d’architecture à la résolution de problème en passant par les bonne pratiques. Les questions sont assez difficiles.

Les 30 questions suivantes sont aussi difficiles et sont assez larges sur les sujets de cet examen.

Au final, le MCSE est acquis et le soulagement fût total au moment de valider mon examen.