Перейти к содержимому

Mssql

Все по MSsql

Как в always on mssql синхронизировать логины и пароль на вторичной реплики и удалять логины когда удалили на первичной c проверкой хэш паролей

Первый скрипт 

Задача сделать синхронизацию логинов с праймери реплики в mssql на секондори реплики с проверкой паролей совпадают или нет . если не совпадают то скопировать логин с правильным паролем. Сделал проверку по хэшу паролей. Лог будет удаляться из папки старше 7 дней

Set-DbatoolsInsecureConnection -SessionOnly
# Параметры
$AGListenerName = "db01" # Имя листенера группы доступности
$LogFolder = "D:\DBA_scripts_AG\log"
$MaxLogAge = 7 # Максимальный возраст логов в днях

# Создаем папку для логов если не существует
if (-not (Test-Path $LogFolder)) {
    New-Item -ItemType Directory -Path $LogFolder -Force | Out-Null
}

# Удаляем старые логи (старше 7 дней)
Get-ChildItem -Path $LogFolder  |
… Читать далее

Как в Mssql сделать новый файл файловой группы на новом диске H .

Задача в mssql добавить новый файл файловой группы на базах на новый диск H

Проверяем какие базы не имеют файлов на диске

SELECT
    DB_NAME(database_id) AS DatabaseName
FROM
    sys.master_files
WHERE
    database_id > 4 -- исключаем системные базы
GROUP BY
    database_id
HAVING
    SUM(CASE WHEN LEFT(physical_name, 1) = 'H' THEN 1 ELSE 0 END) = 0;

Создать команды для создания файла по всем базам.

-- Объявление переменных
DECLARE @Sql NVARCHAR(MAX);
DECLARE @DbName NVARCHAR(128);

-- Удаление временных таблиц, если они уже существуют
IF OBJECT_ID('tempdb..#DatabaseFG') IS NOT NULL DROP TABLE tempdb..#DatabaseFG;
IF OBJECT_ID('tempdb..#SysFiles') IS NOT NULL DROP TABLE tempdb..#SysFiles;
IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL DROP
… Читать далее

Как сделать скрипт для бэкапа mssql что бы следить что фуул лежит в том же месте что и новое место.

Задача сделать скрипт который будет проверять если ли фулл бэкап в заданной директории .

Процедура используется SQL Server Backup ola.hallengren

 

-- Объявляем переменные
DECLARE @BackupDirectory NVARCHAR(500) = N'\\ARC03\sql_backup$';
DECLARE @DatabaseList NVARCHAR(MAX) = ''; -- Список баз для бэкапа
DECLARE @DatabaseName NVARCHAR(128);
DECLARE @LastFullBackupPath NVARCHAR(500);
DECLARE @LastFullBackupDate DATETIME;

-- Курсор для перебора всех пользовательских баз данных
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE database_id > 4 -- Исключаем системные базы данных
AND state = 0; -- Только базы в режиме ONLINE

-- Открываем курсор
OPEN db_cursor;

-- Перебираем базы данных
FETCH NEXT FROM db_cursor INTO @DatabaseName;

WHILE @@FETCH_STATUS
… Читать далее

Как найти в mssql какой запрос больше всего нагружает файловую систему.

Выросли очереди на диски на сервере базы данных mssql. Задача найти запросы топ по чтению с диска.

Нагрузка на диск на чтение

Как найти в mssql какой запрос больше всего нагружает файловую систему.

По графику видно что нагрузка выросла. Диски nvme и поменять на более производительные нет возможности

Запрос по топу чтения

WITH DB_Disk_Reads_Stats

AS

(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_physical_reads) AS [physical_reads]

 FROM sys.dm_exec_query_stats AS qs

 CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 

              FROM sys.dm_exec_plan_attributes(qs.plan_handle)

              WHERE attribute = N'dbid') AS F_DB

 GROUP BY DatabaseID)

SELECT ROW_NUMBER() OVER(ORDER BY [physical_reads] DESC) AS [row_num],

       DatabaseName, [physical_reads], 

       CAST([physical_reads] * 1.0 / SUM([physical_reads]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Physical_Reads_Percent]

FROM DB_Disk_Reads_Stats

WHERE
… Читать далее

Как сгенерировать скрипт AUTOGROW включить автоматического увеличения всех файлов

DECLARE @Sql NVARCHAR(MAX);
DECLARE @DbName NVARCHAR(128);
 
-- Удаление временных таблиц, если они уже существуют
IF OBJECT_ID('tempdb..#DatabaseFG') IS NOT NULL DROP TABLE tempdb..#DatabaseFG;
IF OBJECT_ID('tempdb..#SysFiles') IS NOT NULL DROP TABLE tempdb..#SysFiles;
IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL DROP TABLE tempdb..#DatabaseList;
 
-- Создание временных таблиц
CREATE TABLE #DatabaseFG (
    DatabaseName NVARCHAR(128),
    FG_name NVARCHAR(128),
    data_space_id INT,
    type NVARCHAR(128),
    is_default INT,
    is_autogrow_all_files INT
);
 
CREATE TABLE #SysFiles (
    DatabaseName NVARCHAR(128),
    FileName NVARCHAR(128),
    FileSizeMB DECIMAL(18, 2),
    SpaceUsedMB DECIMAL(18, 2),
    FreeSpaceMB DECIMAL(18, 2)
);
 
CREATE TABLE #DatabaseList (
    DatabaseName NVARCHAR(128)
);
 
-- Курсор для обхода баз данных
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name
… Читать далее

Как сгенерировать скрипт для создание файловых групп

DECLARE @Sql NVARCHAR(MAX);
DECLARE @DbName NVARCHAR(128);
 
-- Удаление временных таблиц, если они уже существуют
IF OBJECT_ID('tempdb..#DatabaseFG') IS NOT NULL DROP TABLE tempdb..#DatabaseFG;
IF OBJECT_ID('tempdb..#SysFiles') IS NOT NULL DROP TABLE tempdb..#SysFiles;
IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL DROP TABLE tempdb..#DatabaseList;
 
-- Создание временных таблиц
CREATE TABLE #DatabaseFG (
    DatabaseName NVARCHAR(128),
    FG_name NVARCHAR(128),
    data_space_id INT,
    type NVARCHAR(128),
    is_default INT,
    is_autogrow_all_files INT
);
 
CREATE TABLE #SysFiles (
    DatabaseName NVARCHAR(128),
    FileName NVARCHAR(128),
    FileSizeMB DECIMAL(18, 2),
    SpaceUsedMB DECIMAL(18, 2),
    FreeSpaceMB DECIMAL(18, 2)
);
 
CREATE TABLE #DatabaseList (
    DatabaseName NVARCHAR(128)
);
 
-- Курсор для обхода баз данных
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name
… Читать далее

Как вывести размер всех баз в mssql с файловыми группами и Space Use Free Space Свободное место

DECLARE @Sql NVARCHAR(MAX)
DECLARE @DbName NVARCHAR(128)
 
-- Курсор для обхода баз данных
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') AND state_desc = 'ONLINE' -- Исключаем системные базы данных
 
-- Удаление временных таблиц, если они уже существуют
IF OBJECT_ID('tempdb..#DatabaseFG') IS NOT NULL
BEGIN
    DROP TABLE tempdb..#DatabaseFG;
END

IF OBJECT_ID('tempdb..#SysFiles') IS NOT NULL
BEGIN
    DROP TABLE tempdb..#SysFiles;
END
 
-- Создание временных таблиц
CREATE TABLE #DatabaseFG (
    DatabaseName NVARCHAR(128),
    FG_name NVARCHAR(128),
    data_space_id INT,
    type NVARCHAR(128),
    is_default INT,
    is_autogrow_all_files INT
);

CREATE TABLE #SysFiles (
    DatabaseName NVARCHAR(128),
    FileName NVARCHAR(128),
    FileSizeMB DECIMAL(18, 2),
    SpaceUsedMB DECIMAL(18, 2),
… Читать далее

Как в mssql узнать какие права на базы данных имеет пользователь.

Задача узнать на какие базы и какие права имеет определённый logon в mssql

-- Создаем временную таблицу для сохранения результатов
IF OBJECT_ID('tempdb..#UserMapping') IS NOT NULL
    DROP TABLE #UserMapping;

CREATE TABLE #UserMapping (
    DatabaseName NVARCHAR(128),
    LoginName NVARCHAR(128),
    UserName NVARCHAR(128),
    UserType NVARCHAR(128),
    DefaultSchema NVARCHAR(128),
    DatabaseRole NVARCHAR(128)
);

-- Динамический SQL для всех баз данных
DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += '
USE [' + name + ']; 
INSERT INTO #UserMapping (DatabaseName, LoginName, UserName, UserType, DefaultSchema, DatabaseRole)
SELECT 
    ''' + name + ''' AS [DatabaseName], 
    sp.name AS [LoginName],
    dp.name AS [UserName],
    dp.type_desc AS [UserType],
    dp.default_schema_name AS [DefaultSchema],
    dp2.name AS [DatabaseRole]
FROM
… Читать далее

Как сделать шаг проверки первичной реплики в задании в always on

Задача в job сделать первым шагом проверку primary сервера

$StartTime = (Get-Date)
#Добавление шага проверки в джобы
$Sql_primary = 'db01'   # Первичная реплика
 
$JobNames =  Get-DbaAgentJob -SqlInstance $Sql_primary -ExcludeJob 'syspolicy_purge_history', 'AlwaysOn_Latency_Data_Collection', 'dba_Monitoring_Long_Query_Execution', 'sp_WhoIsActive'
foreach ($JobName in $JobNames) {
 
$jobStep = @{
    SqlInstance = $Sql_primary
    Job = $JobName.Name
    StepId = 1
    StepName = "CheckAO"
    Subsystem = "TransactSql"
    Command = "--шаг в джобах для проверки аг
    IF (master.sys.fn_hadr_is_primary_replica ('beer') = 0)
    BEGIN
        EXEC msdb.dbo.sp_stop_job @job_id =  `$(ESCAPE_SQUOTE(JOBID))
    END"
    OnSuccessAction = "GoToNextStep"
    OnFailAction = "QuitWithFailure"
    Insert = $true
    #Force = $true
    }
 New-DbaAgentJobStep @jobStep
}
 
$EndTime = (Get-Date)
$TotalTime =$EndTime-$StartTime
$TotalTime.ToString()

 

Как бэкапить логины (login) и задания (JOB) mssql через dbatools

Задача настроить бэкап в папку включенных и отключённых job и login с mssql

Set-DbatoolsInsecureConnection -SessionOnly
$StartTime = (Get-Date)
$AGLSN = 'db01' #'msk-dblistener' # имя листенера группы доступности
$ExcludeJobs = ''
$ExcludeLogins = ''
$BackupPath = '\\ARC03\sql_backup$\' # В конце обязательно должен стоять "\"
$CurrDate = (Get-Date -Format 'yyyy.MM.dd.HH.mm')

$PrimaryReplica = Get-DbaAgReplica -SqlInstance $AGLSN | Where-Object Role -eq Primary

# Папки для хранения бэкапов
$BackupDisabledJobPath = $BackupPath + $primaryReplica.Name + '\BackupDisabledJob'  # Папка для выключенных джобов
$BackupEnabledJobPath = $BackupPath + $primaryReplica.Name + '\BackupEnabledJob'  # Папка для включённых джобов
$BackupDisabledLoginPath = $BackupPath + $primaryReplica.Name + '\BackupDisabledLogin'  # Папка для выключенных логинов
$BackupEnabledLoginPath
… Читать далее
Яндекс.Метрика