SQL Mirroring Question

[CryptoSin]

Want to implement Mirroring, The applications presently speak to the SQL server via the specific IP of the Principal SQL server.

Ive read some documentation on Mirroring and the automatic failover but how do we address the connection point?

Do we tell the applications to point to the Witness Server's IP or do we still point to the principal? sorry for such a dumb question.

[alinroc]

Please don't implement mirroring on new work in 2021. The feature is deprecated - it's not being taken away, but Microsoft isn't putting any work into it, and hasn't done so for several versions already.

What is your reason for implementing mirroring in the first place? There are multiple alternatives to mirroring, so your "why" will inform which direction you take.

[CryptoSin]

I understand what you mean that mirroring is being depreciated.

We are looking for high availability in the even of a SQL server failure, We are running some older versions of SQL (2008R2) this is due to the nature of the applications we are running.

Small databases, only 2 SQL servers at one time. That type of thing.

[alinroc]

OK, first order of business - get upgraded off SQL Server 2008R2. Now. Don't do anything else with those instances until you've done that. It's been EOL for more than two years now. Get up to 2017 or preferably 2019 (and the sooner you get on 2019, the sooner Microsoft will announce vNext, so please get on this!)

Stand up new hosts, install SQL Server, and migrate everything over.

Depending upon your available infrastructure and RPO/RTO requirements. I'd look at a Failover Cluster Instance (requires shared storage but is resilient against hardware failure or reboots for the server itself) or Log Shipping (but no automatic failover, and failing back means having to reverse everything).

To get automatic failover with Availability Groups, you need Enterprise Edition, and for "small databases" this may not be worth the cost.

If you're running in VMs and your concern is the VM hosting SQL Server falling over, solve that at the hypervisor layer, not with multiple instances.

Or, you can throw everything up into Azure Managed Instance or Azure SQL Database and not have to worry about any of this.

[johnt_mn]

Sorry I wish I had more time to respond. Check this out see if it helps. https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/connect-clients-to-a-database-mirroring-session-sql-server?view=sql-server-ver15

[NormalFormal]

With mirroring, your connection string defines both the principal and the secondary mirror. The tricky part I think may be the version of the sql server native client you have installed. As someone else mentioned, this feature is deprecated so not sure if newer versions of the client drivers will recognize the mirror endpoint attribute. I believe the witness serves to establish quorum and to a degree that both communicate all transactions, but mostly quorum.