Solved

Synchronize backups for different databases.

Posted on 2011-03-02
9
861 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

734 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