Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Synchronize backups for different databases.

Posted on 2011-03-02
9
Medium Priority
?
866 Views
Last Modified: 2012-05-11
The frequent argument for Oracle over Sql Server is scale-out ability.  With Sql Server's Active-Passive clustering model, the only real way to scale is up with a box with more CPUs, where Oracle's RAC is basically active-active clustering.  However, Sql Server's concept of a database is more like that of a super-schema when compared to Oracle's server=database model, giving an additional point of vertical decomposition.  In otherwords it would be pretty easy to move a database to a new server should it outgrow it's current shared server.  The only problem with that is that if you have a distributed transaction writing to two different databases, how would you resync these databases if you needed to restore them from backup (assuming that it's impossible to have two backups start and end at exactly the same time)?
0
Comment
Question by:b_levitt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 11

Author Comment

by:b_levitt
ID: 35017380
"Federated Servers" seems to be inline with what I'm talking about if that helps bring anybody to where I'm at.
0
 
LVL 11

Author Comment

by:b_levitt
ID: 35017453
Ok I don't understand this at all:

http://msdn.microsoft.com/en-us/library/ms187098.aspx

"SQL Server does not require that you coordinate backups across member servers. Backups can be independently taken from each database, without regard for the state of the other member databases. Because the backups do not have to be synchronized, there is no processing overhead for synchronization and no blockage of running tasks."
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35018575
You should try to look at PITR - or point in time recovery by using T-Log backups

http://msdn.microsoft.com/en-us/library/ms190982.aspx
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 40

Expert Comment

by:lcohan
ID: 35018887
Maybe have a look at different solutions than Active/Active if federated with clustered is too much to achieve:

http://social.msdn.microsoft.com/Forums/en/sqldatabasemirroring/thread/b6787cbc-e165-4730-9fba-7898af1716a6
0
 
LVL 11

Author Comment

by:b_levitt
ID: 35019187
I understand transaction log backups.  What I didn't understand about the quote was "backups do not need to be synchronized"

If database A is backed up (full or transaction log) at time T and database B is backed up at T+n, then any data written during n would be invalid on database B, since corresponding records on A would not have been restored.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35019362
Just becuase they say the RESTORE must be syncronised:

"...you must coordinate the recovery of the member databases to make sure that they remain synchronized correctly."

"The most important aspect of recovering a set of member databases is the same as recovering any other database: Plan and test the recovery procedures before you put the databases into production. You must set up processes to restore all the databases to the same logical point in time. SQL Server includes features to support the recovery of all member databases to the same point in time."

0
 
LVL 11

Author Comment

by:b_levitt
ID: 35019810
Well now I just feel silly for missing that :).

It doesn't elaborate much on the "features".   I did find this:
http://technet.microsoft.com/en-us/library/ms187014.aspx

Are there other features?
0
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 35020038
No problem - "they" don't say/provide much for large scale implementations and scalability for SQL comparing to ORACLE for instance. I would think twice though to use the MARK vs. PITR because "You can recover related databases only to a marked transaction, not to a specific point in time." and MARKing can be costly. I never used that method but successfully recovered to a speciffic point in time very close to the point of failure. My personal feeling is that you need to carefully evaluate both methods MARK and PITR with business to see which is the min data loss you can afford (of course idealy is 0zero) and then decide wich method is less costly/performance impact and better to implement.
0
 
LVL 11

Author Closing Comment

by:b_levitt
ID: 35035996
Thank you for your help.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question