July 14, 2011 at 3:01 am
Jayanth_Kurup (7/14/2011)
A Good DR plan has Clustering or DB mirroring to recover from OS level and network level failures , RAID and SAN to recovers from disk and data corruption and Log shipping , backups and recovery etc to bring databases online in a DR , All of them used correctly is what makes a good DR plan.
Exactly.
but you can't have mirroring *and* log shipping
(edit) YES you can http://msdn.microsoft.com/en-us/library/ms187016%28v=SQL.100%29.aspx
I haven't implemented clustering or replication - mainly because I haven't ever seen a server go down for more than 10 minutes in the last decade - so I feel these technologies introduce more problems than they solve.
What I have seen on ms clusters is people running around panicking all the time because they don't know which node is primary, when or why the failover occurred and what they should do to get fully online again ... that never happens to me - I either fix the primary or semi-manually bring the secondary online - and I haven't had to do either of those things for over 6 years.
So log shipping is the way for me - easy, predictable and useful (as long as you build it yourself and avoid the ms stuff)
July 14, 2011 at 7:12 am
hopefully we can agree that transaction marks are required for successful restores of related databases
the problem is knowing which marks are in which log file
the solution I came up with is to copy records from msdb..logmarkhistory into one of the related databases
msdb..logmarkhistory is appended after each successful commit of a marked transaction
using the [description] column in logmarkhistory you can group all marks related to a given set of databases
now, if needs be, I can restore that database and then read the mark information - then use that to perform a multidatabase restore
this isn't a perfect solution as logmarkhistory is only appended when the engine feels like it ...
it is also kind of pointless as there is typically 1 mark per minute ...
Still interested to hear about other peoples' solutions to this problem
July 15, 2011 at 5:34 pm
It is my thinking that you have to run the BEGIN TRANSACTION ... WITH MARK against all the DB's in the set - So assume it has to be done on the instance in as close to parallel as possible...
We're looking at it here to deal with this TFS caution:
Caution
You must restore all databases to the same point in time, or the databases will be out of synchronization. The following procedures assume that you are using marked transactions to help ensure synchronization of the databases that Team Foundation Server uses. For more information, see Back Up Team Foundation Server. If your deployment uses SharePoint Products, you should follow the guidance for the version of that product in your deployment. For more information, see Backup and Recovery (SharePoint Server 2010), Protecting and restoring a farm (Office SharePoint Server 2007), or Protecting and restoring a farm (Windows SharePoint Services 3.0).
So I too am curious on how y'all work this in to the daily operation...
:unsure:
[font="Verdana"]-- Wally
-- Reluctant DBA[/font]
July 16, 2011 at 1:22 am
this is working for me:
begin tran '20110716_1030' with mark 'NameOfDatabaseSet'
create table db01.dbo.SomeRubbish ( [field] int ); drop table db01.dbo.SomeRubbish;
create table db02.dbo.SomeRubbish ( [field] int ); drop table db02.dbo.SomeRubbish;
...
create table db11.dbo.SomeRubbish ( [field] int ); drop table db11.dbo.SomeRubbish;
commit tran
as long as each db tran log has something committed - the marks will work
July 16, 2011 at 9:22 am
I came to pretty much the same conclusion late last night - Though I'll probably throw a GETDATE() in the BEGINTRAN call for the mark set so I can automate the run.
Thanks for the input. 🙂
[font="Verdana"]-- Wally
-- Reluctant DBA[/font]
July 16, 2011 at 2:32 pm
WaIIy (7/16/2011)
I came to pretty much the same conclusion late last night - Though I'll probably throw a GETDATE() in the BEGINTRAN call for the mark set so I can automate the run.Thanks for the input. 🙂
I would use: getutcdate() otherwise you will get overlap on DST change over
also consider using a control table for the mark - to ensure you never make a mark before the last mark (due to clock changes etc.)
because that can cause total chaos
You will need to store the mark as part of the transaction - there is no way to work out the marks from the tlogs etc.
I am using a small database just for this - in an emergency I can bring this online and see all the available marks for recovery
it is part of the database set and log shipped etc. just like production dbs
Otherwise you could spend weeks trying to guess your marks ...
July 17, 2011 at 3:03 pm
DataDog (7/16/2011)
WaIIy (7/16/2011)
I came to pretty much the same conclusion late last night - Though I'll probably throw a GETDATE() in the BEGINTRAN call for the mark set so I can automate the run.Thanks for the input. 🙂
I would use: getutcdate() otherwise you will get overlap on DST change over
also consider using a control table for the mark - to ensure you never make a mark before the last mark (due to clock changes etc.)
because that can cause total chaos
You will need to store the mark as part of the transaction - there is no way to work out the marks from the tlogs etc.
I am using a small database just for this - in an emergency I can bring this online and see all the available marks for recovery
it is part of the database set and log shipped etc. just like production dbs
Otherwise you could spend weeks trying to guess your marks ...
Great input - We'll get this all figured out yet! 😀
[font="Verdana"]-- Wally
-- Reluctant DBA[/font]
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply