AlwaysOn Readable Mirror RRS feed

  • General discussion

  • 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.
    Sunday, November 14, 2010 12:58 AM

All replies

  • 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?





    Sunil Agarwal, SQL Server PM
    Friday, November 19, 2010 12:48 AM
  • 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
    Wednesday, December 1, 2010 5:02 AM
  • 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.
    Bill Ramos
    Worksteam Manager for SQL Server at http://www.advaiya.com


    Wednesday, December 1, 2010 4:46 PM
  • 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
    Sunday, January 23, 2011 7:59 AM
  • Question rearding SQL Server 2012 Edition support. Does Standard Edition for SQL Server 2012 support readable secondary.
    Tuesday, March 6, 2012 8:22 PM
  • SQL 2012 AlwaysOn is an Enterprise Edition only feature. 

    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP

    Monday, March 12, 2012 1:53 PM