Detailed configuration and deployment of SQLServer replication and database images, detailed configuration of sqlserver

Detailed configuration and deployment of SQLServer replication and database images, detailed configuration of sqlserver

SQLserver can deploy images and copies at the same time, and combine the high availability of both parties to ensure better Database Availability and Disaster Tolerance.

About images: database images

About copying: database images

Copy in this chapter is a transaction updatable subscription: updatable subscription of transaction Replication

About copying and database images: copying and database images

For more information about the theory, see the official document. Here we mainly deploy the configuration process.

For this chapter, refer to the deployment architecture diagram:

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	Image (Mirror)
Kk-db3	192.168.2.12	WITNESS machine (WITNESS) + distribution
Kk-db4	192.168.2.13	Subscription (Test 1)

Pay attention to the following requirements and precautions when using a copy with a database image:

1. The master database and the image database must share the distribution server. We recommend that you use a remote distribution server. If the distribution server experiences an unexpected failover, the remote distribution server can provide greater fault tolerance capabilities.

2. For merged replication and transaction Replication Using the read-only subscription server or queuing Update Subscription server, replication supports mirroring the published database. The subscription server, Oracle Publishing Server, and publishing server in the Peer topology cannot be updated in real time.

 

3. Metadata and objects stored outside the database are not copied to the image database, including the login name, job, and linked server. If you require the image database to have metadata and objects, you must manually copy them.

Follow these steps to configure replication and database mirroring:

1. Configure the database image. (Refer to SQLServer database image (2) complete image script configuration in the domain environment)

2. Configure the distributor. (Refer to how to configure the distributor and how to enable the remote distributor on the distributor)

3. Configure publishing and subscription. (Refer to how to create updatable subscriptions for transactional Publishing. SqlServer uses scripts to create updatable subscriptions for distribution services and transaction replication)

Note:

This test uses the domain account as the database Logon account: [KK \ UserReplMirror]

Corresponding Database User: [UserReplMirror]

Test Database: [DemoDB]

Ensure that servers can access each other

Database [DemoDB] recovery mode is full (images are required)

Change the database [DemoDB] owner to [sa]

Account [KK \ UserReplMirror] grants sysadmin permission; or In database [DemoDB], grant user [UserReplMirror] database role to [db_owner]

The owner of the copied proxy job is changed to [sa].

1. Configure the database image:

