SqlServer2008 數(shù)據(jù)庫(kù)同步的兩種方式(Sql JOB)

　　數(shù)據(jù)庫(kù)同步是一種比較常用的功能。以下結(jié)合我自己的體會(huì)整理的，如果有理解不完全或者有誤的地方望大牛不理賜教。下面介紹的就是數(shù)據(jù)庫(kù)同步的兩種方式：

　　1、SQL JOB的方式

　　sql Job的方式同步數(shù)據(jù)庫(kù)就是通過(guò)SQL語(yǔ)句，將一個(gè)數(shù)據(jù)源中的數(shù)據(jù)同步到目標(biāo)數(shù)據(jù)庫(kù)中。特點(diǎn)是它可以靈活的通過(guò)SQL的方式進(jìn)行數(shù)據(jù)庫(kù)之間的同步操作。可以在制定的時(shí)間時(shí)間作為任務(wù)計(jì)劃自動(dòng)執(zhí)行。缺點(diǎn)是需要寫(xiě)SQL來(lái)進(jìn)行操作。既然是數(shù)據(jù)庫(kù)之間的同步就涉及到數(shù)據(jù)庫(kù)之間的連接。建立連接是同步的第一步。SQL Server建立連接可以通過(guò)系統(tǒng)存儲(chǔ)過(guò)程建立【是否還有其他方式，我還不清楚】。存儲(chǔ)過(guò)程有以下幾個(gè)：sp_droplinkedsrvlogin、sp_dropserver、sp_addlinkedserver、sp_addlinkedsrvlogin。前面兩個(gè)是刪除數(shù)據(jù)庫(kù)之間連接的，后兩個(gè)是建立數(shù)據(jù)庫(kù)之間連接的。

刪除連接存儲(chǔ)過(guò)程參數(shù)用法如下：

a)sp_droplinkedsrvlogin

語(yǔ)法

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
     [ , [ @useself = ] 'useself' ]
     [ , [ @locallogin = ] 'locallogin' ]
     [ , [ @rmtuser = ] 'rmtuser' ]
     [ , [ @rmtpassword = ] 'rmtpassword' ]

參數(shù)

[@rmtsrvname =] 'rmtsrvname'

是應(yīng)用登錄映射的鏈接服務(wù)器名稱(chēng)。rmtsrvname 的數(shù)據(jù)類(lèi)型為 sysname，沒(méi)有默認(rèn)設(shè)置。

[@useself =] 'useself'

決定用于連接到遠(yuǎn)程服務(wù)器的登錄名稱(chēng)。useself 的數(shù)據(jù)類(lèi)型為 varchar(8)，默認(rèn)設(shè)置為 TRUE。true 值指定 SQL Server 驗(yàn)證的登錄使用其自己的憑據(jù)以連接到 rmtsrvname，而忽略 rmtuser 和 rmtpassword 參數(shù)。false 值指定 rmtuser 和 rmtpassword 參數(shù)用來(lái)連接到特定 locallogin 的 rmtsrvname。如果 rmtuser 和 rmtpassword 也設(shè)置為 NULL，則連接鏈接服務(wù)器不需使用任何登錄或密碼。對(duì)于 Windows NT 身份驗(yàn)證登錄而言，useself 為 true 則無(wú)效，除非 Microsoft Windows NT? 環(huán)境支持安全帳戶(hù)委托，并且，提供程序支持 Windows 身份驗(yàn)證（此時(shí)不再需要?jiǎng)?chuàng)建值為 true 的映射，不過(guò)創(chuàng)建仍然有效）。

[ @locallogin =] 'locallogin'

本地服務(wù)器上的登錄。locallogin 的數(shù)據(jù)類(lèi)型為 sysname，默認(rèn)設(shè)置為 NULL。NULL 值指定此條目將應(yīng)用于所有連接到 rmtsrvname 的本地登錄。如果值不為 NULL，則 locallogin 可以是一個(gè) SQL Server 登錄或 Windows NT 用戶(hù)。必須授予 Windows NT 用戶(hù)直接訪(fǎng)問(wèn) SQL Server 或通過(guò)其作為已授予訪(fǎng)問(wèn)權(quán)限的組的成員來(lái)訪(fǎng)問(wèn) SQL Server 的權(quán)限。

[@rmtuser =] 'rmtuser'

當(dāng) useself 為 false 時(shí)，用來(lái)連接 rmtsrvname 的用戶(hù)名，rmtuser 的數(shù)據(jù)類(lèi)型為 sysname，默認(rèn)設(shè)置為 NULL。

[@rmtpassword =] 'rmtpassword'

與 rmtuser 相關(guān)的密碼。rmtpassword 的數(shù)據(jù)類(lèi)型為 sysname，默認(rèn)設(shè)置為 NULL。

返回代碼值

0（成功）或 1（失敗）

注釋?zhuān)?/span>

當(dāng)用戶(hù)登錄到本地服務(wù)器并執(zhí)行分布式查詢(xún)，以訪(fǎng)問(wèn)鏈接服務(wù)器上的表時(shí)，本地服務(wù)器必須登錄鏈接服務(wù)器上，代表該用戶(hù)訪(fǎng)問(wèn)該表。使用 sp_addlinkedsrvlogin 來(lái)指定本地服務(wù)器登錄鏈接服務(wù)器的登錄憑據(jù)。

本地服務(wù)器上所有登錄和鏈接服務(wù)器上的遠(yuǎn)程登錄之間的默認(rèn)映射通過(guò)執(zhí)行 sp_addlinkedserver 自動(dòng)創(chuàng)建。默認(rèn)映射說(shuō)明連接到鏈接服務(wù)器代表的登錄時(shí)，SQL Server 使用本地登錄的用戶(hù)憑據(jù)（等同于將鏈接服務(wù)器的 @useself 設(shè)置為 true 時(shí)執(zhí)行 sp_addlinkedsrvlogin）。使用 sp_addlinkedsrvlogin 只可以更改特定的本地服務(wù)器的默認(rèn)映射或添加新映射。若要?jiǎng)h除默認(rèn)映射或任何其它映射，請(qǐng)使用 sp_droplinkedsrvlogin。

當(dāng)所有下列條件存在時(shí)，SQL Server 可以自動(dòng)地使用正在發(fā)出查詢(xún)的用戶(hù)的 Windows NT 安全憑據(jù)（Windows NT 用戶(hù)名稱(chēng)和密碼），以連接到鏈接服務(wù)器，而不是必須使用 sp_addlinkedsrvlogin 創(chuàng)建一個(gè)預(yù)設(shè)的登錄映射。

用法示例：

EXEC sp_droplinkedsrvlogin 'DBLink',Null

b)sp_dropserver

從本地 Microsoft? SQL Server? 上的已知遠(yuǎn)程和鏈接服務(wù)器列表中刪除服務(wù)器。

語(yǔ)法

sp_dropserver [ @server = ] 'server'
[ , [ @droplogins = ] { 'droplogins' | NULL} ]

參數(shù)

[@server =] 'server'

將被刪除的服務(wù)器。server 的數(shù)據(jù)類(lèi)型為 sysname，沒(méi)有默認(rèn)值。server 必須已經(jīng)存在。

[@droplogins =] 'droplogins' | NULL

指明如果指定了 droplogins，那么對(duì)于 server，那些相關(guān)的遠(yuǎn)程及鏈接服務(wù)器登錄也將被刪除。@droplogins 的數(shù)據(jù)類(lèi)型為 char(10)，帶有默認(rèn)值 NULL。

返回代碼值

0（成功）或 1（失敗）

注釋

如果某個(gè)服務(wù)器有相關(guān)的遠(yuǎn)程及鏈接服務(wù)器登錄條目，當(dāng)在該服務(wù)器上運(yùn)行 sp_dropserver 時(shí)，會(huì)導(dǎo)致一條錯(cuò)誤信息，該消息說(shuō)明：在刪除遠(yuǎn)程或鏈接服務(wù)器之前，必須先刪除相關(guān)的登錄。當(dāng)刪除服務(wù)器時(shí)，為了刪除服務(wù)器的所有遠(yuǎn)程及鏈接服務(wù)器登錄，請(qǐng)使用 droplogins 參數(shù)。

不能在用戶(hù)定義的事務(wù)內(nèi)執(zhí)行 sp_dropserver。

權(quán)限

只有 sysadmin 或 setupadmin 固定服務(wù)器角色的成員才可以執(zhí)行 sp_dropserver。

用法示例：

EXEC sp_dropserver 'DBLink'

建立連接存儲(chǔ)過(guò)程參數(shù)用法如下：

a)sp_addlinkedserver

創(chuàng)建一個(gè)鏈接的服務(wù)器，使其允許對(duì)分布式的、針對(duì) OLE DB 數(shù)據(jù)源的異類(lèi)查詢(xún)進(jìn)行訪(fǎng)問(wèn)。在使用 sp_addlinkedserver 創(chuàng)建鏈接的服務(wù)器之后，此服務(wù)器就可以執(zhí)行分布式查詢(xún)。如果鏈接服務(wù)器定義為 Microsoft? SQL Server?，則可執(zhí)行遠(yuǎn)程存儲(chǔ)過(guò)程。

語(yǔ)法

sp_addlinkedserver [ @server = ] 'server'
     [ , [ @srvproduct = ] 'product_name' ]
     [ , [ @provider = ] 'provider_name' ]
     [ , [ @datasrc = ] 'data_source' ]
     [ , [ @location = ] 'location' ]
     [ , [ @provstr = ] 'provider_string' ]
     [ , [ @catalog = ] 'catalog' ]

參數(shù)

[ @server = ] 'server'

要?jiǎng)?chuàng)建的鏈接服務(wù)器的本地名稱(chēng)，server 的數(shù)據(jù)類(lèi)型為 sysname，沒(méi)有默認(rèn)設(shè)置。

如果有多個(gè) SQL Server 實(shí)例，server 可以為 servername\instancename。此鏈接的服務(wù)器可能會(huì)被引用為下面示例的數(shù)據(jù)源：

SELECT *FROM    [servername\instancename.]pubs.dbo.authors.

如果未指定 data_source，則服務(wù)器為該實(shí)例的實(shí)際名稱(chēng)。

[ @srvproduct = ] 'product_name'

要添加為鏈接服務(wù)器的 OLE DB 數(shù)據(jù)源的產(chǎn)品名稱(chēng)。product_name 的數(shù)據(jù)類(lèi)型為 nvarchar(128)，默認(rèn)設(shè)置為 NULL。如果是 SQL Server，則不需要指定 provider_name、data_source、location、provider_string 以及目錄。

[ @provider = ] 'provider_name'

與此數(shù)據(jù)源相對(duì)應(yīng)的 OLE DB 提供程序的唯一程序標(biāo)識(shí)符 (PROGID)。provider_name 對(duì)于安裝在當(dāng)前計(jì)算機(jī)上指定的 OLE DB 提供程序必須是唯一的。provider_name 的數(shù)據(jù)類(lèi)型為nvarchar(128)，默認(rèn)設(shè)置為 NULL。OLE DB 提供程序應(yīng)該用給定的 PROGID 在注冊(cè)表中注冊(cè)。

[ @datasrc = ] 'data_source'

由 OLE DB 提供程序解釋的數(shù)據(jù)源名稱(chēng)。data_source 的數(shù)據(jù)類(lèi)型為 nvarchar(4000)，默認(rèn)設(shè)置為 NULL。data_source 被當(dāng)作 DBPROP_INIT_DATASOURCE 屬性傳遞以便初始化 OLE DB 提供程序。

當(dāng)鏈接的服務(wù)器針對(duì)于 SQL Server OLE DB 提供程序創(chuàng)建時(shí)，可以按照 servername\instancename 的形式指定 data_source，它可以用來(lái)連接到運(yùn)行于特定計(jì)算機(jī)上的 SQL Server 的特定實(shí)例上。servername 是運(yùn)行 SQL Server 的計(jì)算機(jī)名稱(chēng)，instancename 是用戶(hù)將被連接到的特定 SQL Server 實(shí)例的名稱(chēng)。

[ @location = ] 'location'

OLE DB 提供程序所解釋的數(shù)據(jù)庫(kù)的位置。location 的數(shù)據(jù)類(lèi)型為 nvarchar(4000)，默認(rèn)設(shè)置為 NULL。location 作為 DBPROP_INIT_LOCATION 屬性傳遞以便初始化 OLE DB 提供程序。

[ @provstr = ] 'provider_string'

OLE DB 提供程序特定的連接字符串，它可標(biāo)識(shí)唯一的數(shù)據(jù)源。provider_string 的數(shù)據(jù)類(lèi)型為 nvarchar(4000)，默認(rèn)設(shè)置為 NULL。Provstr 作為 DBPROP_INIT_PROVIDERSTRING 屬性傳遞以便初始化 OLE DB 提供程序。

