1. 全栈程序员必看首页

PowerShell应用之-事务复制

全栈程序员-用户IM • • 未分类

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之前，我们先要准备好一些工作：

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

image

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

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

PowerShell应用之-事务复制
ExpandedBlockStart.gif
View Code

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




#
#配置发布、分发服务器者登录用户名&密码，这里发布与分发使用同一个实例

#
————————————————————


$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 
$_

}

 

image

 

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

 

PowerShell应用之-事务复制
ExpandedBlockStart.gif
View Code

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




#
#配置发布、分发服务器者登录用户名&密码，这里发布与分发使用同一个实例

#
————————————————————


$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 
$_

}

 

image

image

 

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

 

PowerShell应用之-事务复制
ExpandedBlockStart.gif
View Code

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




#
#分发代理程序执行账户

#
————————————————————


$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 
$_

}

 

image

 

 

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

 

 

PowerShell应用之-事务复制
ExpandedBlockStart.gif
View Code

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




#
#分发代理程序执行账户

#
————————————————————


$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 
$_

}

 

image

 

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

image

 

 

小结

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

 

 

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

发布者：全栈程序员-用户IM，转载请注明出处：https://javaforall.cn/110512.html原文链接：https://javaforall.cn

【正版授权，激活自己账号】： Jetbrains全家桶Ide使用，1年售后保障，每天仅需1毛

【官方授权 正版激活】： 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

赞 (0)
全栈程序员-用户IM全栈程序员-用户IM
0 0


相关推荐

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号