October 29, 2015 at 3:06 am
Hi Guys,
I am setting up mirroring on one of our live databases tonight. The database is quite big, around 300GB.
At the moment we are doing log backups once a day and full backups every night.
My question is, when I set up the mirror, will the log backups on my Principle DB affect the mirror in anyway?
October 29, 2015 at 3:08 am
Log backups once a day, really? Whats your RPO, 24 hours, if so no need to do the log backup your nightly full will meet that expectation.
But in answer to your question no. Log backups don't have an affect on mirroring.
October 29, 2015 at 4:00 am
What is RPO?
I am new in the company, so I did not set up the backups. Do you maybe have an article for me to find out how mirroring works, how the data physically gets replicated?
October 29, 2015 at 4:08 am
RPO = recovery point objective
Also known as how much data can the company afford to loose before it becomes a problem for the company.
Can you survive with 24 hours of data loss in the event of a failure? If so a nightly full backup.
Can you survive with 15 minutes of data loss, if so you need to backup the logs every 15 minutes.
There are plenty of articles on the web, a simple search engine search for DB Mirroring will bring many a result, also as you posted in 2012 forum, I take it you are aware mirroring is a depreciated feature and will be removed at some point in the near future in a later release. Recommendation is AlwaysOn Availability Groups.
October 30, 2015 at 1:14 am
I'm no expert, but my thought is that if you backup the log file only once a day it will be "big" - where "big" is one whole day's worth of transactions. That will include any index rebuilds and so on.
That in turn means that the log file will have to be "big" too, to accommodate a whole day's worth of transactions.
If you backup the log every, say, 5 minutes then you will have 288 log files each day. The total size will be the same as the single-day-log-file, plus a bit of overhead, but your Log File can then be reduced significantly.
If you have to restore your database SQL will pre-create the log file to the same size as the database that the backup was made from (even if the log file was "empty" at the time the backup was made). A massive log file can take some time to create, which could be an issue in disaster recovery if time is of the essence.
As already said, if you only backup the log file once a day then you risk losing 23 hours and 59 minutes of data changes. If the data can easily be recreated [e.g. it is imported from some other source, or manually keyed in from paper documents] that's probably not a problem, if your data changes are made by people on phones and reading emails etc. then it is probably impossible for the data to be recreated at all.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply