Sqlserver 2014 master and slave nodes use scripts to complete alwayson construction
Environment:
The two nodes of Sqlserver have completed the failover cluster construction:
Node 01: hereinafter referred to as 01, primary node
Node 02: hereinafter referred to as 02, is the secondary node
The startup account of the database service must be established in the database user.
01: create EndPoint and empower it
Create endpoint [Hadr_endpoint]
Authorization sa
State=started
As tcp (listener_port=5022,listener_ip=all)
For database_mirroring (role=all,authentication=windows, ENCRYPTION = REQUIRED ALGORITHM RC4)
Go
GRANT CONNECT ON ENDPOINT:: [Hadr_endpoint] TO [database service startup account, set to service01]
Go
02: create and empower EndPoint
Create endpoint [Hadr_endpoint]
Authorization sa
State=started
As tcp (listener_port=5022,listener_ip=all)
For database_mirroring (role=all,authentication=windows, ENCRYPTION = REQUIRED ALGORITHM RC4)
Go
GRANT CONNECT ON ENDPOINT:: [Hadr_endpoint] TO [service01]
Go
01: primary node database backup
Use master
GO
BACKUP DATABASE [test] TO DISK = nicked:\ dbbackup\ test_full.bak' WITH COMPRESSION, NOFORMAT, NOINIT
GO
02: restore database backup
Use master
Go
Restore database [test] from disk=N'D:\ dbbackup\ test_full.bak'
With
Move 'test' to'e:\ SQL_Data\ test.mdf'
, move 'test_log' to'e:\ SQL_Data\ test_log.ldf'
, norecovery,replace,stats=5
Go
01: primary node database log backup
BACKUP log [test] TO DISK = nicked:\ dbbackup\ test_log.bak' WITH COMPRESSION, NOFORMAT, NOINIT
GO
02: restore database log backup
Restore log [test] from disk=N'D:\ dbbackup\ test_log.bak'
With norecovery,stats=5
Go
01: the master node creates an AG
CREATE AVAILABILITY GROUP [test_AlwaysOn]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE [test]
REPLICA ON
Nevertest 01' WITH (
ENDPOINT_URL = Ninth TCPVUGUGUP Test01.pousheng.comRose 5022'
, FAILOVER_MODE = MANUAL
, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT
, BACKUP_PRIORITY = 50
, SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL))
, Nevertest02' WITH (
ENDPOINT_URL = Ninth TCPVUGUGUP Test02.pousheng.comRose 5022'
, FAILOVER_MODE = MANUAL
, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT
, BACKUP_PRIORITY = 50
, SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL))
02: join alwayson
ALTER AVAILABILITY GROUP test_AlwaysOn JOIN
02: set copy
ALTER DATABASE [test] SET HADR AVAILABILITY GROUP = [test_AlwaysOn]
01: change the synchronous mode to asynchronous
ALTER AVAILABILITY GROUP [test_AlwaysOn] MODIFY REPLICA ON test 02' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT)
Go
Just check the synchronization status.
OK .
Add new database:
01: availability group add database
ALTER AVAILABILITY GROUP [test_AlwaysOn] ADD DATABASE [xxx]
02: set copy
ALTER DATABASE [xxx] SET HADR AVAILABILITY GROUP = [test_AlwaysOn]
Add new nodes:
02: add nodes
ALTER AVAILABILITY GROUP [test_AlwaysOn]
ADD REPLICA ON 'test01'
WITH (
ENDPOINT_URL = 'TCP://test01.pousheng.com:5022'
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT
FAILOVER_MODE = MANUAL
BACKUP_PRIORITY = 50
SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
);
01: connect nodes
ALTER AVAILABILITY GROUP test_AlwaysOn JOIN
Query database backup execution script:
Select 'backup database [' + name+'] to disk=N''F:\ backup\'+ name+'.bak'' with STATS=5' as cmd
From sys.databases with (nolock)
Where database_id > = 4
Order by name
Query database restore execution script:
With tb as (
Select 'RESTORE DATABASE' + db_name (database_id) + 'FROM Disk=N''e:\ backup\' + db_name (database_id) + '.bak' 'WITH RECOVERY,' as db,'MOVE''+ name+''' TO'f:\ sqldata\'+ SUBSTRING (physical_name,len (physical_name)-CHARINDEX ('\', REVERSE (physical_name)) + 2recoery CHARINDEX ('\', REVERSE (physical_name)-1) +''as fl From sys.master_files with (nolock))
)
SELECT db+ [values] FROM (SELECT DISTINCT db FROM tb) A
OUTER APPLY (SELECT [values] = STUFF (REPLACE (REPLACE ((SELECT fl FROM tb N WHERE db = A.db FOR XML AUTO),','), 1, 1,') N order by db
Go
If you only set up mirror, do not join the alwayson cluster
After restoring the database log backup on 02, you can test port 5022 on both sides:
02:
ALTER DATABASE test SET PARTNER = N'TCP:// host IP:5022'
01:
ALTER DATABASE test SET PARTNER = N'TCP:// standby IP:5022' ALTER DATABASE test SET SAFETY OFF
If the report is wrong:
Maybe port 5022 is not open.
Or the database user of the opposite side does not have a startup account for the database service.
