Premier contact avec SQL Server 2016

Avec quelques jours d’avance, SQL Server 2016 CTP2 est disponible publiquement au téléchargement.

image

Vous avez ainsi la possibilité de tester la nouvelle mouture de SQL Server, ses nouvelles fonctionnalités et les améliorations des précédentes.

D’autant plus que la documentation en ligne est également disponible.

Globalement, niveau  installation, rien de particulier. Les habitués ne seront pas dépaysés.

imageimage

Je vous épargne les écrans suivants que vous maitrisez probablement.

En revanche, sur l’écran de sélection des fonctionnalités, enfin du nouveau :

image

Polybase est enfin disponible, édition entreprise il y a fort à parier.

Afin de bénéficier de cette fonctionnalité, il faudra néanmoins installer une JRE 7.51. Et j’avoue que ce point me chagrine un peu. je suis assez réfractaire quant à l’installation de Java sur un serveur SQL. Bref.

image

Même si votre serveur Windows 2012R2 est up-to-date, il faudra néanmoins ajouter un KB supplémentaire afin de pouvoir poursuivre l’installation.

image

690MB et un reboot plus tard, vous serez en mesure de poursuivre l’installation.

Et bonne surprise, on se préoccupe enfin de la base TempDB et de la contention si vous ne disposez que d’une seul fichier de data (principalement sur la page PFS – 2:1:1).

image

 

De manière étonnante, nous avons droit à une version 13 … J’avoue que je m’attendais à basculer directement sur une version 14. Histoire de superstition outre-atlantique. A vérifier sur la RTM.

image

Car il reste quelques bugs à corriger comme l’intitulé de la version SQL ( 2015 ):

image

 

Polybase !

Présenté par le Dr David DeWitt lors du Pass Summit 2012, polybase offre  la possibilité de requêter un cluster Hadoop depuis SQL Server. On imagine sans peine un scénario alliant un stockage massif pour des données non structurées ou semi structurées dans Hadoop et des données agrégées ou semi-agrégées stockées dans SQL Server. Et exécuter des requêtes sur les 2 sources de données, indifféremment.

PolyBase Concept

 

 

Query Store

Autre fonctionnalité attendue : le query store. Le but est ici de fournir aux DBA des outils (DMVs) afin de comprendre des changement au niveau des performances dus à des plans d’exécution différents.

image

 

Live Query Statistics

SQL Server 2014 avait introduit une nouvelle DMV (SQL 2014 – Nouvelle DMV sys.dm_exec_query_profiles) afin de visualiser l’état d’avancement de l’exécution d’une requête en présentant chaque opérateur, avec le nombre de lignes traitées, etc … Voici maintenant la version graphique. Très réussie. Sourire

 

Strech Databases

Enfin, dirons nous. L’archivage des données, avouons le, était un problème épineux. Au pire, on ne faisait rien et toutes les données restaient dans la ou les tables de production, pour des années voire plus. Soit on concevait une politique d’archivage qui déplaçait les enregistrements “froids” et/ou “tièdes” dans des tables spécifiques. Encore fallait il pouvoir lire simplement ces données et produire des requêtes/vues intégrant données chaudes et données froides. Ou bien, encore, on jouait avec les tables partitionnées.

Les strech databases vont permettre, simplement, d’archiver de manière transparente des données dans Azure, avec à la clé un gain d’espace et un cout de stockage réduit.

Incontestablement dans le top 5 des fonctionnalités de SQL Server 2016.

 

Temporal Tables

Ou la possibilité de requêter une table et renvoyer des données telle qu’elles étaient à un point donné dans le temps ! Cette fonctionnalité est une également une de mes préférées.

 

Sécurité

De belles nouveautés sur le plan sécurité avec le dynamic data masking, le row level security et des nouvelles permissions.

 

Haute disponibilité

Les groupes de disponibilité se voient dotés du round robin pour les accès en lecture seule au travers du listener, ce qui permet de balancer la charge sur l’intégralité des serveurs secondaires. Les transactions distribuées sont enfin supportées et le nombre de serveurs pouvant bénéficier de la bascule automatique passe de 2 à 3.

 

Bref, de nombreux articles à venir …

Bon téléchargement.

Publié dans SQL Server | Marqué avec | Laisser un commentaire

SQL Server vNext devient SQL Server 2016

Enfin quelques nouvelles (publiques) de la prochaine version de SQL Server ….

Tout d’abord, enfin, nous connaissons le nom : SQL Server 2016.

La datasheet, téléchargeable ici.

image

Elle confirme l’orientation cloud de Microsoft avec les strech tables, où le déplacement automatique des données froides vers le cloud alors que les données chaudes restent sur votre server OnPremise. Bref, un scénario hybride dont je ne manquerai pas de vous reparler.

 

L’accent est aussi mis sur la haute disponibilité avec des améliorations attendues  dans AlwaysOn : le support de MsDTC et l’accès en round-robin sur les serveurs secondaires.

D’un point de vue performances, il sera dorénavant  possible d’utiliser le columnstore index sur des données OLTP sous Hekaton. Le meilleur des deux mondes ! De l’OLTP ultra performant (et avec moins de contraintes qu’avec la version SQL 2014) et le columnstore index en read/write pour l’analyse …

Polybase sera aussi inclu dans SQL Server (présent dans APS auparavant), permettant ainsi l’accès à des données non structurées (Hadoop)  et structurées via le langage t-SQL. Au passage, on peu noter le support natif de JSON !

Une petite dose de sécurité (Row level security et Dynamic Data Masking) viennent compléter le tableau de cette nouvelle mouture de SQL Server.

Bienvenue à SQL Server 2016 !!!!

Stay tuned

Publié dans SQL Server | Laisser un commentaire

Groupes de disponibilité et disaster recovery

Vous le savez probablement, une des fonctionnalité phare de SQL Server 2012 (aka Denali) est AlwaysON.

Mais AlwaysOn n’est qu’une “marque” regroupant les technologies de haute disponibilité dans SQL Server, à savoir le FCI, ou Fail Over Cluster Instance, et les groupes de disponibilité.

J’ai pour ma part énormément communiqué sur ces fonctionnalités de haute disponibilité, que ce soit lors des Techdays (2012, 2013, 2014) , des Journées SQL Server, ou sur ce blog(Création d’un groupe de disponibilité, plusieurs groupes de disponibilité, Ajout d’un nœud à un Groupe de disponibilité).

Mais, au final, peu de littérature sur le Disaster Recovery, le “vrai”. Imaginez un scénario dans lequel vous disposez de 2 Datacenter. Le principal héberge deux réplica de votre groupe de disponibilité, pour le PCA, alors que le second Datacenter, lui, n’est là que pour pallier à une défaillance “majeure” puisque l’on perdrait 2 nœuds sur 3 dans le cluster, le PRA.  Voilà donc le décors planté.

Je ne vais pas vous abreuver une fois de plus de copies d’écran sur la création du cluster ou bien l’installation de SQL Server, l’activation de la fonctionnalité HADRON si la mise en haute disponibilité d’une base au sein d’un groupe de disponibilité.

Bref, partons du principe que les 3 nœuds sont configurés et opérationnels au sein du cluster. Node1 et Node2 dans le Datacenter principal et Node3 dans le Datacenter secondaire.

image

Le groupe de disponibilité est lui aussi configuré et opérationnel.

image

image

Tant qu’a bien faire les choses, il est possible de ne pas attribuer de vote à un nœud du cluster qui ne sert qu’au Disaster Recovery.

(Get-ClusterNode Node3).NodeWeight = 0

image

Le témoin dynamique fonctionne ici parfaitement pour “équilibrer” les votes au sein du cluster afin d’avoir un nombre de votes impair et disposer d’un quorum.

Et là, c’est le drame. Perte totale, et durable, du Datacenter principal. Les ouragans passent, les avions tombent, les pelleteuses arrachent des fibres, bref autant d’histoires connues. Mais, par chance, ou plutôt parce qu’un architecte a pensé au Disaster Recovery, le Datacenter secondaire comporte un minimum de serveur lié à l’infrastructure (AD, DNS).

Il suffit alors de stopper le service cluster, un simple Stop-Service ou bien via le gestionnaire de services Windows, qui doit être théoriquement en méforme.

Ensuite, nous allons redémarrer le cluster en forçant le quorum, a n’utiliser bien sur que dans ce genre de cas extrêmes.

$node = "Node3"
Start-ClusterNode -Name $node -FixQuorum

image

Quelques instants plus tard, le cluster est de nouveau en vie (on peut à présent lancer la console de gestion du cluster pour s’en assurer).

image

Cependant, d’un point de vue groupe de disponibilité, tout n’est pas terminé car le groupe de ressource est OFFLINE.

image

Il suffit alors de forcer la bascule du groupe de disponibilité, en autorisant la perte éventuelle de données (vous pouvez également le faire en T-SQL) :

Switch-SqlAvailabilityGroup `
   -Path SQLSERVER:\Sql\Node3\Default\AvailabilityGroups\AgDemo `
   -AllowDataLoss -Force

image

Et le groupe de disponibilité redevient ONLINE, prêt pour redémarrage des applications.

image

A compter de ce moment là, la solution n’est plus hautement disponible (il faudrait juste ajouter un second nœud dans le Datacenter secondaire. Mais la production (d’un point de vue service SQL) a pu reprendre.

Lorsque la Datacenter principal sera de nouveau opérationnel, les serveurs vont rejoindre le cluster, sans action de votre part.

image

Mais d’un point de vue bases de données, le groupe de disponibilité n’est repassé en mode synchronisé car le mouvement de données est alors suspendu.

image

Un simple “Resume Data Movement” et les bases vont progressivement se resynchroniser avec le serveur de Disaster Recovery.

image

Pour au final disposer de nouveau de bases de données en haute disponibilité

image

Libre à vous ensuite de refaire des bascules pour remettre les bases sur les serveurs principaux …

Si vous avez des questions ou si vous souhaitez mettre en œuvre une solution de HA/DR SQL Server, en environnement physique ou virtuel, n’hésitez pas, contactez moi !

Happy DR

Publié dans SQL Server, Windows | Marqué avec , , | Laisser un commentaire

Enregistrement des compteurs de performance dans un fichier BLG

Une de mes principales activités, outre le conseil en bases de données (architecture, virtualisation, haute disponibilité, montée en charge, …) consiste a réaliser des audits de performance et par la suite effectuer du tuning, autant côté système que base de données.

Afin de gagner du temps, ou lorsque l’on est face à une situation urgente et que mon emploi du temps ne me permet pas d’agit dans l’immédiat, je demande alors à une personne de me créer un fichier BLG que je pourrai ensuite analyser offline une fois téléchargé sur mon site FTP.

Et, relativement fréquemment, les administrateurs / DBAs ne connaissent pas cette possibilité offerte par Perfmon, nativement présent dans Windows.

La fonctionnalité la plus connue de Perfmon est de procurer un rendu visuel temps réel, dans la section Monitoring Tools | Performance Monitor. Mais il est aussi possible d’enregistrer un fichier, format “binaire”, contenant les valeurs des compteurs de performance capturées à intervalles réguliers. Une fois la capture terminée, il est alors possible de l’analyser a postériori et éventuellement sur un PC afin de ne pas travailler directement sur le serveur.

image

Voici donc un petit tutorial qui permet de créer un fichier BLG. Un click droit sur User Defined, dans la section Data Collector Sets et …suivez le guide :

image

On donne un petit nom au data collector set, et sachant que l’on va spécifier nos propres compteurs, on sélectionne l’option Create manually, et dans l’écran suivant Performance Counter (Pour els habitués, on peut également importer un fichier XML contenant els compteurs, typiquement issus du PAL).

imageimage

Ensuite, on va éditer le Data Collector afin de spécifier nos paramètres et ajouter nos métriques

image

Il est possible de modifier l’intervalle de capture en fonction des besoins. Si je souhaite une analyse fine, sur une durée limitée, alors j’opte pour un intervalle d’une seconde. Tout dépend aussi du nombre de compteurs choisis, cela influe sur le volume du fichier BLG. Ensuite on ajoute les compteurs de performance système et SQL nécessaire à l’analyse. Si vous êtes un peu perdu et que vous ne savez pas quels compteurs choisir, reportez vous à ma session lors des JSS 2014.

imageimage

J’apprécie également de modifier le nommage du fichier BLG afin d’ajouter le nom du serveur ainsi qu’une date et heure. Croyez-en mon expérience, lorsque l’on se retrouve avec 10 fichiers qui portent tous le même nom (DataCollector01.blg), difficile de s’y retrouver, bien que le nom du répertoire sur le serveur comporte le HostName et un numéro séquentiel.

image

Ensuite, il est possible de spécifier des conditions de démarrage et d’arrêt de la capture. Par ce que je n’apprécie guerre de me lever la nuit pour activer une collecte, je vous suggère de planifier l’exécution ! Vous pouvez disposer de 0 ou N planifications par CollectorSet.

imageimage

Vous pouvez également spécifier des conditions d’arrêt, en fonction de la durée de capture, de la taille du fichier BLG afin de ne pas saturer le disque, voire même relancer automatiquement une capture, sur un autre fichier, de manière automatique. On peut ainsi avoir un fichier BLG par heure, ou par tranche de 6 heures suivant les besoins.

Les plus organisés peuvent aussi positionner plusieurs Data Collectors dans un même Set afin de séparer les données système des données SQL Server par exemple.

image

Un click droit sur le collector set permet de démarrer la collecte ou bien de la stopper manuellement.

imageimage

Ensuite, laissons la capture s’effectuer. Un petit symbole (flèche verte dans un cercle blanc) permet de savoir si la capture est en cours.

image

Une fois la capture terminée, le fichier BLG est disponible.

image

Un simple double click sur le fichier permet de l’ouvrir et de l’exploiter, avec la possibilité de cacher certains compter, de “zoomer” en sélectionnant un intervalle de temps, changer l’échelle les compteurs, ….

image

Si vous disposez également d’une trace SQL couvrant le même intervalle de temps, le profiler vous permet alors de croiser les informations et ainsi d’identifier des requêtes en fonction des métriques système, et inversement de visualiser la consommation de ressources lors de l’exécution de requêtes spécifiques.

Le troubleshooting commence nécessairement par la collecte de données sur les bons indicateurs, que ce soit les compteurs de performance ou bien les DMVs/DMFs. Maintenant plus aucune excuse pour ne pas savoir créer un fichier BLG.

Enjoy

Publié dans SQL Server, Windows | Marqué avec , | 2 commentaires

Job–Recherche DBAs

En plus des différentes missions de consulting et de formation que je suis amené à réaliser, j’assiste certains de mes clients pour du recrutement de DBA.

Alors, que vous soyez DBA junior, ou bien DBA plus expérimenté :

J’ai actuellement 2 postes ouverts, dans deux sociétés Parisiennes distinctes. Les postes sont à pourvoir rapidement et ne se cantonnent pas qu’à de l’administration SQL Server. Donc même si vous avez un profil Administrateur Système amis que vous avez déjà un peu manipulé du SQL Server, c’est jouable.

Contactez-moi directement par email ou via la page de contact du blog. Le seul risque que vous prenez c’est de devoir répondre à mes questions (techniques) sur la partie SQL Server lors de l’entretien …

Publié dans Job | Laisser un commentaire

Storage Replica–Windows Server vNext

Une des fonctionnalités phare de la prochaine version de Windows Server vNext est sans nul doute le storage replica (lire le blog de l’équipe storage Windows).

Imaginez vous, simplement, sans logiciels d’un quelconque éditeur/constructeur tiers, répliquer les données d’un volume depuis une baie de disque située dans un premier datacenter vers une seconde baie de disque située dans un second datacenter.

Attention on parle bien ici de solution de disaster recovery et non pas de haute disponibilité, même si cela pourrait fonctionner.

D’autres s’y sont attaqués avant, mais on bénéfice ici de toute l’interopérabilité avec les autres fonctionnalités Windows.

Dans cette Technical Preview, a priori, seul Hyper-V est “supporté”. Mais en ce qui me concerne, je préfère me focaliser sur SQL Server, et donc le Scale-Out File Server afin de supporter les bases d’un cluster SQL Server, dont j’avais parlé ici. mais cette fois-ci on ajoute la possibilité d’effectuer du disaster recovery, sans passer les groupes de disponibilité.

Le cluster SQL sera donc multi site, avec du stockage asynchrone, comme j’ai pu le faire au travers de DataKeeper.

Attention : a priori le Scale-Out File Server n’est pas supporté pour l’instant. Et SQL Server branché sur cette architecture encore moins …

J’espère seulement que la version finale de Windows et la prochaine release de SQL Server pourront directement bénéficier de cette fonctionnalité pour du FCI dans passer par un stockage SMB3.0. Attaquer directement un volume répliqué serait vraiment top.

Tout d’abord créons l’infrastructure disque avec le storage replica.

D’un point de vue préparatifs, j’ai créé 4 VMs, 2 sur chaque site. Je créé ensuite2 LUNS sur chaque baie (stockage asynchrone) avec un zoning ne permettant aux nœuds que de voir le stockage du site local. J’ai choisi d’utiliser le protocole iSCSI (avec masque sur mes iqn, pensez à modifier vos scripts), mais si vous avec du FC cela fonctionne aussi bien évidement. Je n’ai aussi testé avec du Shared VHDX. Cela fonctionne !!!

# ajout des fonctionalités 
$Servers = 'sr-site1-n1','sr-site1-n2','sr-site2-n3','sr-site2-n4'
$Servers | ForEach { Install-WindowsFeature -ComputerName $_ `
-Name File-services,WVR,Failover-Clustering -IncludeManagementTools -restart }


# formation du cluster
New-Cluster -Name sr-sofs -Node sr-site1-n1,sr-site1-n2,sr-site2-n3,sr-site2-n4 `
-NoStorage -StaticAddress 192.168.1.220

# Add a FSW
Set-ClusterQuorum -Cluster sr-sofs -FileShareWitness "\\DC\FSW"

Ensuite, on configure la partie disque sur les nœuds du site 1

#site 1


Invoke-Command -ComputerName 'sr-site1-n1','sr-site1-n2' -ScriptBlock {

    Set-Service -Name msiscsi -StartupType Automatic

    Start-Service msiscsi

    New-IscsiTargetPortal -TargetPortalAddress ds415

    Connect-IscsiTarget -NodeAddress "iqn.2000-01.com.xxx.target-site1.xxx"  `
     -IsPersistent $True 

    Get-iSCSISession | Get-Disk
}



Invoke-Command -ComputerName 'sr-site1-n1' -ScriptBlock {

 Get-Disk | Where-Object IsOffline -Eq $True | Set-Disk -IsOffline $False

 Initialize-Disk -Number 1 -PartitionStyle GPT
 Initialize-Disk -Number 2 -PartitionStyle GPT

 New-Partition -DiskNumber 1 -DriveLetter E -UseMaximumSize
 New-Partition -DiskNumber 2 -DriveLetter F -UseMaximumSize

 Format-Volume -DriveLetter E -AllocationUnitSize 65536 -FileSystem NTFS `
     -NewFileSystemLabel "Site1-Data" -confirm:$false
 Format-Volume -DriveLetter F -AllocationUnitSize 65536 -FileSystem NTFS `
    -NewFileSystemLabel "Site1-Log"  -confirm:$false
    
}


Invoke-Command -ComputerName 'sr-site1-n2' -ScriptBlock {

 Get-Disk | Where-Object IsOffline -Eq $True | Set-Disk -IsOffline $False
     
}




# add all disk to the available storage
Get-ClusterAvailableDisk -cluster sr-sofs | Add-ClusterDisk

Move-ClusterGroup -Cluster sr-sofs -Name "Available Storage" -Node sr-site1-n1


Invoke-Command -ComputerName 'sr-site1-n1' -ScriptBlock {

    $NewName = (get-volume -DriveLetter E).FileSystemLabel
    #write-host $NewName
    (Get-ClusterResource -Name "Cluster Disk 1" ).Name = $NewName

    $NewName = (get-volume -DriveLetter F).FileSystemLabel
    #write-host $NewName
    (Get-ClusterResource -Name "Cluster Disk 2" ).Name = $NewName
   
} 
      

Ensuite, on effectue la même opération sur les 2 nœuds du site 2.

# site 2

Invoke-Command -ComputerName 'sr-site2-n3','sr-site2-n4' -ScriptBlock {

    Set-Service -Name msiscsi -StartupType Automatic

    Start-Service msiscsi

    New-IscsiTargetPortal -TargetPortalAddress ds415

    Connect-IscsiTarget -NodeAddress "iqn.2000-01.com.xxx.target-site2.xxx"  `
    -IsPersistent $True 

    Get-iSCSISession | Get-Disk
}


restart-vm -ComputerName vnext2 -Name "sr-site2-n3" -Confirm:$false
restart-vm -ComputerName vnext2 -Name "sr-site2-n4" -Confirm:$false

Invoke-Command -ComputerName 'sr-site2-n3' -ScriptBlock {

 Get-Disk | Where-Object IsOffline -Eq $True | Set-Disk -IsOffline $False

 Initialize-Disk -Number 1 -PartitionStyle GPT
 Initialize-Disk -Number 2 -PartitionStyle GPT

 New-Partition -DiskNumber 1 -DriveLetter E -UseMaximumSize
 New-Partition -DiskNumber 2 -DriveLetter F -UseMaximumSize

 Format-Volume -DriveLetter E -AllocationUnitSize 65536 -FileSystem NTFS `
   -NewFileSystemLabel "Site2-Data" -confirm:$false
 Format-Volume -DriveLetter F -AllocationUnitSize 65536 -FileSystem NTFS `
   -NewFileSystemLabel "Site2-Log"  -confirm:$false     
}


Invoke-Command -ComputerName 'sr-site2-n4' -ScriptBlock {

 Get-Disk | Where-Object IsOffline -Eq $True | Set-Disk -IsOffline $False
     
}



# add all disk to the available storage
Get-ClusterAvailableDisk -cluster sr-sofs | Add-ClusterDisk

Move-ClusterGroup -Cluster sr-sofs -Name "Available Storage" -Node sr-site2-n3


Invoke-Command -ComputerName 'sr-site2-n3' -ScriptBlock {

    $NewName = (get-volume -DriveLetter E).FileSystemLabel
    #write-host $NewName
    (Get-ClusterResource -Name "Cluster Disk 1" ).Name = $NewName

    $NewName = (get-volume -DriveLetter F).FileSystemLabel
    #write-host $NewName
    (Get-ClusterResource -Name "Cluster Disk 2" ).Name = $NewName
   
} 

 

Reste maintenant à configurer le Storage Replica

# all disk resrouces on node 1
Move-ClusterGroup -Cluster sr-sofs -Name "Available Storage" -Node sr-site1-n1


# add the data disk to CSV volume
Add-ClusterSharedVolume -cluster sr-sofs -Name "Site1-Data"

Move-ClusterSharedVolume -cluster sr-sofs -Name "Site1-Data" -Node sr-site1-n1


Invoke-Command -ComputerName 'sr-site1-n1' -ScriptBlock {

New-SRPartnership -SourceComputerName sr-site1-n1 -SourceRGName rg01 `
  -SourceVolumeName "C:\ClusterStorage\Volume2" -SourceLogVolumeName f: `
  -DestinationComputerName sr-site2-n3 -DestinationRGName rg02 `
  -DestinationVolumeName e: -DestinationLogVolumeName f: -LogSizeInBytes 1gb
}

 

image

A compter de ce point, la réplication est effective entre les 2 sites. Il est possible de basculer les disques sur le site 2 et de voir les données.

Maintenant, ajout de la fonctionnalité Scale-Out File Server

# Add Role Filehare / SOFS
Add-ClusterScaleOutFileServerRole -Name "SR-SQL" -Cluster sr-sofs


# Create file share
Move-ClusterSharedVolume -Name "site1-Data" -Node sr-site1-n1

MD C:\ClusterStorage\Volume2\SQL
New-SmbShare -Name SQL -Path C:\ClusterStorage\Volume2\SQL `
   -FullAccess LAB\Administrator

 

image

Reste à présent à installer 2 autres VMs, une sur chaque site, afin d’héberger les 2 nœuds du cluster SQL (cela pourrait être un cluster 4 nœuds …).

Si cette architecture semble répondre à vos besoins, n’hésitez pas à me contacter pour en parler.

Stay tuned …

Enjoy !

Publié dans Windows | Marqué avec , , , , , | Laisser un commentaire

Qui a fait quoi sur ma table ?

Lors d’une discussion sur un réseau social bien connu avec Christian (@chrisql) et Isabelle (@sqlgrrl) autour du thème du qui a fait quoi dans une base, j’ai proposé une solution qui évitait le recours à des fonctionnalités de l’édition entreprise de SQL Server.

Et, au delà de cette discussion Francophone (Suisse, Belgique et France), la question m’a été souvent posée chez des clients.

Alors, bien sur la solution n’est pas parfaite, mais, elle fourni déjà beaucoup d’informations …

Reprenons l’histoire au début. Lorsque l’on veut savoir le qui-fait-quoi, dans SQL Server, il n’existe pas des dizaine de solutions.

  • On peut utiliser l’API SQL Trace, communément utilisée au travers du Profiler SQL Server, qui permet de tracer toutes les requêtes qui arrivent sur SQL Server. Fonctionnel. Mais attention aux ressources consommées sur le serveur !!! Même si vous filtrez votre trace, sachez que le filtre ne s’applique que lors du stockage de l’information, pas lors de la capture. Donc SQL Trace capture toutes les requêtes ! Je vus laisse imaginer le résultat sur un environnement bien sollicité …
  • Depuis SQL Server 2008 il est possible d’utiliser  les XEvent, les évènements étendus. Alors, oui, en 2008 et 2008R2, il fallait aimer el code SQL et le parsing de contenu XML pour s’en sortir. Autant le côté trace était opérationnel, autant le côté restitution laissait à désirer, soyons honnêtes. Enfin, en 2012, SSMS propose une IM qui permet à la fois de créer des sessions d’évènements étendus ET de visualiser / traiter le résultat. En live ou a postériori. Enfin une bonne solution, d’autant que le surtout n’est absolument pas comparable avec celui de SQL Trace. Cette fois ci le filtre se fait lors de la capture, bien plus efficace donc.
  • On peut également mettre en place des triggers sur les tables. Bon ça reste un trigger, donc ….

Ces 3 techniques sont intéressantes, mais quelque peu “brutes”, dirons nous.

Depuis SQL Server 2008, il existe aussi les audits de sécurité. Au final, ce ne sont que des XEvents qui ont été packagés dans une IHM spécifique. Ainsi, il est possible, de manière extrêmement simple de mettre en place un audit sur des opérations de type INSERT / UPDATE / DELETE sur des tables. Tout comme on pourrait auditer l’accès à des données sensibles sur du SELECT, ou bien du EXEC de procédure stockée.

Bref, voici un petit exemple :

-- Création de l'audit et activation de celui-ci
CREATE SERVER AUDIT AuditDataAccess
TO FILE ( FILEPATH ='C:\SQLAudit\'
		  ,MAXSIZE = 0 MB
		  ,MAX_ROLLOVER_FILES = 2147483647
		  ,RESERVE_DISK_SPACE = OFF )
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
)
GO

ALTER SERVER AUDIT AuditDataAccess WITH (STATE = ON);
GO
USE [AdventureWorks]
GO

-- Création d'une spécification d'audit pour tracer
-- - les écritures sur la table empployee
-- - les select sur l'intégralité sur schéma
CREATE DATABASE AUDIT SPECIFICATION [AuditSpecificationHR]
FOR SERVER AUDIT [AuditDataAccess]
ADD (INSERT,UPDATE,DELETE 
          ON OBJECT::[HumanResources].[Employee] BY [public]),
ADD (SELECT,EXECUTE       
          ON SCHEMA::[HumanResources]            BY [public])
WITH (STATE = ON) ;
GO
-- Consultation de l'audit
SELECT * 
FROM sys.fn_get_audit_file ('C:\SQLAudit\AuditDataAccess*.sqlaudit',
                                                    default,default);

La consultation peut aussi se faire dans SSMS, de manière graphique.

La mariée serait elle trop belle? Possible, car cela sous entend que la session d’audit existait déjà (alors qu’il est peut être trop tard … l’update a déjà été fait) et …. que … l’on dispose de l’édition entreprise de SQL Server.

Comment, donc, rechercher qui a effectué une modification de donnée (oui, la solution n’est pas parfaite car elle ne couvre pas le SELECT) qui s’est produite quelques heures/jours auparavant ?

La première chose, car il faut quand même quelques prérequis : la base doit être en mode de récupération complet ou bulklogged. Sinon le contenu du journal de transaction est vidé lors du checkpoint, qui est issu lorsque le journal dépasse un seuil de remplissage de 70%.

Le journal de transaction, donc, est chargé, comme son nom l’indique d’enregistrer les transactions. Cela tombe bien, on recherche une transaction, une modification de données effectuée par erreur, par exemple. Il faut donc explorer le journal de transaction. Cela peut se faire (attention si vous passez cette commande sur la prod …) a l’aide de la fonction fn_dblog. Sauf que cela suppose que le journal n’a pas été sauvegardé ! EN mode de récupération complet et bulklogged, le journal est vidé lorsque une sauvegarde du journal est effectuée.

Notre salut viendrait donc des sauvegardes du journal de transaction ! Il est possible de lire ces sauvegardes a l’aide de la fonction fn_dump_dblog. Attention, une nouvelle fois. Utiliser ces fonctions n’est pas un jeu !!!

Mettons en place un petit scénario. 3 utilisateurs qui vont effectuer des requêtes sur une table. Je vais en profiter pour utiliser els bases partiellement contenues (nouveauté SQL Server 2012). Cela n’est en rien obligatoire dans cette démo.


EXEC sys.sp_configure N'contained database authentication', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

CREATE DATABASE DemoDB
GO

ALTER DATABASE [DemoDB] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO


USE DemoDB
GO


CREATE TABLE DemoTable
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	filler CHAR(50)
)


CREATE USER [SQLUser1] WITH PASSWORD=N'Password1', DEFAULT_SCHEMA=[dbo];
CREATE USER [SQLUser2] WITH PASSWORD=N'Password1', DEFAULT_SCHEMA=[dbo];
CREATE USER [SQLUser3] WITH PASSWORD=N'Password1', DEFAULT_SCHEMA=[dbo];
GO


CREATE ROLE [db_MyAppRole] AUTHORIZATION [dbo];
GO

GRANT INSERT,DELETE,UPDATE,SELECT ON SCHEMA::[dbo] TO [db_MyAppRole];
GO

ALTER ROLE [db_MyAppRole] ADD MEMBER [SQLUser1];
ALTER ROLE [db_MyAppRole] ADD MEMBER [SQLUser2];
ALTER ROLE [db_MyAppRole] ADD MEMBER [SQLUser3];
GO


-- Une sauveagarde pour vider le journal de transactions
BACKUP DATABASE DemoDB
TO DISK = 'NUL'; -- Attention, pas en prod !!!

BACKUP LOG DemoDB
TO DISK = 'NUL'; -- Attention, pas en prod !!!
GO

A présent, les requêtes de test. Il s’agit ici de transactions imbriquées, mais encore une fois, cela fonctionne avec des transactions “simples”.


-- Login as SQLUser1
BEGIN TRANSACTION SQLUser1_Transaction
	
	SELECT USER_NAME();
	
	INSERT INTO DemoTable (filler)
	VALUES ('SQLUser 1 - Insert');

	-- Login as SQLUser2
	BEGIN TRANSACTION SQLUser2_Transaction

		SELECT USER_NAME();

		INSERT INTO DemoTable (filler)
		VALUES ('SQLUser 2 - Insert');

		UPDATE DemoTable 
		SET filler = 'SQLUser 2 - Update'
		WHERE ID = 2;

		-- Login as SQLUser3
		BEGIN TRANSACTION SQLUser3_Transaction

			SELECT USER_NAME();

			INSERT INTO DemoTable (filler)
			VALUES ('SQLUser 3 - Insert');

			UPDATE DemoTable 
			SET filler = 'SQLUser 3 - Update'
			WHERE ID = 3;

			DELETE FROM DemoTable 
			WHERE ID = 3;

		COMMIT TRANSACTION SQLUser3_Transaction

		DELETE FROM DemoTable 
		WHERE ID = 2;

	COMMIT TRANSACTION SQLUser2_Transaction


	UPDATE DemoTable 
	SET filler = 'SQLUser 1 - Update'
	WHERE ID = 1;

	DELETE FROM DemoTable 
	WHERE ID = 1;

COMMIT TRANSACTION SQLUser1_Transaction

A présent, un backup du journal de transaction. Ce backup, et donc les transactions qu’il contient, peuvent dater de maintenant, hier ou bien avant, cela ne modifie en rien le scénario.

BACKUP Log DemoDB
TO DISK = 'c:\temp\DemoDB.trn'

Il est temps à présent d’explorer cette sauvegarde. Je vous suggère de recopier les données dans une table temporaire, afin de pouvoir travailler plus facilement et plus rapidement par la suite.

SELECT *
INTO #temp_dump_dblog
FROM   fn_dump_dblog( DEFAULT, DEFAULT,DEFAULT, DEFAULT,  
'C:\temp\DemoDB.trn', DEFAULT,DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT ) AS LogRecords;
GO

Maintenant, il ne reste plus qu’a requêter cette table temporaire, effectuer quelques opérations a l’aide de la clause OVER pour récupérer le début et la fin des transactions et de croiser les données avec database_pincipals. Je vous laisse ensuite remonter à server_principals et d’autres infos si vous le souhaitez.


-- Result ordered by LSN
WITH db_log AS 
(
SELECT [Current LSN],
FIRST_VALUE([Current LSN]) OVER (PARTITION BY [Transaction ID] 
	ORDER BY [Current LSN]) AS [Begin_Tran_LSN],
LAST_VALUE([Current LSN])  OVER (PARTITION BY [Transaction ID] 
	ORDER BY [Current LSN]
	ROWS BETWEEN CURRENT ROW	
		AND UNBOUNDED FOLLOWING    ) AS [End_Tran_LSN],
[Transaction ID],  
FIRST_VALUE([Transaction SID]) OVER (PARTITION BY [Transaction ID] 
	ORDER BY [Current LSN]) AS [Transaction SID],
FIRST_VALUE([Begin Time]) OVER (PARTITION BY [Transaction ID] 
	ORDER BY [Current LSN]) AS [Begin_Transaction_DT],
LAST_VALUE([End Time])  OVER (PARTITION BY [Transaction ID] 
	ORDER BY [Current LSN]
	ROWS BETWEEN CURRENT ROW	
		AND UNBOUNDED FOLLOWING    ) AS [End_Transaction_DT],
FIRST_VALUE([Transaction Name]) OVER (PARTITION BY [Transaction ID] 
	ORDER BY [Current LSN]) AS [Transaction Name],
[OPERATION]
FROM   #temp_dump_dblog l
		
),
    transaction_user AS
(
SELECT * 
FROM db_log l
INNER JOIN sys.database_principals p on p.sid = l.[Transaction SID] 
WHERE  [Transaction ID]  '0000:00000000'
AND [OPERATION] IN ('LOP_BEGIN_XACT','LOP_COMMIT_XACT',
'LOP_INSERT_ROWS','LOP_MODIFY_ROW','LOP_DELETE_ROWS') 
)
SELECT [Current LSN],[Begin_Tran_LSN],[End_Tran_LSN],
	   [Begin_Transaction_DT],[End_Transaction_DT],[Transaction Name],
	   [OPERATION],[Name] AS [User_Name]
FROM transaction_user
WHERE [Transaction Name] NOT IN ('Backup:CommitLogArchivePoint',
                                 'AllocFirstPage','Allocate Root') 
ORDER BY [Current LSN]





-- Result ordered by User / LSN
WITH db_log AS 
(
SELECT [Current LSN],
FIRST_VALUE([Current LSN]) OVER (PARTITION BY [Transaction ID] 
	ORDER BY [Current LSN]) AS [Begin_Tran_LSN],
LAST_VALUE([Current LSN])  OVER (PARTITION BY [Transaction ID] 
    ORDER BY [Current LSN]
	ROWS BETWEEN CURRENT ROW	
	 AND UNBOUNDED FOLLOWING    ) AS [End_Tran_LSN],
[Transaction ID],  
FIRST_VALUE([Transaction SID]) OVER (PARTITION BY [Transaction ID] 
	ORDER BY [Current LSN]) AS [Transaction SID],
FIRST_VALUE([Begin Time]) OVER (PARTITION BY [Transaction ID] 
	ORDER BY [Current LSN]) AS [Begin_Transaction_DT],
LAST_VALUE([End Time])  OVER (PARTITION BY [Transaction ID] 
	ORDER BY [Current LSN]
	ROWS BETWEEN CURRENT ROW	
	 AND UNBOUNDED FOLLOWING    ) AS [End_Transaction_DT],
FIRST_VALUE([Transaction Name]) OVER (PARTITION BY [Transaction ID] 
	ORDER BY [Current LSN]) AS [Transaction Name],
[OPERATION],
CASE
WHEN [Begin Time] IS NOT NULL THEN 1
ELSE 0
END AS [Is_Begin_tran],
CASE
WHEN [End Time] IS NOT NULL THEN 1
ELSE 0
END AS [Is_End_tran]
FROM   #temp_dump_dblog l
),
    transaction_user AS
(
SELECT * 
FROM db_log l
INNER JOIN sys.database_principals p on p.sid = l.[Transaction SID] 
WHERE  [Transaction ID]  '0000:00000000'
AND [OPERATION] IN ('LOP_BEGIN_XACT','LOP_COMMIT_XACT',
'LOP_INSERT_ROWS','LOP_MODIFY_ROW','LOP_DELETE_ROWS') 
)
SELECT [Current LSN],[Begin_Tran_LSN],[End_Tran_LSN],
[Begin_Transaction_DT],[End_Transaction_DT],[Transaction Name],
[Name] AS [User_Name],
CASE
 WHEN Is_Begin_tran + Is_End_tran = 0 THEN '    |->  ' + [Operation]
 ELSE [Operation]
END As [Operation]
FROM transaction_user
WHERE [Transaction Name] NOT IN ('Backup:CommitLogArchivePoint',
                                 'AllocFirstPage','Allocate Root') 
ORDER BY [Name],[Current LSN]

Désolé pour l’indentation du code.

Si vous chercher qui a supprimé une table, cherchez des DROP …

Alors, qui a fait quoi ???

Enjoy

Publié dans SQL Server | Marqué avec , | Un commentaire

Techdays 2015 – SQL Server et la virtualisation : 45 minutes inside

En attendant que les slides soient disponibles au téléchargement, vous avez été nombreux à me demander les scripts Powershell pour créer “rapidement” une machine virtuelle sous Hyper-V.

Pour télécharger les scripts rendez vous sur mon onedrive.

image

Pensez quand même à changer le fichier template et les chemins d’accès !

Happy Powershell !

Publié dans Hyper-V, PowerShell, SQL Server | Marqué avec , | 2 commentaires

JSS 2014 – Compteurs de performance et DMVs DMFs

Publié dans Evènements | Laisser un commentaire

Order By – ordonner le résultat d’une vue

En formation, la question revient invariablement : comment ordonner les enregistrements résultant d’un select sur une vue…
La réponse est on ne peut plus simple : avec un ORDER BY !

Bon, OK, un peu d’humour ne fait pas de mal… Mais de quel order by parle t’on ? Du order by dans la déclaration de la vue ?

Prenons une requête de référence, ordonnée comme on le souhaite :

 

Use AdventureWorks
GO


SELECT  *
FROM sales.SalesOrderHeader 
ORDER BY OrderDate DESC,SalesOrderID  ASC;

image

 

Reprenons le code de ce select pour créer une vue :

CREATE VIEW vDemoOrderBy
AS
SELECT *
FROM sales.SalesOrderHeader 
ORDER BY OrderDate DESC,SalesOrderID  ASC
GO

 

image

 

Oups, cela ne fonctionne pas. Le message est clair : impossible d’utiliser une clause order by dans une vue, a moins d’utiliser un des opérateurs suivants : TOP, OFFSET ou FOR XML.

Les anciens, ayant connu SQL Server 2000, connaissaient l’astuce : ajouter une clause TOP, avec 100 PERCENT en argument et hop, le tour était joué !

 

CREATE VIEW vDemoOrderBy
AS
SELECT TOP 100 PERCENT *
FROM sales.SalesOrderHeader 
ORDER BY OrderDate DESC,SalesOrderID  ASC
GO

 

image

 

Cool, on vient donc de créer une vue, qui a priori renvoie un résultat trié puisque un order by est présent dans le code …

SELECT  *
FROM sales.SalesOrderHeader 
ORDER BY OrderDate DESC,SalesOrderID  ASC


SELECT *
FROM vDemoOrderBy


La première requête est notre requête de référence, alors que la seconde fait appel à la vue “triée”. Et le résultat n’est absolument pas identique :

image

Le comportement de SQL Server a changé depuis SQL Server 2005. Lorsque, dans une vue, les opérateurs ORDER BY et TOP sont combinés, le order by est ignoré !!!

L’affichage du plan d’exécution valide ce point : il n’y a pas de tri lors de l’utilisation de la vue.

image

 

 

Conclusion : ne faites pas confiance au order by contenu dans une vue. Si vous avez réellement besoin d’obtenir un résultat trié en sortie d’une vue, alors ajoutez le order by à la requête qui accède à la vue et chassez le mythe de la vue ordonnée !

Et, souvenez vous bien que le tri est couteux, en terme de ressources système (79% du cout de la requête dans le cas présent). Donc, ne triez le résultat que si vous avez réellement besoin que ce soit le cas. Sinon, abstenez vous, ou bien reportez le tri côté client si possible, et comparez les performances et la montée en charge.

 

Happy sorting !

Publié dans SQL Server | Laisser un commentaire