Maintain consistency of multiple server data

Server | Data Many enterprise users have multiple SQL Server 2000 servers, in order to keep the data on multiple database servers consistent, we can move a database from one database server to a different database server.

The following is an example of two SQL Server 2000 servers (represented by a and B, respectively) to describe the specific procedures.

1. Preliminary preparation

First, make sure that the two computers that have SQL Server 2000 servers installed can access each other, and that the operator has administrator privileges. Suppose the database JK exists only in a, and there is no such database in B. Then determine that two computers are in a domain, or that there is a trust relationship between domains.

Tip: If none of the above two conditions are met, you need to do the following: In Enterprise Manager, right-click the database entry, select Properties, go to the Security tab, and check the SQL Server and Windows entries in the authentication bar. Otherwise the SA account will not be available.

2. Select the data source

Right-click Database JK In a, select all tasks → export data, click Next in the wizard window that appears, and go to the Select Data Source window. Because the database is in a in this article, we keep the default settings.

3, the choice of purpose

Click "Next" to enter the "Select Destination" window. In the server column, select the server name for B, because this article is in B without the JK database operation, so we have to select "new" in the database column, in the window (Figure 1) to enter the database name JK (also free to choose). Then check the "Use SQL Server Authentication" entry and enter the administrator account and password. If you do not configure an administrator account, you can also enter a username sa with a blank password.

Figure 1

4, choose the way to copy

There are 3 options in the database replication options that appear (Figure 2), which are described separately below.

Figure 2

① "Copy tables and views from source database": Imports tables and views of a database into the B database, and stored procedures are not imported.

② "Specify the data to transfer with a query": When you click "Next", the "Type SQL statements" dialog box is displayed and the SQL statement is entered, only records that qualify can be imported into the B database.

③ copy objects and data between SQL Server databases: When you click Next, the Select Objects to Transfer dialog box is displayed, and you can specify the objects and data to be replicated in this dialog box if the source and destination of the data are Microsoft SQL server databases. The objects that can be transferred include tables, views, stored procedures, default values, rules, constraints, user-defined data types, logins, users, roles, and indexes.

Here we choose the last one, because the purpose of this article is to keep the entire database consistent.

5, Operation

Click "Next" to check all the boxes, and in the "Copy Data" column, check the "Replace existing data" item, click "Next", check "Run Now", then click "Next", and finally click "Finish" to import a JK database into B.

Advantages: We can use this method to centralize data in a database server located in different locations for centralized management and maintenance; If there is a problem with a database, we can quickly restore it to its original state. (Sichuan Zhang writes)