J'ai pris tellement de retard dans la retranscription de mes notes que j'ai même perdu de vue ce quatrième volet que je publie enfin (précédents articles: jour 1, jour 2, jour 3). Cependant, la section relative à réplication ne sera pas aussi complète que je l'avais initialement prévu. La réplication est un vaste domaine dont la complexité nécessiterait plusieurs articles. Ne disposant malheureusement pas de ce temps que j'investis plutôt dans Python, je me limiterai donc à une approche introductive.
Dominique, le 2 novembre 2009
Eléments de monitoring
- Activity monitor: Seulement disponible dans sql serveur 2008 via le management console, l'activity monitor présente différents graphes (CPU Usage, Waiting tasks, Database IO), des informations complémentaires (data file IO information, ressources waits, recent expensive queries, etc), la liste des processes en auto-refresh. Super pratique pour avoir une vue d'ensemble d'un serveur sql.
- L'usage de stored procedure: sp_who, sp_lock
- L'usage de Dynamic Management View: voir plus loin.
Les éléments principaux à monitorer sont les suivants:
- La mémoire
- Les disques (s'applique diffiicilement au San)
- Le processeur
- Le réseau
En suivant l'évolution du perfom "Cache Hit Ratio" il est possible de savoir si sql serveur dispose d'assez de mémoire.
Si ce ratio tombe en dessous de 90 %, il faut intervenir (ajout de mémoire ou éventuellement vérifier le data modèle et indexes si déjà assez de mémoire).
Voir SqlServer BufferManager::Cache it ratio
Monitorer les performance disque
Les requêtes de lectures tombent dans une queue de lecture si les disques ne savent pas suivre le flot des demandes d'accès. Pour savoir si les disques deviennent un goulot d'étranglement, il suffit de garder cette queue à l'oeil. En gros, elle ne doit jamais excéder 3 * le nombre de disque physique disponible (même rarement).
Ainsi, pour 3 disques en raid, cette valeur ne peut excéder 15.
Voir perfMon, Physical Disk::Avg Disk Queue length ou Physical Disk::Current Disk Queue length.
Les lecteurs lents peuvent également être détectés via la "dynamic management view" sys.dm_io_pending_io_requests car ils y feront apparaitre des entrées.
Toutes ces considérations n'étant pas applicable au stockage de données sur SAN.
Le seule commentaire reçu concernant le stockage SAN, c'est qu'il serait opportun d'utiliser plusieurs fichiers sur différent lecteurs (virtuels) pour stocker les données; ainsi sql serveur pourra stresser plus fort le système d'accès disque SAN et fournir de meilleurs temps de réponse sql.
Performance processeur
En monitorant les performances du processeur, il est possible de savoir si la machine dipose de cette ressource en suffisance (nbre et fréquence).
Si PerfMon démontre une utilisation continue de 80% à 90% alors les ressources processeurs sont insuffissantes.
Monitoring - Performance monitor et Sql profiler (**)
Voici une méthode de monitoring très intéressante et surtout très partique.
Tout d'abord, il faut savoir qu'il est possible de configurer le "performance monitor" de windows pour qu'il capture les traces et les enregistrer dans un fichier.
En exemple pratique, capturons Hit ratio, Cpu usage, Disk Queue toutes les deux secondes, le tout à enregistrer dans un fichier.
Durant ce temps, on utilise le Sql Profiler pour monitorer les opérations effectuées par sql serveur.
Lorsqu'un événement anormal est détecté dans le profiler (requêtes anormalements lentes, temps de réponse anormal, etc), il est alors possible de comparer ces événements avec les données collectées par le performance monitor.
A cet effet, Sql Profiler dispose d'une option d'import de fichier perfMon (menu File). Une fois les données perfMon chargées, la sélection d'un événement dans le profiler affiche, en regard, l'état des traces de performance. Il est donc possible de connaître l'état des ressources de la machine à l'instant de l'évènement.
Pour bénéficier correctement de cette fonctionalité, il faut bien entendu que la machine exécutant le perfmon d'une part (sql serveur) et le sql profiler (un serveur quelconque) aient tous deux une configuration horaire correcte (la synchronization des horloges systèmes étant ici primodiale).
Monitoring avec l'aide des DMV
Voici quelques notes concernant les dynamic management view qui peuvent aider à monitorer sql server. Il existe beaucoup de DMV disponible dans SQL serveur et seulement quelques-unes seront aborder ici.
sys.dm_exec_session
select * from sys.dm_exec_session where session_id > 50
Retourne une information similaire a sp_who.
sys.dm_db_index_usage_stats
Retourne des information concernant l'usage des indexes.
En autre, il y est possible de savoir si un index est exploité ou scanné (le cluster index scan étant assez cher en ressource).
select * from sys.dm_db_index_usage_stats
Index_id: 1 pour les clustered index, >1 pour autres indexes.
User_seeks: nbre de fois que l'index a été utilisé pour retrouver des données.
User_scan: nbre de fois que l'index à été entièrement lu.
User_scan: nbre de fois que l'index à été entièrement lu.
sys.dm_exec_query_stats
Permet de connaître les query les plus souvents exécutés (via execution_count) ainsi que leurs coûts en opérations disques.
Permet de connaître les query les plus souvents exécutés (via execution_count) ainsi que leurs coûts en opérations disques.
select total_physical_reads + total_logical_reads + total_physical_writes from sys.dm_exec_query_stats order by 1
L'article "Are you using sql's missing index DMV" du Weblog de Bart Duncan expliquant comment utiliser les DMV pour découvrir les indexes manquant dans Sql Server.
sys.dm_io_pending_io_requests
Cette dynamic management view indique les accès disques en attente dans une queue.
Elle permet donc de savoir si les disques représente ou non un goulot d'étranglement.
LECTURE autour des dynamic management view (**)
Le livre "Inside Sql Server 2005: Query Tuning and optimization" (édt: Microsoft Press) explique et utilise de nombreuses DMVs.La réplication
La réplication est un procédé permettant de mettre une partie des informations d'une DB à disposition dans une autre DB
Parmis les cas d'utilisation de la réplication, il y a:
- Mise à disposition de données pour "Reporting".
- Consolidation des données de différents départements.
Par exemple, chaque pays (FR, UK, NL) disposant de ses sales orders, le tout consolidé régulièrement à la maison mère (BE). - Mettre des données à disposition auprès des utilisateurs finaux.
Par exemple, la maison mère (BE) "publiant" la nouvelle liste des prix dans les différents départements (FR, UK, NL). - Mettre des données à disposition en mode déconnecté.
C'est par exemple le cas des vendeurs itinérants. En utilisant une réplication de type "merge", ils peuvent remplir des bons de commandes sur leur portable (dans une DB locale) pour ensuite la synchroniser avec la DB principale une fois le portable reconnecter sur le réseau d'entreprise.
En aucun cas, la réplication peut être assimilée à un procédé permettant de faire du "load balancing" entre serveurs sql.
Pour être clair, Microsoft ne dispose pas - à l'heure actuelle - de solution de "load balancing" entre plusieurs sql serveurs.
La réplication - la métaphore
Le modèle de la publication des livres comme métaphore
Pour facilité la compréhension des modèles et procédés de de réplication, Microsoft a bâti son modèle de réplication sql sur le modèle commerciale de la publication des livres/revues.Ainsi, en suivant ce modèle, l'on retrouve les éléments suivants:
Publisher:
Un publisher sera un serveur sql mettant des publications à disposition (a comprendre comme "des revues contenant des données").
Les publications sont constitués d'articles; les articles étant les éléments d'informations à repliquer (ensemble de tables, partiel ou non, de colonnes, éléments sql, etc).
Subscriber:
A l'identique du modèle commercial, les gens souscrivent (subscribe) des abonnements pour obtenir un exemplaire des revues publiées.
Ainsi donc, un subscriber sera un serveur sql ayant souscrit un abonnement chez un publisher pour obtenir une copie des données (les "articles" dans le langague de la réplication) qu'il stockera dans une de ses propres bases de données.
Distributor:
Toujours en suivant le modèle économique de l'édition, le distributeur (distributor) aura pour mission d'acheminer les informations entre le publisher et le subscriber.
En résumé:
Un processus de réplication fait intervenir 3 services de sql serveur.
Un publisher (les données à dupliquer), un ou plusieurs subscribers (les destinataires recevant les données) et un ou plusieurs distributors (achéminant les données).
Ces 3 services peuvant fonctionner sur un seul serveur sql ou sur des sql serveurs distincts.
Il est communément admit de faire fonctionner le distributor sur la même serveur sql que le publisher (voir, selon condition décrite ci-après, faire fonctionner le service de distribution sur les subscribers).
S'il y a moins de 3 subscribers:
Le distributor peut être installé sur le publisher. Dans ce cas, le couple publisher-distributor pousse les données sur les subscribers.
Ce modèle est appellé "push replication".
S'il y a plus de 3 subscribers:
C'est en autre les cas des laptops connectés en intermittence.
Dans ce cas, Microsoft conseille de faire fonctionner un distributor par subscriber.
Dans ce cas, le couple subscriber-distributor extrait les données du publisher lorsqu'ils est connecté.
Ce modèle est appellé "pull réplication"
La réplication - Limitation
En générale, il est possible de répliquer des données entre sql serveurs dans les deux sens.
Cependant, la réplication avec une db oracle n'est possible que dans un seul sens; à savoir uniquement de SqlServer vers Oracle.
La réplication - types de réplications
D'une façon générale, il est convenable de concevoir la réplication comme un processus allant dans une seule direction. A savoir, la copie des données du publisher vers les subscribers.
Cependant, il est également possible de concevoir des réplications ou les subscribers font des mises-à-jour de données sur le publisher (Merge réplication, voir plus loin).
Sql serveur dispose de plusieurs modèles de réplications:
- Snapshot: Ne fonctionne que dans un seul sens. A ne pas utiliser trop souvent en production car cette réplication est très gourmande en ressource. L'utilitaire bcp étant utilisé pour la réplication snapshot, la table est entièrement dupliquée dans un fichier qui est ensuite transmis au subscriber (où il sera ensuite recharger).
- Merge Replication: Permet de synchroniser des données dans les deux sens. Dans ce cas, des conflits de synchronisation peuvent apparaître si les données ont étés modifiées sur les deux serveurs. Ces conflits sont gérés automatiquement sur base d'une règle de priorité ou règles plus avancées (NB: règle définie pour chaque élément des articles). Un gestionnaire de conflit permet de visualiser les collision de synchronisations et d'éventuellement les corriger.
- Transactional Replication: Tout comme la réplication snapshot, cette réplication ne fonctionne que dans un seul sens. Le "log reader agent" du distributor inspecte le transaction log du publisher et y collecte les modifications concernant les articles (information stockée localement sur le distributor). Le "distributor agent" du distributor collecte ces informations et les poussent vers les subscribers.
- Transactional Replication with update from subscriber: sorte de replication bi-directionnelle basée sur la réplication transactionnelle. Visiblement rien de très intéressant ou spécial, l'instructeur faisant l'impasse sur ce point.
- Peer to Peer replication: réplication bi-directionnelle basé sur les modifications du transactional log ou les SQL serveurs sont en contacts directs.
Ce type de réplication est utilisé dans des cas plus spéciaux ou il est même possible d'éviter les problèmes de synchronisation (merge error) en partitionnant intelligement les données entre serveurs (voir 8-42). La réplication peer to peer peut aussi être utilisée pour améliorer les performances et la scalabilité, avoir un meilleur contrôle sur le traffic réseau, peut convenir pour les connections intermittentes (mais pas des jours!) ou encore pour implémenter un "load-Balancing" maison (voir 8-40).
Par principe, la réplication est schedulée à interval régulier.
En effet, une réplication continue est bien trop couteuse en ressources.
Lorsqu'il s'agit de laptops itinérants, il est même préférable de lancer la replication depuis le laptop sur action de l'utilisateur.
Dans ce cas, on utilise un bout de code sur l'api SMO qui active le PULL distributor du laptop.
-- fin --
Aucun commentaire:
Enregistrer un commentaire