Shulou(Shulou.com)05/31 Report--

This article is to share with you about Sqlserver 2014 alwayson architecture how to add the database on the auxiliary node, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Environment:

Sqlserver 2014 alwayson architecture

1 master node: there is already a database in the alwayson architecture

2 secondary nodes: there are already databases in the alwayson architecture

Objective:

Now there is a separate library on the main node, and you want to make this library an alwayson schema.

Add the library of the master node to alwayson

Steps:

1. Backup the stand-alone database of the primary node

Use master

GO

BACKUP DATABASE [EDI_VIP_Import] TO DISK = nicked:\ dbbackup\ EDI_VIP_Import_full.bak'

WITH COMPRESSION, NOFORMAT, NOINIT,stats=5

GO

2. Restore the database to secondary node 1 and secondary node 2

Secondary Node 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

Secondary Node 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. Back up the transaction log of the primary node:

Use master

GO

BACKUP log [EDI_VIP_Import] TO DISK = nicked:\ dbbackup\ EDI_VIP_Import_log.bak'

WITH COMPRESSION, NOFORMAT, NOINIT,stats=5

GO

4. Restore the transaction logs of secondary node 1 and secondary node 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. Add the database to the availability group on the primary node:

Use master

GO

ALTER AVAILABILITY GROUP [WMSEDI_AlwaysOn] ADD DATABASE [EDI_VIP_Import]

6. Set the copy on the secondary node:

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. Check the synchronization on the primary node:

Exec opsdb.dbo.spm_AG_Moniter

This is how the Sqlserver 2014 alwayson architecture adds databases on secondary nodes. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.