Taches d'administration DBA d'une base MSSQL

Version imprimable

16/12/2015, 14h50
Le-DOC

Taches d'administration DBA d'une base MSSQL

Bonjour,
Je viens du monde Oracle, et je suis de plus en plus amené à devoir gérer des bases SQLServer.
J'aimerais avoir votre avis sur les taches standard d'administration d'une base MSSQL.

De mon coté, je préfère tout gérer via des scripts bat afin de logger facilement les infos, et tout notre système de remonté d'erreur passe par la.
Voila pourquoi, je préfere me passer du "Plan de Maintenance".

Coté sauvegarde, je comptais faire un truc classique (soit: sauvegarde full quotidienne, soit: sauvegarde full hebdo + incrémentale quotidienne). Ca dépend de la taille.

Concernant les taches quotidiennes:
L'Update des statistiques: Un script qui sélectionne la database adéquate, récupère le nom de chaque table, et fait un UPDATE STATISTICS de ces tables

Concernant les taches hebdomadaires:
Check Database Integrity: Un simple DBCC CHECKDB(@name) WITH NO_INFOMSGS pour chaque database
Les Purges: un msdb.dbo.sp_delete_backuphistory et un msdb.dbo.sp_purge_jobhistory suivi d'une purge des sauvegardes

C'est tout ce que je comptais mettre comme jobs.

J'ai souvent remarqué des taches programmées hebdomadairement, (sans trop de suivi) comme:
Rebuild Index: Est ce utile de le planifier, ou est ce plutôt une chose à faire à la main ?
Shrink Database: Même question (car si on le fait quotidiennement), on aura plus beaucoup de marge de manœuvre quant la base sera vraiment trop grosse.

Que pensez vous de cette méthode ?

Merci

En cas, si vous voulez, je posterai des scripts.
16/12/2015, 15h00
Invité

Shrink Database : mais pourquoi réduire la taille des fichiers et fragmenter les données et indexes ? Pour détériorer les performances ?
Pour moi (et d'autres ici), c'est inutile dans un cadre autre qu'exceptionnel (genre une suppression de grosse table)

Citation:

L'Update des statistiques: Un script qui sélectionne la database adéquate, récupère le nom de chaque table, et fait un UPDATE STATISTICS de ces tables

sp_updatestats fait ça en une seule commande !
16/12/2015, 15h40
SQLpro

Citation:

Envoyé par Le-DOC

Bonjour,
Je viens du monde Oracle, et je suis de plus en plus amené à devoir gérer des bases SQLServer.
J'aimerais avoir votre avis sur les taches standard d'administration d'une base MSSQL.

De mon coté, je préfère tout gérer via des scripts bat afin de logger facilement les infos, et tout notre système de remonté d'erreur passe par la.

Erreur !!! L'agent SQL est fait spécialement pour cela : traçabilité, facilité d'exploitation, information automatique par mail en cas d'échec et optimisation du déclenchement des tâches ! Vous aurez moins d'info et ce sera inexploitable en dépouillement !!!

Citation:

Voila pourquoi, je préfere me passer du "Plan de Maintenance".

Çà c'est autre chose et vous avez bien raison.

Citation:

Coté sauvegarde, je comptais faire un truc classique (soit: sauvegarde full quotidienne, soit: sauvegarde full hebdo + incrémentale quotidienne). Ca dépend de la taille.

Pensez à compressez systématiquement les sauvegardes, ça réduit de 2 à 6 fois les temps d'exécution, sauf si TDE.
Vous oubliez les indispensables sauvegardes du journal de transaction qui joue l'équivalent de la création des REDO logs d’Oracle. Sans cela votre journal va croitre jusqu'à saturation des disques. Mais vous ne devez le faire que pour les bases dont le mode de récupération des <> 'simple'

Citation:

Concernant les taches quotidiennes:
L'Update des statistiques: Un script qui sélectionne la database adéquate, récupère le nom de chaque table, et fait un UPDATE STATISTICS de ces tables

Il est dommage que vous ne suiviez pas un cours DBA SQL Server. Car cette tâche est plus complexe qu'il n'y parait.
1) il faut D'ABORD faire la maintenance des index, car suivant la façon dont est faite cette maintenance une partie des stats est remise à jour (notamment par ALTER INDEX ... REBUILD)
2) il faut obtenir la liste des stats réellement obsolète, c'est à dire dont le taux de modif est d'au moins 10 voire 20% de la table
3) il faut voir si c'est un FULL SCAN ou pas qu'il faut faire pour cette mise à jour des stats
Tout cela nécessite de lire base par base les DMV :
sys.stats
sys.dm_db_stats_properties

