彩世界平台-彩世界时时app-彩世界开奖app苹果下载

热门关键词: 彩世界平台,彩世界时时app,彩世界开奖app苹果下载

您的位置:彩世界平台 > 工作委员会 > SQLServer 镜像功能完全实现_MsSql_脚本之家

SQLServer 镜像功能完全实现_MsSql_脚本之家

发布时间:2019-11-23 04:48编辑:工作委员会浏览(179)

    在域环境下我没配置成果,也许是域用户的原因,因为我在生产环境下搞的,更改域用户需要重启SQLServer ,所以这个方法放弃了,只能用证书形式。 环境: 主机:192.168.10.2 镜像:192.168.10.1 两台服务器上的都是SQLServer2005 首先配置主机 主机上执行以下SQL 复制代码 代码如下: --创建主机数据库主密钥 USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'; GO --在10.2上为数据库实例创建证书 CREATE CERTIFICATE As_A_cert WITH SUBJECT = 'As_A_cert', START_DATE = '09/02/2011', EXPIRY_DATE = '01/01/2099'; GO --在10.2上使用上面创建的证书为数据库实例创建镜像端点 CREATE ENDPOINT Endpoint_As STATE = STARTED AS TCP ( LISTENER_PORT=5022, LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE As_A_cert, ENCRYPTION = REQUIRED ALGORITHM RC4, ROLE = ALL ); GO 注:这里要注意设置数据库的镜像端口。5022. --备份10.2上的证书并拷贝到10.1上 BACKUP CERTIFICATE As_A_cert TO FILE = 'D:As_A_cert.cer'; GO 注:备份证书A,并将证书A拷贝到镜像服务器B上。 配置镜像服务器 复制代码 代码如下: USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'; GO --在10.1 B上为数据库实例创建证书 CREATE CERTIFICATE As_B_cert WITH SUBJECT = 'As_B_cert', START_DATE = '09/2/2011', EXPIRY_DATE = '01/01/2099'; GO --在10.1 B上使用上面创建的证书为数据库实例创建镜像端点 CREATE ENDPOINT Endpoint_As STATE = STARTED AS TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE As_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL ); GO --备份10.1 B上的证书并拷贝到10.2 A上 BACKUP CERTIFICATE As_B_cert TO FILE = 'D:As_B_cert.cer'; GO 同样将备份的证书B 拷贝到A服务器上。 建立用于镜像登录的账户 在A上执行 --交换证书, --同步 Login 复制代码 代码如下: CREATE LOGIN B_login WITH PASSWORD = 'password'; CREATE USER B_user FOR LOGIN B_login; CREATE CERTIFICATE As_B_cert AUTHORIZATION B_user FROM FILE = 'D:As_B_cert.cer'; GRANT CONNECT ON ENDPOINT::Endpoint_Bs TO [B_login]; 在B上执行 复制代码 代码如下: --交换证书, --同步 Login CREATE LOGIN A_彩世界平台,login WITH PASSWORD = 'password'; CREATE USER A_user FOR LOGIN A_login; CREATE CERTIFICATE As_A_cert AUTHORIZATION A_user FROM FILE = 'D:As_A_cert.cer'; GRANT CONNECT ON ENDPOINT::Endpoint_As TO [A_login]; 记得两台服务器的端口5022是不被占用的,并且保证两个服务器可以连接 以后步骤执行没问题,镜像已经完成一半了。 接下来完整备份A服务器上的Test库 复制代码 代码如下: --主机执行完整备份 USE master; ALTER DATABASE Test SET RECOVERY FULL; GO BACKUP DATABASE Test TO DISK = 'D:SQLServerBackupsTest.bak' WITH FORMAT; GO BACKUP LOG Test TO DISK = 'D:SQLServerBackupsTest.bak'; GO --将备份文件拷贝到B上。 一定要执行完整备份。 在B服务器上完整欢迎数据库 这里问题多多。一个一个说。 如果我们直接执行如下SQL. 复制代码 代码如下: RESTORE DATABASE Test FROM DISK = 'D:BackTest.bak' WITH NORECOVERY GO RESTORE LOG Test FROM DISK = 'D:BackTest_log.bak' WITH FILE=1, NORECOVERY GO [code] 可能会报: 消息 3154,级别 16,状态 4,第 1 行 备份集中的数据库备份与现有的 'Test'数据库不同。 消息 3013,级别 16,状态 1,第 1 行 可能是两个数据库的备份集名称不同导致,找了半天原因未果,所以采用下面sp_addumpdevice方法来做。 用sp_addumpdevice来建立一个还原的设备。这样就保证了改备份文件是数据这个数据库的。 [code] exec sp_addumpdevice 'disk','Test_backup', 'E:backupTest.bak' exec sp_addumpdevice 'disk','Test_log_backup', 'E:backupTest_log.bak' go 成功之后我们来执行完成恢复 复制代码 代码如下: RESTORE DATABASE Test FROM Test_backup WITH DBO_ONLY, NORECOVERY,STATS; go RESTORE LOG Test FROM Test_log_backup WITH file=1, NORECOVERY; GO 这里如果之前备份过多次数据库的话,肯会产生多个备份集。所以这里的 file就不能指定为1了。这个错误可能是: 消息 4326,级别 16,状态 1,第 1 行 此备份集中的日志终止于 LSN 36000000014300001,该 LSN 太早,无法应用到数据库 。可以还原包含 LSN 36000000018400001 的较新的日志备份。 可以通过这句话来查询该备份文件的备份集 restore headeronly from disk = 'E:backupTest_log.bak' 找到最后一个的序号就指定给file就可以。 还需要注意的是第一次完整恢复的时候需要指定NORECOVERY。 至此所有准备工作都已经完成我们开始执行镜像 先在镜像服务器上执行 ALTER DATABASE Test SET PARTNER = 'TCP://192.168.10.2:5022'; 成功之后再在主机上执行 ALTER DATABASE Test SET PARTNER = 'TCP://192.168.10.2:5022'; 这样两台服务器的镜像就同步了。

    CDC:Change Data Capture复制代码 代码如下:--步骤:本文中以GPOSDB为例

    删除镜像:

    --第一步、对目标库显式启用CDC:--在当前库使用sys.sp_cdc_enable_db。返回0。--注意,无法对系统数据库和分发数据库启用该功能。且执行者需要用sysadmin角色权限。--该存储过程的作用域是整个目标库。包含元数据、DDL触发器、cdc架构和cdc用户。--使用以下代码启用:USE GPOSDB --要启用CDC的数据库GOEXECUTE sys.sp_cdc_彩世界时时app,enable_db;GO--在一开始直接执行时,出现了报错信息:--消息22830,级别16,状态1,过程sp_cdc_enable_db_internal,第193 行--无法更新元数据来指示已对数据库AdventureWorks 启用了变更数据捕获。--执行命令'SetCDCTracked' 时失败。--返回的错误为15517: '无法作为数据库主体执行,因为主体"dbo"--不存在、无法模拟这种类型的主体,或您没有所需的权限。'。请使用此操作和错误来确定失败的原因并重新提交请求。--这里引出了另外一个知识点:错误号 15517 的错误--这种错误会在很多地方出现,如还原数据库的时候也会有可能出现。--共同点是:某个/些存储过程使用了具有WITHEXECUTE AS 的选项。--使其在当前库具有了某个架构,但是当在别的地方执行时,由于没有这个架构,所以就报错,解决方法:ALTER AUTHORIZATION ON DATABASE::[GPOSDB] TO [sa]

    ALTER DATABASE Test SET PARTNER OFF

    --经过检查,uspUpdateEmployeeHireInfo这个存储过程的确有:WITH EXECUTE AS CALLER--使用sa的原因是即使sa被禁用,sa还是存在的。所以不会报错。--现在重新执行:USE GPOSDBGOEXECUTE sys.sp_cdc_enable_db;GO--启用成功,然后通过以下语句检查是否成功:SELECT is_cdc_enabled,CASE WHEN is_cdc_enabled=0 THEN 'CDC功能禁用' ELSE 'CDC功能启用' END 描述FROM sys.databasesWHERE NAME = 'GPOSDB'

    如果主机出现问题,在主机执行复制代码 代码如下: USE MASTER Go ALTER DATABASE Test SET PARTNER FAILOVER Go 总结: 如果在建立镜像的时候中间的那个步骤出国,需要重新执行的时候一定要把该删得东西删除掉。 --查询镜像 select * from sys.endpoints --删除端口 drop endpoint Endpoint_As --查询证书 select * from sys.symmetric_keys --删除证书,先删除证书再删除主键 DROP CERTIFICATE As_A_cert --删除主键 DROP MASTER KEY --删除镜像 alter database set partner off --删除登录名 drop login sp_addumpdevice 的语法 复制代码 代码如下: sp_addumpdevice [ @devtype = ] 'device_type' , [ @logicalname = ] 'logical_name' , [ @physicalname = ] 'physical_name' ] 其中参数有: @devtype:设备类型,可以支持的值为disk和tape,其中disk为磁盘文件;tape为 windows支持的任何磁带设备。 @logicalname:备份设备的逻辑名称,设备名称。 @physicalname:备份设备的物理名称,路径

    --创建成功后,将自动添加CDC用户和CDC架构。--在用户和架构下面可以看到cdc用户和cdc架构

    参考:

    --创建这两个用户、架构的原因是因为CDC要求独占方式使用这两个架构,所以要单独创建。--如果存在了非CDC功能创建的CDC用户、架构的话,则需要先删除该cdc命名的架构,才能开启。

    --第二步、对目标表启用CDC:--使用db_owner角色的成员执行sys.sp_cdc_enable_table为每个需要跟踪的表创建捕获实例。--然后通过sys.tables目录视图中的is_tracked_by_cdc列来判断是否创建成功。--默认情况下会对表的全部列做捕获。如果只需要对某些列做捕获,--可以使用@captured_column_list参数指定这些列。--如果要把更改表放到文件组里的话,最好创建单独的文件组。

    --如果不想控制访问角色,则@role_name必须显式设置为null。sys.sp_cdc_enable_table [ @source_schema = ] 'source_schema', [ @source_name = ] 'source_name' , [ @role_name = ] 'role_name' [,[ @capture_instance = ] 'capture_instance' ] [,[ @supports_net_changes = ] supports_net_changes ] [,[ @index_name = ] 'index_name' ] [,[ @captured_column_list = ] 'captured_column_list' ] [,[ @filegroup_name = ] 'filegroup_name' ] [,[ @partition_switch = ] 'partition_switch' ]

    --例子:--把SYSTEMPARA 这个表开启变更捕获。USE GPOSDBGOEXEC sys.sp_cdc_enable_table @source_schema = 'DBO', @source_name = 'SYSTEMPARA',@role_name = NULL

    --然后查询是否成功:SELECT name , is_tracked_by_cdc , CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用' ELSE 'CDC功能启用' END 描述FROM sys.tablesWHERE OBJECT_ID = OBJECT_ID--对表开启以后,可以在下图中看到多了很多cdc架构开头的表:--刷新一下GPOSDB数据库,在系统表下面可以看到多了下面几张表[cdc].[DBO_SYSTEMPARA_CT][cdc].[change_tables][cdc].[captured_columns][cdc].[ddl_history][cdc].[index_columns][cdc].[lsn_time_mapping][dbo].[systranschemas][dbo].[dtproperties]

    --启动之后,可以看到SQLServer代理里面的作业,也出现了这两个作业:[cdc.GPOSDB_capture][cdc.GPOSDB_cleanup]

    --在可编程性-》函数-》表值函数里,也多了两个函数[cdc].[fn_cdc_get_all_changes_DBO_SYSTEMPARA][cdc].[fn_cdc_get_net_changes_DBO_SYSTEMPARA]

    --下面列出相关的存储过程:--Sys.sp_cdc_add_job--说明及例子--Sys.sp_cdc_generate_wrapper_function--说明及例子--Sys.sp_cdc_change_job--说明及例子--Sys.sp_cdc_get_captured_columns--说明及例子--Sys.sp_cdc_cleanup_change_table--说明及例子--Sys.sp_cdc_get_ddl_history--说明及例子--Sys.sp_cdc_disable_db--说明及例子 建议先禁用表,再禁用库--Sys.sp_cdc_help_change_data_capture--说明及例子--Sys.sp_cdc_disable_table--说明及例子--Sys.sp_cdc_help_jobs--说明及例子--Sys.sp_cdc_drop_job--说明及例子--Sys.sp_cdc_scan--说明及例子--Sys.sp_cdc_enable_db--说明及例子--Sys.sp_cdc_start_job--说明及例子--Sys.sp_cdc_enable_table--说明及例子--Sys.sp_cdc_stop_job--说明及例子

    --函数:--Cdc.fn_cdc_get_all_changes_--说明及例子--Sys.fn_cdc_has_column_changed--说明及例子--Cdc.fn_cdc_get_net_changes_--说明及例子--Sys.fn_cdc_increment_lsn--说明及例子--Sys.fn_cdc_decrement_lsn--说明及例子--Sys.fn_cdc_is_bit_set--说明及例子--Sys.fn_cdc_get_column_ordinal--说明及例子--Sys.fn_cdc_map_lsn_to_time--说明及例子--Sys.fn_cdc_get_max_lsn--说明及例子--Sys.fn_cdc_map_time_to_lsn--说明及例子--Sys.fn_cdc_get_min_lsn--说明及例子

    --------------------下面开始从头到尾做一个实际案例---------------------------下面开始从头到尾做一个实际案例

    --步骤一:对目标库显式启用CDCUSE GPOSDB --要启用CDC的数据库GOEXECUTE sys.sp_cdc_enable_db;GO

    --某些数据库可能存在一些存储过程包含有:execute as 等语句,此时会报错:

    --文字描述:--消息22830,级别16,状态1,过程sp_cdc_enable_db_internal,第186 行--无法更新元数据来指示已对数据库AdventureWorks 启用了变更数据捕获。执行命令'SetCDCTracked' 时失败。返回的错误为15517: '无法作为数据库主体执行,因为主体"dbo" 不存在、无法模拟这种类型的主体,或您没有所需的权限。'。请使用此操作和错误来确定失败的原因并重新提交请求。--消息266,级别16,状态2,过程sp_cdc_enable_db_internal,第0 行--EXECUTE 后的事务计数指示BEGIN 和COMMIT 语句的数目不匹配。上一计数= 0,当前计数= 1。--消息266,级别16,状态2,过程sp_cdc_enable_db,第0 行--EXECUTE 后的事务计数指示BEGIN 和COMMIT 语句的数目不匹配。上一计数= 0,当前计数= 1。--消息3998,级别16,状态1,第1 行--在批处理结束时检测到不可提交的事务。该事务将回滚。--如果出现这个错误,目前的解决方法是执行下面语句,原因已在开头说明,对于没有使用EXECUTE AS的库,一般不会有这样的问题:

    ALTER AUTHORIZATION ON DATABASE::[GPOSDB] TO [sa]

    --现在重新执行:USE GPOSDBGOEXECUTE sys.sp_cdc_enable_db;GO--通过以下语句检查是否成功:SELECT is_cdc_enabled,CASE WHEN is_cdc_enabled=0 THEN 'CDC功能禁用' ELSE 'CDC功能启用' END 描述FROM sys.databasesWHERE NAME = 'GPOSDB'

    --步骤二:对表启用CDCUSE GPOSDBGOEXEC sys.sp_cdc_enable_table@source_schema = 'DBO',@source_name = 'SYSTEMPARA',@role_name = NULL,@capture_instance=DEFAULTGO

    --然后查询是否成功:SELECT name , is_tracked_by_cdc , CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用' ELSE 'CDC功能启用' END 描述FROM sys.tablesWHERE OBJECT_ID = OBJECT_ID

    --可以看到GPOSDB数据库里的系统表里新增了[cdc].[DBO_SYSTEMPARA_CT]表

    --步骤三:检验,下面来改动数据--先查询一下DBO_SYSTEMPARA_CT表SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]

    --可以看到一条记录都没有,因为刚刚创建,并没有对原表systempara做任何增删改操作

    --向[SystemPara]表插入一条记录INSERT INTO [dbo].[SystemPara] ( [ParaValue] , [Name] , [Description] )VALUES ( '中国' , -- ParaValue - varchar '中国' , -- Name - varchar '中国' -- Description - varchar

    --查询一下DBO_SYSTEMPARA_CT表,可以看到多了一条记录SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]

    --更新[SystemPara]表的一条记录UPDATE [dbo].[SystemPara] SET [ParaValue]='德国' WHERE [Description]='中国'

    --查询一下DBO_SYSTEMPARA_CT表,可以看到多了两条记录SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]

    --删除[SystemPara]表的一条记录DELETE FROM [dbo].[SystemPara] WHERE [Description]='中国'

    --查询一下DBO_SYSTEMPARA_CT表,可以看到多了一条记录SELECT * FROM cdc.[DBO_SYSTEMPARA_CT]

    --现在来分析一下DBO_SYSTEMPARA_CT表--可以在联机丛书上查看:--cdc._CT --可以看到,这样命名的表,是用于记录源表做过更改操作的表。--对于insert/delete操作,会有对应的一行记录,而对于update,会有两行记录。--对于__$operation列:1 = 删除、2= 插入、3= 更新--update语句的__$operation列的值是3和4,所以一条update语句对应两条记录

    --对于__$start_lsn列:由于更改是来源与数据库的事务日志,所以这里会保存其事务日志的开始序列号--但是微软不检查直接查询这类表,建议使用--cdc.fn_cdc_get_all_changes_--cdc.fn_cdc_get_net_changes_-- 来查询

    本文由彩世界平台发布于工作委员会,转载请注明出处:SQLServer 镜像功能完全实现_MsSql_脚本之家

    关键词:

上一篇:Mysql Cluster概述与部署

下一篇:没有了