Kankuru regsrvr importer

Hello,

Oui je travaille avec Kankuru tous les jours, ca m’aide beaucoup au quotidien, mais je n’écris pas ceci parce que je suis le développeur ! Je pense que c’est un bon complément de SSMS. Par contre, s’il y a bien une chose qui m’ennuie vraiment dans Kankuru, c’est d’ajouter de nouveaux serveurs ! A l’époque, je voulais pouvoir gérer d’autres serveurs que des serveurs SQL Server, j’avais donc séparé l’ajout des serveurs et des instances SQL.

Il suffit de le refaire !

Oui, effectivement, je pourrais redévelopper ces interfaces pour les simplifier. Ca viendra peut être mais ce n’est pas ma priorité et surtout, je fais déjà le travail d’enregistrer les serveurs dans SSMS alors pourquoi faire le travail 2 fois.

Il y a quelques jours, sur twitter, David m’a demandé si on ne pouvait pas importer les serveurs enregistrés dans SSMS et je me suis dit que c’était une bonne idée.

Par contre, j’ai déjà testé les scans réseaux avec notamment la méthode SMO mais sans résultat probant. Je préfère ne pas passer de temps dans cette voie pour le moment.

Kankuru regsrvr Importer

kankuru regsrvr importer preview

Screenshot du script généré par Kankuru importer

J’ai réalisé ce projet Windows Forms très rapidement avec l’aide de Martial, un collègue. Le code est loin d’être beau mais il devrait vous permettre d’importer vos fichier regsrvr.

NB: Lorsque vous exportez les serveurs enregistrés, pensez à décocher cette case :

Export Regsrvr file from ssms

Exporter un fichier regsrvr depuis SSMS

Les mots de passes en authentification SQL sont encryptés donc il ne vous restera plus qu’à le remplacer par le bon.

GIthub

Je ne suis pas vraiment fier du code que j’ai écrit mais je le partage tout de même sur github.

Et pour les plus pressés, voici le fichier exe déjà compilé KankuruImporter

Il suffit d’avoir le framework 4.0 installé comme pour Kankuru.

Meet the DBA – rencontrez les DBA de Criteo

Bonjour tout le monde,

Mercredi 13 janvier, à partir de 18h30, nous organisons avec le GUSS une session chez Criteo (la société qui m’emploie).

Au programme Scale SQL for the web

  • L’organisation de l’équipe DBA
  • La gestion de l’infrastructure
  • Notre implémentation de la réplication SQL
  • Le déploiement du code SQL en production (Intégration continue)

Et ensuite, nous pourrons discuter autour d’un verre et d’une pizza.

Ca se passe à Paris dans le 9ème arrondissement et c’est gratuit 🙂

Vous n’avez qu’à vous inscrire sur le site du GUSS.

J’espère vous y rencontrer nombreux.

BULK INSERT Format File

Dernièrement, je suis tombé sur une application qui alimentait des milions de lignes de données dans une table de façon unitaire. Pour améliorer les performances d’insertion, j’ai du mettre en place une procédure qui BULK INSERT un fichier dans une table SQL Server. J’ai décidé d’utiliser un fichier de format (BCP Format File).
Ce fichier de format contient la structure de la table et du fichier.

Génération du fichier de format (Format File)

Pour générer un fichier de format, j’ai utilisé l’utilitaire BCP. Cet utilitaire bien connu des DBA permet d’exporter des données d’une table vers un fichier ou de faire le chemin inverse. Il permet également de générer des fichiers de format qui seront utilisés lors d’un BULK INSERT.

Pour les besoins de cet article, j’ai créé la table testimport dans ma base TEST. Elle contiendra les données insérées.

CREATE TABLE [dbo].[testimport](
id int NOT NULL
, name nvarchar(50)
, category smallint
, comments nvarchar(500)
, width decimal (18,2)
, height decimal (18,2)
CONSTRAINT [PK_testimport] PRIMARY KEY CLUSTERED
(
id ASC
) ON [PRIMARY]
) ON [PRIMARY]

Ensuite, en ligne de commande, je peux générer un fichier de format :

bcp « TEST.dbo.testimport » format -S »localhost » -T -f « C:\temp\testimport_format.xml » -c -x -t »; » -r »\n »

fichier_format_code

Explication :
bcp : commande d’appel de l’utilitaire. S’il n’est pas reconnu, vérifiez qu’il soit installé et votre PATH.
« TEST.dbo.testimport » : la table utilisée pour générer le formatfile
format : c’est cet argument qui permet de générer un fichier de format
-S « localhost » : le nom de mon instance locale
-T : authentification Windows. On peut également utiliser l’authentification SQL
-f « C:\temp\testimport_format.xml » : le nom de mon fichier de format qui sera créé.
-c : type de données char. -w pour de l’unicode
-x : format XML pour le format file
-t « ; » : le séparateur de colonne choisi
-r « \n » : le séparateur de ligne choisi

Vous pouvez retrouver toutes les options sur le site MSDN

Et voici le résultat :

Format file généré

Format file généré

BULK INSERT

J’ai donc ce fichier de 10 000 lignes à insérer dans ma table. Et grâce à BULK INSERT, je peux importer par lot mes données en T-SQL.

Le fichier à insérer

Le fichier à insérer

Et voici la requête qui permet de BULK INSERT

BULK INSERT TEST.dbo.testimport FROM 'C:\temp\testimport.dat'
WITH (
   BATCHSIZE =1000, MAXERRORS = 1
   ,FORMATFILE='C:\temp\testimport_format.xml');

Table Calendrier

Je n’y coupe jamais. A chaque fois, je me pose les mêmes questions. Dans beaucoup de projets, j’ai besoin d’un table calendrier et je ne me rappelle jamais des fonctions de format de date ! J’ai toujours besoin de retourner sur le site MSDN pour vérifier la syntaxe de telle ou telle fonction de date. Cette fois ci, j’ai décidé de publier un exemple de table calendrier. Cette table n’est pas forcément la plus complète mais elle a le mérite de recenser tout de même pas mal d’informations intéressantes et d’être facile à implémenter dans un ETL ou un job d’alimentation.

Création de la table Calendrier

Voici le DDL de la table Calendrier.

CREATE TABLE [dbo].[Calendrier]
(
   [DateJour] [date] NOT NULL,
   [Jour] [int] NOT NULL,
   [Mois] [int] NOT NULL,
   [Annee] [int] NOT NULL,
   [Trimestre] [int] NOT NULL,
   [Semestre] [int] NOT NULL,
   [JourAnnee] [int] NOT NULL,
   [JourSemaine] [int] NOT NULL,
   [Semaine] [int] NOT NULL,
   [SemaineIso] [int] NOT NULL,
   [JourTexteLong] [varchar](50) NOT NULL,
   [JourTexteCourt] [varchar](50) NOT NULL,
   [MoisTexteLong] [varchar](50) NOT NULL,
   [MoisTexteCourt] [varchar](50) NOT NULL,
   [DateTexteLong] [varchar](50) NOT NULL,
   [MoisTexteAnnee] [varchar](50) NOT  NULL,
   [TrimestreTexte] [varchar](14) NOT NULL,
   [SemestreTexte] [varchar](13) NOT NULL
) ON [PRIMARY]

ALTER TABLE dbo.Calendrier ADD CONSTRAINT PK_Calendrier
PRIMARY KEY CLUSTERED (DateJour ASC)

Insérer les données

Avant d’insérer les données, il faut tout d’abord définir une date de début et une date de fin.
Le script utilise la récursivité d’une CTE afin de sélectionner toutes les dates entre l’intervalle de début et de fin. (N’oubliez pas de fixer l’indicateur MAXRECURSION 0 à la fin de la requête)

DECLARE @DATEDEBUT DATE
DECLARE @DATEFIN DATE
SET @DATEDEBUT = '20100101'
SET @DATEFIN = '20141231'

;WITH mycte AS
(
   SELECT @DATEDEBUT DateJour
   UNION ALL
   SELECT DATEADD(DAY, 1, DateJour)
   FROM mycte
   WHERE DATEADD(DAY, 1, DateJour) < @DATEFIN
)
INSERT INTO [dbo].[Calendrier]
(    [DateJour]
   , [Jour]
   , [Mois]
   , [Annee]
   , [Trimestre]
   , [Semestre]
   , [JourAnnee]
   , [JourSemaine]
   , [Semaine]
   , [SemaineIso]
   , [JourTexteLong]
   , [JourTexteCourt]
   , [MoisTexteLong]
   , [MoisTexteCourt]
   , [DateTexteLong]
   , [MoisTexteAnnee]
   , [TrimestreTexte]
   , [SemestreTexte])
SELECT DateJour
   , DATEPART(DAY, DateJour) as Jour
   , DATEPART(MONTH, DateJour) as Mois
   , DATEPART(YEAR, DateJour) as Annee
   , DATEPART(QUARTER, DateJour) as Trimestre
   , CASE WHEN DATEPART(MONTH, DateJour) < 7 THEN 1 ELSE 2 END as Semestre
   , DATEPART(DAYOFYEAR, DateJour) as JourAnnee
   , DATEPART(WEEKDAY, DateJour) as JourSemaine
   , DATEPART(WEEK, DateJour) as Semaine
   , DATEPART(ISO_WEEK, DateJour) as SemaineIso
   , FORMAT(DateJour, 'dddd', 'fr-fr') as JourTexteLong
   , FORMAT(DateJour, 'ddd', 'fr-fr') as JourTexteCourt
   , FORMAT(DateJour, 'MMMM', 'fr-fr') as MoisTexteLong
   , FORMAT(DateJour, 'MMM', 'fr-fr') as MoisTexteCourt
   , FORMAT(DateJour, 'dd MMMM yyyy', 'fr-fr') as DateTexteLong
   , FORMAT(DateJour, 'MMMM yyyy', 'fr-fr') as MoisTexteAnnee
   , CASE WHEN DATEPART(QUARTER, DateJour) = 1 THEN '1er trimestre' ELSE CAST(DATEPART(QUARTER, DateJour) AS CHAR(1)) + 'ème trimestre' END as TrimestreTexte
   , CASE WHEN DATEPART(MONTH, DateJour) < 7 THEN '1er semestre' ELSE '2ème semestre' END as SemestreTexte
FROM mycte
OPTION (MAXRECURSION 0)

1er jour de la semaine

En fonction de l’environnement, le premier jour de la semaine n’est pas forcément le lundi. Mais pas de panique, il suffit de jouer avec cette fonction de session pour configurer le premier jour de la semaine. Plus d’information sur le site MSDN.

SET DATEFIRST 1

Insérer les nouvelles données


SET DATEFIRST 1

DECLARE @DATEDEBUT DATE
DECLARE @DATEFIN DATE
SET @DATEDEBUT = '20100101'
SET @DATEFIN = GETDATE()

;WITH mycte AS
(
   SELECT @DATEDEBUT DateJour
   UNION ALL
   SELECT DATEADD(DAY, 1, DateJour)
   FROM mycte
   WHERE DATEADD(DAY, 1, DateJour) < @DATEFIN
)
INSERT INTO [dbo].[Calendrier]
(    [DateJour]
   , [Jour]
   , [Mois]
   , [Annee]
   , [Trimestre]
   , [Semestre]
   , [JourAnnee]
   , [JourSemaine]
   , [Semaine]
   , [SemaineIso]
   , [JourTexteLong]
   , [JourTexteCourt]
   , [MoisTexteLong]
   , [MoisTexteCourt]
   , [DateTexteLong]
   , [MoisTexteAnnee]
   , [TrimestreTexte]
   , [SemestreTexte])
SELECT DateJour
   , DATEPART(DAY, DateJour) as Jour
   , DATEPART(MONTH, DateJour) as Mois
   , DATEPART(YEAR, DateJour) as Annee
   , DATEPART(QUARTER, DateJour) as Trimestre
   , CASE WHEN DATEPART(MONTH, DateJour) < 7 THEN 1 ELSE 2 END as Semestre
   , DATEPART(DAYOFYEAR, DateJour) as JourAnnee
   , DATEPART(WEEKDAY, DateJour) as JourSemaine
   , DATEPART(WEEK, DateJour) as Semaine
   , DATEPART(ISO_WEEK, DateJour) as SemaineIso
   , FORMAT(DateJour, 'dddd', 'fr-fr') as JourTexteLong
   , FORMAT(DateJour, 'ddd', 'fr-fr') as JourTexteCourt
   , FORMAT(DateJour, 'MMMM', 'fr-fr') as MoisTexteLong
   , FORMAT(DateJour, 'MMM', 'fr-fr') as MoisTexteCourt
   , FORMAT(DateJour, 'dd MMMM yyyy', 'fr-fr') as DateTexteLong
   , FORMAT(DateJour, 'MMMM yyyy', 'fr-fr') as MoisTexteAnnee
   , CASE WHEN DATEPART(QUARTER, DateJour) = 1 THEN '1er trimestre' ELSE CAST(DATEPART(QUARTER, DateJour) AS CHAR(1)) + 'ème trimestre' END as TrimestreTexte
   , CASE WHEN DATEPART(MONTH, DateJour) < 7 THEN '1er semestre' ELSE '2ème semestre' END as SemestreTexte
FROM mycte
WHERE NOT EXISTS (SELECT * FROM dbo.Calendrier c WHERE mycte.DateJour = c.DateJour)
OPTION (MAXRECURSION 0)

Conclusion

Table calendrier

SELECT sur la table calendrier

Bien évidemment cette table n’est pas exhaustive, n’hésitez donc pas à me proposer vos idées d’amélioration dans les commentaires.

Changer le propriétaire d'un abonnement SSRS

Changer le propriétaire d’un abonnement SSRS

En début de mois, j’ai changé de travail. J’ai quitté la société qui m’employait depuis 2 ans.Ce changement m’a rappelé une situation à laquelle j’ai été confronté lors du départ d’un ancien collègue.

Quelques jours après qu’il ait quitté la société, je me suis aperçu que tous ses abonnements SSRS étaient en erreur. En effet, son compte dans l’Active Directory n’existait plus. Heureusement que Kankuru sait aussi alerter les erreurs de fonctionnement des abonnements dans Reporting Services, sinon j’aurais appris ce problème lorsqu’un utilisateur se serait plaint de ne plus recevoir ses rapports SSRS.

Interface WEB

En allant voir de plus près un des abonnement, j’ai vu que le propriétaire de l’abonnement était le login Windows du créateur et qu’il n’y avait pas de solution pour changer ce propriétaire via l’interface graphique. (NB : le propriétaire ne s’affiche pas dans l’interface si votre login est le propriétaire de l’abonnement)

Le propriétaire n'est pas modifiable via l'interface de management

Le propriétaire n’est pas modifiable via l’interface de management

En T-SQL

Mais pas de panique, on peut changer le propriétaire d’un abonnement SSRS directement en SQL dans la base de données Reporting Services.

DECLARE @ANCIEN uniqueidentifier
DECLARE @NOUVEAU uniqueidentifier

SELECT @ANCIEN = UserID
FROM dbo.Users
WHERE UserName = 'KANKURU.FRbob'

SELECT @NOUVEAU = UserID
FROM dbo.Users
WHERE UserName = 'KANKURU.FRgreg'

IF @NOUVEAU IS NOT NULL AND @ANCIEN IS NOT NULL
BEGIN
    UPDATE dbo.Subscriptions
    SET OwnerID = @NOUVEAU
    WHERE OwnerID = @ANCIEN
END

Bonnes pratiques

Plutôt que de changer manuellement le propriétaire sur les abonnements de vos anciens collègues par votre login, utilisez plutôt un compte de service. Vous quitterez surement vous aussi votre société un jour ou l’autre.

Pour ne plus avoir à faire cette manipulation, créez un compte de service windows, puis modifiez le propriétaire de chaque abonnement avec ce compte dans un job planifié dans l’agent SQL tous les jours.
Les speakers des JSS2014

Mon retour sur les JSS2014

Début décembre, j’ai participé aux JSS2014. Les JSS, ce sont 2 jours de conférences gratuites organisées par le GUSS au centre de conférence Microsoft. 2 jours dédiés à SQL Server et tout ce qui tourne autour.

Session SMO et C#

Cette année encore, j’ai présenté une session en compagnie de Philippe sur le développement C# avec les bibliothèques SMO.

Avec des exemples simples, j’ai montré comment débuter une application SMO afin d’automatiser les taches récurrentes de l’administration SQL Server. Bien évidemment, en moins d’une heure, il est impossible de faire un tour complet du sujet mais en regardant cette session + la documentation, vous pourrez être rapidement autonome sur l’utilisation des bibliothèques SMO.

JSS2014 Session SMO et C#

JSS2014 Session SMO et C# avec Philippe Geiger et moi-même.

Cette année encore, les sessions étaient enregistrées. Vous pouvez donc consulter la vidéo sur la chaine youtube du GUSS :

Voici le powerpoint de présentation disponible sur Slideshare


Et la solution visual studio contenant les exemples présentés et plus encore :

Projet SMO

Les autres sessions des JSS2014

J’ai également apporté mon aide à la session streaminsight de Philippe Geiger. Avant de préparer cette session, je ne connaissais pas du tout StreamInsight mais je l’ai découvert avec Philippe. J’ai travaillé avec lui pour permettre de récupérer un flux Twitter pour StreamInsight Je suis sur que vous retrouverez très vite une présentation de cette session sur le blog de Philippe.

Le 1er jour, j’ai assisté à la session de David Barbarin sur AlwaysOn. Il a détaillé de nombreux problèmes qu’il a rencontré chez ses clients et expliqué comment les résoudre. Il en a profité pour montrer en avant-première le nouveau Dashboard de Kankuru qui permet de monitorer tous les groupes de disponibilité.

J’ai également fait un tour dans la session « Azure SQL Database : 1 an après » de Stéphane Goudeau et Benjamin Talmard. J’y suis plus allé par curiosité pour découvrir cette offre et j’en ai eu un bon aperçu.

Le lendemain, j’ai vu du fond de la salle (il y avait du monde) la session de Sarah Bessard et David Barbarin sur l’industrialisation de l’audit MSSQL. Elle nous a présenté sa méthode d’audit.

Pour finir, je me suis invité dans la session de Galla Pupel sur Power View et Power Q&A. Je ne connaissais pas Q&A et ca ne m’intéresse pas forcément pour mon travail au quotidien mais finalement, la curiosité m’a amené dans une bonne session.

Mais les Journées SQL Server ne s’arrêtent pas aux sessions, on y rencontre beaucoup d’anciens collègues, des personnes que l’on ne connaissait que via les réseaux sociaux, des sponsors, bref on fait toujours de belles rencontres 🙂

Je regrette toutefois de n’avoir pas pu assister à certaines sessions des JSS2014 mais je me console en me disant que je peux maintenant les revoir sur la chaine youtube du GUSS.

Les journées SQL Server 2014

Les journées SQL Server 2014

Il y a un an, j’assistais à mes premières journées SQL Server. Étant un néo-parisien, je n’avais jamais pu m’y rendre. Pour cette première, j’avais même eu la chance d’y animer une session sur le monitoring avec Kankuru avec l’aide de Philippe Geiger ! Cette année encore, les journées SQL Server reviennent pour une 4ème édition. Et le moins que l’on puisse dire, c’est que le casting est excellent.

Les Journées SQL Server ?

Les journées SQL Server, ce sont 2 jours de conférences gratuites sur des sujets autour de la technologie SQL Server. Cet évènement, organisé par le GUSS et Microsoft, est le plus grand en France dédié à notre chère base de données. Il s’adresse à tous les acteurs du monde informatique puisqu’il y aura des sessions sur l’administration, la BI, la Big Data, les outils, les nouveautés et tous les sujets connexes à SQL Server.

Mon planning

Il est toujours compliqué de faire des choix lors de la construction de son planning. Est ce que je dois assister à des sessions sur le moteur SQL afin d’en apprendre encore plus ? Ou dois-je m’ouvrir vers d’autres tracks pour améliorer ma culture informatique ? J’hésite encore, mais je pense tout de même voir les sessions qui ont un rapport avec le moteur SQL, les performances et l’architecture.

Agenda JSS2014

Agenda des JSS 2014, faites votre choix !

Je recommande tout de même la session de David Barbarin, le premier jour, pour tous ceux qui souhaiteraient en savoir plus sur l’AlwaysOn et la haute disponibilité. C’est d’ailleurs un sujet sur lequel je travaille actuellement dans Kankuru…

La session de Frederic Pichaut est pour moi un incontournable ! Ceux qui ont déjà eu la chance de voir une de ses sessions savent pourquoi je la recommande. Même quand on pense être un expert sur un sujet, on apprend toujours avec Fred.

Les sessions de Jean-Pierre Riehl sont toujours excellentes. Un bon moyen de voir ce qu’il se fait dans la BI de Microsoft.

Pour le deuxième jour, tout dba devrait être présent dans la track « Core SQL ». Les meilleurs DBA français proposent des sessions techniques très intéressantes.

Et moi dans tout ça ?

Cette année encore, j’ai été sélectionné pour présenter une session. Mais pour changer, il ne sera pas question de Kankuru. Je vais vous présenter les bibliothèques SMO avec du code C#. Grâce à cette session, vous pourrez débuter un projet SMO afin d’automatiser toutes les taches quotidiennes d’administration. Cette fois encore, Philippe Geiger m’a proposé son aide 🙂

Et en retour, je l’assisterai pour sa session sur StreamInsight ! En tout, 2 heures de session en compagnie de Philippe 🙂

Inscriptions

Ces 2 jours de conférences sont gratuits. Ils se déroulent au centre de conférence de Microsoft à Issy-les-moulineaux.

Pour vous inscrire, c’est très simple ! Il suffit de se rendre sur le site du GUSS à cette adresse, de créer un compte sur le site (si vous n’en avez pas encore) et de vous inscrire. Ça ne prend pas plus de 2 minutes.

En pleine session, lors des JSS 2013 avec Philippe Geiger

Ma présentation lors des JSS 2013, avec mon énorme micro !

Ma présentation lors des JSS 2013, avec mon énorme micro !

J’espère y revoir d’anciens collègues et rencontrer de nouvelles têtes ! N’hésitez pas à venir discuter avec moi si vous me croisez dans les couloirs 🙂

defrag

SMO et C# : Reorg Rebuild Index

Dans tous les plans de maintenance des bases de données, on retrouve une étape importante : le reorg rebuild index. En effet, au fil des écritures dans une base de données, un index se fragmente et devient donc de moins en moins performant. Il convient donc de les réorganiser ou de les reconstruire régulièrement. On peut bien évidemment réaliser cette tache en T-SQL ou via un plan de maintenance standard, mais on peut également l’exécuter depuis une application .net grâce à SMO.

Prérequis

Pour les débutants, je vous propose de consulter cet article pour apprendre à créer un projet C# + SMO.

Connexion à l’instance

Je passe rapidement la première étape qui sert à se connecter à une instance SQL Server. Pour la démo, je me connecte à mon instance locale.

string _instance = "localhost";
smoCommon.ServerConnection sc = new smoCommon.ServerConnection(_instance);
sc.Connect();
smo.Server myServer = new smo.Server(sc);

Reorg Rebuild Index simple en C# avec SMO

Voici comment recréer tous les indexs de toutes les bases de données :

string _instance = "localhost";
smoCommon.ServerConnection sc = new smoCommon.ServerConnection(_instance);
sc.Connect();
smo.Server myServer = new smo.Server(sc);

foreach (smo.Database myDb in myServer.Databases)
{
   // Pour chaque base Accessible
   // (ne pas tenter si la base est offline ou
   // en cours de restauration par exemple)
   if (myDb.IsAccessible)
   {
      foreach (smo.Table myTable in myDb.Tables)
      {
         foreach (smo.Index myIndex in myTable.Indexes)
         {
            // Pour chaque index de chaque table dans
            // la base de données, je reconstruis l'index
            myIndex.Rebuild();
            //myIndex.Reorganize(); ou Reorganize pour juste défragmenter l'index
         }
      }
   }
}

C’est relativement simple, tous les indexs sont rebuildés mais pour plus d’efficacité, allons plus loin.

Choisir en fonction de la fragmentation

Ces opérations de rebuild ou reorg d’index sont couteuses. Et il ne sert à rien de réorganiser un index d’une table qui n’a pas été modifiée. Il convient donc de vérifier son taux de fragmentation et de décider ensuite quelle est la meilleure action. En principe, si l’index est fragmenté à + de 30% alors je reconstruis l’index. S’il est fragmenté entre 10 et 30%, je le réorganise. Sinon je ne fais rien.

Grâce à la méthode EnumFragmentation, nous pouvons consulter le taux de fragmentation d’un index. C’est équivalent à l’écran dans SSMS ci-dessous.

smo fragmentation

int _seuilReorg = 10;
int _seuilRebuild = 30;
foreach (smo.Database myDb in myServer.Databases)
{
   if (myDb.IsAccessible)
   {
      foreach (smo.Table myTable in myDb.Tables)
      {
         foreach (smo.Index myIndex in myTable.Indexes)
         {
            DataTable dtFragmentation = myIndex.EnumFragmentation(smo.FragmentationOption.Detailed);
            // Consultation de la fragmentation
            // Fast : Calcule les statistiques uniquement sur les pages de niveau parent (- couteux mais + approximatif)
            // sampled : Calcule les statistiques sur des échantillons de données
            // Detailed : Calcule les statistiques sur 100 % des données (+ couteux)
            if (dtFragmentation.Rows.Count > 0)
            {
               double _fragmentation = 0;
               double.TryParse(dtFragmentation.Rows[0]["AverageFragmentation"].ToString(), out _fragmentation);
               if (_fragmentation >= _seuilRebuild) // Supérieur à 30%, je reconstuis
               {
                  myIndex.Rebuild();
               }
               if (_fragmentation >= _seuilReorg && _fragmentation < _seuilRebuild) // entre 10 et 30% je réorganise
               {
                  myIndex.Reorganize();
               }
            }
         }
      }
   }
}

Opérations ONLINE

Par défaut, les indexs sont reconstruits OFFLINE.

SMO Index OfflineSi vous avez de l’activité à ce moment là, les utilisateurs risquent fort d’être lockés. Il faut donc utiliser les opérations ONLINE (uniquement valable en Enterprise Edition). Il suffit d’activer cette propriété :

myIndex.OnlineIndexOperation = true;

smo index online

Notes

Il est possible qu’après avoir mis à jour tous les indexs, vous remarquiez qu’un index est toujours fragmenté. Ceci peut être normal s’il s’agit d’un index contenant moins de 8 pages.

Si vous avez plusieurs partitions, vous pouvez reconstruire partition par partition grâce à la surcharge des méthodes index.Rebuild et index.Reorganize. En effet, une des surcharges de cette méthode permet de passer en paramètre le numéro de la partition à défragmenter. Lien MSDN

SMO Créer une base de données en C#

Beaucoup d’applications ont besoin de leur base de données pour fonctionner. Après l’installation d’un logiciel, l’application doit donc créer sa propre base. C’est le cas de Kankuru par exemple. Après son installation, j’utilise SMO pour créer la base de données vide (mais ensuite, contrairement à cet article, j’utilise des scripts SQL pour créer les objets). Dans ce tutoriel, je vais vous montrer comment on peut se passer du moindre script T-SQL pour créer une base de données et ses objets (une table) en utilisant SMO.

Prérequis

Pour les débutants, je vous propose de consulter cet article pour apprendre à créer un projet C# + SMO.

Connexion à l’instance

Oui pour créer une base de données, il faut avant tout être connecté à une instance SQL Server ! Pour la démo, je me connecte à mon instance locale.

string _instance = "localhost";
smoCommon.ServerConnection sc = new smoCommon.ServerConnection(_instance);
sc.Connect();
smo.Server myServer = new smo.Server(sc);

Création de la base de données

Une fois connecté à l’instance, je vais créer une base de données. A ce stade, ce ne sera qu’une enveloppe vide.

smo.Database myDb = new smo.Database(myServer, "TestSMO"); // TestSMO est le nom de la base que je vais créer
myDb.Collation = "French_CI_AS"; // Un exemple de collation
myDb.RecoveryModel = smo.RecoveryModel.Simple; // Il y a aussi BulkLogged et FULL
myDb.CompatibilityLevel = smo.CompatibilityLevel.Version110; // Pour SQL Server 2012

// FileGroup et Fichiers
smo.FileGroup myFileGroup = new smo.FileGroup(myDb, "PRIMARY", false); // True pour un FileStream
myDb.FileGroups.Add(myFileGroup);

// Fichier de données
smo.DataFile myDataFile = new smo.DataFile(myFileGroup, "TestSMO_data");
myDataFile.FileName = myServer.MasterDBPath + "\TestSMO_data.mdf"; // je crée le fichier dans le répertoire par défaut de l'instance mais vous pouvez mettre un chemin complet
myDataFile.Size = 500 * 1024.0; // 500Mo
myDataFile.GrowthType = smo.FileGrowthType.Percent; // None pour pas de croissance et KB si accroissement en taille fixe
myDataFile.Growth = 12; // 12%
myDataFile.IsPrimaryFile = true; // Uniquement sur le fichier primaire hein !
myFileGroup.Files.Add(myDataFile); // j'ajoute le fichier au bon filegroup

//Fichier de transactions
smo.LogFile myLogFile = new smo.LogFile(myDb, "TestSMO_log");
myLogFile.FileName = myServer.MasterDBLogPath + "\TestSMO_log.ldf"; // je crée le fichier dans le répertoire par défaut de l'instance mais vous pouvez mettre un chemin complet
myLogFile.Size = 50 * 1024.0; // 50Mo
myLogFile.GrowthType = smo.FileGrowthType.KB; // None pour pas de croissance et Percent si accroissement en pourcentage
myLogFile.Growth = 20 * 1024.0; // 20 Mo
myDb.LogFiles.Add(myLogFile); // j'ajoute le fichier à ma base (et non pas au filegroup comme le fichier de données)

myDb.Create(); // création de la base, jusqu'ici rien n'avait été créé sur l'instance.

smo_create_database

Création de la table

Une fois ma base créée, je peux y insérer des objets. Par exemple, je vais créer une table avec 2 colonnes dont une clef primaire auto-incrémentée.

// Déclaration de la table
smo.Table myTable = new smo.Table(myDb, "TableTest1", "dbo");
// Première colonne de la table. C'est une PK auto incrémentée
smo.Column myCol1 = new smo.Column(myTable, "id", smo.DataType.Int);
myCol1.Nullable = false;
myTable.Columns.Add(myCol1);

// Auto Incrément
myCol1.Identity = true;
myCol1.IdentityIncrement = 1;

// Définition de la PK
smo.Index myPk = new smo.Index(myTable, "IXPk");
myPk.IsClustered = true;
myPk.IsUnique = true;
myPk.IndexKeyType = smo.IndexKeyType.DriPrimaryKey;
myPk.IndexedColumns.Add(new smo.IndexedColumn(myPk, myCol1.Name));
myTable.Indexes.Add(myPk);

// Deuxième colonne de la table. C'est une chaine de caractère
smo.Column myCol2 = new smo.Column(myTable, "nom", smo.DataType.VarChar(200));
myCol2.Nullable = true;
myTable.Columns.Add(myCol2);

// Création de la table
myTable.Create();

smo Create Table

Script Complet

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using smoCommon = Microsoft.SqlServer.Management.Common;
using smo = Microsoft.SqlServer.Management.Smo;

namespace DemoCreateDatabase
{
class Program
{
static void Main(string[] args)
{
string _instance = "localhost";
smoCommon.ServerConnection sc = new smoCommon.ServerConnection(_instance);
sc.Connect();
smo.Server myServer = new smo.Server(sc);

smo.Database myDb = new smo.Database(myServer, "TestSMO"); // TestSMO est le nom de la base que je vais créer
myDb.Collation = "French_CI_AS"; // Un exemple de collation
myDb.RecoveryModel = smo.RecoveryModel.Simple; // Il y a aussi BulkLogged et FULL
myDb.CompatibilityLevel = smo.CompatibilityLevel.Version110; // Pour SQL Server 2012

// FileGroup et Fichiers
smo.FileGroup myFileGroup = new smo.FileGroup(myDb, "PRIMARY", false); // True pour un FileStream
myDb.FileGroups.Add(myFileGroup);

// Fichier de données
smo.DataFile myDataFile = new smo.DataFile(myFileGroup, "TestSMO_data");
myDataFile.FileName = myServer.MasterDBPath + "\TestSMO_data.mdf"; // je crée le fichier dans le répertoire par défaut de l'instance mais vous pouvez mettre un chemin complet
myDataFile.Size = 500 * 1024.0; // 50Mo
myDataFile.GrowthType = smo.FileGrowthType.Percent; // None pour pas de croissance et KB si accroissement en taille fixe
myDataFile.Growth = 12; // 12%
myDataFile.IsPrimaryFile = true; // Uniquement sur le fichier primaire hein !
myFileGroup.Files.Add(myDataFile); // j'ajoute le fichier au bon filegroup

//Fichier de transactions
smo.LogFile myLogFile = new smo.LogFile(myDb, "TestSMO_log");
myLogFile.FileName = myServer.MasterDBLogPath + "\TestSMO_log.ldf"; // je crée le fichier dans le répertoire par défaut de l'instance mais vous pouvez mettre un chemin complet
myLogFile.Size = 50 * 1024.0; // 50Mo
myLogFile.GrowthType = smo.FileGrowthType.KB; // None pour pas de croissance et Percent si accroissement en pourcentage
myLogFile.Growth = 20 * 1024.0; // 20 Mo
myDb.LogFiles.Add(myLogFile); // j'ajoute le fichier à ma base (et non pas au filegroup comme le fichier de données)

myDb.Create(); // création de la base, jusqu'ici rien n'avait été créé sur l'instance.

// Déclaration de la table
smo.Table myTable = new smo.Table(myDb, "TableTest1", "dbo");
// Première colonne de la table. C'est une PK auto incrémentée
smo.Column myCol1 = new smo.Column(myTable, "id", smo.DataType.Int);
myCol1.Nullable = false;
myTable.Columns.Add(myCol1);

// Auto Incrément
myCol1.Identity = true;
myCol1.IdentityIncrement = 1;

// Définition de la PK
smo.Index myPk = new smo.Index(myTable, "IXPk");
myPk.IsClustered = true;
myPk.IsUnique = true;
myPk.IndexKeyType = smo.IndexKeyType.DriPrimaryKey;
myPk.IndexedColumns.Add(new smo.IndexedColumn(myPk, myCol1.Name));
myTable.Indexes.Add(myPk);

// Deuxième colonne de la table. C'est une chaine de caractère
smo.Column myCol2 = new smo.Column(myTable, "nom", smo.DataType.VarChar(200));
myCol2.Nullable = true;
myTable.Columns.Add(myCol2);

// Création de la table
myTable.Create();

sc.Disconnect();
}
}
}

