SQLSERVER資料庫同步技術

SQLServer2000同步複製技術實現步驟
一、 預備工作
1.發行伺服器,訂閱伺服器都建立一個同名的windows使用者,並設定相同的密碼,做為發布快照檔案夾的有效訪問使用者
--管理工具
--電腦管理
--使用者和組
--右鍵使用者
--建立使用者
--建立一個隸屬於administrator組的登陸windows的使用者（SynUser）
2.在發行伺服器上,建立一個共用目錄,做為發布的快照檔案的存放目錄,操作:
我的電腦--D:\ 建立一個目錄,名為: PUB
--右鍵這個建立的目錄
--屬性--共用
--選擇"共用該檔案夾"
--通過"許可權"按紐來設定具體的使用者權限,保證第一步中建立的使用者(SynUser) 具有對該檔案夾的所有許可權
--確定
3.設定SQL代理(SQLSERVERAGENT)服務的啟動使用者(發布/訂閱伺服器均做此設定)
開始--程式--管理工具--服務
--右鍵SQLSERVERAGENT
--屬性--登陸--選擇"此賬戶"
--輸入或者選擇第一步中建立的windows登入使用者名稱（SynUser）
--"密碼"中輸入該使用者的密碼
4.設定SQL Server身分識別驗證模式,解決串連時的許可權問題(發布/訂閱伺服器均做此設定)
企業管理器
--右鍵SQL執行個體--屬性
--安全性--身分識別驗證
--選擇"SQL Server 和 Windows"
--確定
5.在發行伺服器和訂閱伺服器上互相註冊
企業管理器
--右鍵SQL Server組
--建立SQL Server註冊...
--下一步--可用的伺服器中,輸入你要註冊的遠程伺服器名 --添加
--下一步--串連使用,選擇第二個"SQL Server身分識別驗證"
--下一步--輸入使用者名稱和密碼（SynUser）
--下一步--選擇SQL Server組,也可以建立一個新組
--下一步--完成
6.對於只能用IP,不能用電腦名稱的,為其註冊伺服器別名（此步在實施中沒用到）
(在串連端配置,比如,在訂閱伺服器上配置的話,伺服器名稱中輸入的是發行伺服器的IP)
開始--程式--Microsoft SQL Server--用戶端網路工具 + 生產力
--別名--添加
--網路程式庫選擇"tcp/ip"--伺服器別名輸入SQL伺服器名
--串連參數--伺服器名稱中輸入SQL伺服器ip地址
--如果你修改了SQL的連接埠,取消選擇"動態決定連接埠",並輸入對應的連接埠號碼
二、 正式配置
1、配置發行伺服器
開啟企業管理器，在發行伺服器（B、C、D）上執行以下步驟:
(1) 從[工具]下拉式功能表的[複製]子功能表中選擇[配置發布、訂閱伺服器和分發]出現配置發布和分發嚮導
(2) [下一步] 選擇散發者 可以選擇把發行伺服器自己作為散發者或者其他sql的伺服器（選擇自己）
(3) [下一步] 設定快照檔案夾
採用預設\\servername\Pub
(4) [下一步] 自訂配置
可以選擇:是,讓我設定散發資料庫屬性啟用發行伺服器或設定發布設定
否,使用下列預設設定（推薦）
(5) [下一步] 設定散發資料庫名稱和位置 採用預設值
(6) [下一步] 啟用發行伺服器 選擇作為發布的伺服器
(7) [下一步] 選擇需要發布的資料庫和發布類型
(8) [下一步] 選擇註冊訂閱伺服器
(9) [下一步] 完成配置
2、建立出版物
發行伺服器B、C、D上
(1)從[工具]菜單的[複製]子功能表中選擇[建立和管理髮布]命令
(2)選擇要建立出版物的資料庫，然後單擊[建立發布]
(3)在[建立發布嚮導]的提示對話方塊中單擊[下一步]系統就會彈出一個對話方塊。對話方塊上的內容是複製的三個類型。我們現在選第一個也就是預設的快照發布(其他兩個大家可以去看看協助)
(4)單擊[下一步]系統要求指定可以訂閱該發布的資料庫伺服器類型,
SQLSERVER允許在不同的資料庫如 orACLE或ACCESS之間進行資料複製。
但是在這裡我們選擇運行"SQL SERVER 2000"的資料庫伺服器
(5)單擊[下一步]系統就彈出一個定義文章的對話方塊也就是選擇要出版的表
注意: 如果前面選擇了事務發布 則再這一步中只能選擇帶有主鍵的表
(6)選擇發布名稱和描述
(7)自訂發布屬性 嚮導提供的選擇:
是 我將自訂資料篩選,啟用匿名訂閱和或其他自訂屬性
否 根據指定方式建立發布 （建議採用自訂的方式）
(8)[下一步] 選擇篩選發布的方式
(9)[下一步] 可以選擇是否允許匿名訂閱
1)如果選擇署名訂閱,則需要在發行伺服器上添加訂閱伺服器
方法: [工具]->[複製]->[配置發布、訂閱伺服器和分發的屬性]->[訂閱伺服器] 中添加
否則在訂閱伺服器上提取訂閱時會出現的提示:改發布不允許匿名訂閱
如果仍然需要匿名訂閱則用以下解決辦法
[企業管理器]->[複製]->[發布內容]->[屬性]->[訂閱選項] 選擇允許匿名提取訂閱
2)如果選擇匿名訂閱,則配置訂閱伺服器時不會出現以上提示
(10)[下一步] 設定快照 代理程式調度
(11)[下一步] 完成配置
當完成出版物的建立後建立出版物的資料庫也就變成了一個共用資料庫。
/*--同步兩個資料庫的樣本

有資料
srv1.庫名..author有欄位:id,name,phone,
srv2.庫名..author有欄位:id,name,telphone,adress

要求：
srv1.庫名..author增加記錄則srv1.庫名..author記錄增加
srv1.庫名..author的phone欄位更新，則srv1.庫名..author對應欄位telphone更新
--*/

--大致的處理步驟
--1.在 srv1 上建立串連伺服器,以便在 srv1 中操作 srv2,實現同步
exec sp_addlinkedserver 'srv2','','SQLOLEDB','srv2的sql執行個體名或ip'
exec sp_addlinkedsrvlogin 'srv2','false',null,'使用者名稱','密碼'
go

--2.在 srv1 和 srv2 這兩台電腦中,啟動 msdtc(分散式交易處理服務),並且設定為自動啟動
我的電腦--控制台--管理工具--服務--右鍵 Distributed Transaction Coordinator--屬性--啟動--並將啟動類型設定為自動啟動
go

--然後建立一個作業定時調用上面的同步處理預存程序就行了

企業管理器
--管理
--SQL Server代理
--右鍵作業
--新增作業
--"常規"項中輸入作業名稱
--"步驟"項
--建立
--"步驟名"中輸入步驟名
--"類型"中選擇"Transact-SQL 指令碼(TSQL)"
--"資料庫"選擇執行命令的資料庫
--"命令"中輸入要執行的語句: exec p_process
--確定
--"調度"項
--建立調度
--"名稱"中輸入調度名稱
--"調度類型"中選擇你的作業執行安排
--如果選擇"反覆出現"
--點"更改"來設定你的時間安排

然後將SQL Agent服務啟動,並設定為自動啟動,否則你的作業不會被執行

設定方法:
我的電腦--控制台--管理工具--服務--右鍵 SQLSERVERAGENT--屬性--啟動類型--選擇"自動啟動"--確定.

--3.實現同步處理的方法2,定時同步

--在srv1中建立如下的同步處理預存程序
create proc p_process
as
--更新修改過的資料
update b set name=i.name,telphone=i.telphone
from srv2.庫名.dbo.author b,author i
where b.id=i.id and
(b.name <> i.name or b.telphone <> i.telphone)

--插入新增的資料
insert srv2.庫名.dbo.author(id,name,telphone)
select id,name,telphone from author i
where not exists(
select * from srv2.庫名.dbo.author where id=i.id)

--刪除已經刪除的資料(如果需要的話)
delete b
from srv2.庫名.dbo.author b
where not exists(
select * from author where id=b.id)
go
SQL SERVER 2005 同步複製技術

一、準備工作：

1．建立一個 WINDOWS 使用者，設定為管理員權限，並設定密碼，作為發布快照檔案的有效訪問使用者。

2．在SQL SERVER下實現發行伺服器和訂閱伺服器的通訊正常（即可以互訪）。開啟1433連接埠，在防火牆中設特例

3．在發行伺服器上建立一個共用目錄，作為發布快照檔案的存放目錄。例如：在D盤根目錄下建檔案夾名為SqlCopy

4．設定SQL 代理（發行伺服器和訂閱伺服器均設定）本篇文章發表於www.xker.com(小新技術網)

開啟服務(控制台---管理工具---服務)

---右擊SQLSERVER AGENT---屬性---登入---選擇“此帳戶“

---輸入或選擇第一步中建立的WINDOWS 使用者

---“密碼“中輸入該使用者密碼

5．設定SQL SERVER 身分識別驗證，解決串連時的許可權問題（發布、訂閱伺服器均設定）

步驟為：物件總管----右擊SQL執行個體-----屬性----安全性----伺服器身分識別驗證------選“SQL Server和WINDOWS“，然後點確定

6.開啟SQL Server 2005的網路通訊協定TCP/IP和管道命名協議並重啟網路服務。

7.在SQL Server中建立步驟1中對應的系統使用者登陸名，作為發行集資料庫的擁有者（設定為dbo_owner和public）。

8．以系統超級使用者sa登陸SQL Server建立資料庫和表。

9．發行伺服器和訂閱伺服器互相註冊

步驟如下：視圖----單擊以註冊伺服器----右鍵資料庫引擎----建立伺服器註冊-----填寫要註冊的遠程伺服器名稱------身分識別驗證選“SQL Server驗證“-----使用者名稱(sa) 密碼------建立組（也可不建）-----完成。

10．對於只能用IP，不能用電腦名稱的，為其註冊伺服器別名

二、開始：

發行伺服器配置（在發行伺服器上配置發布和訂閱）

1． 選擇 複製 節點

2． 右鍵本地發布 ----下一步---------系統彈出對話方塊看提示----直到“指定快照檔案夾“

----在“快照檔案夾“中輸入準備工作中建立的目錄(指向步驟3所建的共用資料夾)------選擇發行集資料庫-------選擇發布類型-------選擇訂閱伺服器類型-------選擇要發布的對象------設定快照代理-------填寫發布名稱。本篇文章發表於www.xker.com(小新技術網)

3． 右鍵本機訂閱--------選擇發行伺服器-------選擇訂閱者式（如果是在伺服器方訂閱的話選擇發送訂閱反之

選擇提取訂閱）-------填加訂閱伺服器--------選擇代理計劃（一般選擇連續運行）---------其餘選擇預設項。

至此, SQL SERVER 2005 同步複製就完成了。使用複製技術，使用者可以將一份用戶端的資料發布到多台伺服器上，從而使不同的伺服器使用者都可以在許可權的許可的範圍內共用這份資料。複製技術可以確保分布在不同地點的資料自動同步更新，從而保證資料的一致性,就無需編程實現用戶端和伺服器端資料同步了！大大提高了工作效率！

*--調用樣本
--備份資料當前資料庫的所有內容
exec p_backupdatabase

--備份當前資料庫的指定表
exec p_backupdatabase @tblist='tb,tb1,tb2'
--*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_BackupDataBase]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_BackupDataBase]
GO

CREATE PROCEDURE p_BackupDataBase
@s_dbname sysname='', --要備份的資料庫名,如果不指定,則備份當前資料庫
@d_dbname sysname='', --備份產生的資料庫名,如果不指定,則為:@s_dbname+'_bak'
@tblist varchar(8000)='' --要備份的表名列表,如果不指定,則表示所有使用者表
AS
declare @sql varchar(8000),@err_msg varchar(1000)

--參數檢測
if isnull(@s_dbname,'')='' set @s_dbname=db_name()
if isnull(@d_dbname,'')='' set @d_dbname=@s_dbname+'_bak'
if exists(select 1 from master..sysdatabases where name=@d_dbname)
begin
set @err_msg='備份的資料庫 ['+@d_dbname+'] 已經存在!'
goto lb_exit
end
if not exists(select 1 from master..sysdatabases where name=@s_dbname)
begin
set @err_msg='要備份的資料庫 ['+@d_dbname+'] 不存在!'
goto lb_exit
end

--建立備份的資料庫
set @sql='create database ['+@d_dbname+']'
exec(@sql)

--備份表
declare @tbname sysname
set @sql='declare tb cursor for
select name from ['+@s_dbname+']..sysobjects
where status>0 and xtype=''U'''
+case isnull(@tblist,'') when '' then ''
else ' and name in('''+replace(@tblist,',',''',''')+''')' end
exec(@sql)
open tb
fetch next from tb into @tbname
while @@fetch_status=0
begin
set @sql='select * into ['+@d_dbname+']..['+@tbname
+'] from ['+@s_dbname+']..['+@tbname+']'
exec(@sql)
fetch next from tb into @tbname
end
close tb
deallocate tb

lb_exit:
if @err_msg <>'' raiserror(@err_msg,1,16)
go