DP-300 Administering Microsoft Azure SQL Solutions

Configure log shipping

Concepts

Log shipping is a key feature in Microsoft Azure SQL Solutions that allows you to create and maintain a secondary copy, or replica, of your database for disaster recovery and reporting purposes. By configuring log shipping, you can ensure that your SQL database remains highly available, even in the event of a primary database failure.

1. Create a target database

First, you need to create a target database on the secondary server where the log shipping copy will be stored. You can create the database using Transact-SQL or the Azure portal.

2. Enable log shipping

To enable log shipping, you need to run a series of Transact-SQL statements. Open SQL Server Management Studio and connect to the primary server. Execute the following T-SQL statements, replacing values in square brackets with your own configurations:

-- Enable log shipping on the primary database USE [PrimaryDatabase] EXEC sp_add_log_shipping_primary_database @database = N'PrimaryDatabase', @backup_directory = N'\\BackupShare\BackupFolder', @backup_share_drive = N'\\BackupShare', @backup_schedule_description = N'Nightly Backup', @backup_threshold = 60, @threshold_alert_enabled = 1


-- Add the secondary server

EXEC sp_add_log_shipping_secondary_primary

    @primary_server = N'PrimaryServer',

    @primary_database = N'PrimaryDatabase',

    @backup_directory = N'\\BackupShare\BackupFolder',

    @backup_share_drive = N'\\BackupShare',

    @overwrite = 1
-- Configure the secondary database

-- You need to create a backup and restore job manually for the first time

EXEC sp_add_log_shipping_secondary_database

    @secondary_database = N'SecondaryDatabase',

    @primary_server = N'PrimaryServer',

    @primary_database = N'PrimaryDatabase',

    @restore_mode = 1,

    @restore_threshold = 60
-- Configure the log backup job

EXEC sp_add_schedule

    @schedule_name = N'Nightly Backup',

    @freq_type = 4,

    @freq_interval = 1,

    @active_start_time = 10000

EXEC sp_add_jobserver @job_name = N'LSBackup_PrimaryDatabase', @server_name = 'PrimaryServer'

3. Set up the file share

Log shipping requires a file share to store the transaction log backups. You can set up a file share in Azure Storage or use a network file share. Make sure that the secondary server has appropriate permissions to read from the file share.

4. Copy backups to the secondary server

After the log shipping configuration is complete, SQL Server Agent jobs on the primary server will start creating transaction log backups and copying them to the designated file share. The secondary server will then restore those backups to the secondary database.

By following these steps, you can configure log shipping in Azure SQL Solutions. Remember that log shipping is an asynchronous process, so there might be a slight delay between the primary and secondary databases. However, log shipping provides a reliable and efficient method for maintaining a secondary copy of your database for disaster recovery and reporting purposes.

Answer the Questions in Comment Section

Which of the following statements about log shipping in Microsoft Azure SQL Solutions is true?

a) Log shipping can be configured between a primary database and multiple secondary databases simultaneously.

b) Log shipping requires both the primary and secondary databases to have the same schema and data.

c) Log shipping in Azure SQL Solutions can only be configured manually and cannot be automated.

d) Log shipping in Azure SQL Solutions requires the use of a third-party tool for configuration.

Correct answer: a) Log shipping can be configured between a primary database and multiple secondary databases simultaneously.

True or False: Log shipping in Azure SQL Solutions can be used for disaster recovery purposes.

Correct answer: True

Which of the following components are involved in log shipping in Azure SQL Solutions? (Select all that apply.)

a) Primary server

b) Secondary server(s)

c) Transaction log backup jobs

d) Backup and restore jobs

e) File transfer jobs

Correct answer: a) Primary server, b) Secondary server(s), c) Transaction log backup jobs, d) Backup and restore jobs

True or False: Log shipping in Azure SQL Solutions can be configured across different geographical regions.

Correct answer: True

When configuring log shipping in Azure SQL Solutions, which of the following backup types are supported? (Select all that apply.)

a) Full backups

b) Differential backups

c) Transaction log backups

d) Copy-only backups

Correct answer: a) Full backups, c) Transaction log backups

In log shipping, which component(s) is/are responsible for restoring transaction log backups on secondary databases? (Select all that apply.)

a) Primary server

b) Secondary server(s)

c) File transfer jobs

d) Backup and restore jobs

Correct answer: b) Secondary server(s), d) Backup and restore jobs

True or False: Log shipping in Azure SQL Solutions supports automatic failover to secondary databases in case of a primary server failure.

Correct answer: False

Which of the following replication modes can be used when configuring log shipping in Azure SQL Solutions? (Select all that apply.)

a) Synchronous replication

b) Asynchronous replication

c) Mirrored replication

d) Snapshot replication

Correct answer: b) Asynchronous replication

True or False: Log shipping in Azure SQL Solutions can be configured with a delay in applying transaction log backups to secondary databases.

Correct answer: True

Which of the following are considerations when configuring log shipping in Azure SQL Solutions? (Select all that apply.)

a) Network bandwidth and latency

b) Disk space requirements on the primary database

c) Compatibility of the secondary databases with the primary database

d) Availability of a backup location for transaction log backups

Correct answer: a) Network bandwidth and latency, b) Disk space requirements on the primary database, d) Availability of a backup location for transaction log backups