High Availability In Sql Server 2008 - Log Shipping
Log shipping provides a means of providing a secondary server that can be used for data access in case the primary database server becomes unavailable
. log shipping is used to send transaction logs from one database (the primary database) to another (the secondary database) on a regular basis. Log shipping keeps the secondary database nearly synchronized with the primary database by constantly backing up the transaction logs from a primary database and then copying and restoring them to a secondary database.
LogShipping Operations:
Logshipping consists of three basic operations. A log can be shipped multiple times, essentially creating multiple secondary servers.
The three steps in log shipping include: taking a backup of the transaction log on the primary server instance, copy the backup to the secondary server and restore the transaction log on the secondary server.
You can configure log shipping to produce alerts when certain events have taken place or failed.
Secondary servers can server other purposes in order to relieve the load on the primary server (such as reporting).
Primary, Secondary and Monitor Servers:
Log shipping requires at least two servers with an optional third.
The primary server serves as the production server. This is the instance that backups will be created from.
The secondary server is the recipient of the transaction logs from the primary server instance and is capable of serving as the production server should the primary server instance become unavailable.
The optional, monitor server tracks the details of log shipping operations that take place and is capable of producing alerts upon completion or failure of events or jobs.
Log Shipping Jobs:
There are four primary jobs that occur when log shipping is implemented as a high-availability solution. These jobs are handled by the SQL Server Agent. You will control the frequency of these jobs.
The backup job, located on the primary server, performs the backup of the transaction logs, logs history to the local server and the monitor server, and deletes old backup files.
The copy job is created on the secondary server. This job handles the copying of the transactions log from the primary server instance to the secondary server instance.
The restore job entails the restoration of the transaction log on the secondary server instance.
An optional alert job can be implemented on a monitor server.The Job raises alerts for the primary and secondary databases when backup and restore operations have not completed successfully.
Log Compression:
Log compression is dependant on the state of backup compression on the SQL Server instance.
If backup compression is enabled, transaction log backups will be compressed.
You can use the sp_configure stored procedure to enable log compression during log shipping operations.
Enabling Log Shipping:
Enabling log shipping consists of several steps that take place on each SQL Server instance. There are also several requirements that must be met in order for log shipping to function properly.
Choose servers for your primary server,secondary server,and optional monitor server. A file share must be created between the primary and secondary server instances for transaction log backups to be copied to. Choose a backup schedule for the primary database. Create a folder for each secondaty server into which the transaction log backup files will be copied. Configure one or more secondary server. Optionally configure a monitor server.
Several stored procedures can be used to implement log shipping effectively allowing them to be saved for later use.
SQL Server Management studio can also be used to enable log shipping and the same generic steps can be used as those used when enabling log shipping through stored procedures.
Requirements:
1. The servers involved in Log shipping should have the same case sentivity settings.
2. The databases in log shipping configuration must use the full recovery model or bulk-logged recovery model
Benefits of Log shipping:
1. High Availability --->If primary is down we have the standby server hence it provides high availability of data 2. Cost ---> It is the least expensive as it is available even with Workgroup edition 3. Multiple Secondary ---> This provides higher redundancy of data by providing multiple standby servers
TO Learn More about "LOG Shipping using SQL Server 2008" please check out our website
by: Dbatag Dbatag
The Purpose of Server Racks Burry Hosting - Web Hosting Review, The Cheapest Plan Start under $1.00/month!! Web Hosting Review! Guidelines To Follow For Choosing A Web Hosting Server Is Managed Hosting Right for Me? Moving to Dedicated Servers Understanding a Top Web Hosting Companies List The Rise Of Ibm Iseries Servers Managed Hosting - What Does It Mean To You? 5 Necessary Factors When Choosing A Reliable Server The Importance Of Reliable Web Hosting Which Server Platform And Web Server Should I Use? Learn How to Fix "RPC Server is Unavailable" Problem Hostgator- Best Webhosting Option For Most