MsSqlServer Replication Distribution Overview

Replication scheme can be divided into snapshot Replication, transactional Replication, Peer-2-peer Replication, Merge Replication.
Snapshot Replication: Typically used for a one-time, full replication of a database.
Transactional Replication: Used for one-way replication from the primary database to the database.
Transactional publications with updatable subscriptions: publishers and Subscribers can modify independently and merge periodically
Merge Replication: You can merge data from multiple databases and copy them to the target database.

Note: A Windows account is required at the Publisher to read and write the files generated by the distribution, and in order to access the publication database with this account, you need to create this account on SQL Server, select Browse, select Windows account. Also, in order for the SQL Server service to read and write the files generated by the distribution, set the agent service to this system account. A Windows account is also established at the Subscriber to increase the privileges of the agent service and to write to the subscription data.
First, Preparation:
1. Establish a WINDOWS user on the distribution and postback database, set as administrator rights, and set the password as a valid access user to publish the snapshot files.
2. It is normal for the publisher and subscriber to communicate under SQL Server (that is, you can exchange visits). Open 1433 port and set the exception
3 in the firewall. Establish a shared directory at the publisher as the repository for publishing snapshot files. For example, the folder named Sqlcopy
4 is built under the D-Packing directory. Set SQL Agent (both Publisher and Subscriber settings) Open Service (Control Panel-Administrative Tools-services)-Right-click SQL Server agent-Properties-Login-Select "This Account"-Enter or select the Windows user created in the first step-enter the user password in the password
5. Set SQL Server Authentication, resolve permissions issues when connecting (publish, subscribers are set)
Step to: Object Explorer--right-click SQL Instance-Properties-security-Server Authentication-select SQL Server and Windows, then click OK
6. Open the SQL Server 2005 Network protocol TCP/IP and pipe naming protocol and restart the Network service.
7. In SQL Server, create the system user login name in step 1 as the owner of the publication database (set to Dbo_owner and public).

Second, start:
To build a shared folder on the distribution server
1, on the Distributor, right-click Replication, establish distribution, and note that the network path is required when specifying the snapshot path, such as: \\192.168.16.204\sqlCopy 204 is the Distributor. If you run replication Distribution "The process cannot read due to operating system error 3 ..." You want to see if the snapshot in the Distributor properties is pointing to a network connection with a snapshot of the Subscriber.
2, on the Publisher, right-click Copy-Local publish, build publish
2, at the Subscriber, right-click Replication-Local subscription, build publication, Distribution Agent Security
Run under the following Windows account, this account is the subscriber's account number, or select the agent
Connect to Distributor: Select the account of the database owner to be published by using the following SQL Server login Distributor

Publisher configuration (Configure publications and subscriptions at the publisher)
1. Select a Replication node
2. Right-click Local Publishing-Next ——— the System Pop-up dialog to see the prompt-until "Specify Snapshot folder"
--In the snapshot folder, enter the directory that you created in the preparation (point to the shared folder that you built in step 3)--Select the publication database ——-Select the publication type ——-Select the Subscriber type ——-select the object to publish-set the Snapshot Agent ——-fill in the publication name.
3. Right-click Local subscription--– Select a Publisher ——-Select a subscription (if you subscribe to a server, select push subscriptions and select pull subscriptions) ——-fill in the Subscriber--– Select the agent plan (which is generally selected for continuous operation) ——— the rest of the selection defaults.

Note: 1)
The release of the original database is generally divided into three parts: 1. Changes in table structure (including addition/deletion of tables, plus/delete); 2. Load configuration data (such as adding configuration data for new features); 3. Brush functions and stored procedure scripts.
For the replication database in this project, the following points should be noted in the release process: 1. If the newly added table needs to be replication, you will need to configure the table to replicate and initialize it, in addition to creating the table in the primary database. 2. To delete a table in replication, first cancel the replication of this table, and then drop the table in the main, from the library. 3. If you need to add/delete columns of the replication table (this column cannot be a primary key column), you can execute the script directly on the primary database, and the changes will be automatically replicate to the slave database. 4. The loading of the configuration data is only required to complete the primary database. 5. Functions and stored procedures need to be refreshed both in the master and from the library.
2) Adding columns and deleting columns cannot be done directly on manage studio, otherwise the table ' Frmuser ' cannot be deleted because it is being used for replication. (. Net SqlClient Data Provider, at which point SQL statements are used.) The description of SQL Server is that when you make schema changes to a table, you must use Transact-SQL, and when you make schema changes in SQL Server Management Studio, Management Studio tries to delete and recreate the table. The schema change failed because the published object could not be deleted.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MsSqlServer Replication Distribution Overview