What is SQL Server Log Shipping?
Log Shipping allows replicating databases using the database transaction log. The transaction log contains all the transactions in the database. The main idea is to copy the transaction log to a secondary database in order to replicate the information. So, you need a Primary Server with a database, configure log shipping to have some transactional log backups, and then copy it to a secondary database to have a replica.
How to Configure Log Shipping in SQL Server?
To configure Log Shipping, you need at least two SQL Servers on the same network. However, it is recommended to have three servers. The third server (known as Monitor Server) is used to monitor Log Shipping.
To start Log Shipping, right-click the database that you want to use for log shipping and select Tasks > Ship Transaction Logs.
Check the Enable this as a primary database in Log Shipping configuration option and press the Backup Settings button to configure the backup configuration.
You have two options to configure the path of the backup:
- You can configure a Network path. Make sure to grant permissions to write in the path to the SQL Server Agent.
- If the folder is in the primary server, you can use local paths.
Additionally, you can delete older files. By default, files older than 72 hours are deleted. Also, by default, an alert is raised if no backup occurs within 1 hour.
How to Add Secondary Instance and Database?
Once you have your primary database configured, right-click the database that you want to use for Log Shipping and then select Tasks > Ship Transaction Logs.
In the Secondary server instances and databases, press the Add button to add a secondary server instance and database.
Now, press the Connect button to connect to the SQL Server, which will be used as a secondary database.
Enter the SQL Server name and press Connect.
You have 3 options in the Secondary Database Settings:
- You can generate a full backup and restore it into the secondary database.
- You can use an existing backup (if you have one). You need to specify the path.
- If the secondary database was already restored, you do not need to do anything else.
In the Copy Files tab, specify the destination folder to copy the files. You can also set when the files will expire. By default, the time is 72 hours.
In the Restore Transaction Log tab, you have two options:
- The No Recovery mode: In this mode, the secondary database is not in read-only mode. The users cannot read the data.
- The Standby mode: It is in read-only mode. The users can read the information but not modify or delete the data.
There are also options to delay the restoring backups or send alerts, if no restore occurs within a specified time. By default, it is 0 minutes for the Delay restoring backups at least option and 45 minutes for the Alert if no restore occurs within option.
To change the job name and schedule, you can press the Schedule button. The Schedule will let you set the execution date, time, and frequency of the backups.
What is Monitor Server Instance?
Log Shipping Monitor is a SQL Server that you can use to monitor the Log Shipping process. Suppose, there are problems between the primary and secondary servers. You can check Transaction Log Shipping Status report there. You could use a single instance to monitor several Primary and Secondary instances.
How to Configure Monitor Server Instance?
To set up the Monitor Server instance, you need to first right-click the database that you want to use for log shipping and select Tasks > Ship Transaction Logs to start the configuration.
Then, try to add the Monitor Server Instance. To do that, press the Settings button in the Monitor server instance and set up the server.
What are the Jobs Created by Log Shipping?
The jobs are automatic tasks that are configured when you set up log shipping. These tasks work on the SQL Server Agent and are used to schedule the Log Shipping tasks. If you go to the SQL Server Agent in SSMS, to the job section, you can see and modify the jobs used for Log Shipping. The following jobs are created by Log Shipping:
- Backup Job: It is created in the primary database. This job backup the transaction log.
- Copy Job: The job copies the transaction log backup from the Primary Server to the secondary.
- Restore Transaction Log in the Secondary Database: This job restores the copied backup.
- Alert: If something fails, this job will raise an alert.
How to Configure Primary Server in Log Shipping using T-SQL?
The following code will create a primary server for Log Shipping:
--Declare 2 variables
DECLARE @JobId AS uniqueidentifier ;
DECLARE @ PrimaryId AS uniqueidentifier ;
EXECUTE dbo.sp_add_log_shipping_primary_database
--The database name is stellar
@database='stellar',
--Path of the backup
@backup_directory=N'c:\stellar',
-- Share folder for backup
@backup_share = N'\\backup\stellar',
--Job name
@backup_job_name = N'LSBackup_Stellar',
--Retention period of the backups in minutes.
@backup_retention_period = 1000,
--Name of the server to monitor
@monitor_server = N'StellarMonitor',
--Enable the security mode.
@monitor_server_security_mode = 1,
--The backup will run every 25 minutes.
@backup_threshold = 25,
--No alert will be raised if the backup threshold is reached.
@threshold_alert = 0,
--Threshold alert disabled
@threshold_alert_enabled = 0,
--Retention period equal to 1000 minutes.
@history_retention_period = 1000,
--The job ID of the backup will be the output to the variable @backup_job_id
@backup_job_id = @ JobId OUTPUT,
--The primary ID for the log shipping configuration is the output to the variable @primary_id.
@primary_id = @ PrimaryId OUTPUT,
Disable overwriting the configuration
@overwrite = O,
--Compress the backup
@backup_compression = 1;
GO
How to Configure Secondary Server in Log Shipping using T-SQL?
The following code will create a secondary database in Log Shipping:
USE master
GO
--the stored procedure is used to add the secondary database
EXEC sp_add_log_shipping_secondary_database
--Specify the database name
@secondary_database = 'SecondaryDatabaseName',
--Specify the server’s name of the primary server
@primary_server = 'MyPrimaryServer',
--Specify the primary database
@primary_database = 'stellar',
--This enables the option to restore the database
@restore_mode = 1,
--When this is set to 1, the users are disconnected.
@disconnect_users = 1,
--This shows the minutes that can elapse between log shipping restoration before a warning
@restore_threshold = 60,
--name of the job created
@restore_job_name = 'RestoreJobName'
GO
What to do if the Log Shipping Database is Corrupted?
Sometimes, the database gets damaged or corrupted due to virus or malware attacks, natural disasters, or any other reason. To repair the Log Shipping database, you can use SQL repair software, such as Stellar Repair for MS SQL. The software takes a few minutes to download and install.
Before using the software, make sure to set your database offline.
USE [master]
GO
ALTER DATABASE stellar SET OFFLINE
GO
Once it is offline, make a copy of it and work with the copy. This will help if something goes wrong.
Now, launch the software and select the database copy.
Press the Repair button to repair your database.
Once repaired, you can save your data. Press the Save icon. You can create a new database or replace the existing one.
In addition, you can save the tables’ data and views in other formats, like Excel, CSV, and HTML.
Conclusion
In this article, we learned what SQL Server Log Shipping is, how to configure Log Shipping in SQL Server, and how to add primary and secondary servers in Log Shipping. Finally, we learned how to fix a damaged database in Log Shipping using Stellar Repair for MS SQL.
Technical Marketer at Stellar Information Technology Private Limited. He makes Tech concepts easy to understand with his strong grip on Technology.
4 min read
4 min read
4 min read
