Enterprise TechSQLWhat is SQL Server Log Shipping and How to Set it Up February 29, 20164051 views0Share By IG ShareSQL Server Log ShippingLogshipping in SQL provides database redundancy to multiple servers in a cost effective way since no specialized hardware or shared storage is required. The setup is fully integrated into SQL Server Management Studio. Using Wizards you can perform a straightforward setup and easy database administration. In this post, I am going to talk about how to setup SQL Server Log Shipping.Note: If you buy something from our links, we might earn a commission. See our disclosure statement.The main advantage of Log Shipping is the ability to service “read-only queries. This enables you to do “Select’s” such as running reports on the secondary server. This however will have licensing implications. Please refer to my earlier post on Licensing SQL Server for High Availability deployments.Users are disconnected when log restore occursUnlike Database Mirroring, You can maintain multiple secondary servers and enable Read-only operations on them. Optionally, you can add a Monitor server to monitor the entire deployment setup and check for errors. It also records history and status of backup/restore jobs. You can set up the monitoring server to raise alerts when jobs fail.Considerations before implementing Log-Shipping Source: https://msdn.microsoft.com/en-in/library/ms190640.aspxUnlike Database Mirroring, Log-shipping is not a Real-Time solution. The secondary servers will have a lag of several minutes or more between backup-restore jobs.Additional license is required, if you want to use any of the Secondary servers for Read-Only or reporting queries.For implementing Log-Shipping, “Full Recovery Model” is required, which can result in significant increase in Log File sizes. Backup maintenance jobs needs to be implemented with a proper “retention policy” for log files.Additional overhead for running the “Backup” “Copy” & “Restore” jobs, compared to Database mirroring which uses a lightweight log compression algorithm for its synchronization method.Dedicated DBA is required for the additional Administrative overhead. You need to have full-time IT staff in order to maintain the above setup.Using “Backup Compression” is recommended in order to reduce the size of the backup files. Additional CPU overhead needs to be taken into consideration. This option is available from SQL Server 2008 and above.In the next post, I will talk about Windows Clustering for implementing a High Availability solution. Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0
Enterprise TechList of the Best Threadripper Pro Workstations – W-3100 Xeon W-3200 & W-1200 Alternatives By IGMay 25, 2021
Enterprise TechList of the Best HPTX Cases – Huge PC Cases for Large BuildsThe computer cases have been one of the essential aspects when it comes to enjoying ...
AzureBest Industrial IoT Routers & Gateways For AWS and Azure IoT ServicesIn this article, we are going to list out the Best Industrial IoT Routers for professional ...
Enterprise TechWhat Is A DataCenter Power Distribution Unit (pdu) Server Rack CabinetsNo matter whether you call it a server closet or server room or a cabinet, ...
Storage SystemsWD SE vs WD Red Pro Specifications Comparison – Enterprise Class NASThe HDDs or the Hard Disk drives are slowly moving into oblivion with the advancements ...