O espelhamento de banco de dados tem sido uma solução popular de recuperação de desastres para o SQL Server desde sua introdução no SQL Server 2005. No entanto, assim como o log shipping, ele está limitado a espelhar apenas o próprio banco de dados e não inclui logins, associações de função do servidor ou permissões de nível de servidor. Como resultado, os administradores de banco de dados (DBAs) precisam planejar com antecedência e criar jobs personalizados para criar scripts e documentar essas limitações.

Neste artigo, exploraremos uma solução que usa o PowerShell para criar scripts de logins e permissões para um banco de dados espelhado. Usaremos o banco de dados AdventureWorks2012 como exemplo, com dois logins que existem no servidor principal, mas não no servidor espelho.

O primeiro passo em nosso script do PowerShell é conectar-se ao servidor principal e gerar um script CREATE LOGIN para os logins ausentes. Este script recupera o nome do login, SID e senha criptografada (se aplicável) usando o seguinte código:

SELECT 'USE master; CREATE LOGIN ' + QUOTENAME(p.name) + ' ' + 
    CASE WHEN p.type IN ('U', 'G') THEN 'FROM WINDOWS ' ELSE '' END + 
    'WITH ' + 
    CASE WHEN p.type = 'S' THEN 'PASSWORD = ' + 
        master.sys.fn_varbintohexstr(l.password_hash) + ' HASHED, ' + 
        'SID = ' + master.sys.fn_varbintohexstr(l.sid) + ', ' + 
        'CHECK_EXPIRATION = ' + 
        CASE WHEN l.is_expiration_checked > 0 THEN 'ON, ' ELSE 'OFF, ' END + 
        'CHECK_POLICY = ' + 
        CASE WHEN l.is_policy_checked > 0 THEN 'ON, ' ELSE 'OFF, ' END + 
        CASE WHEN l.credential_id > 0 THEN 'CREDENTIAL = ' + c.name + ', ' ELSE '' END 
    ELSE '' END + 
    'DEFAULT_DATABASE = ' + p.default_database_name + 
    CASE WHEN LEN(p.default_language_name) > 0 THEN ', DEFAULT_LANGUAGE = ' + p.default_language_name ELSE '' END + 
    ';' AS 'LoginScript' 
FROM master.sys.server_principals p 
LEFT JOIN master.sys.sql_logins l ON p.principal_id = l.principal_id 
LEFT JOIN master.sys.credentials c ON l.credential_id = c.credential_id 
WHERE p.type IN ('S', 'U', 'G') 
    AND p.name NOT IN ('sa', 'NT AUTHORITY\SYSTEM') 
    AND p.name NOT LIKE '##%##' 
    AND p.name NOT LIKE 'BUILTIN\%' 
    AND p.name NOT LIKE 'NT SERVICE\%' 
ORDER BY p.name;

O script gera uma instrução CREATE LOGIN para cada login ausente, incluindo as opções e configurações necessárias. A saída é então gravada em um arquivo no servidor espelho usando a classe System.IO.StreamWriter.

Em seguida, precisamos criar um script para as associações de função do servidor ou permissões de nível de servidor para os logins ausentes. Isso é feito usando o seguinte código:

-- CRIAR ASSOCIAÇÕES DE FUNÇÃO DO SERVIDOR
SELECT 'USE master; EXEC sp_addsrvrolemember @loginame = ' + QUOTENAME(s.name) + ', @rolename = ' + QUOTENAME(s2.name) + ';' AS 'ServerPermission'
FROM master.sys.server_role_members r
INNER JOIN master.sys.server_principals s ON s.principal_id = r.member_principal_id
INNER JOIN master.sys.server_principals s2 ON s2.principal_id = r.role_principal_id
WHERE s2.type = 'R'
    AND s.is_disabled = 0
    AND s.name NOT IN ('sa', 'NT AUTHORITY\SYSTEM')
    AND s.name NOT LIKE '##%##'
    AND s.name NOT LIKE 'NT SERVICE\%'
UNION ALL
-- CRIAR PERMISSÕES DE NÍVEL DE SERVIDOR
SELECT 'USE master; ' + sp.state_desc + ' ' + sp.permission_name + ' TO ' + QUOTENAME(s.name) COLLATE Latin1_General_CI_AS + ';' AS 'ServerPermission'
FROM sys.server_permissions sp
JOIN sys.server_principals s ON sp.grantee_principal_id = s.principal_id
WHERE s.type IN ('S', 'G', 'U')
    AND sp.type NOT IN ('CO', 'COSQ')
    AND s.is_disabled = 0
    AND s.name NOT IN ('sa', 'NT AUTHORITY\SYSTEM')
    AND s.name NOT LIKE '##%##'
    AND s.name NOT LIKE 'NT SERVICE\%';

Este código recupera as associações de função do servidor e as permissões de nível de servidor para os logins ausentes e as grava no mesmo arquivo do script de login.

Uma vez que o banco de dados é failover para o servidor espelho, o DBA pode executar este script para recriar os logins ausentes e aplicar quaisquer permissões ausentes. O script pode ser agendado para ser executado regularmente, garantindo que quaisquer alterações nos logins ou permissões sejam capturadas na versão mais recente dos arquivos.

Embora este exemplo tenha sido usado com o espelhamento de banco de dados, a mesma estratégia pode ser aplicada ao log shipping ou qualquer outra solução de recuperação de desastres que não espelhe automaticamente logins e permissões.

Ao usar o PowerShell para criar scripts de logins e permissões, os DBAs podem garantir que sua solução de recuperação de desastres inclua todos os componentes necessários para um failover bem-sucedido. Essa abordagem fornece um nível de personalização e controle que não está disponível prontamente com o espelhamento de banco de dados ou log shipping.

Obrigado por ler este artigo sobre conceitos do SQL Server e jobs personalizados. Esperamos que você o tenha achado informativo e útil para o seu próprio ambiente do SQL Server.