Cet article vous donne une analyse détaillée du processus de l'agent d'instantané SQL Server 2016 et un exemple de ce à quoi il faut prêter attention. Suivez l'éditeur pour en savoir plus. Nous utiliserons une table de données d'instance de SQL Server 2016 pour vous donner une analyse détaillée des problèmes et des solutions rencontrés dans le processus de l'agent d'instantané, ainsi qu'une explication détaillée du processus de génération d'instantané. Voici le contenu complet :
Présentation
L'agent d'instantané prépare le schéma et les fichiers de données initiaux des tables publiées et d'autres objets, stocke les fichiers d'instantané et enregistre les informations de synchronisation dans la base de données de distribution. L'agent d'instantané s'exécute sur le serveur de distribution ; la version SQL Server 2016 a apporté de meilleures optimisations pour l'agent d'instantané. Apprenons ensuite plus sur le processus d'exécution d'instantané.
1. Fichier proxy d'instantané
Lors de l'exécution d'un travail d'instantané, 4 types de fichiers seront générés dans le répertoire d'instantané spécifié.
Fichier BCP : le fichier de données de l'objet de publication.
Fichier IDX : fichier de script de création d'index
Fichier PRE : copie du fichier de script d'instantané.
Fichier SCH : Fichier de script de création d'architecture
2. Fichier de configuration de l'agent d'instantané par défaut
-BcpBachSize : Le nombre maximum de lignes d'enregistrement pour chaque copie d'opération bcp, la valeur par défaut est de 100 000 lignes.
-HistoryVerboseLevel : Spécifie la taille de l'historique enregistré lors de l'opération d'instantané.
-LoginTimeout : Le nombre de secondes à attendre avant l'expiration de la connexion. La valeur par défaut est de 15 secondes.
-QueryTimeOut : Nombre de secondes à attendre avant l'expiration du délai de requête. La valeur par défaut est de 1 800 secondes
Remarque : Vous pouvez configurer l'agent d'instantané en cliquant avec le bouton droit sur le fichier de configuration de l'agent d'instantané-agent d'instantané.
3. Comparez différentes versions d'agents d'instantanés
L'étape suivante consiste à tester et à comparer 200 millions de tables d'enregistrement pour générer des instantanés
1. Comparaison du nombre de dossiers bcp
2008R2
2016SP1
Ici, nous nous concentrons sur les fichiers BCP, car l'application d'instantanés à l'abonné utilise les fichiers BCP comme unité de base, ce qui signifie que quelle que soit la taille de votre fichier BCP, il sera envoyé en masse à l'abonné en même temps, donc plus le BCP est grand. fichier, plus chaque application prendra du temps. Si un fichier BCP est trop volumineux, l'insertion dans l'abonné peut échouer.
Comme vous pouvez le voir sur l'image ci-dessus, il y a également 200 millions d'enregistrements. 2008R2 contient un total de 8 fichiers BCP, et la plus grande taille de fichier BCP est de près de 1 Go. Les autres ne font que quelques mégaoctets ; contient 16 fichiers BCP et les 15 premiers contiennent tous environ 50 millions de données, ce qui est relativement égal. Ensuite, regardez la comparaison des enregistrements de chaque fichier BCP dans la figure ci-dessous.
2. Comparaison du processus détaillé de génération d'instantanés
2008r2
2016SP1
De la comparaison des enregistrements du fichier BCP générés :
2008R2 : Les 7 premiers fichiers contiennent environ 700 000 enregistrements dans chaque fichier et le dernier fichier en enregistre 110 millions. .
2016 : Les 15 premiers fichiers ont enregistré environ 7 millions chacun, et le dernier fichier en a enregistré 780 000.
Explication :
Le nombre approximatif d'enregistrements stockés dans chacun des 7 premiers fichiers de 2008R2 est de 700 000. Les enregistrements restants seront stockés dans le dernier fichier, donc le nombre d'enregistrements de table qui. est plus approprié pour 2008R2 est d'environ 6 millions.
Le nombre approximatif d'enregistrements stockés dans chacun des 15 premiers fichiers en 2016 est de 7 millions. Les enregistrements restants seront stockés dans le dernier fichier. Le nombre d'enregistrements de table appropriés en 2016 est d'environ 120 millions.
Inconvénient courant : une fois que les enregistrements de la table dépassent le "nombre approprié d'enregistrements de table répliqués", toutes les données restantes seront stockées dans le dernier fichier bcp.
3. Comparaison de distribution
Jetons un coup d'œil au processus détaillé de distribution
Vous pouvez voir à chaque fois à partir de la distribution 2008R2 le processus d'enregistrement BULK est basé sur des fichiers bcp. Il a fallu environ 22 minutes pour copier le dernier fichier bcp, et chacun des fichiers précédents a pris plus de dix secondes ou parce que ma table actuelle n'a que trois champs et aucun index à l'exception de la clé primaire ; Sinon, le temps sera juste plus long.
4. Processus de génération d'instantanés
L'agent d'instantané de réplication est un fichier exécutable utilisé pour préparer des fichiers d'instantanés (qui contiennent des tables publiées et un objet de base de données schéma et données), puis stocke ces fichiers dans le dossier d'instantanés et enregistre le travail de synchronisation dans la base de données de distribution.
Vous pouvez comprendre l'ensemble du processus de génération d'instantanés à partir de l'image ci-dessus.
5. Syntaxe
snapshot [ -?]
-Publisher server_name[\instance_name]
-Publication publication_name
[-70Subscribers]
[-BcpBatchSize bcp_batch_size]
[-DefinitionFile def_path_and_file_name]
[-Distributor server_name[\instance_name]]
[-DistributorDeadlockPriority [-1|0|1] ]
[-DistributorLogin distributor_login]
[-DistributorPassword distributor_password]
[-DistributorSecurityMode [0|1] ]
[-DynamicFilterHostName dynamic_filter_host_name]
[-DynamicFilterLogin dynamic_filter_login]
[-DynamicSnapshotLocation dynamic_snapshot_location]
[-EncryptionLevel [0|1|2]]
[-FieldDelimiter field_delimiter]
[-HistoryVerboseLevel [0|1|2|3] ]
[-HRBcpBlocks number_of_blocks ]
[-HRBcpBlockSize block_size ]
[-HRBcpDynamicBlocks ]
[-KeepAliveMessageInterval keep_alive_interval]
[-LoginTimeOut login_time_out_seconds]
[-MaxBcpThreads number_of_threads ]
[-MaxNetworkOptimization [0|1]]
[-Output output_path_and_file_name]
[-OutputVerboseLevel [0|1|2] ]
[-PacketSize packet_size]
[-ProfileName profile_name]
[-PublisherDB publisher_database]
[-PublisherDeadlockPriority [-1|0|1] ]
[-PublisherFailoverPartner server_name[\instance_name] ]
[-PublisherLogin publisher_login]
[-PublisherPassword publisher_password]
[-PublisherSecurityMode [0|1] ]
[-QueryTimeOut query_time_out_seconds]
[-ReplicationType [1|2] ]
[-RowDelimiter row_delimiter]
[-StartQueueTimeout start_queue_timeout_seconds]
[-UsePerArticleContentsView use_per_article_contents_view]
Copier après la connexion
Paramètres
-?
Sortie de tous les paramètres disponibles.
-Publisher server_name[instance_name]
Le nom du serveur de publication. Spécifiez server_name pour l'instance par défaut de Microsoft SQL Server sur ce serveur. Spécifiez server_name pour l'instance par défaut server_nameinstance_name instance_name de SQL Server sur ce serveur.
-Publication Publication
Le nom de la publication. Ce paramètre n'est efficace que si la publication est configurée pour toujours rendre les instantanés disponibles pour les abonnements nouveaux ou réinitialisés.
-70Subscribers
Ce paramètre est requis si des abonnés exécutent SQL Server version 7.0.
-BcpBatchSize bcp batch size
Le nombre de lignes envoyées dans une opération de copie groupée. Lors de l'exécution d'un bcp en fonctionnement, la taille du lot est le nombre de lignes à envoyer au serveur en une seule transaction, et est le nombre de lignes qui doivent être envoyées avant que l'agent de distribution n'enregistre un message de progression bcp. Lors de l'exécution d'une opération de sortie bcp, une taille de lot fixe de 1 000 sera utilisée. Une valeur de 0 signifie qu'aucun message n'est enregistré.
-DefinitionFile def_path_and_file_name
Le chemin d'accès au fichier de définition de l'agent. Le fichier de définition de l'agent contient les paramètres de ligne de commande de l'agent. Le contenu du fichier est analysé comme un fichier exécutable. Utilisez des guillemets doubles (") pour spécifier les valeurs de paramètre contenant des caractères.
-Distributeur nom_serveur[nom_instance]
Distributeur name . Spécifiez server_name pour l'instance par défaut de SQL Server sur ce serveur 🎜>-DistributorDeadlockPriority [-1|0|1]La priorité de l'agent d'instantané se connectant au serveur de distribution. lorsqu'un blocage se produit. Ce paramètre est spécifié pour résoudre le problème entre l'agent d'instantané et l'application utilisateur lors de la génération d'un instantané. Problème de blocage qui se produit entre les programmes >-1
En cas de blocage sur. le serveur de distribution, l'application a priorité sur l'agent d'instantané
0 (par défaut)
Aucune priorité attribuée .
1
Lorsqu'un blocage se produit. sur le serveur de distribution, l'agent d'instantané est prioritaire. >Le nom de connexion utilisé lors de la connexion au serveur de distribution à l'aide de l'authentification SQL Server
-DistributorPassword
distributor_password
Lors de la connexion au serveur de distribution à l'aide de l'authentification SQL Server. Le mot de passe à utiliser
-DistributorSecurityMode [0|1]
Spécifie le mode de sécurité du serveur de distribution. le mode d'authentification SQL Server (le mode d'authentification Windows par défaut
-DynamicFilterHostName
dynamic_filter_host_name
Utilisé pour définir la valeur de HOST_NAME (Transact-SQL). dans le filtre lors de la création d'un instantané dynamique. Par exemple, si la clause de filtre de sous-ensemble rep_id = HOST_NAME() est spécifiée pour un projet et que la propriété DynamicFilterHostName est définie sur « FBJones » avant d'appeler l'agent de fusion, seules les lignes avec « FBJones » dans la colonne rep_id sera copié.
-DynamicFilterLogin
dynamic_filter_login
est utilisé pour définir la valeur de SUSER_SNAME (Transact-SQL) dans le filtrage lors de la création d'un instantané dynamique. Par exemple, si vous spécifiez la clause de filtre de sous-ensemble user_id = SUSER_SNAME() pour un projet et que vous définissez la propriété DynamicFilterLogin sur "rsmith" avant d'appeler la méthode Run de l'objet SQLSnapshot, seules les lignes avec "rsmith" dans la colonne user_id sont incluses dans l'instantané.
-DynamicSnapshotLocation
dynamic_snapshot_location
L'emplacement où les instantanés dynamiques doivent être générés.
-EncryptionLevel [ 0 | 1 | 2 ]
Le niveau de cryptage Secure Sockets Layer (SSL) utilisé par l'agent d'instantané lors de l'établissement d'une connexion.
Valeur EncryptionLevel
Description
0
Spécifie de ne pas utiliser SSL.
1
Spécifie d'utiliser SSL, mais le proxy ne vérifie pas que le certificat du serveur SSL est signé par un émetteur de confiance.
2
Spécifiez l'utilisation de SSL et vérifiez le certificat.
-FieldDelimiter field_delimiter
Un caractère ou une séquence de caractères utilisé pour marquer la fin d'un champ dans les fichiers de données de copie groupée SQL Server. La valeur par défaut est nn.
-HistoryVerboseLevel [ 1| 2| 3]
Spécifie la taille de l'historique enregistré lors de l'opération d'instantané. Choisissez 1 pour minimiser l’impact sur les performances de la journalisation historique.
Valeur HistoryVerboseLevel
Description
0
Les messages de progression seront écrits dans la console ou dans le fichier de sortie. N'enregistre pas l'historique dans la base de données de distribution.
1
Mette toujours à jour le message d'historique précédent avec le même statut (démarré, en cours, réussi, etc.). S'il n'existe aucun enregistrement précédent avec le même statut, un nouvel enregistrement sera inséré.
2 (par défaut)
Insère un nouvel enregistrement d'historique sauf si l'enregistrement est un message inactif ou un message de travail de longue durée, auquel cas l'enregistrement précédent sera mis à jour.
3
Insérez toujours un nouvel enregistrement sauf s'il est lié à un message inactif.
-HRBcpBlocks number_of_blocks
Le nombre de blocs de données bcp mis en file d'attente entre le thread d'écriture et le thread de lecteur. La valeur par défaut est 50. Les HRBcpBlocks ne sont utilisés que dans les versions Oracle.
Remarques
Ce paramètre permet d'optimiser les performances de bcp via Oracle Publisher.
-HRBcpBlockSizeblock_size
La taille de chaque bloc de données bcp en Ko. La valeur par défaut est 64 Ko. Les HRBcpBlocks ne sont utilisés que dans les versions Oracle.
Remarques
Ce paramètre permet d'optimiser les performances de bcp via Oracle Publisher.
-HRBcpDynamicBlocks
Si la taille de chaque bloc de données bcp peut croître de manière dynamique. Les HRBcpBlocks ne sont utilisés que dans les versions Oracle.
Remarques
Ce paramètre permet d'optimiser les performances de bcp via Oracle Publisher.
-KeepAliveMessageInterval keep_alive_interval
Le temps en secondes pendant lequel l'agent d'instantané attend avant d'enregistrer un "message d'attente du backend" dans la table MSsnapshot_history. La valeur par défaut est de 300 secondes.
-LoginTimeOut login_time_out_seconds
Le nombre de secondes à attendre avant l'expiration de la connexion. La valeur par défaut est de 15 secondes.
-MaxBcpThreads number_of_threads
Spécifie le nombre d'opérations de copie en bloc qui peuvent être exécutées en parallèle. Le nombre maximum de threads simultanés et de connexions ODBC est le plus petit entre MaxBcpThreads ou le nombre de demandes de copie en masse affichées dans les transactions de synchronisation dans la base de données de distribution. La valeur de MaxBcpThreads doit être supérieure à 0 et il n’y a pas de limite supérieure codée en dur. La valeur par défaut est 1.
- MaxNetworkOptimization [ 0| 1]
S'il faut envoyer des opérations de suppression non pertinentes à l'abonné. Une opération de suppression sans rapport est une commande DELETE envoyée à l'Abonné pour les lignes qui n'appartiennent pas à la partition de l'Abonné. Les opérations de suppression non pertinentes n'affectent pas l'intégrité ou la convergence des données, mais elles peuvent générer un trafic réseau inutile. La valeur par défaut de MaxNetworkOptimization est 0. La définition de MaxNetworkOptimization sur 1 minimise le risque d’opérations de suppression non liées, réduisant ainsi le trafic réseau et maximisant l’optimisation du réseau. S'il existe plusieurs niveaux de filtres de jointure et de filtres de sous-ensembles complexes, la définition de ce paramètre sur 1 peut également augmenter le stockage des métadonnées et entraîner une diminution des performances de Publisher. Vous devez soigneusement évaluer votre topologie de réplication et définir MaxNetworkOptimization sur 1 uniquement lorsque le trafic réseau provoqué par des opérations de suppression non liées est inacceptablement élevé.
Notes
La définition de ce paramètre sur 1 n'est utile que lorsque l'option d'optimisation de synchronisation pour les publications de fusion (paramètre (@keep_partition_changes de sp_addmergepublication (Transact-SQL)) est définie sur true.
-Output output_path_and_file_name
Le chemin d'accès au fichier de sortie proxy. Si aucun nom de fichier n'est fourni, cette sortie est envoyée à la console. Si le nom de fichier spécifié existe déjà, la sortie sera ajoutée au fichier.
-OutputVerboseLevel [ 0| 1| 2]
Spécifie si la sortie doit fournir un contenu détaillé.
Valeur OutputVerboseLevel
Description
0
Émet uniquement les messages d'erreur.
1 (par défaut)
Émettre tous les messages de rapport de progression (par défaut).
2
Émet tous les messages d'erreur et les messages de rapport de progression, ce qui est utile pour le débogage.
-PacketSize packet_size
La taille du paquet en octets utilisée par Snapshot Agent lors de la connexion à SQL Server. La valeur par défaut est de 8 192 octets.
Remarques
Ne modifiez pas la taille du paquet sauf si vous êtes sûr que cela améliorera les performances. Pour la plupart des applications, la taille de paquet par défaut constitue la valeur optimale.
-ProfileName profile_name
Spécifie le profil proxy utilisé pour les paramètres de proxy. Si ProfileName est NULL, le profil d'agent sera désactivé. Si ProfileName n'est pas spécifié, le profil par défaut pour ce type d'agent est utilisé.
-PublisherDB publisher_database
Le nom de la base de données de publication. Oracle Publisher ne prend pas en charge ce paramètre.
-PublisherDeadlockPriority [-1|0|1]
La priorité pour que l'agent d'instantané se connecte à l'éditeur lorsqu'un blocage se produit. Ce paramètre est spécifié pour résoudre un problème de blocage qui se produit entre l'agent d'instantané et l'application utilisateur lors de la génération d'instantané.
Valeur PublisherDeadlockPriority
Description
-1
En cas de blocage sur le Publisher, c'est l'application qui prime et non le Snapshot Agent.
0 (par défaut)
Aucune priorité attribuée.
1
Lorsqu'un blocage survient sur l'éditeur, l'agent d'instantané est prioritaire.
-PublisherFailoverPartner server_name[instance_name]
Spécifie l'instance du partenaire de basculement SQL Server qui participe à une session de mise en miroir de base de données avec la publication base de données .
-PublisherLogin publisher_login
Le nom de connexion à utiliser lors de la connexion à l'éditeur à l'aide de l'authentification SQL Server.
-PublisherPassword publisher_password
Le mot de passe à utiliser lors de la connexion à l'éditeur à l'aide de l'authentification SQL Server. .
-PublisherSecurityMode [ 0| 1]
Spécifie le mode de sécurité de l'éditeur. Une valeur de 0 indique l'authentification SQL Server (valeur par défaut) et une valeur de 1 indique le mode d'authentification Windows.
-QueryTimeOut query_time_out_seconds
Le nombre de secondes à attendre avant l'expiration de la requête. La valeur par défaut est de 1 800 secondes.
-ReplicationType [ 1| 2]
Spécifie le type de réplication. Une valeur de 1 indique une réplication transactionnelle et une valeur de 2 indique une réplication de fusion.
-RowDelimiter row_delimiter
Caractère ou séquence de caractères utilisé pour marquer la fin d'une ligne dans les fichiers de données de copie groupée SQL Server. La valeur par défaut est n<,@g>n.
-StartQueueTimeout start_queue_timeout_seconds
Lorsque le nombre de processus d'instantanés dynamiques simultanés en cours d'exécution atteint la limite définie par l'attribut @max_concurrent_dynamic_snapshots de sp_addmergepublication (Transact-SQL), Nombre maximum de secondes pendant lesquelles l'agent d'instantané attend. Si Snapshot Agent attend toujours après le nombre maximum de secondes, Snapshot Agent se fermera. Une valeur de 0 signifie que l'agent attendra indéfiniment, même s'il peut être annulé.
- UsePerArticleContentsView use_per_article_contents_view
Ce paramètre est obsolète et est pris en charge pour des raisons de compatibilité ascendante.
Intégrité des données de la base de données du serveur SQL
Contraintes du serveur SQL
Résumé de l'utilisation des fonctions couramment utilisées dans la méthode SQL Server
Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!
![[Web front-end] Démarrage rapide de Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
