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.יום ראשון 14 נובמבר 2010 00: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?
Sunil Agarwal, SQL Server PMיום שישי 19 נובמבר 2010 00: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
Microsoft Certified Master: SQL Server 2008
My book: Pro SQL Server 2008 Mirroringיום רביעי 01 דצמבר 2010 05: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.
Worksteam Manager for SQL Server at http://www.advaiya.comיום רביעי 01 דצמבר 2010 16:46