Shulou(Shulou.com)06/01 Report--

Note:

1. Add a new system user to the master slave server and set the password

2. The proxy service of SQL Server is enabled for both master and slave servers, and is set to boot automatically.

3. In the database configuration management, the login information of SQL Server service and SQL Server agent service is set to the system user added above, and the password is set. (remember that both master and slave servers need to be set up like this. Don't forget it. I've forgotten it. I can't do it anyway.)

4. the file directory shared by the user. You need a password to access the share. Remember to access the share and remember the credentials first, otherwise you will be prompted to fail.

5. The backup of SQL Server is that the database server of the master library automatically backs up the database and generates backup files. During this interval, the backup log files (.trn files) are generated and restored from the database according to the log files obtained from the master database, so as to ensure the consistency of the data of the master-slave database. However, if there is no data update, log files (.trn) will still be generated and data will be synchronized, affecting network bandwidth.

6. Try not to separate from the library, because sometimes if you are detaching in the process of restore, when you attach it again after separation, there will be an error that "cannot attach the database being restored. (Microsoft SQL Server, error: 1824)".

1. Preparatory work:

Primary database server:

Operating system: Windows Server 2008 R2

Database: SQL Server 2008 R2

IP address: 192.168.1.61

From the database server:

Operating system: Windows Server 2008 R2

Database: SQL Server 2008 R2

IP address: 192.168.1.62

There is WebDB,WebDB in the master database server, which is the database we want to synchronize. There is only one test table, WebDB, as shown in the following figure:

There is no database from the server database. WebDB,WebDB database is the database we want to synchronize, as shown in the following figure:

Second, create the administrator user and shared folder permissions of the SQL Server database, and set the login user of the SQL Server service and SQL Server agent to sqladmin

1. Create a user sqladmin on the server where the main database WebDB is located, click start-run (or press the shortcut key Win+R), enter the command lurmgr.msc, hit enter, and in the pop-up local users and groups window, add users:

The interface of the new user is shown below:

The permission to set sqladmin belongs to Administrators

two。 You also need to create a user sqladmin on the slave database and join the administrators group, and delete everything else!

3. Create a shared folder SQLDataBack and share permissions and NTFS permissions on the master-slave database server to store master-slave backup log files, as shown in the following figure:

4. Open the SQLServer configuration manager on the master database server and the slave database server, respectively, and set the login identity of the SQLServer service and the SQLServer agent service as the sqladmin user and the startup mode to: automatic, as shown in the following figure:

Generally, the startup mode of the SQL Server server is self-starting, and the SQL Server proxy service is started manually, which needs to be selected as self-starting in the service.

Configure SQLServer log delivery

1. Configure on the primary database server

Connect to the local SQL Server database server using sqladmin (I have tried here, it is also possible to log in with the Administrator user)

In the database instance (in my case, the database instance is SQL2008, right-click on.\ SQL2008), right-click-Properties-Security, configure the server authentication mode and server proxy account, where the proxy account is the sqladmin account we set up earlier.

two。 Configure the property information for the WebDB database in the primary database server.

Right-click on the WebDB database-Properties, open the database properties window, select the options tab, and select "complete" (which is also complete by default), as shown in the following figure

Then select the "transaction log shipping" option in the database properties window, check "enable this database as the master database in the log shipping configuration", and then click the "backup Settings" button:

The settings in the transaction log backup settings window are as follows:

In the image above, click the Plan button and pop up as shown in the following figure:

If you need to keep synchronizing, in the duration, select no end date. Keep clicking OK until you see the following picture!

The previous arrow in the image above points to the backup plan we just set in the backup settings. Click the "add" button pointed to by the arrow to connect to the secondary database instance of the slave server.

After connecting, you can see the secondary database instance and the secondary database name. The secondary database can select an existing database, or you can enter a name to create a new database, and the default secondary database name is the same as the primary database name that needs to be synchronized.

In the initialization secondary database tab, click the "restore options" button, and the restore options window pops up, as shown below:

Note: this folder is the local folder of the slave server, that is, the folder where the data files and log files of the secondary database (synchronous library) need to be stored. If not, the default data storage path from the database instance on the server is used.

Click the "copy Files" tab and enter the destination folder where the files are copied from the server (note that it is the local folder on the server and the shared folder we set up earlier on the server). The deletion time also needs to be set with our synchronization frequency. The more frequent the synchronization frequency, the shorter the interval between deleting replicated files.

Click the plan button in the image above, pop up the "Job schedule Properties" window, set the synchronization interval from the server and whether there is an end date, and click OK.

Select the restore transaction log tab and select standby mode, as shown below:

At the same time, click the "Plan" button, pop up "Job Plan Properties", select the appropriate information as needed, and click OK.

Export our above configuration information to a file, as shown below:

After saving the configuration script, click the OK button to start saving the log and sending the configuration. If successful, you can see the following interface.

Note: if it fails, the corresponding error message will be prompted. You can check the error message. After the error message appears, click OK, the window of the database properties will close, we will open it again in the properties, in the "transaction log shipping" will not see our configuration of master-slave data configuration information, we can reconfigure. However, we can also execute our saved script in the new query window through the script information we saved. After executing the script, you can see the master-slave backup information we set in the transaction Log Shipping option in the database properties, correct the error, and reconfirm the execution.

Third, verify whether the master and slave databases of SQL Server 2008 R2 are synchronized

1. Let's go to the server to see if there is a WebDB database, as shown in the following figure, it is a success!

two。 Now let's go to the main database server to add the table shown in the following figure to the WebDB database:

Create a table named Sys_News

3. Now let's go to see if there is a table Sys_News in the WebDB database of the database server, as shown in the following figure:

Did not see, because we set the scheduled task is 15 minutes, 15 minutes did not arrive, so there is no synchronization. We'll have it in 15 minutes. So when synchronizing, be sure to plan the synchronization time.

Note: how to delete a WebDB (standby / read-only) database

Right-click on the database-- Properties-- options-- status-- the database is read-only, and change it to False!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.