PowerShell应用之事务复制

概述

---

在之前的几篇描述了PowerShell在SQL Server对SMO（SQL Server Management Objects）的管理，现在开始描述在SQL Server Replication中如何使用PowerShell脚本实现同步复制功能。在本篇中讲述的是实现同步复制中的事务复制，涉及如何创建Publishing,Distribution, Publication, Subscription等。这里将让我们了解到RMO(Replication Management Objects)一些类的使用：

• Microsoft.SqlServer.Replication.ReplicationServer
• Microsoft.SqlServer.Replication.DistributionDatabase
• Microsoft.SqlServer.Replication.DistributionPublisher
• Microsoft.SqlServer.Replication.ReplicationDatabase
• Microsoft.SqlServer.Replication.TransPublication
• Microsoft.SqlServer.Replication.TransArticle
• Microsoft.SqlServer.Replication.TransSubscription

如何配置发布和分发(Publishing & Distribution)

---

在具有域管理的环境中，配置Publishing & Distribution之前，我们先要准备好一些工作：

• 准备一个域账号，专门应用于同步复制使用
• 创建快照文件夹，设成共享，并设置该域账户具有修改快照文件夹的权限。

如上图，设置域账户“TN\SQLAccount”具有快照文件夹ReplicationSnapshot的更改权限。

若没有特殊的需要，一般配置Publishing & Distribution是在同一个SQL Server实例上进行。在通过SQL Server配置Publishing & Distribution向导，我们可以看到其过程主要涉及到配置快照集文件夹和分发数据库两个位置。当我们使用PowerShell脚本实现的时候，首先要考虑这两个位置。

<#===========================配置分发=================================#>

##配置发布、分发服务器者登录用户名&密码，这里发布与分发使用同一个实例
#------------------------------------------------------------
$serverInstance="WINSERVER01\SQL2008DE01"
$userName="sa"
$password="sql20081"


##设置域账号，应用于同步复制
#------------------------------------------------------------
$RAccount="TN\SQLAccount"
$RPassword="Sql123456"


##设置快照文件夹，要使用网络路径，而且设置账户$RAccount具有更改权限
#------------------------------------------------------------
$WorkingDirectory="\\WINSERVER01\ReplicationSnapshot"



<#================================================================#>

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Rmo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null

$DistributionDB="Distribution"

#Step 1: 创建连接
$ServerConnection =New-object "Microsoft.SqlServer.Management.Common.ServerConnection" $serverInstance,$userName, $password  

#Step 2: 创建同步复制服务器
$ReplicationServer =New-object "Microsoft.SqlServer.Replication.ReplicationServer" $ServerConnection

$Server=New-object "Microsoft.SqlServer.Management.Smo.Server" $ServerConnection


Try
{
    $ServerConnection.Connect()
    
    if($ServerConnection.IsOpen -And $ReplicationServer.IsDistributor -eq $false)
    {
        
        #Step 3:创建分发数据库Distribution
        $DistributionDatabase =New-Object "Microsoft.SqlServer.Replication.DistributionDatabase" $DistributionDB,$ServerConnection
        $DistributionDatabase.DataFolder=$Server.Settings.DefaultFile
        $DistributionDatabase.LogFolder=$Server.Settings.DefaultLog        
        
        #Step 4 : 设置分发数据库
        $ReplicationServer.InstallDistributor([string]$null,$DistributionDatabase)
        
        #Step 5: 设置DistributionPublisher
        $DistributionPublisher=New-Object "Microsoft.SqlServer.Replication.DistributionPublisher" $serverInstance,$ServerConnection
        $DistributionPublisher.DistributionDatabase=$DistributionDatabase.Name
        $DistributionPublisher.WorkingDirectory=$WorkingDirectory
        $DistributionPublisher.PublisherSecurity.WindowsAuthentication=$false
        $DistributionPublisher.PublisherSecurity.SqlStandardLogin=$userName
        $DistributionPublisher.PublisherSecurity.SqlStandardPassword=$password
        $DistributionPublisher.Create()
        
        #Step 6 : 设置域账号具有Owner权限
        if($Server.Logins[$RAccount] -eq $null)
        {
            $Login= New-Object "Microsoft.SqlServer.Management.Smo.Login" $Server,$RAccount
            $Login.LoginType="WindowsUser"
            $Login.Create()
        }                       
        
        $DB=$Server.Databases[$DistributionDB]
        $User=New-Object "Microsoft.SqlServer.Management.Smo.User" $DB,$RAccount               
        $User.Login=$RAccount
        $User.AddToRole("db_owner")
        $User.Create()
        Write-Host "设置分发完成！."
            
    }

}
Catch
{
    Write-Error $_
}

如何创建事务发布和设置发布项目(TransPublication & TransArticle)

<#===========================创建事务发布&发布项目===========================#>

##配置发布、分发服务器者登录用户名&密码，这里发布与分发使用同一个实例
#------------------------------------------------------------
$serverInstance="WINSERVER01\SQL2008DE01"
$userName="sa"
$password="sql20081"


##设置域账号，应用于同步复制
#------------------------------------------------------------
$RAccount="TN\SQLAccount"
$RPassword="Sql123456"

##发布数据库
#------------------------------------------------------------
$DataBase="ReplicationDB"

##项目名称
#------------------------------------------------------------
$TransPublicationName=$DataBase+“_Tran_”

##项目对应的是表，使用"Select * Form TableName Where ...;"格式
#------------------------------------------------------------
$SQL="
Select * From DataOwner Where ID=2;
Select * From Data1 Where OwnerID=2;
Select * From Data2 Where ParentID In(Select ID From dbo.Data1 Where OwnerID=2);
Select * From DataRelation Where ParentID In(Select dbo.Data2.ID From dbo.Data1 Inner Join dbo.Data2 On dbo.Data1.ID = dbo.Data2.ParentID And dbo.Data1.OwnerID=2);
"


##发行项目选项
#------------------------------------------------------------
$PreCreationMethod="drop" #当名称已被使用时的操作.可以选择“none”，“delete”，“drop”,"truncate"

<#================================================================#>

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Rmo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null

#Step 1: 创建连接
$ServerConnection =New-object "Microsoft.SqlServer.Management.Common.ServerConnection" $serverInstance,$userName, $password  

#Step 2:




