復制的概念 復制是將一組數據從一個數據源拷貝到多個數據源的技術
是將一份數據發布到多個存儲站點上的有效方式
使用復制技術
用戶可以將一份數據發布到多台服務器上
從而使不同的服務器用戶都可以在權限的許可的范圍內共享這份數據
復制技術可以確保分布在不同地點的數據自動同步更新
從而保證數據的一致性
SQL復制的基本元素包括 出版服務器
訂閱服務器
分發服務器
出版物
文章
SQL復制的工作原理 SQL SERVER 主要采用出版物
訂閱的方式來處理復制
源數據所在的服務器是出版服務器
負責發表數據
出版服務器把要發表的數據的所有改變情況的拷貝復制到分發服務器
分發服務器包含有一個分發數據庫
可接收數據的所有改變
並保存這些改變
再把這些改變分發給訂閱服務器
SQL SERVER復制技術類型 SQL SERVER提供了三種復制技術
分別是
快照復制（呆會我們就使用這個）
事務復制
合並復制
只要把上面這些概念弄清楚了那麼對復制也就有了一定的理解
接下來我們就一步一步來實現復制的步驟
第一先來配置出版服務器 (
)選中指定[服務器]節點
(
)從[工具]下拉菜單的[復制]子菜單中選擇[發布
訂閱服務器和分發]命令
(
)系統彈出一個對話框點[下一步]然後看著提示一直操作到完成
(
)當完成了出版服務器的設置以後系統會為該服務器的樹形結構中添加一個復制監視器
同時也生成一個分發數據庫(distribution)
第二創建出版物 (
)選中指定的服務器
(
)從[工具]菜單的[復制]子菜單中選擇[創建和管理發布]命令
此時系統會彈出一個對話框
(
)選擇要創建出版物的數據庫
然後單擊[創建發布]
(
)在[創建發布向導]的提示對話框中單擊[下一步]系統就會彈出一個對話框
對話框上的內容是復制的三個類型
我們現在選第一個也就是默認的快照發布(其他兩個大家可以去看看幫助)
(
)單擊[下一步]系統要求指定可以訂閱該發布的數據庫服務器類型
SQLSERVER允許在不同的數據庫如 ORACLE或ACCESS之間進行數據復制
但是在這裡我們選擇運行
SQL SERVER
的數據庫服務器
(
)單擊[下一步]系統就彈出一個定義文章的對話框也就是選擇要出版的表
(
)然後[下一步]直到操作完成
當完成出版物的創建後創建出版物的數據庫也就變成了一個共享數據庫
第三設計訂閱 (
)選中指定的訂閱服務器
(
)從[工具]下拉菜單中選擇[復制]子菜單的[請求訂閱]
(
)按照單擊[下一步]操作直到系統會提示檢查SQL SERVER代理服務的運行狀態
執行復制操作的前提條件是SQL SERVER代理服務必須已經啟動
(
)單擊[完成]
完成訂閱操作
完成上面的步驟其實復制也就是成功了
但是如何來知道復制是否成功了呢？這裡可以通過這種方法來快速看是否成功
展開出版服務器下面的復制——發布內容——右鍵發布內容——屬性——擊活——狀態然後點立即運行代理程序接著點代理程序屬性擊活調度把調度設置為每一天發生
每一分鐘
在
和
之間
接下來就是判斷復制是否成功了打開C:\Program Files\Microsoft SQL Server\MSSQL\REPLDATA\unc\XIAOWANGZI_database_database下面看是不是有一些以時間做為文件名的文件夾差不多一分中就產生一個
要是你還不信的話就打開你的數據庫看在訂閱的服務器的指定訂閱數據庫下看是不是看到了你剛才所發布的表—
一個手工同步的方案
定時同步服務器上的數據
例子:
測試環境
SQL Server
遠程服務器名:xz
用戶名為:sa
無密碼
測試數據庫:test
服務器上的表(查詢分析器連接到服務器上創建)
create table [user](id int primary key
number varchar(
)
name varchar(
))
go
以下在局域網(本機操作)
本機的表
state說明:null 表示新增記錄
表示修改過的記錄
表示無變化的記錄
if exists (select * from dbo
sysobjects where id = object_id(N
[user]
) and OBJECTPROPERTY(id
N
IsUserTable
) =
)
drop table [user]
GO
create table [user](id int identity(
)
number varchar(
)
name varchar(
)
state bit)
go
創建觸發器
維護state字段的值
create trigger t_state on [user]
after update
as
update [user] set state=
from [user] a join inserted b on a
id=b
id
where a
state is not null
go
為了方便同步處理
創建鏈接服務器到要同步的服務器
這裡的遠程服務器名為:xz
用戶名為:sa
無密碼
if exists(select
from master
sysservers where srvname=
srv_lnk
)
exec sp_dropserver
srv_lnk
droplogins
go
exec sp_addlinkedserver
srv_lnk
SQLOLEDB
xz
exec sp_addlinkedsrvlogin
srv_lnk
false
null
sa
go
創建同步處理的存儲過程
if exists (select * from dbo
sysobjects where id = object_id(N
[dbo]
[p_synchro]
) and OBJECTPROPERTY(id
N
IsProcedure
) =
)
drop procedure [dbo]
[p_synchro]
GO
create proc p_synchro
as
set XACT_ABORT on
啟動遠程服務器的MSDTC服務
exec master
xp_cmdshell
isql /S
xz
/U
sa
/P
/q
exec master
xp_cmdshell
net start msdtc
no_output
no_output
啟動本機的MSDTC服務
exec master
xp_cmdshell
net start msdtc
no_output
進行分布事務處理
如果表用標識列做主鍵
用下面的方法
BEGIN DISTRIBUTED TRANSACTION
同步刪除的數據
delete from srv_lnk
test
dbo
[user]
where id not in(select id from [user])
同步新增的數據
insert into srv_lnk
test
dbo
[user]
select id
number
name from [user] where state is null
同步修改的數據
update srv_lnk
test
dbo
[user] set
number=b
number
name=b
name
from srv_lnk
test
dbo
[user] a
join [user] b on a
id=b
id
where b
state=
同步後更新本機的標志
update [user] set state=
where isnull(state
)=
COMMIT TRAN
go
創建作業
定時執行數據同步的存儲過程
if exists(SELECT
from msdb
sysjobs where name=
數據處理
)
EXECUTE msdb
dbo
sp_delete_job @job_name=
數據處理
exec msdb
sp_add_job @job_name=
數據處理
創建作業步驟
declare @sql varchar(
)
@dbname varchar(
)
select @sql=
exec p_synchro
數據處理的命令
@dbname=db_name()
執行數據處理的數據庫名
exec msdb
sp_add_jobstep @job_name=
數據處理
@step_name =
數據同步
@subsystem =
TSQL
@database_name=@dbname
@command = @sql
@retry_attempts =
重試次數
@retry_interval =
重試間隔
創建調度
EXEC msdb
sp_add_jobschedule @job_name =
數據處理
@name =
時間安排
@freq_type =
每天
@freq_interval =
每天執行一次
@active_start_time =
點執行
go
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22013.html
