Log Shipping SQL Server 2012

SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. 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.

8357.LogShipping.jpg-650x0

Benefits 

  • 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.

Log shipping consists of three operations:

  1. Back up the transaction log at the primary server instance.
  1. Copy the transaction log file to the secondary server instance.
  1. Restore the log backup on the secondary server instance.

The log can be shipped to multiple secondary server instances. In such cases, operations 2 and 3 are duplicated for each secondary server instance.

A log shipping configuration does not automatically fail over from the primary server to the secondary server. If the primary database becomes unavailable, any of the secondary databases can be brought online manually.

You can use a secondary database for reporting purposes.

In addition, you can configure alerts for your log shipping configuration.

Configure Log Shipping 

*Prerequisites

  • The primary database must use the full or bulk-logged recovery model; switching the database to simple recovery will cause log shipping to stop functioning.
  • Before you configure log shipping, you must create a share to make the transaction log backups available to the secondary server. This is a share of the directory where the transaction log backups will be generated. For example, if you back up your transaction logs to the directory c:\data\tlogs\, you could create the \\primaryserver\tlogs share of that directory.1-Share backup folder

*Permissions 

The log-shipping stored procedures require membership in the sysadmin fixed server role.

configuration

-Right click the database you want to use as your primary database in the log shipping      configuration, and then click Properties.
-Under Select a page, click Transaction Log Shipping.
-Select the Enable this as a primary database in a log shipping configuration check box.
-Under Transaction log backups, click Backup Settings2-Bakup settings

-In the Network path to the backup folder box, type the network path to the share you created for    the transaction log backup  folder.

-Configure the Delete files older than and Alert if no backup occurs within parameters.

-Click OK.4-Network Path

-Under Secondary server instances and databases, click Add.3-Add

-Click Connect and connect to the instance of SQL Server that you want to use as your secondary server.

5-Secondary server instance connect

-In the Secondary Database box, choose a database from the list or type the name of the database  you want to create.

-On the Initialize Secondary database tab, choose the option that you want to use to initialize the  secondary database.

-On the Copy Files tab, in the Destination folder for copied files box, type the path of the folder into -which the transaction logs  backups should be copied. This folder is often located on the secondary  server.6-secondary database destination path

-On the Restore tab, under Database state when restoring backups, choose the No recovery  mode or Standby mode option.

-Click OK.

Success7-Success

-once you open the secondary server8-Sql2 connect 

9-Secondary server final mirror

🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s