熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

兩台SQL Server數據同步解決方案

2013-11-15 14:34:49  來源: SQL Server 

  復制的概念
  
  復制是將一組數據從一個數據源拷貝到多個數據源的技術是將一份數據發布到多個存儲站點上的有效方式使用復制技術用戶可以將一份數據發布到多台服務器上從而使不同的服務器用戶都可以在權限的許可的范圍內共享這份數據復制技術可以確保分布在不同地點的數據自動同步更新從而保證數據的一致性
  
  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 keynumber varchar()name varchar())
  go
  
  以下在局域網(本機操作)
  
  本機的表state說明:null 表示新增記錄 表示修改過的記錄 表示無變化的記錄
  
  if exists (select * from dbosysobjects where id = object_id(N[user]) and OBJECTPROPERTY(id NIsUserTable) = )
  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 aid=bid
  where astate is not null
  go
  
  為了方便同步處理創建鏈接服務器到要同步的服務器
  
  這裡的遠程服務器名為:xz用戶名為:sa無密碼
  
  if exists(select from mastersysservers where srvname=srv_lnk)
  exec sp_dropserver srv_lnkdroplogins
  go
  exec sp_addlinkedserver srv_lnkSQLOLEDBxz
  exec sp_addlinkedsrvlogin srv_lnkfalsenullsa
  go
  
  創建同步處理的存儲過程
  
  if exists (select * from dbosysobjects where id = object_id(N[dbo][p_synchro]) and OBJECTPROPERTY(id NIsProcedure) = )
  drop procedure [dbo][p_synchro]
  GO
  create proc p_synchro
  as
  set XACT_ABORT on
  
  啟動遠程服務器的MSDTC服務
  
  exec masterxp_cmdshell isql /Sxz /Usa /P /qexec masterxp_cmdshell net start msdtcno_outputno_output
  
  啟動本機的MSDTC服務
  
  exec masterxp_cmdshell net start msdtcno_output
  
  進行分布事務處理如果表用標識列做主鍵用下面的方法
  
  BEGIN DISTRIBUTED TRANSACTION
  
  同步刪除的數據
  
  delete from srv_lnktestdbo[user]
  where id not in(select id from [user])
  
  同步新增的數據
  
  insert into srv_lnktestdbo[user]
  select idnumbername from [user] where state is null
  
  同步修改的數據
  
  update srv_lnktestdbo[user] set
  number=bnumbername=bname
  from srv_lnktestdbo[user] a
  join [user] b on aid=bid
  where bstate=
  
  同步後更新本機的標志
  
  update [user] set state= where isnull(state)=
  COMMIT TRAN
  go
  
  創建作業定時執行數據同步的存儲過程
  
  if exists(SELECT from msdbsysjobs where name=數據處理)
  EXECUTE msdbdbosp_delete_job @job_name=數據處理
  exec msdbsp_add_job @job_name=數據處理
  
  創建作業步驟
  
  declare @sql varchar()@dbname varchar()
  select @sql=exec p_synchro 數據處理的命令
  @dbname=db_name() 執行數據處理的數據庫名
  exec msdbsp_add_jobstep @job_name=數據處理
  @step_name = 數據同步
  @subsystem = TSQL
  @database_name=@dbname
  @command = @sql
  @retry_attempts = 重試次數
  @retry_interval = 重試間隔
  
  創建調度
  
  EXEC msdbsp_add_jobschedule @job_name = 數據處理
  @name = 時間安排
  @freq_type = 每天
  @freq_interval = 每天執行一次
  @active_start_time = 點執行
  go
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22013.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.