lundi 25 août 2008

WSC 08 - Nouvel événement Winwise !

Bonjour à tous !

Après un an de silence, je reviens pour vous annoncer un nouvel événement Winwise : la Winwise Solutions Conference !
Cet événement gratuit, en partenariat avec Microsoft, vous propose 15 sessions techniques dédiées aux nouvelles technologies Microsoft et à leurs utilisations avancées.

Inscrivez vous en cliquant sur la bannière :

A très bientôt sur l'événement !

jeudi 23 août 2007

Hands On Labs chez Winwise !

Bonjour à tous !

Le mois prochain, Winwise, en coopération avec le GUSS (Groupe d'Utilisateurs de SQL server), vous propose de découvrir gratuitement SQL Server au travers de plusieurs Hands-On-Labs.
Les différents ateliers seront présentés par les formateurs de Winwise, dont votre serviteur.




Je ne peux donc que vous encourager à vous inscrire pour cet événement, et ce très rapidement car les places sont limitées !
Vous trouverez toutes les informations nécessaires ici.

Alors à vos claviers !

mardi 21 août 2007

Haute disponibilité avec SQL Server - Quelle solution pour quel résultat ?

Bonjour à tous !

SQL Server est depuis longtemps rompu à la nécessaire disponibilité des données. Mais quelles sont les différentes solutions proposées, et que peut-on en attendre ?

Je vous propose aujourd'hui un résumé de chaque solution proposée par Microsoft avec SQL Server 2005, et ce que vous pouvez raisonnablement espérer de chacune d'entre elles.

LOG SHIPPING ET MIRRORING - DEUX SOLUTIONS, UNE FINALITE

Beaucoup ont entendu parler de Log Shipping, nombreux sont ceux qui ont entendu parler de mirroring... Mais pour quel résultat, quel impact sur l'existant, quelle maintenance ?
Le Log Shipping est reconnu depuis longtemps comme une solution peu onéreuse, simple à mettre en oeuvre, mais assez délicate à exploiter, en particulier lors d'un basculement de serveur. En effet, le basculement est manuel, la perte des dernières données probable, car le temps d'indisponibilité fluctue en fonction de la réactivité du DBA.
Le Mirroring peut être vu comme une solution de Log Shipping évoluée. Si le mode SAFETY OFF est sélectionné, le Mirroring se comporte en effet comme le Log Shipping, recopiant le journal de transaction au fil de l'eau. Le temps de latence reste globalement plus faible que dans le cas du Log Shipping, et la mise en oeuvre est totalement automatisée.
En revanche, si l'on sélectionne le mode SAFETY FULL, le Mirroring permet de garantir à l'utilisateur une parfaite synchronisation des bases de données, ce que ne permet pas le Log Shipping.
De plus, l'utilisation d'un WITNESS permet d'automatiser le processus de basculement des serveurs en cas d'erreur.
Qu'il s'agisse de Log Shipping ou de Mirroring, l'initialisation des bases de données s'effectue par Backup/Restore.

Devant ces similitudes, je privilégie donc largement le Mirroring sous SQL Server 2005, qui permet un temps de latence bien plus faible que son copain le Log Shipping.
De plus l'utilisation d'un WITNESS assure un basculement automatique des plus appréciables, et qui plus est l'un des plus rapides du marché avec un temps d'absence de service inférieur à 5 secondes.

REPLICATION ET DISTRIBUTION DES DONNEES

L'utilisation de la distribution des données comme moyen de haute disponibilité est une méthode couramment utilisée.
Il existe de nombreux moyens de distribuer ses données. Le plus simpliste reste la copie par batch des données à intervalle régulier. Cette méthode très simple à mettre en oeuvre s'apparente dans les applications à du Log Shipping, même si son procédé est radicalement différent. De plus, il est possible d'utiliser la base de données ainsi obtenue en lecture par exemple.
Pour les bases de faible volumétrie et supportant le risque de la perte des données les plus récentes, la réplication snapshot correspond aussi à cette problématique. En revanche, contrairement au batch de recopie, la réplication snapshot supporte difficilement les très grosses volumétries.
Enfin, pour une distribution assurant une plus grande fraîcheur de données, la réplication transactionnelle offre également un compromis intéressant. Là encore, les données sont accessibles en lecture. En revanche, la publication transactionnelle doit être désactivée pour effectuer quelque changement que ce soit dans la structure des données. De plus, la réplication transactionnelle s'avère souvent très fragile dès qu'une opération de maintenance sur le journal de transactions est nécessaire.

La distribution des données, qu'il s'agisse d'un batch ou d'une réplication, aussi faible son temps de latence soit-il, n'est pas à proprement parler une véritable solution de haute disponibilité. En revanche, il peut s'agir d'un compromis efficace pour pallier à une éventuelle défaillance du système tout en conservant l'utilisation de toutes les machines achetées.

CLUSTERING

Le clustering reste la méthode reine de la haute disponibilité, permettant entre autres de ne spécifier qu'une seule adresse réseau pour plusieurs ordinateurs physiques, assurant ainsi un basculement transparent pour les applications clientes.
Pour être mis en oeuvre, le clustering demande des connaissances avancées tant sur la partie SQL Server que sur le système d'exploitation.
Le point noir du clustering proposé par Microsoft reste l'inactivité des serveurs de secours, induisant un coût d'immobilisation supplémentaire. De plus, le clustering n'apporte aucune solution en cas de destruction physique des supports de stockage - incendie par exemple - étant donné que ces ressources physiques sont partagées entre les différents noeuds (je n'évoquerai pas ici les possibilités de géo-cluster pour des raisons de simplification).
Concernant les surcoûts d'immobilisation, il faut tout de même noter que dans le cadre du clustering, tout comme dans le cadre du mirroring, un serveur de secours actif moins de 30 jours par an ne requiert aucune licence.
Le délai de basculement de serveur dans le cas du clustering avoisine les 30 secondes, à mettre en parallèle avec les 5 secondes du mirroring.

Contrairement à son grand concurrent ORACLE, SQL Server ne dispose pas d'une réelle solution de clustering Actif-Actif. Microsoft préfère en effet privilégier l'accroissement des performances monoserveurs. Pour ceux dont l'architecture nécessiterait un scale out, il est possible d'implémenter une solution alternative consistant à créer deux clusters Actif-Passif croisés, simulant de fait le clustering Actif-Actif.

REPLICATION PEER-TO-PEER ET MIRRORING "ACTIF-PASSIF"

Microsoft offre également d'autres méthodes permettant d'utiliser les différentes machines tout en limitant les pertes de données potentielles en cas de crash système.
La plus ancienne des deux méthodes que je vous présenterai ici est la réplication Peer-To-Peer. Il ne s'agit ni plus ni moins que d'une réplication transactionnelle bi-directionnelle.
Ce type de réplication est extrêmement utile dans le cadre de sites de sauvegarde délocalisés. De plus, tous les serveurs sont actifs. Si l'on multiplie les noeuds de réplication, cette méthode s'avère très efficace et permet même d'envisager un partage de charge (Load Balancing). En revanche, contrairement au clustering, chaque machine est identifiée individuellement, et par conséquent le partage de charge doit être géré à un niveau plus bas, par exemple à l'aide d'un switch actif.
Une autre technique, s'appuyant sur le mirroring, permet d'avoir accès en lecture aux bases de données. Cette méthode nécessite un partage de ressource à l'aide, par exemple, d'une baie SAN, et un seul serveur accède à la base en écriture. Cette solution peut être très intéressante dans le cas de DataWareHouse nécessitant une grande capacité de calcul lors des lectures. Un des serveurs mirroirs prend le relai en cas de panne du serveur maître.

La réplication Peer-To-Peer est une solution largement répandue, mais souvent utilisée pour ses capacités de rapprochement des données des utilisateurs plus que pour la haute disponibilité ainsi procurée.

EN CONCLUSION

Je concluerai ce post en faisant le constat suivant : le besoin métier pilote évidemment le choix technologique, tant par les aspects financiers que par les aspects de criticité des données. Il est donc primordial de bien prendre en compte tous les éléments avant de définir une architecture de serveurs de bases de données, cela conditionnant également le développement des applications gravitant autour de celle-ci.

A très bientôt !

lundi 30 juillet 2007

Mise en oeuvre d'un test de validité avec SQL Server Integration Services

Bonsoir à tous !

Si SSIS présente de grands avantages en matière de débugging de part sa conception, il n'en demeure pas moins un outil largement perfectible sur un certain nombre de points.

Le premier d'entre eux est sans doute la faiblesse des bibliothèques de fonctions accessibles dans les tâches de conversions de données. En effet, les tests classiques permettant d'identifier la bonne typologie de la donnée importée dans un Data Flow (ISDATE, ISNUMERIC, ...) ne peuvent pas être effectués autrement qu'à l'aide d'une tâche Script Component... Une tâche souvent fastidieuse et longue à développer, en particulier si le nombre de champs à tester est particulièrement important.
Si devant cette lacune importante la solution de redévelopper un composant spécifique pour SSIS vient immédiatement à l'esprit, la réalité du déploiement sur les postes clients nous rattrape inexorablement. En effet, il est nécessaire de déployer le dit composant sur tous les serveurs impactés ce qui peut s'avérer rapidement délicat...

Je viens donc ici vous proposer une solution alternative, bien que peu satisfaisante pour les perfectionnistes dont je fais partie : l'utilisation de tables temporaires !
En effet, si SSIS ne possède pas les fonctions adéquates, le Transact-SQL peut venir à notre secours !
Il convient donc d'intégrer la source de données dans une table temporaire indépendante du format des données - par exemple des champs VARCHAR - puis d'effectuer les transformations et les vérifications d'intégrité dans le cadre d'une procédure stockée...

Si cette solution demeure peu reluisante, elle reste réellement la seule solution viable tant que SQL Server Integration Services n'offrira pas une bibliothèque de composants et de fonctions satifaisante...

A suivre...

Déplacement des bases de données système SQL Server 2005...

Bonjour à tous !!!

Voici un post qui ne devrait pas laisser indifférentes les personnes qui ont procédé au déplacement laborieux de toutes les bases d'une instance SQL, en particulier les bases système !

En effet, si le déplacement de ces bases est largement documenté dans la MSDN, il n'en demeure pas moins extrêmement long et fastidieux d'entreprendre une telle opération...
On note cependant l'effort important que Microsoft a consenti pour simplifier ce déplacement entre la version SQL Server 2000 et SQL Server 2005.

Pour mémoire, vous trouverez ici la documentation concernant le déplacement des fichiers des bases de données système de SQL Server 2000 et de SQL Server 7.
Sous SQL Server 2005, seul le déplacement des bases de données Master et mssqlsystemresource, nouvelle base de données qui n'existait pas sous SQL Server 2000, nécessite un traitement particulier.

Je vous propose donc ici un script customisable qui devrait ravir les grands et les petits qui souhaitent déplacer leurs bases de données système SQL Server 2005 en un clic !

Pour exploiter le code suivant, sauvegardez le dans un fichier Visual Basic Script (par exemple c:\movedb.vbs) et lancez le !

Function newliner(s)
newliner = Replace(s,"\n",VBCrLf)
End Function

Sub commande(s)
shcmd.Run "cmd /C " & s,1,true
End Sub

Dim base(4)
base(0) = array("ma","","","","master.mdf","mastlog.ldf")
base(1) = array("rs","mssqlsystemresource","data","log","mssqlsystemresource.mdf","mssqlsystemresource.ldf")
base(2) = array("mo","model","modeldev","modellog","model.mdf","modellog.ldf")
base(3) = array("db","msdb","MSDBData","MSDBLog","MSDBData.mdf","MSDBLog.ldf")
base(4) = array("tp","tempdb","tempdev","templog","tempdb.mdf","templog.ldf")

Function baseray(s)
baseray = array()
For Each row in base
If row(0) = s Then
baseray = row
End If
Next
End Function

' Déplace une base de données autre que "master" et "resource"
Sub movenormal(row, s)
database = row(1)
If s = "d" Then
nom = row(2)
fichier = row(4)
Elseif s = "j" Then
nom = row(3)
fichier = row(5)
End If
commande("sqlcmd -S " & instance & " -Q ""ALTER DATABASE " & database & " MODIFY FILE (name=" & nom & ", FILENAME='" & dest & "\" & fichier & "')""")
commande("net stop " & serviceinstance & " /yes")
commande("move """ & src & "\" & fichier & """ """ & dest & """ ")
commande("net start " & serviceinstance)
End Sub

' Déplace "master" et "resource"
Sub movemaster()
row = base(0)
fichierd = row(4)
fichierj = row(5)
commande("net stop " & serviceinstance & " /yes")

'SEARCH and replace parameters
cle = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\" & keyinstance
cleinstance = shcmd.RegRead(cle)
cle = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\" & cleinstance & "\MSSQLServer\Parameters"
If s = "d" Then
shcmd.RegWrite cle & "\SQLArg0", "-d" & dest & "\" + fichierd, "REG_SZ"
commande("move """ & src & "\" & fichierd & """ """ & dest & """ ")
Elseif s = "j" Then
shcmd.RegWrite cle & "\SQLArg2", "-l" & dest & "\" + fichierj, "REG_SZ"
commande("move """ & src & "\" + fichierj & """ """ & dest & """ ")
End If
commande("net start " + serviceinstance + " /f /T3608")
row = base(1)
database = row(1)
nomd = row(2)
nomj = row(3)
fichierd = row(4)
fichierj = row(5)
If s = "d" Then
commande("sqlcmd -S " & instance & " -Q ""ALTER DATABASE " & database & " MODIFY FILE (name=" & nomd & ", FILENAME='" & dest & "\" & fichierd & "')""")
commande("move """ & src & "\" & fichierd & """ """ & dest & """ ")
Elseif s = "j" Then
commande("sqlcmd -S " & instance & " -Q ""ALTER DATABASE " & database & " MODIFY FILE (name=" & nomj & ", FILENAME='" & dest & "\" & fichierj & "')""")
commande("move """ & src & "\" & fichierj & """ """ & dest & """ ")
End If
commande("sqlcmd -S " & instance & " -Q ""ALTER DATABASE " & database & " SET READ_ONLY""")
commande("net stop " & serviceinstance & " /yes")
commande("net start " & serviceinstance)
End Sub

set shcmd = WScript.CreateObject("WScript.Shell")

' Variables à redéfinir par InputBox ou manuellement:
' nom du serveur SQL et de l'instance dont on déplace les basese
serveur = InputBox("Nom du serveur")
If serveur = "" Then WScript.Quit
instance = InputBox("Nom de l'instance (Pour l'instance par défaut, laisser le champ vide)")
If Replace(instance, " ", "") = "" Then
instance = serveur
serviceinstance = "MSSQLSERVER"
keyinstance = "MSSQLSERVER"
Else
instance = serveur & "\" & instance
serviceinstance = "MSSQL$" & instance
keyinstance = instance
End If

src = InputBox("Chemin source")
dest = InputBox("Chemin destination")

line = "Base(s) à déplacer:\nmr: master + resource\ndb: msdbdata\nmo: model\ntp: tempdb\n(ex: 'mr, tp')"
db = InputBox(newliner(line))

line = "Déplacer (d)onnées, (j)ournaux\n ou les deux (d,j)"
dj = InputBox(newliner(line))

'line = "Paramêtres:\n- source: " + src + "\n- destination: " + dest + "\n- bases: " + db + "\n- objets: " + dj
'msgbox(newliner(line))

dbpar = Split(Replace(db," ",""),",")
djpar = Split(Replace(dj," ",""),",")

For Each db In dbpar
If db <> "mr" Then
r = baseray(db)
If ubound(r) > -1 Then
For Each dj in djpar
movenormal r, dj
Next
End If
Else
movemaster
End If
Next


Bonne journée !

lundi 18 juin 2007

Gestion des requêtes sur les vues partitionnées...

Bonsoir à tous !

Suite à de nombreux problèmes rencontrés avec les vues partitionnées, je souhaite clarifier certaines règles d'usage pour éviter tout problème avec ces éléments capricieux.

Tout d'abord, je voudrai parler du cas des champs date utilisés comme clé de partition. L'optimiseur de SQL Server n'est pas capable d'interpréter correctement les clauses CHECK sur ce type de données.
Le plus simple est alors de créer un champ acceptant l'optimisation des vues partitionnées. Par exemple, le plus simple avec une clé de partition sur l'année est de créer un champ de type INT contenant cette information.

Ainsi, la clé de partition ne sera plus le champ date qui nous posait problème, mais bien le champ INT qui lui sera supporté sans aucun problème.

Je souhaiterai ensuite évoquer les problèmes liés au mode de requêtage. En effet, l'optimiseur de requête de SQL Server effectuant le processus de Paramétrisation avant le processus de Planification de la requête, SQL Server ne procède pas aux optimisations nécessaires lors de la requête suivante :
DECLARE @TEST INT
SET @TEST = 50
SELECT Id, PartitionCol FROM VALL WHERE PartitionCol = @TEST


Nous pouvons alors privilégier le SQL Dynamique dans ce cas bien particulier, seule solution pour obtenir le résultat escompté.

Enfin, je voulai attirer ici l'attention sur l'importance des statistiques dans le cadre de l'utilisation des vues partitionnées. En effet, de mauvaises statistiques induisent le stockage d'un mauvais plan d'exécution. Les performances s'en ressentent alors de façon très importante, en particulier dans le cadre de l'utilisation de procédures stockées, où en cas de paramétrisation des requêtes ad hoc.

Bonne soirée !

Optimisation des requêtes à l'aide des vues partitionnées

Bonjour à tous !

Les vues partitionnées, tout comme les tables partagées dans SQL Server 2005, sont un moyen simple de répartir ses données sur plusieurs filegroups afin d'alléger les coûts de maintenance. Il est même possible à l'aide des vues partitionnées distribuées de répartir la charge, tout simplement en stockant les différentes tables sur différents serveurs.
Les vues partitionnées regroupent donc les données de plusieurs tables dont la structure est identique. Ainsi, toutes les opérations d'insertion, de modification et de suppression peuvent être effectuées sur une vue partitionnée, moyennant une condition sine qua non : la clé de répartion doit faire partie de la clé primaire de chacune des tables aggrégées.

Si les vues partitionnées sont très couramment utilisées pour aggréger les données dans le cadre d'infocentres multisites, les vues partitionnées n'en demeurent pas moins la seule façon de faire du partitionnement horizontal sous SQL Server 2000.
Dès lors, ces vues s'avèrent incontournables sous SQL Server 2000 pour architecturer une solution à forte volumétrie ou disposant d'une fenêtre de données glissante - dans ce cas particulier, la clé de répartition est couramment la date.

Afin de mieux comprendre comment optimiser une vue partitionnée, commençons par un exemple. Créons deux tables possédant la même structure.
CREATE TABLE TB1 (Id INT NOT NULL CONSTRAINT PK_TB1 PRIMARY KEY, PartitionCol INT NOT NULL)
CREATE TABLE TB2 (Id INT NOT NULL CONSTRAINT PK_TB2 PRIMARY KEY, PartitionCol INT NOT NULL)


La vue suivante est alors la vue "partitionnée" qui permet de concaténer les données de ces deux tables :
CREATE VIEW VALL AS SELECT Id, PartitionCol FROM TB1 UNION SELECT Id, PartitionCol FROM TB2

Cependant, cette vue est loin d'optimiser les performances, effectuant même une tâche de reconnaissance afin d'éviter les doublons.
Admettons maintenant que nos données soient réparties de façon bien distincte selon la clé de répartition. Nous pouvons alors de façon sûre écrire :
ALTER VIEW VALL AS SELECT Id, PartitionCol FROM TB1 UNION ALL SELECT Id, PartitionCol FROM TB2

Cette vue est maintenant nettement plus performante, mais reste toujours sans grand intérêt.
Appliquons maintenant des contraintes de type CHECK afin d'assurer au système la bonne séparation des données, par exemple en séparant les éléments négatifs des éléments positifs :
ALTER TABLE TB1 ADD CONSTRAINT CK_TB1_PartitionCol CHECK (PartitionCol < 0)
ALTER TABLE TB2 ADD CONSTRAINT CK_TB2_PartitionCol CHECK (PartitionCol >= 0)


En exploitant notre vue à l'aide de la commande suivante, on obtient enfin le résultat attendu :
SELECT Id, PartitionCol FROM VALL WHERE PartitionCol = 50

En effet, on peut voir dans le plan d'exécution de notre requête que seule la table TB2 est effectivement requêtée.

Nous pouvons maintenant procéder à la dernière étape de l'optimisation de notre vue partitionnée. Pour cela il nous faut ajouter notre clé de répartition dans la clé primaire. Ainsi, nous pourrons utiliser notre vue partitionnée pour insérer et modifier nos données.
ALTER TABLE TB1 DROP CONSTRAINT PK_TB1
ALTER TABLE TB1 ADD CONSTRAINT PK_TB1 PRIMARY KEY (Id, PartitionCol)
ALTER TABLE TB2 DROP CONSTRAINT PK_TB2
ALTER TABLE TB2 ADD CONSTRAINT PK_TB2 PRIMARY KEY (Id, PartitionCol)


La commande suivante est alors possible !!!
INSERT INTO VALL (Id, PartitionCol) VALUES (10, 1000)

Je conviens aisément que ces vues partitionnées présentent de nombreuses difficultés d'implémentation, et qu'en matière de "fenêtre glissante", rien ne vaut les tables partitionnées de SQL Server 2005. En revanche, qu'il s'agisse d'une architecture sous SQL Server 2000 ou une architecture distribuée sur plusieurs serveurs, les vues partitionnées s'avèrent être une arme redoutable dans l'arsenal du développeur Base de Données.

A bientôt, et tous à vos benchs !