Data Synchronization in SQL Server 2000

1. does the ms SQL Server and SQL Server agent service start and run as domain users. \ administrator users are also allowed) If you log on to the local account, the network function is not available 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 replication in SQL Server .)

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 no srvid = 0 or srvid = 0 is the machine) but the srvname and datasource are different, 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 oracledb 192.168.0.65 fengyu02 fengyu02 202.84.10.193 bj_db bj_db

Or create an alias in the SQL Server Client Network utility.

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 ='xplog70.dll'

Next, you can use the [copy]-> right-click and choose-> [configure Publishing, subscription Server, and Distribution] GUI in the SQL Server Enterprise Manager to configure database replication.

The steps for configuring replication are as follows:

1. Create a distribution server [welcome to the configuration release and distribution wizard]-> [select a distribution server]-> [make "@ servername" its own distribution server, SQL Server will create a distribution database and log]-> [Create a snapshot folder]-> [custom configuration]-> [No, use the following default configuration]-> [complete]

After the preceding steps are completed, a distribion library and a distributor_admin administrator-level user will be created in the current "@ servername" SQL Server database (we can change the password at will)

Four new jobs are added to the server:

[Clear Agent history: distribution]

[Clear distribution: distribution]

[Copy proxy check]

[Re-initialize a subscription with failed data verification]

The SQL Server Enterprise Manager has a replication Monitor, which allows you to publish, distribute, and subscribe to the current machine.

In SQL Server Enterprise Manager, we [copy] Again-> right-click and choose-> [configure Publishing, subscription Server, and Distribution]

You can go to the [attributes of the Publishing Server and the distribution server] WINDOW> [Publishing Server]> [add]> [OK]> [Publishing Database]> [transaction] /[merge]-> [OK]-> [subscription Server]-> [add]-> [OK] add other SQL Server servers on the network as release or subscription servers.

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

The basic elements of SQL replication include

Publishing Server, subscription server, distribution server, publications, articles

How SQL 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, and then distribute the changes to the subscription server.

SQL SERVER replication technology type

SQL SERVER provides three Replication technologies:

1. We will use this option when copying snapshots)

2. Transaction Replication

3. Merge and copy

As long as the above concepts are clarified, we will have a certain understanding of replication. Next we will perform the copy step.

First, configure the Publishing Server

(1) Select the specified [server] Node

(2) Select the [Publish, subscribe server, and distribute] command from the [copy] sub-menu in the [tools] drop-down menu.

(3) In the dialog box that appears, click [next] And then follow the prompts until the operation is completed.

(4) After setting the Publishing Server, the system adds a replication monitor to the tree structure of the server. A distribution database (distribution) is also generated)

Create a publication

(1) Select the specified server

(2) Select the [create and manage release] command from the [copy] sub-menu in the [tools] menu. A dialog box is displayed.

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

(4) 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)

(5) Click [next] system requirements to specify the type of database server 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"

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

(7) then [next] until the operation is completed. After the publication is created, the database for creating the publication becomes a shared database.

Design subscription

(1) Select the specified subscription Server

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

(3) Click [next] until the system prompts you to check the running status of the SQL server proxy service. The precondition for performing the copy operation is that the SQL SERVER proxy service must be started.

(4) Click [finish]. Complete the subscription operation.

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: \ 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. Check the subscription server's specified subscription database to see if you have seen the table you just released-A Manual

Synchronization scheme

	--Synchronize the data on the server regularly


--Example:


--Test environment, SQL Server, remote server name: xz, user name: sa, no password, test database: test


-The table on the server (the query analyzer is connected to the server to create)


create table [user] (id int primary key, number varchar (4), name varchar (10))
go
--The following is on the local area network (local operation)
--Local table, state description: null means newly added record, 1 means modified record, 0 means no change record
if exists (select * from dbo.sysobjects where id = object_id (N '[user]') and OBJECTPROPERTY (id, N'IsUserTable ') = 1)
drop table [user]
GO
create table [user] (id int identity (1,1), number varchar (4), name varchar (10), state bit)
go
-Create a trigger to maintain the value of the state field
create trigger t_state on [user]
after update
as
update [user] set state = 1
from [user] a join inserted b on a.id = b.id
where a.state is not null
go
-To facilitate synchronization, create a linked server to the server to be synchronized
--The remote server name here is: xz, the user name is: sa, no password
if exists (select 1 from master..sysservers where srvname = 'srv_lnk')
exec sp_dropserver 'srv_lnk', 'droplogins'
go
exec sp_addlinkedserver 'srv_lnk', '', 'SQLOLEDB', 'xz'
exec sp_addlinkedsrvlogin 'srv_lnk', 'false', null, 'sa'
go
--Create a synchronous stored procedure
if exists (select * from dbo.sysobjects where id = object_id (N '[dbo]. [p_synchro]') and OBJECTPROPERTY (id, N'IsProcedure ') = 1)
drop procedure [dbo]. [p_synchro]
GO
create proc p_synchro
as
--set XACT_ABORT on
--Start the MSDTC service of the remote server
--exec master..xp_cmdshell 'isql / S "xz" / U "sa" / P "" / q "exec master..xp_cmdshell` `net start msdtc' ', no_output"', no_output
--Start the local MSDTC service
--exec master..xp_cmdshell 'net start msdtc', no_output
-For distributed transactions, if the table uses an identity column as the primary key, use the following method
--BEGIN DISTRIBUTED TRANSACTION
--Sync deleted data
delete from srv_lnk.test.dbo. [user]
where id not in (select id from [user])
-Sync new data
insert into srv_lnk.test.dbo. [user]
select id, number, name from [user] where state is null
--Synchronized modified data
update srv_lnk.test.dbo. [user] set
number = b.number, name = b.name
from srv_lnk.test.dbo. [user] a
join [user] b on a.id = b.id
where b.state = 1
-Update the logo of this machine after synchronization
update [user] set state = 0 where isnull (state, 1) = 1
--COMMIT TRAN
go
-Create jobs and regularly execute stored procedures for data synchronization
if exists (SELECT 1 from msdb..sysjobs where name = 'data processing')
EXECUTE msdb.dbo.sp_delete_job @ job_name = 'Data Processing'
exec msdb..sp_add_job @ job_name = 'Data Processing'
--Create job steps
declare @sql varchar (800), @ dbname varchar (250)
select @ sql = 'exec p_synchro'-command for data processing
@ dbname = db_name ()-the name of the database to perform data processing
exec msdb..sp_add_jobstep @ job_name = 'data processing',
@step_name = 'Data Sync',
@subsystem = 'TSQL',
@ database_name = @ dbname,
@command = @sql,
@retry_attempts = 5,-number of retries
@retry_interval = 5-retry interval
--Create a schedule
EXEC msdb..sp_add_jobschedule @job_name = 'Data Processing',
@name = 'Schedule',
@freq_type = 4,-every day
@freq_interval = 1,-execute once a day
@active_start_time = 00000 --0 points to execute
go



	

1. Difference between ms SQL Server and MySQL
2. Discuss the primary key and automatic number of SQL Server
3. SQL Server executes proxy tasks through code