[Multiple] How does SQL Server 2000 push data to SQL Server 2008 R2

A recent project needs to obtain some data from other servers. To ensure security, it is implemented by pushing data from other "servers" to our servers. Our server uses SQL Server 2008 R2, and other "servers" use SQL server 2000 and run on Windows XP. Some problems have been encountered throughout the process, I also referred to some documents and finally paid a lot of work to solve the problem.

[1. Configure the distributor]

The replication service of SQLServer 2000 includes three roles: the publishing server, the distribution server, and the subscription server. The relationship is shown in Figure 1.

 

Figure 1

The publishing server (data source server) is responsible for transmitting data changes (such as snapshots or transactions) to the distribution server, the distribution server distributes the changes to the subscription server (target server. The distributor can be configured with the publisher in one server.

Before configuring the distribution Server, you must first enable the SQL Server Agent Service and configure it to start automatically. In addition, for database replication operations, the SQL Server Agent cannot use a system account to log on, but must be a local account. On the "login" tab, set "logon identity" to "this account" and select any administrator account (this administrator account must have a password ).

Then you can configure the distributor. In SQL Server 2000, open the Enterprise Manager, select the registered SQL Server (the local Server), select the "Tools" menu, and select "wizard ", the dialog box shown in Figure 2 is displayed.

 

Figure 2

Select "configure release and distribution Wizard", click "next", and the dialog box shown in Figure 3 is displayed.

 

Figure 3

If you want to create a distributor on the current database server, select the first option. Otherwise, select the second option and click Next. The dialog box shown in Figure 4 is displayed.

 

Figure 4

Note that the path of the snapshot folder must be the network path, that is, the default shared path of the computer name drive letter. The default system has filled this field and you can click Next directly. If an error occurs, open cmd and enter "net share" to check whether the drive letter is shared by default. If not, use "net share c $" to create a default share. If not, visit link 2.

After the deployment is successful, the distribution server can be configured in the next step. After the distributor is configured, "copy monitor" will appear on the registered database server ".

[2. Configure the publishing database]

For SQL Server 2000 to push data to 2000, the next operation can be continued in the enterprise manager, and for SQL Server 2000 to push data to the high version SQL Server, you must install the corresponding version of SQL Server Management Studio, otherwise, you cannot connect to the subscription server in the last step ("[SQL-dmo] must use SQL server2005 management tool to connect to this server ). The target Server is SQL Server 2008 R2, so SQL Server 2008 R2 Management Studio must be installed.

After the installation is complete, connect to the local Server first. Note that the Server name cannot use "(local)" or IP address. You must use the name of the local computer and use SQL Server identity authentication for logon.

To configure the database to be released, you must first make sure that the "recovery mode" of the database is "complete", right-click the database, select Properties, and select "option ", set "full" in "recovery mode.

Next, you can create a local release, expand the local database, expand "copy", right-click "local release" and choose "New release", as shown in figure 5.

 

Figure 5

Click next and select the database to be released. Click next to enter the select release type dialog box, as shown in figure 6.

 

Figure 6

Snapshot publishing is suitable for data tables with no primary keys in the table. All data is copied during each replication. Transaction Publishing requires that the published data tables contain primary keys. Otherwise, data cannot be copied. Obviously, when the data volume is large, it is better to use the latter. If the database can be modified, we recommend that you add an auto-incrementing primary key to a table without a primary key. Because the source data table does not have a primary key and the data table structure cannot be modified, you can only select "snapshot release ". After clicking next, the dialog box for selecting the content to be published appears. If a table does not meet the requirements (for example, the selected transaction is published but there is no primary key), the table name contains a prohibited symbol and cannot be selected, see Figure 7.

 

Figure 7

Click next again to display the custom data filtering dialog box. If you do not need to filter data, click next to set the snapshot proxy time. The snapshot agent is responsible for generating database modifications. It can be set to be repeated by day, week, or month. Here, a snapshot is generated once every day, as shown in figure 8. If you want to push the subscription once after the subscription is set, you can select the first option.

 

Figure 8

Next, you can complete the release settings.

[3. Configure subscription]

After completing the preceding two steps, you can configure subscription. There are two types of subscription: push subscription and pull subscription. The former is connected to the subscription server and pushed to the subscription server; the latter is connected to the distribution server by the subscription server to pull data from the distribution server. Therefore, after the previous step is completed, a new release is displayed in "local Publishing". Right-click and choose "New Subscription" to display the "new subscription wizard" dialog box, click next and select the current subscription. Click Next. The subscription type setting is displayed, as shown in Figure 9.

 

Figure 9

Select "push-type subscription", and then click next to display the select subscription server and subscription database dialog box, as shown in Figure 10.

 

Figure 10

First, click "add SQL Server subscription Server" to add a subscription Server. The "Connect to Server" dialog box appears. Note that the subscription Server must also be logged on using the Server name. If the target server and the local server are in the same LAN, you can log on directly using the target server name; otherwise, you need to set the hosts file and add the corresponding relationship between the target server IP address and name. You can also use SQL Server identity authentication for logon. Note that the modification takes effect only after the hosts is restarted.

After the subscription server is added, if the subscription server has a database with the same name, it will be automatically displayed in "subscription database. In addition, to subscribe to a database, you need to log on to the account with the "db_owner" permission. Otherwise, the account will not be displayed in "subscribe Database". You can also create a database. Click next and a dialog box is displayed, as shown in Figure 11.

 

Figure 11

The proxy plan can be set to "continuous run", "only run on demand", or a custom time similar to the time when the release is created, "Continuous Running" means to constantly check updates and push them to the subscription server. "only run as needed" requires manual execution, "<definition plan>" is the same as when you create a release. You can set a recurrence period. If the task is pushed only once a day, you can set an event after the execution time of the snapshot agent in the previous step. After the synchronization plan is set, you can set the initialization time in the next step, as shown in Figure 12.

 

Figure 12

The initialization time can be set to "instant" or "first synchronization time". If you select to generate a snapshot immediately at the time of the previous step and select "instant" to initialize the subscription, after the Wizard is complete, the data will be pushed once immediately; otherwise, the data will be executed according to the preset plan.

After completing the preceding settings, a problem occurs. When the subscription proxy is executed, "logon failed. The login name is from an untrusted domain and cannot be used with Windows authentication. However, you can see that both the local Server and the subscription Server use SQL Server identity authentication. Then, go to the Enterprise Manager of SQL Server 2000, right-click the local Server copy, select "configure Publishing, subscription Server and distribution", and click "Subscribe Server, in the subscriber list, click "... the logon method is "Windows identity authentication ". You can solve this problem by re-configuring logon to the subscription Server as SQL Server identity authentication.