Requete multi serveur

Requête multi serveurs avec SSMS

Dans le métier de DBA ou de développeur, il arrive d’avoir besoin d’exécuter une même requête sur plusieurs instances SQL Server. On pourrait se connecter sur chaque instance et exécuter la requête manuellement mais SQL Server Management Studio offre une possibilité bien pratique : la requête multi serveurs.

Registered Servers

Tout d’abord, dans les prérequis, il faut enregistrer les instances SQL dans les Registered Servers de SSMS.

Pour afficher cette fenêtre, il faut l’activer dans le menu « View » puis « Registered Servers ».

Dans mon exemple, je vais créer un groupe nommé « TEST » :

New Server Group

Add New Server Group

Dans ce groupe, je vais enregistrer quelques instances :

New Server Registration

Add New Server Registration

J’obtiens donc ceci :

Registered ServersExécuter une requête multi serveurs

Maintenant que j’ai un groupe d’instance, je peux exécuter une requête multi serveurs. Je vais exécuter une requête simple pour vérifier que mes 4 serveurs sont bien tous à la même heure.

New Query

En cliquant sur « New Query », cette fenêtre s’ouvre. C’est le requêteur habituel SSMS mais ils y a quelques différences:

New Query Windows

La couleur du bandeau est rose/rouge pour bien montrer que je suis connecté à plusieurs serveurs. J’ai un « various logins » car j’utilise des logins différents sur chaque serveur.

newquerymultiresult

En plus d’afficher l’heure, SSMS a inséré une colonne « Server Name ».

Bien évidemment, on pourrait exécuter des requêtes ou procédures plus complexes. Je vous laisse imaginer les possibilités…

Purger la base SSISDB

Purger la base SSISDB

Lorsque nous installons un catalogue SSIS sur nos instances, SSIS crée sa propre base de données SSISDB. Elle contient notamment les logs d’exécution de tous les packages. Après plusieurs mois d’activité, on peut constater que cette base peut grossir énormément.

Configurer la rétention

Dans SSMS, on peut configurer une période de rétention dans les propriétés d’un catalogue SSIS.

retention01

Il faut préciser le nombre de jours dans « Retention Period (days) » et s’assurer que « Clean Logs Periodically ». Par défaut, le nombre de jours est fixé à 365.

retention02

Le Job « SSIS Server Maintenance Job » utilise cette configuration pour purger la base SSISDB régulièrement. C’est la procédure stockée internal.cleanup_server_retention_window qui s’occupe de faire le ménage.

retention03

Script Maison

Voici un script qui permet de purger la base SSISDB en précisant un nombre de jours de rétention. Oui c’est toujours plus marrant de le faire soit même mais ce n’est surement pas supporté par Microsoft 🙂

USE SSISDB;
SET NOCOUNT ON;

DECLARE @NbJourRetention int = 90;

IF object_id('tempdb..#OperationASupprimer') IS NOT NULL
BEGIN
DROP TABLE #OperationASupprimer;
END;

CREATE TABLE #OperationASupprimer
(
operation_id bigint NOT NULL PRIMARY KEY
);

INSERT INTO
#OperationASupprimer
(
operation_id
)
SELECT
IO.operation_id
FROM
internal.operations AS IO
WHERE
IO.start_time < DATEADD(day, -@NbJourRetention, GETDATE());

