MSSQLSERVER Release-distribution-subscription

First, the environment

Publishing Server

O s:windows Servier 2003 64-bit

Soft:microsoft SQL Server R2

I p:192.168.3.70

Host-name:wxl-pc

Distribution server

O s:windows Servier 2003 64-bit

Soft:microsoft SQL Server R2

I p:192.168.23.130

Host-name:wxl-pc-vm2

Subscription:

O s:windows Servier 2003 64-bit

Soft:microsoft SQL Server R2

I p:192.168.23.129

Host-name:wxl-pc-vm1

Second, the configuration

1. Modify the DB instance name (skip this step if the computer name is not modified)

Because the name of the Distributor has been modified, you need to modify the instance name to keep the server name consistent with the instance name

query instance name [email protected] @servername

Remove old strength name Execsp_dropserver ' wxl-pc-vm2 '

Add the instance name Execsp_addserver ' wxl-pc-vm2\sql1 ', ' local '

Restart Service

Inquiry [email protected] @SERVERNAME, serverproperty (' SERVERNAME ')

Two columns of data are consistent, then the strength name modified successfully.

2. Configure aliases

Distribution server:

Start SQL Server Configuration Manager

SQL Server network configuration àtcp/ipà enabled

SQL Native Client 10.0 Configuration (32-bit) à alias à new alias such as:

< yo? " http://www.2cto.com/kf/ware/vc/"target=" _blank "class=" Keylink ">vcd4kcjxwpsu1w/ejuiagicagsfdd+9a4t6kyvc+ 2qdtet/7o8cb3w/s8l3a+cgo8cd4gicagicagicagicagicagicagtsu/ 2rrf1rhnu1nxbhnlcnzlcsq508o1xlblv9q6xtwvcd4kcjxwpiagicagicagicagicagicagicc3/s7xxvfwuleisrwvtqnuxlf+ zvhg90lqpc9wpgokpha+u3fsie5hdgl2zsbdbgllbnqgmtaummxk1sooplhww/uopndcvaix8mp7im2syc8gpc9wpgokpha+ 0ttjz8xk1spn6rpjuvojrmjnufu7ucrhzt63qns2s8zbrl3ttb3k/b7dv+kjrnty0n64xm+1zbpf5nbdzss8/ jwvcd4kcjxwpkm6xfdjtkrpv1ncc3lzdgvtmzjczhjpdmvyc1xldgncag9zdhmgicagvnpi6yagicagicagpc9wpgokpha+ mtkylje2oc4ymy4xmjkgd3hslxbjlxztmtwvcd4kcjxwpje5mi4xnjgumy43mcagihd4bc1qqzwvcd4kcjxwpreisry3/s7xxvehorap1ms3/ s7xxvex8mp7xetww7lov7y31reit/7o8cb3xetwwzwvcd4kcjxwpsu1w/ejuta71qez1rf+zvhg98p7jim0mzvktcd9w/ smizqzo7blv9q6xbe9yr23w87kpc9wpgo8cd4zoalf5nbdt9a3orf+ zvhg9zwvcd4kcjxwprxhwrxtcwwgu2vydmvyie1hbmfnzw1lbnqgu3r1zglvqks4tnbgqktf5nbdt9a3oidi58/czbw8l3a+ cjxwpjxpbwcgc3jjpq== "http://www.2cto.com/uploadfile/Collfiles/20140411/2014041109103238.png" alt= "\">

Click the Next button, such as:

Select native to act as your own distributor, click the Next button, such as:

To set a snapshot file path, click the Next button, such as:

Enter the name of the distribution database and click the Next button, such as:

Click the Add button and select Add SQL Server Publisher, such as:

Select the Publisher that you just added, click the Next button, such as:

Set a password yourself, click the "Next" button, such as:

Click the Next button, such as:

Click the Finish button, such as:

After the status bar shows success, click the Close button to complete the distribution server configuration.

4. Configuring the Publisher

Log on to SQL Server Management studioà copy à local publication à right-click: Create a new publication such as:

Click the Next button, such as:

Select the database that contains the data or objects you want to publish, and click the Next button, such as:

Description

) Snapshot publication

Snapshot replication is the simplest and most intuitive type of replication that can be used to obtain a photo or snapshot of a database and propagate it to subscribers. The advantage is that there is no persistent resource overhead at the publisher and Subscriber, and the database at the Subscriber is simply the state at the time the snapshot was taken.

) Transactional Publishing

Transactional replication can be used to replicate tables and stored procedures, and any changes made to an item can be continuously and automatically captured from the transaction log and propagated to the Distributor through transactional replication. You can keep the Publisher and subscribers in almost the same state.

) Merge publication

Merge replication is similar to transactional replication when tracking changes made to an item, but it does not propagate the transaction that made the change, but instead periodically propagates all changes made to the database. These changes can be batched and sent as needed. Because the changes are sent in bulk, merge replication is a bit like a combination of transactional replication and snapshot replication.

Select a publication type, this example selects a transactional publication, click the Next button, such as:

Select the tables you want to copy, and click the Next button, such as:

You can filter the fields that need to be copied in the table, this example does not need to filter the data in the publication, and click the Next button, such as:

Choose to create a snapshot immediately and keep the snapshot available to initialize the subscription. Click the Next button, such as:

Click the Security Settings button to specify the Snapshot Agent account information, such as:

When you are finished, click the OK button and click the Next button, such as:

Click the Next button, such as:

Enter a publication name and click the Finish button, such as:

After the status bar shows success, click the Close button. Expand Local publishing to see the newly created publication, such as:

The publisher configuration is complete.

5. Configuring Subscribers

Log on to SQL Server Management studioà copy à local subscription à new subscription, such as:

Click the Next button, such as:

Locate the SQL Server Publisher, and select the name of the publication you want to subscribe to, such as:

Click the Next button, such as:

Select Run all agents at the Distributor, and click the Next button, such as:

Expand the subscription Database drop-down menu, select New Database, select Database as subscription, and click Next, such as:

Click the button to set Subscriber account information such as:

Click the OK button and click the Next button, such as:

The agent plan selects run continuously and clicks the Next button, such as:

Click the Next button, such as:

Click the Next button, such as:

Click the Finish button, such as:

The status bar displays success, and click the Close button.

The Subscriber configuration is complete. Expand the local subscription to see the subscription that you just created.

Third, testing

1. On the test database at the publisher

deletefromtest1wherecityid>500

2. Login to the Subscriber server

The visible data is synchronized to the Subscriber.

MSSQLSERVER Release-distribution-subscription