IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Administration SQL Server Discussion :

Always-on maintenance/backup/et logfile growth [2012]


Sujet :

Administration SQL Server

  1. #1
    Membre régulier
    Profil pro
    Inscrit en
    Août 2009
    Messages
    146
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2009
    Messages : 146
    Points : 100
    Points
    100
    Par défaut Always-on maintenance/backup/et logfile growth
    Bonjour,

    Je suis en train de mettre en place un SQL server always-on pour un client et voici la configuration:
    Un primaire et un secondaire en lecture en mode synchrone avec backup préféré sur le secondaire.

    Je me pose les questions suivantes:

    Backups:
    - Quelle stratégie de backup mettre en place sur le secondaire ?

    Maintenance plan:
    - mettre en place les rebuilds/reorganize sur le primaire affecte aussi le secondaire et vice versa (je penses que je rêve pour le vice versa :p) ?
    - Faire mes DBCC CHECKDB sur la secondaire est une bonne politique afin de détecter la corruption ? (malgrès le système de récupération de blocs always-on)

    logfile growth:
    - Comment se gère le processus de grossissement/réduction de la taille des fichiers de transaction log (sur les 2 noeuds)?
    - J'ai lancé un gros batch sur la primaire et le log ne se réduit pas dans le temps malgrès les plans de maintenance backups etc. Pourtant ce n'est pas en production actuellement et personne ne se connecte.

    Merci d'avance,
    Pour votre retour d'expérience.

    Cordialement.

  2. #2
    Membre régulier
    Profil pro
    Inscrit en
    Août 2009
    Messages
    146
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2009
    Messages : 146
    Points : 100
    Points
    100
    Par défaut
    un petit up concernant le transactionlog.

    Je peux voir ca:
    SELECT [log_reuse_wait_desc]
    FROM [master].[sys].[databases]

    log_reuse_wait_desc
    NOTHING
    NOTHING
    NOTHING
    NOTHING
    NOTHING
    NOTHING
    ACTIVE_TRANSACTION
    LOG_BACKUP
    NOTHING

    J'ai beau faire autant de backup que log que je peux ca ne change strictement rien.

  3. #3
    Expert éminent sénior
    Avatar de mikedavem
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2005
    Messages
    5 450
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Distribution

    Informations forums :
    Inscription : Août 2005
    Messages : 5 450
    Points : 12 891
    Points
    12 891
    Par défaut
    - Quelle stratégie de backup mettre en place sur le secondaire ?
    Ta stratégie n'est pas différente d'une instance standalone et dépend uniquement de ton business. Il faut traduire ensuite ton besoin en planification de sauvegarde vers AlwaysOn.
    Sur le secondaire il faut cependant faire avec les limitations:
    - Backup full uniquement en copy only (ce qui implique aussi qu'une stratégie à base de backup différentiel sera impossible)
    - backup diff non supporté
    - backup log possible

    Maintenance plan:
    - mettre en place les rebuilds/reorganize sur le primaire affecte aussi le secondaire et vice versa (je penses que je rêve pour le vice versa :p) ?
    - Faire mes DBCC CHECKDB sur la secondaire est une bonne politique afin de détecter la corruption ? (malgrès le système de récupération de blocs always-on)
    Sur le secondaire ta base de données est en lecture seule donc aucune chance d'avoir un impact sur le secondaire :-)
    Si tu fais tes backups sur le secondaire alors oui c'est une bonne pratique d'effectuer la vérification d'intégrité sur le primaire et sur le secondaire concerné par les sauvegardes


    logfile growth:
    - Comment se gère le processus de grossissement/réduction de la taille des fichiers de transaction log (sur les 2 noeuds)?
    - J'ai lancé un gros batch sur la primaire et le log ne se réduit pas dans le temps malgrès les plans de maintenance backups etc. Pourtant ce n'est pas en production actuellement et personne ne se connecte.
    Pour cela il faut que tu regardes ce qui t'empêche de réduire ton journal (sys.databases et colonne log_reuse_wait_desc) mais vu de loin je pense que tu es bloqué par la synchronisation. A confirmer

    ++

  4. #4
    Expert éminent sénior
    Avatar de mikedavem
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2005
    Messages
    5 450
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Distribution

    Informations forums :
    Inscription : Août 2005
    Messages : 5 450
    Points : 12 891
    Points
    12 891
    Par défaut
    Citation Envoyé par AlternantOracle Voir le message
    un petit up concernant le transactionlog.

    Je peux voir ca:
    SELECT [log_reuse_wait_desc]
    FROM [master].[sys].[databases]

    log_reuse_wait_desc
    NOTHING
    NOTHING
    NOTHING
    NOTHING
    NOTHING
    NOTHING
    ACTIVE_TRANSACTION
    LOG_BACKUP
    NOTHING

    J'ai beau faire autant de backup que log que je peux ca ne change strictement rien.

    ok et que donne un

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    DBCC SQLPERF('logspace')
    ++

  5. #5
    Membre régulier
    Profil pro
    Inscrit en
    Août 2009
    Messages
    146
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2009
    Messages : 146
    Points : 100
    Points
    100
    Par défaut
    Hello Mike,

    Merci pour tes retours que je viens de voir.

    Du coup je suis arrivé à réduire le journal de transaction à coups de backups log + checkpoint en utilisant la vue log_reuse.

    Merci pour tes réponses .

    Cordialement.

  6. #6
    Membre régulier
    Profil pro
    Inscrit en
    Août 2009
    Messages
    146
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2009
    Messages : 146
    Points : 100
    Points
    100
    Par défaut
    Citation Envoyé par mikedavem Voir le message
    Ta stratégie n'est pas différente d'une instance standalone et dépend uniquement de ton business. Il faut traduire ensuite ton besoin en planification de sauvegarde vers AlwaysOn.
    Sur le secondaire il faut cependant faire avec les limitations:
    - Backup full uniquement en copy only (ce qui implique aussi qu'une stratégie à base de backup différentiel sera impossible)
    - backup diff non supporté
    - backup log possible
    Hello Mikedavem,

    Pour information si tu fais aucun backup sur le primaire ça va te faire grossir indéfiniment la BDD. J'ai eu confirmation du support.

    BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups when it is executed on secondary replicas. Note that copy-only backups do not impact the log chain or clear the differential bitmap.

  7. #7
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 716
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 716
    Points : 52 380
    Points
    52 380
    Billets dans le blog
    4
    Par défaut
    Citation Envoyé par AlternantOracle Voir le message
    Maintenance plan:
    - mettre en place les rebuilds/reorganize sur le primaire affecte aussi le secondaire et vice versa (je penses que je rêve pour le vice versa :p) ?
    Un plan de maintenance qui modifie les données ou index ne peut être réalisé que sur le primaire. En revanche DBCC CHECK... peut être fait sur le secondaire.

    - Faire mes DBCC CHECKDB sur la secondaire est une bonne politique afin de détecter la corruption ? (malgrès le système de récupération de blocs always-on)
    Ne confondez pas bloc et page. Nous ne sommes pas sous oracle.... Une page SQL Server est toujours de 8 Ko et organisées dans une extensions qui est un bloc de 8 pages contiguës sur le disque, soit 64 Ko

    logfile growth:
    - Comment se gère le processus de grossissement/réduction de la taille des fichiers de transaction log (sur les 2 noeuds)?
    Contrairement à Oracle, SQL Server transactionne, y compris le DDL. Un agrandissement de fichier est juste une commande SQL interne journalisée et à ce titre répercutée dans les processus de mirroring sur lequel se base AlwaysOn (lecture des binaires du journal de transaction). tout et
    - J'ai lancé un gros batch sur la primaire et le log ne se réduit pas dans le temps malgrès les plans de maintenance backups etc. Pourtant ce n'est pas en production actuellement et personne ne se connecte.
    Le JT ne se réduit jamais naturellement. Il ne fait que grossir. Pour le réduire il faut effectivement faire appel à DBCC SHRINK... qui doit rester une opération tout à fait exceptionnelle.

    Merci d'avance,
    Pour votre retour d'expérience.

    Cordialement.
    PS : j'ai monté un cours SQL Server admin de 3 jours pour les DBA Oracle à Orsys si cela vous intéresse :
    http://www.orsys.fr/formation-admini...sql-server.asp

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  8. #8
    Expert éminent sénior
    Avatar de mikedavem
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2005
    Messages
    5 450
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Distribution

    Informations forums :
    Inscription : Août 2005
    Messages : 5 450
    Points : 12 891
    Points
    12 891
    Par défaut
    @AlternantOracle Désolé de revenir sur ce thread tardivement (et clôturé)

    Hello Mikedavem,

    Pour information si tu fais aucun backup sur le primaire ça va te faire grossir indéfiniment la BDD. J'ai eu confirmation du support.

    Si on parle bien de la même chose, à savoir des journaux de transaction qui grossissent indéfiniment avec une stratégie de sauvegardes de journal depuis le(s) secondaires qui ne videraient pas le journal, alors j'aurais quelques exemples clients ou démos simples qui prouveraient le contraire. La documentation Microsoft est également clair sur le sujet:

    Additionally, the log backup chain is supported across all replicas regardless of where the log backup is taken (primary or secondary replicas) or the mode of the replication (asynchronous or synchronous).

    Mais bon parle-t-on bien de la même chose ici? Si tu as un peu de temps pour répondre ca sera volontiers que je lirais ta réponse :-)

    ++

  9. #9
    Membre régulier
    Profil pro
    Inscrit en
    Août 2009
    Messages
    146
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2009
    Messages : 146
    Points : 100
    Points
    100
    Par défaut
    Bonjour,

    Merci a vous 2 pour les réponses (les choses ont beaucoup évoluées depuis mon tout premier post ).
    Ici bien entendu que le backup secondaire préserve la chaine LSN mais ce que je voulais dire c'est qu'en aucun cas il truncate le transactionlog.

    Du coup on se retrouve avec des BDD qui grossissent sans faire de truncate de mon point de vue.
    Je n'ai pas pris le temps de faire des tests afin de vérifier le comportement.

    ps: j'utilise les scripts OLA pour faire mes backups (peut être que ça a une mauvaise influence sur mes résultats)

  10. #10
    Expert éminent sénior
    Avatar de mikedavem
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2005
    Messages
    5 450
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Distribution

    Informations forums :
    Inscription : Août 2005
    Messages : 5 450
    Points : 12 891
    Points
    12 891
    Par défaut
    Ici bien entendu que le backup secondaire préserve la chaine LSN mais ce que je voulais dire c'est qu'en aucun cas il truncate le transactionlog.
    On est en ligne avec cela

    Du coup on se retrouve avec des BDD qui grossissent sans faire de truncate de mon point de vue.
    La nous ne sommes plus en phase Les backups de journal depuis les secondaires vont initier un truncate également sur le primaire. En réalité les réplicas AlwaysOn fonctionnent par message / tâche et lorsqu'un backup est correctement effectué depuis le secondaire, un message est envoyé au primaire pour initier un truncate du journal.

    Je viens de refaire un test sur un des mes environnements et cela fonctionne comme prévu. Le message qui est visiblement utilisé pour synchroniser le truncate des journaux est NewLogReady (en mettant une petite trace XE)

    ps: j'utilise les scripts OLA pour faire mes backups (peut être que ça a une mauvaise influence sur mes résultats)
    J'ai quelques clients qui l'utilisent également avec des infrastructures AlwaysOn et backup depuis les secondaires et nous n'avons pas eu ce souci à ce jour. (ce qui ne veut pas dire qu'il n'existe pas de cas où cela pourrait arriver et que nous n'avons pas encore rencontré )
    A froid, je ne vois qu'un cas où cela pourrait arriver avec un backup log with copy_only mais ce type de backup n'est pas autorisé depuis un secondaire.

    Si tu as plus de précisions sur ton contexte et que tu as le temps je suis preneur ..

    ++

  11. #11
    Membre régulier
    Profil pro
    Inscrit en
    Août 2009
    Messages
    146
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2009
    Messages : 146
    Points : 100
    Points
    100
    Par défaut
    Hello Mike,

    Merci pour ton temps et tes réponses .
    Du coup je vais aller reproduire cela avec des traces extended events.

    Tu mets quels filtres pour avoir ces résultats ? Désolé d'abuser
    Sur ce je vais aller lire ta présentation extended events.

    Cordialement,
    Jonathan.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [2008R2] Maintenance backup restore
    Par castorcharly dans le forum Administration
    Réponses: 2
    Dernier message: 09/04/2013, 18h29
  2. Plan de maintenance Backup plus long qu'une action manuelle
    Par Glouferu dans le forum MS SQL Server
    Réponses: 21
    Dernier message: 06/12/2011, 21h35
  3. Backup Plan de maintenance
    Par rar77 dans le forum MS SQL Server
    Réponses: 2
    Dernier message: 23/08/2008, 01h42
  4. Sql Server 05 - Maintenance et backup automatique
    Par Monkey_D.Luffy dans le forum MS SQL Server
    Réponses: 8
    Dernier message: 15/07/2008, 12h26
  5. Créer backup avec plan de maintenance
    Par liliprog dans le forum Administration
    Réponses: 4
    Dernier message: 07/07/2006, 12h40

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo