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

Description:

As a result of temporarily receiving the master-slave environment for OA, based on Windows 200R2 SQLServer2008 R2, because the information searched is incomplete and there are many important omissions, I have done it myself several times, filled out the pit, and sorted it out in preparation for forgetting and sharing. If there are any mistakes or omissions, welcome to correct and communicate.

I. configure the environment

Main Library: 10.8.11.214 Database version: SQL Server2008 R2 Server name: WIN-D4GRPQKED93

Slave Library: 10.8.11.79 Database version: QL Server2008 R2 Server name: WIN-3ME2DJ8L9KT

Note:

The server name (hostname) is best determined before installing the SQL Server 2008R2 database to ensure that the hostname and instance name are consistent

If the hostname is modified after installing sqlserver2008, it usually leads to the inconsistency between the hostname and the instance name.

It can be detected and repaired with the following command

Check whether the service name (the instance name at the default installation is the same as the hostname) and the hostname are the same select @ @ servername select serverproperty ('servername'). If it is inconsistent, modify it: if serverproperty (' servername') @ @ servername begin declare @ server sysname set @ server = @ @ servername exec sp_dropserver @ server = @ server set @ server = cast (serverproperty ( 'servername') as sysname) exec sp_addserver @ server = @ server @ local = 'LOCAL' End

The following is the result of my run:

Second, build master-slave preparation

1. Create a sqladmin user from the master and join the administrators group respectively to delete the default users group, and set "password never expires" and "user cannot change password" to create a password for sqladmin.

2. Set up network sharing between master and slave

Open the Control Panel-- > Network and Internet-- > Network and sharing Center-- > Advanced sharing Settings as shown in the figure:

3. Create a shared folder DB_Backpup on the master-slave library to store master-slave backup log files (sqladmin users can read and write administrators owners)

The steps are as follows:

Right-click "DB_Bakcup"-- > share-- > specific user-- > add sqladmin read and write permissions

As shown in the figure:

In the Advanced sharing window, check the share this folder option, and then the stand-alone permissions button sets the permissions for the shared folder. To give the sqladmin user full control of the folder, delete the default "erverone" user, then add the sqladmin user, check "full Control", "change" and "read" in the permissions of "sqladmin", and then click the OK button twice to save the shared settings. As shown below:

In the NTFS file system, you also need to set the user's security permissions on the directory. If the security permissions are not enough, the system will report an error with no permissions when writing to the backup file.

You can on the "Security" tab, stand-alone "Edit" button, in the "DB_Backup permissions" interface, click "Button", add sqladmin users, and then select "full Control" permission in "sqladmin permissions", stand-alone "OK" button to save permission information.

As shown below:

4. Open the SQLServer configuration Manager from the master database server and the slave database server respectively, and log in as a sqladmin user for the SQLServer service and the SQLServer proxy service, and the startup mode is: automatic

As shown in the figure:

Restart the main library server at this point!

Configure SQLServer log delivery

After the main library is restarted, log in to the system as a sqladmin user and log in to the database with sqladmin

Configure on the primary database

When you open the SQL Server Management Studio management console on the primary database server and connect to the local primary database server, make sure that the user in the user name below has permissions to control the SQL Server server. Click the Connect button to connect to the local SQL Server database example.

As shown in the figure:

Right-click the database server instance, select the "properties" option, in the pop-up "server properties-WIN-D4GRPQKED93" interface, the left side of the stand-alone "security", and then in the right window "server authentication" select "SQLServer and Windows authentication mode", and check "server proxy account" in the "enable server proxy account" option. Enter the correct proxy account and password, and click OK to save the configuration information.

As shown in the figure:

Configure the database UFSystem properties to be synchronized in the main database server WIN-D4GRPQKED93

As shown in the figure:

After setting up, select transaction Log Shipping, check the option to enable this database to master database in unlogged shipping configuration, click backup Settings button in transaction Log backup, and open the transaction Log backup Settings interface.

As shown in the figure:

Pause the configuration of the master library first, and test whether the network shared folder DB_Backup folder of the master library can be accessed from the slave library.

Perform operations on the repository:

Win+R, enter the network path to see if you can access\ 10.8.11.214\ DB_Backup

As shown in the figure:

Right-click-UFSystem-- Properties-transaction Log delivery-backup Settings-in the "Job schedule Properties" interface, confirm that the "schedule type" is repeated. For obvious test results, set the job schedule to be executed once in 15 seconds. Finally, confirm the "duration" and set it according to your 4 needs. If you keep backing up, you can set it to "No end date".

As shown in the figure:

Once the settings are completed, open the transaction Log backup Settings interface again after saving, then the backup job name will be followed by the "Edit Job" button, click in, and change the "owner" to sqladmin.

As shown in the figure:

Click the add button in Secondary Database in the database properties interface to open the Secondary Database Settings window.

If you cannot browse the slave library, you need to start the SQL Server Browser service from the library

You may not be able to connect to the secondary database on the first connection, and an error will be reported:

A. User XXX login failed

Solution:

Log in using sa on the slave library, create a new query on master, enter

Create LOGIN [WIN-3ME2DJ8L9KT\ sqladmin] FROM WINDOWS

B. User XXX is not in the fixed server role of sysadmin

Execute on the slave library

EXEC sp_addsrvrolemember 'WIN-3ME2DJ8L9KT\ sqladmin','sysadmin'

Set up the secondary database after a successful connection

As shown in the figure:

View the data directory of the secondary database

As shown in the figure:

Click the OK button after setting, save the configured script locally in the database properties configuration interface, and finally click OK as shown in the figure:

When the master / slave configuration of the database is completed, you can operate in the master database to check whether the corresponding data is synchronized on the slave database. If the synchronization is not successful, you can view the synchronization task status on the slave database and check the cause of the failure.

The following is a screenshot of a successful synchronization, and the UFSystem database on the master database has been synchronized from the slave library.

As shown in the figure:

Verify master-slave synchronization

Create a table in the main library UFSystem

Wait a minute and check it on the slave library in 15 seconds.

At this point, you can find that the master-slave synchronization has been completed.

Reference documentation:

Https://yq.aliyun.com/articles/601067

Https://www.cnblogs.com/tatsuya/p/5025583.html

Https://www.cnblogs.com/brucewang/p/7825128.html

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.