February 17, 2016 at 9:12 pm
Comments posted to this topic are about the item Why Log Shipping is still a viable solution in 2016
February 18, 2016 at 1:13 am
Thanks for the info. Valid perspective in my opinion.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 18, 2016 at 5:55 am
Use it for moving databases between data centres.
Simple straight and straightforward.
February 18, 2016 at 6:33 am
TDE does reduce compression, but it's often a requirement to have it on - don't optimize yourself into an insecure configuration.
February 18, 2016 at 6:58 am
Log Shipping is tried and true, no doubt, but data center operations folks mostly (in my experience) think about 0 data loss from a DR perspective and therefore favor mirroring tech like AlwaysOn and storage replication. The delayed restore is something that is rarely considered by the data center folks however this can be crucial for business continuity. At present Log Shipping is the only OOTB feature option. There is a connect item asking for AO to have delayed sync options but if that were added to the product Standard Edition would still be a reason to implement Log Shipping for delayed sync needs.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 18, 2016 at 7:53 am
Nice article, Kevin, thank you.
I have developed a custom log shipping solution for our DR server (more flexible than the Microsoft one, and without agent jobs all over the place). It could be used to check my backups (since I can chose to restore all my backups automatically). It can leave the databases in standby mode so that we can run reports very quickly between 2 restore sessions and have very fresh data every time, etc... Log shipping for me can be quite convenient and versatile, especially when tailored via custom scripts. The out-of-the box solution from Microsoft is already quite good and simple to setup and to use (not quite the same picture with AlwaysOn AG, where you need a lot more legwork).
I have a question by the way: could you please provide me with a link from Microsoft where it says that the secondary is for free of licensing fees as long as one doesn't run anything but the log shipping jobs on it? I thought that this setup would require software assurance since SQL Server 2012...
Cheers
February 18, 2016 at 8:12 am
We use SAN replication in combination with VMware recovery groups, but I still keep log shipping in my back pocket just in case for our three most critical databases. It isn't likely that we would actually fail-over to any or all of them, but I find it extremely useful to have the copies available. If someone makes an oops and tells be soon enough I can recover records from that copy without the lag-time of a restore. Even better is that if we are going to make some updates to tables, etc. I can just suspend the restore job(s) and if something goes wrong scrape out what is needed.
I like log shipping because it is just so forgiving and it just runs. The only time I have problems with it isn't so much related to log shipping itself, but to disk space. Sometimes people don't realize that the select into thing, Massive update, etc. actually generates substantial backup files.
Cheers
February 18, 2016 at 8:48 am
Sure, here's the link to the original SQL Server 2012 Licensing guide, page 14 onwards will hopefully help, https://www.google.co.uk/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0ahUKEwjqueDB0YHLAhVMOxQKHe58CwsQFggfMAA&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2F7%2F3%2Fc%2F73cad4e0-d0b5-4be5-ab49-d5b886a5ae00%2Fsql_server_2012_licensing_reference_guide.pdf&usg=AFQjCNH9fa6bCvXk8bHYo7OaJGGnr78qFw&bvm=bv.114733917,d.d24 .
I will throw a disclaimer in here though, as it may be subject to change in later versions or different license programs 😉
February 18, 2016 at 9:22 am
Thank you! I thought I had read this one through but I must have got lost somewhere 😛
Ah, I can see there are indeed restrictions... page 18-19:
License Mobility is a use right that is available for all editions of SQL Server 2012 software licenses with active Software Assurance (SA) coverage. With this SA benefit, customers can re-assign SQL Server licenses to different servers within a server farm as often as needed. Customers can also reassign licenses to third party shared servers. License Mobility is available for licenses under both the Per Core and Server+CAL license models.
? SQL Server licenses that are not covered with active SA can only be reassigned to a different server within a server farm once every 90 days, and they cannot be reassigned to a third party web hoster or non-private cloud at any time. (In the event of permanent hardware failure, the 90-day reassignment limit is waived.)
? All SQL Server licenses with active SA can be reassigned to another server within the server farm as often as needed; however, they can only be reassigned to another server in another server farm, or to a non-private cloud, once every 90 days.
A server farm may consist of up to two data centers located in time zones that are within four hours of one another and/or with the European Union (EU) and/or European Free Trade Association (EFTA).
A given data center may only be part of one server farm.
? License Mobility use rights do not apply to SQL Server 2012 PDW software.
So, without SA, you can only use your secondary for DR warm standby, and you can't really fail over to it to maintain availability of your systems while you are doing patching or Windows updates on the primary (which may/should be more frequent than once every 90 days)...
I have just checked the SQL 2014 licensing guide, and the wording is the same.
February 18, 2016 at 9:48 am
Nelson,
I too wrote my own log shipping/restore routines. It does everything the out of the box solution does, but I never put it in to production. While I thought it was fun to write, I think all I really did was reinvent the wheel. I've used the restore portion more than anything.
Cheers
February 18, 2016 at 10:07 am
jfogel, in fact my custom scripts were originally meant to help me restore the databases to a point in time quickly, reading the databases backup history data, copied over from the primary msdb database (via another script + job). One step further and it was automated as a log shipping solution that didn't require one agent job per database (we have many on that instance).
Digging deeper, I figured out that the out-of-the-box log shipping was using executables to do the restore, but I wanted to restore with standby for reporting and couldn't do it with the executables.
So all in all, I turned a handful of DR scripts into log shipping because I had the opportunity with little additional effort and a benefit greater than the Microsoft one would bring me. Plus I learnt a lot on the way, and that is invaluable 😉
Cheers
February 18, 2016 at 10:26 am
My stuff also has all that functionality as well, and yes, it was worth all the effort for the learning experience. The reporting aspect is nice, but not something I could use in my environment. Well, to say I can't use it is incorrect. I ended up not needing it because we use replication for the reporting server.
Oddly enough, I've had issues using the MS GIU to do point in time restores while my process works without any issues. I could never figure out why the MS-supplied stuff failed, but having to do it is so rare and when it happens it is a hot issue, so I never really took the time to investigate. That alone is worth a good bit to me.
Cheers
March 9, 2016 at 4:33 pm
Thank you for the information.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply