Copy concept
The copy of the Microsoft® SQL Server® 2000 is a set of techniques for copying and distributing data and database objects between data and database objects between databases to ensure their consistency.
Using replication can be distributed to different locations through a local area network, using dial-up connection, distribute to remote or mobile users via Internet. Copying can also enable users to improve application performance, physically separated according to the use of data (for example, separate online transaction processing (OLTP) and decision support system), or spanning multiple server distribution database processing.
-------------------------------------------------- ------------------------- SQL copying basic elements include
Publishing server, subscription server, distribution server, publishing, project
• Publishing Server Publishing Server is a server that provides data to copy to other servers. The publishing server can have one or more publishing, each publishing data on behalf of a set of logic. In addition to specify which data needs to be replicated, the publishing server also detects data that changes during transaction replication and maintains all published information on this site.
• Distribution Server Distribution Server is a server as a distribution database host and store historical data and / or transaction, and metadata. The role of the distribution server is different depending on the type of replication execution. For more information, see Copy Types. The remote distribution server is a server that is independent of the distribution server and configured to replicate. Local distribution servers are servers that are all configured to replicate distribution servers.
• Subscribe Server Subscribe Server is a server that receives replication data. Subscribe Server Subscribe is an item published rather than publishing; and the subscription server only subscribes to the release it needs, not all available releases on the publisher. Depending on the type of replication and the selected copy option, the subscription server can also propagate the data change back to the publishing server or re-release the data to other subscribers.
· Publishing is a collection of one or more items in a database. The packets of such multiple items make a set of data related to the specified logic and database objects to replicate together.
· The project project is a data sheet, data partition, or database object to be copied. The project can be a complete table, a few columns (using vertical filters), a few rows (using horizontal screening), stored procedures, or view definitions, execution, view, index view, or user-defined functions.
• Subscribe subscription is a request for a copy of the data or database object. Subscription Definition The time and place of the release and location of the release. Subscribe synchronization or data distribution can be requested by a publisher (forced subscription) or a subscriber (request subscription). Publishing can support compulsory subscriptions and request subscriptions.
-------------------------------------------------- -------------------------- SQL Server mainly uses publications, subscribers to process replication. The server where the source data is located is a publishing server and is responsible for publishing the data. Publishing Server copies copies of all changes to the data to be published to the Distribution Server Distribution Server contains a distribution database, receives all changes in data, and saves these changes, then distribute these changes to subscribers
-------------------------------------------------- ------------------------- SQL Server replication technology type, three replication technology, respectively (detailed instructions for SQL online help): 1, Snapshot copy 2, transaction copy 3, merge copy
============================================================================================================================================================================================================= ========================== Introduce the step of implementing the copy. (Take a snapshot copy as an example) Preparation:
1. Publish the server, the subscriber is created a Windows user with the same name, and set the same password to make a valid access to the Snapshot folder - My Computer - Control Panel - Management Tool - Computer Management - User and Group - Right-click User - New User - Create a user who is submitted to the Administrator group.
2. On the publishing server, create a shared directory, use the storage directory of the released snapshot file:
My computer - D: / New Directory named: Pub - Right click on this new directory - Property - Share - Select "Share this Folder" - to set the specific item by "Permissions" User authority, guarantee that the user created in the first step has all permissions to the folder - determine
3. Set the boot user of the SQL agent (SQLSERVERAGENT "service (publish / subscribe server to do this)
Start - Program - Administrative Tools - Services - Right-Type SQLServert-- Properties - Login - Select "This Account" - Enter or select the Windows Login User Name created in the first step - "Password" Enter The user's password
4. Set the SQL Server authentication mode to resolve the permissions when the connection is resolved (the release / subscriber is doing this setting)
Enterprise Manager - Right-click SQL Instance - Property - Security - Authentication - Select "SQL Server and Windows" - OK
5. Register Enterprise Manager on the Publisher and Subscriber - Right-click SQL Server Group - New SQL Server Register ...-- Next - In the available server, enter the remote server name you want to register - Add - Next - Connection, select Second "SQL Server Authentication" - Next - Enter Username and Password - Next - Select SQL Server Group, you can also create a new group - Step - complete
6. For use only IP, you cannot use the computer name, the server alias (on the connection end configuration, for example, if you configure on the subscriber, the IP of the publish server is start-program-program- -Microsoft SQL Server - Client Network Utility - Alias - Add - Network Library Select "TCP / IP" - Server Alias Enter SQL Server Name - Connection Parameters - Enter SQL Server IP Address in Server Name - - If you modify the port of SQL, deselect "Dynamic Decision Port" and enter the corresponding port number
============================================================================================================================================================================================================= ============================ officially started:
1. Configure the publishing server
a. Select the specified [Server] Node B. Select [Publish, Subscriber and Distribution] command c. The system pops up a dialog box point [Next] and then look at the prompt operation - Until "Specify Snapshot Folder" - Enter the directory created in the preparation work in "Snapshot Folders": //
-------------------------------------------------- -------------------------
2. Create a release a. Select the specified server b. Select [Create and Manage Publish] commands from the [Copy] submenu of the [Tools] menu. The system will pop up a dialog box. Select to create a published database, then click [Create Publish] d. In the [Create Publish Wizard] prompt dialog box, click [Next] system will pop up a dialog box. . The content on the dialog is three types of replication. We now choose the first one is the default snapshot release (other two can go see help) E. Click [Next] System requirements Specify that you can subscribe to the published database server type, SQLServer allows you like Data replication is performed between Oracle or Access. But here we choose the database server f run "SQL Server 2000". Click [Next], select the object you want to publish (such as the table, view, stored procedure, usually table) g. Then [Next] until the operation carry out. The database created after the publication is completed, it will become a shared database.
-------------------------------------------------- -------------------------
3. Design Subscribe A. Select the specified subscription server b. Select [Request Subscription] c. The service is running, and the prerequisite for performing replication operations is that the SQL Server Agent Service must have started. d. Click [Finish] to complete the subscription operation.
-------------------------------------------------- ----------------------------
