Solved

Synchronize backups for different databases.

Posted on 2011-03-02
9
857 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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 500 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

730 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