今天干了件力活,手工写语句来完成数据库同步.

[fcuandy]

两个网站A,B放在不同的远程服务器上,各带一个sql server.物理距离比较远.
使用同一个后台进行管理.本来是放在一台服务器的,昨天移到国外一台服务器上,部分数据要共享,另外,部分数据需要更新.
比如新的客户信息,订单信息要从A复制到B,及客户在A站上更新的信息要写回B,B(与整个后台在一台服务器上)上数据经过改动后,又要更改A的部分数据.
通过设置复制可以实现,但比较烦琐,并且一些意外情况不容易控制(因为并不是数据对传或更新), 使用web service进行即时更新时比较好的解决办法. ,但程序改动量太大,时间要求的急,就花了几个小时干了件体力活,还好A上要用的表并不多,只有几十个.

1,建立链接服务器(以下代码中,是最初测试时这么做的,实际上建过一次就可以了,不用每次都重建并删除).
2,创建几个存储过程(本来一个就可以了,但考滤数据量大小及业务上对数据操作变更的频率,就建了几个,分为5mi,60mi,120mi,1day),来执行数据比对和更新操作.
3,创建几个对应的job,执行对应的存储过程.

哪位朋友有好的解决办法,请不吝赐教

以下代码,表和字段为节选.语句中定义的几个参数亦为节选,参数值我定期维护和更新,目的很简单,在聚集索引上过滤不必要操作的数据. 另外,具体的还有事务处理,这里就略去了.

说我贴子内容过长,只有下一贴了.

[fcuandy]

还是说太长,又删了些

/*add romate vendor database server*/
EXEC sp_addlinkedserver 
   'VendorDB', 
   '', 
   'MSDASQL',
   NULL,
   NULL,
   'DRIVER={SQL Server};SERVER=192.168.1.15;UID=webdev;PWD=web123dev;'
GO

DECLARE @maxCustomerID INT
DECLARE @maxSaleOrderID INT
DECLARE @maxSaleOrderItemID INT

SELECT @maxCustomerID = 0,@maxSaleOrderID = 0,@maxSaleOrderItemID = 0

/*begin update game data for sale*/
--delete the game not exists
DELETE a FROM Game a
	WHERE a.GameID NOT IN(SELECT GameID FROM VendorDB.Vcsale.dbo.Game)
--update rows exist
UPDATE a SET a.Name=b.Name,
		a.GoldName = b.GoldName,
		a.GoldUnit = b.GoldUnit,
		a.TradeMode = b.TradeMode,
		a.DirectMode = b.DirectMode,
		a.Type = b.Type,
		a.StockType = b.StockType,
		a.SaleStatus = b.SaleStatus
	FROM game a
INNER JOIN VendorDB.Vcsale.dbo.Game b
	ON a.GameID=b.GameID
		AND CHECKSUM(a.Name,a.GoldName,a.GoldUnit,a.TradeMode,a.DirectMode,a.Type,a.StockType,a.SaleStatus)
			!= 
			CHECKSUM(b.Name,b.GoldName,b.GoldUnit,b.TradeMode,b.DirectMode,b.Type,b.StockType,b.SaleStatus)
--insert new rows
INSERT Game
	SELECT a.* FROM VendorDB.Vcsale.dbo.Game a
LEFT JOIN Game b
	ON a.GameID=b.GameID
	WHERE b.GameID IS NULL
/*end update game data for sale*/

/*begin update gameserver data for sale*/
--delete the server not exists
DELETE a FROM GameServer a
	WHERE NOT EXISTS(SELECT 1 FROM VendorDB.Vcsale.dbo.GameServer WHERE a.GameServerID = GameServerID)
--update rows exist
UPDATE a SET a.GameID = b.GameID,
		a.Name = b.Name,
		a.SaleStatus = b.SaleStatus,
		a.Price = b.Price,
		a.SearchCode = b.SearchCode
	FROM GameServer a
INNER JOIN VendorDB.Vcsale.dbo.GameServer b
	ON a.GameServerID = b.GameServerID
		AND CHECKSUM(a.GameID,a.Name,a.SaleStatus,a.Price,a.SearchCode)
			!=
			CHECKSUM(b.GameID,b.Name,b.SaleStatus,b.Price,b.SearchCode)
--insert new rows
INSERT GameServer
	SELECT a.* FROM VendorDB.Vcsale.dbo.GameServer a
LEFT JOIN GameServer b
	ON a.GameServerID = b.GameServerID
	WHERE b.GameServerID IS NULL
/*end update gameserver data for sale*/


