none
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


    • Moved by Tom Phillips Tuesday, June 28, 2011 4:39 PM Replication question (From:SQL Server Database Engine)
    Tuesday, June 28, 2011 2:57 PM

Answers

  • 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.
    Thursday, June 30, 2011 6:18 AM

All replies

  • 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

    Tuesday, June 28, 2011 3:12 PM
  • 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.

    Tuesday, June 28, 2011 3:15 PM
  • Hi thanks Sean, Just to be sure does this mean that ASYNCHRONOUS mode is not possible with standard edition ?
    Wednesday, June 29, 2011 7:23 AM
  • 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
    Thursday, June 30, 2011 2:35 AM
  • 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.
    Thursday, June 30, 2011 6:18 AM