Solved

Synchronize backups for different databases.

Posted on 2011-03-02
9
853 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
  • 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 39

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
 
LVL 39

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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 39

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 39

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to SQL Trace a SPECIFIC query 24 58
SqlAdvisor 2016 3 28
Max Consumption Rate (MCR) 3 34
Anyway to make these 2 SQL statements into one? 13 24
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
     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 …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now