SQL 2008 r2 standard - Safety Full Only meaning

Question

Hi

I'm looking at purchasing sql server 2008 r2 Standard, on the spec inder Mirror it says - Yes (Single Threaded, Safety Full Only)

Whats does single thread mean, the principle sever has quad core, are there any implacations ?
More importantly, could someone explain 'Safety Full Only' pls. Will it work in async and sync mode ?

Thanks

Answer 1

In the context of Database Mirroring, this means that your Mirroring Session performs in a synchronized fashion, i.e. Transactions must commit at the Mirror database before they are able to be committed at the Principal database.

For further reading please refer to Transact-SQL Settings and Database Mirroring.

---

John Sansom | SQL Server DBA Blog | Twitter

Answer 2

Safety being set to FULL means that it will ONLY operate in SYNCHRONOUS MODE (whether or not you choose to have it automatically fail or manually fail is still your choice), SAFETY set to OFF will allow for Asynchronous mode which allows for data loss where synchronous does not.

As for the single threaded, I'm not entirely sure as there are threads for communications, redo, logging, etc. Someone will have to comment on that.

Answer 3

Hi thanks Sean, Just to be sure does this mean that ASYNCHRONOUS mode is not possible with standard edition ?

Answer 4

Hi,

As per this article http://msdn.microsoft.com/en-us/library/ms143761(v=sql.90).aspx

Database Mirroring with Safety FULL only is the only one supported by Standard Edition of SQL Server 2008 R2

HTH,

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.wordpress.com

Answer 5

Hi roastchicken，

 

Yes. The ASYNCHRONOUS is not supportted in the SQL Server Standard Edition. In addition, the link provied by Ashwin should be updated to the below one:

http://msdn.microsoft.com/en-us/library/cc645993.aspx

 

And for your original question, please refer the below thread with the similar issue as yours:
http://social.msdn.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/a5b58253-cf4a-4dd6-9472-c484347d39ab/

 

The single thread mentioned refers to that only one thread will be used by theREDO queue of database mirroring system in Std edition . But in Enterprise edition , it follows the formula Redo threads = FLOOR ((number of logical processors +3) / 4) . Synchronous mode - SAFETY is always set to FULL ( Transactions gets committed at both the partners simultaneously)

Bottom line - If that is a busy system with lot of transactions then go for Enterprise edition and use asynchronous mode of database mirroring.

 

Thanks,
Weilin Qiao

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.