SQL Server Replication Subscriptions

Original: SQL Server replication Subscription

Tags: SQL server/mssql server/Database/dba/High performance solution/high availability

Overview

Configuration replication without database mirroring and AlwaysOn requirements is so high, only two servers can communicate over TCP, both server operating system and SQL version can not be exactly the same, and two servers do not need to join the domain, so the configuration of replication subscriptions is much simpler, But it is also a disadvantage that replication subscriptions are primarily for data tables and cannot be configured as an entire database like mirroring and AlwaysOn.

The next major addition to the previous configuration replication subscription will explain some of the considerations.

Copy database: ADVENTUREWORKS2008R2

Directory

• Overview
• To Configure the distribution server
• Create a publication
• Create a subscription
• View Configuration
• Add a new publishing table
• Summary

To Configure the distribution server

Configure the Distributor and publisher as the same server.

1. Replication-Local Publishing-Configuration

2. Configure Distribution Wizard-Next

3.clu11 is the publisher, where you choose to configure CLU11 as the distributor at the same time, the distribution database is created on the CLU11

4. Select the snapshot path and the default path to

5. Distribution database name and distribution database data file, log file path

6. Default Next

7. Default Completion

8. Complete the distribution server configuration

Create a publication

1. Replication-Local Publishing-New publication

2. Select the database you want to publish

3. Select a publication type-transactional publication

There are four types of publishing type, here you configure the most common types of transactional publications

4. Select the tables that you want to configure the publication

Create a published table be sure to have a primary key column

5. View the published project properties, default configuration

6. In the table article properties, it is primarily the subscriber that configures which objects and settings are applied.

In the properties we can see the replication of clustered indexes, collations, DML operations, DLL operations, and so on to subscribers,

Because the general Publisher and subscriber business applications are not the same, the default is to replicate only these primary objects, not the nonclustered index foreign keys, etc., because at the subscriber it may be necessary to build the index of the business scenario that it needs.

7. Do not configure filtering, default next

8. Tick create snapshot now and remain available

9. Configure the Agent security account

10. The rep account used to connect to the Publisher must have owner rights to the ADVENTUREWORKS2008R2 database, and the server-level permissions public can

11. Create a publication-Next

12. Enter the name of the publication project

13. Complete the Release database configuration

Create a subscription

1. At the subscriber-replication-local subscription-new subscription

2. Select Publisher-Find SQL Server Publishers

3. Connecting to the CLU11 publishing server

4. Because only one publication is configured in the CLU11 server, the publication that was just configured is displayed by default

5. Configure the Distribution Agent location-choose to run all agents at the Distributor

6. Next Step

8. Configure the subscription agent security account

9. The account that connects to the Subscriber also needs the owner permission of the subscription database, and the server level is public.

10. Agent Plan-Continuous operation

11. Initialization time-Immediate

12. Create a subscription

13. Complete the subscription creation

14. Complete

View Configuration

1. The newly generated snapshot file path can be found under the path of the publisher CLU11

2. The "Distribution" distribution database was created in the system database of the CLU11 publishing server

3. At the Subscriber, the subscription database "ADVENTUREWORKS2008R2" generated two published tables,

Note: The subscription database does not need to be the same as the publication database, don't be misunderstood here

4. A publishing project was created under replication at the publisher

5. View Replication Monitor, where you can configure and view the Publish subscription and monitor the distribution, you can configure the warning

6. Publish Properties-Here you can configure and modify the publication

7. Subscription properties-Here you can modify your subscription secure login account

8. Three jobs were generated in the agent for the publisher, respectively, and distributed about the snapshot log read job, and the publish job related to the publication, and the subscription-related distribution job.

Add a new publishing table

1. In Replication Monitor-my publisher-clu11-right-click the Publish item-Properties, remove the "show only selected items in list" check box

Tick the "awbuildversion" table to confirm the save

Create a published table be sure to have a primary key column

2. Replication Monitor-My publisher-clu11-right-click Publish project-Generate snapshot

3. View the generated subscription table in the subscription database

4. Under the snapshot folder of the publisher, you can see that a new snapshot folder has been generated

5. Viewing subscription properties, you can see the distribution history

Summary

Because the Distributor and the Publisher are configured on the same server, there is some pressure on the publisher, in the ordinary course of operations, if you want to delete a field from the published table before you first want to see whether the index of the field is created in the subscription table, if you create the need to remove the field from the index, Failure to do so will result in a publish subscription error.

SQL Server High-availability scenarios Daquan:

SQL Server alwayson:http://www.cnblogs.com/chenmh/p/4484176.html

SQL Server Image: http://www.cnblogs.com/chenmh/p/4452902.html

SQL Server transaction log transfer: Http://www.cnblogs.com/chenmh/p/3671030.html

SQL Server replication: http://www.cnblogs.com/chenmh/p/4487766.html

Failover cluster: http://www.cnblogs.com/chenmh/p/4479304.html

if the article is helpful to everyone, please point a recommendation, thank you!!!

Note: pursuer.chen Blog:http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly to the link, otherwise reserves the right to hold responsibility. Welcome to the exchange of discussions

SQL Server Replication Subscriptions