Shulou(Shulou.com)06/01 Report--

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.

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.