February 25, 2014 at 11:09 am
I just received an email saying that "The backup was running at that time, which could have froze things up for a moment".
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
February 25, 2014 at 11:17 am
Yeah, though abt backups. But again felt that if it was backups then that should have been a recurring daily issue. Not a one time one ..
That's where SQL Error Logs would come it to picture which captures this specific info ..
--
SQLBuddy
February 25, 2014 at 12:31 pm
sqlbuddy123 (2/25/2014)
Yeah, though abt backups. But again felt that if it was backups then that should have been a recurring daily issue. Not a one time one ..That's where SQL Error Logs would come it to picture which captures this specific info ..
Is there a way we can avoid this kind of problem in the future. I mean we have to take the backup regardless but if users are having issues accessing the application at that time, I don't think the management will approve of this.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
February 25, 2014 at 12:43 pm
Yeah, Backups need to be scheduled so that they don't interfere with the business processes. Schedule it during the night or early hours when you don't have users. That way users don't get timeouts\blocked.
Also in SQL Server 2012 Always ON, you can even take backups from the secondary servers. This way you offload the backups and reduce the impact on the primary production server.
--
SQLBuddy
February 26, 2014 at 4:51 am
New Born DBA (2/25/2014)
I just received an email saying that "The backup was running at that time, which could have froze things up for a moment".
If they're talking about the SQL Server backup, it's an online operation and wouldn't cause that. If they're talking about the server backup, I don't see how (well, i/o too much for the network card, possibly, but then again this wouldn't happen on a one off basis as far as I can see).
However, if the backup was the server backup, rather than the SQL Server one, which was running at this time, and it wasn't affected then this tends to imple the issue's with te application server or connecting to the SQL Server server from the app server.
Are these physical or virtual by the way?
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
February 26, 2014 at 6:45 am
Virtual and backups are run by 3rd party tool (Veeam).
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
February 26, 2014 at 8:30 am
May be you can schedule the backups using that Third Party tool at a convenient time so that they don't interfere with your business activity.
--
SQLBuddy
February 26, 2014 at 8:42 am
We have a client and they are 24/7 shop. We take our backup around 1 am central time (US/Canada) but we also have users accessing the DB from UK and India so 1 am central time would be normal business hours in India. I was under the impression that when backup runs, DB is available to users. There has to be a way that when backups are running, its not preventing users to access the DB.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
February 26, 2014 at 9:30 am
SQL Server Backups doesn't directly cause locking\blocking on user objects. But they do put a heavy overload on the I/O system so that it may appear as blocking. However users may be experiencing I/O Waits and may get timed out. To prevent this it's recommended to do backups off hours.
Third party backup tools usually use VSS service to do backups. VSS backups freeze database I/O and it will resume the I/O once the backups are done. This may cause application timeouts and may appear as blocking or database down.
1 AM is equivalent to 6 AM (UK) and 11:30 AM (India). How about moving backups to a different time between 9 - 11 PM. 9 PM is equivalent to 2 AM (UK) and 7:30 AM (India). This way we can ensure that you have less user traffic.
But the backups have to be scheduled at times when they don't interfere with any business processes\jobs. Best thing is to check with your client for the best time to schedule the backups.
--
SQLBuddy
February 26, 2014 at 2:53 pm
New Born DBA (2/26/2014)
We have a client and they are 24/7 shop. We take our backup around 1 am central time (US/Canada) but we also have users accessing the DB from UK and India so 1 am central time would be normal business hours in India. I was under the impression that when backup runs, DB is available to users. There has to be a way that when backups are running, its not preventing users to access the DB.
Ok, I'd be really concerned if your recovery strategy is based around a daily veem backup and nothing else. Is that all you have? Do you want to stand up in front of the business and say, "hell, yeah, we've lost a full working day's worth of data - it that a problem?". Could be career limiting.
Veem can cause issues with connectivity afaik (from a quick google). I've never seen it myself because I simply would not use that as a backup mechanism. (Yet).
Is there any reason you're using this tool rather than a native SQL Server full and log backup process? Other than the ServerTeam saying "We've seen a brouchure and ... ". Sorry if I appear to be going off at a tangent but, veem may be flooding I/o causing occasional connectivity issues. Veem may even be suffering connectivity issues itself which may be impacting on the backup process, are you certain your backups are restorable?
Get Server team to check the logs, try restoring the veem backup from the day when this occurred to a test VM - can you recover from this backup?
If the server team kick up a fuss, you need to kick it up the ladder as far as you can. Simple issue, "We are vulnerable to x hours of data loss, on the assumption that the backups work and we know how to restore this has last been tested on ... ". Hope I'm wring - but my guess would be never from what I've seen walking into a number of shops.
Hopefully I'm just being an arse - but it's probably worth checking.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply