A: Preparation conditions
<1> Software Preparation conditions
Machine A-side: SqlServer2005 Management Studio + winserver 2003 Enterprise (as publisher)
Machine B-side: Sqlserver2005 Management Studio Express + WinXP (as subscriber)
(You can use something else, but the Subscriber version must not be higher than the publisher version)
<2> Database Replication Readiness Conditions
1. All synchronized data tables use primary keys as much as possible, and if there is no primary key, SQL Server prompts the table to generate the primary key automatically
2. The Publisher, Distributor, and Subscriber must use the computer name to register SQL Server servers.
3. SQL Server must start the Proxy service, and the proxy service has to be run on the local computer's account. Do not use IP addresses and aliases for registration, such as local, "." and localhost and so on.
4. If it is not the same network segment or remote server, or can only use IP, can not use the computer name, the other party to register the server alias. Add their corresponding relationships to the local system network configuration file. The exact location of the file is in C:\Windows\system32\drivers\etc\hosts, where the configuration is similar to Linux:
192.168.0.1 server192.168.0.2 Client
5.SqlServer must start the proxy service, and the proxy service must be running on the local computer's account.
6. Both the Publisher and the Subscriber are setting MSDTC, allowing the Network access Control Panel---> Administrative Tools---> Component Services---> Computer---> My computer (Win7 continue to find-->distributed Transaction Coordinator---> Local DTC), right-click Properties, locate the MSDTC tab, tap Security settings, as follows:
OK, restart the MSDTC service.
II: Detailed procedures for preparing the work
1. Start the SQL Agent (SQLServerAgent) service.
2. Open the SQL Server 2005 network protocol TCP/IP and pipe naming protocol and restart the Network service (configured in SQL Server Configuration Manager).
3. Set SQL Server Authentication for mixed authentication SQL Server and Windows (publish, subscriber settings)
4. Publisher and Subscribers register with each other
The steps are as follows: View---Click registered Server---Right-clicking the database engine---New server registration----fill in the name of the remote server to be registered----Authentication Select SQL Server Authentication----user name (SA) password, for IP only, not computer name, Register the server alias for it, see above.
The preparation is done and the next thing we do is publish and subscribe
Three: Publish and subscribe
Release
(1) First we open SqlServer2005 Management Studio (Enterprise Edition) on the publisher (winserver2003). Locate the replication node in the Object Explorer, right----> New Publication (Note: If your SQL Server is an express version, there is no release of this feature, only the subscription function!)
(2) Next select the database to publish, Next, select "Publish Type", I choose "Merge publication" here,
I understand several types of publishing:
- snapshot replication: Snapshot replication only at a certain time to the publication data published in the database to take photos, and then copy the data to the subscriber's server, snapshot replication is relatively simple, it is only a moment of time the database of the data, so real bad, It is also noteworthy that snapshot replication is not constantly monitoring changes in the publishing database, it is the publication database for the opposite sex scan, all published data from the source database sent to the target database, rather than just the change of data. If the amount of data is large, then there is a lot of data to replicate. Therefore, the network resource requirements are very high, not only to have a faster transmission speed, but also to ensure the reliability of transmission. Snapshot replication is the simplest type of replication that ensures consistent data between publishers and Subscribers, and snapshot replication is typically used in situations where a large number of changes occur over a period of time, but the amount of data is small and the period of change is longer.
- Transactional replication: Snapshot replication sends the entire data set to the Subscriber, which results in a replication lag due to the large size and long replication cycles. Then transactional replication uses the transaction log to generate transactions that replicate to subscribers because it replicates only transactions that are also variable, so lag is much lower than snapshot replication because it will continue to be applied at the Subscriber at all time.
Transactional replication has three components:
The Snapshot Agent, which generates the schema, data, and the data required to track the replication process;
Distribution Agent: It distributes the snapshot and subsequent commands;
Log Reader Agent: It reads the transaction log of the published data. In transactional replication, this change is immediately communicated to subscribers when the publication database changes. And in a relatively short period of time (a few seconds), rather than a long time interval like snapshot replication. Therefore, transactional replication is a method of distributing data from source to destination in near real time. The frequency of transactional replication is high for some reason. It is important to ensure a reliable network connection between subscribers and publishers.
- Merge replication: Merge replication is designed for mobile users, can be modified at the Publisher or at the Subscriber, and when the Merge Agent is running, these modifications are synchronized and used for both the publisher and the subscription service to modify the data. Works as follows: Implement the trigger on each table that you want to replicate, and use the Include GUID column to uniquely identify each row in the table that you want to copy. When you modify any of these tables, the changes are recorded in a data table that, when the Merge Agent runs, collects the GUIDs in the data tables that indicate the rows that were modified at the publisher and at the Subscriber. For data that is modified only at the publisher or at the Subscriber, such as insert,update,delete, if both parties have GUIDs, the conflict is resolved in a user-specified manner, with the default publisher server Volt-first.
To configure replication:
Whether it's snapshot replication, transactional replication, or merge replication, there are several steps to creating replication:
1. Create a publisher. Select the server you want to publish. If you have a condition, you can also distribute the server, where we set the Publisher and Distributor on the same computer.
2. The proxy service must be turned on either by the Publisher or the Subscriber.
3. Create a publication. Publish the databases and objects that will be needed.
4. Choose a release type that suits you.
5. Sets the replication agent and security, which specifies the user account that can run the agent.
(3) Next, select the Subscriber type, select "SqlServer2005", Next, select the Publish project, we select the table, view, stored procedure and so on.
(4) Next set up the job schedule, in order to easily see the test results, we set "every minute" to execute the job.
(5) Setting Snapshot Agent Security, when connecting to the publisher, using the SA
(6) To publish a name, complete the release
Subscription
Next, we configure the Subscriber. Subscribers are WinXP systems and SQL Server version is SQL Server 2005 Manager Studio Express, which only supports subscriptions.
(1) Same release, new subscription
(2) Select the Publisher, find our newly-created release "Test3copy", Next
(3) Select the location to run the agent, choose whether to "push" or "request" according to the requirement
(4) Next similar to the release, here will not repeat, the subscription execution succeeds, in the subscription database and the publication database in the table will have a field, SQL Server automatically generated flag subscription success
SqlServer2005 database Publishing, subscription configuration graphic detailed
