SQL Redundant server

Question

Hi

My SQL server is Microsoft SQL Server 2019 Standard.

My goal is to mirror the database on a backup server and to archive the database as well.

Is it necessary to upgrade it to SQL Enterprise? Where can I find instructions on how to do this?

Regards
Syed Haris Ahmed

Answer 1

You will have more options with Enterprise Edition, but you can achieve this on Standard Edition as well.

There are four options:

1. Log shipping. You apply the transaction log from the main server regularly. You can have read-only access to the mirror, but users will be kicked out every time logs are applied. Failing over to the mirror is a manual process. Since logs are only applied like every 15 minutes, there can be some data loss.
2. Transactional replication. Again, the secondary is readable, and this time without interruptions. Replication requires some work to setup, monitor and manage. Failover is manual. Here, too, you can get data loss in case of a failover.
3. Basic Availability Group. Logs are shipped constantly. Secondary is not readable(*). With synchronous mode failover can be automatic and without data loss. While AGs are less complex than replication, they still need care and monitoring.
4. Old style Database Mirroring. This is deprecated, use Basic Availability Groups instead.

(*) Readable secondaries require Enterprise Edition.

Answer 2

Hi @Syed Quadri

Additionally to Erland's explanations...

An Overview what are those features and how to start with them, you might want to know where you can learn about those...
https://learn.microsoft.com/en-us/sql/database-engine/sql-server-business-continuity-dr?view=sql-server-ver16

For the deprecated (but still available) feature "database mirroring"...
https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-sql-server?source=recommendations&view=sql-server-ver16

I hope my answer is helpful to you,

Your
Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

Answer 3

Hi @Syed Quadri ,
I don't think you need to upgrade to the enterprise version. And you can check from this link: https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2019?view=sql-server-ver16

Answer 4

Hey,

You can use Basic Availability Groups with SQL Server Standard. As another option, you can go with Failover Cluster Instances. It requires shared storage. You can use StarWind VSAN to create replicated storage between your server, which can be used as a shared storage for FCI. The following guide covers the configuration process:
https://www.starwindsoftware.com/resource-library/starwind-virtual-san-installing-and-configuring-sql-server-2019-tp-failover-cluster-instance-on-windows-server-2016/

Cheers,
Alex Bykovskyi
StarWind Software
Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.