Introduction
- Introduction
- Process of Log Shipping
- Log Shipping topology
- Log Shipping Prerequisites
- Step by step example
- How to Failing Over
- Conclusion
- Resources
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
- 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 .
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.
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
An Overview of Log Shipping in SQL Server 2005
http://www.codeproject.com/KB/database/LogShipping.aspx
No comments:
Post a Comment