Sql 2008 R2 - How do System databases figure in high availability options and Plans

Question

Hi

   I have been doing some reading on the high availability options in sql 2008 R2.  I understand the big picture concepts but was wondering how the system databases figure into the plan.  For example, with log shipping the database needs to be in full recovery mode, so I can't log ship master model or msdb.  And can they be mirrored?

I understand that failover clustering is sharing databases so they would be the same in the regular and failover instance.

Maybe I am missing something, but is there some information that addresses this?

Thank You

Pam

Answer 1

Hi Pam,

Unfortunately failover clustering is the only technology that will allow you to synchronize/share system databases. All other technologies work on the user database level.

You obviously need to take care of synchronizing system related objects between the servers. Such as logins, jobs, etc. There are plenty of methods that can help you to do that - SSIS, for example, has the set of tasks for that.

---

Thank you!

My blog: http://aboutsqlserver.com

Answer 2

Hi there,

I was under the same impression when i was started. Fail over cluster shares the databases between nodes.

But what is shared here is the disk. The shared disk that contains the databases including system and user will be shared between nodes. if one node is down then fail over occurs to the other node.

If your shared disk corrupts everything is gone. Is that making sense?

Cheers

kumar

Answer 3

Correct. Using FCIs, the entire clustered instance is abstracted at that level (the instance) and therefore will failover as a unit, however as you quite rightly say the shared disk is potentially a single point of failure since this is the only place that the system and user data will be located on. Even in an asymetric disk configuration of a SQL FCI, the disk would still be a single point of failure in situations where there is memory scribbler style corruption OR user data error (i.e. table drop) -that situation is the same with Mirroring and Availability Groups.

To clarify Dmitri's statement -when he talks about synchronizing sytem related object between servers (in other words instance level components that live outside of the user database) he is referring to Database Mirroring and AlwaysOn Availability Groups since doing so in a clustered configuration is obviously not required. He wasn't suggesting that you need to do this with FCIs.

Pam to answer your question on mirroring or log shipping the system databases; no they cannot do that using these technologies, however you could automate syncronization with another instance through various means that script out and apply the changes (i.e. thru sql jobs, SSIS packages etc) -as Dmitri touched upon.

---

Regards,
Mark Broadbent.

Contact me through (twitter|blog|SQLCloud)

---

Please click "Propose As Answer" if a post solves your problem
or "Vote As Helpful" if a post has been useful to you
Watch my sessions at the PASS Summit 2012

Answer 4

Hello Pam,

You are right.

Master database is in simple recovery but can be changed to full/bulk but having said that you can't take tlog backup for master database. So, we are left to take regular full database backup.

Model database can be configured as needed but don't see a reason to have a DR/Logshipping/Mirroring for this as it is rarely changed.

Msdb database can be full & default is simple. It is recommended to be full recovery & have tlog backup made on regular basis.

Resource database is our special system datbase & we need to file backup of msssqlsystemresource files.

Tempdb is in simple & we don't backup it up or rather we CAN'T backup it.

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful, if a post was useful to help other user's find a solution quicker.