1.MS SQL Server and SQL Server agent service is based on a domain user and running the (. Administrator user is possible) if the registry using a local system account, local, would not have the network function, will produce the following error: the process can not connect to Distributor '@ Server name' (if your server has used a SQL Server Full Text Search service, please do not modify MSSQLserver and Sqlserveragent services, local start. will be adhered to as full-text search service can not be used. Requests for another machine to do replication in SQL Server in the distribution server.)
Start the registry modification service users, need to restart MSSQLserver and Sqlserveragent service to take effect.
2, check a couple of machines related to whether to turn over a SQL Server server name (required srvid = 0 of the local machine as srvname and datasource)
In the implementation of the Query Analyzer:
use master select srvid, srvname, datasource from sysservers
If there is no srvid = 0 or srvid = 0 (that is, the machine) but srvname and the datasource not the same, changes required by the following methods:
USE master GO - set the two variables DECLARE @ serverproperty_servername varchar (100), @ servername varchar (100) - to obtain Windows NT Server and SQL Server instance with the specified instance of associated information SELECT @ serverproperty_servername = CONVERT (varchar (100) , SERVERPROPERTY ('ServerName')) - Back to running Microsoft SQL Server in the local server name SELECT @ servername = CONVERT (varchar (100), @ @ SERVERNAME) - Display for these two parameters select @ serverproperty_servername, @ servername - - If @ serverproperty_servername and @ servername different (because you turn over the computer name), then run the following - remove the wrong server name EXEC sp_dropserver @ server = @ servername - add the correct server name EXEC sp_addserver @ server = @ serverproperty_servername, @ local = 'local'
Modify the parameters, need to restart MSSQLserver and Sqlserveragent service to take effect.
This way will not create a copy in the process of emergence 18482,18483 error.
3, check the SQL Server Enterprise Manager several of which related to SQL Server login name is and where the second point above, as described in srvname
IP addresses can not register names. (We can delete the IP address of the registered, new to SQL Server administrator-level users to register the server name) This way will not create a copy in the process of emergence 14010,20084,18456,18482,18483 error.
4, check a couple of machines related to SQL Server server network is the normal access
If you can ping the host IP address, ping the host name of unreasonable but when the need
winntsystem32driversetchosts (WIN2000) windowssystem32driversetchosts (WIN2003)
File written to the database server IP address and host name of the correspondence.
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 in the SQL Server Client Network Utility in the establishment of an alias
5, the system requires the existence of extended stored procedure (if it does not exist, need to restore): 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 '
Then you can use SQL Server Enterprise Manager in [Copy] -> right button -> [Configure Publishing, Subscribers, and Distribution] graphical interface to configure the database copied.
The following are listed in the order configured by copying the steps:
First, the establishment of publishing and distribution server [Welcome to the Configure Publishing and Distribution Wizard] -> [select the distribution server] -> [to "@ servername" as its own distribution server, SQL Server will create the distribution database and log] -> [development snapshot folder] -> [Custom Configuration] -> [No, use the following default configuration] -> [completed]
Upon completion of the above steps, will present "@ servername" SQL Server database, set up a library and a distributor_admin distribion administrator-level users (we can arbitrarily modify the password)
Server, the additional four operations:
[Agent history clear: distribution]
[Distribution clear: distribution]
[Replication agent checks]
[Re-initialize the existence of data validation failure Subscribe]
SQL Server Enterprise Manager Replication Monitor in one more, the current of this machine can publish, distribute, subscribe.
We are once again in SQL Server Enterprise Manager [Copy] -> right choice -> [Configure Publishing, Subscribers, and Distribution]
We can [Publisher and Distributor properties] windows -> [Publisher] -> [New] -> [OK] -> [Post Database] -> [Services] / [combined] -> [OK] -> [subscriber] -> [New] -> [OK] to network with other SQL Server servers to add a server publish or subscribe.
Replication is a set of data copied from one data source to multiple data sources of technology, is a data distribution to multiple storage sites and effective way. Using replication technology, users can publish a copy of the data to multiple servers on a different server so that users can license the rights to share the data within. Replication technology to ensure that the data in different locations automatically synchronized, to ensure data consistency.
SQL replication of the basic elements include
Publishing server, subscription server, distributed servers, publications, articles
SQL replication works
SQL SERVER mainly publications, subscription way to handle replication. Source data server where the server is publishing, for publishing data. Publishing server to the data to be published a copy of all change the situation to the distribution server copy, distribute, distribution server contains a database of all the changes can receive data, and save the changes, then distributed to subscribers of these changes
Replication Types SQL SERVER
SQL SERVER provides three replication technologies are:
1, snapshot replication (stay will we use this)
2, transaction replication
3, merge replication
As long as the above to clarify these concepts so on the copy and will have a certain understanding. Then we step by step to achieve the replication step.
The first publication of the first to configure the server
(1) select the specified [server] node
(2) from the [Tools] pull-down menu in the [Copy] sub-menu, select [publish, subscribe to the server and distributed] command
(3) The system pops up a dialog box click [Next] and then look to the prompt action has been completed.
(4) Upon the completion of the publishing server settings after the system tree for the server to add a replication monitor. Also generate a distributed database (distribution)
Second create a publication
(1) select the specified server
(2) from the [Tools] menu of the [Copy] sub-menu, choose [to create and manage release] command. At this point the system will pop up a dialog box
(3) choose to create a publications database, and then click [Create Post]
(4) [the Create Publication Wizard] in the prompt dialog box, click [Next] system will pop up a dialog box. Dialog is to copy the contents of the three types. We now select the first snapshot is the default (the other two to see if we can help)
(5) Click [Next] system requirements specified can subscribe to the release of the database server type, SQLSERVER allows different databases such as ORACLE or ACCESS data replication between. But here we choose to run the "SQL SERVER 2000" database server
(6) Click [Next] system will pop up a dialog box that is the definition of the article published in the table to select
(7) and then [Next] until the operation is complete. When completed, after the creation of publications, publication of the database would be used to create a shared database.
The third design Subscriptions
(1) to select the specified subscriber
(2) from the [Tools] pull-down menu, select [Copy] sub-menu [request subscription]
(3) by clicking [Next] until you will be prompted to check the operation of SQL SERVER Agent service running state, a prerequisite for the implementation of copy operation is SQL SERVER Agent service must be started.
(4) Click [Finish]. Complete the subscription operation.
In fact, the above steps completed copy is successful. But how to replicate the success that out? Here, you can quickly see whether this method successfully. Started publishing a copy of the following server - publishing content - Right publish content - properties - hit live - state and then immediately run the agent and then point hit live agent scheduling the scheduling attribute is set to occur every day, every minute In between 0:00:00 and 23:59:59. The next step is to judge the success of the open copy C: Program FilesMicrosoft SQL ServerMSSQLREPLDATAuncXIAOWANGZI_database_database the following to see if they have some time as the file name to the folder on almost a point to generate a. If you do not believe, then open your database to look at the subscription database server under the specified subscription is not seen to see you have just published a table - a hand-
Synchronization program
- Timing synchronization data on the server
- Examples:
- Test environment, SQL Server, Remote Server Name: xz, user name: sa, no password, and test database: test
- Server list (on the Query Analyzer to connect to the server to create)
create table [user] (id int primary key, number varchar (4), name varchar (10)) go - the following in the LAN (local operation) - the machine's table, state Description: null, said new record 1 that the modified record, 0 for no change in the record if exists (select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp 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 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 - for the convenience of synchronization, create a link to the server to synchronize the server - where the remote server named: xz, user name: 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 - synchronization of the stored procedure created if exists (select backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp 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 a remote server MSDTC service - exec master .. xp_cmdshell' isql / S " xz "/ U" sa "/ P" "/ q" exec master .. xp_cmdshell''net start msdtc'', no_output "', no_output - start the machine the MSDTC service - exec master .. xp_cmdshell' net start msdtc ', no_output - for distributed transaction processing, call the shots if the table with identity column key, use the following method - BEGIN DISTRIBUTED TRANSACTION - delete data synchronization delete from srv_lnk.test.dbo. [user] where id not in (select id from [user]) - synchronize the new data insert into srv_lnk.test.dbo. [user] select id, number, name from [user] where state is null - synchronization 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 - After the update the machine synchronous symbol update [user] set state = 0 where isnull (state, 1) = 1 - COMMIT TRAN go - to create jobs, time to perform data synchronization stored procedure 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' - to create job steps to declare @ sql varchar (800), @ dbname varchar (250) select @ sql = 'exec p_synchro' - data processing, command, @ dbname = db_name () - to perform data processing of the database name exec msdb .. sp_add_jobstep @ job_name = 'data processing', @ step_name = 'data synchronization ', @ subsystem =' TSQL ', @ database_name = @ dbname, @ command = @ sql, @ retry_attempts = 5, - retries @ retry_interval = 5 - Retry interval - to create scheduling EXEC msdb .. sp_add_jobschedule @ job_name = 'data processing', @ name = 'schedule', @ freq_type = 4, - daily @ freq_interval = 1, - a daily one @ active_start_time = 00000 - 0 locations in go