SQL Server 2000 push data to SQL Server 2008 R2 Tutorial _mssql2008

A recent project to get some data that exists on other servers is implemented in a way that is pushed by other "servers" to our servers for security reasons. Our server is using SQL Server 2008 R2, other "Servers" are using SQL Server 2000, are running on Windows XP, the whole process encountered some problems, but also refer to some documents, the final cost of a lot of things to be done.

"First, configure the Distributor"

The Replication service for SQL Server 2000 includes three roles: publisher, Distributor, and Subscriber, as shown in Figure 1.

Figure 1

where the publisher (data source server) is responsible for transferring data changes, such as snapshots or transactions, to the Distributor, the Distributor distributes the changes to the Subscriber (the destination server). Where the Distributor can be configured with the Publisher on a single server.

Before you configure the Distributor, you first need to open the SQL Server Agent service and configure it to start automatically. In addition, for database replication operations, the SQL Server agent cannot log on using the system account, but must be a local account , and in the Login tab, set "Logon as" to "This account" and select any administrator account (the Administrator account must have a password).

You can then configure the Distributor. In SQL Server 2000, open Enterprise Manager, select the registered SQL Server server (here is the native server), and select the Tools menu, select Wizards, and the dialog box shown in Figure 2 appears.

Figure 2

Select the Configure Publishing and Distribution Wizard, and then click Next to appear in the dialog box shown in Figure 3.

Figure 3

If you need to create a distributor on the current database server, select the first item, or select the second item, and then click Next to appear in the dialog box shown in Figure 4.

Figure 4

Note here that the snapshot folder path must be a network path, that is, the \ computer name \ Letter default share \ Path, the default system has already completed the item, click Next directly. If there is an error, you can open cmd, enter "net share" to see if there is a default share of the letter in the current share, if not, use "net share C $" to create the default share, if you can access the reference link 2.

The Distributor can be configured the next step after success. After the Distributor is configured, the Replication Monitor item appears in the registered database server.

"Two, configure the publishing database"

For SQL Server 2000 push data to 2000, the next action can continue to be done in Enterprise Manager, and for SQL Server 2000 to push data to the high version of SQL Server, you need to install the appropriate version of SQL Server Management Studio, otherwise you will not be able to connect when you connect to the Subscriber at the last step (the [SQL-DMO] must use the SQL SERVER2005 administration tool to connect to this server "coarse none.) Our target server is installed with SQL Server 2008 R2, so we need to install SQL Server 2008 R2 Management Studio.

When the installation is complete, connect to the native server first, note that the server name cannot be used "(local)" or IP, and you must use the native computer name to log in using SQL Server Authentication.

To configure the database to be published, you first need to make sure that the recovery mode for the database is complete, right-click the database, select Properties, and then select options and set to full in the recovery mode.

You can then create a local publication, expand the native database, expand Replication, right-click Local publishing, and choose New publication, as shown in Figure 5.

Figure 5

Click Next to select the database to publish, click Next to go to the Select Publication Type dialog box, as shown in Figure 6.

Figure 6

Where the snapshot publication is appropriate for a table with no primary key, the data is replicated every time it is replicated; a transactional publication requires that a data table that is published must contain a primary key, or it cannot be replicated. Obviously when the volume of data is large, it is better to use the latter. If the database can be modified, it is recommended that a table without a primary key be added a self-added primary key. Here, because there is no primary key in the source datasheet and the datasheet structure cannot be modified, only snapshot publishing can be selected. Clicking the next step shows a dialog box that selects what you want to publish, and if a table does not meet the requirements (such as the selected transactional publication but does not have a primary key), the table name appears in front of the block symbol and cannot be selected, as shown in Figure 7.

Figure 7

Click Next again to customize the Filter Data dialog box, if you do not need to filter the data, click Next to go to set the Snapshot Agent time. The Snapshot Agent is responsible for making changes to the database, which can be set to repeat by day, week, and month. This is pushed once a day, so you only need to generate a snapshot, as shown in Figure 8. If you intend to push once after you have set up your subscription, you can choose to check the first option.

Figure 8

Then the next step is to complete the release settings.

"Three, configure subscriptions"

After you have completed the two steps, you can configure your subscription, which is divided into two types, push and pull subscriptions, which are connected to subscribers by the Distributor, push data to subscribers, and the Subscriber connects to the Distributor to pull data from the Distributor. So after completing the previous step, "a newly created publication appears in local publishing, the New Subscription Wizard dialog box appears when you right-click to select New Subscription, click Next to select the current subscription, and then click Next to receive the subscription type setting, as shown in Figure 9.

Figure 9

Select Push subscription here and click Next to select the Subscriber and Subscription Database dialog box, as shown in Figure 10.

Figure 10

First click on "Add SQL Server Subscriber" Add subscriber, appear "Connect to Server" dialog box, it should be noted that subscribers also need to log on with the server name. If the target server is in the same LAN as the local one, you can log on using the target server name, otherwise you will need to set the Hosts file and add the target server IP to the name of the corresponding relationship. The same way you log in is using SQL Server identity authentication. Note that the changes will not take effect until after the hosts reboot.

After the subscriber is added, if a database with the same name exists at the Subscriber, it is automatically displayed in the subscription database. In addition, the subscription database requires a login account with "db_owner" permissions, otherwise it will not appear in the subscription database, of course, you can create a new database. Click Next to set up the Sync Schedule dialog box, as shown in Figure 11.

Figure 11

The agent plan can be set to run continuously, run on demand only or a custom time similar to the creation of a publication where continuous running refers to checking for updates and pushing them to subscribers, "running on demand only" requires manual execution, < definition plan > is the same as creating a publication. You can set a recurring cycle. If you push only once a day, you can set an event after the Snapshot Agent's execution time in the previous step, and then you can set the initialization time after you set the synchronization schedule, as shown in Figure 12.

Figure 12

The initialization time can be set to immediate or first sync, and if you set the immediate build snapshot that you selected in the previous step, and you select "immediately" to initialize the subscription, the wizard will immediately push the data back, otherwise it will be executed on a preset schedule.

You have encountered a problem when you perform the subscription agent, and a "Logon failure" occurs when you complete this setting. The logon name is from an untrusted domain and cannot be used with Windows authentication "error. However, the view found that both the local server and the Subscriber use SQL Server identity authentication. Later, the Enterprise Manager for SQL Server 2000 right-click in replication on the local server, select Configure Publications, Subscribers, and distribution, then click the Subscriber, click "..." on the subscriber's list, and find the login mode " Windows identity authentication. The problem can be resolved by reconfiguring the subscriber's login to SQL Server authentication.

This article and the article in the code are based on "signed-non-commercial use-the same way to share 3.0", the article is welcome to reprint, but please be sure to indicate the author of the article and source links, if you have questions please DMS me contact!