One of my clients asked me to find a solution that can sync the 2 databases in near real time. Basically, they try to pull the data through linked server from server A through server B, but since server A goes down they can't pull the data as the database is not available, so they need some near real time data available to be queried even if the source is not available for sometime. Also, something that I forgot to add the database should always be on server A.
- SQL version - SQL 2012 SP3 (both server A and server B)
- RAM - 32 GB
So essentially, Server A's database is the production DB, and Server B's database is used as a "read-only" copy - no changes go through server B. If Server A goes down, then Server B is up-to-date, or almost so. However, Server B is not intended to become the live production DB at that point - it's just available to reference the data until Server A comes back up