SQL Server 2014 Log Shipping Deployment (7): Log shipping failover and delete log shipping

13.4 Fail-Over

13.4.1 Fault Location

As explicitly mentioned in the previous sections, log shipping consists of three basic jobs: backup jobs, copy jobs, and restore jobs. Use the previous festive log transfer monitoring function to locate which job has a problem:

If there is a problem with the backup job, check the primary server status.

If there is a problem with the restore job, check the secondary server status, or the secondary database is in standby mode when the user is using the secondary database.

If there is a problem with the replication job, check the network status in addition to the secondary server state.

13.4.2 failover

Log shipping Failover In addition to switching technology operations, it is more necessary to consider the consistency of the various dependent objects and the underlying environment between the primary and secondary servers.

1. Synchronizing dependent objects

Based on business continuity requirements, a high-availability solution for a database system must allow for the ability to switch between the current primary database and the secondary database. Log shipping technology is also one of the database high availability solutions, but log shipping is database-level and it does not transfer objects outside the transferred database. For example, SQL Server logins stored in the master database and SQL jobs stored in msdb, as well as other dependent objects.

Tips: It is recommended that you pass an SSIS package to transport dependent objects, which are periodically performed with SQL jobs to ensure that the dependent objects of the primary and secondary servers are synchronized to avoid the lack of availability of these dependent objects during failover.

2. Failover

When the primary server fails to become unavailable, switch the secondary server to assume the primary server role. After a primary server failure occurs, the copy job and the restore job on the SQL Agent may not have replicated and restored the most recent transaction log backup to the secondary server. Synchronize the secondary server as much as possible to ensure that business data is minimized during a failure. The manual transfer steps are as follows:

(1) Use "standard report" "Transaction log Shipping" To view log backups that have not yet been replicated and restored after a primary server failure occurs;

(2) Manually copy the transaction log backups to the secondary server, and restore the transaction log backups to the secondary database in NORECOVERY mode;

At this point the secondary server is ready to serve as the primary server for external use.

Tips: (1) Use NORECOVERY mode to restore the secondary database to make it available, and more importantly, the failover of log shipping can only be performed manually. (2) After the restoration of the primary server, you can use the original secondary server as the main server, the master server to rebuild log shipping as a secondary server, the steps follow the 13.2.3 section.

3. Client redirection connects to the secondary server

After a failover, the client is redirected to the secondary server, and as far as possible, the end-user operation is reduced, in general, in the following ways:

Application code develops an auto-aware failure feature in the application, logically connecting the primary server first, or, if the primary server is unavailable, automatically or manually connecting the secondary server through the application.

Network Load Balancing (NLB) through Network Load Balancing or similar hardware solutions, once a failure occurs that requires manual or script to be restored in NORECOVERY mode after the secondary server is added to the Network load balancer, Network Load Balancing automatically redirects the application to the secondary server.

Domain Name Service (DNS) DNS provides a mapping of name-to-IP addresses. Once the primary server fails, you can redirect the client's database requests to the secondary server by simply changing the IP address of the name corresponding to the secondary server's IP address.

SQL Client alias master database failure modification SQL Native client The SQL clients alias can redirect the client's database request to the secondary server, which is not necessarily useful if there are a large number of clients that need to be modified. If the client connects to the database server through the Web or application server, it only needs to modify the SQL client alias on the Web or application server, and all clients are redirected to the secondary server

。

13.5 Delete log Shipping

13.5.1 Deleting log shipping through Management Studio

The steps to remove log shipping through Management Studio are as follows:

1. Open the Properties page for the primary database DB01 as log shipping in the primary server SQLSVR1, and select transaction log shipping, and cancel the enable this database as the primary database in the Log shipping configuration check box.

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m01/57/fd/wkiom1slqzmhsa-haah2bnksf1q293.jpg "height=" 541 "/>

2. In the pop-up Database Properties screen, click "Yes";

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m00/57/fd/wkiom1slqz7jegjkaac1lwnyjr4124.jpg "height=" 153 "/>

3. Click "OK";

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m00/57/fa/wkiol1slqmpzhoa6aahvdj-gmxa560.jpg "height=" 551 "/>

4. Wait for the delete log shipping configuration to complete, click Close, and complete the delete log shipping.

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" image "src=" http:// S3.51cto.com/wyfs02/m00/57/fd/wkiom1slqa_z1pvlaadbwsrjyha447.jpg "height=" 286 "/>

13.5.2 Deleting log shipping with T-SQL

1. On the primary server, run the following command to remove information about the secondary server SQLSVR2 in the Msdb.dbo.log_shipping_primary_secondaries table on the primary server SQLSVR1:

Use Master;gosp_delete_log_shipping_primary_secondary db01,sqlsvr2,db01;

2. On the secondary server SQLSVR2, run the following command to remove information about the secondary server SQLSVR2 and copy and restore jobs on the secondary server SQLSVR2:

Use Master;gosp_delete_log_shipping_secondary_database db01;

3. On the log shipping primary server, execute the following command to remove information about the log shipping configuration for the primary server. This operation will also delete the backup job.

Use Master;gosp_delete_log_shipping_primary_database DB01;

4. On the monitoring server for the log, execute the following command to delete the alert jobs that monitor log shipping.

Use master; Gosp_delete_log_shipping_alert_job;

Summary

Log shipping is a simple, inexpensive, and reliable SQL Server high availability solution that preserves long-time tracking records. As a disaster recovery solution, you can deploy remotely to provide business continuity by maintaining a secondary server to mitigate the impact of local disasters, power grid failures, and network failures. Log shipping can send transaction logs to anywhere in the world, with the only limit being the network bandwidth required to transfer transaction logs in a timely manner.

This article is from the "Margin with Wish" blog, please be sure to keep this source http://281816327.blog.51cto.com/907015/1598318

SQL Server 2014 Log Shipping Deployment (7): Log shipping failover and delete log shipping