3

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:

  1. 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.

  2. 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.

  3. 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?

Improve this question

3 Answers 3

Reset to default
2

I have implemented solution 2 multiple times in the past and it has proved extremely effective.

You are right in being concerned about the performance impact of replicating tables though. You will have to invest time in making sure replication isn't causing issues. Things to consider would be:

  1. Where you will host the distributor - the DML and DDL performed on the replicated tables are passed from the publisher (where the original tables are) to the distributor. The actual commands are effectively copied into a set of special tables in a separate "distribution" database. The distributor has to be powerful enough to deal with this load. This could be the same instance as the publishing database, but with high volume OLTP or OLAP you may want to scale that out to another machine.

  2. How often do you make schema changes to the tables you are wanting to replicate? Schema changes are possible, but they can be a nightmare in a replication topology. This will make schema changes something you will need to be much more careful with in future.

  3. How much data is inserted/updated/deleted in the replicated tables? Replication can keep with with very high loads, but your underlying systems will need to be able to deal with that (it is very much a system architecture question). As replication is effectively copying the DML and DDL into a second database you can basically see it as creating double the data (and I/O etc.) compared to the same DB without replication.

There are many more things to consider, but this should give you a quick intro to what needs considering if you went with solution 2.

Improve this answer
0

We currently use transactional replication to keep data in sync. Both servers have different indexes. Reporting server has fusion io drives that house the data files. It works well.

Improve this answer
0

You could also combine Mirroring with Snapshots.

The mirroring would keep the databases in sync, and then you create snapshots off the secondary database.

http://technet.microsoft.com/en-us/library/ms175511.aspx

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.