-- Create database use [master] gocreate database [DemoDB] goalter database [DemoDB] set recovery full with NO_WAITGO -- Subject: Create certificate and backup USE master; go create master key encryption by password = 'kk _ 2012 @ '; go create certificate Cert_kk_db1_mssqlserver with subject = 'cert _ kk_db1_mssqlserver', START_DATE = '2017-06-01 ', EXPIRY_DATE = '2017-06-01 '; go backup certificate Cert_kk_db1_mssqlserver to file = 'C: \ Databases \ Cert_kk_db1_mssqlserver.cer'; GO -- image: Create a CERTIFICATE and back up USE master; go create master key encryption by password = 'kk _ 2012 @ '; go create certificate Cert_kk_db2_mssqlserver with subject = 'cert _ kk_db2_mssqlserver', START_DATE = '2017-06-01 ', EXPIRY_DATE = '2017-06-01 '; go backup certificate Cert_kk_db2_mssqlserver to file = 'C: \ Databases \ Cert_kk_db2_mssqlserver.cer'; GO -- Witness: Creating certificates and backing up USE master; go create master key encryption by password = 'kk _ 2012 @ '; go create certificate Cert_kk_db3_mssqlserver with subject = 'cert _ kk_db3_mssqlserver', START_DATE = '2017-06-01 ', EXPIRY_DATE = '2017-06-01 '; go backup certificate Cert_kk_db3_mssqlserver to file = 'C: \ Databases \ Cert_kk_db3_mssqlserver.cer'; GO -- exchange CERTIFICATE (mutual CERTIFICATE copy ): /* subject certificate (copy to) ----> image, witness image certificate (copy to) ----> subject, witness certificate (copy) ----> subject, image */-- subject (CREATE user, restore certificate, and CREATE endpoint): USE master go 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_kk_db3_mssqlserver.cer '; gocreate endpoint [Endpoint_For_Mirror] AUTHORIZATION [KK \ UserReplMirror] STATE = started as tcp (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR data_appsing (ROLE = PARTNER, AUTHENTICATION = CERTIFICATE failed, 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 \ certificates'; go create certificate [Cert_kk_db3_mssqlserver] AUTHORIZATION [UserReplMirror] from file = 'C: \ Databases \ users'; gocreate endpoint [Endpoint_For_Mirror] AUTHORIZATION [KK \ UserReplMirror] STATE = started as tcp (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR data_grouping ing (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 a user, restore a certificate, and CREATE an 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_db2_mssqlserver] AUTHORIZATION [UserReplMirror] from file = 'C: \ Databases \ Cert_kk_db2_mssqlserver.cer '; GOUSE master; create endpoint [Endpoint_For_Mirror] AUTHORIZATION [KK \ UserReplMirror] STATE = started as tcp (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR data_appsing (ROLE = WITNESS, AUTHENTICATION = certificate authentication, ENCRYPTION = required algorithm aes) GO -- grant connect on endpoint: Endpoint_For_Mirror TO [KK \ UserReplMirror]; -- GO -- Subject: backup 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 -- image: 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 \ DemoDB. mdf ', MOVE n' DemoDB _ log' TO n' C: \ Databases \ DemoDB_log.ldf', NOUNLOAD, NORECOVERY, STATS = 10 go restore database [DemoDB] from disk = n'c: \ Databases \ DemoDB_LOG.BAK 'with norecoverygo -- start image -- execute in image, PARTNER is the master server USE [master] go alter database [DemoDB] set partner = 'tcp: // 192.168.2.10: 8080'; GO -- runs on the [subject, PARTNER is the backup server USE [master] go alter database [DemoDB] set partner = 'tcp: // 192.168.2.11: 8080'; go alter database [DemoDB] set witness = 'tcp: // 192.168.2.12: 5022 '; GO -- run on the subject: 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 distributor

Log on to the server.

Kk-db3

192.168.2.12

WITNESS machine (WITNESS) + distribution

Right-click Copy to configure distribution.

The distributor selects the kk-db3 local server. Add KK-DB1 (host) andThe KK-DB2 (image) is the publisher, otherwise the distributor cannot be connected.

Note: The data management password is required when you create a distributor. Right-click "copy"-"distributor attributes"-"publisher" to view and set the settings.

Configure a replication proxy for failover

You can configure the proxy of the PublisherFailoverPartner parameter:
1-copy snapshot proxy (for all releases)
2-copy the Log Reader Agent (released for all transactions)
4-copy and merge proxy (for merge subscriptions)
9-replication Queue Reader Agent (for transaction publishing that supports queuing Update Subscription)

The current subscription is updatable. You only need to change 1, 2, and 9 (executed by the Distribution library)

-- @ Parameter_value is the image service exec sp_add_agent_parameter @ profile_id = 1, @ parameter_name = N'-PublisherFailoverPartner ', @ parameter_value = n' kk-db2 'exec region @ 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-db'

Restart the SQL Server proxy.

3. Configure publishing and subscription

Log on to the server

Kk-db1

192.168.2.10

Host (Replication + Mirror)

Create a local release and select the distribution server. (Only the information of the Publishing Server added to the distribution can be accessed here)

Enter the management password set on the distributor.

Note: In this case, the copy-related job proxy generated on the distributor (192.168.2.12) is changed to [sa].

Log on to the server

Kk-db4

192.168.2.13

Subscription

Create subscription: (select "publish"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-click Add publisher, and add the publisher kk-db1 to see replication monitoring

Configuration completed !~

4. Test

Now stop the master (Replication + Mirror) Service:

After the stop, go to the original image (kk-db2) view, the image becomes the main body, the local release also appears (the original image does not exist)

However, when I insert data from the current subject (kk-db2), the data is not synchronized to the subscription !~

Where is the configuration incorrect ??!~~

After searching on the Internet, it is found to be a BUG because:

This problem occurs because Log Reader Agent does not copy the transactions that are marked for replication from the transaction 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 has been hardened to the transaction log of the mirror database.

FIX: Changes in the publisher database are not replicated to the subscribers in a transactional replication if the publisher database runs exposed in a database refreshing session in SQL Server 2005

It is not fixed for the time being. You can set it in other ways:

(Start the SQL Server service for the kk-db1 first)

In the subject and image, set the service startup parameters, add-T1448, and restart the service.

In this case, the master instance is stopped and the image becomes the master instance. The operation data can be synchronized with the subscription !~

At this point, even if the test is complete, it may be wrong.