Enterprise TechSQL What is SQL Server Log Shipping and How to Set it Up February 29, 2016758 views0 Share SQL Server Log Shipping Logshipping 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. 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 occurs Unlike 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.aspx Unlike 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. Disclaimer: The Questions and Answers provided on https://www.gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0 IG Website Twitter
TSQL How to Effectively split a string using STRING_SPLIT function in SQL server By DataGeekApril 15, 2019
Enterprise Tech How to use Azure Ad Join on Windows Server 2019? & Gateway Access Just like Microsoft Windows 10, the Microsoft Server 2019 also lets you make use of ...
Enterprise Tech What is VMware VMotion? Limitations & Requirements – Storage & Shared If you are in the IT industry, especially infrastructure professionals, you may have heard the ...
Cloud Computing Differences Between VMWare SRM Standard and Enterprise Licensing Disaster Management is one of the essential aspects of any virtualization technique. VMWare, being one ...
Enterprise Tech Cisco WebEx vs Skype for Business: Differences and Comparisons The workforce today has become completely mobile. With that change in the work strategy, the ...