Try
{
    $ServerConnection.Connect()
    if ($ServerConnection.IsOpen)
    {
        $ReplicationDatabase =New-object "Microsoft.SqlServer.Replication.ReplicationDatabase" $DataBase,$ServerConnection
        $ReplicationDatabase.EnabledTransPublishing=$true
        
        #创建队列读取器代理
        if ($ReplicationDatabase.LogReaderAgentExists -eq $false)
        {
            $ReplicationDatabase.LogReaderAgentProcessSecurity.Login=$RAccount
            $ReplicationDatabase.LogReaderAgentProcessSecurity.Password=$RPassword            
            $ReplicationDatabase.LogReaderAgentPublisherSecurity.WindowsAuthentication=$true
            
            $ReplicationDatabase.LogReaderAgentPublisherSecurity.SqlStandardLogin=$userName
            $ReplicationDatabase.LogReaderAgentPublisherSecurity.SqlStandardPassword=$password
            $ReplicationDatabase.LogReaderAgentPublisherSecurity.WindowsAuthentication=$false
            $ReplicationDatabase.CreateLogReaderAgent()
        }
        
        #创建事务发布   
        $TransPublication=New-object "Microsoft.SqlServer.Replication.TransPublication" $TransPublicationName,$DataBase,$ServerConnection
        
        $TransPublication.SnapshotGenerationAgentProcessSecurity.Login=$RAccount
        $TransPublication.SnapshotGenerationAgentProcessSecurity.Password=$RPassword
        $TransPublication.SnapshotGenerationAgentPublisherSecurity.WindowsAuthentication=$true
        
        $TransPublication.SnapshotGenerationAgentPublisherSecurity.SqlStandardLogin=$userName
        $TransPublication.SnapshotGenerationAgentPublisherSecurity.SqlStandardPassword=$password
        $TransPublication.SnapshotGenerationAgentPublisherSecurity.WindowsAuthentication=$false
        if ($TransPublication.IsExistingObject -eq $false)
        {
            $TransPublication.Create()
        }        
        
        
        #定义发布项目               
                
        While($SQL.IndexOf(“`r”) -gt 0)   #处理Select列表
            {$SQL=$SQL.Replace("`r","")}

        While($SQL.IndexOf(“`n”) -gt 0)
            {$SQL=$SQL.Replace("`n","")}
            
        While($SQL.IndexOf(“`t”) -gt 0)
            {$SQL=$SQL.Replace("`t "," ")}
                
        While($SQL.IndexOf("  ") -gt 0)
            {$SQL=$SQL.Replace("  "," ")}
        $SQL=$SQL.ToLower()
                    
        Foreach ($SqlLine In $SQL.split(";"))
        {
            if ($SqlLine.IndexOf("from") -gt 0)
            {          
                $Where=""     
                $TB=$SqlLine.split(" ")[3]
                If ($SqlLine.LastIndexOf("where") -gt 0)
                {
                    $Where=$SqlLine.substring([int32]($SqlLine.IndexOf("where")+6))
                }
                $Article=New-object "Microsoft.SqlServer.Replication.TransArticle" $TB,$TransPublicationName,$DataBase,$ServerConnection
                $Article.SourceObjectName=$TB
                $article.FilterClause=$Where
                $article.PreCreationMethod=$PreCreationMethod
                if ($Article.IsExistingObject -eq $false)
                {
                    $Article.Create()
                }                
            }
        }  
        
        Write-Host "事务发布 '$TransPublicationName' 已创建!"                              
    }
}
Catch
{

    Write-Error $_
}

如何创建推送订阅(Push Subscription)

<#===========================创建推送订阅===========================#>

##分发代理程序执行账户
#------------------------------------------------------------
$serverInstance="WINSERVER01\SQL2008DE01"
$userName="sa"
$password="sql20081"

##设置域账号，应用于同步复制
#------------------------------------------------------------
$RAccount="TN\SQLAccount"
$RPassword="Sql123456"


##连接到订阅服务器账户
#------------------------------------------------------------
$SubserverInstance="TON-WINXP001\SQL2008DE"
$SubuserName="sa"
$Subpassword="WinXP00120081"

##发布对象
#------------------------------------------------------------
$DataBase="ReplicationDB"
$TransPublicationName=“ReplicationDB_Tran_”

##是否要初始化
#------------------------------------------------------------
$invalidate=$true

<#================================================================#>

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Rmo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null

#Step 1: 创建连接
$ServerConnection =New-object "Microsoft.SqlServer.Management.Common.ServerConnection" $serverInstance,$userName, $password  

#Step 2:

Try
{
    $ServerConnection.Connect()

    if ($ServerConnection.IsOpen)
    {
        $TransPublication=New-object "Microsoft.SqlServer.Replication.TransPublication" $TransPublicationName,$DataBase,$ServerConnection
        if ($TransPublication.LoadProperties() -eq $true)
        {
            ##设置推送订阅
            if($TransPublication.Attributes -notmatch  "AllowPush")
            {
                ##使用到位運算OR(inclusive) "-bor" ,如果要刪除某一特征就使用"-bxor"
                $TransPublication.Attributes =$TransPublication.Attributes -bor [Microsoft.SqlServer.Replication.PublicationAttributes]::AllowPush
            }
            
            $subscriptionDBName=$TransPublication.DatabaseName
            $publicationDBName=$TransPublication.DatabaseName
            $publicationName=$TransPublication.Name
            
            $TransSubscription=New-object "Microsoft.SqlServer.Replication.TransSubscription" $publicationName,$publicationDBName,$SubserverInstance,$subscriptionDBName,$ServerConnection                  
            
            if($TransSubscription.LoadProperties() -eq $false)
            {
                ##设置分发代理程序账号(Windows账号)
                $TransSubscription.SynchronizationAgentProcessSecurity.Login=$RAccount
                $TransSubscription.SynchronizationAgentProcessSecurity.Password=$RPassword
                
                ##设置订阅服务器登录账号(SQL Server账号)
                $TransSubscription.SubscriberSecurity.WindowsAuthentication=$false
                $TransSubscription.SubscriberSecurity.SqlStandardLogin=$SubuserName
                $TransSubscription.SubscriberSecurity.SqlStandardPassword=$Subpassword           
                
                $TransSubscription.CreateSyncAgentByDefault=$true
                
                #每天执行
                $TransSubscription.AgentSchedule.FrequencyType=[Microsoft.SqlServer.Replication.ScheduleFrequencyType]::Daily
                
                #分钟
                $TransSubscription.AgentSchedule.FrequencySubDay=[Microsoft.SqlServer.Replication.ScheduleFrequencySubDay]::Minute
                
                #多少分钟执行一次
                $TransSubscription.AgentSchedule.FrequencySubDayInterval=1
                
                ##是否初始化
                if($invalidate -eq $false)
                {
                    $TransSubscription.SyncType=[Microsoft.SqlServer.Replication.SubscriptionSyncType]::ReplicationSupportOnly
                }
                
                $TransSubscription.Create()                                            
                                
                 #启动快照代理作业
                if ($TransPublication.SnapshotAvailable -eq $false )
                {
                    $TransPublication.StartSnapshotGenerationAgentJob()
                }
                
                
                Write-Host "订阅 "$TransSubscription.Name" 创建完成!"
            }
            Else
            {
                Write-Host "订阅 "$TransSubscription.Name" 已创建!"
            }
            
        }
        Else
        {
            Write-Host "发布对象 $TransPublicationName  不存在！"
        }
    }
}
Catch
{

    Write-Error $_
}

如何创建请求订阅(Pull Subscription)

<#===========================创建请求订阅===========================#>

##分发代理程序执行账户
#------------------------------------------------------------
$serverInstance="WINSERVER01\SQL2008DE01"
$userName="sa"
$password="sql20081"

##设置域账号，应用于同步复制
#------------------------------------------------------------
$RAccount="TN\SQLAccount"
$RPassword="Sql123456"

##连接到订阅服务器账户
#------------------------------------------------------------
$SubserverInstance="TON-WINXP001\SQL2008DE"
$SubuserName="sa"
$Subpassword="WinXP00120081"

##发布对象
#------------------------------------------------------------
$DataBase="ReplicationDB"
$TransPublicationName=“ReplicationDB_Tran_”

##是否要初始化
#------------------------------------------------------------
$invalidate=$true

<#================================================================#>

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Rmo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null

#Step 1: 创建连接
$ServerConnection =New-object "Microsoft.SqlServer.Management.Common.ServerConnection" $serverInstance,$userName, $password  
$SubServerConnection =New-object "Microsoft.SqlServer.Management.Common.ServerConnection" $SubserverInstance,$SubuserName, $Subpassword  

#Step 2:

Try
{
    $ServerConnection.Connect()
    $SubServerConnection.Connect()
    
    if ($ServerConnection.IsOpen -And $SubServerConnection.IsOpen)
    {
        $TransPublication=New-object "Microsoft.SqlServer.Replication.TransPublication" $TransPublicationName,$DataBase,$ServerConnection
        if ($TransPublication.LoadProperties() -eq $true)
        {
            ##设置请求订阅
            if($TransPublication.Attributes -notmatch  "AllowPull")
            {
                ##使用到位运算符OR(inclusive) "-bor" ,如果要刪除某一特征就使用"-bxor"
                $TransPublication.Attributes =$TransPublication.Attributes -bor [Microsoft.SqlServer.Replication.PublicationAttributes]::AllowPull
            }
            
            $subscriptionDBName=$TransPublication.DatabaseName
            $publisherName=$serverInstance
            $publicationDBName=$TransPublication.DatabaseName
            $publicationName=$TransPublication.Name
            
            $TransSubscription=New-object "Microsoft.SqlServer.Replication.TransPullSubscription" $subscriptionDBName,$publisherName,$publicationDBName,$publicationName,$SubServerConnection
            
            if($TransSubscription.LoadProperties() -eq $false)
            {
                ##设置分发代理程序账号(Windows帳戶)
                $TransSubscription.SynchronizationAgentProcessSecurity.Login=$RAccount
                $TransSubscription.SynchronizationAgentProcessSecurity.Password=$RPassword                          
                
                ##设置分发服务器登录账号(SQL Server帳戶)
                $TransSubscription.DistributorSecurity.WindowsAuthentication=$false
                $TransSubscription.DistributorSecurity.SqlStandardLogin=$userName
                $TransSubscription.DistributorSecurity.SqlStandardPassword=$password
                
                $TransSubscription.CreateSyncAgentByDefault=$true
                
                #每天执行
                $TransSubscription.AgentSchedule.FrequencyType=[Microsoft.SqlServer.Replication.ScheduleFrequencyType]::Daily
                
                #分钟
                $TransSubscription.AgentSchedule.FrequencySubDay=[Microsoft.SqlServer.Replication.ScheduleFrequencySubDay]::Minute
                
                #多少分钟执行一次
                $TransSubscription.AgentSchedule.FrequencySubDayInterval=1
                                                                
                $TransSubscription.Create()                                            

                #是否初始化
                if($invalidate -eq $false)
                {
                    $SyncType=[Microsoft.SqlServer.Replication.SubscriptionSyncType]::ReplicationSupportOnly
                }                
                Else
                {
                    $SyncType=[Microsoft.SqlServer.Replication.SubscriptionSyncType]::Automatic
                }
                
                #设置订阅服务器是否只读
                $subscriberType=[Microsoft.SqlServer.Replication.TransSubscriberType]::ReadOnly
                                                                
                #在发布服务器上注册订阅服务器                                                
                $TransPublication.MakePullSubscriptionWellKnown($SubserverInstance,$subscriptionDBName,$SyncType,$subscriberType)
                
                 #启动快照代理作业
                if ($TransPublication.SnapshotAvailable -eq $false )
                {
                    $TransPublication.StartSnapshotGenerationAgentJob()
                }
                
                
                Write-Host "订阅 "$TransSubscription.Name" 创建完成!"
            }
            Else
            {
                Write-Host "订阅 "$TransSubscription.Name" 已创建!"
            }
            
        }
        Else
        {
            Write-Host "发布对象 $TransPublicationName  不存在！"
        }
    }
}
Catch
{

    Write-Error $_
}

设置请求订阅的时候，需要设置域账户“TN\SQLAccount”对订阅数据库具有db_Owner成员权限，否则无法初始化。

小结

---

在同步复制使用PowerShell脚本创建，能为我们带来方便，特别是一次配置多个同步复制的时候。是于查看和检查同步复制，我们可以结合SSMS和复制监视器来完成。