SQL Server 2008 Implementing Log Shipping
In this post
- Introduction
- Process of Log Shipping
- Log Shipping topology
- Log Shipping Prerequisites
- Step by step example
- How to Failing Over
- Conclusion
- Resources
Introduction
SQL Server provide a way to backups automation that let to others
servers to maintain these backups in the scale out of the main server.
it's Log Shipping ! . Log Shipping is a way to make high availability
to solutions by making backups from the main server and restore it to
one or more other servers . administrator can recover the main SQL
server database (for any fail) from other servers by restore the
database from one of other server to main server.
Process of Log Shipping
Log Shipping focus on transaction log file of the database . Log
Shipping make a backup for that type of file only . therefore the
recovery mode for the main database must be in Full or Bullk-logged
option.
the following is the main processes of log shipping work .
1- Backup transaction log file of the main database to a folder founded in the main server.
2- Coping the transaction log backup from main sql server to one or more other sql server
3- Restore the transaction log backup that is exists in others sql servers.
Log Shipping topology
Log shipping consists of the following servers.
- Primary Database Server : that contain the primary database the need to maintain high availability.
- Secondary Database Server : in this server the second database that save the transaction log data from the primary database server
- Monitor Server : track the process of log shipping process in other both servers (primary, secondary).
Log Shipping Prerequisites
1- Primary database must to be a Full or Bulk-Logged recovery model.
2- Configurations need high permissions on SQL server and SQL Server Agent Service also.
3- at least two Database Servers or two SQL Server instances.
4- Folder for copying the transaction logs must be Shared and has a suitable permissions access.
Step by step example
1- First,
SQL Server Agent Service for both server (Primary, Secondary) must enabled (Started Service)
2-
Right Click on primary database for primary server. and then select
Properties. from the left pane select
Transaction Log Shipping .
3- click on checkbox
Enable this as a primary database in a log shipping configuration to enable log shipping in this database. and then click on
Backup Settings button.
4- in
Backup Settings window, set network path for
backup folder . this path let sql server in primary server to save
backup result on it. and also you can set path in local machine (in the
second textbox) if backup result will be set on the primary server not
in nework path.
it's mandatory to put network path to can leave this step. Consider also
that network path must be a shared folder with suitable permission to
let log shipping done task job well.
after that click on
Schedule Button to plan the time of backup job implementation.
5- Returning again to
Database Properties window to setting up the first secondary SQL Server. in the intermediate part of window ckick on
Add Button .
Press
Connect Button, and select your second SQL Server.
after selecting the second server, select the first option in the
Initialize Secondary Database
tab. this option mean that log shipping will generate a full backup of
the primary database and restore it into the secondary database . log
shipping will create the secondary database if it doesn't exist.
Now, goto second tab
CopyFiles to set path folder for
coping backup files after backup job finish. this coping process need to
automate . for automate that press the
Schedule button to set appropriate time job .
in the last tab
Restore transaction log select the second option
Standby mode .
the Standby mode mean that second database in the second server will be
read only database. it's suitable situation if need working with second
database by select query only, it's let customers to connect to this
database for reading but it's reading only.
there is second option No recovery mode that is mean the second database
will not be available for any one , it's just working for log shipping
processes.
select on
Schedule button to set appropriate time job for recovering.
after finish three tab options press
ok to return Database Properties window for primary database. the secondary database will appear like this.
press
Add button to add another SQLServer that will act as others secondary database servers.
6- in the last part of
Database Properties window, optionally click on use a monitor server instance checkbox option to determine a sql server that act Monitor Server.
7- in
Database Properties window click
ok, to take processes of log shipping work.
8- in the stage the log shipping work well . to check the effects of log
shipping check the added jobs in the both SQL agent service for two
servers.
Note
that because of Standby mode that selected for second database server,
the second database server will act a read only database as show below.
checkup also the result file in both paths that sets in the log shipping configuration .
How to Failing Over
1- After failing primary server, it's may be to find backup files in the
backup folder not transfered to destination folder. copy all that files
that is not transfered to destination folder(is the secondary server).
2- Restore all tranaction log backup files in the destination folder
sequencelly (identify that by date of files) to secondary sql server.
3- If it's possible to access primary database server take a (Tail log)
backup (BACKUP LOG-WITH NO_RECOVERY) this leaves the database in the
inconsistent state (restoring state) . and then restore (tail log)
backup to secondary SQL server with a (RESTORE LOG-WITH RECOVERY)
after these steps the primary database in unavailable and second
database is online now, it's time for administrator now to redirect
users to second server.
Conclusion
Log shipping is a way from
ways that can achive a level of high availability, design a suitable
topology with right configuration with prerequisites to have a higher
power benefits of log shipping.
Resources
Failing Over to a Log Shipping Secondary
http://msdn.microsoft.com/en-us/library/ms191233.aspx
SQL Server 2008 Log Shipping
http://blogs.technet.com/b/josebda/archive/2009/04/02/sql-server-2008-log-shipping.aspx