DELETE T
FROM internal.event_message_context AS T
WHERE EXISTS( SELECT * FROM #OperationASupprimer oas WHERE oas.operation_id = T.operation_id);

DELETE T
FROM internal.event_messages AS T
WHERE EXISTS( SELECT * FROM #OperationASupprimer oas WHERE oas.operation_id = T.operation_id);

DELETE T
FROM internal.operation_messages AS T
WHERE EXISTS( SELECT * FROM #OperationASupprimer oas WHERE oas.operation_id = T.operation_id);

DELETE T
FROM internal.operations AS T
WHERE EXISTS( SELECT * FROM #OperationASupprimer oas WHERE oas.operation_id = T.operation_id);

Dans le script ci-dessus, la base ne contiendra plus que les 90 derniers jours de log. Il suffit de changer la valeur de la variable @NbJourRetention pour une période de rétention plus ou moins importante.

Si vous n’avez jamais purgé la base SSISDB, exécutez cette requête en diminuant progressivement le nombre de jours de rétention et en sauvegardant le journal de log de la base. Sinon celui ci pourrait grossir d’un seul coup !

Il ne vous reste plus qu’à planifier ce job régulièrement. 1 fois par semaine dans une période creuse par exemple.

SQLSaturday à Paris en septembre 2014

Cette année encore, le GUSS organise un SQLSaturday à Paris le 13 septembre. Cette journée de conférences vous permettra de vous former gratuitement avec des sessions d’un haut niveau technique en français et en anglais.

Voici le planning de la journée. Que vous soyez intéressés par le moteur de base de données ou la BI, je suis persuadé que vous trouverez votre bonheur !

Agenda SQLSaturday ParisCliquez pour agrandir

Pour s’inscrire à cette journée gratuite, ça se passe ici.

Pour ma part, je serai à l’organisation donc je ne pourrai pas forcément suivre les sessions mais il y en a certaines qui me tentent bien, surtout dans le premier track 🙂 Mais il y en aura pour tout le monde, même les anglophones.

Pour la première fois cette année, la veille du SQLSaturday, vous pourrez également assister à des préconférences. Ces préconférences sont payantes mais le coût (95€ en early bird) est très faible par rapport à une journée classique de formation et le niveau technique sera au rdv grâce à des speakers de renom. Pour les DBA, je recommande fortement celle ci : Stockage SQL Server , sauvegarde et récupération de données en détail par David Barbarin (MVP, MCM) & Christophe Laporte (MVP, MCM)

Pour les informations sur les préconférences, c’est ici.

Pour l’occasion, l’école Supinfo nous met à disposition ses locaux de la tour Montparnasse. En plus de 2 journées autour de SQL Server, vous pourrez profiter d’une des plus belles vues de Paris !

J’ai couru pour la SPA – Cani-course SPA 2014

Dimanche dernier, le 29 juin, j’ai participé à la cani-course SPA 2014. C’était une course caritative avec ma chienne afin de récolter des fonds pour l’association. Tout le monde pouvait s’inscrire, soit avec son chien, soit avec un chien « prêté » par la SPA. Pour participer, la seule chose à faire était de recueillir 100€ minimum de dons et la communauté SQL Server s’est montrée plutôt généreuse 🙂

J’ai donc couru avec ma chienne de 8 ans, Orka, que j’ai adopté il y a environ 2 mois.

Le trajet

Ce n’est vraiment pas facile de voyager avec un chien ! Pour le train, aucun problème le dimanche, mais c’est inimaginable de le faire en période de forte affluence. Il faut également payer un billet demi-tarif. Arrivé à Paris, c’est de suite plus compliqué. Les animaux ne sont pas admis dans le métro mais j’ai tout de même tenté l’aventure. Au retour, je me suis fait contrôler par des agents RATP. Je pensais avoir droit à une amende mais ils m’ont juste demandé de faire semblant de mettre une muselière. Très sympathiques pour une fois ! Si vous voulez prendre le métro avec votre chien, pensez à prendre une muselière au cas ou 🙂

La course

6.5km de course. Ça semble peu, mais il y a 15 jours je tenais à peine 2km 🙂 Après 2 semaines d’entrainement quotidien, mon objectif était donc de terminer la course en courant. J’ai également dû entrainer ma chienne parce qu’après quelques mois dans une fourrière et un refuge, elle avait autant de mal que moi !

La course n’était pas chronométrée, mais j’ai atteint l’objectif de terminer la course en 45 ou 50 minutes. Au début, Orka m’a bien aidé en me tractant mais vers la moitié de la course, j’ai du l’encourager pour qu’elle court à coté de moi, bizarrement elle ne tirait plus (à part la langue :p)

L’ambiance était très bonne, le but n’étant pas de rechercher un chrono mais d’offrir une ballade à 250 chiens dans les bois de Vincennes.

Nous avons passé une très bonne matinée et Orka a l’air d’avoir beaucoup apprécié car elle ne me quitte plus.

cani-course spa 2014

Retrouvez plus d’informations sur l’article du site de la SPA et les photos sur la page Facebook de l’association.

Pagination

Régulièrement, je rencontre des projets, essentiellement web, dans lesquels il y a des besoins de pagination de liste. Par exemple, un site e-commerce a besoin d’afficher une liste de produits d’une catégorie. Bien souvent, les développeurs râlent après SQL Server car il n’y aurait pas d’options comme le LIMIT de MySQL. Je vais donc vous montrer comment réaliser une pagination avec SQL Server.

Le besoin

Dans cet article, je vais partir du principe que je travaille pour un site e-commerce qui me demande d’afficher la liste des produits en fonction de la catégorie choisie. Il faudra que cette liste n’affiche que 20 articles par page et bien sur que les pages soient affichées très rapidement.

Mise en place

Pour réaliser ces tests, j’ai créé la table produits :

CREATE TABLE [dbo].[produits](
    [id] [int] NOT NULL,
    [libelle] [varchar](250) NULL,
    [prix] [decimal](9, 2) NULL,
    [categoryId] [int] NULL,
    [ordreAffichage] [int] NULL,
    [description] [varchar](500) NULL,
CONSTRAINT [PK_produits] PRIMARY KEY CLUSTERED([id] ASC))

J’ai ensuite chargé 110 000 lignes de données dans cette table répartie en 3 catégories. J’ai généré les données dans Excel en incrémentant des id et des libellés.

Données de la pagination

ALL in the DOM

Une des techniques de pagination rencontrée fréquemment sur de petits sites est la technique que j’appelle « ALL in the DOM ». Elle consiste à charger tous les produits de la catégorie dans la page et de gérer la pagination avec JavaScript. La rapidité d’intégration de cette solution est sans doute un avantage mais les performances peuvent très vite se montrer désastreuses ! En effet, si on doit surcharger la page de plusieurs centaines d’articles qui ne seront sans doute jamais consultés, l’affichage pourrait être extrêmement long. Et c’est sans compter le requêtage de la table qui devra lire beaucoup de pages (et d’IO) ainsi que la bande passante du server SQL qui devra transférer les données à l’application. Bref, cette solution est à réserver exclusivement pour des projets amateurs à faible volumétrie.

La requête exécutée sur le serveur ressemblerait à ceci :

DECLARE @CATEGORY int = 3

select id
, libelle
, prix
, categoryId
, description
from produits
where categoryId = @CATEGORY
order by ordreAffichage

Table temporaire

Une autre solution que j’ai rencontré, mais que je ne recommande pas, est d’utiliser des TOP avec une table temporaire. Une première étape consiste à créer un table temporaire qui contient toutes les lignes jusqu’à la fin de la pagination : par exemple, avec 20 produits par page pour afficher la 5ème page, la table temporaire contiendra les 100 premiers produits. Dans la 2ème étape, il suffit ensuite de sélectionner avec un top 20 les produits de la page.

DECLARE @DEBUT int = 500
DECLARE @LONGUEUR int = 20
DECLARE @CATEGORY int = 3

select top (@DEBUT + @LONGUEUR) id
, libelle
, prix
, categoryId
, description
, ordreAffichage
into #t
from produits
where categoryId = @CATEGORY
order by ordreAffichage

select top (@LONGUEUR) *
from #t
order by ordreAffichage desc

drop table #t

Avec ce système, plus l’utilisateur paginera et plus les performances se dégraderont. Avec de nombreux utilisateurs, la TEMPDB pourrait grossir très vite.

CTE et Fenêtrage

Depuis la version 2005, on peut utiliser les CTE pour les requêtes récursives et avec la fonction de fenêtrage ROWNUMBER qui permet d’attribuer un rang à une ligne, nous sommes capable de ne récupérer que les lignes qui nous intéresse.

DECLARE @DEBUT int = 500
DECLARE @LONGUEUR int = 20
DECLARE @CATEGORY int = 3

;WITH cte AS
(
select id
, libelle
, prix
, categoryId
, description
, ROW_NUMBER() OVER (ORDER BY ordreAffichage) as rang
from produits
where categoryId = @CATEGORY
)
select id
, libelle
, prix
, categoryId
, description
FROM cte
WHERE rang > @DEBUT AND rang <= @DEBUT + @LONGUEUR

Je pense que cette solution (ou une adaptation de celle-ci) est bonne pour les SQL Server < 2012.

OFFSET et FETCH

Avec SQL Server 2012 et 2014, OFFSET et FETCH ont fait leur apparition. Cette méthode se rapproche le plus du LIMIT connu sur MySQL.

DECLARE @DEBUT int = 80000
DECLARE @LONGUEUR int = 20
DECLARE @CATEGORY int = 3

select id
, libelle
, prix
, categoryId
, description
from produits
where categoryId = @CATEGORY
order by ordreAffichage
offset @DEBUT rows
fetch next @LONGUEUR rows only

Au niveau performance, elle est quasiment identique aux CTE + ROWNUMBER mais elle est bien plus lisible.

TVP et Cache Client

Cette méthode est plus complexe à mettre en oeuvre. En effet, il est nécessaire d’utiliser les TVP dans SQL Server et gérer un cache au niveau de l’application.
Pour utiliser une TVP, il est nécessaire de créer un type TABLE au niveau de la base de données comme ceci :

CREATE TYPE dbo.TypePagination AS TABLE
(
id int
)

Lors du premier affichage d’une catégorie, il convient de le récupérer les ID de tous les produits de la catégorie et de les stocker dans le cache de l’application.

DECLARE @CATEGORY int = 3

select id
from produits
where categoryId = @CATEGORY
order by ordreAffichage

Lors de la pagination par l’utilisateur, il convient ensuite de récupérer les 20 ID suivant :

select id
, libelle
, prix
, categoryId
, description
from produits p
where exists (select * from @TypePagination t where t.id = p.id)
order by ordreAffichage

Cette méthode est de loin la plus performante si l’indexation de la table produits est réalisée correctement. L’inconvénient est la difficulté (relative) à sa mise en place, la consommation de ressources supérieure au niveau du serveur applicatif et la lenteur du premier affichage de la liste de produit (mais on peut compenser ceci par une procédure de chargement de cache lors du reboot du serveur applicatif).

Comparatif

Afin de tester les performances, j’ai exécuté 10 fois chacune des requêtes et j’ai calculé des moyennes sur le CPU Time, l’Elapsed Time et le Logical Reads. J’ai fait ce test pour la 1ère et la 50ème page de la liste de produit.

Comparatif Performance Pagination

La méthode avec la table temporaire est de moins en moins efficace en avançant dans la pagination. De plus elle a beaucoup d’inconvénients à cause de la création de tables temporaires.

La méthode All in the DOM est catastrophique ! Surtout que dans cet exercice, je ne tiens compte que de la consommation au niveau de la base de données. Il reste ensuite à la page Web de gérer la pagination.

La technique Offset + Fetch semble être un bon compromis entre performance et facilité de développement et reste stable au fur et à mesure de l’avancée dans la pagination. Cependant la méthode utilisant CTE + fonction de fenêtrage est très proche.

La dernière méthode utilisant la TVP est la plus performante au niveau base de données. Mais comme la performance doit se mesurer dans son ensemble avec la chaine complète et qu’une partie du travail est réalisé coté applicatif, il faut tester en fonction de vos besoins.

Plan de Maintenance

Plan de maintenance – a quoi ça sert ?

Le plan de maintenance d’une instance SQL Server est l’ensemble des jobs qui permettent de garantir le bon fonctionnement des bases de données dans le temps.

Est-ce indispensable ?

Imaginez une instance de bases de données non maintenue. Sans action sur vos indexs, les performances deviendraient catastrophiques. Sans sauvegarde, vous risqueriez de perdre toutes vos données, les journaux de logs satureraient l’espace de vos disques. Et ce n’est qu’un avant-gout des problèmes qui pourraient se multiplier. Je suis donc obligé de dire que la mise en place d’un plan de maintenance est obligatoire. C’est même une des premières choses à définir lors d’une installation.

Les différentes étapes

La sauvegarde

C’est surement à la sauvegarde de nos bases de données que l’on pense en premier quand on parle de plan de maintenance. Vous devez adapter votre stratégie de sauvegarde en fonction de vos besoins. Par exemple, pour un petit site web relativement statique avec un recovery model simple, une sauvegarde FULL chaque nuit lors des heures creuses d’activité pourrait suffire. Par contre, pour un gros site e-commerce, une sauvegarde FULL quotidienne + des sauvegardes incrémentales régulières + des sauvegardes des logs toutes les 5 minutes pourrait être envisageable.

Bref, cet article n’a pas vocation à vous aider à choisir une stratégie de sauvegarde mais plutôt de ne pas oublier des étapes importantes dans votre plan de maintenance.

N’oubliez pas que le but de la sauvegarde des bases de données est de pouvoir les restaurer ! Il faut donc penser à vérifier que vos sauvegardes sont opérationnelles.

Echec restore

Je sauvegarde toujours dans un nouveau fichier, je ne suis pas vraiment adepte de plusieurs sauvegardes dans un seul fichier (bien qu’il y ait quelques avantages). Ce nouveau fichier est horodaté dans son nom. Avec ce système, je suis donc obligé de créer une étape de suppression des anciennes sauvegardes pour ne pas remplir mes disques.

J’ai souvent la question sur la sauvegarde des bases systèmes. Oui il est préférable de sauvegarder au minimum MASTER (pour le mot de passe des logins par exemple), MSDB (pour toutes les données de l’agent) et éventuellement MODEL.

En principe, je sauvegarde également régulièrement dans des fichiers SQL le script des logins, les server objects et les objets de l’agent. Ce n’est pas obligatoire, mais ça permet souvent de gagner du temps.

La vérification d’intégrité

La vérification d’intégrité des bases de données est également incontournable. Si une de vos bases est corrompue, il faut être alerté le plus rapidement possible pour réduire la perte de données.

En général, j’exécute un DBCC CHECKDB avant de faire les sauvegardes complètes. Pour réduire le coût de cette opération, on peut alterner avec un DBCC CHECKDB et l’option WITH PHYSICAL_ONLY.

Integrité de vos bases

Je recommande de conserver la dernière sauvegarde FULL effectuée juste après le dernier DBCC CHECKDB (FULL). En cas de corruption, on peut repartir d’une sauvegarde saine.

Les indexs et statistiques

A chaque insertion / suppression / mise à jour de données dans une table, ses indexs se fragmentent.

Si une maintenance des indexs et des statistiques n’est pas effectuée, les performances globales de la base de données vont se dégrader. En effet, un index fragmenté va obliger à lire plus de pages de données donc plus d’IO et plus de RAM seront nécessaires. Si les statistiques ne sont pas à jour, l’optimiseur pourrait décider d’utiliser un plan d’exécution non adapté ce qui aurait pour effet de consommer trop de ressources et de ralentir les temps d’exécution des requêtes.

Il convient donc de défragmenter régulièrement les indexs et de recalculer les statistiques. Cette tache est une partie du plan de maintenance indispensable à une base de données.

On pourrait être tenté de reconstruire chaque index mais pour une base de données utilisée 24/24h, ça pourrait ne pas coller avec les besoins métier car cette opération peut être très couteuse. L’idéal est donc d’utiliser des scripts et en général j’applique cette règle :
– Si l’index est fragmenté à plus de 30% alors je reconstruis l’index (à la reconstruction de l’index, les statistiques sont recrées donc pas besoin de refaire une mise à jour).
– si l’index est fragmenté entre 10 et 30% alors je réorganise l’index et je recalcule les statistiques.
– sinon je ne fais rien.

defrag

N’oubliez pas qu’en version Enterprise, les indexs peuvent être mis à jour ONLINE. C’est plus lent mais la base est toujours accessible.

Si un index reste toujours fragmenté et possède moins de 8 pages, inutile de s’acharner à le défragmenter…

Le nettoyage

La dernière tache indispensable dans un plan de maintenance est la suppression des historiques de sauvegardes et des jobs de l’agent. En effet, si vous ne le faites pas, la base MSDB deviendra énorme.

Nettoyer les historiquesEssayez de restaurer rapidement une base à partir d’une sauvegarde FULL + quelques sauvegardes de LOGS avec un historique de quelques mois et vous comprendrez ce dont je veux parler 😉

Conclusion

Le plan de maintenance est une des taches de base d’un DBA et devrait être mis en place sur chaque instance. Il n’existe pas un plan de maintenance type car c’est à vous d’adapter votre plan au besoin de l’entreprise.

N’oubliez pas que grâce à Kankuru, vous pouvez surveiller que vos plans fonctionnent correctement sur tous vos serveurs !

Il est possible que vos plans de maintenance contiennent des étapes supplémentaires. N’hésitez pas à les décrire dans vos commentaires.

Parcours du combattant

Migrer Iphone vers Windows Phone – mon parcours du combattant

J’utilise un iphone depuis pas mal d’années. Je m’étais juré de ne jamais changer d’OS tant j’en étais satisfait. Cependant, j’étais lassé du manque d’autonomie et du peu de choix de téléphone : en gros, il faut toujours prendre du haut de gamme. J’étais également tenté par le format phablet qu’Apple n’a pas l’air d’avoir l’intention de sortir. En tant qu’utilisateur de produits Microsoft, je voulais bien sûr tester leur fameux OS. Ce sont toutes ces raisons qui m’ont poussé à migrer d’Iphone vers Windows Phone.

Le téléphone

Je me suis laissé tenté par le Nokia 1320 (le low-cost du 1520) car le prix était faible, c’est à dire inférieur à 300€, et que le format de la SIM était du micro donc le même que celui de mon iphone 4S. Ceci me permet de revenir sur mon vieil iphone si Windows Phone ne me plait pas 😉

Nokia Lumia 1320

Le téléphone en lui même est plutôt lourd et peu pratique à cause du bouton d’appareil photo qui s’active à chaque fois. Ça doit être parfait pour les gauchers mais manque de chance, je suis droitier ! Si vraiment Windows Phone me plait, je pense en choisir un plus petit.

Récupérer mes données

Lorsque j’ai changé mon 3GS pour mon 4S, je n’ai eu qu’une sauvegarde/restauration à faire avec itunes pour retrouver toute ma configuration. La migration vers Windows Phone risque d’être plus compliquée.

Première chose à faire : copier mes contacts. Comme je n’avais pas vraiment envie d’entrer manuellement chacun de mes contacts dans mon nouveau téléphone, il a donc fallu trouver une solution. La bonne nouvelle, c’est que Nokia a pensé à tout ! Grâce à ce logiciel, en activant le Bluetooth sur mes 2 téléphones, j’ai réussi à recopier tous mes contacts.

Ensuite, les photos. Je m’en suis sorti très facilement car je sauvegardais déjà mes photos Iphone sur OneDrive.

WIndows Phone 8

La configuration de l’OS est relativement simple, je retrouve la plupart des applications que j’utilisais précédemment à part les jeux (mais je suis obligé d’avouer que je n’ai pas spécialement cherché pour le moment).

Par contre, je n’ai pas réussi à configurer certaines messageries POP3 d’OVH et 1&1. Il faudrait que je creuse encore plus avant de dire que ça ne fonctionne pas mais tout de même l’ajout d’un compte de messagerie ne devrait pas poser de problème. En plus, j’ai une dizaine de boites mail et je trouve dommage d’avoir autant de tuiles sur ma page d’accueil.

La deuxième chose que je reproche à l’OS, est le manque cruel d’un vrai centre de notification. Après 24h de téléphone éteint, j’ai reçu une vingtaine de notifications sans en avoir un résumé. Bref, il faudrait rester en permanence avec un œil sur son téléphone pour ne rien rater. Normalement, avec Windows Phone 8.1, le problème devrait être réglé mais pour le moment, ça ne me convient pas.

Dernier point, mais je pense que le problème est plutôt lié à IOS, les personnes qui m’envoyaient des iMessages depuis d’autres Iphone ne peuvent plus m’envoyer de SMS. Enfin en tout cas, je ne les reçois plus sauf s’ils pensent à me les envoyer en sms.

Conclusion

Pour le moment, je ne retrouve pas tout ce que j’attends d’un téléphone, je suis donc toujours sur mon iphone ! Je me suis inscrit au programme développeur qui permet de tester Windows Phone 8.1. Je vais le tester dans les prochains jours et peut être migrer définitivement. Dans tous les cas, je pense que j’écrirai un nouvel article pour vous tenir au courant.

Sql Server 2014

SQL Server 2014 : les nouveautés

Hier, Microsoft a officiellement lancé SQL Server 2014. Une version d’évaluation gratuite de 180 jours est disponible sur le site Microsoft.

Je pensais écrire un article complet sur toutes les améliorations qu’apporte cette nouvelle release SQL Server 2014 mais le GUSS l’avait déjà fait avant moi lors des Journées SQL Server. Je vous propose donc de visionner la vidéo de la session :

Pour moi, la fonctionnalité qui m’intéresse le plus est bien sur Hekaton. J’y vois beaucoup de possibilités de gain de performance. C’est pourquoi, je vous propose également une seconde vidéo, enregistrée en décembre lors des journées SQL Server, entièrement dédiée au In-Memory. En plus, elle est présentée par un des meilleurs speakers : Christophe Laporte :

Je pense que dans le futur, je publierai des exemples concrets d’utilisations du In-Memory…

Déploiement SSAS

SSAS déploiement – L’erreur système suivante s’est produite :

La semaine dernière, j’ai tenté de déployer un cube SSAS mais je me suis heurté à une erreur qui n’est pas forcément très explicite :

L'erreur système suivante s'est produite :
 SSAS Erreur déploiement

Et bien évidemment, aucune information supplémentaire ! C’est Jean-Pierre Riehl qui m’a donné la solution. Dans mon projet SSAS, j’ai un rôle qui s’appelle « Lecteur ».

SSAS Role

Parmi les membres de ce rôle, j’ai un login qui n’existe plus dans mon Active Directory. Si je le supprime, le déploiement fonctionne bien !

SSAS Role login inexistant

Sans l’aide de Jean-Pierre, j’aurais pu encore chercher longtemps. J’espère que dans les prochaines versions de SQL Server, les développeurs ajouteront l’erreur suivante :

L’erreur système suivante s’est produite : Un membre de votre rôle n’a pas été trouvé.

Bien évidemment, certains pourront me reprocher de ne pas avoir utilisé de groupe d’utilisateurs plutôt qu’un utilisateur directement et ils auront bien raison ! 🙂

Débuter avec SMO et C# : performances SetDefaultInitFields

Bonjour,

Dans l’article précédent, je vous ai montré comment ouvrir une connexion à une instance SQL Server avec SMO et C# puis à lister le nom des bases de données. Avant d’aller plus loin dans l’apprentissage de SMO, il y a une notion très importante à retenir pour avoir de bonnes performances : SetDefaultInitFields.

Si je lance une trace SQL lors de l’exécution du programme qui liste le nom des bases de données, je vais voir que 5 requêtes ont été exécutées. Les 4 premières sont propre à l’utilisation de SMO, il a besoin d’obtenir des informations sur l’instance et la base avant de générer sa requête. Celle qui nous intéresse est la dernière qui sert à lister le nom des bases de données :


SELECT
dtb.name AS [Name]
FROM
master.sys.databases AS dtb
ORDER BY
[Name] ASC

SQL Profiler et SMO

Jusqu’ici, la requête générée est plutôt propre.

Lister la collation de chaque base de données

Je vais un peu modifier le code afin d’ajouter la collation de chaque base de données


string _instanceName = "localhost";
smoCommon.ServerConnection sc = new smoCommon.ServerConnection(_instanceName);
sc.Connect();
smo.Server monServeur = new smo.Server(sc);
foreach (smo.Database maBase in monServeur.Databases)
{
    Console.WriteLine(maBase.Name + " : " + maBase.Collation);
}
sc.Disconnect();

SMO Affichage des bases et des collations

Analyse de la trace SQL Profiler

Voici le résultat de la trace SQL Profiler. Comme vous pouvez le constater, nous sommes loin d’un traitement ensembliste et efficace ! Il y a une seule requête pour récupérer le nom de toutes les bases de données mais ensuite une requête par base de données pour récupérer la collation !

SQL Profiler SMO sans SetDefaultInitFields

Comment rendre le traitement ensembliste ?

Il existe une méthode pour améliorer la génération de l’écriture de la requête. Il s’agit de SetDefaultInitFields. Cette méthode permet de préciser à SMO qu’il doit récupérer certaines ou toutes les propriétés en une seule étape.

Je vais maintenant modifier mon code afin d’utiliser cette méthode pour récupérer également la collation :


string _instanceName = "localhost";
smoCommon.ServerConnection sc = new smoCommon.ServerConnection(_instanceName);
sc.Connect();
smo.Server monServeur = new smo.Server(sc);
monServeur.SetDefaultInitFields(typeof(smo.Database), "Collation");
foreach (smo.Database maBase in monServeur.Databases)
{
 Console.WriteLine(maBase.Name + " : " + maBase.Collation);
}
sc.Disconnect();

Et voici donc le résultat de la trace SQL Profiler.
SQL Profiler SMO avec SetDefaultInitFields

Il n’y a plus qu’une seule requête pour récupérer le nom et la collation :


SELECT
dtb.name AS [Name],
dtb.collation_name AS [Collation],
dtb.name AS [DatabaseName2]
FROM
master.sys.databases AS dtb
ORDER BY
[Name] ASC

Conclusions

Je vous ai montré cette méthode pour les bases de données, mais elle est également valable pour tous les autres objets SMO (les tables, les procédures stockées, les vues, etc…). Imaginez les gains de performance sur une base de données à plusieurs milliers de procédures stockées !

Attention tout de même : si vous écrivez mal la ou les propriétés dans la méthode SetDefaultInitFields, vous n’aurez pas d’erreur à la compilation puisque c’est une stringCollection. L’erreur surviendrait à l’exécution.

Débuter avec SMO et C# pour piloter SQL Server

SMO est un ensemble de bibliothèques fournies par Microsoft pour automatiser le pilotage des bases de données SQL Server. Je m’en sers beaucoup dans le développement de Kankuru ou dans mon quotidien de DBA. Dans Kankuru par exemple, je les utilise pour afficher la liste des bases de données d’une instance ou la liste des tables d’une base de données.

Je trouve ces librairies très pratique bien que parfois plus lent que du code T-SQL. En effet, l’utilisation de SMO revient souvent à faire du traitement unitaire plutôt que du traitement ensembliste. Je reviendrai dans un futur article sur ces problèmes de performance.

Dans cet article, je vais vous montrer un exemple de base pour apprendre à vous connecter à une instance.

Prérequis

Bien que l’on puisse utiliser ces bibliothèques avec Powershell, pour ma part je préfère le C#. Je vais donc utiliser Visual Studio 2012 Express Edition for Windows Desktop.

Vous trouverez les dll SMO dans le répertoire d’installation de SQL Server (sur mon PC : C:Program FilesMicrosoft SQL Server110SDKAssemblies).

Préparation du projet

Dans Visual Studio, je vais créer une application console. Mais bien évidemment, vous pouvez l’utiliser dans une application WPF, Windows Forms ou ASP.Net.

Création du projet d'application console

Une fois le projet créé, il faut importer les DLL requises. A minima, vous aurez besoin de :

  • Microsoft.SqlServer.Smo.dll
  • Microsoft.SqlServer.ConnectionInfo.dll
  • Microsoft.SqlServer.Management.Sdk.Sfc.dll

Ajouter les références

Connexion à une instance avec SMO

Il faut tout d’abord commencer par importer les bibliothèques :

using smoCommon = Microsoft.SqlServer.Management.Common;
using smo = Microsoft.SqlServer.Management.Smo;

Ensuite, on peut se connecter à l’instance. Ci-dessous, un simple test de connexion puis déconnexion.

NB : Cet exemple utilise l’authentification Windows. Votre utilisateur Windows doit avoir des droits sur l’instance.

Si vous n’arrivez pas à exécuter ceci, pas la peine d’aller plus loin ! Vérifier le nom de votre instance (serveurinstance)

string _instanceName = "localhost";
smoCommon.ServerConnection sc = new smoCommon.ServerConnection(_instanceName);
sc.Connect();
sc.Disconnect();

Afficher le nom des bases de données

Une fois connecté à l’instance, on peut facilement récupérer la liste de toutes les bases de données:


string _instanceName = "localhost";
smoCommon.ServerConnection sc = new smoCommon.ServerConnection(_instanceName);
sc.Connect();
smo.Server monServeur = new smo.Server(sc);
foreach (smo.Database maBase in monServeur.Databases)
{
 Console.WriteLine(maBase.Name);
}
sc.Disconnect();

Afficher la liste des bases de données avec SMO

Dans un prochain article, je rentrerai plus dans le détail de l’utilisation de SMO. Je vous montrerai comment scripter des objets par exemple.

Afterwork du Guss

Afterwork du GUSS

Tous les deux mois, le GUSS organise un afterwork à Paris. Vous êtes DBA, développeur SQL, vous faites de la BI ou juste intéressés par SQL Server alors vous êtes les bienvenues.

C’est un bon moyen pour rencontrer des personnes qui font le même métier que vous et d’échanger sur vos problématiques techniques autour d’un verre.

En principe, nous définissons un thème technique lors de ces afterworks mais cette fois-ci, puisque c’est le premier afterwork depuis l’élection du board, vous pourrez nous soumettre vos questions et vos idées.

Pour venir, c’est très simple. Il suffit de vous inscrire à l’évènement sur le site du GUSS puis de vous rendre le 2 avril 2014 à 19h à cette adresse :

Charly-Birdy
1 place Etienne Pernet, Paris 15ème
Métro Commerce

Venez nombreux !