Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 868
  • Last Modified:

Synchronize backups for different databases.

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
b_levitt
Asked:
b_levitt
  • 5
  • 4
1 Solution
 
b_levittAuthor Commented:
"Federated Servers" seems to be inline with what I'm talking about if that helps bring anybody to where I'm at.
0
 
b_levittAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
lcohanDatabase AnalystCommented:
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
 
b_levittAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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
 
b_levittAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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
 
b_levittAuthor Commented:
Thank you for your help.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now