Removing Monitor Server From Log Shipping: A Step-By-Step Guide

Removing a monitor server from log shipping can be done in several ways. One way is to use Management Studio by choosing the primary database properties, unchecking the Enable this as a primary database in a log shipping configuration checkbox, and clicking OK. Another method involves using T-SQL commands to delete secondary information on the primary server and issuing a specific command on the secondary server. Additionally, there are manual steps to remove log shipping, including executing specific procedures on the primary and secondary servers. In some cases, it may be necessary to update the log shipping tables on the primary and secondary nodes to point to a new monitor server.

Removing log shipping with Management Studio

• Choose the primary database properties: Start by accessing the properties of the primary database in your Management Studio. This will allow you to configure the log shipping settings.
• Select "Transaction Log Shipping": Under the "Select a Page" section, choose "Transaction Log Shipping". This will bring up the specific settings related to log shipping for this database.
• Uncheck the "Enable this as a primary database in a log shipping configuration" checkbox: By unchecking this option, you are disabling log shipping for this primary database. Click "OK" to confirm the change.
• Remove secondary servers (optional): If you have any secondary servers associated with the primary database, you can remove them by selecting the server instance under "Secondary Databases" and clicking "Remove". This step is optional, depending on your specific setup.
• Uncheck the "Use a monitor server instance" checkbox: To remove the monitor server from the log shipping configuration, simply uncheck this box. This will disable the use of a monitor server for this setup.

By following these steps, you will have successfully removed log shipping using Management Studio. It is important to note that when you remove log shipping, all schedules, jobs, history, and error information associated with it will be deleted. Therefore, it is recommended to script the log-shipping configuration before deleting it if you think you might need to redeploy log shipping in the future.

Removing log shipping with T-SQL commands

Step 1: Remove Log Shipping Information from the Primary Server

On the primary server, execute the following T-SQL command:

Use Master;

Sp_delete_log_shipping_primary_secondary @primary_database, @secondary_server, @secondary_database;

This command will delete secondary information related to the log shipping configuration from the primary server. Make sure to replace `@primary_database`, `@secondary_server`, and `@secondary_database` with the appropriate database and server names.

Step 2: Remove Log Shipping Information from the Secondary Server

Next, connect to the secondary server and execute the following command:

Use Master;

Sp_delete_log_shipping_secondary_database @secondary_database;

This command will delete the secondary database and remove log shipping information from the secondary server. Again, replace `@secondary_database` with the correct database name.

Step 3: Remove Log Shipping Job Information from the Primary Server

Now, return to the primary server and execute one more command to remove the log shipping job information:

Sp_delete_log_shipping_primary_database @database;

Here, replace `@database` with the name of the relevant database.

Step 4: Handle Witness or Monitor Servers (If Applicable)

If you have a witness or monitor server as part of your log shipping configuration, you will need to take additional steps to remove log shipping completely. Connect to the witness or monitor server and execute similar commands to remove the relevant information. You can refer to specific guides for handling witness or monitor servers, as it involves additional considerations.

Note: Before performing these steps, ensure that you have the necessary permissions and that you are comfortable with T-SQL commands. Always be cautious when executing commands that can modify your database configuration.

Manually removing log shipping

To manually remove log shipping, you need to perform three steps:

Step 1: Delete Information about the Secondary Database from the Primary Server

On the primary server, execute the following command:

EXEC master.dbo.sp_delete_log_shipping_primary_secondary @primary_database = N'AdventureWorks', @secondary_server = N'LogShippingServer', @secondary_database = N'LogShipAdventureWorks' GO

This command will delete information about the secondary database from the primary server. Make sure to replace the database and server names with your specific values.

Step 2: Delete Information about the Log Shipping Configuration from the Primary Server

On the primary server, execute the following command:

Sp_delete_log_shipping_primary_database N'AdventureWorks'

This command will delete information about the log shipping configuration and also remove the SQL Agent backup job from the primary server. Again, replace 'AdventureWorks' with your primary database name.

Step 3: Delete the Secondary Database

On the secondary server, execute the following command:

Sp_delete_log_shipping_secondary_database N'LogShipAdventureWorks'

This command will delete the secondary database. Replace 'LogShipAdventureWorks' with your secondary database name.

These steps will completely remove log shipping from your configuration. Please note that you should be cautious while performing these steps, as manually removing log shipping can have consequences if not done correctly. It is always recommended to have a backup of your data before proceeding with any database-related operations.

Additionally, you can also remove log shipping using Management Studio or T-SQL commands. With Management Studio, you can choose the primary database properties, select "Transaction Log Shipping," and clear the checkbox for "Enable this as a primary database in a log shipping configuration." For T-SQL commands, you can use the sp_delete_log_shipping_primary_secondary command on the primary server and the sp_delete_log_shipping_secondary_database command on the secondary server.

Moving to a new monitor server

As mentioned previously, there is no straightforward way to move to a new monitor server. One option is to simply remove and re-add log shipping using the new monitor server. However, if you want to avoid this, there is a manual method that involves updating the log shipping tables on all three servers (primary, secondary, and new monitor).

Firstly, create the new linked server objects on the primary and secondary servers. This involves using specific code to generate the same linked object name for the new monitor server that the log shipping jobs would calculate. Once you have the linked server name, you can create the linked server on the primary server by following these steps:

• Right-click on "Linked servers" and select "New Linked Server" to open the wizard.
• In the general tab, enter the linked server name and select "SQL Server" as the server type.
• On the Security tab, choose "Be made using the login's current security context".
• On the server options tab, change RPC and RPC out to True.
• Click OK.

You will likely encounter an error as the wizard tries to validate the link connection to the monitor server. Click "Yes" to keep and create the linked server. Repeat these steps on your secondary server using the same linked server object name.

Next, configure the data_source name of the linked server object in the sys.Servers table to connect to the new monitor server. Update the is_system flag to true for security purposes. Verify that your new linked server is configured correctly by running a query.

Now, gather information from the MSDB log shipping tables on both the primary and secondary servers. You will need the primary_id and secondary_id GUIDs from each server.

Update the new monitor server with the gathered information so it knows to expect data from the primary and secondary servers. Add the primary and secondary ID GUIDs, server names, database names, and alert thresholds.

Finally, update the log shipping tables on the primary and secondary nodes to point to the new monitor server. After this step, your servers will stop updating the existing monitor server and will start updating the new one.

If done correctly, when your log shipping agent jobs run, you should see a fully functioning entry for your database(s) on your new monitor server.

Removing a monitor server from a log shipping configuration

To remove a monitor server instance from a log shipping configuration, you can use either the Management Studio or T-SQL commands.

Removing Log Shipping with Management Studio

• Choose the primary database properties.
• Under "Select a Page", choose "Transaction Log Shipping".
• Uncheck the "Use a monitor server instance" checkbox and click OK.

Removing Log Shipping with T-SQL Commands

Issue the following command on the primary server:

> Use Master; sp_delete_log_shipping_primary_secondary @primary_database, @secondary_server, @secondary_database;

On the secondary server, issue the following command:

> Use Master; sp_delete_log_shipping_secondary_database @secondary_database;

Note that removing log shipping will delete all schedules, jobs, history, and error information. It is recommended to script the log-shipping configuration before deleting it to facilitate quick redeployment in the future.

Frequently asked questions