We have an application that is licensed on MS sql server instance name and database name. It is not possible to add a second instance/database. Is it possible to have active mirroring and let the second server jump in when first server is unavailable? If this is possible, could this also be used to balance load (both servers/databases used)?
1 Answer
It is not possible to add a second instance/database.
Yes it is, please note instance is not same as database in SQL Server. One instance of sql server can hold many databases.
Is it possible to have active mirroring and let the second server jump in when first server is unavailable
Yes we have database mirroring in SQL Server where mirror takes on principal role when failover happens. We also have Availability Groups.
If this is possible, could this also be used to balance load (both servers/databases used)?
No, mirroring cannot be used to balance load. To balance load, only for read-only queries you have Read Scale AG. This is not pure load balancer you are looking but more of offloading read only queries to replicas.
-
Thank you. I better understand not to mix mirroring and load balancing. First main point is still not clear: with instance I meant server name (what you use for connection string). I suppose this is not possible to have two servers with same server name and the second server has a mirror of the same database, right? Nov 23, 2022 at 19:26
-