July 4, 2017 at 3:15 am
Hi all,
One of my clients has both of these for the same server. For my own understanding I'd like to know what possible reasons this might be for. I can think of a few...
1) Extra layer of safety - if one or other fails catastrophically - they still have their data;
2) Mirroring enabled, local backups due to be switched off but as these cause little overhead, there's little urgency on this;
3) Logs bloating, therefore backups maintained (which IS the case there)
Any others I may have missed? And any other considerations
July 4, 2017 at 3:31 am
JaybeeSQL - Tuesday, July 4, 2017 3:15 AMHi all,One of my clients has both of these for the same server.
Good, finally a sensible client.
Mirroring does not, in any way. make backups unnecessary. Mirroring is HA, high availability, the ability to keep operating when something fails.
Backups are DR, a way to get back up and running if something goes severely wrong.
Mirroring won't save you from accidental drops of a table, or scripts run without a where clause.
Depending on where the servers are, mirroring may not save you from a site failure (though backups need to be off-site to protect against this)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 4, 2017 at 8:59 am
--3) Logs bloating, therefore backups maintained (which IS the case there)
There is NO reason for log bloating with proper backup strategies (which perforce should be happening with log shipping being setup). Well, no RATIONAL reason anyway. I suppose something silly like index reorg followed by index rebuild, unnecessary updating of all rows in largest table, or some such.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 4, 2017 at 9:50 am
TheSQLGuru - Tuesday, July 4, 2017 8:59 AM--3) Logs bloating, therefore backups maintained (which IS the case there)There is NO reason for log bloating with proper backup strategies (which perforce should be happening with log shipping being setup). Well, no RATIONAL reason anyway. I suppose something silly like index reorg followed by index rebuild, unnecessary updating of all rows in largest table, or some such.
Could you elaborate on the index part? I'm also involved in rationalising indexing.
July 4, 2017 at 10:13 am
JaybeeSQL - Tuesday, July 4, 2017 9:50 AMTheSQLGuru - Tuesday, July 4, 2017 8:59 AM--3) Logs bloating, therefore backups maintained (which IS the case there)There is NO reason for log bloating with proper backup strategies (which perforce should be happening with log shipping being setup). Well, no RATIONAL reason anyway. I suppose something silly like index reorg followed by index rebuild, unnecessary updating of all rows in largest table, or some such.
Could you elaborate on the index part? I'm also involved in rationalising indexing.
I have lost count of the times over the years I have seen clients with a maintenance plan do what I said - reorg then rebuild all indexes. That is often followed by update stats, which actually results in WORSE stats for those indexes that were just REBUILT because they almost certainly go from 100% scan to sampled.
There are an UNLIMITED number of things clients do wrong/poorly/suboptimally with SQL Server and databases/applications based on it. I'm actually thankful though. The result is that there is an infinite amount of work out there for top tier consultants like myself. 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 4, 2017 at 1:06 pm
JaybeeSQL - Tuesday, July 4, 2017 9:50 AMCould you elaborate on the index part? I'm also involved in rationalising indexing.
Reorg followed by rebuild is redundant, unnecessary and a waste of time. So's rebuilding everything without checking if it needs it.
And with a proper backup strategy, with log backups scheduled to meet RPO, there's no reason why logs should be bloating.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply