• emile.milne (1/23/2013)


    Thanks, thats not ideal. I think most people will use the replica for SSRS. Big flaw in the design then.

    If I want to tweak the index for reports they need to be added to the LIVE db. Very poor oversight.

    Not an oversight. The wrong tool for the job. Availability groups is primarily there so that if/when a disaster hits your primary database, you can switch to any of the secondaries and continue working with minimal to no interruption.

    Availability groups, like mirroring (and log shipping), work by replaying the log records from the primary, not replaying the original. So an insert on the primary would result in log records that said 'insert this into allocation unit X and this into allocation unit Y'. The internal structure of the databases much be completely, 100% identical and the log records must match exactly (identical LSNs) or that replay of the log records will not be possible. That means that every operation done on the primary goes to the second and no other operations can be done on the secondary or the LSNs and internal structure will no longer match.

    For a secondary server that has a different schema and is not log-sequence identical with your primary, you're looking for one of the replication forms, transactional most likely. That allows for as many different replicas as you like, with different indexing structures, possibly even different subsets of the data, with rollup and aggregation tables, different stored procs and views, etc.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass