Un plan de maintenance complet pour SQL Server 2008
Optimisation des bases de données
Cette section est dédiée à l'optimisation des bases de données. Dans cet exemple, nous allons configurer le code pour faire ce qui suit (voir un schéma approximatif de la façon dont les serveurs sont connectés) ;
- Sauvegarde de la base de données du serveur en direct vers un dossier de sauvegarde partagé
- Réindexer la base de données
- Arrêtez toutes les connexions et restaurez la base de données sur le serveur de rapports.
- Les serveurs liés seront bientôt dans un article séparé
- Log Shipping sera bientôt dans un article séparé
Lorsque vous le faites sur plusieurs serveurs, vous devez autoriser le compte exécuté par SQL Server à accéder au système de fichiers des serveurs cibles.
Tout le code SQL est exécuté à partir d'une base de données d'utilitaires et le schéma est défini sur "maint"
Il y a une foule d'autres articles que nous avons écrits ci-dessous qui pourraient vous intéresser.
Plan d'entretien complet
Maintenant, nous pouvons gérer tous ces segments de code séparés avec une autre procédure qui les appellera dans le bon ordre, cela sera mis dans le serveur live. Le code s'exécute comme suit ;
- Travailler le temps
- Si c'est le soir, réindexez la base de données
- Exécutez une sauvegarde de la base de données (ajoutez là où vous en avez besoin)
- Si c'est le soir, sauvegardez les autres bases de données (ajoutez là où vous en avez besoin)
- Tuez les connexions et restaurez la base de données (ajoutez là où vous en avez besoin)
Ce code a été essayé et testé et a fonctionné pendant des mois sans aucun problème.
Veuillez noter que la réduction des fichiers journaux et des fichiers de base de données doit être réduite au minimum, en exécutant une sauvegarde, vous videz le journal (bien qu'il conservera l'espace utilisé). S'il est nécessaire lorsqu'il doit être exécuté en dehors des heures normales de travail.
SQL Code
Use [utilities]GOCREATE PROC [maint].MaintenancePlan AS BEGINDECLARE @BackupType VARCHAR(1)='E'IF DATEPART(HOUR,GETDATE()) BETWEEN 5 AND 21 BEGINSET @BackupType='D'END--EXEC ('USE TempDb; DBCC SHRINKFILE(templog, 0)');--This is only needed when space is at a premium!--Re-index LiveIF @BackupType='E' EXEC [maint].DatabaseReIndex 'dbname'--Create BackupBACKUP DATABASE TO DISK=N'{backuplocation}{dbname}.bak'WITH NOFORMAT, INIT, NAME =N'{dbname}', SKIP, NOREWIND, NOUNLOAD, STATS= 10;--EXEC ('USE ; DBCC SHRINKFILE(_log, 0)');--This is only needed when space is at a premium!--Backup Other Files at NightIF @BackupType='E' BEGIN EXEC [maint].DatabaseReIndex 'dbname' --Backup Others BACKUP DATABASE [databasename] TO DISK=N'{backuplocation}{dbname2}.bak' WITH FORMAT,INIT, NAME =N'{dbname2}',SKIP, NOREWIND, NOUNLOAD, STATS= 10END--Restore Backups on other serverEXEC [server].[utilities].[maint].KillConnections 'dbname';EXEC [server].[utilities].[maint].RestoreDatabase_{dbname};--Restore Backups on other server for db_2 etcIF @BackupType='E' BEGIN EXEC [server].[utilities].[maint].KillConnections 'dbname2'; EXEC [server].[utilities].[maint].RestoreDatabase_{dbname2};ENDENDGOBase de données de sauvegarde
Pour obtenir le code de sauvegarde de votre base de données, il est plus simple de scripter le code à partir de SSMS.
Suivez le processus que vous utiliseriez normalement, puis sélectionnez « Script Action to New Query Window ».
Copiez ce code dans le plan de maintenance.
Réindexer la base de données
Ensuite, nous pouvons ajouter du code pour réindexer notre base de données, il s'agit encore une fois d'une procédure stockée partagée où vous n'avez qu'à indiquer le nom au système.
Pour éviter la duplication de code, vous pouvez en savoir plus sur le lien ci-dessous.
Tuer les connexions
Lorsque vous effectuez une restauration sur la base de données, vous ne pouvez avoir qu'une seule connexion à celle-ci (le processus effectuant la restauration), nous pouvons donc créer une procédure stockée pour fermer toutes les connexions en dehors du processus en cours. Nous avons à nouveau créé un article séparé pour cela.
Restaurer la base de données
Ce code peut également être scripté à partir de SQL Server Management Studio. Si vous ajoutez ce code à une procédure stockée, vous pouvez l'appeler à partir d'autres processus et même d'autres serveurs très facilement. Nous avons déplacé cela dans un article séparé pour couvrir plus d'options.
