Set and use database replication on SQL Server 2000

Set and use database replication on SQL Server 2000

Concept of ReplicationReplication is a technology that copies a set of data from one data source to multiple data sources. It is an effective way to publish a set of data to multiple storage sites. With the replication technology, users can publish a copy of data to multiple servers, so that different server users can share the data within the permitted range of permissions. The replication technology ensures that data distributed in different locations is automatically updated synchronously to ensure data consistency.

 

Why use replication?Replication can be used in many ways. For example, replication is the perfect solution for maintaining the Report Server. You can use replication to send necessary data objects to a report database (preferably a separate server) to meet the company's reporting needs and reduce the burden on OLTP servers. When you copy data to the Report Server, replication also allows you to transmit data, which means you can only copy the rows or columns of the required table. In addition to tables, you can also copy objects, such as views, user-defined functions, and stored procedures. In the table to be copied, you can also include restrictions, indexes, and triggers. In addition to the report function, you can also use replication to mark some jobs on the production server. In this case, data objects should be copied from the production environment to a separate server. The required job occurs on this server. Once necessary jobs are completed, the changes are transferred back to the production server, effectively alleviating the workload of the production server. Another advantage of replication is that it allows you to copy data to different geographic locations for ease of use. The branch of an organization can adopt this method. The company maintains the "master" database, and each branch has a copy of the database. The branch can view and modify the database allocated to them. Any modification can be returned to the Head Office database at the configuration time and merged with the database. The company then delivers the changes to all other subsidiaries. Each branch can have its own independent database, and the change will be returned during the rest time, which can effectively reduce the network traffic between the company and the branch. The basic elements of replication include
Publishing servers, subscription servers, distribution servers, publications, and articles.
 
How replication works
SQL Server mainly uses publications and subscriptions to process replication. The server on which the source data is located is the publishing server, which is responsible for publishing data. The Publishing Server copies all changes to the data to be published to the distribution server. The distribution server contains a distribution database that can receive all changes to the data and save the changes, then, distribute these changes to the subscription server.

　Replication typeThere are three types of replication, each applicable to a special need. The following describes the three types of replication :·Snapshot replication: This is the simplest replication type. It sends database objects to the subscription server at a specific time. This kind of replication produces very little additional overhead because the system does not always check for replication changes. However, the Copied object is only synchronized with the latest snapshot. ·Transaction Replication: This type of replication starts when the initial snapshot is applied to the target database. When the data of the target database is modified, the changes are returned to the source database for merging. Then, the source database can publish these changes to other target databases. This allows these target databases to Work offline and return to the source database for merging if necessary. ·Merge and copy: In this form of replication, any changes in the transaction log are immediately submitted to the subscription server. It allows you to keep the source database "almost" synchronized with the target database based on the lag time configured for the data. Because changes are almost synchronized in real time, such replication generates a large amount of additional costs. Transaction replication also supports bidirectional replication. Data changes in the target database can be transferred back to the source database.High-availability solution with replicationIn my opinion, replication is not a very suitable and highly practical solution. Replication tends to be faulty. These errors are usually minor and can be easily fixed, but they are still errors. In addition, as with log transmission, manual operations are required to fix the fault of the Replicated Database, which requires careful planning, which is time-consuming and may cause data loss.StepsAs long as the above concepts are clarified, we will have a certain understanding of replication. Next we will perform the copy step.

Step 1: configure the Publishing Server 

On the Publishing Server, perform the following steps:

(1) select [configuration Publishing, subscription server and Distribution] from the [copy] sub-menu in the [tools] drop-down menu to see the configuration publishing and distribution wizard

(2) [next] You can select the distributor as the distributor or another sqlserver server.

(3) [next] set the snapshot folder

The default value is/servername/d $/program files/Microsoft SQL Server/MSSQL/repldata.

Create a user in the Administrator group to access this path.

(4) ** [next] custom configuration

You can select:

Yes. Let me set the attributes of the distribution database to enable the Publishing Server or set the publishing settings.

No, use the following default settings

Custom settings are recommended.

(5) [next] use the default value to set the name and location of the Distribution Database

(6) [next] enable the Publishing Server and select as the Publishing Server

(7) [next] select the database and release type to be released

(8) [next] Select register subscription Server

(9) [next] complete configuration

Step 2: Create a publication 

On the Publishing Server

(1) Select the [create and manage release] command from the [copy] sub-menu in the [tools] menu.

(2) Select the database for which you want to create the publication, and click [Create release]

(3) In the prompt dialog box of the [Create release wizard], click [next]. A dialog box is displayed.

The dialog box contains three types of copies. Select the second one.Transaction Publishing 

(4) Click [next] system requirements to specify the database server type that can subscribe to the release,

Sqlserver allows data replication between different databases, such as oracle or access.

But here we choose to run the database server "SQL Server 2000"

(5) Click [next]. A dialog box for defining the document is displayed, that is, the table to be published.

NOTE: If transaction publishing is selected, only tables with primary keys can be selected in this step.

In "project default value" "Table Project", set the login user to sfcuser to update all projects. "Stored Procedure project" and "View Project" are similar settings.

(6) Select the release Name and description.

(7) ** custom publishing attributes

Selection provided by the Wizard:

Yes. I will filter custom data, enable Anonymous subscription, and other custom attributes.

No create a release according to the specified Method

We recommend that you use a custom method.

(8) [next] Select a method for filtering and Publishing

(9) [next] You can select whether anonymous subscription is allowed

A. if you select "signed Subscription", you need to add a subscription server to the publisher.

Method: [tools]-> [copy]-> [configure attributes of publishing, subscription server, and Distribution]-> Add in [subscription server]

Otherwise, a prompt will be displayed when you request subscription on the subscription server: you cannot subscribe anonymously if you change the subscription to publish.

If anonymous subscription is still required, use the following solution:

[Enterprise Manager]-> [copy]-> [publish content]-> [properties]-> [subscription options] Select allow anonymous request subscription

B. If anonymous subscription is selected, the above prompt will not appear When configuring the subscription Server

Select Anonymous subscription

(10) [next] configuring snapshot Agent Scheduling

This depends on the specific needs.

(11) [next] complete configuration

After the publication is created, the database for creating the publication becomes a shared database.

The ID of the shared database has one more hand, for example:

Step 3: configure the subscription Server 

A mandatory subscription configuration (this is selected by default)

On the Publishing Server

(1) [Enterprise Manager]-> [copy]-> [publish content]-> [properties]-> [subscription]-> [Force New]

Then the force subscription wizard appears.

(2) Select subscription Server> subscription database name> set distribution Agent Scheduling> initialize subscription> Start required services

(The agent of the Publishing Server must be started.)-> complete. 
  

B. Subscription Configuration

On the subscription server, perform the following operations:

(1) select [request subscription] from the [tools] drop-down menu in the [copy] submenu

(2) select [request a new subscription] to open the request subscription wizard.

(3) [next] Select a registered release

(4) [next] select the database to create the subscription

(5) [next] select Anonymous subscription or signed Subscription

(6) [next] Select initialization Architecture and Data for initialization subscription

(7) [next] select the storage location of the snapshot file. Generally, the default snapshot folder of the release server is used.

(8) ** [next] You can set the interval between subscription and update content for scheduling the distribution agent.

(9) [next] The system will prompt you to check the running status of the proxy service of the Publishing Server

(10) [next] complete configuration

Conclusion:

The usage of force subscription (push mode) or request subscription (PULL mode) depends on the workload of the distribution server and the subscription server, but the difference between the two is not tested in detail. We recommend that you try to subscribe again after force subscription to obtain the best performance of replication. The above steps are actually copied successfully. But how can we know whether the replication is successful? This method can be used to quickly check whether the operation is successful. Expand copy under the Publishing Server -- publish content -- Right-click Publish content -- properties -- click live -- status, and then click Run Agent immediately. Then click agent properties, click live scheduling, and set Scheduling to every day. occurred, every minute, between 0:00:00 and 23:59:59. The next step is to determine whether the replication succeeds and enable C: under/program files/Microsoft SQL Server/MSSQL/repldata/UNC/xiaowangzi_database_database, check whether there are some folders that use time as the file name. If you do not believe it, open your database and check whether the table you just published is displayed in the specified subscription database of the subscribed server -. This is the daily management and monitoring interface: