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

This article shows you how to achieve high-availability log transfer in sql server database, the content is concise and easy to understand, it can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

one。 Overview of log shipping

SQL Server uses log shipping to automatically send transaction log backups from the primary server to one or more secondary databases. Transaction log backups are applied to each secondary database separately. An optional third server instance, called the Monitoring Server, records the history and status of backup and restore operations and can raise alerts when these operations cannot be performed as scheduled.

Optional monitoring server to record the history and status of backup and restore operations.

There is no more to say below, let's take a look at the detailed introduction.

Advantages

Provide disaster recovery solution support restricted read-only access to the secondary database allows the user to define the delay for log delivery (long delays can be useful if the data on the primary database is accidentally changed)

Terms and definitions

Backup job

The agent job of the primary server, which performs backup operations, records transaction logs to the local server and monitoring server, and deletes old records.

Replication job

An agent job for the primary server that copies backup files from the primary server to the secondary server and records them on the secondary server and the monitoring server.

Restore job

The proxy job of the secondary server, the backup files are restored to the secondary database, recorded on the local server and monitoring server, and the old files are deleted.

Repeat replication and restore jobs for multiple secondary servers.

two。 Preparatory work

Two instances of the same service database (can be multiple servers in the local area network)

Master database sqlserver 2012 R2 MSSQLSERVERTWO

Secondary database sqlserver 2012 R2 MSSQLSERVER

Presentation library LogShipping_Test, the same primary and secondary libraries, the same table structure and data. Same sql login, password

three。 Configure log shipping

-- step 1: set the primary server recovery model to full backup ALTER DATABASE LogShipping_Test SET RECOVERY FULL-- step 2: make a full backup of the primary service database BACKUP DATABASE LogShipping_Test TO DISK='D:\ LogShipping\ LogShipping_Test.bak' WITH NOFORMAT,INIT

-step 3:

Create a shared directory on the primary server C:\ LogShipping\ primary with job access

Create a shared directory on the secondary server C:\ LogShipping\ secondary with job access

-step 4:

Primary server LogShipping_ Test Library-- > Properties-- > Task-- > Transport transaction Log after the primary server and secondary database are configured successfully, set replication and restore to deliver every two minutes, as shown in the following figure

After the log shipping configuration is successful, the secondary database is identified as (standby / read-only) as follows

The main database SQL Server agent job is shown below:

Backup Job (LSBackup_LogShipping_Test)

Warning Job (LSAlert_ {computer name})

The secondary database SQL Server agent job is shown in the following figure

Replication job (LSCopy_ {computer name} _ LogShipping_Test)

Restore job (LSRestore_ {computer name} _ LogShipping_Test)

Warning job (LSAlert_ {computer name}\ MSSQLSERVERTWO)

The file share directory backed up by the main server every 2 minutes is as follows

The file share directory replicated by the secondary server every 2 minutes is as follows

Finally: check to see if log shipping is correct

Use visualization to check whether log shipping is normal in a SQL Server agent job

Use SQL query to see if log shipping is working properly.

-- (primary database query) exec master..sp_help_log_shipping_monitor exec master..sp_help_log_shipping_primary_database 'LogShipping_Test'-- (secondary database query) exec master..sp_help_log_shipping_secondary_database' LogShipping_Test'

four。 Manually switch configuration between master and slave database

Step 1: in the primary database, make it in the process of restoring

Use master Backup log [LogShipping_Test] to disk ='c:\ LogShipping\ LogShipping_Test1.bak' with NORECOVERY

-- manually run replication and restore jobs on the secondary database (quick replication restore to slave tables)

-manually disable backup and warning jobs on the primary database (stop backup)

Step 2: on the secondary database, restore the spare parts file using the step

Use masterRestore log [LogShipping_Test] from disk ='c:\ LogShipping\ LogShipping_Test1.bak' with RECOVERY

Delete the data from the folder where you previously backed up and replicated (D:\ LogShipping\ primary,D:\ LogShipping\ secondary)

Reconfigure log shipping to turn the secondary database into the primary database on the secondary database (LogShipping_ Test Library-- > Properties-- > Task-- > after shipping transaction logs have successfully configured the primary server and the secondary database)

Delete the log shipping of the original master database (LogShipping_Test Library-- > Properties-- > Task-- > transfer transaction log, check and uncheck to determine).

The above is how to achieve high-availability log transfer in sql server database. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.