The default schedule will have the transaction log backup running every 15 minutes. For the purposes of this example I will not be changing this but if you need it to run more frequently or less often you can easily change this by clicking Schedule and making any desired changes.
The final step on this page is to configure the backup compression setting. In most cases I would recommend having compression enabled. If you have database compression enabled on the server then you can select Use the default server setting. Otherwise I would recommend selecting Compress backup. Depending on the seize of your database this could make a significant difference in the time it takes for the backups to run and restore. In the next step we will configure the Secondary database.
To get started, click the Add… button under Secondary databases. The first step is to connect to the secondary server. In order to do this simply click Connect and specify the server. Once that is done the grayed out portion of the window containing the three tabs Initialize Secondary Database, Copy Files, and Restore Transaction Log will be available.
Now, depending on whether the database exists on the secondary server will determine which option you should select on the Initialize Secondary Database tab. If the database exists and is already populated from a recent backup you should be able to select the option No, the secondary database is initialized.
This option covers pretty much any scenario that could exist and is definitely the safest bet. If you choose the first option you can also click on Restore Options… to manually set the folder where the data and log files will reside on the secondary server.
The transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server , records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled. Provides a disaster-recovery solution for a single primary database and one or more secondary databases, each on a separate instance of SQL Server.
Supports limited read-only access to secondary databases during the interval between restore jobs. Allows a user-specified delay between when the primary server backs up the log of the primary database and when the secondary servers must restore apply the log backup.
A longer delay can be useful, for example, if data is accidentally changed on the primary database. If the accidental change is noticed quickly, a delay can let you retrieve still unchanged data from a secondary database before the change is reflected there.
All administration of the log shipping configuration through SQL Server Management Studio is performed from the primary database. Once the monitor server has been configured, it cannot be changed without removing log shipping first.
When log shipping is enabled, the job category "Log Shipping Backup" is created on the primary server instance. When log shipping is enabled on a database, the job category "Log Shipping Copy" is created on each secondary server in a log shipping configuration.
It logs history on the local server and the monitor server, and deletes old files and old history information. The backup files are copied to a network share that can be accessed by both SQL Servers. The backup files are restored to the destination database on the secondary SQL Server. Log shipping is useful if you are maintaining a "standby" server as a backup to your primary server, but do not require automatic failover. For information on manually failing over to a log-shipped standby server, see Failing over to a standby server.
Alternatives for maintaining a standby server include replication, a failover clustering solution, AlwaysOn Availability Groups, and database mirroring. SQL Backup Pro simplifies the configuration process for log shipping and protects against network outages. It also makes the backup and restore process up to ten times faster than native backup and restore, and supports compression and encryption.
If you have not already set up the destination database for log shipping, the wizard can do this for you by taking a full backup of the source database and restoring it to a new or existing database on the secondary SQL Server. This ensures the destination database is consistent with the primary database and in the correct state for receiving the transaction log backups. In this context, failover means substituting primary server with a backup sometimes also referred to as standby server if the primary hardware becomes unusable.
Failover solutions can be automatic or manual. With automatic failover, the backup server detects when the primary server is not available and takes over without any intervention from the database administrator. An example of an automatic failover solution is clustering. With manual failover, the database administrator DBA has to perform some steps to bring the standby server online. In SQL Server , log shipping is a form of manual failover.
Transaction log for the "log-shipped" database is backed up on the primary server periodically. Note that only databases that are in FULL recovery mode can be log-shipped. Transaction log backups are placed on a shared drive; standby server's SQL Server Agent account must have access to this shared drive. Transaction log backups are applied to the database on the standby server in the order that they were taken on the primary server. Either primary server, standby server, or a separate server can be used to monitor log shipping.
If the primary server becomes unavailable due to disk failure or some other reason, DBA can take the following steps to fail the database over to the standby server:. Copy all transaction log backups to the standby server and apply them in the same order they were taken on the primary server. Transfer any logins that exist on the primary server to the standby server. Only the logins that must have access to the log-shipped database must be transferred. This step might be further complicated if logins with the same name exist on both servers.
In such cases, the DBA needs to ensure that appropriate mappings exist between SQL Server logins and database users on the standby server. During the initial configuration of log shipping, you can allow the standby database to assume the primary role.
That means you can ship transaction logs from the standby server to the primary server after you have failed the primary database over. So if primary server server A fails over to standby server server B , servers can switch roles so that you can fail server B back to server A if needed.
I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time. Pearson Education, Inc. This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site.
Please note that other Pearson websites and online products and services have their own separate privacy policies. To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:.
0コメント