Log shipping is used to back up data in a primary database and restore it on a backup or secondary database. As an Azure SQL administrator preparing for the DP-300 exam, understanding how to configure log shipping is crucial. Microsoft Azure SQL provides the capabilities for log shipping and is a fundamental concept to grasp when administering Azure SQL solutions.
Log Shipping Overview
Log shipping operates by automatically sending transaction log backups from a primary SQL Server instance to one or more secondary SQL Server instances. This process involves three main operations:
- Backup Operation: This operation happens at the primary server. It involves performing a backup of the transaction logs of the primary database.
- Copy Operation: This operation involves copying the backup files into the secondary server (destination server).
- Restore Operation: This process happens at the secondary server and involves restoring the copied backup files into the secondary database in either a standby or recovery mode.
How to configure Log Shipping in Azure SQL
To configure log shipping, you need to perform a series of tasks. Here, we will review each task in detail.
1. Setting up the Primary Server
First, you need to set up the primary server. You can do so in the primary database properties window under the ‘Transaction Log Shipping’ page.
- Select ‘Backup Settings’ and specify the network path to the backup folder. The SQL Server Agent service account on the primary server must have read/write permissions to this network share.
- Under ‘Delete files older than’, specify the file retention period.
- Under ‘Backup local folder’, provide the local directory path where the backup will be stored temporarily.
Here is a screenshot of how it looks:
[Insert Screenshot here]
2. Setting up Secondary Server and Databases
This process involves configuring the secondary server and databases.
- In the ‘Secondary Databases’ tab, click ‘Add’ to define the secondary database.
- Specify the secondary database and destination folder for the copied files.
- In ‘Restore Transaction Log’ tab, select either ‘No recovery mode’ or ‘Standby mode’.
Here is a screenshot of how it looks:
[Insert Screenshot here]
3. Setting up Log Shipping Monitor
Monitoring Server instance holds details about all log shipping configuration and can send alerts if operations fail to occur as scheduled. To set up Log Shipping Monitor:
- Click on ‘Use a monitor server instance.’
- If you want SQL Server to raise an alert when backup, copy, or restore operations are late, check the ‘Enable alerts’ box.
Note: If you are configuring log shipping with SQL Server Management Studio, make sure to enable the Service Broker for the msdb database on all servers (primary, secondary, and monitor).
We have just walked through setting up log shipping manually. However, you can also automate the process using scripts. To generate a script, after setting up everything, click on the ‘Script Configuration to’ button, and choose ‘New Query Window.’ This script can be stored and used to set up log shipping in the future.
With a deep understanding and practical exposure to setting up log shipping, you will be well-equipped to handle related scenarios in the DP-300 exam for Azure SQL Administrator Associate certification. Gaining this knowledge is a step forward in becoming proficient in administering Microsoft Azure SQL solutions.
Practice Test
True or False: Log shipping in Microsoft Azure is a process of automating the backup of a database and transaction log files on a primary (source) database, and then restoring them onto a standby (target) server.
- True
- False
Answer: True
Explanation: Log shipping is a solution that allows you to automate the backup of database and transaction log files from a primary database server and then restore them on one or more standby database servers.
A primary server in a log shipping can have multiple secondary servers. True or False?
- True
- False
Answer: True
Explanation: A primary server in a log shipping can indeed have multiple secondary servers which essentially serve as backup servers.
Which of the following is NOT a component of log shipping in Azure SQL?
- A. Primary Server
- B. Secondary Server
- C. Monitor Server
- D. Relay Server
Answer: D. Relay Server
Explanation: Log shipping in Azure SQL consists of three components: The primary server, the secondary server, and the monitor server. There is no component called “relay server”.
In configuring Azure SQL log shipping, who has the responsibility of transferring the log backups to the secondary server?
- A. Monitor Server
- B. Primary Server
- C. Azure Blob Storage
- D. Secondary Server
Answer: C. Azure Blob Storage
Explanation: Azure Blob Storage is responsible for transferring the log backups to the secondary server for restoration.
True or False: The Monitor Server in the Log Shipping cannot send alerts if something goes wrong.
- True
- False
Answer: False
Explanation: The Monitor Server can indeed send alerts if something goes wrong. This includes if the process fails or falls behind.
Which of the following can be a disadvantage of log shipping in Microsoft Azure SQL Solutions?
- A. Data Redundancy
- B. Delay in Data Availability
- C. Data Fragmentation
- D. Data Consistency
Answer: B. Delay in Data Availability
Explanation: Sometimes there may be a delay in data availability due to the time required to back up the transaction logs, copy the backup files to the secondary server, and restore them on the secondary server.
True or False: It is possible to configure log shipping between two databases in the same Azure SQL server.
- True
- False
Answer: False
Explanation: Log shipping generally involves importing transaction logs from a primary server to one or more secondary servers. Two databases in the same Azure SQL server cannot carry out this process.
Which of the following scenarios is log shipping NOT suited for?
- A. Disaster recovery solution
- B. Offloading reporting
- C. Load balancing read operations
- D. Real-time data replication
Answer: D. Real-time data replication
Explanation: Log shipping involves a delay as the data is backed up and restored on the secondary server, which makes it unsuitable for real-time data replication.
The failure to restore logs on the secondary server triggers a notification to the monitor server. True or false?
- True
- False
Answer: True
Explanation: The monitor server in the log shipping configuration is responsible for tracking and managing all the information and can trigger notification in case of any failures.
Multiple user databases can be configured with Azure SQL log shipping. True or false?
- True
- False
Answer: True
Explanation: In Microsoft Azure SQL, multiple user databases can be configured for log shipping. This enables the replication of multiple databases to the secondary servers for backup purposes.
Interview Questions
What is log shipping in the context of Azure SQL?
Log shipping is a high-availability and disaster recovery solution in Azure SQL that keeps a primary and secondary database in sync by automatically sending transaction log backups from the primary database to the secondary database.
What are some of the advantages of configuring log shipping in Azure SQL?
The benefits include the ability to provide a warm standby server for failover in the event of primary server failures, the offloading of reporting traffic from the primary server, and minimization of data loss in the event of a disaster.
Are the primary and secondary databases in log shipping readable?
Only the primary database is readable and writable. In contrast, the secondary databases are only readable, and they cannot be modified while log shipping is implemented.
What are the primary components of a Log Shipping Configuration?
The components of a Log Shipping Configuration include the primary server, secondary server(s), primary database, secondary database, and the backup, copy, and restore job agents.
How frequently does the log shipping process in Azure SQL occur?
It generally depends on the backup and restore settings configured. However, a common configuration is to schedule log backups every 15 minutes.
Can log shipping be setup between databases in different Azure SQL servers?
Yes, log shipping can be set up between two databases in either the same Azure SQL server or in different Azure SQL servers.
How do you monitor Log Shipping in Azure SQL?
Log Shipping can be monitored via the Log Shipping Status report available in the SQL Server Management Studio and also by using system stored procedures and system functions provided by SQL Server.
How do you initiate failover with log shipping?
To initiate failover with log shipping, you manually fail over to the secondary database by restoring the latest transaction log backup with the RECOVERY option and redirecting clients to the secondary server.
When is log shipping a more suitable solution than Always On Availability Groups in Azure SQL?
Log shipping is more suitable when you need to support more than three secondary replicas or when you need to manually control the failover process.
What are the requirements for configuring log shipping in Azure SQL?
The requirements include the fact that the databases must be in full or bulk-logged recovery model, the primary and secondary servers must be different, and SQL Server Agent should be running on both servers.
How is the data transferred between the primary and secondary databases in log shipping?
Data is transferred by taking transaction log backups from the primary database, copying them onto secondary server(s), and then restoring the log backup onto the secondary database.
What happens to the connections in the primary database during the switchover in log shipping?
During the switchover operation in log shipping, all existing connections to the primary database are severed, and new connections are established with the new primary database.
Can you use different versions of SQL Server in a log shipping configuration?
No, you can’t use different versions of SQL Server in a log shipping configuration. The SQL Server version must be the same on both the primary and secondary server.
If a log backup job fails partway through, will log shipping continue from where it left off or start a new job?
Log shipping will start a new job. The failed log backup must be fixed or removed before log shipping can continue.
How can log shipping be configured to minimize network traffic?
Log shipping can be configured to minimize network traffic by setting the backup and restore job schedules such that they don’t peak at the same time or by compressing the backup files.
extutorials.com