SQL Server replication and database mirroring detailed configuration deployment

SQL Server can deploy mirroring and replication at the same time, combining high availability on both sides to ensure better availability and disaster recovery for the database.

About Mirroring: Database mirroring

About Replication: Database mirroring

Replication for this chapter is a transactional updatable subscription: updatable Subscriptions for transactional replication

About replication and database mirroring: Replication and Database Mirroring

The theory of Things refer to the official documentation, here is primarily the deployment configuration process.

Refer to the deployment Architecture diagram for this chapter:

This chapter simulates the server:

Kk-ad	192.168.2.1	DC (Domain control)
Kk-db1	192.168.2.10	Host (Replication + Mirror)
Kk-db2	192.168.2.11	Mirroring (Mirror)
Kk-db3	192.168.2.12	Witness Machine (WITNESS) + distribution
Kk-db4	192.168.2.13	Subscribe (Test 1)

When using replication with database mirroring, be aware of the following requirements and considerations:

1. The principal and mirror databases must share the Distributor. It is recommended that you use a remote distributor here, and the remote Distributor can provide greater fault tolerance if the publisher has an unexpected failover.

2. For merge replication, and for transactional replication using read-only Subscribers or queued updating subscribers, replication supports mirroring the publication database. Immediate updating of subscribers, Oracle Publishers, publishers, and republishing in a peer-only topology is not supported.

3. Metadata and objects that exist outside the database are not replicated to the mirrored database, including logins, jobs, linked servers, and so on. If you require metadata and objects in the mirrored database, you must copy them manually.

To configure replication and database mirroring main steps:

1. Configure database mirroring; (refer to SQL Server database Mirroring (ii) Full image script configuration in a domain environment)

2. Configure the Distributor; (refer to How to configure publishing and distribution, how to enable remote publishers at the distributor)

3. Configuring publications and subscriptions; (refer to how to create updatable subscriptions for transactional publications, SQL Server using scripts to create a distribution service and updatable subscriptions for transactional replication)

Description and notice of changes in the place:

This test uses the domain account as the database login account: [Kk\userreplmirror]

Corresponding database user: [Userreplmirror]

Test database: [Demodb]

Ensure that each server has access to each other

database [Demodb] recovery mode is full mode (mirror must)

Database [Demodb] owner changed to [SA]

account [Kk\userreplmirror] grants sysadmin permission, or grant user [Userreplmirror] database role to [db_owner] in database [Demodb]

The owner of the copied agent job is changed to [SA]

1. Configure Database Mirroring:

--Principal creation database use [master]gocreate database [Demodb]goalter database [Demodb] SET RECOVERY full with no_waitgo--body: Create certificate and Backup use  Master  GO CREATE MASTER KEY encryption by PASSWORD = ' [email protected] '; GO CREATE CERTIFICATE cert_kk_db1_mssqlserver with SUBJECT = ' Cert_kk_db1_mssqlserver ', start_date = ' 2015-06-01 ', expi  ry_date = ' 2018-06-01 ';  GO BACKUP CERTIFICATE cert_kk_db1_mssqlserver to FILE = ' C:\Databases\Cert_kk_db1_mssqlserver.cer ';  GO-Mirroring: Creating certificates and backing up use master;  GO CREATE MASTER KEY encryption by PASSWORD = ' [email protected] '; GO CREATE CERTIFICATE cert_kk_db2_mssqlserver with SUBJECT = ' Cert_kk_db2_mssqlserver ', start_date = ' 2015-06-01 ', expiry _date = ' 2018-06-01 ';  GO BACKUP CERTIFICATE cert_kk_db2_mssqlserver to FILE = ' C:\Databases\Cert_kk_db2_mssqlserver.cer ';  GO-Witness: Create certificate and backup use master;  GO CREATE MASTER KEY encryption by PASSWORD = ' [email protected] '; GO CREATE CERTIFICATE cert_kk_db3_mssqlserver with SUBJECT = ' cert_kk_db3_mssqlserver ', STArt_date = ' 2015-06-01 ', expiry_date = ' 2018-06-01 ';  GO BACKUP CERTIFICATE cert_kk_db3_mssqlserver to FILE = ' C:\Databases\Cert_kk_db3_mssqlserver.cer '; GO--Exchange certificate (Mutual copy certificate):/* Principal certificate (copy to) ———— > Mirror, witness image certificate (copy to) ———— > body, Witness Witness certificate (copy to) ———— > Principal, Mirror */--Body (create user, restore certificate, create end      Point): Use master GO to CREATE LOGIN [kk\userreplmirror] from WINDOWS;      GO CREATE USER [userreplmirror] for LOGIN [Kk\userreplmirror]; GO CREATE CERTIFICATE [cert_kk_db2_mssqlserver] AUTHORIZATION [Userreplmirror] from FILE = ' C:\Databases\Cert    _kk_db2_mssqlserver.cer '; GO CREATE CERTIFICATE [cert_kk_db3_mssqlserver] AUTHORIZATION [Userreplmirror] from FILE = ' C:\Databases\Cert_k    K_db3_mssqlserver.cer '; Gocreate ENDPOINT [Endpoint_for_mirror] AUTHORIZATION [Kk\userreplmirror] state=started as TCP (LIST Ener_port = 5022, listener_ip = All) for data_mirroring (ROLE = PARTNER, authentication = CERTIFICATE Cert_ Kk_db1_mssqlserver, ENCRYPTION = REQUIRED algorithm AES) GO--grant CONNECT on Endpoint::endpoint_for_mirror to [Kk\userreplmirror];      --go--image (create user, restore certificate, create Endpoint): Use master GO create LOGIN [kk\userreplmirror] from WINDOWS;      GO CREATE USER [userreplmirror] for LOGIN [Kk\userreplmirror]; GO CREATE CERTIFICATE [cert_kk_db1_mssqlserver] AUTHORIZATION [Userreplmirror] from FILE = ' C:\Databases\Cert    _kk_db1_mssqlserver.cer '; GO CREATE CERTIFICATE [cert_kk_db3_mssqlserver] AUTHORIZATION [Userreplmirror] from FILE = ' C:\Databases\Cert_k    K_db3_mssqlserver.cer '; Gocreate ENDPOINT [Endpoint_for_mirror] AUTHORIZATION [Kk\userreplmirror] state=started as TCP (LIST Ener_port = 5022, listener_ip = All) for data_mirroring (ROLE = PARTNER, authentication = CERTIFICATE Cert_ Kk_db2_mssqlserver, encryption = REQUIRED algorithm AES) GO--grant CONNECT on Endpoint::endpoint_for_mirror to [kk\    Userreplmirror]; --go--Witness (create user, restore certificate,Create Endpoint): Use master GO to create LOGIN [Kk\userreplmirror] from WINDOWS;      GO CREATE USER [userreplmirror] for LOGIN [Kk\userreplmirror]; GO CREATE CERTIFICATE [cert_kk_db1_mssqlserver] AUTHORIZATION [Userreplmirror] from FILE = ' C:\Databases\Cert    _kk_db1_mssqlserver.cer '; GO CREATE CERTIFICATE [cert_kk_db2_mssqlserver] AUTHORIZATION [Userreplmirror] from FILE = ' C:\Databases\Cert_k    K_db2_mssqlserver.cer ';    Gouse master; CREATE ENDPOINT [Endpoint_for_mirror] AUTHORIZATION [Kk\userreplmirror] state=started as TCP (LISTEN Er_port = 5022, listener_ip = All) for data_mirroring (ROLE = WITNESS, authentication = CERTIFICATE Cert_kk _db3_mssqlserver, encryption = REQUIRED algorithm AES) GO--grant CONNECT on Endpoint::endpoint_for_mirror to [Kk\userr    Eplmirror];  --go--body: Backing up the database use master; Backup DATABASE [Demodb] to disk = ' C:\Databases\DemoDB.BAK ' with Init,format GO BACKUP LOG [Demodb] to disk= ' C:\Databases\DemoDB_LOG.  BAK ' with Init,format GO--Mirror: Restore Database (NORECOVERY) use master; RESTORE DATABASE [Demodb]from DISK = n ' C:\Databases\DemoDB.BAK ' with FILE = 1, MOVE n ' demodb ' to n ' C:\Databases\Demo Db.mdf ', MOVE n ' demodb_log ' to n ' C:\Databases\DemoDB_log.ldf ', Nounload, NORECOVERY, STATS = ten GO RESTORE Databa SE [Demodb]from DISK = N ' C:\Databases\DemoDB_LOG. BAK ' with Norecoverygo--start mirroring-in "mirroring" execution, PARTNER primary server use [master] GO ALTER DATABASE [Demodb] SET PARTNER = ' tcp://192  .168.2.10:5022 ';  Go--in "main" execution, PARTNER for mirror server use [master] GO ALTER DATABASE [Demodb] SET PARTNER = ' tcp://192.168.2.11:5022 '; GO ALTER DATABASE [Demodb] SET WITNESS = ' tcp://192.168.2.12:5022 ';   go--in principal execution: set to High security mode ALTER DATABASE [Demodb] set SAFETY full

EXEC [Demodb].dbo.sp_changedbowner @loginame = N ' sa ', @map = falsegoexec Master: sp_addsrvrolemember @loginame = n ' kk\userreplmirror ', @rolename = n ' sysadmin ' GO

2. Configure the distribution server

Log on to the server.

Kk-db3

192.168.2.12

Witness Machine (WITNESS) + distribution

Right-click Copy, configure distribution.

The distributor selects the "KK-DB3" local server. Add "KK-DB1" (host) and "KK-DB2" (mirror) to the publisher, otherwise you cannot connect to the Distributor.

Note: The data management password is required when creating the Distributor. Right-click "Copy"-"Distributor Properties"-"publisher" to see and set.

Configure the replication agent for failover

Proxy for configurable parameter publisherfailoverpartner:
1-Copy Snapshot Agent (for all publications)
2-Replication Log Reader Agent (for all transactional publications)
4-Replication Merge Agent (for merge subscriptions)
9-Replication Queue Reader Agent (for transactional publications that support queued updating subscriptions)

Currently updatable subscriptions, as long as the 1,2,9 is changed (distribution library execution)

[Email protected]_value for image service exec sp_add_agent_parameter @profile_id = 1, @parameter_name = N '-publisherfailoverpartner ', @parameter_value = N ' kk-db2 ' exec sp_add_agent_parameter @profile_id = 2, @parameter_name = N '-publisherfailoverpartner ', @parameter_value = n ' kk-db2 ' exec sp_add_agent_parameter @profile_id = 9, @parameter_name = N '-publisherfailoverpartner ', @parameter_value = n ' kk-db2 '

Then restart SQL Server Agent

3. Configuring publications and Subscriptions

Log on to the server

Kk-db1

192.168.2.10

Host (Replication + Mirror)

Create a local publication and select a Distributor. (Only information in the distribution that has been added to the Publisher can be accessed here.)

Here you need to enter the administrative password set at the Distributor.

Note: The replication-related job agent that is generated at the Distributor (192.168.2.12) is changed to [SA]

Log on to the server

Kk-db4

192.168.2.13

Subscription

Create subscription: (Publish select "kk-db1")

After the subscription is created, initialize the subscription and log on to the server:

Kk-db1

192.168.2.10

Host (Replication + Mirror)

Log on to the server:

Kk-db3

192.168.2.12

Witness Machine (WITNESS) + distribution

Right-click "Copy"-"Start Replication Monitor", right "add publisher", add publisher "KK-DB1" to see Replication monitoring situation

Configuration is complete! ~

4. Testing

Now stop the principal (Replication + Mirror) Service:

After stopping, to the original mirror (KK-DB2) view, the mirror becomes the main body, the local publishing also appeared (the original image does not exist)

However, when I insert data from the current principal (KK-DB2), the data is not synced to the subscription! ~

Where is the wrong configuration??! ~~

After searching the web, it is found to be a bug because:

This problem occurs because Log Reader Agent does not copy of the transactions that is marked for replication from the Trans Action log into the distribution database. Log Reader Agent cannot read past a specific log sequence number (LSN). This specific LSN represents, the last LSN, that had been hardened to the transaction log of the mirror database.

Fix:changes in the Publisher database is not replicated to the subscribers in a transactional replication if the publish Er database runs exposed in a database mirroring session in SQL Server 2005

For the time being, it can also be set in other ways:

(Start KK-DB1 SQL Server service first)

In the principal and mirror, set the service startup parameters, add-t1448, and restart the service.

At this point, stop the principal instance, the mirror becomes the main body, the operation data is normal and subscription synchronization! ~

At this point, even if completed, personal testing, there may be a wrong place.

SQL Server replication and database mirroring detailed configuration deployment