Let's take a look at a basic concept of replication.
What is replication? replication is the distribution of multiple copies (replicas) of data to each server in the company, providing the same data for multiple servers through replication. This allows the user to access the same information on different servers. for an enterprise with a large number of users, replication can spread the load of user access to the server.
What is a replication model? defines the relationship between the server and the copy of the data. There are three roles in the replication model, and their tasks vary. 1.
publisher : Provide data for replication to other servers. 2.
distributor : As a store between publications and subscriptions. 3.
Subscriber : Receive replicated data. A Database server instance can act as a Publisher and distributor two roles.
What is a replication type? the following three types of replication are available:1.
snapshot replication : Updates and modifications of data by the Publisher are periodically replicated to other servers. The disadvantage is that it can cause increased traffic. 2,
transactional replication : Data updates and modifications by the publisher, but replication is real-time, that is, when there is a change in data to trigger the behavior of replication, the advantage is that data replication can reduce the traffic. 3.
merge replication : Both the Publisher and Subscribers can update and modify the data. Replication is real-time in nature. When data synchronization occurs simultaneously, modifications to the publisher and changes to the Subscriber are merged together. The following is a case study of how replication is implemented, this time with merge replication as an example. Requirements:
demand: Now as the number of players increased, "Moshou1
" Some overwhelmed, so added a SQL Server2005
server, the default instance "Moshou2
". Now you want
to
sync the player information table
in the player database in moshou1 to the
Player information table in the player database in MOSHOU2 and to realize that the player will sync to another server regardless of which server they log in to change personal information. How is it implemented?
Requirements Analysis: Use SQL Server 2005
data replication to enable data synchronization and updates between load-balanced databases. This example uses the merge replication data to be modified by both the Publisher and the Subscriber. And the modification of the data is real-time. Merge replication automatically creates the initial snapshot. Note: In order to demonstrate the convenience, in the same server two different instances to do this experiment, and the actual situation does not have much access. Note The default instance in the simulation moshou1, named instance cool simulation MOSHOU2(a), first to
start the
SQL server
Agent Proxy service , MOSHOU1, MOSHOU2 Two instances of the service must be startedLog on to the SSMs interface of SQL Server(b), the
definition of "player information table" PRIMARY key , if not defined, then at the time of publishing Server error(iii)
new release on
moshou1 Open the New Publication WizardChoose yourself as the Distributor, that is, the publisher and the DistributorTo Configure SQL Server Agent to start automaticallyset the location of the snapshot folderSelect a database to publishSelect a publication type of merge publicationSubscriber Type Save defaultto publish a project, select the Player information tableproject issues remain defaultSnapshot Agent, tick "create snapshot Now"Click the Snapshot Agent's security Settings button to set the security of the Snapshot AgentCreate a publicationgive the publication a nameis to create a good local publication(iv),
Create a new subscription on
moshou2 Find PublishersSelect a publishing Serverset the location of the Merge AgentCreate a new subscription database at the SubscriberClick the button on the right to set the Merge Agent securitywhen Setup is complete,synchronization Schedule set to run continuouslyinitializing a subscription nowsubscription type remains the defaultTick to create a subscription after the wizard finishes(v),
verify Open the Player database under cool instance, expand the table, and you can see the Synchronized Player information table .The Player Information table is modified in the player database of the default instance and can be synced to the player information table in the cool player databasewe add a column eee to the Player information table in the default instance, and we can see that it's synced to the cool instance's table right away.
Leverage SQL Server replication to spread the load of user access servers
