Best way to create a second instance current and available 24x7.

Question

Hi everyone!

What would be the best solution to make a production database available to another SQL Server instance (on a different server) in read-only mode, without interruptions? I mean, current and available 24x7.

Mirror, replication, etc.

Hope I was clear enough.

Answer 1

Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64) Jul 12 2019 17:43:08 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor).

Since you have standard SQL Server 2017 Full fledged AG is rules out and if you configure Basic Availability Groups (BAG) you would not get readable secondary. So you are left with 2 options

1. Transaction log shipping with secondary server is Standby/Read only mode. Here whenever restore happens from primary the running connections would be terminated to take exclusive lock for restore
   2. Transactional replication. Will have your copy of database but has some inherent limitations. See books online.

Answer 2

As you might imagine there are more than one option, and which is the best depends on your needs, your SQL version and edition etc.

The most popular solution is probably to set up an availability group with a read-only secondary. However, this requires Enterprise Edition, and if you are on SQL 2016 or earlier, it also requires a Windows failover cluster. On SQL 2017 you can set up a clusterless AG which is kind of intended for the scenario you may have. That is, you want a read-only copy, but you don't need HA. (A clusterless AG can be used for distaster-recovery purposes, but it is not considered to be high-availability solution.)

One drawback with a readonly secondary is that indexing has to be the same as on the primary. For a reporting workload you may want to have extra indexes. In this situation, replication is a better option, but it is also a more complex solution.

You should also beware of that adding a readable secondary, may also have some impact on the primary, as you will activate the version store. (Which is already active if you have enabled snapshot isolation in any form.)

Answer 3

In addition to what Erland Sommarskog already mentioned, you need to clearly define what "without interruption" means for the business. Always On Availability Groups as well as other high availability technologies will perform a failover to another server if and when the current server becomes unavailable. This process involves taking the databases offline on the current server and bringing it online on another server. The fact that it takes the databases offline is in itself an interruption - an outage - because client applications will not be able to connect to the databases while they are in the process of being brought online. Clearly defining what the business requirements are allows you to manage expectations and design the appropriate solution.

Answer 4

Sorry, I forgot to inform the version we work with:

Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64) Jul 12 2019 17:43:08 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

"without interruption" I meant no time for restore process; it must be available 24x7!

I've already set up mirrors, I like them, but I can't use them on the other side as a read-only base.

:(

Answer 5

On time, I imagined that a sum of mirror + read only snapshot could solve the problem, but there will be interruptions during the creation of the snapshots too. :(