SQL Server publishing and subscription

1. Publishing and subscription
Use forced subscription to synchronize Databases
Large amounts of data and batch data can be processed using the database synchronization mechanism:
//
Note:
Publishing Server (distribution) operation, subscription server (subscription) Operation
Use the forced subscription method on the customer's machine.
Test passed
//
-- 1: Environment
Server Environment (release ):
Machine name: devserver
Operating System: Windows 2003 Server
Database Version: SQL 2005 Server Enterprise Edition
Client (subscription ):
Machine name: pkit-bingxu
Operating System: Windows 2003 Server
Database Version: SQL 2005 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: dbrepluser
Userpwd: dbrepluser
Affiliated to: sqlserver2005mssqluser $ devserver $ MSSQLServer
Sqlserver2005mssqlagentuser $ devserver $ MSSQLServer

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

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

-- 4: Install the Publishing Server
A: configure the Publishing Server
Copy-> local publishing-> create and manage publishing-> select the database to be released (sz)-> next-> snapshot publishing-> next->
Select the content to be released-> next-> complete
B: Force configure the subscription server (the request mode is the same as the push mode)
Copy-> Local subscription-> New subscription-> subscription server-> New-> SQL Server database-> enter the client server name (zlp)-> use SQL Server Authentication (SA, empty Password)-> OK-> application-> OK
C: Initialize subscription
Copy Monitor-> Publish server (zehuadb)-> double-click subscription-> force new-> next-> select an enabled subscription server-> zlp->
Next-> next-> finish
-- 5: test whether the configuration is successful
Copy the monitor-> the Publishing Server (zehuadb)-> double-click SZ: SZ-> click status-> click Run Agent now
View:
Copy Monitor-> publisher (zehuadb)-> SZ: SZ-> select zlp: SZ (type forced)-> right-click-> Start Synchronization
If no error mark (Red Cross) exists, congratulations!
-- 6: Test Data
-- Run the following command on the server:
Select a table and execute the following SQL statement:
Insert into wq_newsgroup_s select 'test successfully', 5
Copy Monitor-> Publish server (zehuadb)-> SZ: SZ-> snapshot-> Start agent
-> Zlp: SZ (mandatory)-> Start Synchronization
Check whether a new record is inserted in the synchronized wq_newsgroup_s.
The test is completed and passed.
-- 7. Modify the synchronization time of the database. Generally, you can perform database synchronization at night.
(Specific Operation): d
/*
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.
Note: When preparing a subscription request, you must register the publishing server on the subscription server.
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
Request mode: Shared ~!
*/
A few databases can be synchronized using triggers. You can synchronize a single table:
========================================================== ======================
========================================================== ======================
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'
Note: When adding a new table to the Publishing Server, you only need to enable the replication monitor on the Publishing Server-> right-click the subscription server-> re-initialize the subscription.

Bytes ----------------------------------------------------------------------------------------------------

A manual synchronization Solution

-- Regularly synchronize data on the server
-- Example:
-- Test environment: SQL Server2000, remote server name: xz, User name: SA, no password, Test Database: Test
-- Tables on the server (query analyzer is connected to a table created on the server)
Create Table [user] (ID int primary key, number varchar (4), name varchar (10 ))
Go

-- Perform the following operations on the LAN (Local Machine)
-- Local table, State Description: NULL indicates new record, 1 indicates modified record, 0 indicates 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 connection server to the server to be synchronized
-- The remote server name is xz, the user name is SA, and no password is required.
If exists (select 1 from Master .. sysservers where srvname = 'srv _ lnk ')
Exec sp_dropserver 'srv _ lnk ', 'droplogins'
Go

Exec sp_addmediaserver 'srv _ lnk ', '', 'sqloledb', 'xz'
Exec sp_add1_srvlogin 'srv _ lnk ', 'false', null, 'sa'
Go

-- Create a stored procedure for synchronization
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_mongoshell 'isql/s "xz"/u "sa"/P ""/Q "Exec master .. xp_cmdshell ''net start MSDTC '', no_output" ', no_output

-- Start the MSDTC Service of the Local Machine
-- Exec master.. xp_cmdshell 'net start MSDTC ', no_output
-- Performs distributed transaction processing. If the table uses the ID column as the primary key, use the following method:
-- Begin Distributed Transaction

-- Synchronize Deleted Data
Delete from srv_lnk.test.dbo. [user]
Where id not in (select ID from [user])

-- Synchronize new data
Insert into srv_lnk.test.dbo. [user]
Select ID, number, name from [user] Where state is null

-- Synchronize modified data
Update srv_lnk.test.dbo. [user] Set
Number = B. number, name = B. Name
From srv_lnk.test.dbo. [user]
Join [user] B on A. ID = B. ID
Where B. State = 1

-- Update the local flag after synchronization
Update [user] Set state = 0 where isnull (State, 1) = 1
-- Commit tran
Go

-- Create a job and regularly execute the data synchronization Stored Procedure
If exists (select 1 from MSDB .. sysjobs where name = 'data ')
Execute MSDB. DBO. sp_delete_job @ job_name = 'data'
Exec MSDB .. sp_add_job @ job_name = 'data'

-- Create a job
Declare @ SQL varchar (800), @ dbname varchar (250)
Select @ SQL = 'exec p_synchro '-- Data Processing Command
, @ Dbname = db_name () -- Name of the database for processing data
Exec MSDB .. sp_add_jobstep @ job_name = 'data ',
@ Step_name = 'data synchronization ',
@ Subsystem = 'tsql ',
@ Database_name = @ dbname,
@ Command = @ SQL,
@ Retry_attempts = 5, -- number of retries
@ Retry_interval = 5 -- Retry Interval

-- Create Scheduling
Exec MSDB .. sp_add_jobschedule @ job_name = 'data ',
@ Name = 'schedule ',
@ Freq_type = 4, -- daily
@ Freq_interval = 1, -- run once a day
@ Active_start_time = 00000 -- execute at 0
Go

This article is from the csdn blog. For more information, see file: // G: /Peng huabin/learning/SQL % 20server publish and subscribe to % 20-% 20bingtingabc's column % 20-% 20csdn blog. MHT