用于配置 SQL Server 镜像的示例脚本 (SharePoint Foundation 2010)
适用于: SharePoint Foundation 2010
上一次修改主题: 2016-11-30
本文包含可用于为测试 Microsoft SharePoint Foundation 2010 环境建立 Microsoft SQL Server 镜像的一系列脚本示例。建议 SQL Server 数据库管理员为生产环境配置镜像。
要使用 SharePoint Foundation 2010 建立数据库镜像,必须对要镜像的每个数据库执行单独操作。
本文内容:
使用证书和完整恢复配置数据库镜像
设置见证服务器
将权限传输到镜像服务器
以下章节中的步骤适用于下列服务器场拓扑:
一台或多台前端 Web 服务器
运行 SQL Server 2008 的服务器有三种:主体服务器、镜像服务器和见证服务器
一个配置数据库
多个内容数据库
一个或多个应用程序数据库
使用证书和完整恢复配置数据库镜像
每个步骤都列出了应在其上执行该步骤的服务器。使用 Transact-SQL 将这些命令发送至 SQL Server。占位符信息由尖括号 (<>) 表示,应使用特定于您的部署的信息替换其中的内容。
为出站连接设置主体服务器
在主体服务器上,创建一个证书并打开镜像端口。
--On the master database, create the database master key, if needed CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<test1234->'; GO -- Make a certificate for this server instance. USE master; CREATE CERTIFICATE <MASTER_HostA_cert> WITH SUBJECT = '<Master_HostA certificate>'; GO --Create a mirroring endpoint for server instance by using the certificate CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=5024 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE <MASTER_HostA_cert> , ENCRYPTION = REQUIRED ALGORITHM RC4 , ROLE = ALL ); GO
在主体服务器上,备份该证书。
--Back up the HOST_A certificate. BACKUP CERTIFICATE MASTER_HostA_cert TO FILE = '<c:\MASTER_HostA_cert.cer>'; GO
在主体服务器上,备份数据库。本例使用配置数据库。对所有数据库重复此操作。
USE master; --Ensure that SharePoint_Config uses the full recovery model. ALTER DATABASE SharePoint_Config SET RECOVERY FULL; GO USE SharePoint_Config BACKUP DATABASE SharePoint_Config TO DISK = '<c:\SharePoint_Config.bak>' WITH FORMAT GO BACKUP Log SharePoint_Config TO DISK = '<c:\SharePoint_Config_log.bak>' WITH FORMAT GO
将备份文件复制到镜像服务器。对所有数据库重复此操作。
使用任意安全复制方法,将备份证书文件(例如,C:\HOST_HostA_cert.cer)复制到镜像服务器。
在主体服务器上,为镜像服务器创建登录名和用户、将证书与用户相关联,并为此合作关系授予登录连接权限。
--Create a login on HOST_A for HOST_B USE master; CREATE LOGIN <HOST_HostB_login> WITH PASSWORD = '<1234-test>'; GO --Create a user for that login. CREATE USER <HOST_HostB_user> FOR LOGIN <HOST_HostB_login>; GO --Associate the certificate with the user CREATE CERTIFICATE <HOST_HostB_cert> AUTHORIZATION <HOST_HostB_user> FROM FILE = '<c:\HOST_HostB_cert.cer>' --do not use a network path, SQL Server will give an error about the key not being valid GO --Grant CONNECT permission on the login for the remote mirroring endpoint. GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [<HOST_HostB_login>]; GO
为出站连接设置镜像服务器
在镜像服务器上,创建一个证书并打开镜像端口。
--On the master database, create the database master key, if needed. USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1234-test>'; GO -- Make a certificate on the HOST_B server instance. CREATE CERTIFICATE <HOST_HostB> WITH SUBJECT = '<HOST_HostB certificate for database mirroring>'; GO --Create a mirroring endpoint for the server instance on HOST_B. CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=5024 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE <HOST_HostB> , ENCRYPTION = REQUIRED ALGORITHM RC4 , ROLE = ALL ); GO
在镜像服务器上,备份该证书。
--Back up the HOST_B certificate. BACKUP CERTIFICATE <HOST_HostB> TO FILE = '<C:\HOST_HostB_cert.cer>'; GO
使用任意安全复制方法,将备份证书文件(例如,C:\HOST_HostB_cert.cer)复制到主体服务器。
在镜像服务器上,从备份文件还原数据库。本例使用配置数据库。对所有数据库重复此操作。
RESTORE DATABASE SharePoint_Config FROM DISK = '<c:\SharePoint_Config.bak>' WITH NORECOVERY GO RESTORE log SharePoint_Config FROM DISK = '<c:\SharePoint_Config_log.bak>' WITH NORECOVERY GO
为入站连接设置镜像服务器
在镜像服务器上,为主体服务器创建登录名和用户、将证书与用户相关联,并为此合作关系授予登录连接权限。
--Create a login on HOST_B for HOST_A USE master; CREATE LOGIN <MASTER_HostA_login> WITH PASSWORD = '<test1234->'; GO --Create a user for that login. CREATE USER <MASTER_HostA_user> FOR LOGIN <MASTER_HostA_login>; GO --Associate the certificate with the user CREATE CERTIFICATE <MASTER_HostA_cert> AUTHORIZATION <MASTER_HostA_user> FROM FILE = '<c:\MASTER_HostA_cert.cer>' --do not use a network path, SQL Server will give an error about the key not being valid GO --Grant CONNECT permission on the login for the remote mirroring endpoint. GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [<MASTER_HostA_login>]; GO
为入站连接设置主体服务器
在主体服务器上,为镜像服务器创建登录名和用户、将证书与用户相关联,并为此合作关系授予登录连接权限。
--Create a login on HOST_A for HOST_B USE master; CREATE LOGIN <HOST_HostB_login> WITH PASSWORD = '<1234-test>'; GO --Create a user for that login. CREATE USER <HOST_HostB_user> FOR LOGIN <HOST_HostB_login>; GO --Associate the certificate with the user CREATE CERTIFICATE <HOST_HostB_cert> AUTHORIZATION <HOST_HostB_user> FROM FILE = '<c:\HOST_HostB_cert.cer>' --do not use a network path, SQL Server will give an error about the key not being valid GO --Grant CONNECT permission on the login for the remote mirroring endpoint. GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [<HOST_HostB_login>]; GO
设置镜像合作关系
在主体服务器上,设置镜像合作关系。本例使用配置数据库。对所有数据库重复此操作。
--At HOST_A, set the server instance on HOST_B as a partner (mirror server). ALTER DATABASE SharePoint_Config SET PARTNER = '<TCP://databasemirror.adatum.com:5024>'; GO
在镜像服务器上,设置镜像合作关系。本例使用配置数据库。对所有数据库重复此操作。
--At HOST_B, set the server instance on HOST_A as a partner (principal server): ALTER DATABASE SharePoint_Config SET PARTNER = '<TCP://databasemaster.adatum.com:5024>'; GO
设置见证服务器
每个步骤都列出了应在其上执行该步骤的服务器。使用 Transact-SQL 将这些命令发送至 SQL Server。占位符信息由尖括号 (<>) 表示,应使用特定于您的部署的信息替换其中的内容。
在见证服务器上,设置证书并打开端口。
--On the master database, create the database master key, if needed CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1234test->'; GO -- Make a certificate for this server instance. USE master; CREATE CERTIFICATE <WITNESS_HostC_cert> WITH SUBJECT = '<Witness_HostC certificate>'; GO --Create a mirroring endpoint for server instance by using the certificate CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=5024 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE <WITNESS_HostC_cert , ENCRYPTION = REQUIRED ALGORITHM RC4 , ROLE = ALL ); GO
在见证服务器上,备份该证书。
--Back up the HOST_C certificate BACKUP CERTIFICATE <WITNESS_HostC_cert> TO FILE = '<c:\ WITNESS_HostC_cert.cer>'; GO
使用任意安全复制方法,将备份证书文件(例如,C:\WITNESS_HOSTC_cert.cer)复制到主体服务器和镜像服务器。
在见证服务器上,为主体服务器和镜像服务器创建登录名和用户、将证书与用户相关联,并为此合作关系授予登录连接权限。
--Create a login on witness HOST_C for principal HOST_A USE master; CREATE LOGIN <MASTER_HostA_login> WITH PASSWORD = '<test1234->'; GO --Create a user for that login. CREATE USER <MASTER_HostA_user> FOR LOGIN <MASTER_HostA_login>; GO --Associate the certificate with the user CREATE CERTIFICATE <MASTER_HostA_cert> AUTHORIZATION <MASTER_HostA_user> FROM FILE = '<c:\MASTER_HostA_cert.cer>' --do not use a network path, SQL Server will give an error about the key not being valid GO --Grant CONNECT permission on the login for the remote mirroring endpoint. GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [<MASTER_HostA_login>]; GO --Create a login for the mirror Host_B CREATE LOGIN <HOST_HostB_login> WITH PASSWORD = '<1234-test>'; GO --Create a user for that login. CREATE USER <HOST_HostB_user> FOR LOGIN <HOST_HostB_login>; GO --Associate the certificate with the user CREATE CERTIFICATE <HOST_HostB_cert> AUTHORIZATION <HOST_HostB_user> FROM FILE = '<c:\HOST_HostB_cert.cer>' --do not use a network path, SQL Server will give an error about the key not being valid GO --Grant CONNECT permission on the login for the remote mirroring endpoint. GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [<HOST_HostB_login>]; GO
在主体服务器上,为见证服务器创建登录名和用户、将证书与用户相关联,并为此合作关系授予登录连接权限。对镜像服务器重复此操作。
--Create a login on master HostA for witness HostC USE master; CREATE LOGIN <WITNESS_HostC_login> WITH PASSWORD = '<1234test->'; GO --Create a user for that login. CREATE USER <WITNESS_HostC_user> FOR LOGIN <WITNESS_HostC_login>; GO --Associate the certificate with the user CREATE CERTIFICATE <WITNESS_HostC_cert> AUTHORIZATION <WITNESS_HostC_user> FROM FILE = '<c:\WITNESS_HostC_cert.cer>' --do not use a network path, SQL Server will give an error about the key not being valid GO --Grant CONNECT permission on the login for the remote mirroring endpoint. GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [<WITNESS_HostC_login>]; GO
在主体服务器上,附加见证服务器。本例使用配置数据库。对所有数据库重复此过程。
--Set up the witness server ALTER DATABASE SharePoint_Config SET WITNESS = '<TCP://databasewitness.adatum.com:5024>' GO
将权限传输至镜像服务器
设置镜像数据库时,不会在镜像服务器上的主数据库和 msdb 数据库中自动配置将用于 SharePoint 场的数据库的 SQL Server 登录名和权限。您必须为所需登录名配置相应权限
建议运行脚本,以将您的登录名和权限从主体服务器传输至镜像服务器。知识库文章 918992:如何在 SQL Server 2005 实例之间传输登录名和密码 (https://go.microsoft.com/fwlink/?linkid=122053&clcid=0x804) 中提供了建议使用的脚本。
从服务器中删除镜像
要从服务器中删除镜像,请参阅如何:删除数据库镜像 (Transact-SQL) (https://go.microsoft.com/fwlink/?linkid=185070&clcid=0x804)。