SQL Server implements failover-sqlserver mirrored backups for failover (3)

Purpose: Install sqlserver2012 on a computer that has been added to the domain, and configure database mirroring for real-time synchronization and failover.

Establishment of domain control see: https://www.cnblogs.com/xiaoerlang90/p/9224745.html

Computer plus domain: https://www.cnblogs.com/xiaoerlang90/p/9234805.html

Domain control:

Name: dctest.com

ip:192.168.31.20

User name: Sql_admin

Password: dcTest2018

Primary database server (added domain):

Computer Name: db01-pc

ip:192.168.31.201

Domain user/password: sql_admin/dctest2018

Operating system: Unlimited.

Database: sqlserver2012 R2

Secondary database server (added domain):

Computer Name: db02-pc

ip:192.168.31.202

Domain user/password: sql_admin/dctest2018

Operating system: Unlimited.

Database: sqlserver2012 R2

Witness database server (added domain):

Computer Name: wt-pc

ip:192.168.31.196

Domain user/password: sql_admin/dctest2018

Operating system: Unlimited.

Database: sqlserver2012 R2

---------------------------------------------------------------------------------------------------------

Steps

Configure firewall: Because the firewall will automatically open when the computer is added to the domain, you need to configure the db01-pc, DB02-PC, wt-pc Three server's inbound rules (where 1433 ports are the SQL Server instance default port, 5022 is the listening port), and the outbound rule is the inbound rule:

Three servers are installed sqlserver2012 R2, installation steps are not mentioned here.

Configure the SQL Server service login account separately

Go to DB01-PC, open SQL Server Management Studio, and create an endpoint named "Mirrored" using the SQL statement in 2012:

Use [master]
CREATE ENDPOINT [Image]
state=started

As TCP (Listener_port = 5022, listener_ip = All)

For data_mirroring (ROLE = PARTNER, authentication = WINDOWS NEGOTIATE

, encryption = REQUIRED algorithm RC4)

Create the database you want to back up: Synctest. and back up

After the backup is complete, copy the backup files to the DB02-PC server and then restore them to the mirror database. When using database mirroring technology, the mirror database needs to be in the "Restore with NoRecovery" state. Be sure to tick the "restore with NoRecovery" option during the restore operation. ：

Start configuring Database Mirroring Backup and failover: Right-click the database selection you want to back up-tasks-mirroring,:

Run mode Select "High Security with failover (synchronous) ... , click Configure security to start the configuration.

Our demo uses 2 SQL Server servers, one witness server, configure the "Include witness server instance" step needs to check the option to "Yes", select click Next, Pop Up "Select the server to configure" dialog box by default check "Witness server instance", confirm error Click Next. ：

In the principal Server Instance dialog box, the listener ports and endpoint names that have been configured natively are displayed by default, and click Next when you are sure that they are correct. ：

Configure the mirror database, select browse more, pop up the DB Instance Login window, enter db02-pc IP, select SQL Server Authentication, enter username and password to login,

Configure the witness server, step with the mirror server configuration step.

After confirming the IP, listening port, and the endpoint name is correct, click "Next":

To set up a DB instance login account:

Finally, after confirming that all the information is correct, click "Finish" to complete the database mirroring configuration. ：

5. Complete the configuration

Once you have completed the above configuration, you will be prompted to ask if you want to start mirroring now. Click "Do not start mirroring" to return to the configuration dialog box. ：

in the Configuration dialog box, after verifying the configuration information, click "Start Mirroring":

At this point the primary server database displays state such as:

The secondary server displays such as:

When you disconnect the primary server, it automatically switches to the secondary server, the secondary server becomes the primary server, and the primary and secondary server roles can be toggled by switching failover.

Verify that the DB01 and DB02 can be backed up and switched on if the domain control server is disconnected. You can start the database to enjoy the monitor to see the database status:

For servers that have been added to the domain, how to use the cache to log on to a domain account in the case of a domain:

can enter local policy (Run command: gpedit.msc)

Open Group Policy (either the default Domain policy or the Create policy under the OU)-Interactive logon: The number of times you logged in to the cache before (domain controller is not available), can be set according to the actual situation, set to 0 is forbidden cache.

SQL Server implements failover-sqlserver mirrored backups for failover (3)