/*begin update tradeaddress data for sale*/
--delete the tradeaddress not exists
DELETE a FROM TradeAddress a
	WHERE a.TradeAddressID NOT IN(SELECT TradeAddressID FROM VendorDB.Vcsale.dbo.TradeAddress)
--update row exists
UPDATE a SET a.GameID = b.GameID,a.Address = b.Address
	FROM TradeAddress a
INNER JOIN VendorDB.Vcsale.dbo.TradeAddress b
	ON a.TradeAddressID = b.TradeAddressID
		AND CHECKSUM(a.GameID,a.Address) != CHECKSUM(b.GameID,b.Address)
--insert new rows
INSERT TradeAddress
	SELECT a.* FROM VendorDB.Vcsale.dbo.TradeAddress a
LEFT JOIN TradeAddress b
	ON a.TradeAddressID = b.TradeAddressID
	WHERE b.TradeAddressID IS NULL
/*end update tradeaddress*/

/*begin update systemconfig*/
UPDATE a SET a.SaleSiteInfoWhenClose = b.SaleSiteInfoWhenClose,
		a.SaleSiteStatus = b.SaleSiteStatus
	FROM SystemConfig a
INNER JOIN VendorDB.Vcsale.dbo.SystemConfig b
	ON 1=1 AND CHECKSUM(a.SaleSiteInfoWhenClose,a.SaleSiteStatus)
			!=
			CHECKSUM(b.SaleSiteInfoWhenClose,b.SaleSiteStatus)
/*end update systemconfig*/

/*begin update saleblock*/
--delete the saleblock not exists
DELETE a FROM SaleBlock a
	WHERE NOT EXISTS(SELECT 1 FROM VendorDB.Vcsale.dbo.SaleBlock WHERE SaleBlockID = a.SaleBlockID)
--update rows exist
UPDATE a SET a.GameID = b.GameID,
		a.Amount = b.Amount,
		a.ChargePercent = b.ChargePercent
	FROM SaleBlock a
INNER JOIN VendorDB.Vcsale.dbo.SaleBlock b
	ON a.SaleBlockID = b.SaleBlockID
		AND CHECKSUM(a.GameID,a.Amount,a.ChargePercent) != CHECKSUM(b.GameID,b.Amount,b.ChargePercent)
--insert new rows
INSERT SaleBlock 
	SELECT a.* FROM VendorDB.Vcsale.dbo.SaleBlock a
LEFT JOIN SaleBlock b
	ON a.SaleBlockID = b.SaleBlockID
	WHERE b.SaleBlockID IS NULL
/*end update saleblock*/

/*update customer*/
--write new customer to vendor db
INSERT VendorDB.Vcsale.dbo.Customer
	SELECT a.* FROM Customer a
		--WHERE a.CustomerID > @maxCustomerID
		--AND NOT EXISTS(SELECT 1 FROM FROM VendorDB.Vcsale.dbo WHERE CustomerID = a.CustomerID)
LEFT JOIN VendorDB.Vcsale.dbo.Customer b
	ON a.CustomerID = b.CustomerID
	WHERE b.CustomerID IS NULL
		AND a.CustomerID > @maxCustomerID
--update customer status for sale
UPDATE a SET	a.ClassID = b.ClassID,
		a.Consume = b.Consume
	FROM Customer a
INNER JOIN VendorDB.Vcsale.dbo.Customer b
	ON a.CustomerID = b.CustomerID
		AND a.MailAccount = b.MailAccount
		AND CHECKSUM(a.ClassID,a.Consume) != CHECKSUM(b.ClassID,b.Consume)
--update customer profile for vendor
UPDATE a SET a.Phone = b.Phone,
		a.TrueName = b.TrueName,
		a.Non_US_Phone = b.Non_US_Phone,
		a.Country = b.Country,
		a.Area = b.Area
	FROM VendorDB.Vcsale.dbo.Customer a
INNER JOIN Customer b
	ON a.CustomerID = b.CustomerID
		AND a.MailAccount = b.MailAccount
		AND CHECKSUM(a.Phone,a.TrueName,a.Non_US_Phone,a.Country,a.Area)
			!=
			CHECKSUM(b.Phone,b.TrueName,b.Non_US_Phone,b.Country,b.Area)
/*end update customer*/

/*update saleorder*/
--write new saleorder to vendor db
INSERT VendorDB.Vcsale.dbo.SaleOrder
	SELECT a.* FROM SaleOrder a
LEFT JOIN VendorDB.Vcsale.dbo.SaleOrder b
	ON a.OrderID = b.OrderID AND a.OrderSerial = b.OrderSerial
	WHERE b.OrderID IS NULL
		AND a.OrderID > @maxSaleOrderID
------
INSERT VendorDB.Vcsale.dbo.SaleOrderItem
	SELECT a.* FROM SaleOrderItem a
LEFT JOIN VendorDB.Vcsale.dbo.SaleOrderItem b
	ON a.SaleOrderItemID = b.SaleOrderItemID
	WHERE b.SaleOrderItemID IS NULL
		AND a.SaleOrderItemID > @maxSaleOrderItemID
--update sale order status for sale
UPDATE a SET a.PayTime = b.PayTime,
		a.Status = b.Status
	FROM SaleOrder a
INNER JOIN VendorDB.Vcsale.dbo.SaleOrder b
	ON a.OrderID=b.OrderID AND a.OrderSerial = b.OrderSerial
	AND CHECKSUM(a.PayTime,a.Status) != CHECKSUM(b.PayTime,b.Status)
	WHERE a.OrderID > @maxSaleOrderID
------
UPDATE a SET a.LeaveAmount = b.LeaveAmount,
		a.Status = b.Status
	FROM SaleOrderItem a
INNER JOIN VendorDB.Vcsale.dbo.SaleOrderItem b
	ON a.SaleOrderItemID = b.SaleOrderItemID
		AND a.OrderID = b.OrderID
		AND CHECKSUM(a.LeaveAmount,a.Status) != CHECKSUM(b.LeaveAmount,b.Status)
--update sale order item tradetime for vendor
UPDATE a SET a.TradeTime = b.TradeTime
	FROM VendorDB.Vcsale.dbo.SaleOrderItem a
INNER JOIN SaleOrderItem b
	ON a.SaleOrderItemID = b.SaleOrderItemID
		AND a.OrderID = b.OrderID
	WHERE a.OrderID>@maxSaleOrderID
/*end update saleorder*/


GO
/*drop romate vendor database server*/
EXEC sp_dropserver 'VendorDB'
GO

[-狙击手-]

SF

[-狙击手-]

1,建立链接服务器(以下代码中,是最初测试时这么做的,实际上建过一次就可以了,不用每次都重建并删除).



----

嘿嘿，你不会开始每次都重建并删除吧？？？？

[fcuandy]

没有.

这个我当然知道了.是因为我本机不需要建与测试服务器的连接,因为拿本机与测试服务器数据进行操作,所以这么写的.用过就删除,免得忘了.不喜欢把自己系统弄的乱乱的.

运行了6个多小时了,一切正常.数据量大以后还得改,这只是权宜之计.

[rouqu]

你狠

[areswang]

跟帖学习！

[playwarcraft]

SERVER=192.168.1.15;UID=webdev;PWD=web123dev;

這個這個。。。可以連上去伐?

[playwarcraft]

感覺還是用web service更新，
或者將異動資料以xml file傳送，再更新。
資料大的話直接同步sql server，會不會吃memory過重

[dobear_0922]

今天干了件牛活,手工写语句来完成数据库同步

[andy84]

今天干了件**活,手工写语句来完成数据库同步

[TopFans]

SERVER=192.168.1.15;UID=webdev;PWD=web123dev;

這個這個。。。可以連上去伐?

强汗。。。

[yowbell]

我建议楼主把所有的代码都公开,让我等小辈也仔细学习学习一些细节的东西,高手莫B我啊~~~~

[utpcb]

SERVER=192.168.1.15;UID=webdev;PWD=web123dev;

這個這個。。。可以連上去伐?

强汗。。。

192.168 的网段是局域网的


这个他是用了VPN 所以能链接的！

[lhqmcy]

看了,学了!恩，真好！

[青锋-SS]

强汉

[fcuandy]

感覺還是用web service更新，
或者將異動資料以xml file傳送，再更新。
資料大的話直接同步sql server，會不會吃memory過重
----------------------------------------------

以xml file传送,是一种方法,但有两个问题.
1,需要多出一个东西记录某条记录是否已传送过.未传送过的才生成xml file.比较烦琐
2,最重要的,安全性问题不好处理.

最好的当然还是web service同步更新,操作一条记录后就执行一次更新方法. 但网络如果短暂性阻塞时,会造成数据不一致.所以还是需要多一个表记录更新状态,另外程序改动的也比较大.

同在做同步,还跑得过来,等以后再想办法改进了,呵呵.

[-狙击手-]

学习

[thzgh]

XUEXI 学习