時間:2024-02-05 11:03作者:下載吧人氣:23
緣由
日常工作中經(jīng)常遇到類似的問題:把某個服務(wù)器上的某些指定的表同步到另外一臺服務(wù)器。
類似需求用SSIS或者其他ETL工作很容易實現(xiàn),比如用SSIS的話,就會會存在相當(dāng)一部分反復(fù)的手工操作。
建源的數(shù)據(jù)庫信息,目標(biāo)的數(shù)據(jù)庫信息,如果是多個表,需要一個一個地拉source和target,然后一個一個地mapping,然后運行實現(xiàn)數(shù)據(jù)同步。
然后很可能,這個workflow使用也就這么一次,就壽終正寢了,卻一樣要浪費時間去做這個ETL。
快速數(shù)據(jù)同步實現(xiàn)
于是在想,可不可能快速實現(xiàn)類似需求,盡最大程度減少重復(fù)的手工操作?類似基于命令行的方式,簡單快捷,不需要太多的手動操作。
于是就有了本文,基于Python(目的是順便熟悉一下Python的語法),快速實現(xiàn)SQL Server的數(shù)據(jù)庫之間的數(shù)據(jù)同步操作,后面又稍微擴展了一下,可以實現(xiàn)不同服務(wù)器的數(shù)據(jù)庫之間的表結(jié)構(gòu),表對應(yīng)的數(shù)據(jù),存儲過程,函數(shù),用戶自定義類型表(user define table type)的同步
目前支持在兩個SQL Server數(shù)據(jù)源之間:每次同步一張或者多張表/存儲過程,也可以同步整個數(shù)據(jù)庫的所有表/存儲過程(以及表/存儲過程依賴的其他數(shù)據(jù)庫對象)。
支持sqlserver2012以上版本
需要考慮到一些基本的校驗問題:在源服務(wù)器上,需要同步的對象是否存在,或者輸入的對象是否存在于源服務(wù)器的數(shù)據(jù)庫里。
在目標(biāo)服務(wù)器上,對于表的同步:
1,表的存在依賴于schema,需要考慮到表的schema是否存在,如果不存在先在target庫上創(chuàng)建表對應(yīng)的schema
2,target表中是否有數(shù)據(jù)?如果有數(shù)據(jù),是否以覆蓋的方式執(zhí)行
對于存儲過程的同步:
1,類似于表,需要考慮存儲過程的schema是否存在,如果不存在先在target庫上創(chuàng)建表對應(yīng)的schema
2,類似于表,arget數(shù)據(jù)庫中是否已經(jīng)存在對應(yīng)的存儲過程,是否以覆蓋的方式執(zhí)行
3,存儲過程可能依賴于b表,某些函數(shù),用戶自定義表變量等等,同步存儲過程的時候需要先同步依賴的對象,這一點比較復(fù)雜,實現(xiàn)過程中遇到在很多很多的坑
可能存在對象A依賴于對象B,對象B依賴于對象C……,這里有點遞歸的意思
這一點導(dǎo)致了重構(gòu)大量的代碼,一開始都是直來直去的同步,無法實現(xiàn)這個邏輯,切實體會到代碼的“單一職責(zé)”原則
參數(shù)說明
參數(shù)說明如下,大的包括四類:
1,源服務(wù)器信息 (服務(wù)器地址,實例名,數(shù)據(jù)庫名稱,用戶名,密碼),沒有用戶名密碼的情況下,使用windows身份認(rèn)證模式
2,目標(biāo)服務(wù)器信息(服務(wù)器地址,實例名,數(shù)據(jù)庫名稱,用戶名,密碼),沒有用戶名密碼的情況下,使用windows身份認(rèn)證模式
3,同步的對象類型以及對象
4,同步的對象在目標(biāo)服務(wù)器上存在的情況下,是否強制覆蓋
其實在同步數(shù)據(jù)的時候,也可以把需要同步的行數(shù)提取出來做參數(shù),比較簡單,這里暫時沒有做。
比如需要快速搭建一個測試環(huán)境,需要同步所有的表結(jié)構(gòu)和每個表的一部分?jǐn)?shù)據(jù)即可。
表以及數(shù)據(jù)同步
表同步的原理是,創(chuàng)建目標(biāo)表,遍歷源數(shù)據(jù)的表,生成insert into values(***),(***),(***)格式的sql,然后插入目標(biāo)數(shù)據(jù)庫,這里大概步驟如下:
1,表依賴于schema,所以同步表之前先同步schema
2,強制覆蓋的情況下,會drop掉目標(biāo)表(如果存在的話),防止目標(biāo)表與源表結(jié)構(gòu)不一致,非強制覆蓋的情況下,如果字段不一致,則拋出異常
3,同步表結(jié)構(gòu),包括字段,索引,約束等等,但是無法支持外鍵,刻意去掉了外鍵,想想為什么?因吹斯汀。
4,需要篩選出來非計算列字段,insert語句只能是非計算列字段(又導(dǎo)致重構(gòu)了部分代碼)
5,轉(zhuǎn)義處理,在拼湊SQL的時候,需要進行轉(zhuǎn)義處理,否則會導(dǎo)致SQL語句錯誤,目前處理了字符串中的’字符,二進制字段,時間字段的轉(zhuǎn)義處理(最容易發(fā)生問題的地方)
6,鑒于insert into values(***),(***),(***)
語法上允許的最大值是1000,因此每生成1000條數(shù)據(jù),就同步一次
7,自增列的identity_insert
標(biāo)識打開與關(guān)閉處理
使用如下參數(shù),同步源數(shù)據(jù)庫的三張表到目標(biāo)數(shù)據(jù)庫,因為這里是在本機命名實例下測試,因此實例名和端口號輸入
執(zhí)行同步的效果
說明:
1,如果輸入obj_type=”tab” 且-obj=為None的情況下,會同步源數(shù)據(jù)庫中的所有表。
2,這個效率取決于機器性能和網(wǎng)絡(luò)傳輸,本機測試的話,每秒中可以提交3到4次,也就是每秒鐘可以提交3000~4000行左右的數(shù)據(jù)。
已知的問題:
1,當(dāng)表的索引為filter index的時候,無法生成包含where條件的索引創(chuàng)建語句,那個看起來蛋疼的表結(jié)構(gòu)導(dǎo)出語句,暫時沒時間改它。
2,暫時不支持其他少用的類型字段,比如地理空間字段什么的。
存儲過程對象的同步
存儲過程同步的原理是,在源數(shù)據(jù)庫上生成創(chuàng)建存儲過程的語句,然后寫入目標(biāo)庫,這里大概步驟如下:
1,存儲過程依賴于schema,所以同步存儲過程之前先同步schema(同表)
2,同步的過程會檢查依賴對象,如果依賴其他對象,暫停當(dāng)前對象同步,先同步依賴對象
3,重復(fù)第二步驟,直至完成
4,對于存儲過程的同步,如果是強制覆蓋的話,強制覆蓋僅僅對存儲過程自己生效(刪除&重建),對依賴對象并不生效,如果依賴對象不存在,就創(chuàng)建,否則不做任何事情
使用如下參數(shù),同步源數(shù)據(jù)庫的兩個存儲過程到目標(biāo)數(shù)據(jù)庫,因為這里是在本機命名實例下測試,因此實例名和端口號輸入
說明:測試要同步的存儲過程之一為[dbo].[sp_test01],它依賴于其他兩個對象:dbo.table01和dbo.fn_test01()
create proc [dbo].[sp_test01] as begin set no count on; delete from dbo.table01 where id = 1000 select dbo.fn_test01() end
網(wǎng)友評論