• T-SQL 镜像测试


    --======================================================
    ----镜像计划建立 2016-05-10 17:05:16.463 hubiyun
    ----======================================================
    
     
    
    --配置
    --1.主体服务器一台(包含主体数据库)、镜像服务器一台(包含镜像数据库)、见证服务器一台(可选,监控,故障自动转移)
    --2.主体数据库:提供客户端应用程序的连接,查询,更新,执行相关事务等,主体数据库要求使用完全恢复模式。
    --3.镜像数据库:持续同步来自主体数据库的事务,镜像数据库不允许任何的连接存在,但可以对其创建数据库快照来作为只读数据库,实现用户的相关查询操作。
    --4.见证服务器:可选的配置,用于高可用性操作模式,通过见证服务器自动侦测故障,实现角色切换和故障转移。一个见证服务器可以为多组镜像提供服务。
    --5.角色的转换:主体数据库与镜像数据库互为伙伴,当见证服务器侦测到主体服务器故障时,在高可用性模式下,实现故障自动转移后,会自动将主体服务器切换为镜像服务器角色,即角色发生了互换。
    --6.确保主体服务器和镜像服务器使用相同的版本,见证服务器可以使用相对应的任意版本的SQL server。
    --7.主体服务器的主体数据库设置为 FULL恢复模式。
    
     
    
    --步骤以及常见问题
    --1.主机192.168.2.152 镜像192.168.2.153
    --2.用于实现镜像的数据库为Performance
    --3.服务器d:mirror为共享文件夹,其中涉及的文件都放在该文件夹中
    --常见1418ERROR—》 开启RemoteDacEnabled、修改hosts文件、修改计算机DNS、telnet测试、修改xpstar.dll、xplog70.dll权限
    --使用管理-SQL SERVER日志来诊断具体问题
    --https://msdn.microsoft.com/en-us/library/ms191140.aspx
    --http://blog.csdn.net/whatday/article/details/8350709
    --http://www.cnblogs.com/mrhgw/p/3514855.html
    
     
    
    
    exec sp_configure 'show advanced options', 1
    reconfigure
    exec sp_configure 'xp_cmdshell', 1
    reconfigure
    exec sp_configure 'Ad Hoc Distributed Queries',1
    reconfigure 
    --exec sp_addlinkedserver 'srv_lnk', '', 'SQLOLEDB','192.168.2.153'
    --exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa','sa'
    --go
    --exec sp_dropserver 'srv_lnk','droplogins'
    --go
    --=================
    ----1.数据同步
    ----===============
    
    --数据库备份
    use master
    go
    backup database Performance to disk = N'D:mirrorPerformance.bak'
    with format, init, name = N'Performance-Full Database Backup', skip, norewind, nounload, stats = 10
    go
    backup log Performance to disk = N'D:mirrorPerformance.bak'
    with noformat, noinit, name = N'Performance-Transaction Log Backup', skip, norewind, nounload, stats = 10
    go 
    exec master..xp_cmdshell 'net use \192.168.2.153mirror Aa123456 /user:domainAdministrator'
    exec Master..xp_cmdshell 'copy D:mirrorPerformance.bak \192.168.2.153mirrorPerformance.bak'
    go
    
    
    --数据库恢复
    use master
    go
    restore database Performance from disk = N'D:mirrorPerformance.bak'
    with file = 1,
    norecovery, nounload, replace, stats = 10
    go
    restore log Performance from disk = N'D:mirrorPerformance.bak'
    with file = 2, norecovery, nounload, stats = 10
    go
    
     
    
    --=================
    ----2.证书交换
    ----===============
    
    --A服务器执行SQL
    use master
    go
    if exists (select 1 from sys.endpoints where name ='Endpoint_Mirroring')
    drop endpoint Endpoint_Mirroring
    if exists (select 1 from sys.certificates where name ='HOST_A_cert')
    drop certificate HOST_A_cert
    if exists (select 1 from sys.certificates where name ='HOST_B_cert')
    drop certificate HOST_B_cert
    if exists (select 1 from sys.databases where name ='master' and is_master_key_encrypted_by_server>0)
    drop master key
    if exists (select 1 from sysusers where name ='HOST_B_user')
    drop user HOST_B_user
    if exists (select 1 from syslogins where name ='HOST_B_login')
    drop login HOST_B_login
    create master key encryption by password = 'OmBzkbeUGmXgJoZqg0vsqp4'
    go
    create certificate HOST_A_cert 
    with subject = 'HOST_A certificate',start_date='20160510',expiry_date='20991231'
    go
    create endpoint Endpoint_Mirroring
    state = started
    as tcp (
    listener_port=7024
    , listener_ip = ALL
    ) 
    for database_mirroring ( 
    authentication = certificate HOST_A_cert
    , encryption = required algorithm AES
    , role = ALL
    )
    go
    exec master.dbo.xp_cmdshell 'del D:mirrorHOST_A_cert.cer' 
    backup certificate host_a_cert to file = 'D:mirrorHOST_A_cert.cer'
    go
    exec master..xp_cmdshell 'net use \192.168.2.153mirror Aa123456 /user:domainAdministrator'
    exec Master..xp_cmdshell 'copy D:mirrorHOST_A_cert.cer \192.168.2.153mirrorHOST_A_cert.cer'
    go
    create login HOST_B_login with password = 'BBluOHCsEqd4'
    go
    create user HOST_B_user for login HOST_B_login
    go
    
     
    
    
    --B服务器执行SQL
    use master
    go
    if exists (select 1 from sys.endpoints where name ='Endpoint_Mirroring')
    drop endpoint Endpoint_Mirroring
    if exists (select 1 from sys.certificates where name ='HOST_B_cert')
    drop certificate HOST_B_cert
    if exists (select 1 from sys.certificates where name ='HOST_A_cert')
    drop certificate HOST_A_cert
    if exists (select 1 from sysusers where name ='HOST_A_user')
    drop user HOST_A_user
    if exists (select 1 from syslogins where name ='HOST_A_login')
    drop login HOST_A_login
    if exists (select 1 from sys.databases where name ='master' and is_master_key_encrypted_by_server>0)
    drop master key
    create master key encryption by password = 'Ny0d7X1X46QAqxfheSkdE9PQYFu9B'
    go
    create certificate HOST_B_cert 
    with subject = 'HOST_B certificate for database mirroring',start_date='20160510',expiry_date='20991231'
    go
    create endpoint Endpoint_Mirroring
    state = started
    as tcp (
    listener_port=7024
    , listener_ip = ALL
    ) 
    for database_mirroring ( 
    authentication = certificate HOST_B_cert
    , encryption = required algorithm AES
    , role = ALL
    )
    go
    exec master.dbo.xp_cmdshell 'del D:mirrorHOST_B_cert.cer' 
    backup certificate HOST_B_cert to file = 'D:mirrorHOST_B_cert.cer'
    go 
    exec master..xp_cmdshell 'net use \192.168.2.152mirror Aa123456 /user:domainAdministrator'
    exec Master..xp_cmdshell 'copy D:mirrorHOST_B_cert.cer \192.168.2.152mirrorHOST_B_cert.cer'
    go
    create login HOST_A_login with password = 'OTYNeiFau7DY'
    go
    create user HOST_A_user for login HOST_A_login
    go
    
     
    
    --A服务器执行SQL
    create certificate HOST_B_cert
    authorization HOST_B_user
    from file = 'D:mirrorHOST_B_cert.cer'
    go
    grant connect on endpoint::Endpoint_Mirroring TO HOST_B_login
    go
    
    
    --B服务器执行SQL
    create certificate HOST_A_cert
    authorization HOST_A_user
    from file = 'D:mirrorHOST_A_cert.cer'
    go
    grant connect on endpoinT::Endpoint_Mirroring TO HOST_A_login
    go
    
     
    
    --=================
    ----3.镜像操作
    ----===============
    
    --B服务器
    use master;
    go
    alter database Performance 
    set partner = 'TCP://2008zjk152.bird.cn:7024'
    --set partner off
    go
    alter database Performance set partner resume
    alter database Performance set partner failover
    alter database Performance set partner force_service_allow_data_loss
    go
    
    
    --A服务器
    use master;
    go
    alter database Performance 
    set partner = 'TCP://2008zjk153.bird.cn:7024'
    --set partner off
    go
    alter database Performance set partner resume
    alter database Performance set partner failover
    go
    
     
    
     
    
     
    
     
  • 相关阅读:
    Golang sync.Once
    Golang type的两种用法
    Golang函数变量——把函数作为值保存到变量中
    Golang sync.Mutex包——互斥锁
    Golang sync/atomic包——原子操作
    Golang Dockerfile build dial tcp: lookup timeout报错解决方案
    Kubernetes限制节点启动的Pod数量
    Kubernetes定时任务的实现
    Kubernetes EventBroadcaster事件管理机制源码分析
    Golang类型转换
  • 原文地址:https://www.cnblogs.com/yunyun0574/p/5646593.html
Copyright © 2020-2023  润新知