MSSQLSERVER publishing-distribution-subscription

I. Environment

Publishing Server

O s: Windows servier 2003 64-bit

Soft: Microsoft SqlServer 2008 R2

I P: 192.168.3.70

HOST-NAME: wxl-PC

Distributor

O s: Windows servier 2003 64-bit

Soft: Microsoft SqlServer 2008 R2

I P: 192.168.23.130

HOST-NAME: wxl-pc-vm2

Subscription:

O s: Windows servier 2003 64-bit

Soft: Microsoft SqlServer 2008 R2

I P: 192.168.23.129

HOST-NAME: wxl-pc-vm1

Ii. Configuration

1,Modify the database instance name (skip this step if the computer name is not modified)

Because the distributor name is modified, You need to modify the Instance name to make the server name consistent with the instance name.

Query Instance name select @ servername

Delete old strength name EXECsp_dropserver 'wxl-pc-vm2'

Add the repaired Instance name execsp_addserver 'wxl-pc-vm2 \ sql1', 'local'

Restart service

Query SELECT @ SERVERNAME, SERVERPROPERTY ('servername ')

If the two columns are consistent, the strength name is modified successfully.

2. Configure aliases

Distributor:

Start SQL Server Configuration Manager

SQL Server network configuration à TCP/IP à Enabled

SQL Native Client 10.0 configuration (32-bit) à alias à New Alias such:

<喎?http: www.bkjia.com kf ware vc " target="_blank" class="keylink"> Release/ejuiAgICAgsfDD + 9a4t6KyvC + 2 qdTEt/7O8cb3w/s8L3A + release/release + release/b7dv + KjrNTy0N64xM + release/release /examples/ejuta71qez1rf + zvHG98P7JiM0MzvKtcD9w/examples + examples/Czbw8L3A + cjxwpjxpbwcgc3jjjpq = "http://www.2cto.com/uploadfile/Collfiles/20140411/2014041109103238.png" alt = "\">

Click "Next", for example:

Select the local server as your distribution server and click "Next", for example:

Set a snapshot file path and click "Next", for example:

Enter the name of the distribution database and click "Next", for example:

Click "add" and select "add SQL Server Release Server", as shown in the following figure:

Select the new publishing server and click "Next", as shown in the following figure:

Set a password and click "Next", for example:

Click "Next", for example:

Click "finish", for example:

After the status bar shows "successful", click the "close" button to complete the configuration of the distributor.

4. Configure the Publishing Server

Log on to SQL Server Management Studio, click Copy local release, and right-click Create release, for example:

Click "Next", for example:

Select the database that contains the data or objects to be published, and click "Next", for example:

Note:

) Snapshot Publishing

Snapshot replication is the simplest and most intuitive replication type. You can use this copy to obtain database photos or snapshots and distribute them to the subscription server. The advantage is that it does not cause sustained resource overhead on the Publishing Server and subscription server. The database on the subscription server is only in the status when the snapshot is obtained.

) Transaction Publishing

Transaction replication can be used to copy tables and stored procedures. Through transactional replication, any changes made to the project can be continuously and automatically captured from the transaction log and propagated to the distributor. The Publishing Server and subscription server can be in almost the same status.

) Merge and publish

Merge replication is similar to transaction replication in tracking changes made to the project, but it does not disseminate the changed transactions, but regularly propagates all changes made to the database. These changes can be processed and sent in batches as needed. Because changes are sent in batches, the combined replication is similar to the combination of transaction replication and snapshot replication.

Select the release type. In this example, select transaction release and click "Next", as shown in the following figure:

Select the table to be copied and click "Next", as shown in the following figure:

You can filter fields to be copied in the table. In this example, you do not need to filter published data. Click "Next", for example:

Select create snapshot now and make the snapshot available to initialize the subscription. Click "Next", for example:

Click the "Security Settings" button to specify the snapshot Proxy account information, such:

Click OK and click Next, as shown in the following figure:

Click "Next", for example:

Enter the release name and click "finish", for example:

After the status bar shows "successful", click the "close" button. Expand local release and you can see the new release, for example:

The publishing server configuration is complete.

5. Configure the subscription Server

Log on to SQL Server Management Studio to copy a local subscription to create a subscription, for example:

Click "Next", for example:

Find the SQL Server Publishing Server and select the name of the Publishing Server to be subscribed to, for example:

Click "Next", for example:

Select "Run all agents on the distributor" and click "Next", for example:

Expand the subscribe database drop-down menu, select create database, select as subscription database after creating the database, and click Next, as shown in:

Click the button to set the account information of the subscription server, for example:

Click OK and click Next, as shown in the following figure:

Select "continuous running" as the proxy plan and click "Next", as shown in the following figure:

Click "Next", for example:

Click "Next", for example:

Click "finish", for example:

The status bar shows "successful" and click "close.

The subscription server configuration is complete. Expand local subscription to see the newly created subscription.

Iii. Test

1. test Database on the Publishing Server

VPC: deletefromtest1wherecityid> 500

2. log on to the subscription Server

The data has been synchronized to the subscription server.