异地两台SQL服务器之间如何实现数据同步

[dghrkj]

一台SQL服务在深圳，一台SQL服务器在上海。

两台服务器的数据类型，资料都是一样的。

放在两地主要是为了两边使用的人员，查询输入快速。

上海的SQL相当于深圳SQL的一个备份，上海办公室中的SQL数据，需要定时传入到深圳的SQL服务器中来。

深圳办公室的人，在深圳的SQL服务器中输入的内容，也需要定时传入到上海的服务器中去。

最少应该每天晚上，将两边的资料同步一次。

这个要怎么来实现呢？

[xiequan2]

等经典笔记

[黄_瓜]

--参考: 
/* 
作者:邹建 
*/ 

/*--同步两个数据库的示例 

有数据 
srv1.库名..author有字段:id,name,phone, 
srv2.库名..author有字段:id,name,telphone,adress 

要求： 
srv1.库名..author增加记录则srv1.库名..author记录增加 
srv1.库名..author的phone字段更新，则srv1.库名..author对应字段telphone更新 
--*/ 

--大致的处理步骤 
--1.在  srv1  上创建连接服务器,以便在  srv1  中操作  srv2,实现同步 
exec  sp_addlinkedserver    'srv2','','SQLOLEDB','srv2的sql实例名或ip' 
exec  sp_addlinkedsrvlogin  'srv2','false',null,'用户名','密码' 
go 

--2.在  srv1  和  srv2  这两台电脑中,启动  msdtc(分布式事务处理服务),并且设置为自动启动 
我的电脑--控制面板--管理工具--服务--右键  Distributed  Transaction  Coordinator--属性--启动--并将启动类型设置为自动启动 
go 


--然后创建一个作业定时调用上面的同步处理存储过程就行了 

企业管理器 
--管理 
--SQL  Server代理 
--右键作业 
--新建作业 
--"常规"项中输入作业名称 
--"步骤"项 
--新建 
--"步骤名"中输入步骤名 
--"类型"中选择"Transact-SQL  脚本(TSQL)" 
--"数据库"选择执行命令的数据库 
--"命令"中输入要执行的语句:  exec  p_process  
--确定 
--"调度"项 
--新建调度 
--"名称"中输入调度名称 
--"调度类型"中选择你的作业执行安排 
--如果选择"反复出现" 
--点"更改"来设置你的时间安排 


然后将SQL  Agent服务启动,并设置为自动启动,否则你的作业不会被执行 

设置方法: 
我的电脑--控制面板--管理工具--服务--右键  SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定. 


--3.实现同步处理的方法2,定时同步 

--在srv1中创建如下的同步处理存储过程 
create  proc  p_process  
as 
--更新修改过的数据 
update  b  set  name=i.name,telphone=i.telphone 
from  srv2.库名.dbo.author  b,author  i 
where  b.id=i.id  and 
(b.name <> i.name  or  b.telphone <> i.telphone) 

--插入新增的数据 
insert  srv2.库名.dbo.author(id,name,telphone) 
select  id,name,telphone  from  author  i 
where  not  exists( 
select  *  from  srv2.库名.dbo.author  where  id=i.id) 

--删除已经删除的数据(如果需要的话) 
delete  b  
from  srv2.库名.dbo.author  b 
where  not  exists( 
select  *  from  author  where  id=b.id) 
go 

[黄_瓜]

SQL SERVER 2005 同步复制技术 

以下实现复制步骤（以快照复制为例） 

运行平台SQL SERVER 2005 

一、准备工作： 

1．建立一个 WINDOWS 用户，设置为管理员权限，并设置密码，作为发布快照文件的有效访问用户。 

2．在SQL SERVER下实现发布服务器和订阅服务器的通信正常（即可以互访）。打开1433端口，在防火墙中设特例 

3．在发布服务器上建立一个共享目录，作为发布快照文件的存放目录。例如：在D盘根目录下建文件夹名为SqlCopy 

4．设置SQL 代理（发布服务器和订阅服务器均设置）本篇文章发表于www.xker.com(小新技术网) 

打开服务(控制面板---管理工具---服务) 

---右击SQLSERVER AGENT---属性---登录---选择“此帐户“ 

---输入或选择第一步中创建的WINDOWS 用户 

---“密码“中输入该用户密码 

5．设置SQL SERVER 身份验证，解决连接时的权限问题（发布、订阅服务器均设置） 

步骤为：对象资源管理器----右击SQL实例-----属性----安全性----服务器身份验证------选“SQL Server和WINDOWS“，然后点确定 

6.开启SQL Server 2005的网络协议TCP/IP和管道命名协议并重启网络服务。 

7.在SQL Server中创建步骤1中对应的系统用户登陆名，作为发布数据库的拥有者（设置为dbo_owner和public）。 

8．以系统超级用户sa登陆SQL Server建立数据库和表。 

9．发布服务器和订阅服务器互相注册 

步骤如下：视图----单击以注册服务器----右键数据库引擎----新建服务器注册-----填写要注册的远程服务器名称------身份验证选“SQL Server验证“-----用户名(sa) 密码------创建组（也可不建）-----完成。 

10．对于只能用IP，不能用计算机名的，为其注册服务器别名 

二、开始： 

发布服务器配置（在发布服务器上配置发布和订阅） 

1． 选择 复制 节点 

2． 右键本地发布 ----下一步---------系统弹出对话框看提示----直到“指定快照文件夹“ 

----在“快照文件夹“中输入准备工作中创建的目录(指向步骤3所建的共享文件夹)------选择发布数据库-------选择发布类型-------选择订阅服务器类型-------选择要发布的对象------设置快照代理-------填写发布名称。本篇文章发表于www.xker.com(小新技术网) 

3． 右键本地订阅--------选择发布服务器-------选择订阅方式（如果是在服务器方订阅的话选择推送订阅反之 

选择请求订阅）-------填加订阅服务器--------选择代理计划（一般选择连续运行）---------其余选择默认项。 

至此, SQL SERVER 2005 同步复制就完成了。使用复制技术，用户可以将一份客户端的数据发布到多台服务器上，从而使不同的服务器用户都可以在权限的许可的范围内共享这份数据。复制技术可以确保分布在不同地点的数据自动同步更新，从而保证数据的一致性,就无需编程实现客户端和服务器端数据同步了！大大提高了工作效率！ 

[--小F--]

需要建立远程连接:
/*不同服务器数据库之间的数据操作*/

--创建链接服务器 
exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' 
exec sp_addlinkedsrvlogin  'ITSV ', 'false ',null, '用户名 ', '密码 ' 

--查询示例 
select * from ITSV.数据库名.dbo.表名 

--导入示例 
select * into 表 from ITSV.数据库名.dbo.表名 

--以后不再使用时删除链接服务器 
exec sp_dropserver  'ITSV ', 'droplogins ' 

--连接远程/局域网数据(openrowset/openquery/opendatasource) 
--1、openrowset 

--查询示例 
select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) 

--生成本地表 
select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) 

--把本地表导入远程表 
insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) 
select *from 本地表 

--更新本地表 
update b 
set b.列A=a.列A 
 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b 
on a.column1=b.column1 

--openquery用法需要创建一个连接 

--首先创建一个连接创建链接服务器 
exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' 
--查询 
select * 
FROM openquery(ITSV,  'SELECT *  FROM 数据库.dbo.表名 ') 
--把本地表导入远程表 
insert openquery(ITSV,  'SELECT *  FROM 数据库.dbo.表名 ') 
select * from 本地表 
--更新本地表 
update b 
set b.列B=a.列B 
FROM openquery(ITSV,  'SELECT * FROM 数据库.dbo.表名 ') as a  
inner join 本地表 b on a.列A=b.列A 