當(dāng)針對(duì) Server OLE DB 提供程序提供了鏈接服務(wù)器后，可將 SERVER 關(guān)鍵字用作 SERVER=servername\instancename 來(lái)指定實(shí)例，以指定特定的 SQL Server 實(shí)例。servername 是 SQL Server 在其上運(yùn)行的計(jì)算機(jī)名稱(chēng)，instancename 是用戶(hù)連接到的特定的 SQL Server 實(shí)例名稱(chēng)。

[ @catalog = ] 'catalog'

建立 OLE DB 提供程序的連接時(shí)所使用的目錄。catalog 的數(shù)據(jù)類(lèi)型為sysname，默認(rèn)設(shè)置為 NULL。catalog 作為 DBPROP_INIT_CATALOG 屬性傳遞以便初始化 OLE DB 提供程序。

返回代碼值

0（成功）或 1（失敗）

結(jié)果集

如果沒(méi)有指定參數(shù)，則 sp_addlinkedserver 返回此消息：

Procedure 'sp_addlinkedserver' expects parameter '@server', which was not supplied.

使用適當(dāng) OLE DB 提供程序和參數(shù)的 sp_addlinkedserver 返回此消息：

Server added.
用法示例：
EXEC sp_addlinkedserver 'DBLink', ' ', 'SQLOLEDB', "10.0.222.12"

b)sp_addlinkedsrvlogin創(chuàng)建或更新本地 Microsoft? SQL Server? 實(shí)例上的登錄與鏈接服務(wù)器上遠(yuǎn)程登錄之間的映射。
語(yǔ)法
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
    [ , [ @useself = ] 'useself' ]
    [ , [ @locallogin = ] 'locallogin' ]
    [ , [ @rmtuser = ] 'rmtuser' ]
    [ , [ @rmtpassword = ] 'rmtpassword' ]
參數(shù)
[@rmtsrvname =] 'rmtsrvname'
是應(yīng)用登錄映射的鏈接服務(wù)器名稱(chēng)。rmtsrvname 的數(shù)據(jù)類(lèi)型為 sysname，沒(méi)有默認(rèn)設(shè)置。
[@useself =] 'useself'
決定用于連接到遠(yuǎn)程服務(wù)器的登錄名稱(chēng)。useself 的數(shù)據(jù)類(lèi)型為 varchar(8)，默認(rèn)設(shè)置為 TRUE。true 值指定 SQL Server 驗(yàn)證的登錄使用其自己的憑據(jù)以連接到

 rmtsrvname，而忽略 rmtuser 和 rmtpassword 參數(shù)。false 值指定 rmtuser 和 rmtpassword 參數(shù)用來(lái)連接到特定 locallogin 的 rmtsrvname。

如果 rmtuser 和 rmtpassword 也設(shè)置為 NULL，則連接鏈接服務(wù)器不需使用任何登錄或密碼。對(duì)于 Windows NT 身份驗(yàn)證登錄而言，useself 為 true 則無(wú)效，

除非 Microsoft Windows NT? 環(huán)境支持安全帳戶(hù)委托，并且，提供程序支持 Windows 身份驗(yàn)證（此時(shí)不再需要?jiǎng)?chuàng)建值為 true 的映射，不過(guò)創(chuàng)建仍然有效）。
[ @locallogin =] 'locallogin'
本地服務(wù)器上的登錄。locallogin 的數(shù)據(jù)類(lèi)型為 sysname，默認(rèn)設(shè)置為 NULL。NULL 值指定此條目將應(yīng)用于所有連接到 rmtsrvname 的本地登錄。如果值不為 NULL，

則 locallogin 可以是一個(gè) SQL Server 登錄或 Windows NT 用戶(hù)。必須授予 Windows NT 用戶(hù)直接訪(fǎng)問(wèn) SQL Server 或通過(guò)其作為已授予訪(fǎng)問(wèn)權(quán)限的組的成員來(lái)訪(fǎng)問(wèn) 

SQL Server 的權(quán)限。

[@rmtuser =] 'rmtuser'

當(dāng) useself 為 false 時(shí)，用來(lái)連接 rmtsrvname 的用戶(hù)名，rmtuser 的數(shù)據(jù)類(lèi)型為 sysname，默認(rèn)設(shè)置為 NULL。

[@rmtpassword =] 'rmtpassword'

與 rmtuser 相關(guān)的密碼。rmtpassword 的數(shù)據(jù)類(lèi)型為 sysname，默認(rèn)設(shè)置為 NULL。
返回代碼值
0（成功）或 1（失敗）
注釋
當(dāng)用戶(hù)登錄到本地服務(wù)器并執(zhí)行分布式查詢(xún)，以訪(fǎng)問(wèn)鏈接服務(wù)器上的表時(shí)，本地服務(wù)器必須登錄鏈接服務(wù)器上，代表該用戶(hù)訪(fǎng)問(wèn)該表。使用 sp_addlinkedsrvlogin 來(lái)指定本地服

務(wù)器登錄鏈接服務(wù)器的登錄憑據(jù)。
本地服務(wù)器上所有登錄和鏈接服務(wù)器上的遠(yuǎn)程登錄之間的默認(rèn)映射通過(guò)執(zhí)行 sp_addlinkedserver 自動(dòng)創(chuàng)建。默認(rèn)映射說(shuō)明連接到鏈接服務(wù)器代表的登錄時(shí)，SQL Server 

使用本地登錄的用戶(hù)憑據(jù)（等同于將鏈接服務(wù)器的 @useself 設(shè)置為 true 時(shí)執(zhí)行 sp_addlinkedsrvlogin）。使用 sp_addlinkedsrvlogin 只可以更改特定的本地服務(wù)器

的默認(rèn)映射或添加新映射。若要?jiǎng)h除默認(rèn)映射或任何其它映射，請(qǐng)使用 sp_droplinkedsrvlogin。
當(dāng)所有下列條件存在時(shí)，SQL Server 可以自動(dòng)地使用正在發(fā)出查詢(xún)的用戶(hù)的 Windows NT 安全憑據(jù)（Windows NT 用戶(hù)名稱(chēng)和密碼），以連接到鏈接服務(wù)器，而不是必須使用
 sp_addlinkedsrvlogin 創(chuàng)建一個(gè)預(yù)設(shè)的登錄映射。
使用 Windows 身份驗(yàn)證模式，用戶(hù)連接到 SQL Server。

在客戶(hù)端和發(fā)送服務(wù)器上安全帳戶(hù)委托是可用的。

提供程序支持 Windows 身份驗(yàn)證模式（例如，運(yùn)行于 Windows NT 上的 SQL Server）。
使用映射（此映射通過(guò)在本地 SQL Server 上執(zhí)行 sp_addlinkedsrvlogin 定義）的鏈接服務(wù)器執(zhí)行身份驗(yàn)證后，遠(yuǎn)程數(shù)據(jù)庫(kù)中單獨(dú)對(duì)象的權(quán)限由鏈接服務(wù)器決定，

而不是由本地服務(wù)器決定。
不能從用戶(hù)定義的事務(wù)中執(zhí)行 sp_addlinkedsrvlogin。
權(quán)限
只有 sysadmin 和 securityadmin 固定服務(wù)器角色的成員才可以執(zhí)行 sp_addlinkedsrvlogin。
用法示例
EXEC sp_addlinkedsrvlogin 'DBLink', 'false',null, 'ILM', '111111

介紹完建立連接的方式，下面正式介紹SQL JOB的開(kāi)發(fā)。直接用步驟介紹了。
a)新建作業(yè)。如下圖：
a、  新建作業(yè)。如下圖：
b、  點(diǎn)擊【新建作業(yè)】對(duì)話(huà)框的【常規(guī)】選項(xiàng)卡，填寫(xiě) SQL JOB的名稱(chēng)。如下圖
c、  點(diǎn)擊【新建作業(yè)】對(duì)話(huà)框的【步驟】選項(xiàng)卡，填寫(xiě) 步驟名稱(chēng)。



d、  進(jìn)行作業(yè)的第一個(gè)步驟------建立數(shù)據(jù)庫(kù)連接操作。如下圖： 

e、  進(jìn)行作業(yè)的第下一個(gè)步驟。如下圖：
e、設(shè)置作業(yè)執(zhí)行計(jì)劃
f、點(diǎn)擊確定，完成SQL JOB的開(kāi)發(fā)。
g、完成后作業(yè)下面就多了剛開(kāi)發(fā)的作業(yè)。如下:

h、作業(yè)制作完成后，可以立即讓它執(zhí)行。如果不讓它執(zhí)行，它也會(huì)在按照制定頻率執(zhí)行。測(cè)試作業(yè)：