Conclusion

Je n’ai abordé qu’une toute petite partie des fonctionnalités de SMO mais je pense qu’en terme de création d’objets, il n’y a pas de limite.
Bien évidemment, si vous utilisez SMO pour créer une base de données, pensez à tester les exceptions !

SMO et C# : sauvegarder vos bases de données

Pour sauvegarder les bases SQL Server, on utilise généralement un plan de maintenance ou un script T-SQL mais on peut également réaliser cette opération en C# (ou powershell) avec SMO.

Dans un précédent article, j’expliquais comment créer un projet SMO dans Visual Studio. Pour les sauvegardes, Il faut ajouter la DLL Microsoft.SqlServer.SmoExtended.

La logique du script ci-dessous est relativement simple. Je me connecte à une instance SQL Server et pour chaque base de données, j’effectue une sauvegarde FULL.

static void Main(string[] args)
{
   string _instance = "localhost";
   string _repertoireSauvegarde = @"C:temptest";

   string _horodatage = DateTime.Now.ToString("yyyyMMdd_hhmmss");
   smoCommon.ServerConnection sc = new smoCommon.ServerConnection(_instance);
   sc.Connect();
   smo.Server myServer = new smo.Server(sc);
   foreach (smo.Database myDb in myServer.Databases)
   {
      if (myDb.Name != "tempdb")
      {
         smo.Backup myBackup = new smo.Backup();
         myBackup.Database = myDb.Name;

         // Définit le type de sauvegarde à effectuer  (base ou log)
         myBackup.Action = smo.BackupActionType.Database;

         // Sauvegarde FULL = false, Sauvegarde DIFF = true
         myBackup.Incremental = false;

         // Activation de la compression de la sauvegarde
         myBackup.CompressionOption = smo.BackupCompressionOptions.Default;

         // Ajout du device. Ici il s'agit d'un fichier mais on pourrait envisager une sauvegarde sur bande
         myBackup.Devices.AddDevice(_repertoireSauvegarde + myDb.Name + "_" + _horodatage + ".bak", smo.DeviceType.File);
         try
         {
            myBackup.SqlBackup(myServer);
            Console.WriteLine(myDb.Name + " sauvegardée à " + DateTime.Now.ToString());
         }
         catch (Exception ex)
         {
            Console.WriteLine(ex.Message);
         }
      }
   }
   sc.Disconnect();
}

SMO sauvegarder SQL Server

Pour une sauvegarde différentielle, il suffit d’activer la propriété Incremental à true. Pour une sauvegarde du journal de transaction, il faut modifier la propriété Action à smo.BackupActionType.Log.

Dans la bibliothèque SMO, on retrouve toutes les options disponibles dans l’interface de sauvegarde de SQL Server Management Studio. Pourquoi ? Parce que SSMS utilise lui aussi ces bibliothèques. En voici la preuve, si je tente une sauvegarde dans un répertoire dans lequel je n’ai aucun droit, lisez le message d’erreur que j’ai souligné.

preuve_ssms

Sur le site MSDN, vous trouverez une liste exhaustive de toutes les propriétés, méthodes et évènements concernant les sauvegardes. Bon codage !

SSRS erreur export excel xml illégal

SSRS – Erreur Export Excel – Caractère XML illégal

J’aime bien SSRS ! Certains puristes de la BI pensent que SSRS est amené à disparaitre ou à être beaucoup moins utilisé mais personnellement, je trouve que SSRS est très pratique. Je m’en sers souvent pour générer des fichiers Excel automatiquement.

Je suis tombé régulièrement sur cette erreur lors d’un export d’un rapport en Excel :

ssrs_erreur_export_excel_1

SSRS erreur export excel xml illégal

En fait, cette erreur est lié à des caractères ASCII spécifiques. Peut-être qu’il existe un moyen de contournement plus propre que celui que je vous propose mais je m’en suis sorti en écrivant une méthode VB.net embarquée dans mon rapport SSRS. Voici comment le mettre en place.

Ouvrez la fenêtre Report Properties :

ssrs report propertiesPuis, dans l’onglet Code, collez le script qui supprime les caractères spécifiques :

ssrs_code

Dans le rapport, il convient ensuite d’afficher le texte contenant les caractères spéciaux en utilisant la méthode DelChrChiant.

ssrs_expression

Grâce à cette méthode, mes exports Excel fonctionnent même si les performances sont forcément un peu dégradées.

Voici le code complet :

Public Function DelChrChiant (ByVal s As String) As String
Dim strBuilder As New System.Text.StringBuilder(s)
   strBuilder.Replace(chr(1), "")
   strBuilder.Replace(chr(2), "")
   strBuilder.Replace(chr(3), "")
   strBuilder.Replace(chr(4), "")
   strBuilder.Replace(chr(5), "")
   strBuilder.Replace(chr(6), "")
   strBuilder.Replace(chr(7), "")
   strBuilder.Replace(chr(8), "")
   strBuilder.Replace(chr(9), "")
   strBuilder.Replace(chr(10), "")
   strBuilder.Replace(chr(11), "")
   strBuilder.Replace(chr(12), "")
   strBuilder.Replace(chr(13), " ")
   strBuilder.Replace(chr(14), "")
   strBuilder.Replace(chr(15), "")
   strBuilder.Replace(chr(16), "")
   strBuilder.Replace(chr(17), "")
   strBuilder.Replace(chr(18), "")
   strBuilder.Replace(chr(19), "")
   strBuilder.Replace(chr(20), "")
   strBuilder.Replace(chr(21), "")
   strBuilder.Replace(chr(22), "")
   strBuilder.Replace(chr(23), "")
   strBuilder.Replace(chr(24), "")
   strBuilder.Replace(chr(25), "")
   strBuilder.Replace(chr(26), "")
   strBuilder.Replace(chr(27), "")
   strBuilder.Replace(chr(28), "")
   strBuilder.Replace(chr(29), "")
   strBuilder.Replace(chr(30), "")
   strBuilder.Replace(chr(31), "")
   Return strBuilder.ToString()
End Function

Et vous, comment gérez vous cette problématique ?

SQLSaturday

SQLSaturday 2014 – dans les coulisses

De longues semaines de préparations, des centaines de mails, des dizaines d’heures de réunions téléphoniques et en 2 jours tout est fini. Mais quel plaisir d’avoir participé à cette aventure avec Jean-Pierre, Philippe, David et Charly. Le SQLSaturday 2014 était sans aucun doute le meilleur SQLSaturday auquel j’ai assisté ! Bon OK, ce n’était que mon 2ème SQLSat et je n’ai pas vu grand chose car trop occupé par l’organisation, je n’ai assisté à aucune session le samedi ! Mais aucun regret, je n’ai peut être pas pu améliorer mes compétences techniques, mais j’ai tout de même appris beaucoup de choses.

Pré-conférences du vendredi

C’était une première pour le GUSS et un gros pari. J’avoue avoir été sceptique quand nous avons débattu de l’idée d’organiser une journée de pré-conférence payante en France dont une en anglais. Et finalement, les chiffres sont là, il y avait bien 35 personnes le jour J ! Pour une première, c’est plutôt un succès. Je pense que cette réussite est pour beaucoup liée à la qualité des speakers et du faible tarif : si on s’y prenait en avance, la journée de formation revenait à moins de 100€. Bref, un rapport qualité/prix exceptionnel !

Pour ma part, j’avais choisi d’assister à la conférence de David Barbarin et Christophe Laporte (2 maitres DBA que l’on ne présente plus) sur le stockage des données et les restaurations. La matinée avec David était très intéressante et enrichissante mais peu accessible car assez compliquée. Je me suis fait la réflexion suivante à la pause déjeuner : « On ne fait pas le même métier, j’ai encore pas mal de chose à apprendre avant d’arriver à un tel niveau ! ».

Après le repas, Christophe nous a mis en situation de base corrompue en production avec pleins de scénario de restauration. Je pensais bien maitriser les restaurations, j’ai donc révisé pas mal de choses connues mais j’ai tout de même réussi à apprendre de nouvelles choses. A mon avis, cette session est un incontournable pour n’importe quel DBA !

J’ai passé toute la journée avec 2 puits de science et je ne regrette pas d’avoir posé une journée de congés et d’avoir payé cette formation.

Repas Speakers/Sponsors

Certainement une des choses les plus agréables de ce week end : un repas avec des speakers et sponsors du monde entier ! Alors oui, mon anglais est approximatif, mais c’est tout de même agréable de se retrouver en compagnie de passionnés autour d’un plat et de quelque(s) verre(s) (hic) La seule chose à retenir, c’est que la nuit fut courte 🙂

SQLSaturday Paris : le jour J

Le SQLSaturday Paris cette année pour les visiteurs, c’était 20 sessions techniques dont 5 en anglais, 4 Chalk-Talk (dont 1 sur les femmes dans l’IT qui fut très animé) et au moins 150 participants. Pour nous coté organisation, c’était l’accueil des visiteurs, l’organisation des repas, la gestion des sponsors, l’aide aux speakers, les différents concours, bref beaucoup de tâches réalisées dans l’ombre.

Je n’ai pas eu le temps d’assister aux sessions mais j’ai tout de même rencontré énormément de personnes intéressantes ce samedi :

– Des constructeurs sympas avec qui j’ai pu discuter technique et stockage sur leur baie de disques ultra performante (Nimble Storage et Violin). J’espère trouver un arrangement avec eux pour tester leur baie et écrire un article ici !

– Des éditeurs de logiciel pour SQL Server (MyAms Idera et Apex SQL). Alors, ca ne vaut pas Kankuru hein, mais ils se débrouillent pas mal 😉 Je me rends compte que je n’ai pas rencontré XLCubed mais comme je ne fais pas trop de BI…

– Des acteurs importants du monde SQL Server comme les sociétés de services (Neos Sdi, Azeo, D-Cube, Masao, Umanis).

Les photos

L’accueil des participants et la distribution des badges

accueil_badge

Le Chalk-Talk Women in IT

chalk_talk_women_in_it

Le concours de Selfie avec Jean-Pierre

concours_selfie

Et l’afterwork le samedi soir

after1

after2

Remerciements et conclusions

Je tiens à remercier :

– l’école SupInfo, qui nous a prêté ses locaux pour les 2 journées. Ce n’est pas une mince contribution car les locaux sont superbes et la vue sur Paris depuis le 40ème étage est juste magnifique !

– les étudiants de l’école Supinfo, qui ont réalisé un travail énorme sur les 2 jours et ont permis le bon déroulement de ce SQLSaturday. En plus, c’est toujours un plaisir de voir des étudiants s’investir dans la communauté.

– les sponsors, qui ont financé l’évènement. Pour rappel, le GUSS est une association à but non lucratif et notre seule source de revenu pour financer ces évènements vient des sponsors.

– les membres du board qui ont organisé le SQLSaturday (je n’aurais pas pu le faire seul malgré tout mon talent 😉 ) mais aussi Galla et Ratana qui m’ont relayé une bonne partie de la journée pour accueillir les participants.

J’ai passé 2 journées sympas en compagnie du GUSS, un peu de stress au début, beaucoup de technique le vendredi et pas mal de discussions sympas le samedi. Et maintenant ? Maintenant, les JSS arrivent à grand pas (1 et 2 décembre) et je pense cette fois-ci assister à un maximum de sessions et pourquoi pas en animer une…