同步可用性组中SQL Server实例对象

Availability groups can no longer be considered new. The feature was released in SQL Server 2012 but I often notice that SQL Server Failover Cluster Instances (FCIs) are much better understood. For a very long time, when people thought about SQL Server they thought about the entire instance as a unit. Microsoft has been implementing new features and services which begin to segment the concept of an instance from the concept of a database.

可用性组不再视为新的。 该功能已在SQL Server 2012中发布，但是我经常注意到，对SQL Server故障转移群集实例（FCI）的了解要好得多。 很长时间以来，当人们想到SQL Server时，他们就将整个实例作为一个单元来考虑。 Microsoft一直在实施新功能和服务 ，这些新功能和服务开始将实例的概念与数据库的概念区分开。

With an FCI, which most seem to be comfortable with, your entire SQL Server instance gains high-availability. With an Availability Group (AG), that is not the case.

使用FCI（似乎最适合），您的整个SQL Server实例都将获得高可用性。 对于可用性组（AG），情况并非如此。

“An availability group supports a replicated environment for a discrete set of USER databases, known as availability databases.” (emphasis is mine) – MSDN

“ 可用性组支持 离散的一组USER数据库 （称为可用性数据库） 的复制环境 。 ”（强调是我的）– MSDN

Since AGs only replicate user databases, that means that all data stored in master, model, and msdb will not be handled. Replication of your SQL Agent jobs, linked servers, logins, credentials, and all over instance objects, will not be handled for you. This means that you have to implement a process to do keep all of your cluster nodes synchronized.

由于AG仅复制用户数据库，这意味着将不处理存储在master，model和msdb中的所有数据。 不会为您处理SQL Agent作业，链接服务器，登录名，凭据以及所有实例对象的复制。 这意味着您必须实施一个流程来使所有群集节点保持同步。

选件 (Options)

I wrote about this same topic about two years ago. In that article, I expressed a release based method which would be hooked into your database lifecycle management (DLM) process. Over the years, I found that very few people adopted that way of dealing with their server objects. Shifting-left something like security and other instance level objects did not appeal to many and the method was highly specialized to development shops that used desired state configuration tools to manage their DLM.

大约两年前，我写过关于同一主题的文章。 在那篇文章中，我表达了一种基于发布的方法，该方法将与您的数据库生命周期管理（DLM）流程挂钩。 多年来，我发现很少有人采用这种方式处理服务器对象。 向左移动之类的安全性和其他实例级别对象之类的东西并没有吸引很多人，该方法高度适用于使用所需状态配置工具管理其DLM的开发商店。

This article is focusing on a new method that I wrote which is much more generic and fitting for a strong separation of duties between the production DBAs and the development / DevOps teams.

本文关注的是我编写的一种新方法，该方法更为通用，适合于将生产DBA与开发/ DevOps团队之间的职责分离。

过程 (The process)

The solution that you see below is a PowerShell script which is executed by a SQL Agent job on the primary replica of the AG. While it only executes on the primary replica, the job must exist on all nodes of the cluster and be smart enough to know which node is allowed to execute and which are secondary replicas.

您在下面看到的解决方案是PowerShell脚本，该脚本由AG的主副本上SQL Agent作业执行。 尽管该作业仅在主副本上执行，但该作业必须存在于群集的所有节点上，并且必须足够智能以知道允许执行哪个节点以及哪些是辅助副本。

The core functionality all resides within the PowerShell script. In the diagram, I show the script in a Microsoft Azure File Share. This is definitely not a requirement. If you prefer, the script could reside on an on-premises file server, locally on each node of your cluster, or even pasted into the SQL Agent job step itself. When I developed this solution, my servers were Azure VMs and I wanted a single piece of code to manage, rather than ensuring that the script was updated on all nodes of the cluster at all times.

核心功能全部驻留在PowerShell脚本中。 在该图中，我在Microsoft Azure File Share中显示了脚本。 这绝对不是必需的。 如果愿意，脚本可以驻留在本地文件服务器上，本地在群集的每个节点上，甚至可以粘贴到SQL Agent作业步骤本身中。 在开发此解决方案时，我的服务器是Azure VM，我只想管理一个代码段，而不是确保始终在群集的所有节点上更新脚本。

The PowerShell script relies on the community project dbatools and follows this process flow.

PowerShell脚本依赖于社区项目dbatools并遵循此流程。

A notable limitation of this process is that it does not update existing objects. Jobs which already exist but were updated, will not be altered. I chose to omit that functionality because it presents merge complications and problems. For example, the cleanest way to handle the process would be to drop and create the object each time the synchronization runs. If that happened, however, there would be gaps when logins didn’t exist and applications would fail to connect, SQL Agent jobs would lose history, and/or the processing of a job would fail because it was dropped part way through executing.

此过程的显着限制是它不会更新现有对象。 已经存在但已更新的作业将不会更改。 我选择忽略该功能，因为它会带来合并的复杂性和问题。 例如，处理该过程的最简单方法是每次运行同步时都删除并创建对象。 但是，如果发生这种情况，将不存在登录，并且应用程序将无法连接，SQL Agent作业将丢失历史记录，和/或作业的处理将由于在执行过程中被丢弃而失败，因此将出现间隙。

先决条件和社区代码 (Prerequisites and community code)

For this script to work, there are two prerequisites. The prerequisites are required to be installed on all nodes of the cluster.

要使该脚本起作用，必须满足两个先决条件。 要求先决条件必须安装在群集的所有节点上。

• Dbatools PowerShell module version 0.9.24 or higher. Dbatools PowerShell模块版本0.9.24或更高。
• PowerShell version 5.0 or higher. PowerShell 5.0或更高版本。

什么是dbatools？ (What is dbatools?)

Dbatools is a community developed, open-source, PowerShell module. It contains over 200 cmdlets designed to help the DBA with SQL Server administration, migrations, and best practices.

Dbatools是社区开发的开源PowerShell模块。 它包含200多个cmdlet，旨在帮助DBA进行SQL Server管理，迁移和最佳实践。

I chose to use this tool over rolling my own script because this is a supported platform with a team of experts constantly fixing bugs and adding new features. The long-term maintenance of this entire solution is nearly zero because the dbatools contributors will handle all of the tedium for you. They will update the cmdlets for new versions of SQL Server and they have hundreds of automated unit tests to stress this code base with every change.

我选择在滚动自己的脚本时使用此工具，因为这是一个受支持的平台，由专家团队不断修复错误并添加新功能。 整个解决方案的长期维护几乎为零，因为dbatools贡献者将为您处理所有繁琐的工作。 他们将为SQL Server的新版本更新cmdlet，并且它们具有数百个自动化的单元测试，以在每次更改时强调此代码库。

Even outside of this one-use case, I highly recommend to look into what dbatools has to offer and how it can help optimize your day.

即使在这种一次性使用案例之外，我也强烈建议您研究dbatools提供的功能以及它如何帮助优化您的一天。

安装 (Installation)

You can download and install the prerequisites yourself. I prefer scripting out installs, however. For that I recommend reviewing this article by Mike Robbins on scripting out the installation of PowerShell version 5.0 and you can use the below script to install dbatools by running a PowerShell console window as administrator.

您可以自己下载并安装必备软件。 但是，我更喜欢编写安装脚本。 为此，我建议阅读Mike Robbins撰写的有关脚本化PowerShell版本5.0安装的这篇文章 ，并且您可以使用以下脚本通过以管理员身份运行PowerShell控制台窗口来安装dbatools。

 
# Install the installer
if ((Get-Module -ListAvailable -Name PowerShellGet) -eq $null)
{ 
    Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force | Out-Null
}
Import-Module PowerShellGet | Out-Null
 
# Install dbatools
Install-Module dbatools -MinimumVersion 0.9.24 -AllowClobber -Force
 

代码 (The code)

Full PowerShell script and SQL Agent job T-SQL script available for download here as a zip file.

完整的PowerShell脚本和SQL Agent作业T-SQL脚本可从此处下载为zip文件。

SQL Agent作业 (SQL Agent job)

You will need to create a SQL Agent job to run the PowerShell script. In the downloadable content, there is the full T-SQL required to create the job, but edits are required. As mentioned above, I chose to use an Azure File Share to store my PowerShell script. If this is the direction that you will go, then you will need to update the script with:

您将需要创建一个SQL Agent作业来运行PowerShell脚本。 在可下载的内容中，有创建作业所需的完整T-SQL，但需要进行编辑。 如上所述，我选择使用Azure文件共享来存储我的PowerShell脚本。 如果这是您要遵循的方向，则需要使用以下命令更新脚本：

• The storage account name.
  存储帐户名称。
• The share name.
  共享名。
• The storage account’s primary or secondary key.
  存储帐户的主键或辅助键。

If you choose to use an on-premises file server or local directories, you will need to adjust the script accordingly. No matter which direction you take, you will need to create a proxy account with access to each node of your cluster and update the scripts:

如果选择使用本地文件服务器或本地目录，则需要相应地调整脚本。 无论您采用哪个方向，都将需要创建一个有权访问集群中每个节点的代理帐户并更新脚本：

• Domain name.
  域名。
• Port number.
  端口号。
• Proxy name.
  代理名称。

sp_add_jobstep parameters which need review / editing:

需要查看/编辑的sp_add_jobstep参数：

 
@subsystem=N'CmdExec', 
@command=N'cmd /c net use M: \\mystorageaccountname.file.core.windows.net\maintenancescripts "WQblqxDeyorCdof1I+PfakeprimarykeyFDWVVdo6NENQnof4/2AfkSSjGiTmZ5XtMsQ==" /user:Azure\mystorageaccountname && C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe "& M:\Sync-SqlInstanceObjects.ps1 -DomainName ''mydomain.com'' -Port 1433;" && net use M: /delete /Y', 
@proxy_name=N'DOMAIN\service_account_name'
 

资料下载 (Downloads)

• Sync-SqlInstanceObjects-Job-and-Script同步SqlInstanceObjects作业和脚本

附录A – PowerShell脚本 (Appendix A – The PowerShell script)

 
<#
 
.DESCRIPTION
This script will detect your Availability Group primary replica and copy all of its instance level objects to all of the secondary replicas within the Availability Group.
 
.EXAMPLE
./Sync-SqlInstanceObjects.ps1 -DomainName "mydomain.com" -Port 1433
 
.NOTES
One limitation of this script is that it assumes you only have one availability group.
 
.LINK
https://www.sqlhammer.com/synchronizing-server-objects-for-availability-groups/
 
#>
 
[CmdletBinding(SupportsShouldProcess=$true)]
Param
(
    [Parameter(Mandatory=$false,
			   ValueFromPipeline=$true,
			   ValueFromPipelineByPropertyName=$true,
			   HelpMessage="Domain that this Availability Group is in. I.e. 'mydomain.com'")]
	[string]$DomainName,
    [Parameter(Mandatory=$false,
			   ValueFromPipeline=$true,
			   ValueFromPipelineByPropertyName=$true,
			   HelpMessage="Port number that all connections will be made under.")]
	[bigint]$Port
)
 
Write-Output "Sync started."
 
#Error handling
 
$ErrorActionPreference = "stop";
 
Trap 
{
 
    $err = $_.Exception
    while ( $err.InnerException )
    {
 
        $err = $err.InnerException
        Write-Output $err.Message
 
    };
 
}
 
# Helper functions
 
function Get-FullConnectionName ($ServerName)
{
 
    return "$ServerName.$DomainName,$Port"
 
}
 
function Compare-SystemVersion ($Version1, $Version2)
{
    
    try
    {
        if ([System.Version]$Version1 -lt [System.Version]$Version2)
        {
            return -1
        }
        elseif ([System.Version]$Version1 -eq [System.Version]$Version2)
        {
            return 0
        }
        elseif ([System.Version]$Version1 -gt [System.Version]$Version2)
        {
            return 1
        }
    }
    catch
    {
        return -2
    }
 
}
 
# Prerequisites
 
