locked
AlwaysOn Readable Mirror

    일반 토론

  • I am curious if in SQL Denali when setup a secondary availability group as readable, if all databases in that group become readable or not.  I would be good to have granular control and say "only database X of availability group" is readable on this secondary".  You might want only 1 database in a group to be accessible.  It should not be an all or nothing affair.
    2010년 11월 14일 일요일 오전 12:58

모든 응답

  • The design center of availability group by definition contains the related databases together for the application. In a likely customer scenario, you would query or do reporting related databases together. 

    I am curious to know why you consider read access to a subset of databases, including allowing only 1, a useful case?

     

    thakns

    Sunil

     


    Sunil Agarwal, SQL Server PM
    2010년 11월 19일 금요일 오전 12:48
  • You already have that layer of granularity via permissions granting. Permissions will still apply, so if you want, for example, certain users to be able to run ad hoc queries against 1 of the read-only databases but not the others, only grant them access to the 1 database and not the other.

    My blog: SQL Soldier
    Twitter: @SQLSoldier
    Microsoft Certified Master: SQL Server 2008
    My book: Pro SQL Server 2008 Mirroring
    2010년 12월 1일 수요일 오전 5:02
  • When you create an availability group, you specify that the secondary replica allows read-only or allow all connections for the database engine instance. This means all the databases in the availability group will have the ability to accept read-only connections. Unfortunately, you can't define a different set of permissions on the secondart database replicas because they get the permissions from the primary database replica. 

    The way around this in CTP1 is to create multiple availability groups based on having a read-only secondary and another that doesn't allow connections to the secondary. You would then add the databases to the apppropriate availability group based on situation.

    In a future CTP, availability groups will support up to four secondaries where at most, two can be sync secondaries. For the sync secondaries, you'll want to not allow read connections so that potential queries don't delay HA transactions. You can then setup your async secondaries as readable since they won't block commits on the primary database replica.

    For now with CTP1, you'll need to take the dual Availability group approach.
    Cheers,
    Bill Ramos
    Worksteam Manager for SQL Server at http://www.advaiya.com

     

    2010년 12월 1일 수요일 오후 4:46
  • Bill, Dual group will not do complete group failover if only one db fail from 1st group.

    Access control would be best managed by providing access at source db. 

    Though its interesting question, it would be good to have this feature...

     

     


    Prakash Heda
    2011년 1월 23일 일요일 오전 7:59
  • Question rearding SQL Server 2012 Edition support. Does Standard Edition for SQL Server 2012 support readable secondary.
    2012년 3월 6일 화요일 오후 8:22
  • SQL 2012 AlwaysOn is an Enterprise Edition only feature. 

    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP

    2012년 3월 12일 월요일 오후 1:53