Transaction logs are a critical component of SQL Server databases. In this lesson, you'll learn how to perform log shipping to create a backup of the transaction log on another server.
- [Instructor] The transaction log in a SQL installation is an important file. Recall your transactions in the database, such as updates, inserts, and deletes are written to the transaction log first, and then to your tables when the database engine determines it is a good time to do so. Log shipping offers the following advantages. It provides a disaster recovery solution, and it supports limited read-only access to secondary databases, during the interval between restore jobs. It also provides for setting a delay between when the primary server log backup and secondary servers restore.
Why would we use log shipping? Well, we can automatically send log backups to a secondary server, if that's what we decide to do. It preserves those transaction logs for the recovery later on. We can also set up a monitoring server, and this is a third server instance that acts as a monitor server, and can actually record the history and status of the backups and the restores as they take place. The monitor server can also raise alerts if the operation should happen to fail. The three main operations are, backing up your transaction log on the primary server, copying that log to a secondary server instance, and restoring the backup on the secondary server instance.
Let's take a quick look at how to configure log shipping on a primary server. In order to do so, we open SQL Server Management Studio, and find the database that we want to use for log shipping. In this case, we will use AdventureWorks. Right clicking and choosing properties opens the database properties window, where we find transaction log shipping. Here, we can enable this as a primary database, and a log shipping configuration. Clicking backup settings allows us to specify a network path for a backup folder, and in this case, this could be one as simple as a network share that we have already created.
So, a network path to our folder where we have the backups stored can be put in, also for dealing with the local backup, so we might have something stored in our local hard drive, and we can actually have that set up, as well as a database backup, or a log backup, or what have you. We can put this in here as well. Note the caution here that you must grant read and write permissions on this folder to the service account for SQL Server to ensure that it has permissions to actually write to that folder. We can specify things such as deleting files that are older than so many hours, and we can also send an alert if no backup occurs within a certain timeframe, so this is determined based on our scheduling for the backup of the transaction log.
We will create a backup job, and the job name. The default is LSBackup underscore AdventureWorks, so it's just, LS, meaning Log Ship, and then it's a backup, and it's of the AdventureWorks database. You can click the schedule button, and then you can configure the schedule to be recurring, and this is a standard new job schedule from SQL Server. Then, the summary of what your schedule will be shows up here in the schedule box. We can choose to compress the log file backup if we wish. Once we selected the options here, we can click okay.
Now that we are back into our database properties for the transaction log shipping, this is where we can add a secondary database, and we'll do that in a moment, but I also wanted to point out, if we had a third instance, we could use it as a monitor server. We are going to demonstrate that here, because it requires a third server in the configuration. So, under secondary databases, let's click the add button, and then choose connect. In this instance, I'm going to just do a log shipping on my same SQL Server instance that I have, but I will store it in a different database, so when I connect to that SQL Server, it will ask me where I want to put my secondary database, and in this case, I want to use one that I can have for transaction log shipping.
I've created one earlier, called ADV underscore Works underscore TL, for Transaction Log Shipping. When we initialize our secondary database, we can choose a couple of different options. We can generate a full backup of the primary database, and restore it into the secondary database, and create the secondary database if it doesn't exist, and then there are various restore options, such as the folder for the data file, and the folder for the log file. You can also choose to restore an existing backup of the primary database into the secondary database, and create the second one if it doesn't exist.
I'm just going to select this option, that says, no, the secondary database is already initialized, so in other words, we had created it, it's initialized, and it's ready to go. The copy files tab is where you would put a destination folder for copied files. Typically, this would be located on your secondary server. I'm not going to put that in here, because primarily, it's used for a secondary server that might be on the network, someplace else. Files are restored from the destination folder on the secondary server instance, and we can choose how to do the restore transaction log.
The database state should be put in no recovery mode, or standby mode, depending on the option that you choose here, and we can delay the restoring backups for a specific number of minutes, and also send an alert if no restores have occurred. Again, we get a restore job name, and a schedule that goes along with it. For the backup, I'm actually going to put in, D colon backslash, and I have a DB underscore backup folder that will allow me to actually store it on the secondary server as well.
Once again, normally, this would be on a network server, but we can also store it on our local server instance as well. Let's click the okay button, and now we see that we have our server instance, and our database configured here. We are ready to click the okay button, and then we see that it has saved a secondary destination configuration, and it has saved the primary backup set, and now our transaction log shipping has been completed.
This course is also ideal for anybody preparing for the Administering a SQL Database Infrastructure (70-764) exam, one of two exams necessary to earn an MCSA: SQL 2016 Database Administration certification.
- Installation and configuration considerations
- Data storage strategies
- Configuring database instances
- Performance considerations
- Security considerations
- Managing roles and users
- Managing data
- High-availability options