try
{
    
    Write-Output "Valiating prerequisites."
 
	<# Works but needs to run as admin which our SQL Agent is not going to do.
	 # Code remains for reference of how to initiate a new node
 
    if ((Get-Module -ListAvailable -Name dbatools) -eq $null `
        -or ((Compare-SystemVersion (Get-Module -ListAvailable -Name dbatools).Version "0.9.24") -lt 0))
    {
 
        # Install the installer
        if ((Get-Module -ListAvailable -Name PowerShellGet) -eq $null)
        { 
            Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force | Out-Null
        }
 
        Import-Module PowerShellGet | Out-Null
 
        # Install dbatools
        Install-Module dbatools -MinimumVersion 0.9.24 -AllowClobber -Force
 
    }
	#>
 
    if ((Get-Module -Name dbatools) -eq $null)
    {
    
	    Import-Module dbatools | Out-Null
 
    }
 
    Write-Output "Prerequisites loaded."
 
}
catch
{
    
    Write-Error $_.Exception.Message -EA Continue
    Write-Error "One or more of the prerequisites did not load. Review previous errors for more details." -EA Stop
    return
 
}
 
# Detect Availability Group Primary replica
 
Write-Output "Begin query of Availability Group state."
 
$DataSource = (Get-FullConnectionName $env:COMPUTERNAME)
 
$Query = @"
SELECT replica_server_name
	, HAGS.primary_replica
FROM sys.availability_replicas AR
INNER JOIN sys.dm_hadr_availability_group_states HAGS
INNER JOIN sys.availability_groups AG ON AG.group_id = HAGS.group_id
    ON HAGS.group_id = AR.group_id;
"@
 
$AGStates = Invoke-Sqlcmd2 -ServerInstance $DataSource -Query $Query -ConnectionTimeout 30
 
if(([DBNull]::Value).Equals($AGStates[0].primary_replica))
{
 
    Write-Error "Availability Group state query returned no results. Confirm that you connected to a SQL Server instance running an Availability Group. No work was accomplished."
    return
 
}
 
Write-Output "Completed query of Availability Group state."
 
# Only runs if it is the primary, secondaries remain idle.
if($AGStates[0].primary_replica.ToLower().CompareTo($env:COMPUTERNAME.ToLower()) -eq 0)
{
 
    foreach($replica in $AGStates)
    {
        # Skip this iteration because source and destination would be the same
        if($replica.replica_server_name.ToLower().CompareTo($env:COMPUTERNAME.ToLower()) -eq 0)
        {
            continue
        }
 
        $SecondaryReplica = (Get-FullConnectionName $replica.replica_server_name)
 
        Write-Output "Copying objects from $DataSource to $SecondaryReplica"
 
        # Copy objects
 
        Write-Output "Copying Logins."
        Copy-DbaLogin -Source $DataSource -Destination $SecondaryReplica
 
        Write-Output "Copying BackupDevices."
        Copy-DbaBackupDevice -Source $DataSource -Destination $SecondaryReplica
 
        Write-Output "Copying Endpoints."
        Copy-DbaEndpoint -Source $DataSource -Destination $SecondaryReplica
 
        Write-Output "Copying Linked Servers."
        Copy-DbaLinkedServer -Source $DataSource -Destination $SecondaryReplica
 
        Write-Output "Copying Agent Categories."
        Copy-DbaAgentCategory -Source $DataSource -Destination $SecondaryReplica
 
        Write-Output "Copying Shared Schedules."
        Copy-DbaAgentSharedSchedule -Source $DataSource -Destination $SecondaryReplica
 
        Write-Output "Copying Operators."
        Copy-DbaAgentOperator -Source $DataSource -Destination $SecondaryReplica
 
        Write-Output "Copying Credentials."
        Copy-DbaCredential -Source $DataSource -Destination $SecondaryReplica
        
        Write-Output "Copying Proxy Account."
        Copy-DbaAgentProxyAccount -Source $DataSource -Destination $SecondaryReplica
        
        Write-Output "Copying Job."
        Copy-DbaAgentJob -Source $DataSource -Destination $SecondaryReplica
        
        Write-Output "Copy complete from $DataSource to $SecondaryReplica"
    }
 
}
else
{
    Write-Output "No work accompished, this is not the primary node."
}
 
Write-Output "Sync complete."
 

翻译自: https://www.sqlshack.com/synchronizing-sql-server-instance-objects-availability-group/