Latest Posts

Witness Server Setup in Log Shipping

Posted on by Zubair Shaik

Loading

Witness Server Setup in Log Shipping: A Comprehensive Guide

Introduction

In SQL Server, Log Shipping is a high-availability and disaster recovery feature that automatically sends transaction log backups from a primary database to one or more secondary databases. While Log Shipping provides a robust solution for data redundancy, it lacks built-in support for automatic failover. To address this limitation, a Witness Server can be introduced to monitor the health of the primary and secondary servers, facilitating automatic failover in certain scenarios.(MSSQLTips)

Understanding Log Shipping and Witness Server

  • Log Shipping: Involves three primary components:
    • Primary Server: The source server where the original database resides.
    • Secondary Server(s): One or more destination servers where transaction log backups are applied.
    • Shared Folder: A network share accessible by both primary and secondary servers for storing transaction log backups.(axial-sql.com)
  • Witness Server: An additional SQL Server instance that monitors the health of the primary and secondary servers. In case of a failure, the witness server can facilitate automatic failover to ensure continued database availability.(MSSQLTips)

Prerequisites for Configuring a Witness Server

Before setting up a Witness Server for Log Shipping, ensure the following:

  1. SQL Server Editions: All participating servers (primary, secondary, and witness) should be running compatible editions of SQL Server.
  2. Database Recovery Model: The primary database must be in Full or Bulk-Logged recovery model.
  3. Network Configuration: All servers should be on the same network and have proper connectivity.
  4. Shared Folder: A shared folder accessible by all servers for storing transaction log backups.
  5. SQL Server Agent: The SQL Server Agent service must be running on all servers.
  6. Permissions: Appropriate permissions should be set for SQL Server Agent accounts to access the shared folder and perform necessary operations.(MSSQLTips, SQL Solutions)

Step-by-Step Guide to Configuring a Witness Server in Log Shipping

Step 1: Prepare the Primary and Secondary Servers

  1. Primary Server:
    • Ensure the database is in Full or Bulk-Logged recovery model.
    • Create a full database backup and a transaction log backup.
    • Restore the full backup and transaction log backup on the secondary server with the NORECOVERY option.(SQL Solutions)
  2. Secondary Server:
    • Restore the backups from the primary server as mentioned above.
    • Ensure the secondary database is in Standby or NORECOVERY mode.

Step 2: Configure Log Shipping

  1. On the Primary Server:
    • Right-click on the database and select Properties.
    • Navigate to the Transaction Log Shipping page.
    • Check the option Enable this as a primary database in a log shipping configuration.
    • Click on Backup Settings to configure the transaction log backup schedule and destination.
    • Click on Add to configure the secondary server(s).(axial-sql.com, MSSQLTips, DZone)
  2. On the Secondary Server:
    • In the Initialize Secondary Database tab, choose the appropriate option based on how you want to initialize the secondary database.
    • In the Copy Files tab, specify the shared folder where transaction log backups will be copied.
    • In the Restore Transaction Log tab, configure the restore schedule and options.(SQL Solutions, MSSQLTips)

Step 3: Set Up the Witness Server

  1. Install SQL Server:
    • Install a compatible edition of SQL Server on the witness server machine.(axial-sql.com)
  2. Configure Endpoint:
    • On the witness server, create a database mirroring endpoint using the following SQL command:(Microsoft Learn) CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP (LISTENER_PORT = 7022) FOR DATABASE_MIRRORING (ROLE = WITNESS); GO
  3. Configure Logins:
    • On the witness server, create logins for the principal and mirror server instances.
    • Grant connect permissions on the mirroring endpoint to these logins.(Microsoft Learn)
  4. Configure Service Accounts:
    • Ensure that the SQL Server service accounts on all servers (primary, secondary, and witness) have appropriate permissions and are part of the same domain or trusted domains.

Step 4: Configure Automatic Failover

  1. On the Principal Server:
    • Alter the database to set the witness: ALTER DATABASE [YourDatabase] SET WITNESS = 'TCP://WitnessServerName:7022'; GO
  2. On the Mirror Server:
    • Alter the database to set the witness:(Microsoft Learn) ALTER DATABASE [YourDatabase] SET WITNESS = 'TCP://WitnessServerName:7022'; GO

Step 5: Verify Configuration

  1. Check Database Status:
    • On the principal server, run the following command to check the database status:(axial-sql.com) SELECT database_id, name, state_desc FROM sys.databases WHERE name = 'YourDatabase';
  2. Check Mirroring Status:
    • Run the following command to check the mirroring status: SELECT database_id, database_name, mirroring_guid, role_desc, state_desc FROM sys.database_mirroring WHERE database_name = 'YourDatabase';

Step 6: Test Automatic Failover

  1. Simulate Failure:
    • Stop the SQL Server service on the principal server to simulate a failure.(SQL Shack)
  2. Monitor Failover:
    • Monitor the witness server and
SQL Server Database Mirroring ...

Witness Server Setup in Log Shipping: A Comprehensive Guide

Introduction

SQL Server’s Log Shipping is a high-availability and disaster recovery feature that allows for the automatic transfer of transaction log backups from a primary database to one or more secondary databases. While Log Shipping provides data redundancy, it lacks built-in support for automatic failover. Introducing a Witness Server can address this limitation by monitoring the health of the primary and secondary servers, facilitating automatic failover in certain scenarios.

Understanding Log Shipping and Witness Server

  • Log Shipping: Involves three primary components:
    • Primary Server: The source server where the original database resides.
    • Secondary Server(s): One or more destination servers where transaction log backups are applied.
    • Shared Folder: A network share accessible by both primary and secondary servers for storing transaction log backups.
  • Witness Server: An additional SQL Server instance that monitors the health of the primary and secondary servers. In case of a failure, the witness server can facilitate automatic failover to ensure continued database availability.

Prerequisites for Configuring a Witness Server

Before setting up a Witness Server for Log Shipping, ensure the following:

  1. SQL Server Editions: All participating servers (primary, secondary, and witness) should be running compatible editions of SQL Server.
  2. Database Recovery Model: The primary database must be in Full or Bulk-Logged recovery model.
  3. Network Configuration: All servers should be on the same network and have proper connectivity.
  4. Shared Folder: A shared folder accessible by all servers for storing transaction log backups.
  5. SQL Server Agent: The SQL Server Agent service must be running on all servers.
  6. Permissions: Appropriate permissions should be set for SQL Server Agent accounts to access the shared folder and perform necessary operations.

Step-by-Step Guide to Configuring a Witness Server in Log Shipping

Step 1: Prepare the Primary and Secondary Servers

  1. Primary Server:
    • Ensure the database is in Full or Bulk-Logged recovery model.
    • Create a full database backup and a transaction log backup.
    • Restore the full backup and transaction log backup on the secondary server with the NORECOVERY option.
  2. Secondary Server:
    • Restore the backups from the primary server as mentioned above.
    • Ensure the secondary database is in Standby or NORECOVERY mode.

Step 2: Configure Log Shipping

  1. On the Primary Server:
    • Right-click on the database and select Properties.
    • Navigate to the Transaction Log Shipping page.
    • Check the option Enable this as a primary database in a log shipping configuration.
    • Click on Backup Settings to configure the transaction log backup schedule and destination.
    • Click on Add to configure the secondary server(s).(Made Simple MSSQL)
  2. On the Secondary Server:
    • In the Initialize Secondary Database tab, choose the appropriate option based on how you want to initialize the secondary database.
    • In the Copy Files tab, specify the shared folder where transaction log backups will be copied.
    • In the Restore Transaction Log tab, configure the restore schedule and options.

Step 3: Set Up the Witness Server

  1. Install SQL Server:
    • Install a compatible edition of SQL Server on the witness server machine.
  2. Configure Endpoint:
    • On the witness server, create a database mirroring endpoint using the following SQL command: CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP (LISTENER_PORT = 7022) FOR DATABASE_MIRRORING (ROLE = WITNESS); GO
  3. Configure Logins:
    • On the witness server, create logins for the principal and mirror server instances.
    • Grant connect permissions on the mirroring endpoint to these logins.
  4. Configure Service Accounts:
    • Ensure that the SQL Server service accounts on all servers (primary, secondary, and witness) have appropriate permissions and are part of the same domain or trusted domains.

Step 4: Configure Automatic Failover

  1. On the Principal Server:
    • Alter the database to set the witness: ALTER DATABASE [YourDatabase] SET WITNESS = 'TCP://WitnessServerName:7022'; GO
  2. On the Mirror Server:
    • Alter the database to set the witness:(SQL Solutions) ALTER DATABASE [YourDatabase] SET WITNESS = 'TCP://WitnessServerName:7022'; GO

Step 5: Verify Configuration

  1. Check Database Status:
    • On the principal server, run the following command to check the database status: SELECT database_id, name, state_desc FROM sys.databases WHERE name = 'YourDatabase';
  2. Check Mirroring Status:
    • Run the following command to check the mirroring status: SELECT database_id, database_name, mirroring_guid, role_desc, state_desc FROM sys.database_mirroring WHERE database_name = 'YourDatabase';

Step 6: Test Automatic Failover

  1. Simulate Failure:
    • Stop the SQL Server service on the principal server to simulate a failure.
  2. Monitor Failover:
    • Monitor the witness server and secondary server to ensure that the failover occurs as
