SQL Server Recovery Protecting Your Investment in the Event of a Shared Storage Failure

by

A shared storage failure can have a significant impact on your organization, especially if it leads to data loss. To minimize the risk of data loss, you need a recovery plan that includes failover to a standby system.

In this article, we’ll discuss how to set up SQL Server failover in the event of a shared storage failure.

We’ll also provide some tips on how to troubleshoot and avoid common pitfalls.

Setting Up Failover:

There are two main ways to set up failover in the event of a shared storage failure: using database mirroring or Always on Availability Groups.

Database Mirroring:

  • With database mirroring, one server acts as the primary server, while the other server acts as the standby.
  • The primary server maintains a copy of the database, which is synchronized with the standby server.
  • In the event of a failure, the standby server can take over as the primary server.
  • To set up database mirroring, you need to have two SQL Server instances installed on separate servers.
  • You’ll also need to create a witness instance, which helps to ensure that only one server is designated as the primary at any given time.
  • Once you’ve set up your environment, you can configure database mirroring.
  • The first step is to create a database mirroring endpoint on each server.
  • This endpoint uses Windows Authentication and uses port 5022 by default.
  • Next, you need to back up the transaction log on the primary server and restore it on the standby server.
  • Once that’s done, you can configure database mirroring.
  • To do this, you’ll use the Database Mirroring Wizard in SQL Server Management Studio.
  • The wizard will prompt you for the name of the principal server, the mirror server, and the witness server.
  • It will also ask you to specify the transport type and security mode.
  • The transport type can be either TCP/IP or HTTP, and the security mode can be either Windows Authentication or Certificate Authentication.
  • Once you’ve configured database mirroring, you need to set up failover.
  • To do this, you’ll use the Failover Cluster Manager.
  • In the console tree, expand the Services and Applications node.
  • Then, select the Database Mirroring service.
  • Right-click on the service and select Properties.
  • In the failover tab, you need to specify the name of the mirror server and the witness server.
  • You also need to specify the recovery mode, which can be either manual or automatic.
  • If you choose Automatic, SQL Server will automatically fail over to the mirror server in the event of a failure.
  • If you choose Manual, you’ll need to manually initiate failover when a failure occurs.
  • Once you’ve configured failover, you’re ready to test it.
  • To test failover, you can use the Failover Cluster Manager or the Database Mirroring Monitor.
  • The Failover Cluster Manager is a graphical tool that you can use to manage failover clusters.
  • To test failover, select the cluster you want to test and click on the Test button.
  • This will bring up a dialog box where you can specify which node you want to fail over to.
  • Click on the OK button to initiate failover.
  • You can also use the Database Mirroring Monitor to test failover.
  • This is a SQL Server Management Studio add-in that allows you to monitor database mirroring sessions.
  • To test failover, right-click on the database mirroring session and select Fail Over.
  • This will initiate failover to the mirror server.

Troubleshooting Failover:

There are a few common issues that you may encounter when setting up failover.

  • The first issue is that the witness server may not be able to communicate with the principal and mirror servers.
  • This can happen if the witness server is on a different subnet than the other two servers.
  • To fix this, you need to add an entry in the Windows hosts file for each server.
  • The second issue is that database mirroring may not be able to connect to the witness server.
  • This can happen if the witness server is behind a firewall or if it’s not configured correctly.

Conclusion:

Database mirroring is a great way to improve the availability of your SQL Server databases.

It’s important to properly configure failover in order to ensure that your databases are always available.

If you encounter any problems, be sure to check the Windows hosts file and the witness server configuration.

Leave a Comment