--3、opendatasource/openrowset 
SELECT   * 
FROM   opendatasource( 'SQLOLEDB ',  'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta 
--把本地表导入远程表 

[华夏小卒]

[Quote=引用 1 楼 xiequan2 的回复:]
等经典笔记
[/Quote]up

[wzy_love_sly]

最少应该每天晚上，将两边的资料同步一次

快照实现

[sdhdy]

链接服务器+数据库JOB(作业)。
连接服务器参考这个：
http://blog.csdn.net/sdhdy/archive/2009/05/25/4215311.aspx
数据库JOB参考这个：
http://blog.csdn.net/sdhdy/archive/2009/04/26/4126359.aspx

[黄_瓜]

[Quote=引用 6 楼 wzy_love_sly 的回复:]
最少应该每天晚上，将两边的资料同步一次

快照实现
[/Quote]
大大出山了

[wzy_love_sly]

2地查一个库就行了，非要复制到本地才能用？

[soft_wsx]

作业！+

>>>>>>soft_wsx>>>>>>
--数据备份与还原>>同步备用服务器
--1、完全备份主数据库
--2、使用带SANDBY子句的RESTORE DATABASE语句从主数据库的完全备份中还原生成备用数据库
--3、创建作业，使用BACKUP LOG定时对主数据库时行日志备份
--4、创建作业，使用带STANDBY子句的RESTORE LOG语句定时从主数据库的日志备份中恢复数据到备用数据库
--5、主数据库不可用时，使用带RECOVERY子句的RESTORE LOG语句使备用数据库升级为主数据库
----具体代码及说明如下
---->>首先、创建一个淙用的数据库（主数据库）
create database db_test_primary
on
(
   name=db_test_primary_data,
   filename='d:\db_test_primary_data.mdf')
log on
(
    name='db_test_primary_log',
    filename='d:\db_test_primary_log.ldf'
)
go

--在主数据库上创建表
create TABLE db_test_primary.dbo.db_test_primary1(id int)
insert db_test_primary.dbo.db_test_primary1 select top 1000 ID from dbo.sysobjects
go
--对主数据库进行备份
backup database db_test_primary
  to disk='d:\db_test_primary_data.bak'
  with format
go

--通过主数据库备份文件创建备用数据库（演示主数据库与这个备用数据库之间的同步)
restore database db_test_second
  from disk='d:\db_test_primary_data.bak'
  with replace,
  standby='d:\db_test_primary_bak.ldf',
  move 'db_test_primary_data' to 'd:\db_test_second_data.mdf',
  move 'db_test_primary_log' to 'd:\db_test_second_log.ldf'
  
--启动SQL AGENT服务（需要通过作业定时将主数据库同步到备用数据库）
exec master.dbo.xp_cmdshell 'net start sqlserveragent',no_output
go

--创建主服务数据库与备用服务器数据库之间同步的作业
declare @jobid uniqueidentifier
exec msdb.dbo.sp_add_job
      @job_id=@jobid output,
      @job_name=N'数据同步'
      
--创建同步处理步骤
exec msdb.dbo.sp_add_jobstep
     @job_id=@jobid,
     @step_name=N'数据同步',
     @subsystem='TSQL',
     @command=N'
          --主数据库中进行日志备份
          backup log db_test_primary
            to disk=''d:\test_log_primary.bak''
            with format
            
            --备用数据中还原主数据库的日志备份（应用主数据库中的最新变化）
            --注：实际应该对主数据库备份与备用数据库的还原作业应该分别在主服务器和备用服务器上建立，
                --并且备份文件应该放在主服务器和备用服务器都能访问的共享目录中
           restore log db_test_second
            from disk=''d:\test_log_primary.bak''
            with standby=''d:\test_log.ldf''',
      @retry_attempts=5,
      @retry_interval=5
--创建调度
exec msdb.dbo.sp_add_jobschedule
  @job_id=@jobid,
  @name='时间安排',
  @freq_TYPE=4,
  @freq_interval=1,
  @freq_subday_type=0x4,
  @freq_subday_interval=1
      
--添加目录服务器
exec msdb.dbo.sp_add_jobserver
  @job_ID=@jobid,
  @server_name=N'(local)'
go
--通过上述处理，主数据库与备用数据库之间的同步关系已经设置完成
--下面开始测试是否能实现同步
create table db_test_primary.dbo.db_test_primary3(id int)
go
insert db_test_primary.dbo.db_test_primary3 select top 5000 id from dbo.sysobjects
waitfor delay '00:01:30'
go

--查询一下备用数据库，看看同步是否成功

select * from db_test_second.dbo.db_test_primary3


drop table db_test_primary.dbo.db_test_primary3


--最后删除测试环境
drop database msdb.dbo.sp_delete_job
  @job_name=N'数据同步'

控制好时间！前提是先把日志备份传到远程服务器上！

[my_life_1997]

可以尝试一下数据库同步软件,如 SyncNavigator 。

[cjfire]

那两边服务器的IP地址有什么要求么？