SQL Server Database Mirroring ...

Witness Server Setup in Log Shipping: A Comprehensive Guide

Introduction

SQL Server’s Log Shipping is a high-availability and disaster recovery feature that allows for the automatic transfer of transaction log backups from a primary database to one or more secondary databases. While Log Shipping provides data redundancy, it lacks built-in support for automatic failover. Introducing a Witness Server can address this limitation by monitoring the health of the primary and secondary servers, facilitating automatic failover in certain scenarios.

Understanding Log Shipping and Witness Server

  • Log Shipping: Involves three primary components:
    • Primary Server: The source server where the original database resides.
    • Secondary Server(s): One or more destination servers where transaction log backups are applied.
    • Shared Folder: A network share accessible by both primary and secondary servers for storing transaction log backups.
  • Witness Server: An additional SQL Server instance that monitors the health of the primary and secondary servers. In case of a failure, the witness server can facilitate automatic failover to ensure continued database availability.

Prerequisites for Configuring a Witness Server

Before setting up a Witness Server for Log Shipping, ensure the following:

  1. SQL Server Editions: All participating servers (primary, secondary, and witness) should be running compatible editions of SQL Server.
  2. Database Recovery Model: The primary database must be in Full or Bulk-Logged recovery model.
  3. Network Configuration: All servers should be on the same network and have proper connectivity.
  4. Shared Folder: A shared folder accessible by all servers for storing transaction log backups.
  5. SQL Server Agent: The SQL Server Agent service must be running on all servers.
  6. Permissions: Appropriate permissions should be set for SQL Server Agent accounts to access the shared folder and perform necessary operations.

Step-by-Step Guide to Configuring a Witness Server in Log Shipping

Step 1: Prepare the Primary and Secondary Servers

  1. Primary Server:
    • Ensure the database is in Full or Bulk-Logged recovery model.
    • Create a full database backup and a transaction log backup.
    • Restore the full backup and transaction log backup on the secondary server with the NORECOVERY option.
  2. Secondary Server:
    • Restore the backups from the primary server as mentioned above.
    • Ensure the secondary database is in Standby or NORECOVERY mode.

Step 2: Configure Log Shipping

  1. On the Primary Server:
    • Right-click on the database and select Properties.
    • Navigate to the Transaction Log Shipping page.
    • Check the option Enable this as a primary database in a log shipping configuration.
    • Click on Backup Settings to configure the transaction log backup schedule and destination.
    • Click on Add to configure the secondary server(s).(Made Simple MSSQL)
  2. On the Secondary Server:
    • In the Initialize Secondary Database tab, choose the appropriate option based on how you want to initialize the secondary database.
    • In the Copy Files tab, specify the shared folder where transaction log backups will be copied.
    • In the Restore Transaction Log tab, configure the restore schedule and options.

Step 3: Set Up the Witness Server

  1. Install SQL Server:
    • Install a compatible edition of SQL Server on the witness server machine.
  2. Configure Endpoint:
    • On the witness server, create a database mirroring endpoint using the following SQL command: CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP (LISTENER_PORT = 7022) FOR DATABASE_MIRRORING (ROLE = WITNESS); GO
  3. Configure Logins:
    • On the witness server, create logins for the principal and mirror server instances.
    • Grant connect permissions on the mirroring endpoint to these logins.
  4. Configure Service Accounts:
    • Ensure that the SQL Server service accounts on all servers (primary, secondary, and witness) have appropriate permissions and are part of the same domain or trusted domains.

Step 4: Configure Automatic Failover

  1. On the Principal Server:
    • Alter the database to set the witness: ALTER DATABASE [YourDatabase] SET WITNESS = 'TCP://WitnessServerName:7022'; GO
  2. On the Mirror Server:
    • Alter the database to set the witness:(SQL Solutions) ALTER DATABASE [YourDatabase] SET WITNESS = 'TCP://WitnessServerName:7022'; GO

Step 5: Verify Configuration

  1. Check Database Status:
    • On the principal server, run the following command to check the database status: SELECT database_id, name, state_desc FROM sys.databases WHERE name = 'YourDatabase';
  2. Check Mirroring Status:
    • Run the following command to check the mirroring status: SELECT database_id, database_name, mirroring_guid, role_desc, state_desc FROM sys.database_mirroring WHERE database_name = 'YourDatabase';

Step 6: Test Automatic Failover

  1. Simulate Failure:
    • Stop the SQL Server service on the principal server to simulate a failure.
  2. Monitor Failover:
    • Monitor the witness server and secondary server to ensure that the failover occurs as

Leave a Reply

Your email address will not be published. Required fields are marked *