We have 5 tables in a database on Server A that we would like to replicate to Server B for reporting purposes. We would like to create different indexes on the destination copy so we can get better reporting performance. We considered the following options:
Always On Availability group (Feature of SQL Server 2012): Cannot use this because it only allows to create a Read Only copy of the source database i.e. we cannot create different set of indexes on each server.
Transactional Replication: This looks good for implementing what we are looking to do but since our tables are going to be updated very frequently the performance impact on server is expected to be high and we are looking to avoid any performance degradation.
Snapshot each night: This could have been OK too since we don't need to keep the two databases in sync at all times. But this will cause to recreate the tables every night which means we will have to recreate the indexes as well each night. No guarantee that it will all finish in one night.
The other customized solution that we came up with was a two step process. First we replicate the full database on Server B using Always ON method. Then we will create another database on Server B which we will replicate to using the first destination database by Transactional Replication method. I understand that this is longer method to reach the same destination but this allows us to avoid the performance impact on Server A. We are not so concerned with the performance of Server B. Does this solution sound good or are we overlooking any possible issues with this solution?