SQL Server Database Synchronization Technology

Sqlserver2000 synchronous Replication
I. 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.
-- Management Tools
-- Computer Management
-- Users and groups
-- Right-click the user
-- Create a user
-- Create a Windows login user (synuser) 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 (synuser) 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 Tools -- services
-- Right-click SQLServerAgent
-- Property -- login -- select "this account"
-- Enter or select the Windows logon User Name (synuser) 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 (synuser)
-- Next -- select an SQL Server group or create a new group.
-- Next -- complete
6. Register a server alias for an IP address only and a computer name not available (this step is not used in implementation)
(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.
Ii. Formal Configuration
1. Configure the Publishing Server
Open the Enterprise Manager and perform the following steps on the Publishing Server (B, c, d:
(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 distribution server as the distribution server or another SQL Server (select your own)
(3) [next] set the snapshot folder
Use the Default \ servername \ pub
(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 (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
2. Create a publication
Release server B, C, and D
(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. Now we select the first one, that is, the default snapshot release (the other two can be checked for help)
(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] And a definition will pop up. Article Dialog box is used to select the table to be published.
NOTE: If transaction publishing is selected, only tables with primary keys can be selected in this step.
(6) Select the release Name and description.
(7) options provided by the custom publishing properties Wizard:
Yes. I will filter custom data, enable Anonymous subscription, and other custom attributes.
No. Create a release according to the specified method (custom method is recommended)
(8) [next] Select a method for filtering and Publishing
(9) [next] You can select whether anonymous subscription is allowed
1) if you select "signed Subscription", you need to add a subscription server to the Publishing Server.
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
2) if you select Anonymous subscription, the above prompt will not appear When configuring the subscription server.
(10) [next] configuring snapshot Agent Scheduling
(11) [next] complete configuration
After the publication is created, the database for creating the publication becomes a shared database.
/* -- Example of synchronizing two databases

Data available
Srv1. the database name. Author has the following fields: ID, name, phone,
Srv2. database name... author has fields: ID, name, telphone, adress

Requirements:
Srv1. database name... author add record, then srv1. database name... author record is added
If the phone field of srv1. library name... author is updated, the telphone field of srv1. library name... author is updated.
--*/

-- General handling steps
-- 1. Create a connection server on srv1 to operate srv2 in srv1 for synchronization
Exec sp_addrole server 'srv2', '', 'sqloledb', 'SQL Instance name or IP address of srv2'
Exec sp_add1_srvlogin 'srv2', 'false', null, 'username', 'Password'
Go

-- 2. Start MSDTC (Distributed Transaction Processing Service) in the srv1 and srv2 brains and set it to Automatic startup.
My computer -- control panel -- Administrative Tools -- service -- Right-click Distributed Transaction Coordinator -- Property -- start -- and set the start type to automatic start
Go

-- Create a job and call the above synchronization processing stored procedure on a regular basis.

Enterprise Manager
-- Manage
-- SQL Server proxy
-- Right-click a job
-- Create a job
-- Enter the job name in "general"
-- "Step"
-- New
-- Enter the step name in "Step name"
-- Select "Transact-SQL script (tsql)" in "type )"
-- "Database": select the database for Command Execution
-- Enter the statement to be executed in "command": exec p_process
-- OK
-- "Scheduling" item
-- Create Scheduling
-- Enter the scheduling name in "name"
-- Select your job execution schedule in "scheduling type"
-- If "repeated appears" is selected"
-- Click "change" to set your schedule

Then start the SQL Agent service and set it to Automatic startup. Otherwise, your job will not be executed.

Setting method:
My computer -- control panel -- Administrative Tools -- service -- Right-click SQLServerAgent -- properties -- start type -- select "auto start" -- OK.

-- 3. synchronous processing method 2: timed synchronization

-- Create the following synchronization processing stored procedure in srv1
Create proc p_process
As
-- Update modified data
Update B set name = I. Name, telphone = I. telphone
From srv2. database name. DBO. Author B, author I
Where B. ID = I. ID and
(B. Name <> I. Name or B. telphone <> I. telphone)

-- Insert new data
Insert srv2. database name. DBO. Author (ID, name, telphone)
Select ID, name, telphone from author I
Where not exists (
Select * From srv2. database name. DBO. Author where id = I. ID)

-- Delete Deleted Data (if needed)
Delete B
From srv2. database name. DBO. Author B
Where not exists (
Select * from author where id = B. ID)
Go
SQL Server 2005 synchronous replication technology

I. preparations:

1. Create a Windows user, set it as administrator, and set the password as the valid user for publishing the snapshot file.

2. The communication between the publishing server and the subscription server is normal under SQL Server (that is, mutual access is allowed ). Open port 1433 and set a special case in the firewall

3. Create a shared directory on the Publishing Server as the storage directory of the published snapshot file. For example, create a folder named sqlcopy under the root directory of drive D.

4. Set the SQL proxy (both the publishing server and the subscription server are set) This article is published at www.xker.com)

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

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

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

--- Enter the user password in "password"

5. 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 then click OK

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

7. Create the System user login name in step 1 in SQL Server as the publishing database owner (set as dbo_owner and public ).

8. log on to SQL Server as the Super User SA to create a database and a table.

9. 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.

10. If you can only use IP addresses and cannot use computer names, register the server alias for them.

Ii. Start:

Publishing Server Configuration (configure publishing and subscription on the Publishing Server)

1. Select a replication Node

2. Right click Local release ---- next --------- system pop-up dialog box to see the prompt ---- until "specify snapshot folder"

---- Enter the directory created during preparation in the "snapshot folder" (point to the shared folder created in step 3) ------ select Release Database ------- select release type ------- select subscription server type ------- select the object to be released ------ set snapshot proxy ------- enter the release name. This article is published on www.xker.com)

3. Right-click Local subscription -------- select the Publishing Server ------- select the subscription method (if the server side subscribes, select push subscription; otherwise

Select request subscription) ------- fill in the subscription server -------- select the proxy Plan (generally choose to run continuously) --------- select the default items for the rest.

So far, SQL Server 2005 synchronization replication is complete. With the replication technology, users can publish a copy of client data to multiple servers, so that different server users can share the data within the permitted range of permissions. The replication technology ensures that the data distributed in different locations is automatically updated synchronously to ensure data consistency, so that data synchronization between the client and the server is not required! This greatly improves work efficiency!

* -- Call example
-- Backup all contents of the current database
Exec p_backupdatabase

-- Backup the specified table of the current database
Exec p_backupdatabase @ tblist = 'tb, tb1, tb2'
--*/

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_backupdatabase] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [p_backupdatabase]
Go

Create procedure p_backupdatabase
@ S_dbname sysname = '', -- Name of the database to be backed up. If this parameter is not specified, the current database is backed up.
@ D_dbname sysname = '', -- Name of the database generated by the backup. If this parameter is not specified, it is @ s_dbname + '_ Bak'
@ Tblist varchar (8000) = ''-- List of table names to be backed up. If this parameter is not specified, it indicates all user tables.
As
Declare @ SQL varchar (8000), @ err_msg varchar (1000)

-- Parameter detection
If isnull (@ s_dbname, '') ='' set @ s_dbname = db_name ()
If isnull (@ d_dbname, '') ='' set @ d_dbname = @ s_dbname + '_ Bak'
If exists (select 1 from Master .. sysdatabases where name = @ d_dbname)
Begin
Set @ err_msg = 'backup database ['+ @ d_dbname +'] already exists! '
Goto lb_exit
End
If not exists (select 1 from Master .. sysdatabases where name = @ s_dbname)
Begin
Set @ err_msg = 'the database to be backed up [' + @ d_dbname + '] does not exist! '
Goto lb_exit
End

-- Create a backup database
Set @ SQL = 'create database ['+ @ d_dbname +']'
Exec (@ SQL)

-- Backup table
Declare @ tbname sysname
Set @ SQL = 'Clare TB cursor
Select name from ['+ @ s_dbname +']... sysobjects
Where status> 0 and xtype = ''u '''
+ Case isnull (@ tblist, '') When ''then''
Else 'and name in (''' + Replace (@ tblist,', ', ''', ''') + ''') 'end
Exec (@ SQL)
Open TB
Fetch next from TB into @ tbname
While @ fetch_status = 0
Begin
Set @ SQL = 'select * into ['+ @ d_dbname +']... ['+ @ tbname
+ '] From [' + @ s_dbname + ']... [' + @ tbname + ']'
Exec (@ SQL)
Fetch next from TB into @ tbname
End
Close TB
Deallocate TB

Lb_exit:
If @ err_msg <> ''raiserror (@ err_msg, 1, 16)
Go