sqlserver同步登陆账号和代理任务

Question

如果同步用的是mirror 或 always ON，那么登陆账号和代理任务是没办法自动同步的，如果发生切换，则会出现无法连接新主库和没有代理任务的情况。如果在搭建 mirror 或 alwayson 的时候，提前把 登陆账号和代理任务在没有个副本上都创建好的话，可能会避免这个切换连不上的问题。但这种方法也有一个问题，就是如果在使用图中，开发修改了登陆账号密码或修改了代理任务中的执行内容，那么在发生切换后也会有问题。所以我想问的是，有不有什么方法，可以自动同步登陆用户和代理任务？

Answer 1

用powershell同步，这是我的同事编写的一揽子小code：

$connectionString = "Data Source=PRIMARY_SERVER;Initial Catalog=master;Integrated Security=True"

$replicaConnectionStrings = Invoke-Sqlcmd -ConnectionString $connectionString -Query "SELECT replica_server_name, replica_server_url FROM sys.dm_hadr_availability_replica_states WHERE role_desc = 'SECONDARY'" | ForEach-Object { "Data Source=$($_.replica_server_name);Initial Catalog=master;Integrated Security=True" }

$logins = Invoke-Sqlcmd -ConnectionString $connectionString -Query "SELECT name, password_hash, sid, default_database_name, default_language_name FROM sys.server_principals WHERE type_desc = 'SQL_LOGIN'"
$proxyAccounts = Invoke-Sqlcmd -ConnectionString $connectionString -Query "SELECT name, credential_id, enabled, description, proxy_id, credential_identity, credential_name FROM msdb.dbo.sysproxies"

foreach ($replicaConnectionString in $replicaConnectionStrings) {
    $replicaLogins = Invoke-Sqlcmd -ConnectionString $replicaConnectionString -Query "SELECT name FROM sys.server_principals WHERE type_desc = 'SQL_LOGIN'"
    foreach ($login in $logins) {
        if ($replicaLogins.name -notcontains $login.name) {
            $sql = "CREATE LOGIN [$($login.name)] WITH PASSWORD = '$($login.password_hash)', SID = $($login.sid), DEFAULT_DATABASE = $($login.default_database_name), DEFAULT_LANGUAGE = $($login.default_language_name)"
            Invoke-Sqlcmd -ConnectionString $replicaConnectionString -Query $sql
        }
    }
}
foreach ($replicaConnectionString in $replicaConnectionStrings) {
    $replicaProxyAccounts = Invoke-Sqlcmd -ConnectionString $replicaConnectionString -Query "SELECT name FROM msdb.dbo.sysproxies"
    foreach ($proxyAccount in $proxyAccounts) {
        if ($replicaProxyAccounts.name -notcontains $proxyAccount.name) {
            $sql = "EXEC msdb.dbo.sp_add_proxy @proxy_name=N'$($proxyAccount.name)', @enabled=$($proxyAccount.enabled), @description=N'$($proxyAccount.description)', @credential_name=N'$($proxyAccount.credential_name)', @credential_id=$($proxyAccount.credential_id), @credential_identity=$($proxyAccount.credential_identity)"
            Invoke-Sqlcmd -ConnectionString $replicaConnectionString -Query $sql
        }
    }
}