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

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

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

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

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

 

/**/ /*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 gametext data for sale*/
-- delete the gametext not exists
DELETE  a  FROM  GameText a
     WHERE  a.TextID  NOT   IN ( SELECT  TextID  FROM  VendorDB.Vcsale.dbo.GameText)
-- update rows exist
UPDATE  a  SET  a.GameID  =  b.GameID,
        a.Detail  =  b.Detail,
        a.PageTitle  =  b.PageTitle,
        a.GameNotice  =  b.GameNotice,
        a.IsPicGame  =  b.IsPicGame
     FROM  GameText a
INNER   JOIN  VendorDB.Vcsale.dbo.GameText b
     ON  a.TextID  =  b.TextID
         AND  CHECKSUM(a.GameID,a.Detail,a.PageTitle,a.GameNotice,a.IsPicGame)
             !=
            CHECKSUM(b.GameID,b.Detail,b.PageTitle,b.GameNotice,b.IsPicGame)
-- insert new rows
INSERT  GameText
     SELECT  a. *   FROM  VendorDB.Vcsale.dbo.GameText a
LEFT   JOIN  GameText b
     ON  a.TextID  =  b.TextID
     WHERE  b.TextID  IS   NULL
/**/ /*end update gametext*/

/**/ /*begin update servertext data for sale*/
-- delete the servertext not exists
DELETE  a  FROM  ServerText a
     WHERE   NOT   EXISTS ( SELECT   1   FROM  VendorDB.Vcsale.dbo.ServerText  WHERE  TextID  =  a.TextID)
-- update rows exist
UPDATE  a  SET  a.ServerID  =  b.ServerID, a.PageTitle  =  b.PageTitle
     FROM  ServerText a
INNER   JOIN  VendorDB.Vcsale.dbo.ServerText b
     ON  a.TextID  =  b.TextID
         AND  CHECKSUM(a.ServerID,a.PageTitle)  !=  CHECKSUM(b.ServerID,b.PageTitle)
-- insert new rows
INSERT  ServerText
     SELECT  a. *   FROM  VendorDB.Vcsale.dbo.ServerText a
LEFT   JOIN  ServerText b
     ON  a.TextID  =  b.TextID
     WHERE  b.TextID  IS   NULL
/**/ /*end update serverText*/

/**/ /*begin update saletext data for sale*/
-- delete the saletext not exists
DELETE  a  FROM  SaleText a
     WHERE  a.TextID  NOT   IN ( SELECT  TextID  FROM  VendorDB.Vcsale.dbo.SaleText)
-- update rows exist
UPDATE  a  SET  a.WithWhat  =  b.WithWhat,
        a.Content  =  b.Content
     FROM  SaleText a
INNER   JOIN  VendorDB.Vcsale.dbo.SaleText b
     ON  a.TextID  =  b.TextID
         AND  CHECKSUM(a.WithWhat,a.Content)  !=  CHECKSUM(b.WithWhat,b.Content)
-- insert new rows
INSERT  SaleText
     SELECT  a. *   FROM  VendorDB.Vcsale.dbo.SaleText a
LEFT   JOIN  SaleText b
     ON  a.TextID  =  b.TextID
     WHERE  b.TextID  IS   NULL
/**/ /*end update saletext*/

/**/ /*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