Implementing data synchronization updates with SQL Server replication technology

The concept of Microsoft SQL Server 2000 replication: A set of techniques for replicating and distributing data and database objects between databases and synchronizing them to ensure their consistency.

With replication, you can distribute data to different locations, over a local area network, using a dial-up connection, and distributing to remote or mobile users over the Internet. Replication also enables users to improve application performance by physically separating data based on how the data is used (for example, separating online transaction processing (OLTP) and decision support systems), or processing across multiple server distribution databases.

The basic elements of SQL replication include:

Publishers, Subscribers, distributors, publications, projects.

Publishing Server

A publisher is a server that provides data for replication to other servers. The publisher can have one or more publications, each of which represents a set of logically related data. In addition to specifying which of these data needs to be replicated, the Publisher detects data that has changed during transactional replication and maintains information about all publications on this site.

Distribution server

A Distributor is a server that hosts and stores historical data and/or transactions and metadata as a distribution database. The role of the distributor differs depending on the type of replication being performed. For more information, see Replication Types.

A remote Distributor is a server that is independent of the publisher and configured as a Distributor for replication. A local Distributor is a server that is configured both as a replication publisher and as a Distributor for replication.

Subscriber server

A Subscriber is a server that receives replicated data. Subscribers subscribe to publications that are not separate from publications, and subscribers subscribe only to the publications they want, not to all available publications on the publisher. Depending on the type of replication and the replication options you choose, Subscribers can also propagate data changes back to the publisher or redistribute data to other subscribers.

Release

A publication is a collection of one or more items in a database. This grouping of multiple items makes it easier to specify a set of logically related data and database objects to replicate together.

Project

An item is a data table, data partition, or database object that you specify to replicate. A project can be a complete table, a few columns (using a vertical filter), a few rows (using horizontal filters), a stored procedure or view definition, a stored procedure's execution, a view, an indexed view, or a user-defined function.

Subscription

A subscription is a request for a copy of a data or database object. The subscription defines when and where the publication and reception will be received. A subscription's synchronization or data distribution can be requested by the Publisher (push subscription) or subscriber (pull subscription). A publication can support a mix of push and pull subscriptions.

How SQL Replication works

SQL SERVER handles replication primarily with publications, subscriptions. The server on which the source data resides is the publishing server, responsible for publishing the data. The publisher copies copies of all changes to the published data to the Distributor Distributor, which contains a distribution database that receives all changes to the data and saves the changes and distributes the changes to subscribers.

SQL Server replication technology type, three replication technologies, respectively, are

1. Snapshot replication

2. Transactional replication

3. Merge replication

The steps to implement replication are described below. (Take snapshot replication for example)

Preparatory work:

1. Publisher, the Subscriber creates a Windows user with the same name and sets the same password as a valid access user for the publication snapshot folder.

--My computer.

--Control Panel

--management tools

--Computer Management

--Users and groups

--Right key user

--New user

--Establish a user who is logged on to Windows in the group of Administrators

2. At the publisher, create a new shared directory as the repository for the published snapshot files:

My Computer--d:\ A new directory named: PUB

--Right key to this new directory

--Properties--shared

--Select "Share this Folder"

--Set specific user rights by Newlay by "permissions" to ensure that the user created in the first step has all permissions on the folder

-Determine

3. Set up the startup user for the SQL Agent (SQLServerAgent) service (Publish/subscriber does this setting)

Start--Program--management tools--Service

--Right Key SQLServerAgent

--Attribute--Login--Select "This account"

--Enter or select the Windows logon user name created in the first step

--Enter the user's password in "password"

4. Set SQL Server Authentication mode to resolve permissions issues when connecting (publish/subscriber does this setting)

Enterprise Manager

--Right key SQL instance--Properties

--Security--authentication

--Select SQL Server and Windows

-Determine

5. Registering with each other at the Publisher and Subscriber

Enterprise Manager

--Right-click SQL Server Group

--New SQL Server registration ...

--Next--Available servers, enter the name of the remote server you want to register--add

--Next--Connect using, select Second SQL Server Authentication

--Next--Enter user name and password

--Next--Select a SQL Server group, or you can create a new group

--The next step--complete

6. Register a server alias for a computer name that can only be used for IP

(at the end of the connection configuration, for example, at the Subscriber, the server name is entered in the IP of the publisher)

Start--Program--microsoft SQL server--Client Network Utility

--Alias--add

--Network Library Select "TCP/IP"-Server alias input SQL Server name

--Connection parameter--Enter SQL Server IP address in server name

--If you modify the SQL port, deselect "Dynamic decision port" and enter the corresponding port number

Official start:

1. Configure the Publisher

A. Select the specified server node

B. Select the Publish, subscribe to and distribute commands from the Copy submenu of the Tools Drop-down menu

C. The system pops up a dialog point [next] and then looks at the prompt to operate

--until "Specify Snapshot folder"

--In the snapshot folder, enter the directory created in your preparation: \ \ server name \pub

One [next] straight operation to completion.

D. When the publishing server is completed, the system adds a replication Monitor to the server's tree structure and also generates a distribution database (distribution)

2. Create a Publication

A. Select the specified server

B. Choose the Create and manage publications command from the Copy submenu of the Tools menu. A dialog box pops up on the system.

C. Select the database to which you want to create the publication, and then click Create Publication

D. Clicking Next in the Prompt dialog box for the Create Publication Wizard will pop up a dialog box. Dialogue

The contents of the box are three types of replication. We now choose the first one is also the default snapshot publication (Two other

Everyone can go and see Help)

E. Click Next to specify the type of database server that can subscribe to this publication, which SQL Server allows

Data replication between different databases, such as Oracle or access. But here we choose to run

database server for SQL Server 2000

F. Click Next to select the objects (such as tables, views, stored procedures, typically tables) to be published.

G. Then [next] until the operation is complete. When you finish creating your publication, the database that creates the publication becomes a shared database.

---------------------------------------------------------------------------

3. Design Subscriptions

A. Select the specified subscriber

b. Select [Pull subscriptions] from the Tools drop-down menu [Copy] submenu

C. Click Next until you are prompted to check the running state of the SQL Server Agent service and perform

The prerequisite for a copy operation is that the SQL Server Agent service must already be started.

D. Click Finish to complete the subscription operation.

----------------------------------------------------------------------------

Complete the above steps in fact, replication is successful. But how do you know if replication has succeeded?

This is the way to quickly see if success is possible.

Expand the copy below the publisher----publish content--right key publish content--attribute-------------state and then point immediately run agent and then Point Agent property hit schedule

Set the schedule to occur every day, every minute, between 0:00:00 and 23:59:59.

The next step is to determine if replication is successful, and open C:\Program Files\Microsoft SQL Server\mssql\repldata\unc\xiaowangzi_database_database below, See if there are any folders that have a time file name (almost one in a point).