Synchronize data between two SQL Server databases-1

After two days of searching and practice, I finally had a rough understanding of-how to synchronize the data of the two SQL servers.
Instead of writing SQL statements, you can use- Forced subscription enables Database Synchronization. Large amounts of data and batch data can be processed using the database synchronization mechanism.

Note:
For ease of operation, all operations are performed on the Publishing Server (distributor) and the push mode is used, and the forced subscription mode is used on the client machine.

Test passed:
= 1: Environment
Server environment:
Machine name: serverdb
Operating System: Windows 2000 Server
Database Version: SQL 2000 Server Enterprise Edition

Client
Machine name: joy
Operating System: Windows 2000 Server
Database Version: SQL 2000 Server Enterprise Edition

= 2: create a user account
Create a domain user account on the server
My computer management-> local users and groups-> Users-> Create
Username: joy
Userpwd: joy

= 3: restart the server MSSQLServer
My computer-> Control Panel-> management tools-> services-> MSSQLServer services
(Changed to: domain user account, we created joy user. \ joy, password: Joy)

= 4: Install the distributor
A: configure the distributor.
Tool-> copy-> Configuration Publishing, subscription server and distribution-> next (all use the default configuration)
B: configure the Publishing Server
Tools-> copy-> create and manage publishing-> select the database to be released (pubs)-> next-> snapshot publishing-> next->
Select the content to be released-> next-> complete
C: Force configure the subscription server (push mode, pull mode is the same)
Tool-> copy-> Configuration Publishing, subscription server and distribution-> subscription server-> New-> SQL Server database-> Enter client server name (Joy) -> use SQL Server Authentication (SA, SA)-> OK-> application-> OK
D: Initialize the subscription.
Copy the monitor-> the Publishing Server (serverdb)-> double-click subscription-> force new-> next-> select the enabled subscription server-> joy->
Next-> next-> finish

= 6: Test Data
-- Run the following command on the server:
Select the table stores and modify the data, insert, update, and delete manually or using SQL statements.

Copy Monitor-> Publish server (serverdb)-> snapshot-> Start proxyProgram
-> Zlp: SZ (mandatory)-> Start Synchronization

Check whether a new record is inserted in synchronized joy: stores.

The test is completed and passed.

= 7. Modify the synchronization time of the database. Generally, perform database synchronization at night.
(Procedure omitted)

[Note 〕
The server end cannot publish or distribute data in local mode. You need to delete the registration and create a new local computer name.

Uninstall method: tool-> copy-> prohibit publishing-> static release on "zehuadb", Uninstall all Database Synchronization configuration servers

Note: The SQLServerAgent service on the Publishing Server and distribution server must be started.
Use the push mode: "D: \ Microsoft SQL Server \ MSSQL \ repldata \ UNC" directory file can not be set to share
Pull Mode: Shared ~!

A small number of databases can be synchronized using triggers to synchronize a single table.

========================================================== ===
Possible problems during configuration

Before setting and using database replication in SQL Server 2000, check whether the following SQL Server servers meet the requirements:

1. Does the MSSQLServer and SQLServerAgent services start and run as domain users (. \ administrator users can also)

If you use the local account to log on, the network function is unavailable and the following error occurs:

The process failed to connect to the distributor '@ server name'

(If your server has used the full-text retrieval service of SQL Server, do not modify the local startup of MSSQLServer and SQLServerAgent services.
The full-text search service is unavailable. Use another machine as the distribution server for SQL Server 2000 replication .)

To modify the login user who starts the service, you must restart the MSSQLServer and SQLServerAgent services to take effect.

2. Check whether the names of the related SQL Server servers have been changed. (The srvname and datasource must be the same on the local machine with srvid = 0)

Run the following command in the query Analyzer:
Use master
Select srvid, srvname, datasource from sysservers

If there is no srvid = 0 or srvid = 0 (that is, the current machine) but the srvname is different from the datasource, You need to modify it as follows:

Use master
Go
-- Set two variables
Declare @ serverproperty_servername varchar (100 ),
@ Servername varchar (100)
-- Obtain information about the Windows NT server and the instance associated with the specified SQL server instance
Select @ serverproperty_servername = convert (varchar (100), serverproperty ('servername '))
-- Returns the name of the local server running Microsoft SQL Server.
Select @ servername = convert (varchar (100), @ servername)
-- Display the obtained Parameters
Select @ serverproperty_servername, @ servername
-- If @ serverproperty_servername is different from @ servername (because you changed the computer name), run the following
-- Delete the wrong server name
Exec sp_dropserver @ Server = @ servername
-- Add the correct server name
Exec sp_addserver @ Server = @ serverproperty_servername, @ local = 'local'

To modify this parameter, you must restart the MSSQLServer and SQLServerAgent services to take effect.

In this way, errors 18482 and 18483 will not occur during replication creation.

3. Check whether the registration names of the SQL Server related to the SQL Server Enterprise Manager are the same as those described in the second section above.

You cannot use the registration name of an IP address.

(We can delete the registration of IP addresses and create a new server name registered as an SQL Server administrator)

In this way, errors 14010, 20084, 18456, 18482, and 18483 will not occur during replication creation.

4. Check whether the network of Several SQL Server servers can be accessed normally.

If you can ping the Host IP address but cannot ping the host name, you must

Winnt \ system32 \ drivers \ etc \ hosts (Win2000)
Windows \ system32 \ drivers \ etc \ hosts (win2003)

The correspondence between the IP address of the database server and the host name.

For example:

127.0.0.1 localhost 192.168.0.35 oracledb 192.168.0.65 fengyu02 fengyu02 202.84.10.193 bj_db

Or create an alias in the network utility of the SQL Server Client, for example:

5. Whether the extended storage process required by the system exists (if it does not exist, it needs to be restored ):

Sp_addextendedproc 'xp _ regenumvalues ', @ dllname = 'xpstar. dll'
Go
Sp_addextendedproc 'xp _ regdeletevalue ', @ dllname = 'xpstar. dll'
Go
Sp_addextendedproc 'xp _ regdeletekey ', @ dllname = 'xpstar. dll'
Go
Sp_addextendedproc xp_cmdshell, @ dllname = 'loglog70. dll'

Next, you can use the SQL Server Enterprise Manager to [copy]-> right-click to select
-> The [configure Publishing, subscription server, and Distribution] graphic interface is used to configure database replication.