Shulou(Shulou.com)05/31 Report--

This article focuses on "how to build a master-slave backup in SQL Server". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn how to build a master-slave backup in SQL Server.

About Log transfer

Like Oracle DG,MySQL master and slave, SQL Server also supports master-slave high availability. Further improve the security of data and the high availability of business. By transferring the logs on the primary database to the standby instance, the consistency of the data between the primary and standby database can be achieved.

Advantages * multiple backup libraries can be added to a primary library, thereby improving the solution to catastrophic data recovery. * like other database masters and slaves, slave libraries can improve read-only access (during data restore). * data delay application time can be customized. The advantage is that if the master library modifies the data incorrectly, and the slave library has not applied the modified data, then the data of the error environment can be restored by the slave library.

The log transfer process * backs up the transaction log in the primary server instance. * copy the transaction log file to the secondary server instance. * restore log backups in the secondary server instance.

Logs can be delivered to multiple secondary server instances. In these cases, operations 2 and 3 are repeated for each secondary server instance. The log shipping configuration does not automatically fail over from the primary library to the secondary server. If the master database becomes unavailable, you can manually switch to any slave library. The following figure is a master-slave environment composed of one master library and three slave libraries.

Note: backup compression is supported in SQL Server 2008 Enterprise and later.

prerequisite

The primary database must be in the Full recovery model or bulk-logged recovery model. Before you configure log shipping, you must create a shared backup directory so that the standby library can access transaction log backups. This is a share of the directory where the transaction log backup is generated. For example, if you back up the transaction log to the directory E:\ log_backup, you can create a share for that directory.

Build a master and slave

Create an administrator

The following steps are optional, and it is also possible to use the administrator administrator account. If you are using the administrator user, replace it with administrator, which is related to the sqladmin user rights below.

Create a sqladmin user to join the administrators group from the master and delete the default users group, and set "password never expires" and "user cannot change password" to create a password win+R for sqladmin, and enter lusrmgr.msc

Set user permissions right-click the user, click "Properties", delete the default USERS group and add the Administrators group.

Set the shared directory to create a log storage directory on the master or slave server E:\ log_backup

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,administaor user, check "full Control", "change" and "read" in the permissions of "sqladmin,administaor", and then click the OK button twice to save the shared settings.

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 "log_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.

Configure SQL Server startup mode

Open the SQLServer configuration manager from the master database server and the slave database server respectively, and "log in as" the sqladmin user for the SQLServer service and the SQLServer agent service with the startup mode: automatic sqlserver

Configure log transfer right-click the database server instance, select the "Properties" option, in the pop-up "Server Properties" interface, the "Security" on the left side of the stand-alone, then select "SQLServer and Windows authentication mode" in "Server Authentication" in the right window, and check "enable Server proxy account" option in "Server proxy account". Enter the correct proxy account and password and click OK to save the configuration information. no, no, no.

Configure the database AppsHK properties to be synchronized in the primary database server

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.

The network path of the backup folder on the primary database can be tested on the slave database to see if the slave database can access the slave database for operations:

Click the plan, in the "Job Plan Properties" interface, confirm that the "plan type" is repeated execution, for the test effect is obvious, set to 15 seconds to execute the job plan. Finally, confirm the "duration" and set it according to your needs. If you keep backing up, you can set it to "No end date".

Once the setup is complete, open the transaction Log backup Settings interface again after confirmation, then the backup job name will be followed by the "Edit Job" button, click in, and change the "owner" to sqladmin.

If there is no sqladmin, add first.

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

Click the OK button after setting up, save the configured script locally in the database properties configuration interface, and finally click OK.

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.

At this point, I believe you have a deeper understanding of "how to build master-slave backup in SQL Server". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.