We want to handle schema changes

Transactional Replication would be hard to maintain. If you do frequent schema changes, then you have to drop replication and recreate it, which is time consuming and on the top of it, you have to maintain distribution database as well. Network latency also plays a big role if you are replicating your entire database.

For you scenario, I would recommend :

• If not on enterprise edition, you can use any of below :

• Backup and restore with COMPRESSION. SQL Server native compression is very efficient which will reduce the backup size.

• Since data can be stale up-to 24 hrs, another option would be to use logshipping. You just have to delay the restore of the log backups on the secondary server. So when you want to update the data on the reporting server, you can kickoff the job manually or it will get kicked off with the schedule. This way, you just have to restore the T-logs on the secondary server, when you want to refresh the data in the reporting (secondary) server.

  So when setting up Logshipping, configure the secondary database in STANDBY mode and make sure you select Disconnect users in the database when restoring backups.

• If on Enterprise edition, then AlwaysON with readable secondary. This will offload the reporting work from your primary server to the replica.

We want to handle schema changes

Transactional Replication would be hard to maintain. If you do frequent schema changes, then you have to drop replication and recreate it, which is time consuming and on the top of it, you have to maintain distribution database as well. Network latency also plays a big role if you are replicating your entire database.

For you scenario, I would recommend :

• If not on enterprise edition, you can use any of below :

• Backup and restore with COMPRESSION. SQL Server native compression is very efficient which will reduce the backup size.

• Since data can be stale up-to 24 hrs, another option would be to use logshipping. You just have to delay the restore of the log backups on the secondary server. So when you want to update the data on the reporting server, you can kickoff the job manually or it will get kicked off with the schedule. This way, you just have to restore the T-logs on the secondary server, when you want to refresh the data in the reporting (secondary) server.

  So when setting up Logshipping, configure the secondary database in STANDBY mode and make sure you select Disconnect users in the database when restoring backups.

• If on Enterprise edition, then AlwaysON with readable secondary. This will offload the reporting work from your primary server to the replica.

Note: For completeness of this answer, even though Database Mirroring is announced as Deprecated, it is still supported in SQL Server 2012. You have to be careful, as the database snapshot name will be different from the original database name, so if your app relies on the database name, it will need modification. Also, as snapshot works on "COPY ON WRITE" method, it can be significant overhead if there are multiple snapshots. Also, it will randomizes IO during query processing.

We want to handle schema changes

Transactional Replication would be hard to maintain. If you do frequent schema changes, then you have to drop replication and recreate it, which is time consuming and on the top of it, you have to maintain distribution database as well. Network latency also plays a big role if you are replicating your entire database.

For you scenario, I would recommend :

1. If not on enterprise edition, Backup and restore with COMPRESSION. SQL Server native compression is very efficient which will reduce the backup size.

2. If on Enterprise edition, then AlwaysON with readable secondary. This will offload the reporting work from your primary server to the replica.

We want to handle schema changes

Transactional Replication would be hard to maintain. If you do frequent schema changes, then you have to drop replication and recreate it, which is time consuming and on the top of it, you have to maintain distribution database as well. Network latency also plays a big role if you are replicating your entire database.

For you scenario, I would recommend :

• If not on enterprise edition, you can use any of below :

• Backup and restore with COMPRESSION. SQL Server native compression is very efficient which will reduce the backup size.

• Since data can be stale up-to 24 hrs, another option would be to use logshipping. You just have to delay the restore of the log backups on the secondary server. So when you want to update the data on the reporting server, you can kickoff the job manually or it will get kicked off with the schedule. This way, you just have to restore the T-logs on the secondary server, when you want to refresh the data in the reporting (secondary) server.

  So when setting up Logshipping, configure the secondary database in STANDBY mode and make sure you select Disconnect users in the database when restoring backups.

• If on Enterprise edition, then AlwaysON with readable secondary. This will offload the reporting work from your primary server to the replica.