SQL2000 database synchronization variety of methods

2010-09-01  来源:本站原创  分类:Database  人气:57 

SQLServer Replication technology steps Author: gaojier
First, the preparatory work
1 release, Subscribers have the same name to create a windows user, and set the same password as a valid release snapshot folder access to the user
- Management Tools
- Computer Management
- Users and Groups
- Right-user
- New User
- Establish a group attached to the landing windows administrator user (SynUser)
(2) the release of the server, create a shared directory, as the snapshot file storage directory, the operation:
My Computer - D: \ Create a new directory called: PUB
- Right this new directory
- Properties - Sharing
- Select "Share this folder"
- Through the "Permissions" button to set specific user permissions to ensure that the user created in step (SynUser) has all permissions to the folder
- OK
3 Set the SQL Agent (SQLSERVERAGENT) service startup user (publish / subscribe servers to do this)
Start - Programs - Administrative Tools - Services
- Properties - Login - Select "This account"
- Enter or select the first step in creating a windows login user name (SynUser)
- "Password", enter the user's password
4 Set the SQL Server Authentication mode, connecting to resolve permission issues when you (publish / subscribe servers to do this)
Enterprise Manager
- Right-SQL instance - Properties
- Security - Authentication
- Select "SQL Server and Windows"
- OK
5 at the Publisher and each Subscriber Registration Enterprise Manager
- Right SQL Server Group
- New SQL Server Registration ...
- Next - available server, enter your name to register the remote server - add
- Next - connection, select the second "SQL Server Authentication."
- The next step - enter a user name and password (SynUser)
- Next - Select the SQL Server group, you can create a new group
- Next - Finish
6 can only be used for the IP, can not use the computer name, an alias for its registration server (this step is useless to the implementation)
(End of the connection configuration, for example, configured on the subscriber, the server name is entered in the Publisher's IP)
Start - Programs - Microsoft SQL Server - Client Network Utility
- Aliases - Add
- Network library selection "tcp / ip" - enter the SQL server name server alias
- Connection Parameters - Enter the SQL server name server ip address
- If you change the SQL port, deselect the "Dynamic decision to port" and enter the corresponding port number two, the formal configuration
1, configured to open Enterprise Manager, Publisher, the Publisher (B, C, D) perform the following steps:
(1) From the [Tools] pull-down menu [copy] sub-menu, select [Configure Publishing, Subscribers, and Distribution] The Configure Publishing and Distribution Wizard
(2) [Next] to select the distribution server can choose to publish their own as a distribution server sql server or other server (choose)
(3) [Next] to set the default snapshot folder \ \ servername \ Pub
(4) [Next] Custom configuration options: Yes, let me set the distribution database property enables the publisher or publish settings set to No, use the following default settings (recommended)
(5) [Next] to set the distribution database name and location of the default values
(6) [Next] to enable the Publisher selected as the published server
(7) [Next] to select the type of release of the database and release
(8) [Next] to select the registered subscriber
(9) [Next] to complete the configuration
2, create a publication publisher B, C, D on
(1) From the [Tools] menu [copy] sub-menu, select [create and manage release] commands
(2) choose to create a publications database, then click [Create] S
(3) [Create Publication Wizard] prompt dialog box, click [Next] system will pop up a dialog box. The contents of the dialog box copy on the three types. We now select the first snapshot is the default (the other two we can go and see help)
(4) Click [Next] system requirements specified can subscribe to the publisher database server type,
SQLSERVER allows different databases such as orACLE or copy data between ACCESS.
But here we choose to run the "SQL SERVER 2000" database server
(5) Click [Next] system will pop up a dialog box that is defined in the article published in the table to select Note: If you previously selected the transaction is re-released in this step can only choose a table with primary key
(6) Select the publication name and description
(7) Custom publishing Properties Wizard provides the options:
I will custom data filters, enable anonymous subscriptions, and custom properties or any other way to create the specified release (recommended to customize the way)
(8) [Next] to select the screening method released
(9) [Next] can choose whether to allow anonymous subscriptions
1) If a signed subscription, you need to subscribe to the publisher to add a server method: [Tool] -> [Copy] -> [Configure Publishing, Subscribers, and Distribution of Properties] -> [subscriber] add
Otherwise, pull subscription at the Subscriber when the prompt will appear: If you subscribe to change the publication does not allow anonymous anonymous subscription is still needed with the following solution
[Enterprise Manager] -> [Copy] -> [publish content] -> [Properties] -> [subscription options] choose to allow anonymous pull subscription
2) If you select anonymous subscriptions, the subscription server configuration prompt does not appear above
(10) [Next] to set the Snapshot Agent Scheduling
(11) [Next] to complete the configuration when the complete publication created after publication of the database creation will become a shared database.


- Reference:
/ *
Author: Zou
* /

/ *-- Example of the two databases synchronized

There are data

srv1. The library name.. Author field :id,name,phone,
srv2. The library name.. Author field :id,name,telphone,adress 

 Requirements :
srv1. The library name.. Increase author record srv1. The library name.. Author record increase
srv1. The library name.. Author phone Field updates, srv1. The library name.. Corresponds to the author field telphone Update

-- Processing steps
--1. SRV1 connection is created on the server , For SRV1 operation   srv2, Sync
exec  sp_addlinkedserver    'srv2','','SQLOLEDB','srv2 The SQL instance name, or ip'
exec  sp_addlinkedsrvlogin  'srv2','false',null,' Username ',' password '

- 2 in srv1 and srv2 two computers, the start msdtc (Distributed Transaction Service), and set to automatically start my computer - Control Panel - Administrative Tools - Services - Right Distributed Transaction Coordinator- - properties - start - and the startup type is set to start automatically

- Then create a job to synchronize time to call the above stored procedure on the line

Enterprise Manager
- Management
- SQL Server Agent
- Right Job
- New Job
- "General" item in the input job name
- "Step" entry
- New
- "Step Name", enter the step name
- "Type" select "Transact-SQL script (TSQL)"
- "Database" select the database to execute the command
- "Command" enter the statement to be executed: exec p_process
- OK
- "Schedule" item
- New Scheduled
- "Name", enter the schedule name
- "Schedule type" select your operating executive arrangements
- If you choose "recurring"
- Point "Change" to set your schedule

Then start the SQL Agent service and set to start automatically, otherwise your work will not be executed

Setting method:
My Computer - Control Panel - Administrative Tools - Services - Right SQLSERVERAGENT - Properties - Startup Type - Select "Auto Start" - OK.

--3. Method 2 to achieve synchronization, timing synchronization  

-- SRV1 to create the following synchronization stored procedures
create  proc  p_process
-- Update the modified data
update  b  set  name=i.name,telphone=i.telphone
from  srv2. Library name .dbo.author  b,author  i
where  b.id=i.id  and
(b.name <> i.name  or  b.telphone <> i.telphone) 

-- Insert the new data
insert  srv2. Library name .dbo.author(id,name,telphone)
select  id,name,telphone  from  author  i
where  not  exists(
select  *  from  srv2. The library name. dbo.author where removing data that has been deleted ( If needed, )
delete  b
from  srv2. Library name .dbo.author  b
where  not  exists(
select  *  from  author  where 
  • SQL2000 database synchronization variety of methods 2010-09-01

    SQLServer Replication technology steps Author: gaojier First, the preparatory work 1 release, Subscribers have the same name to create a windows user, and set the same password as a valid release snapshot folder access to the user - Management Tools

  • SQL2000 Database Synchronization many ways 2010-09-01

    SQLServer replication technology to achieve the steps of: gaojier First, preparatory work 1. Publish server, subscribe to the same name server to create a windows user, and set the same password, as a snapshot release of effective access to the user

  • oracle database synchronization technology 2011-04-12

    Project, there is a demand from outside the network of another library in the data synchronization to other segments. Use the database synchronization. The following methods: Based on Oracle database technology, data synchronization can be divided in

  • sql2000 database to attach the database (that is. MDF file to import) 2010-08-14

    http://www.x3q2.com/Article/cjwt/200705/217.html sql2000 database to attach the database (that is. MDF file to import) 1) Database Installation: Open the SQL Enterprise Manager, and then click click Microsoft SQL Server, SQL Server Group, (LOCAL) (Wi

  • Oracle database synchronization, sync multiple databases, a database of how to achieve?? 2010-11-01

    Demand for such a function, you need to synchronize multiple Oracle databases, it is estimated there will be 5,000 databases, distributed nationwide. Specific requirements: 1. Implement a database to multiple databases simultaneously 2. Implement mul

  • Array provides a variety of methods to insert and remove elements 2010-11-12

    Array provides a variety of methods to insert and delete elements. Through these methods, you can press the need to manipulate the elements in Array. Array class provides methods push and pop a pair of methods, making the Array class implements a sta

  • MySQL database synchronization 2010-12-21

    Keywords: data synchronization note: 1. Now there are two computers, each time have installed mysql, a host, a slave. 2. Host ip: From the machine ip: 3. When the update database records the host machine records from the co

  • How heterogeneous database synchronization (Part I) 2011-07-25

    1, a summary of recent progress while I accomplish the same function of two simultaneous heterogeneous database systems work, there are some feelings and experiences to share to you, welcome to technical colleagues Paizhuan. The project has a number

  • Oracle database synchronization, sync multiple databases to a database how to achieve?? 2010-11-01

    There is a functional requirements, the need to synchronize multiple Oracle databases, it is estimated there will be 5000 database, the distribution of the country. Specific requirements: 1 to implement a database to multiple databases simultaneously

  • Oracle database import and export methods: 2011-09-21

    Oracle database import and export methods: 1 using the command line: Data Export: 1 TEST full export database, user name, password manager system to export to the D: \ daochu.dmp in exp system / manager @ TEST file = d: \ daochu.dmp full = y (2) the

  • Database synchronization tool for migrating small 2011-10-12

    Company projects the need for database synchronization, the user site license synchronized to the table on the company's Web site and BBS, upload an eclipse of the project, to prevent the use of backup

  • DB2 database backup and recovery methods 2010-05-07

    Transfer from http://blog.sina.com.cn/s/blog_5cecbc550100bc55.html DB2 database backup methods and classification Database backup on the database in accordance with the use of influence to divide: Online backup (also known as hot backup or online bac

  • timesten Series 5: how to define the cache, and background data in oracle database synchronization 2010-07-01

    The most typical application of TT is the TT back to connect a oracle database, TT retain only a small amount of data most needed to provide the fastest access speed, and later Taiwan, oracle database is used to keep all data in the TT, when necessar

  • Optimizing Database SQL statement optimization methods and the principles of 2011-01-06

    Optimization of database: 1, the key index segment. 2, the use of stored procedures, which allows SQL to become more flexible and efficient. 3, backup your database and remove garbage data. 4, SQL statement syntax optimization. (You can use Sybase's

  • Database synchronization 2011-07-29

    1 synchronization script #! / Bin / bash CURRENT_DIR = "` pwd `"; DB_CONFIGURE_FILE = "$ {CURRENT_DIR} / DatabaseConfigure.properties"; LOG_DIR = "$ {CURRENT_DIR} / log"; LOG_FILE = "$ {LOG_DIR} / main.log"; EXP_PAR

  • Mysql database synchronization from the master 2010-08-30

    Environment: Master server: centos 5.2 mysql 5.1.35 source IP: From the server: centos 5.2 mysql 5.1.35 source IP: Configuration: First, the primary server 1.1, create a replication user, with a replication slave privileges.

  • MYSQL exception handling log: the main database synchronization from the analysis of long delay 2011-03-10

    Problem Description: Performance of the procedure after the update operation of the main library, not from the data from the database query to change. Suspect is the main result from the library synchronization delay. View from the library on the mas

  • The synchronization of Java methods and principles 2010-07-09

    Thread Java memory model, will be accompanied by its own stack, registers, if necessary, needs and synchronization between the main memory that heap. You can use Synchornized keywords and Concurrent package Lock mutex and guarantee thread visibility.

  • SQL Server server connections across a variety of methods 2010-07-21

    1, OPENDATASOURCE The text in the SQL statement directly open the database with this example: OPENDATASOURCE ( SQLOLEDB, Data Source = TQDBSV001 ; User This method is more simple, but there are disadvantages is very slow. 2, OPENROWSET OLE DB data so

  • Visual objects AS3 copy a variety of methods 2010-10-06

    AS3 copy visual objects A copy of the editing stage Method 1 - reflection method: var ClassRef: Class = getDefinitionByName (getQualifiedClassName (t_mc)) as Class; var clone_mc: DisplayObject = new ClassRef (); addChild (clone_mc); Method 2 - constr