L’Agent SQL Server ne fait pas partie de l’AG en Faite Les groupes de disponibilité AlwaysOn synchronisent les bases de données qui font partie d’un groupe de disponibilité, mais tout autre objet qui ne fait pas partie de ces bases de données n’est pas synchronisé (c’est-à-dire les connexions, les serveurs liés, les opérateurs, etc.) et cela inclut les travaux de l’Agent SQL Server.
Pour accomplir ce mode de création de travail d’agent SQL, j’utilise cette fonction pour tester si une base de données participe à un AG est accessible en écriture. Si c’est le cas, passez au reste des étapes du travail de l’agent SQL. Si ce n’est pas le cas, stopper le job
-- fn_hadr_group_is_primary
USE master;
GO
IF OBJECT_ID('dbo.fn_hadr_group_is_primary', 'FN') IS NOT NULL
DROP FUNCTION dbo.fn_hadr_group_is_primary;
GO
CREATE FUNCTION dbo.fn_hadr_group_is_primary (@AGName sysname)
RETURNS bit
AS
BEGIN;
DECLARE @PrimaryReplica sysname;
SELECT
@PrimaryReplica = hags.primary_replica
FROM sys.dm_hadr_availability_group_states hags
INNER JOIN sys.availability_groups ag ON ag.group_id = hags.group_id
WHERE ag.name = @AGName;
IF UPPER(@PrimaryReplica) = UPPER(@@SERVERNAME)
RETURN 1; -- primary
RETURN 0; -- not primary
END;
Utilisez la fonction fn_hadr_group_is_primary dans une nouvelle étape de travail pour déterminer si cette instance sql est un réplica principal. S’il ne s’agit pas d’un réplica principal, nous émettons une demande d’arrêt de la tâche tout en identifiant le nom de la tâche actuelle à l’aide des jetons de l’agent SQL Server.
-- Detect if this instance's role is a Primary Replica.
-- If this instance's role is NOT a Primary Replica stop the job so that it does not go on to the next job step
DECLARE @rc int;
EXEC @rc = master.dbo.fn_hadr_group_is_primary N'my-ag';
IF @rc = 0
BEGIN;
DECLARE @name sysname;
SELECT @name = (SELECT name FROM msdb.dbo.sysjobs WHERE job_id = CONVERT(uniqueidentifier, '$(ESCAPE_NONE(JOBID))'));
EXEC msdb.dbo.sp_stop_job @job_name = @name;
PRINT 'Stopped the job since this is not a Primary Replica';
END;
En termes simples, un batch est une structure de stockage interne de 64 Ko de large qui contient un groupe de lignes allant de 64 à 900 selon le nombre de colonnes qui les composent. Chaque colonne utilisée par la requête est stockée dans un vecteur de colonne continu d’éléments de taille fixe, où le vecteur de ligne qualificatif indique quelles lignes font encore logiquement partie du lot
Pour commencer je vais exécuter exécutez mon code TSQL où nous gardons le niveau de compatibilité de la base de données à 140 qui représente SQL Server 2017
Vérifiez le plan d’exécution de la requête
Remarquez bien que comment le mode d’exécution est ROW
CPU time = 142656 ms, elapsed time = 43660ms.
Maintenant, nous prenons la même base de données avec la même requête et on active le mode mode batch en définissant le niveau de compatibilité de la base de données sur « 150 »
Ici, nous voyons que, comme prévu, le stockage est « RowStore », mais le mode d’exécution réel est désormais « Batch ». Et les résultats reviennent en un peu moins de 1/3 du temps de la même requête sur la même table en mode ligne
CPU time = 142547 ms, elapsed time = 24702ms.
Pour obtenir le mode batch, il vous suffit d'exécuter vos requêtes avec le dernier niveau de compatibilité sur SQL Server 2019 en Edition entreprise sur des larges tables
L’application de correctifs à SQL Server peut parfois prendre du temps, en particulier lorsque plusieurs serveurs doivent être corrigés. La connexion à distance à chaque boîte pour exécuter l’assistant de mise à jour est fastidieuse, et si vous avez plusieurs correctifs à appliquer, vous allez y passer beaucoup de temps.
Heureusement, le module dbatool Powershell rend ce processus beaucoup plus facile! En fait, une fois que vous l’avez compris, utiliser Update-DbaInstance pour patcher vos serveurs SQL devient très simple. Mais, comprendre cela peut être un peu déroutant,
Les plus gros problèmes que j’ai rencontrés ont été la configuration d’un emplacement central de mise à jour et l’utilisation d’un identifiant pour y accéder.
Voici le code PowerShell pour ceux qui aimeraient jouer à la maison.
Et voilà, toutes les mises à jour étaient en même temps, avec des cases dans PowerShell ISE me montrant le statut de chacune. C’était magique.
Pour ceux d’entre vous qui connaissent encore moins PowerShell que votre humble hôte, laissez-moi vous expliquer ce qui se passe.
Les choses avec les signes $ sont des variables. Je déclare simplement ce qu’ils sont. Ils ne sont pas obligatoires – vous pouvez les transmettre en tant que valeurs littérales à la commande à la fin du script. Je l’ai juste fait pour la lisibilité.
Get-Credential est une applet de commande qui crée un objet d’informations d’identification pour un nom d’utilisateur et un mot de passe spécifiques. Ce que cela signifie pour vous, c’est que lors de son exécution, vous recevrez une invite vous permettant de saisir un identifiant et un mot de passe pouvant être utilisés pour exécuter les mises à jour cumulatives sur tous les serveurs. C’est bien car vous ne devez saisir cette information qu’une seule fois, pas une fois pour chaque serveur.
ComputerName est explicite, mais encore une fois, vous pouvez lui transmettre PLUSIEURS valeurs. C’est la clé de la vitesse.
Redémarrer indique de redémarrer le serveur une fois la mise à jour cumulative appliquée. C’est vrai – la durée de 12 minutes comprenait les redémarrages!
La version est une sorte de code transmis à UpdateDbaInstance. Il peut également inclure des service packs (c’est-à-dire «2019RTM») mais 2019 n’a pas de service packs, vous utilisez donc «RTM» dans cet espace.
Path est le répertoire dans lequel vous avez le fichier .exe que vous avez téléchargé à partir de Microsoft pour appliquer la mise à jour cumulative. Placez-le quelque part où vous pouvez y accéder et NE le renommez PAS. Si vous faites cela ne fonctionnera pas car UpdateDbaInstance appelle une autre cmdlet qui sait ce que ce nom de fichier doit être basé sur ce que vous avez transmis pour Version.
Credential utilise tout ce qui a été saisi pour Get-Credential. Si vous avez noté cette information, rien de tout cela ne fonctionnera. Cela pourrait être votre point de défaillance unique.
Confirmer est défini sur false, car je ne souhaite pas cliquer sur « Êtes-vous sûr? » type d’invite. Vivez dangereusement, mes amis.
Si vous avez toujours des appréhensions à propos de tout cela, vous pouvez d’abord exécuter tout cela avec -WhatIf ajouté à la fin de la ligne Update-DbaInstance. Cela peut aider à identifier au préalable les erreurs telles que les problèmes de connectivité sans rien faire.
Et voila Finalement le kb4552255 CU5 a été applique a toutes mes instances dans quelques minu
ce script de surveillance PowerShell collectera des données au niveau de l’instance pour tous les serveurs SQL qui ont été enregistrés pour cette solution de surveillance.
Solution
Ce module rassemblera des informations sur l’instance SQL Server, telles RAM ,PLE,cache Hit ration ,CPU l. Il se connectera à chaque serveur de LA TABLE Serveurs et capturera les données pour chacune de ces instances. La connexion à chaque instance SQL Server sera basée sur les paramètres de la table
Ce module rassemblera des informations sur l’instance SQL Server, telles RAM ,PLE,cache Hit ration ,CPU l. Il se connectera à chaque serveur de LA TABLE Serveurs et capturera les données pour chacune de ces instances. La connexion à chaque instance SQL Server sera basée sur les paramètres de la table Serveurs.
Le script PowerShell qui crée les objets ci-dessus et insère des données dans la table nomé CPU_Memory_Usage
Import-Module SqlServer
$server = "COSxxxxxxx"
$inventoryDB = "DBA_TOOLS"
This is the definition of the table that will contain the values for each instance you wish to collect information from
$resourcesUsageTable = "
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'CPU_Memory_Usage' AND xtype = 'U')
CREATE TABLE CPU_Memory_Usage(
[server]varchar(10) NOT NULL,
[max_server_memory] [int] NOT NULL,
[sql_memory_usage] [int] NOT NULL,
[physical_memory] [int] NOT NULL,
[available_memory] [int] NOT NULL,
[system_memory_state]varchar(30) NOT NULL,
[page_life_expectancy] [int] NOT NULL,
[cache hit ratio] NUMERIC(10,6) NOT NULL,
[cpu_usage_30] [int] NOT NULL,
[cpu_usage_15] [int] NOT NULL,
[cpu_usage_10] [int] NOT NULL,
[cpu_usage_5] [int] NOT NULL,
[data_sample_timestamp] [datetime] NULL
) ON [PRIMARY]
"
Make sure you create this table in your central environment, where you wish to gather the information from all the desired instances
$instances = Invoke-Sqlcmd -ServerInstance $server -Database $inventoryDB -Query $resourcesUsageTable
/* Récupérer List Serveur Pour leur parcours */
$instanceLookupQuery = "SELECT name FROM servers"
$instances = Invoke-Sqlcmd -ServerInstance $server -Database $inventoryDB -Query $instanceLookupQuery
$resourcesQuery = "
WITH SQLProcessCPU
AS(
SELECT TOP(30) SQLProcessUtilization AS 'CPU_Usage', ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS 'row_number'
FROM (
SELECT
record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization],
[timestamp]
FROM (
SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%%'
) AS x
) AS y
)
SELECT
SERVERPROPERTY('SERVERNAME') AS 'Instance',
(SELECT value_in_use FROM sys.configurations WHERE name like '%max server memory%') AS 'Max Server Memory',
(SELECT physical_memory_in_use_kb/1024 FROM sys.dm_os_process_memory) AS 'SQL Server Memory Usage (MB)',
(SELECT total_physical_memory_kb/1024 FROM sys.dm_os_sys_memory) AS 'Physical Memory (MB)',
(SELECT available_physical_memory_kb/1024 FROM sys.dm_os_sys_memory) AS 'Available Memory (MB)',
(SELECT system_memory_state_desc FROM sys.dm_os_sys_memory) AS 'System Memory State',
(SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Manager%' AND [counter_name] = 'Page life expectancy') AS 'Page Life Expectancy',
(SELECT CAST(
(
SELECT CAST (cntr_value AS BIGINT)
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio'
)* 100.00
/
(
SELECT CAST (cntr_value AS BIGINT)
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
) AS NUMERIC(6,3)
))as [cache hit ratio] ,
(SELECT AVG(CPU_Usage) FROM SQLProcessCPU WHERE row_number BETWEEN 1 AND 30) AS 'SQLProcessUtilization30',
(SELECT AVG(CPU_Usage) FROM SQLProcessCPU WHERE row_number BETWEEN 1 AND 15) AS 'SQLProcessUtilization15',
(SELECT AVG(CPU_Usage) FROM SQLProcessCPU WHERE row_number BETWEEN 1 AND 10) AS 'SQLProcessUtilization10',
(SELECT AVG(CPU_Usage) FROM SQLProcessCPU WHERE row_number BETWEEN 1 AND 5) AS 'SQLProcessUtilization5',
GETDATE() AS 'Data Sample Timestamp'
"
Try
{
Parcours Lists serveur par Foreach
foreach ($instance in $instances){
Write-Host "Fetching CPU/RAM information for instance" $instance.name
$results = Invoke-Sqlcmd -Query $resourcesQuery -ServerInstance $instance.name -ErrorAction Stop -querytimeout 30
#Insert statement
if($results.Length -ne 0){
$insert = "INSERT INTO CPU_Memory_Usage VALUES"
foreach($result in $results){
$insert += "
(
'"+$result['Instance']+"',
"+$result['Max Server Memory']+",
"+$result['SQL Server Memory Usage (MB)']+",
"+$result['Physical Memory (MB)']+",
"+$result['Available Memory (MB)']+",
'"+$result['System Memory State']+"',
"+$result['Page Life Expectancy']+",
"+$result['cache hit ratio']+",
"+$result['SQLProcessUtilization30']+",
"+$result['SQLProcessUtilization15']+",
"+$result['SQLProcessUtilization10']+",
"+$result['SQLProcessUtilization5']+",
GETDATE()
),
"
}
#Perform the INSERT in the central table
Invoke-Sqlcmd -Query $insert.Substring(0,$insert.LastIndexOf(',')) -ServerInstance $server -Database $inventoryDB
}
}
Write-Host "Done!"
}
catch
{
write-host "Error de connectivité" $instance.name
}
Si nous interrogeons la table CPU_Memory_Usage , nous pouvons voir les données qui ont été collectées
À l’aide du module PowerShell open source dbatools, je peux déployer la nouvelle version de de la procédure stockée. sp_whoisactive et celle solution https://ola.hallengren.com/ sur plusieurs instances SQL server en quelque minutes
Ci joint le code Powershell
La première ligne récupérera les listes de mes instances. après c’est une petit vérification d’existence de la base cible après il téléchargera la dernière version et compilera la procédure stockée sur la base de données sur chacune des instances de cette liste
Dans cet article, nous allons voir comment modifier la destination d’archivog dans Oracle
Si votre point de montage archivelog actuel est PLEIN ou pour toute autre raison, vous souhaitez modifier la destination archivelog dans votre base de données, vous pouvez suivez les étapes ci-dessous
1 Vérifier que votre base est en mode Archivelog
2. Recherchez l’emplacement actuel du journal d’archivage
archive log list
show parameter archive ;
NAME LOG_MODE
--------- ------------
PRODUIT ARCHIVELOG
SQL> archive log list
mode Database log mode Archive
Archivage automatique ActivÚ
Destination de l'archive USE_DB_RECOVERY_FILE_DEST
SÚquence de journal en ligne la plus ancienne 1
SÚquence de journal suivante Ó archiver 3
SÚquence de journal courante 3
3– créer la nouvelle emplacement d’archive et changer la destination
alter system set log_archive_dest_1='LOCATION=c:\backup\produit\' scope=both;
4-Basculez le fichier redolog et l’archivelog gentrated dans l’emplacement FRA
SQL> alter system switch logfile;
System altered
5-Vérifiez si les archives sont générées au nouvel emplacement:
SQL> select destination,STATUS from v$archive_dest where statuS='VALID';
DESTINATION
--------------------------------------------------------------------------------
STATUS
---------
c:\backup\produit\
VALID
Comment savoir si une base est en mode archivelog ?
sql>select name ,log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
sql>ARCHIVE LOG LIST
mode Database log mode No Archive
Archivage automatique Désactivé
Destination de l'archive USE_DB_RECOVERY_FILE_DEST
Séquence de journal en ligne la plus ancienne 82
Séquence de journal courante 84
En mode NOARCHIVELOG, la base de données s’exécute normalement, mais il est impossible d’effectuer des opérations de récupération ou des sauvegardes en ligne à un moment donné. Ainsi, vous devez arrêter la base de données pour la sauvegarder, et lorsque vous récupérez la base de données, vous ne pouvez la récupérer qu’au moment de la dernière sauvegarde.
Comment Activer le mode ARCHIVELOG :
Il faut tout d’abord se connecter à la DB en administrateur :
sqlplus /nolog
connect /as sysdba
1. Arrêter la base.
shutdown immediate
2. Sauvegarder la base.
Ce n’est pas obligatoire mais vaut toujours mieux avoir un backup avant modification.
4. Démarrer la base en mode mount sans l’ouvrir.
STARTUP MOUNT
5. Modifier le mode d’archivage et ouvrir la base.
Il est souvent nécessaire de compresser et de décompresser les fichiers que nous souhaitons traiter avec (SSIS). Il existe plusieurs outils différents dotés de cette fonctionnalité. Dans cette astuce, je montrerai les étapes à suivre pour compresser et décompresser des fichiers à l’aide de 7-Zip dans le cadre d’un package SQL Server Integration Services
Nous pouvons utiliser n’importe quel extracteur de fichier dans SQL Server Integration Services, mais pour cette démonstration, je vais utiliser 7-Zip pour compresser et décompresser des dossiers et des fichiers.
Après avoir installé le 7-Zip sur mon PC, voici l’emplacement d’installation de 7-Zip
Ceci est l’emplacement où je veux compresser les fichiers.
Maintenant j’ai fait glisser une tâche d’exécution de processus de la boîte à outils SSIS vers le flot de contrôle et l’a renommé » ZIP Data « .
Maintenant, faites un clic droit sur la tâche et choisissez « Modifier … » pour ouvrir l’éditeur de tâche d’exécution, puis cliquez sur « Processus » à gauche et modifiez le chemin de l’exécutable, les arguments et le répertoire de travail comme suit et conservez les autres comme valeurs par défaut. Je l’explique plus bas
Exécutable
Placez le chemin complet de l’exécutable en tant que: C: \ Program Files \ 7-Zip \ 7z.exe*
Arguments
Fournissez les arguments d’invite de commande: a -t7z « C:\unzipfile\file.zip » « C:\ZIPfile*.* »
Je vais archiver les fichiers en utilisant l’ argument de commande « a » .
Le format est spécifié avec le commutateur -t -t7z
Placez tous les fichiers dans le fichier zip « C:\unzipfile\file.zip «
De l’emplacement « C:\ZIPfile «
Exécution du package SSIS dans des fichiers Zip
Le paquet est maintenant configuré pour l’archivage des données.
Selon la configuration, tous les fichiers sont compressés dans BulkFiles sur le lecteur c:\
J’ai ajouterai au package SSIS une autre tâche d’exécution de processus pour extraire les fichiers que nous venons de compresser.
Voici les paramètres pour décompresser les fichiers.
Exécutable
Placez le chemin complet de l’exécutable en tant que: C: \ Program Files \ 7-Zip \ 7z.exe
Je vais extraire les fichiers en utilisant l’ argument de la commande « e »
j’utilise un argument pour gérer le changement dynamique du nom
Exécution du package SSIS pour décompresser des fichiers
Voici le paquet
Si nous regardons le lecteur c: \unzipfile , nous pouvons voir les fichiers extraits.
Après avoir utilisé certains des moyens de supprimer les fichiers du dossier particulier, comme Supprimer le fichier à l’aide de la tâche de script et de la tâche du système de fichiers dans SSIS. Nous avons vu de telles méthodes et utilisé la logique pour extraire les fichiers du conteneur de boucles et les traiter pour les supprimer. La même chose s’applique également aux scripts. Nous avons également recherché les fichiers supprimés, qui sont plus anciens qu’une période de conservation avec une tâche de script.
ici, je voudrais utiliser des paramètres pour le chemin du dossier et la période de conservation, puis passer à travers les scripts et les supprimer conformément à la condition.
vérifier les fichiers du dossier cible.
Créez des paramètres et définissez les valeurs. Dans ce cas, supprimez les fichiers de plus de 3 jours du dossier C:\source
3. Faites glisser et déposez la Tâche de script
4. Mettez les paramètres en tant que ReadOnlyVariables
5. Appliquez un script attaché dans l’éditeur doté d’une logique supplémentaire avec condition pour vérifier la date de dernière modification du fichier et vérifier si elle est plus ancienne que la période de conservation spécifiée. Ici vous pouvez voir les paramètres utilisés dans le script
Veuillez noter ici que nous devons importer un espace de noms system.IO.
// TODO: Add your code here
int RetentionPeriod = Convert.ToInt32(Dts.Variables["User::Period"].Value.ToString());
string directoryPath = Dts.Variables["User::BackupFolder"].Value.ToString();
string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, "*.*");
foreach (string currFile in oldFiles)
{
FileInfo currFileInfo = new FileInfo(currFile);
if (currFileInfo.LastWriteTime < (DateTime.Now.AddDays(-RetentionPeriod)))
{
currFileInfo.Delete();
}
6. Activez la dernière étape et exécutez le package. Les fichiers plus anciens que la période de conservation spécifiée doivent être supprimés.
J’espère que vous avez aimé ce post. Restez à l’écoute pour plus.
DBA SQL Server BLOG 