November 22, 2019 at 12:00 am
Comments posted to this topic are about the item DR Priority
November 22, 2019 at 5:32 am
Wonderful thought Steve.
I guess if Organization is not serious about DR or they feel they can live with the downtime caused due to unavailability of DR then DBA's can't do much even if they intent to do so. Infrastructure is needed and has to be funded by the organization. DBA's are not going to fund it from their pocket.
But yes, proper backup strategy can be planned to be prepared to recover the data in case of any disaster. But even for that Infra is needed such as proper Network Bandwidth, Disk Space, Tapes etc. As a DBA we can make sure to have proper Backup strategy and periodic sanity of the backup files to make ourselves in the position to recover from any disaster but as we all know it needs more downtime.
Recently, in one of the Job Interview, I was asked a question to write down the query to fetch the latest database backup chain and backup and compressed size of the backups. I think we can schedule such kind of alert or make it part of periodic checklist.
I must stress DR should be there. If organization do not intent to spend much on DR then they can have the cold DR which will be made up only when there is disaster. It can be done using Logshipping, AlwaysOn on standard edition without paying additional cost on the SQL license.
I've recently found Microsoft Azure offers services for such kind of needs when one have pay only if there is actual use of the instance and not for replicating the data. Although, there is comparatively slight cost involved in setting up the instance. Resource utilization can be configured dynamically or it can be low on normal situation and at the time of disaster resources can be increased in seconds.
November 22, 2019 at 12:44 pm
I get asked questions about this fairly often in our agency, and it boils down to the amount of data, as measured in minutes or hours, the stakeholder may be willing to lose. Fortunately, much of the data we house is performance-related data, so losses are generally more tolerable than in other situations. That plus the fact that we try to keep the cost of our projects to a minimum means that we take a somewhat different approach than other institutions.
In all honesty I don't feel like any database is 100% protected 100% of the time, even in DR situations, because no matter what is implemented to combat data loss, the simple fact is that Murphy and Technology will always win. For that reason, I ask the stakeholders for a non-zero number of minutes/hours of data that they're willing to lose. In a good number of cases, areas in our agency can temporarily lose up to 24 hours' worth of data because there are other sources from which the data can be replaced. In cases when the answer is fifteen minutes or fewer we use Always-On Availability Groups, otherwise we implement a mix of transaction log backups and full backups (which are done every evening/morning), and will restore the database(s) onto another SQL instance if worse comes to worse. We also use DNS aliases for SQL instances so that if we need to use another SQL instance we can change the alias entry. Thanks for allowing us to participate in this conversation!
Experience is what you get when you don't get what you want!
November 22, 2019 at 3:36 pm
DR isn't a thing, as in, I have or don't have DR for my company.
We all have to make choices. We all have to work within constraints. I may have 2 FCIs, geographically dispersed with an AG between them for my important order system, but there are other databases in my org that don't have AGs or don't have FCIs. I often have to make choices in how to handle things, or how to get other groups to think about this.
I like @Columbia's answer of data loss. That is often a good way to decide how much budget to put into things, but it's also that I have to decide what portion of my time and effort to spend trying to do restore checks, DBCCs, etc. on other systems.
November 23, 2019 at 1:25 am
I guess I'm different... perhaps, a lot different. AG is nice and all but, to me, AG has NOTHING to do with DR and vice versa. To me, DR is what happens when all your AG stuff has failed regardless of what and how many forms it may be simultaneously present in your systems and sites you have systems in. To me, DR is when your computers are unable to help you conduct business. And, don't forget, the computers and the data that lives on their disk systems mean absolutely nothing if you can't use them for their intended purpose. That means if you or your customers can't get to them, it's also a disaster.
Heh... I know a whole lot of people are getting ready to argue with me especially when I say having stuff in the cloud (although helpful), is not a adequate DR plan. Before you do, remember the not so distant past when no one could log into O-365... and it happened not once but twice and in a fairly short time span. People say that wasn't a disaster because "it didn't last all that long" but it absolutely was a disaster until people were able to get back in and get back to normal business. Some had all their junk in a system they could no longer log into for hours.
Things like AG are nice because they can help avert what would normally be a disaster but it's NOT DR and if AG is the only "plan" you have for DR, then you're going to find yourself in deep Kimchee sometime, possibly in the not so distant future.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 25, 2019 at 1:07 pm
Hey Jeff:
Actually, no, you're absolutely correct. I have no argument with what you said, because whenever you read anything about DR the main focus is always on making sure you have backups...and not just backups, but backups that actually work when you restore them. At my agency the DR plan starts with something like "there's been a tornado that wiped our datacenter off the map. Now what?" Thanks for the reminder of what true DR really means!
Experience is what you get when you don't get what you want!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply