通过证书方式创建数据库镜像,这篇论坛文章(赛迪网技术社区)主要介绍了SQL Server

这篇论坛文章(赛迪网技术社区)主要介绍了SQL Server,2005数据库镜像配置脚本,通过证书方式创建数据库镜像,主数据库代码,Expian,–==================================================================,–场景,环境,Win2008

图片 3

基本提醒:这篇论坛小说(Sadie网本事社区State of Qatar首要介绍了SQL Server
二〇〇七数据库镜像的配备脚本

经过证书方式成立数据库镜像

/*************************************************
*sql镜像的具体配置步骤表达
*Crate DataTime:
*二〇一六-0205-2014-02-06研商成功
*Expian:
*本实例未有配置亲眼看见服务器,非域境况,数据库版本为SQL2009多少中央版
*两台机,系统版本为SESportageVE宝马X3二零一二,IP分别为:192.168.1.25(A)、192.168.1.26(B)
*中间A为主数据库,B为镜像库;
*遇见的标题记录如下:
*1卡塔尔国两台机都要放1433、5022
*2卡塔尔(قطر‎主数据库备份时要用语句备份
*3卡塔尔(قطر‎镜像服务器在平复数据库时精选NORECOVE中华VY(不回滚),默以为RECOVE大切诺基Y(回滚);
*余烬复起后会发掘B数据中央直属机关接处于还原状态,属张晓芸常现像。
*一旦用的是RECOVE奥迪Q5Y,在两者镜像时会现身1416,未有为镜像配置XX数据库。
*后在海外一网站上查到原因了,顺遂化解!
*—在网络找了累累,都并未有缓和那一个难点,下边记录一下外国同伙说的办法——
*I hope now it will be easy. First, backup the database on primary
server,
*thern copy the backup to the mirror server and then restore the
database with NORECOVERY option.
*Then backup the LOG (I hope your database is in FULL recovery mode) on
the primary database,
*copy to mirror and restore this log with NORECOVERY option. And then
try to start mirroring as soon as you can.
* – Alex_L Mar 28 ’12 at 7:43
***************************************************/
————-首先配置主服务A————-
–成立主密钥
USE master ;
CREATE MASTER KEY ENCRYPTION BY PASSWORD= ‘123456’;
GO
–创造证书
CREATE CERTIFICATE as_a_cert–证书名,自由命名
WITH SUBJECT=’as_a_cert’,
EXPIRY_DATE=’01/0百分之三十八099′ ;–终止日期,起头时间自身这里未有写
GO
–用上边的证件制造镜像端点
–[databasemirroring]镜像端点名,能够慈祥取名
IF NOT EXISTS ( SELECT  1
                FROM    sys.database_mirroring_endpoints )
    BEGIN
        CREATE ENDPOINT [databasemirroring] STATE= STARTED AS TCP (
LISTENER_PORT=5022,
            LISTENER_IP= ALL ) FOR DATABASE_MIRRORING (
AUTHENTICATION=
            CERTIFICATE as_a_cert, ENCRYPTION= REQUIRED ALGORITHM AES,
ROLE=
            ALL ) ;
    END
—备份注脚
BACKUP CERTIFICATE as_a_cert
TO FILE=’c:\as_a_cert.cer’;–路径
GO

–==================================================================
–该文书档案首要用以内部安顿模板
–场景:
–主服务器:192.168.3.32
–镜像服务器:192.168.3.33
–镜像数据库:德姆oDB

延续祖宗门户景况要上镜像,自个儿又做了贰回测验,计算记录一下。

那篇论坛作品(Sadie网才能社区卡塔尔(قطر‎首要介绍了SQL Server
二零零六数据库镜像的布署脚本,详细内容请大家参照他事他说加以考察下文: SQL Server
2006数据库镜像配置脚本:

主数据库代码:

—备份主体数据库–依次实践
ALTER DATABASE news SET RECOVERY FULL;
–2)
BACKUP DATABASE news TO DISK=’c:\news.bak’ WITH
format;–备份数据库入眼
–3)
BACKUP LOG news TO DISK=’C:news.bak’;–备份日志

–=================================================================

目的:福寿绵绵首要数据库的热备和故障自动切换。

演示如下:

图片 1图片 2Code
USE [master]

/*
*A下边证书、镜像端点已经到位了,B服务器步骤同上,A和B创设完毕后,将注解相互对拷
*B服务器步骤同上,只是证书名要改为B
*将两端的注明对拷
*/
—注册B服务器的证书,创立顾客并映射,上边语句要联合运转–在主服务器(A)上运营
CREATE LOGIN b_login WITH PASSWORD=’123456′;
CREATE USER b_user FOR LOGIN b_login;
CREATE CERTIFICATE as_b_cert AUTHORIZATION b_user FROM
FILE=’C:\as_b_cert.cer’;
GRANT CONNECT ON
ENDPOINT::databasemirroring/*(镜像名字,能够慈悲取名卡塔尔国*/ TO
[b_login];
GO
—注册A服务器的证件,创立客商并映射,上面语句要一并运营–在主服务器(B)上运行
CREATE LOGIN a_login WITH PASSWORD=’123456′;
CREATE USER a_user FOR LOGIN a_login;
CREATE CERTIFICATE as_a_cert AUTHORIZATION b_user FROM
FILE=’C:\as_a_cert.cer’;
GRANT CONNECT ON
ENDPOINT::databasemirroring/*(镜像名字,能够和煦取名卡塔尔(قطر‎*/ TO
[a_login];
GO
—-在B上恢复生机数据库,要接收”不回滚“,否会出错!苏醒达成后会看见数据库平素处在“正在还原中”
—-恢复生机数据库(步骤省略)

伊始布局

环境:Win2008_R2_X64,SQLServer2008_R2_X64,WorkGroup网络格局。

–在MITiggo-A上,创造数据库镜像端点

–创立数据库主密钥。
IF EXISTS(SELECT * FROM sys.databases WHERE name=’master’ and is_master_key_encrypted_by_server=1)    
    –drop master key;
    OPEN MASTER KEY DECRYPTION BY PASSWORD=’123456789′;
ELSE
    create MASTER KEY ENCRYPTION BY PASSWORD=’123456789′;
GO

—-创设镜像-先在镜像服务器(B)实施–
ALTE揽胜极光 DATABASE news SET
PARTNERubicon=’tcp://192.168.1.25:5022′;–与主服务器建立连接
–在主服务器(A)上运营
ALTEQashqai DATABASE news SET
PARTNEENCORE=’tcp://192.168.1.26:5022′;–与镜像服务器建设构造连接
–成功后会看见主服务器上出示”主体已一同”,B镜像服务器上彰显“正在复苏”
–到这一步就马到成功了,能够来测验了,通过故障转移来测量检验数据是还是不是同步!
 –二〇一六/02/06/14:53/汉腾汽车国际电商务行业园/暾

  1. 在主服务器和镜像服务器上开创Master Key

    –=========================================================================================================
    –创制Master key
    –在主服务器和镜像服务器上运维 USE master;
    GO
    IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys k WHERE k.Name=’##MS_DatabaseMasterKey##’)
    BEGIN
    CREATE MASTER KEY ENCRYPTION BY PASSWORD =’master@key123′
    END
    GO
    IF NOT EXISTS(SELECT 1 FROM sys.databases db WHERE db.[is_master_key_encrypted_by_server]=1)
    BEGIN
    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
    END
    GO

  2. 在主服务器上创建证书并备份

    –=========================================================================================================
    –创设和备份证书
    –在主服务器上运营 IF NOT EXISTS (SELECT 1 FROM sys.certificates C WHERE C.Name=’HOST_3_32_cert’ )
    BEGIN
    CREATE CERTIFICATE HOST_3_32_cert WITH SUBJECT =’HOST_3_32_cert’,
    START_DATE = ’01/01/2010′ , EXPIRY_DATE = ’01/01/2199′;
    END
    GO
    BACKUP CERTIFICATE HOST_3_32_cert TO FILE=’D:\HOST_3_32_cert.cer’
    GO

  3. 在镜像服务器上成立证书并备份

    –=========================================================================================================
    –创设和备份证书
    –在镜像服务器上运维 IF NOT EXISTS (SELECT 1 FROM sys.certificates C WHERE C.Name=’HOST_3_33_cert’ )
    BEGIN
    CREATE CERTIFICATE HOST_3_33_cert WITH SUBJECT =’HOST_3_33_cert’,
    START_DATE = ’01/01/2010′ , EXPIRY_DATE = ’01/01/2199′;
    END
    GO
    BACKUP CERTIFICATE HOST_3_33_cert TO FILE=’D:\HOST_3_33_cert.cer’
    GO

  4. 在主服务器上创制镜像端点

    –=========================================================================================================
    –创造镜像专项使用的端点,并采取证书加密
    –同八个实例上只可以存在叁个镜像端点
    –在主服务器上运维 IF NOT EXISTS(SELECT * FROM sys.endpoints e WHERE e.[Type]=4)
    BEGIN
    CREATE ENDPOINT [Endpoint_Mirroring]
    STATE=STARTED AS
    TCP(LISTENER_PORT=5022)
    FOR DATABASE_MIRRORING(AUTHENTICATION=CERTIFICATE HOST_3_32_cert, ENCRYPTION=REQUIRED,ROLE=ALL)
    END
    GO

  5. 在镜像服务器上创建镜像端点

    –=========================================================================================================
    –成立镜像专项使用的端点,并使用证书加密
    –同三个实例上只可以存在三个镜像端点
    –在主服务器上运行 IF NOT EXISTS(SELECT * FROM sys.endpoints e WHERE e.[Type]=4)
    BEGIN
    CREATE ENDPOINT [Endpoint_Mirroring]
    STATE=STARTED AS
    TCP(LISTENER_PORT=5022)
    FOR DATABASE_MIRRORING(AUTHENTICATION=CERTIFICATE HOST_3_33_cert, ENCRYPTION=REQUIRED,ROLE=ALL)
    END
    GO

  6. 将主服务器和镜像服务器上备份的证件彼此复制到对方服务器上

  7. 在主服务器上创办登录和客户以至证件,并给与连接镜像端点的权位


    –在主服务器上开创登入和顾客以至证件,并付与连接镜像端点的权能
    –在主服务器上运维 USE master;
    GO
    IF NOT EXISTS(SELECT 1 FROM sys.syslogins l WHERE l.[Name] =’HOST_3_33_login’)
    BEGIN
    CREATE LOGIN HOST_3_33_login WITH PASSWORD =’Auto@sql’
    END
    GO
    IF NOT EXISTS(SELECT 1 FROM sys.sysusers u WHERE u.[Name]= ‘HOST_3_33_User’)
    BEGIN
    CREATE USER HOST_3_33_User FOR LOGIN HOST_3_33_login
    END
    GO
    IF NOT EXISTS(SELECT 1 FROM sys.certificates c WHERE c.[Name]= ‘HOST_3_33_cert’)
    BEGIN
    CREATE CERTIFICATE HOST_3_33_cert AUTHORIZATION HOST_3_33_User FROM FILE=’D:\HOST_3_33_cert.cer’
    END
    GO
    GRANT CONNECT ON ENDPOINT::[Endpoint_Mirroring] TO HOST_3_33_login
    GO

数据库:people

create endpoint DB_MirroringEP

SELECT * FROM sys.key_encryptions;
go

8. 在镜像服务器上开创登录和顾客以至证件,并予以连接镜像端点的权能

主机:192.168.1.3

AS tcp (listener_port = 5022)

–向数据库中增添证书。
IF EXISTS(select * from sys.database_mirroring_endpoints WHERE name=’Endpoint_Mirroring’)
    DROP ENDPOINT Endpoint_Mirroring
GO

---------------------------------------------------------------------
--在镜像服务器上创建登陆和用户以及证书,并授予连接镜像端点的权限
--在镜像服务器上运行
USE master;
GO
IF NOT EXISTS(SELECT 1 FROM sys.syslogins l WHERE l.[Name] ='HOST_3_33_login')
BEGIN
CREATE LOGIN HOST_3_33_login WITH PASSWORD ='Auto@sql'
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.sysusers u WHERE u.[Name]= 'HOST_3_33_User')
BEGIN
CREATE USER HOST_3_33_User FOR LOGIN HOST_3_33_login
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.certificates c WHERE c.[Name]= 'HOST_3_33_cert')
BEGIN
CREATE CERTIFICATE HOST_3_33_cert AUTHORIZATION HOST_3_33_User FROM FILE='D:\HOST_3_33_cert.cer'
END
GO
GRANT CONNECT ON ENDPOINT::[Endpoint_Mirroring] TO HOST_3_33_login
GO

镜像机:192.168.1.110

for database_Mirroring (role = partner,encryption=supported);

IF EXISTS(select * from sys.certificates WHERE name=’HOST_A_cert’)
    DROP CERTIFICATE HOST_A_cert;
GO

  1. 在主服务器和镜像服务器上配置防火墙,开放5022端口

  2. 在镜像服务器上接受Telent来检查端口情状

    telnet 192.168.3.33 5022

  3. 在主服务器上接收Telent来检查端口处境

    telnet 192.168.3.32 5022

  4. 在主服务器上备份数据库和日志

    –数据库完整备份和日志备份
    BACKUP DATABASE DemoDB TO DISK=’德姆oDB_FULL.BAK’ WITH COMPRESSION
    BACKUP LOG DemoDB TO DISK=’DemoDB_Log.BAK’ WITH COMPRESSION

见证机:192.168.1.111

go

CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = ‘HOST_A certificate’,
    START_DATE = ‘2008-01-01’;
GO

 

预备干活:

–在MIHaval-B上,创立数据库镜像端点,用于同伙通信

SELECT * FROM sys.certificates;
GO

  1. 将主服务器上的备份复制到镜像服务器上,使用WITH
    NORECOVELANDY选项还原数据库备份和日志备份\

    –使用数据库完整备份和日志备份还原
    –注意修正还原目录
    RESTORE DATABASE 德姆oDB FROM DISK=’德姆oDB_FULL.BAK’ WITH NORECOVERY
    RESTORE LOG DemoDB FROM DISK=’DemoDB_Log.BAK’ WITH NORECOVERY

  1. 张开各种实例的RemoteDACEnable.

    方法:SSMS选中实例右键Facets(方面State of Qatar在Facet列表选中”Surface
    area configuration”设定”RemoteDacEnabled”为true。    

  2. 在每台Server的防火墙入站准则中增多镜像所需端口(小编用的是5022卡塔尔国.

    方法:伊始调控面板Windows防火墙高档设置入站准则新增添准则   

  3. 对主机的people数据库做二个全备和事务日志备份并将其COPY到镜像机上用Replace,NoRecovery选项恢复生机。

    主机备份:

CREATE ENDPOINT Db_MirroringEP

–创立数据库端点
IF EXISTS(SELECT * FROM sys.database_mirroring_endpoints WHERE name=’Endpoint_Mirroring’)
    DROP ENDPOINT Endpoint_Mirroring;
GO

 

USE master


GO


BACKUP DATABASE \[people\] TO DISK =
N'D:\\people.bak' 

WITH FORMAT, INIT, NAME = N'people-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;


GO


BACKUP LOG \[people\] TO DISK =
N'D:\\people.bak' 

WITH NOFORMAT, NOINIT, NAME = N'people-Transaction
Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10;


GO

AS TCP (LISTENER_PORT = 5022)

CREATE ENDPOINT Endpoint_Mirroring STATE=STARTED
    AS TCP ( LISTENER_PORT=5022 , 
        LISTENER_IP = ALL )
    FOR DATABASE_MIRRORING( AUTHENTICATION = CERTIFICATE HOST_A_cert , 
        ENCRYPTION = REQUIRED ALGORITHM RC4 , 
        ROLE = PARTNER );
GO

  1. 在镜像服务器上铺排同伴


    –从服务器设置镜像
    USE [master]
    GO
    ALTER DATABASE DemoDB SET PARTNER=’TCP://192.168.3.32:5022′
    GO

镜像复苏:

FOR DATABASE_MIRRORING (ROLE = PARTNER, ENCRYPTION = SUPPORTED);

SELECT * FROM sys.database_mirroring_endpoints;
GO

15. 在主服务器上安顿同伴

USE master

GO

–备份申明
BACKUP CERTIFICATE HOST_A_cert TO FILE = ‘D:\MIRROR\HOST_A_cert.cer’;
GO

------------------------------------------------------------------
--主服务器设置镜像
--如果提示日志不足,请继续备份还原日志
USE [master]
GO
ALTER DATABASE DemoDB SET PARTNER='TCP://192.168.3.33:5022'
GO

GO

ALTER ENDPOINT Db_MirroringEP STATE = STARTED

–在服务器之间手动拷贝证书,保障各种服务只器都有全数证书

  1. 假设期望日志运维在异步形式下,更正同步形式


    –在主服务器上运转USE [master]
    GO
    ALTER DATABASE DemoDB SET PARTNER SAFETY OFF
    GO

RESTORE DATABASE [people] FROM DISK
= N’F:\people.bak’

GO

–创设顾客,用于访谈MI兰德瑞鹰RO科雷傲
IF EXISTS(select * from sys.certificates WHERE name=’HOST_B_cert’)
    DROP CERTIFICATE HOST_B_cert
GO

 

WITH FILE
= 1,

–在MILacrosse-W上,创造数据库镜像端点,用于目击通信

IF EXISTS(select * from sys.sql_logins WHERE name=’HOST_B_login’)
    DROP LOGIN HOST_B_login
GO

构造结束后,能够利用镜像监视器来查阅

NORECOVERY, NOUNLOAD, REPLACE, STATS = 10

CREATE ENDPOINT Db_MirroringEP

IF EXISTS(select * from sys.database_principals WHERE  name=’HOST_B_user’)
    DROP USER HOST_B_user
GO

–===================================================================

GO

AS TCP (LISTENER_PORT = 5022)

create LOGIN HOST_B_login WITH PASSWORD = ‘123456789’;
create USER HOST_B_user FOR LOGIN HOST_B_login;
create CERTIFICATE 
    HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = ‘D:\MIRROR\HOST_B_cert.cer’;
GO

常规妹子镇邪

RESTORE LOG [people] FROM DISK
= N’F:\people.bak’

FOR DATABASE_MIRRORING (ROLE = WITNESS, ENCRYPTION = SUPPORTED);

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
GO

图片 3

WITH FILE
= 2,
NORECOVERY, NOUNLOAD, STATS = 10

GO

–创造客商,用于访问WITNESS
IF EXISTS(select * from sys.certificates WHERE name=’HOST_C_cert’)
    DROP CERTIFICATE HOST_C_cert
GO

GO

ALTER ENDPOINT Db_MirroringEP STATE = STARTED

IF EXISTS(select * from sys.sql_logins WHERE name=’HOST_C_login’)
    DROP LOGIN HOST_C_login
GO

实践,以下步骤按编号挨个推行:

GO

IF EXISTS(select * from sys.database_principals WHERE  name=’HOST_C_user’)
    DROP USER HOST_C_user
GO

1.
主机上执行:

–在MISportage-A,MI中华V-B,MI揽胜极光-W上,检查端点配置

create LOGIN HOST_C_login WITH PASSWORD = ‘123456789’;
create USER HOST_C_user FOR LOGIN HOST_C_login;
create CERTIFICATE 
    HOST_C_cert AUTHORIZATION HOST_C_user FROM FILE = ‘D:\MIRROR\HOST_C_cert.cer’;
GO

USE master

SELECT * FROM sys.database_mirroring_endpoints

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login];
GO

GO

GO

–拷贝顾客
SELECT [name],[sid] FROM sys.database_principals WHERE [name]=’BetterDev’

–创立证书,并备份

–在MIHaval-A,MIQashqai-B,MI安德拉-W上,配置数据库镜像安全性,somodesql.com为自身的域名

–备份数据库

CREATE MASTER KEY
ENCRYPTION BY PASSWORD=N’joe123′;

use master

–设置同伴
ALTER DATABASE northwind SET PARTNER=’TCP://192.168.1.116:5022′;
GO
–设置亲眼看见
ALTER DATABASE NORTHWIND SET WITNESS=’TCP://192.168.1.117:5022′;
GO
–设置安全选项
ALTER DATABASE NORTHWIND SET SAFETY FULL

CREATE CERTIFICATE Cert_JOEPC

go

 

WITH SUBJECT=N’JOEPC
Certificate’,START_DATE=’20120405′,EXPIRY_DATE=’20990405′;

grant connect on endpoint::DB_MirroringEP to SOMODESQL/sqladmin

镜像服务器代码:

BACKUP CERTIFICATE Cert_JOEPC TO FILE=N’C:\Cert_JOEPC.cer’;

go

 

GO

–在MI奇骏-A上,对AdventureWorks数据库做完全备份

图片 4图片 5Code
USE [master]

–创设镜像端口

BACKUP DATABASE AdventureWorks TO DISK = C:/AdventureWorks.bak

–创制数据库主密钥。
IF EXISTS(SELECT * FROM sys.databases WHERE name=’master’ and is_master_key_encrypted_by_server=1)    
    –drop master key;
    OPEN MASTER KEY DECRYPTION BY PASSWORD=’123456789′;
ELSE
    create MASTER KEY ENCRYPTION BY PASSWORD=’123456789′;
GO

CREATE ENDPOINT EP_JOEPC_Mirr

GO

SELECT * FROM sys.key_encryptions;
go

STATE=STARTED