SQL Server 2000 pushes data to SQL Server R2

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

"Article index"

1. To configure the distribution server
2. Configuring the publication Database
3. Configure subscriptions

"One, configure the distribution server"

The replication services for SQL Server 2000 include three roles: publisher, Distributor, and Subscriber, as shown in relationship 1.

where the publisher (the 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 with the system account and must be a local account , on the Login tab, set the login status 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, and then select the SQL Server server that you have registered (this is a native server), and then choose the Tools menu, select Wizards, and the dialog box that appears 2 is displayed.

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

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

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

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

"Second, configure the publication database"

For SQL Server 2000 to push data to 2000, the next operation can continue in Enterprise Manager, and for SQL Server 2000 to high-version SQL Server push data, 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 in the last step (the "[SQL-DMO] must use the SQL SERVER2005 administration tool to connect to this server" is not available). Our target server is installed in SQL Server R2, so we need to install SQL Server R2 Management Studio.

When the installation is complete, connect to the local server first, note that the server name cannot use "(local)" or IP, you must use the native computer name , and the logon method uses SQL Server authentication.

To configure a database to be published, you first need to make sure that the database's recovery model is full, right-click the database, select Properties, select Options, and set to full in recovery mode.

You can then create a local publication, expand the native database, expand Replication, and in the local publishing right-click Select New Publication, 5.

Click Next to select the database you want to publish, and click Next to go to the Select a Publication Type dialog box, shown in 6.

Where snapshot publishing is appropriate for tables with no primary key, all data is copied over each copy, and transactional publications require that the published data table must contain a primary key, otherwise it cannot be copied. Obviously, using the latter is better when the amount of data is large. If the database can be modified, it is recommended that you add a self-increment primary key to the table that does not have a primary key. This is because there is no primary key in the source data table, and you cannot modify the data table structure, only select snapshot publication. When you click Next, a dialog box appears that selects what you want to publish, and if a table does not meet the requirements (such as the selected transactional publication but no primary key), the table name appears with a prohibition symbol and cannot be selected, as shown in 7.

Click Next again after the Custom Filter Data dialog box appears, if you do not need to filter the data, click Next to set the Snapshot Agent time. The Snapshot Agent is responsible for generating 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 8. If you intend to push once after you have set up your subscription, you can choose to tick the first option.

Then the next step is to complete the publishing setup.

"Three, configure subscription"

After completing these two steps, you can configure the subscription, which is divided into two types: Push and pull subscriptions, which are connected to subscribers by the Distributor, push data to subscribers, and pull data to the Distributor by the Subscriber connecting to the distribution server. So after completing the previous step, a newly created publication appears in local publishing, right-click to select New subscription to the New Subscription Wizard dialog box, click Next to select the current subscription, continue to click Next, the subscription type setting appears, 9.

Select "Push Subscriptions" here, and then click Next to the dialog box that appears in the Select subscribers and Subscriptions database, shown in 10.

First click on "Add SQL Server Subscribers" to add subscribers, the "Connect to Server" dialog box, you need to be aware that subscribers also need to log on with the server name. If the target server is on the same LAN as the local, you can log in directly using the target server name, otherwise you will need to set up the Hosts file to add the destination server IP to the name. The same logon method uses SQL Server authentication. Note that the hosts restart will not take effect until after the change is completed.

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

The agent plan can be set to run continuously, run on demand only, or a custom time that is similar to when a publication is created, where continuous running refers to checking for updates and pushing them to the subscriber, and running on demand only requires manual execution, < define plan > is the same as when you create a publication. You can set the recurrence cycle. If you only push once a day, you can set an event after the execution time of the Snapshot Agent in the previous step, and after you set up the synchronization schedule, you can set the initialization time, 12.

The initialization time can be set to "immediate" or "first time synchronization", and if you choose to generate a snapshot immediately when you set up the publication at the previous step, and you select "immediately" to initialize the subscription, the wizard will immediately push the data once it is completed, otherwise it will be executed on a scheduled schedule.

A problem has been encountered following the setup above and a "login failed" occurs when the subscription agent is executed. The login is from an untrusted domain and cannot be used with Windows authentication "error. However, view discovery uses SQL Server authentication regardless of the native server or Subscriber. Later, enter SQL Server 2000 Enterprise Manager, right-click in the copy of the native server, select Configure Publishing, Subscribers, and distribution, then click Subscribers, click on the "..." button on the right side of the subscriber in the list of subscribers, and find the login method as " Windows identity Authentication ". The issue can be resolved by re-configuring the subscriber's login as SQL Server authentication.

"Reference link"

1. Configuring Snapshot, Merge, and transactional replication:http://technet.microsoft.com/library/cc917633
2. Windows default shared open and close: http://www.cnblogs.com/Fooo/archive/2007/06/04/771021.html

SQL Server 2000 pushes data to SQL Server R2