sql server 2012--集群采用多数节点的方式加Servercore

最新推荐文章于 2022-11-30 23:51:43 发布

congjie1830

最新推荐文章于 2022-11-30 23:51:43 发布

阅读量212

点赞数

一，前言

自MS SQL SERVER 2005以来，MSFT都该产品做了大量改进，在每个版本都有不少闪光点，MS SQL SERVER 2012也不例外，ALwaysOn group就是其中最闪亮的一个功能点。本文章旨在介绍在Windows ServerCore模式下搭建AlwaysOn Group的方式。

二，实验环境

Server name	role	IP	OS Version	remark
Server1	DC	192.168.133.10 /255.255.255.0	Windows server 2008 Enterprise 64bits sp1 servercore
Servercore01	SQL Server 服务器1	192.168.133.30 /255.255.255.0	Windows server 2008 r2 datacenter 64 bits sp1 servercore
Servercore02	SQL Server 服务器2	192.168.133.31 /255.255.255.0	Windows server 2008 r2 datacenter 64 bits sp1 servercore
Servercore03	SQL Server 服务器3	192.168.133.32 /255.255.255.0	Windows server 2008 r2 datacenter 64 bits sp1 servercore
Tools	工具服务器	192.168.133.36 /255.255.255.0	Windows server 2008 Enterprise 64bits sp1	Servercore 版本上的sql没有SSMS,因此需要有一台装有SSMS的服务器，以方便管理

Server1 ：

ServerCore01:

ServerCore02:

Servercore03:

注：

1，安装Servercore,请参照相应手册，这里略

2，此实验，由于集群是采用多数节点模式，因此至少要三台服务器参与集群，我这里安装了3台虚机

3，三台成员服务器安装的windows 2008 R2 servercore 64bits sp1

一，成员服务器的配置及加域，在所有服务器参与集群的成员服务器上执行这些步骤：

A,修改新装好的服务器的名字，这里以servercore01为例

--rename a server

netdom renamecomputer %computername% /newname:servercore01

B，设置静态IP和DNS服务器

--set static ip address for specfic network card

netsh interface ipv4 set address name="Local Area Connection"

source=static address="192.168.133.30"

mask="255.255.255.0" gateway="192.168.133.10"

--set dns server

netsh interface ipv4 add dnsserver name="Local Area Connection"

address="192.168.133.10" index=1

C,重启

D，加域

netdom join %ComputerName% /domain:contoso.com /userD:administrator /passwordD:*

E，重启

F, 设置管理员密码。在命令行窗口键入:

net user administrator *

G,将域账号加入本地管理员组，这个账号作为sql服务的启动账号

Net localgroup administrators /add contoso\sqladmin

H, 开启远程桌面，这样就可以通过mstsc管理了

Cscript. %systemroot%\system32\SCregedit.wsf /ar 0

I,设置防火墙

netsh firewall set service remoteadmin enable

--disable firewall

netsh firewall set opmode disable

J，为了支持集群，必须安装下面这些组件

--安装组件命令

Dism.exe /Online /Enable-Feature /FeatureName:Microsoft-Hyper-V /FeatureName:NetFx2-ServerCore /FeatureName:MicrosoftWindowsPowerShell

/FeatureName:ServerManager-PSH-Cmdlets /FeatureName:BestPractices-PSH-Cmdlets /FeatureName:FailoverCluster-Core /FeatureName:WindowsServerBackup

K,安装SQL 2012 for servercore

Setup.exe /qs /ACTION=Install /INSTANCEDIR=c:\mssqlserver\software /INSTALLSQLDATADIR=c:\mssqlserver\sqldata /SQLTEMPDBDIR=c:\mssqlserver\tempdata /FEATURES=SQLEngine,Replication /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="contoso\sqladmin" /SQLSVCPASSWORD="yourpassword" /SQLSYSADMINACCOUNTS="contoso\sqladmin" /AGTSVCACCOUNT="NT AUTHORITY\Network Service" /TCPENABLED=1 /IACCEPTSQLSERVERLICENSETERMS

二，安装windows多数节点集群

建立windows server 2008 server core CLUSTER

A,验证先决条件，

输入windows cluster的clustername以及IP

输入 windows cluster 的 clustername 以及 IP，

这里可以输入多个子网IP地址

确认信息，如果觉得不合适，可以退回去修改

创建中。。。。。。

创建成功

集群一览

集群仲裁方式，多数节点

检查集群的情况，我这里略

一，开启SQL server的always 选项，如下：

1，登录tools服务器,按如下次序操作

开始—〉管理工具—〉计算机管理：

然后重启SQL 服务，每个节点都要重复上述操作

四， SQL Server 2012 Alwayson group配置

A,如果还没有登录tools服务器，请登录，launch

ssms,连接到servercore01, servercore02, servercore03

B,建立测试数据库和表，插入初始记录

C，选定servercore01做primary，并导航至[AlwaysOn High Availability]，右击开始建立

有效性检查

配置summary

创建中。。。。。。

创建成功

下面是配置此alwaysOn Group的代码：

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT. IN SQLCMD MODE.

:Connect 192.168.133.30

USE [master]

CREATE ENDPOINT [Hadr_endpoint]

AS TCP (LISTENER_PORT = 5022)

FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0

BEGIN

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED

END

use [master]

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [CONTOSO\sqladmin]

:Connect servercore02

USE [master]

CREATE ENDPOINT [Hadr_endpoint]

AS TCP (LISTENER_PORT = 5022)

FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0

BEGIN

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED

END

use [master]

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [CONTOSO\sqladmin]

:Connect servercore03

USE [master]

CREATE ENDPOINT [Hadr_endpoint]

AS TCP (LISTENER_PORT = 5022)

FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0

BEGIN

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED

END

use [master]

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [CONTOSO\sqladmin]

:Connect 192.168.133.30

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')

BEGIN

ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);

END

IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')

BEGIN

ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;

END

:Connect servercore02

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')

BEGIN

ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);

END

IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')

BEGIN

ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;

END

:Connect servercore03

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')

BEGIN

ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);

END

IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')

BEGIN

ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;

END

:Connect 192.168.133.30

USE [master]

CREATE AVAILABILITY GROUP [SQLAG]

WITH (AUTOMATED_BACKUP_PREFERENCE = NONE)

FOR DATABASE [db_1], [db_2]

REPLICA ON N'SERVERCORE01' WITH (ENDPOINT_URL = N'TCP://SERVERCORE01.contoso.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),

N'SERVERCORE02' WITH (ENDPOINT_URL = N'TCP://SERVERCORE02.contoso.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),

N'SERVERCORE03' WITH (ENDPOINT_URL = N'TCP://SERVERCORE03.contoso.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));

:Connect 192.168.133.30

USE [master]

ALTER AVAILABILITY GROUP [SQLAG]

ADD LISTENER N'ServerCore_LS' (

WITH IP

((N'192.168.133.40', N'255.255.255.0')

)

, PORT=11116);

:Connect servercore02

ALTER AVAILABILITY GROUP [SQLAG] JOIN;

:Connect servercore03

ALTER AVAILABILITY GROUP [SQLAG] JOIN;

:Connect 192.168.133.30

BACKUP DATABASE [db_1] TO DISK = N'\\tools\backup\db_1.bak' WITH COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5

:Connect servercore02

RESTORE DATABASE [db_1] FROM DISK = N'\\tools\backup\db_1.bak' WITH NORECOVERY, NOUNLOAD, STATS = 5

:Connect servercore03

RESTORE DATABASE [db_1] FROM DISK = N'\\tools\backup\db_1.bak' WITH NORECOVERY, NOUNLOAD, STATS = 5

:Connect 192.168.133.30

BACKUP LOG [db_1] TO DISK = N'\\tools\backup\db_1_20120411094958.trn' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5

:Connect servercore02

RESTORE LOG [db_1] FROM DISK = N'\\tools\backup\db_1_20120411094958.trn' WITH NORECOVERY, NOUNLOAD, STATS = 5

:Connect servercore03

RESTORE LOG [db_1] FROM DISK = N'\\tools\backup\db_1_20120411094958.trn' WITH NORECOVERY, NOUNLOAD, STATS = 5

:Connect 192.168.133.30

BACKUP DATABASE [db_2] TO DISK = N'\\tools\backup\db_2.bak' WITH COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5

:Connect servercore02

RESTORE DATABASE [db_2] FROM DISK = N'\\tools\backup\db_2.bak' WITH NORECOVERY, NOUNLOAD, STATS = 5

:Connect servercore03

RESTORE DATABASE [db_2] FROM DISK = N'\\tools\backup\db_2.bak' WITH NORECOVERY, NOUNLOAD, STATS = 5

:Connect 192.168.133.30

BACKUP LOG [db_2] TO DISK = N'\\tools\backup\db_2_20120411094958.trn' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5

:Connect servercore02

RESTORE LOG [db_2] FROM DISK = N'\\tools\backup\db_2_20120411094958.trn' WITH NORECOVERY, NOUNLOAD, STATS = 5

:Connect servercore03

RESTORE LOG [db_2] FROM DISK = N'\\tools\backup\db_2_20120411094958.trn' WITH NORECOVERY, NOUNLOAD, STATS = 5

通过SSMS查看AlwaysOn Group:

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/101629/viewspace-761740/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/101629/viewspace-761740/

congjie1830

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