- Why do you want to synchronize your SQL Server 2000 database?
- The rationale for SQL Server 2000 database synchronization configuration
- Starting from 0 Step by step Configure SQL Server 2000 database synchronization, very thin
- Already very skilled, can see the boutique version SQL Server 2000 database synchronization configuration
- Frequently asked questions when configuring SQL Server 2000 Database synchronization
Why do you want to synchronize your SQL Server 2000 database?
- Data real-time backup synchronization, database server problems when we also have their normal work when the backup
- Data real-time backup synchronization, a server can not afford to do load balancing
- Data real-time backup synchronization, database server can be uninterrupted, no loss of migration
- The primary server is attacked or when another service Synchronizer can be emergency
- 。。。。。 It can be said that the benefits are very much. Write so much for the time being
The rationale for SQL Server 2000 database synchronization configuration
The concept of replication
Microsoft? SQL Server? 2000 replication of data and database objects between databases
And a set of technologies that are distributed and synchronized to ensure their consistency.
Replication allows you to distribute data to different locations, using a LAN, dial-up connection, and Internet access
Sent to remote or mobile users. Replication also enables users to improve application performance, depending on how the data is used physically
Separating data (for example, separating online transaction processing (OLTP) and decision support systems), or spanning multiple services
The distribution database of the device is processed.
---------------------------------------------------------------------------
The basic elements of SQL replication include
Publishers, Subscribers, distributors, publications, projects
Publishing Server
A publisher is a server that provides data for replication to other servers. The publisher can have one or more
Publish, each publication represents a set of logically related data. In addition to specifying which of the data needs to be replicated, publish the clothing
The service also detects data that has changed during transactional replication and maintains information about all publications on this site.
Distribution server
A Distributor is a server that hosts and stores historical data and/or transactions and metadata as a distribution database. Distribute
The role of the server differs depending on the type of replication being performed. For more information, see Replication Types.
A remote Distributor is a server that is independent of the publisher and configured as a Distributor for replication. Local Distribution Service
is a server that is configured both as a replication publisher and as a Distributor for replication.
Subscriber server
A Subscriber is a server that receives replicated data. Subscribers subscribe to items that are separate from the publication rather than from the publication;
And the Subscriber subscribes to only the publications that it needs, not all the publications available at the publisher. According to the copied class
and selected replication options, subscribers can also propagate data changes back to the publisher or redistribute the data
to other Subscribers.
Release
A publication is a collection of one or more items in a database. This grouping of multiple items makes the specified logically related
Group data and database objects become easier to replicate together.
Project
An item is a data table, data partition, or database object that you specify to replicate. A project can be a complete table, a few columns (using the vertical
Direct filter), some rows (using horizontal filters), stored procedure or view definitions, execution of stored procedures, views, indexed views
or user-defined function.
Subscription
A subscription is a request for a copy of a data or database object. The subscription defines when and where the publication and reception will be received. Subscribed to
Synchronization or data distribution can be requested by the Publisher (push subscription) or subscriber (pull subscription). Publishing can support
A blend of push and pull subscriptions.
---------------------------------------------------------------------------
How SQL Replication works
SQL SERVER handles replication primarily with publications, subscriptions. The server where the source data resides is the publishing service
, responsible for publishing data. The publisher copies copies of all changes to the published data to the Distributor
The Distributor contains a distribution database that receives all changes to the data and saves the changes, and then
Change distribution to Subscribers
Boutique version SQL Server 2000 database synchronization configuration
SQL Server replication technology type, three replication technologies, respectively (detailed description reference SQL online Help):
1. Snapshot replication
2. Transactional replication
3. Merge replication
The steps to implement replication are described below. (Take snapshot replication for example)
Preparatory work:
1. Publisher, Subscriber creates a Windows user with the same name and sets the same password as a valid access user for the publication snapshot folder
My computer
--Control Panel
--management tools
--Computer Management
--Users and groups
--Right key user
--New user
--Establish a user who is logged on to Windows in the group of Administrators
2. At the publisher, create a new shared directory as the repository for the published snapshot files:
My Computer--d:\. Create a new directory named: PUB
--Right key to this new directory
--Properties--shared
--Select "Share this Folder"
--Set specific user rights by Newlay by "permissions" to ensure that the user created in the first step has all permissions on the folder
-Determine
3. Set up the startup user for the SQL Agent (SQLServerAgent) service (Publish/subscriber does this setting)
Start--Program--management tools--Service
--Right Key SQLServerAgent
--Attribute--Login--Select "This account"
--Enter or select the Windows logon user name created in the first step
--Enter the user's password in "password"
4. Set SQL Server Authentication mode to resolve permissions issues when connecting (publish/subscriber does this setting)
Enterprise Manager
--Right key SQL instance--Properties
--Security--authentication
--Select SQL Server and Windows
-Determine
5. Registering with each other at the Publisher and Subscriber
Enterprise Manager
--Right-click SQL Server Group
--New SQL Server registration ...
--Next--Available servers, enter the name of the remote server you want to register--add
--Next--Connect using, select Second SQL Server Authentication
--Next--Enter username and password (usually SA, password best set up very complex)
--Next--Select a SQL Server group, or you can create a new group
--The next step--complete
6. Register a server alias for a computer name that can only be used for IP
(at the end of the connection configuration, for example, at the Subscriber, the server name is entered in the IP of the publisher)
Start--Program--microsoft SQL server--Client Network Utility
--Alias--add
--Network Library Select "TCP/IP"-Server alias input SQL Server name
--Connection parameter--Enter SQL Server IP address in server name
--If you modify the SQL port, deselect "Dynamic decision port" and enter the corresponding port number
==============================================================================
Official start:
1. Configure the Publisher
A. Select the specified server node
B. Select the Publish, subscribe to and distribute commands from the Copy submenu of the Tools Drop-down menu
C. The system pops up a dialog point [next] and then looks at the prompt to operate
--until "Specify Snapshot folder"
--In the snapshot folder, enter the directory created in preparation: \\< server name >\pub
One [next] straight operation to completion.
D. The system adds a replication Monitor to the server's tree structure when the publishing server is set up
Also generate a distribution database (distribution)
---------------------------------------------------------------------------
2. Create a Publication
A. Select the specified server
B. Choose the Create and manage publications command from the Copy submenu of the Tools menu. The system will now eject
A dialog box
C. Select the database to which you want to create the publication, and then click Create Publication
D. Clicking Next in the Prompt dialog box for the Create Publication Wizard will pop up a dialog box. Dialogue
The contents of the box are three types of replication. We now choose the first one is also the default snapshot publication (Two other
Everyone can go and see Help)
E. Click Next to specify the type of database server that can subscribe to this publication, which SQL Server allows
Data replication between different databases, such as Oracle or access. But here we choose to run
database server for SQL Server 2000
F. Click Next to select the objects (such as tables, views, stored procedures, typically tables) to be published.
G. Then [next] until the operation is complete. When you finish creating your publication, the database that creates it becomes
A shared database.
---------------------------------------------------------------------------
3. Design Subscriptions
A. Select the specified subscriber
b. Select [Pull subscriptions] from the Tools drop-down menu [Copy] submenu
C. Click Next until you are prompted to check the running state of the SQL Server Agent service and perform
The prerequisite for a copy operation is that the SQL Server Agent service must already be started. (Select a Publisher, then select a publication you want, only add it at a time, and then create a new database with the same name, the next step is OK, and other databases do the same.) During the operation, it may appear that the server does not support anonymity, and you need to right-click a publication, subscribe options, and allow anonymous pull subscriptions to be selected on the publisher. ）
D. Click Finish to complete the subscription operation.
----------------------------------------------------------------------------
Complete the above steps in fact, replication is successful. But how do you know if replication has succeeded?
This is the way to quickly see if success is possible.
Expand the copy below the publisher----publish content--right key publish content--attribute-------------state and then point immediately run agent and then Point Agent property hit schedule
Set the schedule to occur every day, every minute, between 0:00:00 and 23:59:59.
The next step is to determine if replication is successful.
Open C:\Program Files\Microsoft SQL Server\mssql\repldata\unc\xiaowangzi_database_database below
See if there are some folders that take time as filenames to produce one in almost one point.
If you don't believe me, open your database. Look at the subscription database at the specified subscription to see if you saw the table you just posted.
Starting from 0 Step by step Configure SQL Server 2000 database synchronization, very thin
First, after the database is installed normally, the SQL registration that is seen in Enterprise Manager is (local) (Windows NT).
SQL Server replication does not support nicknames, such as using "." or "(local)" as the server name.
Delete the server's SQL Server registration and register it with the actual server name.
I'm going to remove this, right click on him, and choose to delete SQL Server registration. Then create a new registration, right-click the SQL Server group, and create a new SQL Server registration next--"The available servers have your computer name, the middle of the add, add to the" added server "--next--" next--"next-----"
Click the SQL Server registry that you just built with the computer name to select it--tools--copy--Configure publishing, subscribers, and distribution ... --Next--"next"--"the SQL Server Agent on" Seogirl "is currently using the system account, which causes replication between servers to fail. In the following dialog box, specify an additional account for the service startup account. --"OK, select" This Account "(then enter the system to create a new account such as SQL120), the user name entered" seogirl\sql120 ", enter the password, OK, appear: one or more changes made will not take effect until the SQL Server agent restarts. Do you want to stop and restart the SQL Server agent now? , OK, OK,--"next--" next--"The snapshot folder path" \\SEOGIRL\D$\Program Files\Microsoft SQL Server\MSSQL\REPLDATA "uses a special share name, This name is usually accessible only by a login that has administrative privileges on the computer "Seogirl". This share may not be accessible by an agent running on another computer, such as an agent that requests subscriptions.
Are you sure you want to use this snapshot folder path?
--"No, because this path is no way to request a subscription agent, we build a folder D:\ReplData, in this folder's properties set" Share this folder, click "Permissions" set everyone for complete. --"After the confirmation exit-" return to us just "snapshot folder", enter "\\SEOGIRL\ReplData" in the inside-"next--" next--"complete-" close
Open Enterprise Manager--"Select Publisher--" Tools--"copy-" Configure publish, subscriber, and distribute ... --Next step--next--next--"No, choose a snapshot folder location, \\seogirl\ReplData--" next--"--"--"--"--"--"--"
The SQL Server Agent on "Seogirl" is currently using the System account, which can cause replication between servers to fail. In the following dialog box, specify an additional account for the service startup account.
SQL Server Agent on "Seogirl" uses the system account, so SQL Server replication between servers will fail. To use replication between servers, select another server as the Distributor for "Seogirl".
"SQL120" is not a valid Windows NT name. Please give the full name: < domain \ username >.
The following error is prompted when you enter "Administrator":
Error 15407: ' Administrator ' is not a valid Windows NT name. Please give the full name: < domain \ username >.
The system appears with two choices. Configure the SQL Server Agent service to start automatically
Another, I will manually start the SQL Server Proxy Server
And pick that one?
I tried all two of them. Then the snapshot folder appears
\\XXLJD\F$\Program Files\Microsoft SQL Server\MSSQL\REPLDATA
System hint is not a valid file name or path
Then we can't get any older brother to help.
1. One is, configure the SQL Server Agent service to start automatically
Another, I will manually start the SQL Server Proxy Server
Description: This choice is or can be. Yes: The system starts the proxy server itself
No: You manually start the proxy server yourself
That's the difference.
2.
\\XXLJD\F$\Program Files\Microsoft SQL Server\MSSQL\REPLDATA
System hint is not a valid file name or path
This modification: 1. Create a folder on an NTFS partition, such as: D:\ReplData
2. Share this folder--right-click Folder-Properties-Shared
3. At the same time in the same interface-with permissions-set to everyone-Full control of the permissions
That's it.
4. Enter \ \ computer name \repldata in the path
Frequently asked questions when configuring SQL Server 2000 Database synchronization
Problem: SQL Server replication does not support nicknames, such as using "." or "(local)" as the server name.
Answer: Remove SQL Server registration for this server and register with the computer name.
Issue: The SQL Server Agent on "Seogirl" is currently using the System account, which can cause replication between servers to fail. In the following dialog box, specify an additional account for the service startup account.
Answer: You should first build an account with the Admin group permissions, and then use it here.
Problem: The snapshot folder path "\\SEOGIRL\D$\Program Files\Microsoft SQL Server\MSSQL\REPLDATA" uses a special share name that can only be used by the computer "Seogirl" Has administrative permissions on the login access. This share may not be accessible by an agent running on another computer, such as an agent that requests subscriptions. Are you sure you want to use this snapshot folder path?
Answer: Build a folder in D or your other disk such as: D:\pub, set to share, and add the new user above to the full control, the snapshot path write \ \ computer name \pub
Issue: "SQL120" is not a valid Windows NT name. Please give the full name: < domain \ username >.
Resolve: Use: Computer name \ username.
Another configuration method
******************************************************************************
How I set it up:
1. Preparatory work
Set up a common directory and ensure that 2 servers have permissions.
2. Right-click Publisher-Properties-Replication--configuration--Create a distributor and set up a publisher and publishing database.
Then give the subscriber permissions.
3. Expand: Publisher--The database that will be published--the right key to publish content--new publication.
4. Right-click the release that you just established--properties--status--run the agent now--the agent properties set the schedule and start.
4. Right-click the publication that you just created--properties--subscriptions--to force the new.
Sometimes you can't see the new project, and you can go back in.
--------------------------------------------------------------------------------
After testing, the database structure can be successfully initialized and the data of one-way synchronization (that is, subscriber data can be updated in a timely manner to the Publisher data).
Preparatory work:
>> data synchronization in the three main considerations, can be understood as a mainframe. Publishers, distributors, and subscribers; it is literally understood that the functionality to be implemented is to allow subscribers to maintain data in a timely manner consistent with data at the publisher through the Distributor. The entire implementation process is thus:
A. Subscribers should have an initialization, generally when we start to do this synchronization task, the Publisher already has a considerable amount of data, and our subscriber should be a new service. Even if there is no data in the publication, the structure of his database needs to be initialized to the subscriber.
B. I am using things to replicate, the publisher's data changes, the equivalent of triggering an update operation, and things replication can at a set time to update the operation of the data to the Subscriber. Of course, this operation needs to go through the distributor. In my test, I set the Publisher and Distributor to be the same host, so that the data could be synchronized in minutes.
> > At the beginning of the operation, you need to consider whether the Subscriber has a database of the same structure as the publisher, and if so, what you need to do is initialize the publisher's existing data using the proxy; if there is no corresponding database of the same structure, You need to initialize the publisher's current database structure and current data to the Subscriber. Of course, the actions described here are implemented in the following implementation steps and do not need to be handled separately.
>> If you have a requirement for Windows systems, you should make sure that these three servers (publish, distribute, subscribe) are in the same domain and run as the same domain administrator account, as follows:
A. Enter the corresponding server, Control Panel-> service->sqlserveragent, set login, the three servers are also set up.
B. Before the operation, also do not allow the three servers corresponding aliases used to say, such as "local", such aliases to remove the reconstruction, you can use the host name, such as: Server02.
The following steps begin to perform database synchronization:
>> Configuring Publishers and Distributors:
A. Select the registration name of the corresponding server-> tool-> replication-> Configure publish, subscribe, and distribute, follow the default settings directly until completed. Shut down.
B. Once again select the registration name of this server server-> tool-> replication-> configuration Publish, subscribe and distribute, you can see this interface and step a interface is different, we only need to configure the publisher, publishing database, Subscriber. (My publishing database is in the nature of things). Click OK to complete the operation. < Mark P>
> > Create a release, you can select the appropriate registration, with tools-> replication-> Create and manage the publication or after the registration of the replication-> published content selected, in the right margin, right-click, new publication, to create your publication. I chose things to publish, follow the default settings, select the tables or other objects you want to publish, and other places do not need to be modified until the operation is complete.
>> Modify Publication properties: Select Status-> immediately run the agent; Agent Properties-> Set your schedule, such as once a minute. (Dispatch-> edit-> Change; notify-> to write to the Windows Application event log), "OK" to complete the operation.
>> Create subscriptions: Select Publisher corresponding registration, copy-> publish content-> on this content, force new subscription,-> "Next", select Subscriber (Subscriber selected in tag p)-> "Next"-> Select the database you have, or create a new database
-> "Next"-> Modify the schedule you need-> follow the default settings until completed.
Here you can do it, modify the contents of the Publisher database, and wait two minutes for the data to be synchronized to the Subscriber. However, because the first execution requires a snapshot, if there is something in the publishing library, the first execution may take several minutes. If the data can not be synchronized in the past, everyone is scheduled to set the time is too short (such as 1 minutes) so that the snapshot can not be completed, so the following steps can not be executed, encountered in such cases,
Select the content you publish, set the properties, and "Run the agent Now" in "status" so that the data will be synchronized immediately.
--------------------------------------------------------------------------------
http://www.itpub.net/showthread.php?threadid=558706
--------------------------------------------------------------------------------
Http://blog.csdn.net/renzhe2008/relatedarticles/1717005.aspx