Citation:

Concernant les taches hebdomadaires:
Check Database Integrity: Un simple DBCC CHECKDB(@name) WITH NO_INFOMSGS pour chaque database
Les Purges: un msdb.dbo.sp_delete_backuphistory et un msdb.dbo.sp_purge_jobhistory suivi d'une purge des sauvegardes

Il faut vérifier l'intégrité à la même fréquence que vous faites les sauvegardes FULL sinon, vous risquez de ne plus pouvoir corriger via un RESTORE PAGE. Lire l'article que j'ai écrit à ce sujet :
http://blog.developpez.com/sqlpro/p1...ver-corrompues

Citation:

C'est tout ce que je comptais mettre comme jobs.

J'ai souvent remarqué des taches programmées hebdomadairement, (sans trop de suivi) comme:
Rebuild Index: Est ce utile de le planifier, ou est ce plutôt une chose à faire à la main ?
Shrink Database: Même question (car si on le fait quotidiennement), on aura plus beaucoup de marge de manœuvre quant la base sera vraiment trop grosse.

Que pensez vous de cette méthode ?

Merci

En cas, si vous voulez, je posterai des scripts.

Oui, la reconstruction des index est importante dans SQL Server car SQL Server utilise des CLUSTERED index pour toutes les tables avec PK (équivalent des IOT d'Oracle)
Et le reconstruction des index ne pénalise pas les tables contrairement à Oracle.

En ce qui concerne les SHRINK c'est d'une haute stuidité

Enfin, une tâche a ne pas négliger : la verification du capacity planning

A +
16/12/2015, 15h41
janlouk

Pour Integrity Check, Index et Statistics Maintenance tu peux regarder pour les scripts OLA qui fonctionnent très bien.

https://ola.hallengren.com/
16/12/2015, 17h13
Le-DOC

@janlouk: Je me suis en effet basé sur ce site pour créer mes scripts bat

@7gyY9w1ZY6ySRgPeaefZ: Il me semble d'apres mes souvenirs, que si on faisait uniquement le sp_updatestats, il mettait a jour les stats des tables, mais pas des indexes correspondants, alors que si on le faisait individuellement par table, ça rafraichissait les indexes en meme temps.

@SQLpro: D'ou la nécessité de reconstruire les indexes ?
Sinon, j'ai suivi un cours DBA SQL Server, mais c'etait un cours Microsoft, et trop théorique sur beaucoup de points :(

Concrètement, pour les stats, c'est vrai que ca serait mieux de faire un calcul optimisé au besoin, mais pas le temps de monter une usine a gaz :)
Je cherche juste une méthode a implémenter a toute mes futures bases SQLServer.

Concernant le plan de maintenance, on a tout un mécanisme de vérification des traitements batch, avec remontée en temps réel dans notre Intranet d'Exploit, ect.. donc aussi puissant que puisse etre le plan de maintenance, je préfere conserver cette méthode (mais c'est dans le cas de notre SI).

Donc pour résumer:
Action Quotidienne:
Update des Statistiques
Sauvegarde Incrémentale

Action toute les X heures:
Sauvegarde des Logs (j'avais oublié d'en parler en effet)

Action Hebdo:
Update des Statistiques
Check Database Integrity
Purges diverses
Backup Full

Reste a étudier:
Capacity Planning Cette notion est déporté sur un système de supervision (NAGIOS)
Utilité du Rebuild Index vu que les stats sont rafraichies par les tables !?

Qu'en pensez vous ?
16/12/2015, 17h17
SQLpro

1 pièce(s) jointe(s)

Citation:

Envoyé par janlouk

Pour Integrity Check, Index et Statistics Maintenance tu peux regarder pour les scripts OLA qui fonctionnent très bien.

https://ola.hallengren.com/

Il y a actuellement un bug MS qui survient dans les scripts d'Olla Hallengreen qui fait que nous avons du l'abandonné dans une grosse exploit (plusieurs To de données dans une base avec du partitionnement).

Ce bug est ouvert dans CONNECT le site des bugs MS SQL Server :
https://connect.microsoft.com/SQLSer...sp1-error-4819

Il va être corrigé dans le SP suivant.

Pour plus d'info en général sur l'admin SQL Server :
Pièce jointe 195471 Pièce jointe 195471

A +
16/12/2015, 17h29
SQLpro

Citation:

Envoyé par Le-DOC

Concernant le plan de maintenance, on a tout un mécanisme de vérification des traitements batch, avec remontée en temps réel dans notre Intranet d'Exploit, ect.. donc aussi puissant que puisse etre le plan de maintenance, je préfere conserver cette méthode (mais c'est dans le cas de notre SI).

NON, NON et re NON !!!! Je ne parle pas des plan de maintenance et de son assistant qui est un gadget. Je parle de l'Agent SQL qui est un planificateur de tâche, et notamment des scripts SQL que vous allez gentiment écrire....
En effet votre outil de planification (dollar U par exemple) va interrompre SQL Server à des moments inopportuns et vous n'aurez pas les métadonnées d'exécution dans le serveur lui même ce qui sert à tout un tas de chose dont l'optimisation interne et l'alimentation des assistants qui vous seront fort utile en cas de crash !
Ne renversez pas la vapeur.
Qu'est ce qui est critique dans votre SI ? Obtenir à tout prix des logs à la con pour satisfaire l'ego démesuré d'un fonctionnaire qui veut son tableau de bord (genre Hollande et sa courbe du chômage) ou bien faire un bon boulot capable de sauver la situation en cas de problème ? (genre réduire le chômage)
Vous pourrez toujours récupérer les stats d’exécution et les métriques de toutes sortes en faisant quelques requêtes sur les tables msdb sys.job...

Citation:

Donc pour résumer:
Action Quotidienne:
Update des Statistiques
Sauvegarde Incrémentale

NON, NON et re NON !!!
HORAIRE :
toutes les x minutes :
0) Sauvegarde du JT des bases en mode de journalisation FULL ou BULK LOGGED
0 bis) Vérification de capacité des espaces de stockage et des disques

QUOTIDIENNE
1) Maintenance des index
2) Maintenance des statistiques
3) sauvegarde FULL des bases de moins de 250 Go
4) sauvegarde DIFF des bases de plus de 250 Go
5) DBCC CHECKDB des bases de moins de 250 Go

HEBDO
6) sauvegarde FULL des bases de plus de 250 Go
5) DBCC CHECKDB des bases de plus de 250 Go

Citation:

Action toute les X heures:
Sauvegarde des Logs (j'avais oublié d'en parler en effet)

Action Hebdo:
Update des Statistiques
Check Database Integrity
Purges diverses
Backup Full

Reste a étudier:
Capacity Planning je sais pas encore a quoi ca sert, je vais voir ca
Utilité du Rebuild Index vu que les stats sont rafraichies par les tables !?

Qu'en pensez vous ?

A +
22/12/2015, 14h36
elsuket

Bonjour,

Ayant été un aficionados de SQL Server Agent pendant quelques années, j'avais donc essayé de mettre en place des jobs de façon centralisée pour monitorer plusieurs instances de SQL Server à l'aide de la fonctionnalité CMS.
Malheureusement, elle s'est révélée ne pas être très fiable (plantages inexpliqués par exemple, ou planifications qui sautent, ...).
Par ailleurs il arrive que l'Agent ne redémarre pas automatiquement même s'il est paramétré pour cela, après une application de patches Microsoft.

Actuellement, j'utilise des scripts PowerShell en combinaison à un contrôle de code source (pour récupérer la dernière version des scripts SQL) et je journalise les résultats dans une base de données qui centralise les exécutions et leurs résultats. Finalement j'ai réinventé la roue, mais jamais un plantage. Si je dois combiner les tâches de maintenance de base de données avec des tâches de maintenance business qui ont été mise en place bien avant mon arrivée, c'est bien plus simple que de créer une table dans une base de données accessoire pour gérer cela.

La centralisation du journal me permet de créer simplement des rapports, et de voir simplement ce qui n'a pas fonctionné.
Bien sûr on peut faire cela avec SQL Server Integration Services.

Il existe aussi certaines tâches où l'on doit extraire des données d'une base et interroger Active Directory : PowerShell permet de réaliser ce genre de tâches.

Donc je dirais, comme bien souvent : prenez la solution qui répond le mieux à votre problématique. SQL Server Agent peut tout à fait y répondre, c'est d'ailleurs la plupart du temps le cas.
Si vous en avez un paquet (je dois en superviser 200+ par exemple), il est possible que vous optiez pour une solution comme PowerShell.

@++ ;)