February 22, 2010 at 2:05 pm
I am was blessed with being choosen to admintrater our sql 2005 databases here at my company. I am sorta new to being a DBA and have a question. We have an email archive databases that are changing constantly. They are all backed up nightly with transaction logs being backed up once a night as well.
We had an issue with the SQL server this past weekend were several of the databases became corrupt because of a hardware failure to a drive. I had to go back a couple days to a good backup in order to restore working databases. the databases and the indexes of the archiving server became out of synch. What could I have done to restore the difference between the databases and the indexes. Would doing transaction log backups on a more constant basis eased my issue? Does anyone else have to deal with a similar situation? Or could give me any advice on how to handle this situation so we do not run into again in the future.
Regards,
Adrian
February 22, 2010 at 2:12 pm
well, that should not have happened, unless by "indexes" you mean something other than SQL Server indexes.
Please tell us the exact steps that you followed to restore your database.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 22, 2010 at 2:27 pm
RBarryYoung (2/22/2010)
well, that should not have happened, unless by "indexes" you mean something other than SQL Server indexes.Please tell us the exact steps that you followed to restore your database.
Sorry for the misunderstanding. Yes I mean the indexes of the the Email archiving server. Essentially, when ever an email is archived a pointer is recorded in the database saying when it was archived and location of the email, attachements and so forth. Emails were still coming in and being archived when the sql backend was having issues. when the databases were restored they were not in synch with the indexes of the email archiving server. we are having one helluva time trying to get them back in synch.
So I am wondering what I could have done differently on the sql side to prevent this in the future.
Regards,
Adrian
February 22, 2010 at 2:38 pm
The best you'll get on this is doing log backups on a regular basis, like every 15 minutes, or maybe even more frequently.
Then, assuming your logs are on a different I/O system than your data files, if the data files get corrupted, you run a final log backup, and then restore to the point in time of the failure.
Of course, that requires some downtime, because of the asynchronous nature of your e-mail indexes. If they're still getting populated while the database is being recovered, you'll have an error because of that.
If you take the e-mail system offline, or at least the indexing system, while you get the database back up, you might be able to achieve a full sync-up from that.
The keys are frequent log backups, logs on different I/O than data, and knowing how to do a point-in-time restore.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 22, 2010 at 2:47 pm
T&HDBNEWADMIN (2/22/2010)
RBarryYoung (2/22/2010)
well, that should not have happened, unless by "indexes" you mean something other than SQL Server indexes.Please tell us the exact steps that you followed to restore your database.
Sorry for the misunderstanding. Yes I mean the indexes of the the Email archiving server. Essentially, when ever an email is archived a pointer is recorded in the database saying when it was archived and location of the email, attachements and so forth. Emails were still coming in and being archived when the sql backend was having issues. when the databases were restored they were not in synch with the indexes of the email archiving server. we are having one helluva time trying to get them back in synch.
This is very likely an application design problem, specifically, either not using correct transaction management on the above, or else a failure to enable and use distributed transaction management and recovery correctly. The latter might be fixable administratively, but the prior almost certainly would require design/code changes.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 22, 2010 at 3:53 pm
First thing your server team should have disabled pointer (or whatever) once your backend database server went down, that should be automatic. I have no idea how this application works, In future as others said run t logs every 15 or 30 minutes. The problem is you have no idea when pointer marks it in the database, is it doing in intervals? If not, even though you have t logs you cannot achieve 100 sync with this application unless and until you set up an automated process, which stops to record if database is not online.
EnjoY!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply