SQL Server vu par Christian Robert

SQL Server : Query Notification ou comment être notifié de modifications de données côté application (SqlDependency)

Cette fonctionnalité à vue le jour dans Ado.Net 2.0 et s'appuie sur SQL Server 2005 (et plus) même si elle fonctionne avec SQL Server 2000. Le principe de fonctionnement côté applicatif est assez simple, on fournit une requête et lorsque le résultat de celle-ci change on souhaite être notifié par l'appel d'une fonction (callback).

Ce que vous connaissez peut être moins c'est comment fonctionne ce système. Il repose, sous SQL Server 2005, sur le Service Broker et un système de notification basé sur les vues indexées. Sur SQL Server 2000, c'est un système de pooling d'une table alimentée par des triggers.

Dans les 2 cas vous aurez 2 connexions ouvertes vers la base de données. La première sert à créer la notification et malheureusement nécessite des privilèges important (de création d'objet dans la base de données, et de certains privilèges serveurs sous SQL Server 2005). Le seconde sert à la notification et ne nécessite peu de privilèges, mais vous n'aurez pas de contrôle sur cette dernière sauf à utiliser une classe de plus bas niveau (voir plus loin).

Avant de continuer sur le sujet, voici les limites de ce système :

• Repose sur le système des vues indexées, il a donc les même restrictions : pas de SELECT *, non du schéma obligatoire, pas de LEFT ou de RIGHT JOIN, etc. La liste complète des restrictions : http://msdn.microsoft.com/en-us/library/aewzkxxh.aspx ou en français : http://msdn.microsoft.com/fr-fr/library/aewzkxxh.aspx
• Et a aussi les même implications que les vues indexées en termes de performance, je ne serais trop vous recommander de ne pas mettre 2000 notifications sur la même table… D'une manière générale le système monte « mal » en charge
• Nécessite des privilèges élevés pour les classes de plus haut niveau (SqCacheDependency et SqlDependency) au sein de la base de données et certains privilèges serveur.
• Ouvre une nouvelle connexion, chaque notification maintien donc une connexion vers le serveur jusqu'à ce qu'elle soit levée ou passe en TimeOut (Méthode Stop, voir plus loin dans l'exemple)
• Vous ne savez pas quel type d'opération génère la notification ni quelles sont les données modifiées, ce qui vous oblige à ré exécuter la requête pour avoir la totalité des nouvelles données (on en profite pour réamorcer la notification).
• Peut passer en timeout ce qui oblige à réamorcer la notification. Ce réamorçage est aussi nécessaire à chaque déclenchement de cette dernière, il faut le prévoir dans le code.

En allant plus loin, vous découvrirez que 3 classes sont à votre disposition (de celle de plus haut niveau à celle de plus bas niveau) :

• SqlCacheDependency
  • Celle-ci est en relation avec l'objet Cache de l'ASP.Net et permet de piloter le cache de sortie d'ASP.Net en fonction de la modification des données.
• SqlDependency
  • C'est la plus courante, on fournit juste la connexion, la commande SQL et la fonction de Callback. En option il est possible de fournir le nom de la file d'attente du Broker à monitorer.
• SqlNotificationRequest
  • C'est la plus complexe, mais aussi la plus flexible. On s'attend à ce que toute la mécanique du Service Broker soit déjà en place, seule la notification est créée. C'est aussi de votre responsabilité que de créer le thread de monitoring.

On passe maintenant aux exemples, mais avant toute chose assurez-vous d'avoir le service broker activé dans votre base de données ou sinon exécutez :

ALTER DATABASE MaBase SET NEW_BROKER

Exemple avec la classe SqlDepedency

SqlConnection cnx = new SqlConnection(connectionString);

// Démarre la connexion qui crée le Service Borker et sa file d'attente

SqlDependency.Start(connectionString);

cnx.Open();

SqlCommand cmd = new SqlCommand("SELECT ShipperID, CompanyName, Phone FROM dbo.Shippers", connection);

// Ici on défnie la dépendance sur la requête

SqlDependency dependency = new SqlDependency(cmd);

// Et la méthode de Callback

dependency.OnChange += new OnChangeEventHandler(OnDataChange);

// On execute la requête qui est le signal de démarrage de la surveillance de la requête

cmd.ExecuteReader();

cnx.Close();

Et le code de Callback :

// Le squelette du Callback, on est pas obligé de réaliser le nettoyage avec Stop ici

static void OnDataChange(object sender, SqlNotificationEventArgs e)

{

// ... on regarde entre autre (TimeOut, autre) ce qu'on a dans e.Info

// On détruit les objets du Broker

SqlDependency.Stop(connectionString);

}

Exemple avec la classe SqlNotificationRequest.

Je pars du principe que j'ai déjà un service existant côté base de données et une méthode qui a appel

// Création de la notification

SqlNotificationRequest not = new SqlNotificationRequest();

not.UserData = Guid.NewGuid().ToString();

not.Options = "Service=MonService";

not.Timeout = 100000;

// On l'associé à la requête

SqlCommand cmd = new SqlCommand("SELECT a FROM dbo.TabA", cnx);

cmd.Notification = not;

// Ouvre la connecion et execute la requête

cnx.Open();

cmd.ExecuteReader();

cnx.Close();

J'assume aussi le fait que vous ayez un thread séparé qui exécute la commande SQL suivante :

WAITFOR (RECEIVE * FROM MaFileDAttente);

Ma préférence va à la seconde solution car vous avez entièrement la main sur ce qui se passe côté base de données, ce qui est important en termes de permissions et pour d'autres éléments que je vous présenterais dans d'autres billets.

Un point important à garder en tête, ce système est particulièrement difficile à manipuler à cause des restrictions nombreuses, ne baissez pas les bras et lisez la liste des restrictions avec attention ! Plus la requête est simple, plus elle a de chances de passer.

Maintenant que l'on connait le fonctionnement interne, on peut imaginer des solutions tordues comme :

• Query Notification côté serveur uniquement avec SQLCLR à « Trigger asynchrone »
• Query Notification côté serveur pour notifier l'application
• Imitation du système via un trigger en bonus en lui fournissant les données !
• Fonctionnement conjoint du Query Notification avec d'autres fonctionnalités de 2008

Qui a dit que SQL Server n'était pas fun pour faire du développement .Net ?

Bonne notification…

TechDays 2010 Genève : Retrouvez-moi pour une session sur la Haute disponibilité et le ScaleOut avec SQL Server

Eh oui, cette année j'ajoute une corde de plus à mon arc concernant les conférences que je vais animer. Direction la Suisse Romande pour ses TechDays qui ont lieues à Genève.

Pour ma part je vous donne Rendez-Vous le 14 avril à 14h45 pour la session intitulée « Booster performance et fiabilité avec SQL Server en mode multi-serveur »

Au programme de cette session (version longue) :

Découvrez comment associer la puissance d'une ferme de serveurs de bases de données fonctionnant avec SQL Server ; tour à tour nous verrons les méthodes pour combiner la puissance de plusieurs instances et serveurs physiques, puis les techniques permettant de diminuer le temps de non disponibilité des serveurs de bases de données et donc des applications les exploitant.

Comment monter en charge en exploitant plusieurs instances ?

Le Parallel Datawarehouse (alias projet Madison), la nouvelle édition arrivant avec SQL Server 2008 R2 permettant l'exécution parallèle de requêtes sur de multiples instances ainsi que le chargement de données en exploitant des centaines de CPU.

Le Scalable Shared Database ou comment partager une base de données en lecture entre plusieurs instances SQL Server, grâce à un espace de stockage partagé tel qu'un SAN.

La Réplication et ses différents modes de fonctionnement en vue de synchroniser des données vers plusieurs cibles avec différents degrés de latence, une possibilité de flux bidirectionnel avec gestion de conflit et possibilité d'attaquer des périphériques mobiles.

Le ScaleOut qui, en utilisant un partitionnement multi serveur des données, permet la montée en charge d'applications de bases de données ayant de très hautes charges et très gros volumes de données.

Comment augmenter la disponibilité d'une application ?

Le Failover Clustering ou le basculement automatique d'une instance disposant des bases de données sur un espace de stockage partagé.

Le Database Mirroring qui permet synchronisation et basculement de multiples bases de données de manière automatique et en mode synchrone ou asynchrone.

Le Log Shipping qui met en œuvre une automatisation du processus de sauvegarde des journaux de transactions entre une ou plusieurs instances SQL Server en vue d'avoir une copie de récupération de données ou une source de données asynchrone pour pouvoir basculer l'application.

Que nous promet le futur sur l'exploitation de fermes de serveurs de bases de données ?

Quelques techniques ou améliorations qui verront nous dans un futur proche sur nos moteurs de bases de données favoris pour améliorer la disponibilité ou améliorer la montée en charge.

Dans l'idée je pense essayer de faire une démo avec 4 à 5 instances en ScaleOut..

Si ce n'est pas déjà fait inscrivez-vous sur le site des TechDays, ici :

http://www.microsoft.com/switzerland/msdn/fr/techdays/

J'espère vous retrouver nombreux.

Bonne sessions…

SQL Server : Estimer le nombre de lignes renvoyées par une requête avec SQLCLR

Dans certains cas très particuliers il peut être intéressant de savoir le nombre de ligne que va renvoyer une requête sans exécuter cette dernière. En effet les opérations de comptage sont généralement gourmande en ressource est très difficile à optimiser pour un usage qui n'est au final pas toujours évident.

Or SQL Server est capable par le biais de son plan d'exécution de vous signaler le nombre de ligne que statistiquement il estime devoir renvoyer. Cela reste une estimation et en fonction du type de requête le résultat changera beaucoup par rapport au compte réel, mais le chiffre est généralement proche de la réalité.

Comment extraire cette valeur du plan et l'utiliser dans son code SQL ? C'est assez complexe car il faut normalement demander à générer le plan sous forme XML (SET SHOWPLAN_XML), or c'est strictement impossible à l'intérieure d'une procédure stockée sans passer par un SQL externe, comme une commande dynamique. Le plus simple est de passer par le support .Net intégré à SQL Server, le SQLCLR.

Il nous faut créer une procédure stockées, en effet les fonctions ne supporte pas l'appel de certaines commande SET comme celle que nous allons utiliser.

Voici le code :

[Microsoft.SqlServer.Server.SqlProcedure()]

public static SqlInt32 CountFirstResult(SqlString sqlQuery, out SqlDouble rowCount)

{

 

string sqlPlanOn = "SET SHOWPLAN_XML ON";

string sqlPlanOff = "SET SHOWPLAN_XML OFF";

 

SqlCommand sqlCmd;

 

using (SqlConnection sqlCnx = new SqlConnection("context connection=true"))

{

 

sqlCnx.Open();

 

sqlCmd = new SqlCommand(sqlPlanOn, sqlCnx);

sqlCmd.ExecuteNonQuery();

 

// Set the showplan xml to ON

sqlCmd = new SqlCommand(sqlQuery.ToString(), sqlCnx);

XmlReader xmlQueryPlan = sqlCmd.ExecuteXmlReader();

 

if (xmlQueryPlan.ReadToFollowing("StmtSimple", @"http://schemas.microsoft.com/sqlserver/2004/07/showplan"))

rowCount = new SqlDouble(double.Parse(xmlQueryPlan.GetAttribute("StatementEstRows"),CultureInfo.InvariantCulture.NumberFormat));

else

rowCount = 0;

 

xmlQueryPlan.Close();

 

// Set the showplan xml to OFF

sqlCmd = new SqlCommand(sqlPlanOff, sqlCnx);

sqlCmd.ExecuteNonQuery();

 

sqlCnx.Close();

}

 

return 0;

}

Je me contente d'appeler les commandes SET SHOWPLAN_XML avant et après l'exécution de ma requête et j'extrais la valeur de l'attribut « StratementEstRows » qui est de type nombre à virgule flottante. Attention cela ne fonctionne que pour la première requête fournie dans l'état du code, mais il est possible de l'adapter pour obtenir plus d'info.

On teste le code comme suit :

DECLARE @ret float;
EXEC dbo.CountFirstResult 'SELECT * FROM sys.all_objects WHERE name like ''t%''', @ret OUT;
SELECT @ret;

Et on obtient un resultat de type numérique, dans mon cas : 841,614

Si vous ne voulez pas vous embarrasser avec la virgule, remplacez le float par un bigint comme suit :

DECLARE @ret bigint;
EXEC dbo.CountFirstResult 'SELECT * FROM sys.all_objects WHERE name like ''t%''', @ret OUT;
SELECT @ret;

Voilà pour le petit exemple de code SQLCLR pour faire une chose quasi impossible nativement dans SQL Server avec du T-SQL

En termes de resources, la requête n'est jamais utilisée, elle est uniquement compilé, ce qui ne fait que consommer un peu de resources processeur de l'ordre de quelques millisecondes.

Bon compte…

SQL Server : PHP et SQL Server

Juste une petite synthèse sur les possibilités de connecter une application PHP sur un serveur SQL Server. Non pas que je me sois reconverti en développeur PHP, mais je constate qu'actuellement il est très difficile dans un environnement PHP et particulièrement PHP Linux/Unix de se connecter à un serveur SQL Server.

Microsoft a bien diffusé un driver pour PHP permettant de se connecter à SQL Server :

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=ccdf728b-1ea0-48a8-a84a-5052214caad9

Ce driver en est à la version 1.1, il supporte SQL Server jusqu'à la version 2008. Cependant ce dernier ne supporte que les plateformes Windows ! Exit donc la possibilité de se connecter depuis une machine Linux ou Unix qui sont pourtant nombreuses et qui du coup vous donne le choix entre MySQL et de temps à autres d'autres moteurs nativement.

Il existe pour les applications PHP une alternative qui s'appelle PDO permettant de remplacer le support MySQL par un moteur alternatif, à condition que ce dernier dispose d'un driver PDO. A ce jour un certain nombre de moteurs disposent de ces drivers : SQL Lite, PostGreSQL, etc. Côté SQL Server c'est non, çà n'existe pas. On vous dira alors que sous Windows il est possible d'utiliser le driver PDO pour odbc et par le biais d'ODBC d'utiliser le driver natif de SQL Server.

Outre le fait que passer par ODBC est une solution spécifique à Windows (aucuns drivers SQL Server pour ODBC ne sont fournis pour une autre plateforme que Windows), cette dernière souffre des limitations inhérentes à ODBC.

Que reste-il à notre disposition pour les connexions depuis Unix/Linux vers SQL Server ? Un seul outil FreeTDS , qui permet le support direct du protocole TDS commun à Sybase et Microsoft sur les plateformes Unix et Linux. Malgré la diffusion récente de la documentation de TDS par Microsoft, cet outil a une mauvaise réputation dans le monde communautaire, fournit un support partiel de toutes les fonctionnalités de SQL Server et, de fait, se trouve relégué à des applications peu critiques.

Au final il y a un vrai manque : Comment peut-on développer des applications PHP travaillant par exemple sur SQL Server, solutions toutes deux gratuites, sans passer par une solution propriétaire et payante ? Personnellement je verrais bien dans un futur plus ou moins proche un WordPress fonctionnant sur un SQL Server plutôt qu'un MySQL… Rêvons un peu !

Si vous souhaitez voir un jour un driver PDO pour SQL Server… eh bien votez :

https://connect.microsoft.com/SQLServer/feedback/details/541956/pdo-driver-for-sql-server

Bon vote…

SQL Server : Développer un AddOn pour SQL Server Management Studio (SSMS)

Pour celles et ceux qui aiment SQL Server et on l'âme de développeur (ou sont tout simplement développeur) et veulent étendre les fonctionnalités de SQL Server, voici un article qui devrait pouvoir un intéresser.

Certes ce dernier est écrit sur une base de 2005, avec Visual Studio 2005 et donc le Management Studio qui va avec SQL Server 2005. Mais il est facilement « adaptable » pour la version 2008.

Ça se passe ici :

http://aspalliance.com/1374_Extend_Functionality_in_SQL_Server_2005_Management_Studio_with_Addins.all

Bonne lecture…

SQL Server 2008 R2 : l’édition Express supportera des bases de données jusqu’à 10 Go !

Dans les quelques nouveautés de la version 10.5 alias 2008 R2, voici que l'édition Express se voit dotée de quelques Gigaoctets supplémentaires concernant la taille maximale de ses bases de données.

Jusqu'à présent cette taille limite était de 4 Go, cette limite passerait donc à 10 Go pour la version finale de SQL Server 2008 R2 (Attention ne fonctionne pas pour l'actuelle CTP publique) ! Une bonne nouvelle dans la mesure où 4 Go de taille de base de données maximum est relativement petit pour beaucoup d'application, surtout comparé à la concurrence gratuite qui ne limite pas toutes cette taille de base de données.

A noter que la mémoire est toujours limitée à 1 Go pour le Buffer Pool (données, plus quelques autres caches) et à 1 processeur quelques soit le nombre de cœurs. De plus les élements stockées sous forme de FileStream ou RBS ne sont pas comptabilisé dans la taille de la base de données.

La source se trouve dans la documentation en ligne de… Sharepoint !
http://technet.microsoft.com/fr-fr/library/ff384151(office.14).aspx

Sinon pour ceux utilisant toujours les versions 2008 et 2005, quelques méthodes pour vous permettre de « dépasser » les 4 Go de taille limite pour stocker vos données.
http://blogs.codes-sources.com/christian/archive/2007/11/24/sql-server-depassez-les-4go-de-taille-de-base-de-donnees-avec-sql-server-express.aspx

Bonne nouvelle…

SQL Server : SP2 de SQL 2008 et SP4 de SQL 2005 confirmés pour cette année

C'est maintenant chose faite ici :
http://blogs.msdn.com/sqlreleaseservices/archive/2010/02/12/sql-server-servicing-plans.aspx

Le Service Pack 4 de SQL Server 2005 arrivera au dernier trimestre de 2010 et sera le dernier Service Pack de cette version.

Bonne nouvelle...

SQL Server 2008 R2 : C'est pour les tous premiers jours du mois de Mai 2010

On se rapproche de plus en plus de la date fatidique... Jusqu'à présent indiquée pour le premier semestre 2010, on connait maintenant plus précisément le mois de sortie...

Ou même, si on croise les informations trouvées par Patrick sur le site du TPC, la date butoir de sortie du produit.

SQL Server 2008 R2 devrait sortir entre le 1er et le 9 Mai 2010 !

Plus d'infos

http://blogs.technet.com/dataplatforminsider/archive/2010/01/19/sql-server-2008-r2-gets-an-official-date.aspx

et

http://blogs.codes-sources.com/patricg/archive/2009/12/01/date-de-disponibilit-de-sql-server-2008-r2-et-d-office-2010.aspx

Bonne attente...

SQL Server : Vous souhaitez un Service Pack 2 pour SQL Server 2008 ou un Service Pack 4 pour SQL Server 2005 ?

Tout simplement, votez ci-dessous.

Pour le Service Pack 4 pour SQL Server 2005
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=522122

Pour le Service Pack 2 pour SQL Server 2008
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=522123

Bon vote…

Certifications : Retour de la seconde chance du 13 janvier au 30 juin 2010 sur les certifications Microsoft

Celles et ceux qui veulent se certifier peuvent profiter du retour de l'offre seconde chance, qui consiste à vous offrir une 2^ème opportunité pour passer une certification MCP Microsoft si vous avez échoué une première fois et cela pour un prix identique.

Pour en bénéficier il faudra passer une certification Microsoft durant la période indiqué dans un centre de certification Prometric. En cas d'échec la seconde certification est à passer avant le 30 juin.

J'aime beaucoup dans l'email que j'ai reçu la mention suivante :

"Microsoft does not guarantee that candidates that take exams will pass."

C'est vrai que c'est bien de le préciser ;o)

Inscriptions ici : http://www.prometric.com/Microsoft/default.htm

Bonnes certifications…

TechDays 2010 : Le lundi c’est la fête du DBA avec le DBA Day

A l'initiative de M. le chef de produit SQL Server de Microsoft France alias Lionel Billon, le Lundi 8 sera la journée des administrateurs de bases de données.

En effet, une série complète de sessions dédiées à SQL Server sera donnée, et des sessions techniques de haut niveau s'il vous plaît !

En voici la liste :

• Plénière J1
  • Infrastructure et développement des systèmes d'informations agiles
• 11:00 - 12:00
  • DBA DAY - Administrer facilement des environnements SGBD hétérogènes
• 13:00 - 14:00
  • DBA DAY - Très haute disponibilité & Optimisation des performances
• 14:30 - 15:30
  • DBA DAY - Exploitation retour d'expériences
• 16:00 - 17:00
  • DBA DAY - Table ronde - l'avenir du métier de DBA : BI, cloud computing, technologies émergentes
• 17:30 - 18:30
  • DBA DAY - Consolidation et virtualisation des bases de données

Ou sur le site officiel des TechDays : http://www.microsoft.com/france/mstechdays/programmes/parcours.aspx?DomID=fc768892-1aae-44bd-948d-3269d1e19b3a

J'aurais normalement (à moins d'une autre annulation de dernière minute) l'occasion de vous retrouver pour certaines d'entre elle, dont la session sur la Très Haute Disponibilité avec Christophe Laporte et Frédéric Pichaut.

D'autres évènements pour les DBA devraient être prévus durant cette journée, une occasion unique de rencontrer d'autres professionnels du même domaine.

Bon DBA-thon ;o)

TechDays 2010 : Une session sur la sécurité avec SQL Server [Annulé]

Et de une ! Une petite session sur la sécurité avec SQL Server cette année, pour le cru 2010 des Microsoft Techdays à Paris. Première session de la série que j'aurai l'occasion de donner au mois de février.

Le programme en exclu :

SQL Server et la sécurité

• Quels sont les risques ?

Sécuriser le périmètre du serveur

• L'environnement physique du serveur et ses accès
• Sécurisation du réseau pour un serveur de données
• Séparation des privilèges d'administrateur système et de DBA
• Se protéger des désastres naturels et des erreurs humaines

Sécuriser les applications

• Authentification unique sur l'application et le serveur de bases de données (SSO)
• Comment se protéger de l'injection de code SQL
• Auditer et suivre les accès aux données
• Masquer les données et interdire certaines opérations
• Et du coté des outils décisionnels ?

Définir la politique de sécurité de vos serveurs

• Quelques exemples de politiques de sécurité avec SQL Server
  • Pour des éditeurs de logiciels
  • Pour des secteurs « sensibles »
• Et les environnements de test, supervision et développement ?
• S'assurer de l'unicité et du respect des règles mises en place
• Social Engineering ?

Les habitués auront peut être remarqué que je pars d'un contenu 100% inédit. Et pour cause çà sera la 3^ème année que je donne cette session, l'occasion pour moi de renouveler totalement le contenu !

Je suis ouvert aux commentaires pour éventuellement ajouter ou retirer du contenu, mais n'oubliez pas que les sessions font à peine plus d'une heure !

Bonne session…

PS : Il y a pour le moment une coquille dans le planning en ligne et l'intitulé et le contenu de la session sont erronés. Je vous mets un lien dès que c'est corrigé.

UPDATE : En fait j'ai appris très tard hier (du coup tôt ce matin) que la session avait été annulée, bref sympa de la part de MS, je vais pouvoir jeter le contenu préparé !

SQL Server : TOP dynamique, ancienne et nouvelle méthode

Il est possible de ne renvoyer qu'un nombre limité d'enregistrement dans une requête. Cela est possible directement via la clause TOP depuis SQL Server 2005 et à condition de mettre les parenthèses qui deviennent obligatoire dans la syntaxe :

DECLARE @count int

SET @count = 5

SELECT TOP(@count) * FROM dbo.MaTable

Il existe toujours, bien que cette méthode soit annoncée comme retiré d'une prochaine version, la possibilité de le faire via SET ROWCOUNT :

DECLARE @count int

SET @count = 5

SET ROWCOUNT @count

SELECT * FROM dbo.MaTable

Et on pense à remettre à 0 (réinitialise et renvoie toutes les lignes) après usage car ce SET s'applique à toutes les requêtes.

SET ROWCOUNT 0

Cette seconde méthode présente l'avantage de fonctionner sur les versions précédent SQL Server 2005. Dans tous les cas vous éviterez ainsi l'utilisation du SQL Dynamique.

Bonne sélection…

SQL Server : Fin du support de SQL Server 2005 SP2, c’est demain

Pour celles et ceux qui n'aurait pas encore fait la mise à jour vers SQL Server 2005 Service Pack 3 c'est le moment. Faute de quoi, vous n'aurez plus de mises à jour cumulative de la part de Microsoft pour votre version et vous risquez de ne pouvoir accéder au support technique et ce dès le 12 janvier, c'est-à-dire demain !

SQL Server 2005 Service Pack 3 : http://www.microsoft.com/downloads/details.aspx?displaylang=fr&FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4

Prochaine version à voir son support disparaitre, SQL Server 2008 le 13 avril prochain, seul SQL Server 2008 Service Pack 1 et plus seront supportés à cette date.

SQL Server 2008 Service Pack 1 : http://www.microsoft.com/downloads/details.aspx?FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19&displaylang=fr

Bonne mise à jour...

SQL Server : L’après SQL Server 2008 alias SQL11

Avec SQL Server 2008 R2 qui est annoncé pour la première moitié de cette année, que nous réserve Microsoft pour la suite ? La version suivante se nomme en interne SQL11, car celle-ci sera la prochaine version majeure du moteur relationnel soit la version 11.00 de SQL Server.

En effet SQL Server 2008 R2, dont le numéro de version est 10.50, apporte beaucoup de nouveautés côté Business Intelligence et en cela surpasse SQL Server 2008, mais procure relativement moins de nouveautés côté relationnel. SQL Server 2008 R2 est vue par les équipes de développement comme une version dite « BI Refresh », même si certaines modification côté relationnelle la rende indispensable pour nombre d'utilisateurs et de clients (Edition DataCenter et son support de 256 cœurs, compression des types Unicode, support de la compression des Sauvegardes dès l'édition Standard, nouveautés côté gestion des serveurs et déploiement d'applications et StreamInsight).

Malheureusement peu d'information publique ou officielle sur cette version. Beaucoup de bugs ou de demande de fonctionnalités dans Connect (https://connect.microsoft.com/sqlserver) sont marqués comme potentiellement disponible dans cette version, ce qui laisse augurer beaucoup de nouveautés. Concernant la date de disponibilité de cette version, en se basant sur les indications de Microsoft elle serait de 3 ans après la date de sortie de SQL Server 2008, ce qui ferait que SQL11 sortirait en 2011 !

Dans tous les cas, beaucoup d'annonce devraient avoir lieu dès cette année sur la prochaine version majeure du moteur relationnel de SQL Server !

Bonne année...

SQL Server : Calcul du numéro de semaine ISO depuis un type date/time

C'est un grand classique sur SQL Server, ce dernier ayant un calcul de semaine non conforme à la définition ISO.

Que faire ?

Eh bien, soit vous êtes sous SQL Server 2008 et vous avez de la chance, car la fonction DATEPART permet de le faire :

SELECT DATEPART(ISO_WEEK, '20090801')

Soit vous êtes dans une autre version, auquel cas il faudra utiliser une fonction maison (dernier post dans le thread):

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60510

Clairement, gros avantage à SQL Server 2008, car les fonctions scalaires ne sont pas ce qu'il y a de plus efficace dans SQL Server. Mais c'est toujours utile d'avoir de ce genre de calcul sous la main.

Bon calcul…

Techdays : Souvenir de 2007

C'est la 4^ème année de TechDays, l'occasion de revenir en arrière un peu, avec les photos ci-dessous.

Mais qu'est ce donc ? C'est le prix que j'avais obtenu en 2007, pendant la soirée des communautés Days qui eu lieu en même temps que les techDays en 2007 :

http://blogs.codes-sources.com/christian/archive/2007/02/08/techdays-qui-est-le-plus-mauvais-joueur-de-bowling.aspx

En même temps, c'était ma première année de MVP, de quoi se faire remarquer !!!

Bonnes photos…

SQL Server : Changer l’emplacement par défaut des fichiers de bases de données

Tout est dans le titre, comment changer l'emplacement où SQL Server met par défaut les fichiers MDF, NDF (données) et LDF (journal de transactions).

Via Management Studio, on se connecte dans l'explorateur d'objets à une instance. Sur le nom de l'instance on fait un clic droit puis propriétés.

On arrive alors sur la boîte de dialogue suivant, il suffit d'aller sur la page intitulé « Paramètres de bases de données » ou « Database Settings » :

Ou par script :

USE [master]
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'-nouveau-chemin-'
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N' -nouveau-chemin- '
GO

Attention cela ne s'applique qu'aux nouvelles bases de données créées sans spécifier les fichiers, et en aucuns cas aux bases de données existantes. Attentiopn à MSSQLServer qui reprsente dans mon cas l'instance par défaut mais qu'il remplacer par le nom de l'instance si celle-ci en a un.

Pour d'autres chemins je vous conseille un billet précédent :

http://blogs.codes-sources.com/christian/archive/2009/03/12/sql-server-d-terminer-o-se-trouvent-les-r-pertoires-par-d-faut.aspx

Bon emplacement…

SQL Server : Les modes de récupération de Bases de données (Et les mythes autour du mode simple)

Existant officiellement depuis la version 2000 sous les noms qu'on leur connait aujourd'hui, leurs caractéristiques existaient auparavant avec SQL Server 7 entre autres sous formes d'options de bases de données (tel qu'activer ou non les opérations faiblement journalisées).

A partir de la version 2000, ils sont aux nombre de 3 :

• Journalisation Complete : « Full » 
• Journalisation pour mises à jour par lot : « Bulk_Logged »
• Journalisation Simple : « Simple »

Ce mode de récupération se paramètre au niveau de la base de données, à l'exception de certaines bases de données système (tempdb, qui est et reste en mode simple).

A partir de SQL Server 2000, le changement se fait par :

ALTER DATABASE MaBase SET RECOVERY { FULL | BULK_LOGGED | SIMPLE }

Ces modes de récupération définissent la manière dont se comporte le moteur de base de données pour remplir le journal de transaction. On peut se dire que l'impact de ce comportement sur les performances est direct, eh bien oui et non.

Mode de récupération complet / Full.

Passage en mode récupération complet :

ALTER DATABASE MaBase SET RECOVERY FULL

Attention le passage en mode récupération complet nécessite obligatoirement d'exécuter une sauvegarde complète après l'exécution de la commande précédente si vous étiez au préalable en mode de récupération simple. A la création d'une nouvelle base de données ayant le mode de récupération complet, tant qu'une sauvegarde complète n'est pas exécutée le comportement est celui du mode récupération simple !

Dans ce mode toute opération est journalisée de la même manière sans exception. C'est-à-dire qu'un INSERT ira écrire la même quantité d'information qu'un SELECT / INTO ou un BULK INSERT. Il n'y a donc en termes de performance aucunes différences entre ces commandes pour le chargement des données dans une table.

Idem pour les commande de création et de maintenance d'index, elles sont 100% journalisées, donc généralement assez lente et déconseillé dans ce mode hors des fenêtres de maintenance prévues à ces effets.

A noter tout de même que le TRUNCATE TABLE, qui est par définition non journalisé est aussi faiblement journalisé en mode Complet. En fait la seule contrainte d'un TRUNCATE TABLE est de pouvoir réaliser un ROLLBACK dans une transaction. Si cela est nécessaire, cette opération verrouillera les données de la table jusqu'à la fin de la transaction. Cette opération est donc, quelque que soit le mode récupération la plus efficace pour vider une table de son contenu.

Il a des contraintes au niveau de SQL Server qui vous forceront à rester dans ce mode de récupération. Citons entre autres la mise en place d'un miroir (mirroring).

Mode de récupération pour mises à jour par lot / Bulk Logged.

Passage en mode récupération faiblement journalisé (BULK LOGGED):

ALTER DATABASE MaBase SET RECOVERY BULK_LOGGED

Dans ce mode, un certain nombre d'opérations sont dites faiblement journalisées :

• SELECT / INTO
• BULK INSERT sur une table vide (et ses cousines : bcp, FastLoad, etc.)
• CREATE INDEX (et par extension clef primaire et contrainte unique)
• DBCC DBREINDEX / ALTER INDEX REBUILD
• DROP INDEX (clustered index)
• Ecriture ou modification d'un LOB (Large OBject, tel que varbinary(max) ou vachar(max) s'ils font plus de 8ko).

A cette liste s'ajoute à partir de SQL Server 2008 et à condition d'utiliser le TraceFlag 610 (http://blogs.codes-sources.com/christian/archive/2009/09/11/sql-server-quelques-trace-flags-utiles.aspx) au niveau serveur :

• BULK INSERT sur table déjà remplie (et ses équivalentes)
• INSERT sous certaines conditions

Dans ce mode, les opérations faiblement journalisées n'écrivent plus dans le journal de transactions leur progression (tout au moins le début et la fin de l'opération y sont consignés). Au lieu de cela, des pages systèmes contiennent la liste des extensions (bloc de 8 pages, soit 64 ko) modifiées par ces opérations via un bit dans ce type de page = 1 extension modifiée.

La caractéristiques des opérations faiblement journalisées est d'être facilement re-éxécutable sans avoir à consigner la progression de la modification des données. En effet le chargement d'un fichier texte par BULK INSERT nécessiterait juste de recharger ce même fichier avec la même commande. La création d'index nécessitera la même commande, etc.

Les données ainsi impactées sont copiées directement au moment de la sauvegarde du journal de transactions dans le fichier de sauvegarde. A aucuns moment ces données sont dans le journal de transactions lui-même elles sont exclusivement dans les fichiers de données !

BACKUP LOG mabase TO DISK = 'chemin\fichier'

Après cette commande, les pages systèmes permettant de suivre les opérations faiblement journalisées sont réinitialisées. Il est très fortement conseiller d'exécuter une sauvegarde du journal de transaction après chaque opérations de type listé plus haut, car sinon en cas de perte d'un ou plusieurs fichiers de données la récupération de la base de données pourrait être difficile ou compromise. En complément dans ce mode, pensez à garder vos fichiers d'import au moins jusqu'au sauvegarde différentielles ou complète, par précaution.

A noter que la journalisation faible pour les insertions de données se fait à quelques conditions strictes :

• La table de destination des données doit être vide
• La table doit être verrouillée à l'aide d'un verrou de type table (TABLOCK)
• Ne s'applique qu'au BULK INSERT et opération similaires (en aucun cas à l'INSERT)

A partir de SQL Server 2008 et à condition d'activer le TraceFlag 610 :

• La table doit être verrouillée à l'aide d'un verrou de type table (TABLOCK)
• S'applique au BULK INSERT, même table déjà remplie et aux INSERT
• Ne s'appliquera seulement si les pages de destinations des données ne contiennent pas déjà des enregistrements…
  • Il est par exemple probable que sur une petite quantité de données la journalisation soit complète.

Les conséquences de tout çà sont que seules les applications prévues pour optimiser ce type de traitement de données peuvent en profiter (utilisation explicite du BULK INSERT et du TABLOCK) et que ce n'est généralement pas juste en passant dans ce mode de récupération que les gains vont être visibles. Par contre en termes de développement, il est mieux de réaliser un développement utilisant ce type de commande, des SELECT / INTO au cas où le mode de journalisation serait BULK_LOGGED ou SIMPLE.

Mode de récupération simple / Simple.

Passage en mode récupération simple:

ALTER DATABASE MaBase SET RECOVERY SIMPLE

C'est le mode sur lequel il y a le plus de préjugé. En effet ce mode est identique en termes de performance au précédent ! Il n'y a aucune différence sur la manière de journaliser les opérations dans ce mode vis-à-vis du BULK_LOGGED.

La différence entre ce mode et le précédent, vient de la gestion interne du journal de transaction. Là où en mode de récupération BULK_LOGGED il faut sauvegarder le journal de transaction, le mode SIMPLE lui simplifie la gestion en exécutant automatiquement des opérations tronquant le contenu du journal. A chaque fois que les données stockées en mémoire sont inscrites dans les fichiers de données, le journal est tronqué.

En pratique dans ce mode de récupération ont augmentera la cadence des sauvegardes complètes et différentielles pour pallier l'absence de sauvegarde des journaux de transactions.

Quelques clarifications sur ce mode :

• Il n'est pas plus performant que le BULK_LOGGED
• Le journal de transaction peut grossir dans ce mode, car les transactions courantes l'utilisent
• La réduction du journal de transaction n'est pas automatique
• Les pertes de données sont possibles dans ce mode, mais à condition qu'un fichier de données ou le journal de transaction soit corrompu
• Les DELETE sont totalement journalisés… Il n'existe pour le moment aucunes alternatives à ce comportement

Modes particuliers

Il existe et existera, 2 modes particuliers supplémentaires. Ils ne sont à proprement parlé pas des modes utilisable explicitement sur une base de données

Le premier est celui de tempdb, cette base de données est bien entendue en mode récupération simple. En réalité depuis SQL Server 2005, ce base de données est moins journalisée que les modes BULK_LOGGED et SIMPLE, en effet on part du principe que si le moteur de base de données est redémarré cette base de données est tout simplement recrée vide. Dès lors seuls les informations nécessaires à l'exécution des transactions (phase d'annulation, dite Undo) sont nécessaires, les informations nécessaire au démarrage (phase de Redo, qui ré-exécute les transactions dont les données n'ont pas été écriture dans les fichiers de données à l'arrêt de l'instance) de chaque base de données est de fait inutile pour tempdb.

De plus tempdb, bénéficie d'une optimisation concernant la mise en cache de ses table et une réutilisation des structures en cas de DROP / CREATE fréquent. Ce qui la rend plus perfomante en contrepartie de quoi la perte de données est garantie en cas de redémarrage de l'instance.

L'autre est un mode, dit SUPPLEMENTAL_LOGGING, en fait ce mode n'a tout simplement pas été mis en place pour le moment dans SQL Server. Il est apparu dans les premières versions de la documentation en ligne de SQL Server 2008 et a vite été retiré. Peut-être verra-t-il le jour dans une version ultérieure du moteur, son rôle devant être similaire à celui des moteurs concurrent, on ajoute un certain nombre d'information du journal, ceci pour aider les outils tiers lisant ce dernier.

J'essaye de faire un comparatif de performance entre ces modes dans un prochain billet.

Bonne récupération...

SQL Server : Le TOP 10 de mes articles techniques de 2009

Un petit récapitulatif des billets les plus vus de l’année 2009…

Pourquoi le journal de transaction grossit t’il (LDF) ?

http://blogs.codes-sources.com/christian/archive/2007/02/12/sql-server-faq-sql-pourquoi-mon-fichier-de-log-ldf-est-il-aussi-gros-comment-diminuer-sa-taille.aspx

Méthodes pour tronquer la date ou l’heure d’un datetime

http://blogs.codes-sources.com/christian/archive/2007/04/29/sql-server-conserver-la-date-ou-l-heure-d-un-datetime-comparaison-des-methodes.aspx

Les secrets du LIKE

http://blogs.codes-sources.com/christian/archive/2007/12/11/sql-server-la-verite-sur-le-like.aspx

Comment dupliquer la structure d’une table

http://blogs.codes-sources.com/christian/archive/2007/09/21/sql-server-copier-une-table-avec-ses-donn-es-ou-uniquement-sa-structure.aspx

Les différents types d’INSERT

http://blogs.codes-sources.com/christian/archive/2007/10/24/sql-server-les-3-diff-rents-types-d-insert.aspx

Comment changer le classement (COLLATE) d’une base de données ?

http://blogs.codes-sources.com/christian/archive/2007/08/17/sql-server-changer-la-collation-classement-ordre-de-tri-d-une-base-de-donn-es.aspx

Comment récupérer le mot de passe SA d’une instance ?

http://blogs.codes-sources.com/christian/archive/2007/01/18/sql-server-2005-r-cup-ration-du-mot-de-passe-de-sa.aspx

Tableau de synthèse des types de données de SQL Server jusqu’à 2008

http://blogs.codes-sources.com/christian/archive/2008/06/30/sql-server-tous-les-types-de-donnees-en-un-coup-doeuil.aspx

Tout savoir sur l’utilisation du NOLOCK

http://blogs.codes-sources.com/christian/archive/2007/03/08/sql-server-les-verrous-et-l-utilisation-de-nolock.aspx

TRUNCATE TABLE ou DELETE ?

http://blogs.codes-sources.com/christian/archive/2008/05/22/sql-server-delete-from-ou-truncate-table.aspx

Bonne lecture…