Press "Enter" to skip to content

Category: Log Shipping

Log Replay for Azure SQL Managed Instance

Joey D’Antoni has some quick notes on the Log Replay Service:

Recently, I’ve started on a project where we are migrating a customer to Azure SQL Managed Instance, which now supports a few different migration paths. You can simply backup and restore from a database backup, but you can’t apply a log or differential backup to that database. You can also use the Database Migration Service, but that requires a lot of infrastructure and Azure configuration. The log replay service, or LRS, is the functional equivalent of log shipping to your new managed instance database. While log shipping is a well known methodology for both database migrations or disaster recovery. However, the implementation is a little different–let’s talk about how it works.

Click through to see how it differs.

Comments closed

Alerting on Log Shipping Failures

Andrea Allred is waiting for an e-mail:

I don’t want emails that tell me everything is ok, only when things are bad and I want them to be helpful emails. Not only did I want an alert, I wanted an email with actual information that I can use to make my decisions. Decisions like, can I just apply a few logs to get caught up or did everything burn down and I need to pull a full backup plus all the logs to be up and running again?

This was a task for some super fancy alerts on my agent job.

Click through to see how.

Comments closed

Log Shipping Configuration with Powershell

Lee Markum needs to do log shipping in bulk:

I recently needed to configure log shipping for multiple databases at once as part of a migration project. I turned to PowerShell to do this.

But before we get to that part, this post assumes that you’ve done the upfront work to create shares for the backups to write to and for the backups to be copied to. This will involve providing the right permissions for the SQL Server service accounts involved in Log Shipping. If you are not familiar with this, that’s perfectly fine. Check out this article in MS Docs first.

Click through for the next steps in the process, including a well-timed Get-Help call.

Comments closed

Using Log Shipping With Delayed Recovery

Kenneth Igiri shows how you can combine log shipping with delayed recovery to prevent data loss due to user mistakes:

Now let’s talk about the key benefit of this delay. In the scenario, where a user inadvertently drops a table, we can recover the data quickly from the Secondary Database as long as the Delay period has not elapsed. In this example, we drop the table Sales.Orderlines on BOTH databases and verify that the table no longer exists in BOTH databases.

It’s a fairly expensive technique but potentially still cheaper than restoring from backups.

Comments closed

Log Shipping Tests With dbachecks

Sander Stad has a bonus post in his log shipping series:

We want everyone to know about this module. Chrissy LeMaire reached out to me and asked if I could write some tests for the log shipping part and I did.

Because I wrote the log shipping commands for dbatools I was excited about creating a test that could be implemented into this module for everyone to use.

That test is also quite easy to use, as Sander demonstrates.

Comments closed

Recovering A Log-Shipped Database

Sander Stad wraps up his series on log shipping:

Initially log shipping was meant to be used as a warm standby. You have your data on another instance but you still have some human intervention to get it all back up.

Imagine the following situation. You have setup log shipping using either the GUI or the commands in dbatools. You have about 15 databases and everything is working fine.

Until one day the primary instance goes down and is not recoverable. For the production to continue you have to bring the log shipped databases online fast.

You have to figure what the last transaction log backup was. You have to check if it was copied to the secondary instance and if it’s restored.

To do this by running a couple of queries, copying the files if needed and run the log shipping jobs takes time. I’d rather run a command and recover one or more databases and get back to the problem of the primary instance.

Read on to see how to use dbatools to recover a log shipped database.

Comments closed

Log Shipping With dbatools

Sander Stad shows off a few log shipping functions he created for dbatools:

The entire log shipping functionality is now separated between 5 functions. Four of them are used internally and are not visible as a public function because you can easily break stuff if it’s not being used correctly.

The main function, Invoke-DbaLogShipping, is available in the dbatools module for anyone to use.

If you open the GUI for the log shipping you have lots of choices but most of them are already supplied in the GUI itself and you can decide whether to use them or not.
The whole idea behind the functionality was that it would allow you to quickly setup log shipping using a lot of defaults like you can in the GUI, but if you’re more experienced you can change any setting to your preferences.

Read on for an example of how to use this.  It looks pretty snazzy.

Comments closed

sp_AllNightLog

Brent Ozar announces an open source project to perform log shipping at scale:

On the primary server, multiple Agent jobs take backups simultaneously across lots of databases using Ola Hallengren’s proven DatabaseBackup proc. You control the backup frequency and the number of Agent jobs to balance recoverability against performance overhead.

On the restoring server, same thing, but with sp_DatabaseRestore – multiple Agent jobs watch folders for incoming files, and restore ’em. Just like with regular log shipping, you can control the job schedules, so you can use the servers for read-only reporting workloads during the day, then catch ’em back up to current overnight.

You can install the restore side of it on multiple servers, in multiple locations, too.

This looks like a very interesting project for automating DR solutions.

Comments closed

The Value Of Log Shipping

Robert Davis explains that log shipping can be better than mirroring for database migrations:

This topic has come up several times recently, so I feel the need to blog on it. As the person who wrote the book on Database Mirroring, it will probably come as a surprise to many of you that I believe that log shipping is a much better tool for database migrations than database mirroring.

I’m not just talking about the fact that database mirroring is deprecated (since SQL Server 2012) and log shipping is not. Both are still in SQL Server to this day. Because database mirroring is deprecated, it is no longer receiving bug fixes (except maybe critical security bugs) and no work is being done to make sure that it works with new features in current and future versions. Log shipping is still receiving both of these things. I will lay out the real reasons below.

Robert makes two compelling arguments in favor of log shipping.

Comments closed

Log Shipping On Linux

Andrew Pruski digs into how to set up log shipping for SQL Server on Linux:

What I’m going to do is setup two instances of SQL Server running on linux and log ship one database from one to another. So the first thing I did was get two VMs running Ubuntu 16.04.1 LTS which can be download from here.

Once both servers were setup (remember to enable ssh) I then went about getting SQL setup, I’m not going to go through the install in this post as the process is documented fully here. Don’t forget to also install the SQL Tools, full guide is here.

Read on for the guide, but also be sure to read his disclaimer.

Comments closed