SQL Server 2000 subscription and release operations

Synchronization Process

1. preparation. skip this step if it is complete.

1. The intranet DB server acts as the publishing server and the Internet DB server acts as the subscription server.

Create a Windows user JL and a password JL on the publisher and the subscription server respectively. The password belongs to the administrators.

2. Create a shared directory on the Publishing Server as the storage directory of the published snapshot file. For example, create a folder named sqlcopy in the root directory of drive D, Set User JL, and set the permission to full control.

3. Make sure that the database autoweb of the Publishing Server and subscription server is consistent.

4. Create the user login name JL in the SQL server of the Publishing Server and subscription server as the owner of the Publishing Database autoweb (set as dbo_owner and public ). The username and password are consistent.

5. Open the Service (Control Panel --- management tools --- Services)

--- Right-click sqlserver agent --- properties --- login --- Select "this account"

--- Enter or select the Windows user JL created in step 1,

--- Enter the User Password JL in "password"

6. enable the network protocol TCP/IP and pipe naming protocol of SQL Server 2005 and restart the network service.

7. Set SQL Server Authentication To solve the permission issues during connection (publishing and subscription server settings)

Step: Object explorer ---- right-click SQL instance ----- properties ---- Security ---- Server Authentication ------ select "SQL Server and Windows" and click OK.

8. The Publishing Server and the subscription server register with each other.

The steps are as follows: View ---- click Register server ---- right-click Database Engine ---- create Server Registration ----- enter the name of the remote server to be registered ------ select "SQL Server Authentication" ----- User Name (SA) for Identity Authentication) password ------ create a group (or not) ----- complete. If you can only use IP addresses and cannot use computer names, register the server alias for them.

Ii. Publishing and subscription

The following work is configured on the publishing server, including publishing and subscription.

Snapshot publishing and subscription

1. Select a replication node, right-click Local release ---- next --------- system pop-up dialog box to see the prompt ---- until "specify snapshot folder" ---- enter the directory created in preparation in "snapshot folder" (pointing to the shared folder created in step 3)) ------ select Release Database ------- select release type

Next, select the table in the autoweb database to be released, remove the table starting with B (B), remove the table starting with V, and remove the table starting with C, remove the table starting with T _ and publish the remaining table as a snapshot to the subscription server (one-way transmission)

The release interval is determined based on the situation, and is executed every 20 minutes every day.

Next, set the snapshot Proxy Security, connect to the publisher user JL, and password Jl.

------- Enter the release name.

2. Select the replication node, right-click the local subscription, and select the Publishing Server ------- select the subscription method (select push subscription) ------- enter the subscription server -------- select the proxy Plan (generally choose continuous operation) --------- Other Default options are selected.

So far, snapshot publishing and subscription have been completed.

Merge publishing and subscription

1. Select the following three tables as the objects for merged publishing for bidirectional communication.

The release interval is determined based on the situation, and is executed every 20 minutes every day.

2. Select the replication node, right-click the local subscription, and select the Publishing Server ------- select the subscription method (select push subscription) ------- enter the subscription server -------- select the proxy Plan (generally choose continuous operation) --------- Other Default options are selected.

Now the combined publishing and subscription are complete.

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

Pay attention to permission issues. Generally, we recommend that you make the following preparations:

1. On the Publishing Server, the subscription server creates a Windows user with the same name, and sets the same password as the valid user who accesses the snapshot folder.

My computer

-- Control Panel

-- Management Tools

-- Computer Management

-- Users and groups

-- Right-click the user

-- Create a user

-- Create a Windows login user affiliated to the Administrator Group

2. Create a new shared directory on the Publishing Server as the directory for storing the published snapshot files. perform the following operations:

My computer -- D: \ creates a directory named pub

-- Right-click the newly created directory

-- Property -- share

-- Select "share this folder"

-- Use the "permission" button to set specific user permissions to ensure that the user created in step 1 has all permissions on the folder

-- OK

3. Set the startup user of the SQL proxy (SQLServerAgent) Service (this setting is done by the publishing/subscription server)

Start -- program -- management tool -- service

-- Right-click SQLServerAgent

-- Property -- login -- select "this account"

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

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

4. Set the SQL Server Authentication Mode to solve the permission issue during connection (this setting is done by the publishing/subscription servers)

Enterprise Manager

-- Right-click an SQL instance -- Properties

-- Security -- Authentication

-- Select "SQL Server and Windows"

-- OK

5. Register with each other on the Publishing Server and subscription Server

Enterprise Manager

-- Right-click the SQL server group

-- Create an SQL Server registration...

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

-- Next -- connect to use, select the second "SQL Server Authentication"

-- Next -- enter the user name and password

-- Next -- select an SQL Server group or create a new group.

-- Next -- complete

6. Register a server alias for an IP address-only computer name.

(Configure on the connection end. For example, if the server is configured on the subscription server, enter the IP address of the Publishing Server in the server name)

Start -- program -- Microsoft SQL Server -- client network utility

-- Alias -- add

-- Select "TCP/IP" for the network library -- enter the SQL server name as the server alias

-- Connection parameter -- enter the SQL Server IP address in the server name

-- If you have modified the SQL port, deselect "dynamically determine port" and enter the corresponding port number.