SQL Server 2000 detailed steps for Database Synchronization [two servers]

Why should I synchronize the SQL Server 2000 database? Where should I use it? Principles of Database Synchronization configuration in SQL Server 2000 Step by step from 0 to configure SQL Server 2000 Database Synchronization, very fine Already very skilled, you can see the excellent version of SQL Server 2000 Database Synchronization Configuration FAQs about configuring SQL Server 2000 Database Synchronization Why should I synchronize the SQL Server 2000 database? Where should I use it? Real-time data backup and synchronization. When a database server encounters a problem, we also have backups during its normal operation. Real-time data backup and synchronization. When a server cannot afford the load, it can be used for load balancing. Real-time data backup and synchronization, database servers can be migrated without interruption and loss When the master server is attacked or another service synchronization server is on the server, the server can respond to emergencies. ..... It can be said that there are many benefits. Write so much for now Principles of Database Synchronization configuration in SQL Server 2000 Concept of Replication Microsoft? SQL Server? 2000 of data replication is performed between databases to replicate data and database objects. A group of technologies that are distributed and synchronized to ensure their consistency. Data can be distributed to different locations through replication, through LAN, using dial-up connections, and through the Internet Send to remote or mobile users. Replication also enables you to improve application performance. Separate data (for example, separating online transaction processing (OLTP) from decision support systems) or crossing multiple services Distributed Database processing. --------------------------------------------------------------------------- The basic elements of SQL replication include Publishing Server, subscription server, distribution server, publishing, Project Publishing Server The Publishing Server is a server that provides data to be copied to other servers. The Publishing Server can have one or more Release. Each release represents a set of Logical Data. In addition to specifying which data needs to be copied, the Publishing Server The server also detects changed data during transaction replication and maintains all published information on this site. Distributor A distributor is a server that hosts distributed databases and stores historical data and/or transactions and metadata. Distribution The server role varies depending on the replication type. For more information, see replication types. A remote distribution server is a replication-independent distribution server. Local distribution service Server is the server that is configured as both a replication publisher and a replication distributor. Subscription Server The subscription server is the server that receives the copied data. The subscription server subscribes to a project that is released rather than released separately; The subscription server only subscribes to the desired release, instead of all the available releases on the publisher. Based on the copied class Type and the selected copy option, the subscription server can also spread the data changes back to the Publishing Server or re-publish the data To other subscription servers. Release Publishing is a collection of one or more projects in a database. The grouping of multiple projects enables the specified logic-related It is easier to copy group data and database objects together. Project A project is a data table, data partition, or database object to be copied. The project can be a complete table or a few columns (use the vertical Direct filtering), several rows (Using Horizontal Filtering), stored procedure or view definition, stored procedure execution, view, and index View Or user-defined functions. Subscription Subscription is a request to copy data or database objects. Subscription defines the time and place at which the subscription will be published and received. Subscribed Synchronization or data distribution can be requested by the Publishing Server (forced subscription) or the subscription server (request subscription. Release support The combination of forced subscription and request subscription. --------------------------------------------------------------------------- How SQL replication works SQL SERVER mainly uses publications and subscriptions to process replication. The server where the source data is located is the Publishing Service. Is responsible for publishing data. The Publishing Server copies all changes to the data to be published to the distribution server. The distributor contains a distribution database that can receive all changes to the data and save the changes. Change distribution to subscription Server Advanced SQL Server 2000 Database Synchronization Configuration (For details, refer to ): 1. Copying snapshots 2. Transaction Replication 3. Merge and copy This section describes how to copy data. (Take snapshot copy as an example) Preparations: 1. On the Publishing Server, the subscription server creates a windows user with the same name, and sets the same password as the valid user who accesses the snapshot folder. My computer -- Control Panel -- Management Tools -- Computer Management -- Users and groups -- Right-click the user -- Create a user -- Create a windows login user affiliated to the administrator Group 2. Create a new shared directory on the Publishing Server as the directory for storing the published snapshot files. perform the following operations: My computer -- D: \ creates a directory named PUB -- Right-click the newly created directory -- Property -- share -- Select "share this folder" -- Use the "permission" button to set specific user permissions to ensure that the user created in step 1 has all permissions on the folder -- OK 3. Set the startup user of the SQL proxy (SQLSERVERAGENT) Service (this setting is done by the publishing/subscription server) Start -- program -- management tool -- service -- Right-click SQLSERVERAGENT -- Property -- login -- select "this account" -- Enter or select the windows logon user name created in step 1. -- Enter the user's password in "password" 4. Set the SQL Server Authentication Mode to solve the permission issue during connection (this setting is done by the publishing/subscription servers) Enterprise Manager -- Right-click an SQL instance -- Properties -- Security -- Authentication -- Select "SQL Server and Windows" -- OK 5. 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 (generally sa, it is best to set a complicated password) -- Next -- select an SQL Server group or create a new group. -- Next -- complete 6. Register a server alias for an IP address-only computer name. (Configure on the connection end. For example, if the server is configured on the subscription server, enter the IP address of the Publishing Server in the server name) Start -- program -- Microsoft SQL Server -- client network utility -- Alias -- add -- Select "TCP/IP" for the network library -- enter the SQL server name as the server alias -- Connection parameter -- enter the SQL Server IP address in the server name -- If you have modified the SQL port, deselect "dynamically determine port" and enter the corresponding port number. ========================================================== ============================================== Official start: 1. Configure the Publishing Server A. Select the specified [server] Node B. Select the [Publish, subscribe server, and distribute] command from the [copy] submenu in the [tools] drop-down menu. C. In the dialog box that appears, click [next] and follow the prompts. -- Until "specified snapshot folder" -- Enter the directory created during preparation in the "snapshot folder": \ <Server Name> \ pub 1. [next] The operation is complete. D. After setting the Publishing Server, the system will add a replication monitor to the tree structure of the server. A distribution database (distribution) is also generated) --------------------------------------------------------------------------- 2. Create a release A. Select the specified server B. Select the [create and manage release] command from the [copy] sub-menu in the [tools] menu. At this time, the system will pop up A dialog box C. Select the database for which you want to create a release, and click [Create release] D. Click [next] in the prompt dialog box of [Create release wizard]. A dialog box is displayed. Dialog The content in the dialog box is the three types of replication. Now we select the first one, that is, the default snapshot release (the other two You can check the help) E. Click [next] system requirements to specify the type of database server that can subscribe to the release. SQLSERVER allows Data is replicated between different databases, such as ORACLE or ACCESS. But here we choose to run "SQL SERVER 2000" Database SERVER F. Click [next] and select the objects to be published (such as tables, views, stored procedures, and tables) G. Then [next] until the operation is completed. After the publication is created, the database for creating the publication becomes A shared database. --------------------------------------------------------------------------- 3. Design subscription A. Select the specified subscription Server B. Select [subscribe request] from the [tools] drop-down menu in the [copy] submenu C. Follow the prompts and click [next] until the system prompts you to check the running status of the SQL SERVER proxy service. The precondition for the replication operation is that the SQL SERVER proxy service must be started. (Select a publishing server, and then select a release you want. You can only add a new database with the same name at a time. The next step is the same for other databases. During the operation, it may appear that the server does not support anonymity. You need to right-click a publication on the Publishing Server and subscribe to the option to allow anonymous requests to subscribe to the selected items .) D. Click [finish] to complete the subscription operation. ---------------------------------------------------------------------------- The above steps are actually copied successfully. But how can we know whether the replication is successful? This method can be used to quickly check whether the operation is successful. Expand copy under the Publishing Server -- publish content -- Right-click Publish content -- properties -- click live -- status, and then click Run Agent immediately, and then click agent properties to view live Scheduling Set scheduling to happen every day, every minute, between 0:00:00 and 23:59:59. The next step is to determine whether the replication is successful. Open C: \ Program Files \ Microsoft SQL Server \ MSSQL \ REPLDATA \ unc \ XIAOWANGZI_database_database Check whether there are some folders that use time as the file name. If you do not believe it, open your database. Check the specified subscription database of the subscribed server to see if you have seen the table you just released. Step by step from 0 to configure SQL Server 2000 Database Synchronization, very fine First, after the database is installed, the SQL registration shown in the Enterprise Manager is (local) (Windows NT ), SQL Server replication does not support nicknames, such as using "." or "(local)" as the Server name. Delete the SQL Server Registration of the Server and register it with the actual Server name. I will delete this first, right-click it, and choose delete SQL server registration .. Create a new registration, right-click the SQL server group, and create a new SQL server Registration next -- "the available server contains your computer name, click Add in the middle, add to "added server" -- next --> complete Click the SQL server Registration just created with the computer name to select it-> tool-> copy-> Configure Publishing, subscription server, and distribution... --> Next --> "SEOGIRL" shows that the SQL Server proxy on "SEOGIRL" is currently using a system account, which causes replication failures between servers. In the following dialog box, specify another account for the Service Startup account. -- "OK": Select "this account" (enter the system to create an account such as SQL120), enter "SEOGIRL \ SQL120" as the user name, enter the password, and click "OK". The following message is displayed: one or more changes take effect only after the SQL Server proxy restarts. Do you want to stop and restart the SQL Server proxy ?, OK, OK, --> next --> snapshot folder path \ SEOGIRL \ D $ \ Program Files \ Microsoft SQL Server \ MSSQL \ ReplData use a special share name, this name can only be accessed by logon with administrative permissions on the computer "SEOGIRL. This share may not be accessed by a proxy running on another computer, such as a proxy requesting subscription. Are you sure you want to use this snapshot folder path? -- "No, because this path cannot be used to request a subscription proxy program, let's create a folder D: \ ReplData and set" share this folder "in the properties of this folder ", click "permission" to set everyone to full. -- "Confirm and exit --" return to the previous "snapshot folder", enter "\ SEOGIRL \ ReplData" in it -- "next --" next -- "complete --" close Open Enterprise Manager> select Publishing Server> tool> copy> to configure Publishing, subscription server, and distribution... -- Next -- No, select a snapshot folder location, \ seogirl \ ReplData -- next -- The SQL Server proxy on "SEOGIRL" is currently using a system account, which causes replication between servers to fail. In the following dialog box, specify another account for the Service Startup account. The SQL Server proxy on "SEOGIRL" uses the system account, so the SQL Server replication between servers will fail. To use replication between servers, select another server as the "SEOGIRL" distributor. "SQL120" is not a valid Windows NT name. Complete name: <domain \ User Name>. The following error message is displayed when "administrator" is entered: Error 15407: 'admin' is not a valid Windows NT name. Complete name: <domain \ User Name>. The system displays two options: Yes, configure the SQL server proxy service to start automatically The other is no. I will manually start the SQL server Proxy server And then select the one? I tried both of them and the snapshot folder appeared. \ XXLJD \ F $ \ Program Files \ Microsoft SQL Server \ MSSQL \ ReplData The system prompts that the file name or path is not valid. Which of the following can help you? 1. One is to configure the SQL server proxy service to automatically start The other is no. I will manually start the SQL server Proxy server Note: This option is optional. Yes, the system starts the proxy server. No: You manually start the proxy server. This is the difference. 2. \ XXLJD \ F $ \ Program Files \ Microsoft SQL Server \ MSSQL \ ReplData The system prompts that the file name or path is not valid. Modify the value as follows: 1. Create a folder on the ntfs partition, for example, D: \ ReplData 2. Share this folder-right-click the folder-properties-share 3. At the same time, go to the same interface-have permissions-set to everyone-full control permissions In this way, you can. 4. Enter \ computer name \ ReplData in the path FAQs about configuring SQL Server 2000 Database Synchronization Problem: SQL Server replication does not support nicknames, such as using "." or "(local)" as the Server name. A: Delete the SQL Server Registration of the Server and register it with the computer name. Problem: the SQL Server proxy on "SEOGIRL" is currently using a system account, which causes replication failures between servers. In the following dialog box, specify another account for the Service Startup account. A: You should first create an account with the Administrator Group permission and use it here. Problem: the snapshot folder path \ SEOGIRL \ D $ \ Program Files \ Microsoft SQL Server \ MSSQL \ ReplData uses a special sharing name, this name can only be accessed by logon with administrative permissions on the computer "SEOGIRL. This share may not be accessed by a proxy running on another computer, such as a proxy requesting subscription. Are you sure you want to use this snapshot folder path? A: Create a folder, such as D: \ pub, on disk D or on another disk and set it to share. Then, add the user created above to the folder for full control, write \ computer name \ pub in the snapshot path Problem: "SQL120" is not a valid Windows NT name. Complete name: <domain \ User Name>. Solution: use computer name \ User Name. Another configuration method **************************************** ************************************** How do I set it: 1. Preparations Set a public directory and ensure that the two servers have permissions. 2. Right-click the Publishing Server -- properties -- copy -- configuration -- create the distribution server, and set the publishing server and the Publishing Database, Then grant the subscriber permissions. 3. Expand the Publishing Server, select the database to be released, right-click the published content, and click Create release. 4. Right-click the created release -- properties -- status -- run agent now -- set Scheduling and start in Agent properties. 4. Right-click the newly created release, properties, subscription, and force creation. Sometimes you can't see the newly created project. You can just try again. -------------------------------------------------------------------------------- After testing, the database structure initialization and data synchronization can be smoothly implemented (that is, the data of the subscription server can be updated as the data of the Publishing Server in a timely manner ). Preparations: > The three main considerations for data synchronization can be understood as three hosts. The publisher, distributor, and subscription server can be understood literally. The function to be implemented is to ensure that the data on the subscription server is consistent with that on the publisher server in a timely manner through the distribution server. The entire execution process is as follows: A. the subscription server should have an initialization. Generally, when we start this synchronization task, the Publishing Server already has a considerable amount of data, and our subscription server should be a new service. Even if there is no data in the release, you need to initialize the database structure to the subscription server. B. I use transaction replication. After the data on the Publishing Server changes, it triggers an update operation, transaction replication allows you to update data update operations to the subscription server at a specified time. Of course, this operation must go through the distributor. In my tests, I set the publishing server and the distribution server as the same host to synchronize data smoothly within several minutes. > At the beginning of the operation, you need to consider whether the subscription server has a database with the same structure as the Publishing Server. If yes, you need to use a proxy to initialize the existing data of the Publishing Server; if there is no database with the same structure, you need to initialize the current database structure and data of the Publishing Server to the subscription server. Of course, all the operations mentioned here are implemented in the following implementation steps and do not need to be processed separately. > If you have requirements for Windows, you should ensure that the three servers (publishing, distribution, and subscription) run in the same domain as the administrator account of the same domain. The operations are as follows: A. Enter the corresponding server, control panel-> service-> SQLSERVERAGENT, and set logon. The three servers are also set. B. before the operation, the aliases corresponding to the three servers are not allowed to be used. For example, "LOCAL". To delete and recreate these aliases, you can use the host name, for example, server02. Start Database Synchronization: > Configure the Publishing Server and distribution server: A. Select the Registration Name of the corresponding server-> tool-> copy-> Configure Publishing, subscription, and distribution, and execute the configuration directly following the default settings until the configuration is completed. Disable. B. once again, select the Registration Name of the server-> tool-> copy-> Configuration Publishing, subscription, and distribution. You can see that this interface is different from the interface in step, we only need to configure the Publishing Server, publishing database, and subscription server. (My Publishing Database is transactional ). Click "OK" to complete the operation. <Mark P> > Create a release. You can select the corresponding registration. Choose tools> copy> create and manage a release, or click Copy under the Registration tab. After the content is selected, click the blank area on the right, right-click to create a new release to create your release. I chose thing Publishing. According to the default settings, select the table or other objects you want to publish, and do not need to modify them elsewhere until the operation is completed. > Modify Release attributes: Select "status"> Run Agent now; Agent attributes> set your scheduling, for example, once a minute. (Scheduling-> edit-> change; notification-> write windows Application Event Log), "OK" to complete the operation. > Create subscription: Select "register" corresponding to the Publishing Server, copy ">" publish content "> right-click the content to force a new subscription.->" Next ", select the subscription server (the subscription server selected in step P)-> "Next"-> select your database or create a new database -> "Next"-> modify the scheduling you need-> follow the default settings until the scheduling is completed. Now, you can modify the content in the database of the Publishing Server. After a minute or two, the data will be synchronized to the subscription server. However, because snapshot is required for the first execution, the first execution may take several minutes if the repository contains some content. If the data cannot be synchronized, the snapshot cannot be completed because the scheduling setting time is too short (for example, 1 minute), so the subsequent steps cannot be executed. In this case, Select your published content, set properties, and "Run Agent now" in "status". In this way, the data will be synchronized immediately. -------------------------------------------------------------------------------- Http://www.itpub.net/showthread.php? Threadid = 558706 -------------------------------------------------------------------------------- Http://blog.csdn.net/renzhe2008/relatedarticles/1717005.aspx

After testing, the above article can be used normally, but the premise is that the server does not have abnormal security settings, and many details need to be addressed. For details, refer to the sqlserver article published by the. If you have any questions, leave a message.