`
peizhiinfo
  • 浏览: 1417407 次
文章分类
社区版块
存档分类
最新评论

SqlServer2008 数据库同步的两种方式(采用Sql JOB方式)

 
阅读更多

在大型系统架构中,随着数据量几何式的增长及基于数据安全的考虑等,通常会使用的数据库集群(也称作群集)的解决方案。不管是用来做数据库系统的负载均衡,还是用来做双机热备(故障转移,防止数据库服务器宕机等),都必须及时同步数据(当然,数据同步越快越好,数据完整性还要有保障)。

所以,数据库的同步是一种比较常用的功能。以下结合我自己的体会整理的,如果有理解不完全或者有误的地方望大牛不理赐教。下面介绍的就是数据库同步的两种方式:

  1、SQL JOB的方式

  sql Job的方式同步数据库就是通过SQL语句,将一个数据源中的数据同步到目标数据库中。特点是它可以灵活的通过SQL的方式进行数据库之间的同步操作。可 以在制定的时间时间作为任务计划自动执行。缺点是需要写SQL来进行操作。既然是数据库之间的同步就涉及到数据库之间的连接。建立连接是同步的第一步。 SQL Server建立连接可以通过系统存储过程建立【是否还有其他方式,我还不清楚】。存储过程有以下几个:sp_droplinkedsrvlogin、 sp_dropserver、sp_addlinkedserver、sp_addlinkedsrvlogin。前面两个是删除数据库之间连接的,后两 个是建立数据库之间连接的。

删除连接存储过程参数用法如下:

a)sp_droplinkedsrvlogin

语法

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

[,[@rmtuser =]'rmtuser']
[,[@rmtpassword =]'rmtpassword']

参数

[@rmtsrvname=]'rmtsrvname'

是应用登录映射的链接服务器名称。rmtsrvname的数据类型为sysname,没有默认设置。

[@useself =]'useself'

决定用于连接到远程服务器的登录名称。useself的数据类型为varchar(8),默认设置为 TRUE。true值指定 SQL Server 验证的登录使用其自己的凭据以连接到rmtsrvname,而忽略rmtuserrmtpassword参数。false值指定rmtuserrmtpassword参数用来连接到特定localloginrmtsrvname。如果rmtuserrmtpassword也设置为 NULL,则连接链接服务器不需使用任何登录或密码。对于 Windows NT 身份验证登录而言,useselftrue则无效,除非 Microsoft Windows NT® 环境支持安全帐户委托,并且,提供程序支持 Windows 身份验证(此时不再需要创建值为true的映射,不过创建仍然有效)。

[@locallogin=]'locallogin'

本地服务器上的登录。locallogin的数据类型为sysname,默认设置为 NULL。NULL 值指定此条目将应用于所有连接到rmtsrvname的本地登录。如果值不为 NULL,则locallogin可以是一个 SQL Server 登录或 Windows NT 用户。必须授予 Windows NT 用户直接访问 SQL Server 或通过其作为已授予访问权限的组的成员来访问 SQL Server 的权限。

[@rmtuser =]'rmtuser'

useselffalse时,用来连接rmtsrvname的用户名,rmtuser的数据类型为sysname,默认设置为 NULL。

[@rmtpassword =]'rmtpassword'

rmtuser相关的密码。rmtpassword的数据类型为sysname,默认设置为 NULL。

返回代码值

0(成功)或 1(失败)

注释:

当用户登录到本地服务器并执行分布式查询,以访问链接服务器上的表时,本地服务器必须登录链接服务器上,代表该用户访问该表。使用sp_addlinkedsrvlogin来指定本地服务器登录链接服务器的登录凭据。

本地服务器上所有登录和链接服务器上的远程登录之间的默认映射通过执行sp_addlinkedserver自动创建。默认映射说明连接到链接服务器代表的登录时,SQL Server 使用本地登录的用户凭据(等同于将链接服务器的@useself设置为true时执行sp_addlinkedsrvlogin)。使用sp_addlinkedsrvlogin只可以更改特定的本地服务器的默认映射或添加新映射。若要删除默认映射或任何其它映射,请使用sp_droplinkedsrvlogin

当所有下列条件存在时,SQL Server 可以自动地使用正在发出查询的用户的 Windows NT 安全凭据(Windows NT 用户名称和密码),以连接到链接服务器,而不是必须使用sp_addlinkedsrvlogin创建一个预设的登录映射。

用法示例:

EXEC sp_droplinkedsrvlogin 'DBLink',Null

b)sp_dropserver

从本地 Microsoft® SQL Server™ 上的已知远程和链接服务器列表中删除服务器。

语法

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

参数

[@server=]'server'

将被删除的服务器。server的数据类型为sysname,没有默认值。server必须已经存在。

[@droplogins=]'droplogins'| NULL

指明如果指定了droplogins,那么对于server,那些相关的远程及链接服务器登录也将被删除。@droplogins的数据类型为char(10),带有默认值 NULL。

返回代码值

0(成功)或 1(失败)

注释

如果某个服务器有相关的远程及链接服务器登录条目,当在该服务器上运行sp_dropserver时,会导致一条错误信息,该消息说明:在删除远程或链接服务器之前,必须先删除相关的登录。当删除服务器时,为了删除服务器的所有远程及链接服务器登录,请使用droplogins参数。

不能在用户定义的事务内执行sp_dropserver

权限

只有sysadminsetupadmin固定服务器角色的成员才可以执行sp_dropserver

用法示例:

EXEC sp_dropserver 'DBLink'

建立连接存储过程参数用法如下:

a)sp_addlinkedserver

创建一个链接的服务器,使其允许对分布式的、针对 OLE DB 数据源的异类查询进行访问。在使用sp_addlinkedserver创建链接的服务器之后,此服务器就可以执行分布式查询。如果链接服务器定义为 Microsoft® SQL Server™,则可执行远程存储过程。

语法

sp_addlinkedserver[@server=]'server'
[,[@srvproduct=]'product_name']

[,[@provider=]'provider_name']
[,[@datasrc=]'data_source']
[,[@location=]'location']
[,[@provstr=]'provider_string']
[,[@catalog=]'catalog']

参数

[@server =]'server'

要创建的链接服务器的本地名称,server的数据类型为sysname,没有默认设置。

如果有多个 SQL Server 实例,server可以为servername\instancename。此链接的服务器可能会被引用为下面示例的数据源:

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

如果未指定data_source,则服务器为该实例的实际名称。

[@srvproduct =]'product_name'

要添加为链接服务器的 OLE DB 数据源的产品名称。product_name的数据类型为nvarchar(128),默认设置为 NULL。如果是SQL Server,则不需要指定provider_namedata_sourcelocationprovider_string以及目录。

[@provider =]'provider_name'

与此数据源相对应的 OLE DB 提供程序的唯一程序标识符 (PROGID)。provider_name对于安装在当前计算机上指定的 OLE DB 提供程序必须是唯一的。provider_name的数据类型为nvarchar(128),默认设置为 NULL。OLE DB 提供程序应该用给定的 PROGID 在注册表中注册。

[@datasrc =]'data_source'

由 OLE DB 提供程序解释的数据源名称。data_source的数据类型为nvarchar(4000),默认设置为 NULL。data_source被当作 DBPROP_INIT_DATASOURCE 属性传递以便初始化 OLE DB 提供程序。

当链接的服务器针对于 SQL Server OLE DB 提供程序创建时,可以按照servername\instancename的形式指定data_source,它可以用来连接到运行于特定计算机上的 SQL Server 的特定实例上。servername是运行 SQL Server 的计算机名称,instancename是用户将被连接到的特定 SQL Server 实例的名称。

[@location =]'location'

OLE DB 提供程序所解释的数据库的位置。location的数据类型为nvarchar(4000),默认设置为 NULL。location作为 DBPROP_INIT_LOCATION 属性传递以便初始化 OLE DB 提供程序。

[@provstr =]'provider_string'

OLE DB 提供程序特定的连接字符串,它可标识唯一的数据源。provider_string的数据类型为nvarchar(4000),默认设置为 NULL。Provstr作为 DBPROP_INIT_PROVIDERSTRING 属性传递以便初始化 OLE DB 提供程序。

当针对 Server OLE DB 提供程序提供了链接服务器后,可将 SERVER 关键字用作 SERVER=servername\instancename来指定实例,以指定特定的 SQL Server 实例。servername是 SQL Server 在其上运行的计算机名称,instancename是用户连接到的特定的 SQL Server 实例名称。

[@catalog =]'catalog'

建立 OLE DB 提供程序的连接时所使用的目录。catalog的数据类型为sysname,默认设置为 NULL。catalog作为 DBPROP_INIT_CATALOG 属性传递以便初始化 OLE DB 提供程序。

返回代码值

0(成功)或 1(失败)

结果集

如果没有指定参数,则sp_addlinkedserver返回此消息:

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

使用适当 OLE DB 提供程序和参数的sp_addlinkedserver返回此消息:

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

b)sp_addlinkedsrvlogin

创建或更新本地 Microsoft® SQL Server™ 实例上的登录与链接服务器上远程登录之间的映射。

语法

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

[,[@rmtuser =]'rmtuser']
[,[@rmtpassword =]'rmtpassword']

参数

[@rmtsrvname=]'rmtsrvname'

是应用登录映射的链接服务器名称。rmtsrvname的数据类型为sysname,没有默认设置。

[@useself =]'useself'

决定用于连接到远程服务器的登录名称。useself的数据类型为varchar(8),默认设置为 TRUE。true值指定 SQL Server 验证的登录使用其自己的凭据以连接到


rmtsrvname,而忽略rmtuserrmtpassword参数。false值指定rmtuserrmtpassword参数用来连接到特定localloginrmtsrvname


如果rmtuserrmtpassword也设置为 NULL,则连接链接服务器不需使用任何登录或密码。对于 Windows NT 身份验证登录而言,useselftrue则无效,


除非 Microsoft Windows NT® 环境支持安全帐户委托,并且,提供程序支持 Windows 身份验证(此时不再需要创建值为true的映射,不过创建仍然有效)。

[@locallogin=]'locallogin'

本地服务器上的登录。locallogin的数据类型为sysname,默认设置为 NULL。NULL 值指定此条目将应用于所有连接到rmtsrvname的本地登录。如果值不为 NULL,


locallogin可以是一个 SQL Server 登录或 Windows NT 用户。必须授予 Windows NT 用户直接访问 SQL Server 或通过其作为已授予访问权限的组的成员来访问


SQL Server 的权限。


[@rmtuser =]'rmtuser'


useselffalse时,用来连接rmtsrvname的用户名,rmtuser的数据类型为sysname,默认设置为 NULL。


[@rmtpassword =]'rmtpassword'


rmtuser相关的密码。rmtpassword的数据类型为sysname,默认设置为 NULL。

返回代码值

0(成功)或 1(失败)

注释

当用户登录到本地服务器并执行分布式查询,以访问链接服务器上的表时,本地服务器必须登录链接服务器上,代表该用户访问该表。使用sp_addlinkedsrvlogin来指定本地服


务器登录链接服务器的登录凭据。

本地服务器上所有登录和链接服务器上的远程登录之间的默认映射通过执行sp_addlinkedserver自动创建。默认映射说明连接到链接服务器代表的登录时,SQL Server


使用本地登录的用户凭据(等同于将链接服务器的@useself设置为true时执行sp_addlinkedsrvlogin)。使用sp_addlinkedsrvlogin只可以更改特定的本地服务器


的默认映射或添加新映射。若要删除默认映射或任何其它映射,请使用sp_droplinkedsrvlogin

当所有下列条件存在时,SQL Server 可以自动地使用正在发出查询的用户的 Windows NT 安全凭据(Windows NT 用户名称和密码),以连接到链接服务器,而不是必须使用

sp_addlinkedsrvlogin创建一个预设的登录映射。

  1. 使用 Windows 身份验证模式,用户连接到 SQL Server。

  2. 在客户端和发送服务器上安全帐户委托是可用的。

  3. 提供程序支持 Windows 身份验证模式(例如,运行于 Windows NT 上的 SQL Server)。

使用映射(此映射通过在本地 SQL Server 上执行sp_addlinkedsrvlogin定义)的链接服务器执行身份验证后,远程数据库中单独对象的权限由链接服务器决定,


而不是由本地服务器决定。

不能从用户定义的事务中执行sp_addlinkedsrvlogin

权限

只有sysadminsecurityadmin固定服务器角色的成员才可以执行sp_addlinkedsrvlogin

用法示例
EXEC sp_addlinkedsrvlogin 'DBLink', 'false',null, 'ILM', '111111

介绍完建立连接的方式,下面正式介绍SQL JOB的开发。直接用步骤介绍了。
a)新建作业。如下图:

a、 新建作业。如下图:

b、 点击【新建作业】对话框的【常规】选项卡,填写 SQL JOB的名称。如下图

c、 点击【新建作业】对话框的【步骤】选项卡,填写 步骤名称。



d、 进行作业的第一个步骤------建立数据库连接操作。如下图:


e、 进行作业的第下一个步骤。如下图:

e、设置作业执行计划

f、点击确定,完成SQL JOB的开发。

g、完成后作业下面就多了刚开发的作业。如下:

h、作业制作完成后,可以立即让它执行。如果不让它执行,它也会在按照制定频率执行。测试作业:



2、发布、订阅。

发布订阅下节再介绍吧。这节东西有点多了。哈哈

其中几个存储过程的参数参照了资料。有兴趣了解更多的可以参考下面的地址:

参考:

http://www.yesky.com/imagesnew/software/tsql/ts_sp_drop_9c1e.htm

http://www.yesky.com/imagesnew/software/tsql/ts_sp_drop_59v2.htm

http://www.yesky.com/imagesnew/software/tsql/ts_sp_adda_8gqa.htm

http://www.yesky.com/imagesnew/software/tsql/ts_sp_adda_6e26.htm

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics