SQL Server Performance

Change transaction safety mode

Discussion in 'SQL Server 2005 Database Mirroring' started by WingSzeto, Apr 12, 2007.

  1. WingSzeto Member

    I am using SQL 2K5 standard with sp2 and am testing db mirroring (no withness). Because our two servers are in a workgrouop, I set up the database mirroring using TRANSACT SQL statements not the management studio tool. It is working fine that way.

    By default, transaction safety is set to FULL which is sychnronous. The options are grayed out in the db's mirroring properties window. How do I change it to OFF which is asynchronous. The BOL shows me that I can issue "ALTER DATABASE <database> SET PARTNER SAFETY OFF" on the principal server but when I issue the statement, I got an error "This SQL Server edition does not allow changing the safety level. ALTER DATABASE command failed.".

    Please advice.

    Wingman
  2. MohammedU New Member

    Only in EE of sql server 2005 Asychronous mirroring is available...
    SE option synchronous onl you can't change it...

    See the features document...
    SQL Server 2005 Features Comparison
    http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  3. satya Moderator

    http://sqlserver-qa.net/blogs/bc/archive/2007/03/10/effective-usage-of-sql-server-2005-database-mirroring-by-ssqa-in-sql-server-open-world.aspx fyi.l

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page