SQL Server 2016 非域Aways On环境搭建

一、优点

aways on的优点，a. 构建主从数据库，分摊单点数据库压力。b.可以减少数据同步时间，提升用户体验。c.可以实现高可用，自动平滑切换。

二、缺点

及时同步最多只能提交3台，及时同步会导致各服务器性能下降。数据写入只能主库写入。基于服务器故障转移群集服务，需要服务器相互之间信任，非域下，数据服务本身需要互相信任。

三、具体实现步骤（基于阿里云服务器 Windows Server 2016 数据中心GUI版本）

数据库服务器镜像设置。（建议与生产环境的配置一致（主要是内存））

为了方便多台服务器的配置，减少配置时间，一些公共的配置放在此处。

购买临时服务器，建议系统盘在60GB大小，减少允许过程中系统存储空间不足的情况。
安装集群和IIS。
修改计算机名后缀ankec.net。
安装SQL Server 2016数据库服务。
安装SSMS客户端。
创建系统管理员账号 admincluster，并设置为administrators组成员（也可以直接使用administrator账号来操作），设置sql服务的启动账户为该账户。
设置数据库服务的cpu和内存等配置。
创建数据库证书导入导出存储过程。（参看附件1）
创建数据库备份存储过程。（参看附件2）
参照十万并发设置设置服务器，并设置服务器连接超时时间。
设置群集管理、IIS、数据库客户端、数据库服务端配置等快捷方式到桌面或者任务栏。
重启服务器（防止服务器本身系统问题占用过多内存）。
打镜像服务。
释放临时服务器。
通过数据库服务器镜像购买主数据库服务器。根据需要额外挂载数据磁盘。建议是数据库文件大小的10倍以上。
1. 建议密码保持不变。
2. 建立共享文件夹，设置everyone读写权限。
3. 在数据库层级测试共享权限。（可以备份一个简单库到共享文件路径的方式进行测试）
4. 设置数据库服务器名。（只能在此处设置，具体代码参看附件3，设置后重启数据库服务）
通过数据库服务器镜像购买从数据库服务器。根据需要额外挂载数据磁盘。建议是数据库文件大小的10倍以上。（多台可以同时购买）
1. 建议密码与主数据库服务器保持不变。
2. 在数据库层级测试共享权限。（可以备份一个简单库到共享文件路径的方式进行测试）
3. 设置数据库服务器名。（只能在此处设置，具体代码参看附件3，设置后重启数据库服务）
故障转移群集搭建
1. 配置服务器hosts名映射。将各台服务器名称和局域网IP的映射关系添加到所有服务器hosts文件中去。例如：计算机名fuwuqiming（ip：172.18.13.2）需要添加两条记录到各服务器hosts中 172.18.13.2 fuwuqiming 和 172.18.13.2 fuwuqiming.ankec.net。（具体添加cmd代码可以自己构建，也可以手动添加，具体代码可以参看附件4）
2. 各台服务器之间相互ping一下对方的名称，确保可以正常ping通。
3. 主数据库建立群集 servercluster，将各服务器添加到此集群中，添加时采用服务器名+后缀的方式进行添加。（只有添加到群集中后才能进行下一步）
4. 在各台数据库配置中，设置服务的属性中的aways on的√，重启数据库服务。
5. 设置各服务器数据库的默认存放路径。（重要，特别是从库必须要设置，否则后面没有机会处理）
6. 导出各数据库服务器的数据库证书到主数据库服务器的共享文件夹。（各数据库上调用导出存储过程）
7. 导入除了本数据库服务器的证书外，其它的数据库服务器的证书。
搭建数据库的aways on
1. 主数据库上建立数据库（还原数据库也可以）。设置数据库的模式为完整备份模式。
2. 备份数据库。
3. 在aways on 上右键创建aways on，并根据向导进行操作，加入从数据库，在最后列中选择是。并根据向导引导一直点击下一步一直到关闭完成。
4. 设置主数据的代理任务和自动备份任务。

至此，aways on 的配置结束。整个配置过程必须按照顺序一步一步进行，不能随意调整顺序，如果调整可能会造成无法预知的错误。如果出错后不能解决，可以考虑还原系统的方式来初始化。

注意事项：

1．数据库权限问题，可以使用administrator来解决。

2．数据库证书导出前，必须保证可以访问共享文件夹，否则不可继续，一旦继续，则会出现无法解决的错误。

附录1：

导出证书

CREATE PROCEDURE CreateEndpointCert

 @ShareName SYSNAME ,

 @StrongPassword SYSNAME

AS BEGIN

 

--This must be executed in the context of Master

 IF (DB_NAME() <> 'master')

 BEGIN

  PRINT N'This SP must be executed in master.  USE master and then retry.'

  RETURN (-1)

 END

 

    DECLARE @DynamicSQL varchar(1000);

    DECLARE @CompName varchar(250);

 DECLARE @HasMasterKey INT;

    SELECT @CompName = CONVERT(SysName, SERVERPROPERTY('MachineName'));

 

-- Only create a master key if it doesn't already exist

 

 SELECT @HasMasterKey = is_master_key_encrypted_by_server from sys.databases where name = 'master'

 IF (@HasMasterKey = 0)

 BEGIN

 --Create a MASTER KEY to encrypt the certificate.

  SET @DynamicSQL = CONCAT('CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' ,  QUOTENAME(@StrongPassword, ''''));

  EXEC (@DynamicSQL)

 END

 

--Create the certificate to authenticate the endpoint
SET @DynamicSQL = CONCAT('CREATE CERTIFICATE ', QUOTENAME(@CompName + '-Certificate'), ' WITH SUBJECT = ', QUOTENAME(@CompName, ''''),',EXPIRY_DATE=',QUOTENAME(20991231 ,'''') )

EXEC (@DynamicSQL);


--Create the database mirroring endpoint authenticated by the certificate.

    SET @DynamicSQL =

        CONCAT('CREATE ENDPOINT Endpoint_Mirroring

            STATE = STARTED

            AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

            FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE ',QUOTENAME(@CompName + '-Cert'), ' , ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL)');

    EXEC (@DynamicSQL);

 

   --Back up the certificate to a common network share for import into other nodes in the cluster

    SET @DynamicSQL = CONCAT('BACKUP CERTIFICATE ',QUOTENAME(@CompName + '-Cert'),' To FILE = ', QUOTENAME( @ShareName + '\SQL-' + @CompName + '.cer', ''''));

    EXEC (@DynamicSQL);

END

GO

导入证书

CREATE PROCEDURE InstallEndpointCert

    @CompName SYSNAME,

 @ShareName SYSNAME,

 @StrongPassword SYSNAME

AS BEGIN

    DECLARE @DynamicSQL varchar(1000);

    DECLARE @MyCompName varchar(250);

    SELECT @MyCompName = CONVERT(SysName, SERVERPROPERTY('MachineName'));

   --Don't need to create LOGINs for the local system

    IF (@MyCompName <> @CompName)

    BEGIN

        SET @DynamicSQL = CONCAT('CREATE LOGIN ', QUOTENAME (@CompName + '-Login'), ' WITH PASSWORD= ', QUOTENAME( @StrongPassword, ''''));

        EXEC (@DynamicSQL);

 

        SET @DynamicSQL = CONCAT('CREATE USER ', QUOTENAME( @CompName + '-User'), ' FOR LOGIN ', QUOTENAME(@CompName + '-Login'));

        EXEC (@DynamicSQL);

 

        SET @DynamicSQL = CONCAT('CREATE CERTIFICATE ', QUOTENAME(@CompName +'-Cert'), ' AUTHORIZATION ', QUOTENAME(@CompName +'-User'), ' FROM FILE = ', QUOTENAME(@ShareName + '\SQL-' + @CompName + '.cer' , ''''));

        EXEC (@DynamicSQL);

 

        SET @DynamicSQL = CONCAT('GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ', QUOTENAME(@CompName +'-Login'));

        EXEC (@DynamicSQL);

 END

END

GO

附录2：

附录3：

修改数据库名称，修改后要重启服务

if serverproperty('servername')   <>   @@servername   

begin   

    declare   @server   sysname   

    set   @server   =   @@servername    

    exec   sp_dropserver   @server   =   @server 

    set   @server   =   cast(serverproperty('servername')   as   sysname)   

    exec   sp_addserver   @server   =   @server   ,   @local   =   'LOCAL'   

END

查询名称是否一致

use master 

go 

select @@servername 

select serverproperty('servername')

附录4：

写入hosts命令：

echo 172.18.12.153 iZiig0d4mlg0ksZ >> C:\Windows\System32\drivers\etc\hosts

附录5：

十万服务器并发设置

服务器设置：C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config

在%systemroot%\System32\inetsrv\config\applicationHost.config中可以查看到该设置：

服务器设置set.bat 内容：

echo "开始设置"

reg add HKLM\System\CurrentControlSet\Services\HTTP\Parameters /v MaxConnections /t REG_DWORD /d 100000

reg add HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\HTTP\Parameters /v MaxFieldLength /t REG_DWORD /d 32768

reg add HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\HTTP\Parameters /v MaxRequestBytes /t REG_DWORD /d 32768

reg add HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters /v MaxUserPort /t REG_DWORD /d 65534

reg add HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters /v TcpTimedWaitDelay /t REG_DWORD /d 30

net stop http  & net start http & iisreset

echo "设置结束"