High Availability of SQL Server -- log Shipping

References:

Http://msdn.microsoft.com/en-us/library/ms187103.aspx

Overview

SQL Server uses log transmission, you can automatically back up transaction logs in the "primary database" on the "primary server" instance to one or more "secondary databases" on the separate "secondary server" instance ". Transaction Log backup is applied to each secondary database. The optional third server instance (called the "Monitoring Server") records the history and status of backup and restore operations, and can trigger an alarm when these operations cannot be performed as planned.

Advantages

1. Provides disaster recovery solutions for a single primary database and one or more secondary databases (each database is located on a separate SQL server instance.
2. Supports limited read-only access to secondary databases (during the interval between restoration jobs ).
3. The delay time is defined as the time between the backup time of the primary database log on the primary server and the backup time of the secondary server log that must be restored (application. For example, if the data on the primary database is accidentally changed, a long delay may be very useful. If unexpected changes are detected soon, you can retrieve unchanged data from the secondary database before the secondary database reflects the changes.

Terms and definitions

1. Primary server: the SQL server instance on the production server.
2. Primary Database: the database on the master server of another server. All log transfer configuration management performed through SQL Server Management studio is executed in the master database.
3. Secondary server: the SQL server instance in which you want to retain the Hot Standby copy of the primary database.
4. Secondary Database: A hot backup copy of the primary database. Secondary databases can be in the recovering or STANDBY state, which enables the database to be used for restricted read-only access.
5. Monitor server: an optional instance of SQL Server that tracks all detailed information transmitted by logs, including the time when transaction logs were last backed up in the primary database. The time when the secondary server last copied and restored the backup file. Information about any backup failure alarms.

Log transfer procedure

Log transmission consists of three operations:

1. Back up transaction logs in the master server instance.

2. Copy the transaction log file to the auxiliary server instance.

3. Restore the log backup in the secondary server instance.

Logs can be sent to multiple secondary server instances. In these cases, operations 2 and 3 are repeated for each secondary server instance.

The log transfer configuration will not automatically fail over from the primary server to the secondary server. If the primary database becomes unavailable, You can manually bring any secondary database online.

You can use the secondary database for report purposes.

In addition, you can configure alarms for log shipping configurations.

Typical log shipper Configuration

Displays the log transfer configuration with the master server instance, three secondary server instances, and one monitoring server instance. This figure illustrates the steps for backing up a job, copying a job, and restoring a job, as shown below:

1. The master server instance executes a backup job to back up transaction logs on the master database. Then, the server instance puts the log backup into the master log backup file (this file will be sent to the backup folder ). In this figure, the backup folder is located in the shared directory ("backup sharing.

2. All three secondary server instances execute their respective copy jobs to copy the primary log backup file to its local destination folder.

3. Each secondary server instance executes its restoration job to restore the log backup from the local destination folder to the local secondary database.

The primary and secondary server instances send their own historical records and statuses to the monitoring server instance.

Log shipping configuration

Primary server: SANZ-W7 \., starting the default database instance with Local System

Primary Database: insidetsql2008

Secondary server: WUW-W7 \., start the default database instance with the domain account wuwang, because if you use the local system account, you cannot access network resources.

Secondary Database: insidetsql2008

Configuration steps:

Step 1:

Create a shared directory on the master server SANZ-W7 to give everyone read permission. For example, the shared directory path I created is: C: \ temp \ logshipping, and the shared path is: \ SANZ-W7 \ logshipping.

Step 2:

Make a full backup of the primary database insidetsql2008 (you can do not do a full backup, and do a full backup when configuring log shipping, as described below), and then put the backup file insidetsql2008.bak in C: \ temp \ logshipping directory.Create the directory c: \ temp \ logshipping on the secondary server.

Step 3:

Set the Configuration Attribute of logshipping. Right-click insidetsql2008-> tasks-> SHIP transaction logs. The interface shown in is displayed:

Click Backup settings for the following Configuration:

A job namedLsbackup_insidetsql2008To automatically back up transaction logs.

Step 4:

Then add the secondary server WUW-W7, as shown in:

Seconday server instance we choose WUW-W7, secondary database by default is the same as the primary database insidetsql2008. As shown in, we can see two options in initialize secondary database. One is to create a full database backup and then reply the full backup to the secondary database, the other is to specify the path of the existing full backup.

Add the secondary server to the copy file label to save the file path. Here we use the path with the same name. In addition, the secondary server also needs to start the SQL Agent.

After the configuration is complete, click OK. A successful configuration page is displayed:

Log shipping Verification:

We view insidetsql2008 information under the service server WUW-W7, as shown in:

We can find that insidetsql2008 is in the resotoring state, because it is used to restore to the Service database.NonrecoveryMode.

After fully configuring log shipping, we can see the following log backup files in the C: \ temp \ logshipping directory of the primary server:

In the c: \ temp \ logshipping directory of the secondary server

The only difference is that the primary server has full database backup, but not in the secondary server. Whether it's lsbackup_testdb1 on the primary server, or the LSCopy_SANZ-W7_TESTDB1 and LSRestore_SANZ-W7_TESTDB1 on the secondary server, I set them to execute every minute so that you can easily view the experiment results.

"Cocould not retrieve Backup Settings for primary ID" error Solution

References: http://blogs.msdn.com/ B /sqlsakthi/archive/2012/06/14/error-quot-could-not-retrieve-backup-settings-for-primary-quot-in-log-shipping-backup-job.aspx

View in SSMsLsbackup_insidetsql2008The following error is reported when the job is working:

*** Error: Could not retrieve backup settings for primary ID 'f32baa93-0341-48b5-a5a0-2acde90283fa'.(Microsoft.SqlServer.Management.LogShipping)***<nl/>2012-09-09 19:30:15.85*** Error: Failed to connect to server WIN-E5EJQ0EN4O2.(Microsoft.SqlServer.ConnectionInfo)***<nl/>2012-09-09 19:30:15.85*** Error: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider<c/> error: 40 - Could not open a connection to SQL Server)(.Net SqlClient Data Provider)***<nl/>2012-09-09 19:30:15.85----- END OF TRANSACTION LOG BACKUP   -----

From the above error log, we find that the connection fails.Server win-E5ejq0en4o2. But my primary server is a SANZ-W7.Win-E5ejq0en4o2?When the system was first installed, SQL Server was immediately installed and the host name was changed fromWin-E5ejq0en4o2ChangedSANZ-W7. However, there is no update in SYS. servers. You can view it using the following tsql statement:

select @@SERVERNAME

The query result isWin-E5ejq0en4o2. We only need to update the SYS. servers information in the database. Run the following tsql statement to update the server information.

-- Delete the old host name exec sp_dropserver 'win-e5ejq0en4o2 'Go -- add the current host name exec sp_addserver 'sanz-w7', 'local' go

After executing the preceding tsql statement, remember to restart SQL Server. Otherwise, use select @ servername to query whether the current server name isWin-E5ejq0en4o2.