环境:
Sqlserver 2014 alwayson 架构
主节点1个:已经有数据库在alwayson架构里面
辅助节点2个:已经有数据库在alwayson架构里面
目的:
现在在主节点有一个单独的库,要让这个库变成alwayson架构。
把主节点的这个库加入alwayson
步骤:
1、备份主节点的独立数据库
use master
GO
BACKUP DATABASE [EDI_VIP_Import] TO DISK = N'D:\dbbackup\EDI_VIP_Import_full.bak'
WITH COMPRESSION, NOFORMAT, NOINIT,stats=5
GO
2、还原数据库到辅助节点1和辅助节点2
辅助节点1:
use master
go
restore database [EDI_VIP_Import] from disk=N'\\10.0.2.100\d$\dbbackup\EDI_VIP_Import_full.bak'
with norecovery,stats=5
go
辅助节点2:
use master
go
restore database [EDI_VIP_Import] from disk=N'\\10.0.2.100\d$\dbbackup\EDI_VIP_Import_full.bak'
with norecovery,stats=5
go
3、备份主节点的事务日志:
use master
GO
BACKUP log [EDI_VIP_Import] TO DISK = N'D:\dbbackup\EDI_VIP_Import_log.bak'
WITH COMPRESSION, NOFORMAT, NOINIT,stats=5
GO
4、还原辅助节点1和辅助节点2的事务日志:
use master
GO
restore log [EDI_VIP_Import] from disk=N'\\10.0.2.100\d$\dbbackup\EDI_VIP_Import_log.bak'
with norecovery,stats=5
go
use master
GO
restore log [EDI_VIP_Import] from disk=N'\\10.0.2.100\d$\dbbackup\EDI_VIP_Import_log.bak'
with norecovery,stats=5
go
5、主节点上可用性组添加数据库:
use master
GO
ALTER AVAILABILITY GROUP [WMSEDI_AlwaysOn] ADD DATABASE [EDI_VIP_Import]
6、辅助节点上设置副本:
use master
GO
ALTER DATABASE [EDI_VIP_Import] SET HADR AVAILABILITY GROUP = [WMSEDI_AlwaysOn]
GO
use master
GO
ALTER DATABASE [EDI_VIP_Import] SET HADR AVAILABILITY GROUP = [WMSEDI_AlwaysOn]
GO
7、主节点上检查同步情况:
exec opsdb.dbo.spm_AG_Moniter
Sqlserver 2014 alwayson 架构
主节点1个:已经有数据库在alwayson架构里面
辅助节点2个:已经有数据库在alwayson架构里面
目的:
现在在主节点有一个单独的库,要让这个库变成alwayson架构。
把主节点的这个库加入alwayson
步骤:
1、备份主节点的独立数据库
use master
GO
BACKUP DATABASE [EDI_VIP_Import] TO DISK = N'D:\dbbackup\EDI_VIP_Import_full.bak'
WITH COMPRESSION, NOFORMAT, NOINIT,stats=5
GO
2、还原数据库到辅助节点1和辅助节点2
辅助节点1:
use master
go
restore database [EDI_VIP_Import] from disk=N'\\10.0.2.100\d$\dbbackup\EDI_VIP_Import_full.bak'
with norecovery,stats=5
go
辅助节点2:
use master
go
restore database [EDI_VIP_Import] from disk=N'\\10.0.2.100\d$\dbbackup\EDI_VIP_Import_full.bak'
with norecovery,stats=5
go
3、备份主节点的事务日志:
use master
GO
BACKUP log [EDI_VIP_Import] TO DISK = N'D:\dbbackup\EDI_VIP_Import_log.bak'
WITH COMPRESSION, NOFORMAT, NOINIT,stats=5
GO
4、还原辅助节点1和辅助节点2的事务日志:
use master
GO
restore log [EDI_VIP_Import] from disk=N'\\10.0.2.100\d$\dbbackup\EDI_VIP_Import_log.bak'
with norecovery,stats=5
go
use master
GO
restore log [EDI_VIP_Import] from disk=N'\\10.0.2.100\d$\dbbackup\EDI_VIP_Import_log.bak'
with norecovery,stats=5
go
5、主节点上可用性组添加数据库:
use master
GO
ALTER AVAILABILITY GROUP [WMSEDI_AlwaysOn] ADD DATABASE [EDI_VIP_Import]
6、辅助节点上设置副本:
use master
GO
ALTER DATABASE [EDI_VIP_Import] SET HADR AVAILABILITY GROUP = [WMSEDI_AlwaysOn]
GO
use master
GO
ALTER DATABASE [EDI_VIP_Import] SET HADR AVAILABILITY GROUP = [WMSEDI_AlwaysOn]
GO
7、主节点上检查同步情况:
exec opsdb.dbo.spm_AG_Moniter
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22996654/viewspace-2146906/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22996654/viewspace-2146906/