Logshipping Introduction:
• It is a high available technique, and it is used to Shipping of logs from primary server to the secondary 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.
• An optional third server instance, known as the monitor server, records the history and status of backup and restore operations.
• Additionally it will record the backup information in Primary server and restoration information in Secondary server in MSDB database on Logshipping tables.
Log shipping consists of three operations:
ü Back up the transaction log at the primary server instance.
ü Copy the transaction log file to the secondary server instance into some backup share (\\servername\Tlog_bkp ).
ü 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.
• We can use a secondary database for reporting purposes.
• We can configure alerts for with our log shipping configuration.
Primary Server & Database:
• The primary server in a log shipping configuration is the instance of the SQL Server Database Engine that is your production server.
• The primary database is the database on the primary server that you want to back up to another server.
• 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.
Secondary Server and Databases:
• The secondary server in a log shipping configuration is the server where you want to keep a warm standby copy of your primary database.
• A secondary server can contain backup copies of databases from several different primary servers.
• For example, a department could have five servers, each running a mission-critical database system. Rather than having five separate secondary servers, a single secondary server could be used. The backups from the five primary systems could be loaded onto the single backup system, reducing the number of resources required and saving money.
• The secondary database must be initialized by restoring a full backup of the primary database initially when configuring the Transaction logshipping.
• The restore can be completed using either the NORECOVERY or STANDBY option. This can be done manually or through SQL Server Management Studio.
Logshipping Modes:
Logshipping can be configured in 2 modes/ways:
No-Recovery Mode : This is also called a Non-operational mode, users may not able to access the database for querying. Database status would be in Restoring Mode.
Standby Mode : To allow users read-only access to the standby database, choose Read only (Standby Mode); this means you can distribute queries across the standby server to reduce the primary server’s workload (but you cannot modify the data in the standby database and normally used this for reporting purposes). Database would be in Standby/read-only mode.
Logshipping Jobs:
We have 5 logshipping jobs
· Backup Job (Primary Server) – Will take the transaction log backup from primary server database.
· Backup Alert Job (Primary Server) – Will send us alert if the transaction logshipping backup job fails.
· Copy Job (Secondary Server) – Copy the transaction logs from primary sever DB backup location to the backup share.
· Restore Job (Secondary Server) – Will restore the database backup logs from the backup share.
· Restore Alert Job (Secondary Server) – Will send us alerts if the restore job fails.
Advantages:
• Log shipping can be configured by using the Database Maintenance Plan Wizard. It’s just a BCR (Backup, Copy & Restore).
• Can be configured in No recovery mode (will not available for users to query) & Standby mode , available for read only purpose or else reporting purpose.
• Low Maintenance.
• You can ship the transaction logs from the primary server to multiple standby servers. This way, you further reduce the chances of downtime.
• Can be configured in No recovery mode (will not available for users to query) & Standby mode , available for read only purpose or else reporting purpose.
• Low Maintenance.
• You can ship the transaction logs from the primary server to multiple standby servers. This way, you further reduce the chances of downtime.
Disadvantages:
• Transactions at the primary and secondary may or may not sync until or unless if the backup ,copy and restore is very frequent.
• Possible data loss when the primary server fails. If the primary server becomes completely unusable, transactions that occurred after the last transaction log backup that was copied to the standby server are lost. For example, suppose that server A fails at 5 a.m. and you cannot get to it at all. If the last backup copied to server B was taken at 4:45 a.m., all transactions that occurred between 4:45 a.m. and 5 a.m. are lost forever.
• Some manual DBA work is required to bring the standby server online.
• Log shipping setup cannot be scripted. This means that you cannot mimic the production environment for testing purposes without going through the wizard screens.
• Possible data loss when the primary server fails. If the primary server becomes completely unusable, transactions that occurred after the last transaction log backup that was copied to the standby server are lost. For example, suppose that server A fails at 5 a.m. and you cannot get to it at all. If the last backup copied to server B was taken at 4:45 a.m., all transactions that occurred between 4:45 a.m. and 5 a.m. are lost forever.
• Some manual DBA work is required to bring the standby server online.
• Log shipping setup cannot be scripted. This means that you cannot mimic the production environment for testing purposes without going through the wizard screens.
Important Keys Points:
ü You must have at least two SQL Server database engine servers or two database engine instances in your log shipping implementation.
ü The database must use the full or bulk-logged recovery model.
ü You must have a shared folder to copy the transaction log backups to.
ü The SQL Server Agent service account of the primary server must have read/write access either to the shared folder or to the local NTFS folder.
ü The SQL Server Agent account of the standby server must have read and delete access to the shared folder.
ü The SQL Server Agent services must be running and configured with network credentials (such as a domain account)
How to configure logshipping was discussed in one of my earlier post ….. http://dilipparakala.blogspot.in/2013/02/setup-sql-server-transaction-logshipping.html
No comments:
Post a Comment