In our current environment we have about 40 databases on one instance all being log shipped and configured as Standby/Read Only. About 90% of those are for reporting purposes and others for read only. The number of failures and maintenance plus user complain about not being able to access the databases when logs are being restored has forced us to rethink about this feature and we are strongly thinking of moving to Replication.

My main concern is can the Replication feature handle this many databases if we put our distributor on a separate instance? Shall we have one distributor or more than one? The network is handling the log shipping very well and I assume network bandwidth will not be an issue.

If replication is not a good option then what other option would be better that requires less maintenance or more importantly less failures.

We are on Amazon Cloud Infrastructure.

any input will be appreciated.

  • 1
    What version and edition of SQL Server? – HandyD Jul 25 '19 at 23:21
  • @HandyD, the server on which we have Standby/ReadOnly is Microsoft SQL Server 2017 (RTM-CU4) (KB4056498) - 14.0.3022.28 (X64) Feb 9 2018 19:39:09 Copyright (C) 2017 Microsoft Corporation Web Edition (64-bit) on Windows Server 2012 Standard 6.2 <X64> (Build 9200: ) (Hypervisor) – Ali Jul 26 '19 at 14:27
  • 1
    Have you considered delaying the RESTORE LOG ... WITH STANDBY statements until after hours/off hours/defined times? If only done during off hours, the clients would be able to access data during business hours without interruption, though it would come at the cost of the data being up to a day old. Alternatively, you could also do a batch of restores at noon and/or other set times (as agreed to with the business) so that the interruptions are known and planned for. – John Eisbrener Jul 26 '19 at 15:21
  • Have you thought about just doing ETL and running it on a set schedule? Detect New/Changed records in source is left as an exercise for the OP, but would work and neatly sidestep replication and log shipping. – Jonathan Fite Jul 27 '19 at 1:01
  • @JohnEisbrener Yes for some large databases we have set the time such a way that it only restores nightly. However, most databases can have maximum 1 hour data delay and some requires up to date data. – Ali Jul 29 '19 at 16:45

You could consider Availability Groups for your databases. Since you are using them for reporting/read-only already then you already have licenses for your secondary instances.

This will allow better access to your secondaries as it does not need to take the database offline for updates - your data will also be more up to date as it is a constant flow of transaction log updates rather than batched backups.

  • 4
    They need the enterprise edition to have a readable replica. – Denis Rubashkin Jul 26 '19 at 6:10
  • @Martin, I forgot to mention in my question that we are already using Basic Availability Group where you are only charged for licensing when you fail-over a database to secondary and will only be charged for the duration of the fail-over. When the database is failed over back then the passive node does not occur any charges. Second, the 40 Standby/Read only sits on one server but the source (Prod) databases sits on many different servers in the whole environment. – Ali Jul 26 '19 at 14:20
  • 1
    You could have an AG configured such that server A, B, C all have their readable secondary set to Server X. Server X would in theory need to be powerful enough to run all of them, but since it's your reporting server in any case.... Still requires enterprise though. – Jonathan Fite Jul 27 '19 at 0:59

I have not used Amazon Cloud for my servers, but assuming the replication works , Replication will be a good solution for your requirements. Just need to be sure that your tables have a primary key.

In the case of replication since you can choose which tables to replicate, it could help for your performance (log shipping the whole DB vs replication just the required tables), and since there's no "downtime" while transactions are delivering it will work ok for your users.

The best practice is to have distribution DB on a different server,so it will not impact the Productive environment, since you have no performance issues currently, my suggestion will be to put it along with your subscription DBs.

  • thanks for your comments and suggestion. The only concern I have is whether replication will be able to handle 40dbs on one server even if I put my Distributor on a separate server? Yes there will be less load when we move to replications as there will be lesser tables replicating instead of the whole database. – Ali Jul 26 '19 at 18:47

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