SQL Server 2019 HA

  • Looking for a HA solution for SQL server for our branch offices. Current setup has our 3 offices running independent standalone servers with SQL server. No redundancy in place.

    My aim is to centralise the servers in 2 locations. Location A and B has one server each. Both running windows server vm and sql in each vm. One acting as primary and the other as a failover. Trying to achieve real time failover with the latest data on SQL. Maybe via replication?

    So far I've come across Always On Availability Group and FailOver Cluster Instances. Since all the pos applications require server name (IP of server hosting SQL) and database name( name of the database and instance) is there away to achieve something along the lines of VRRP but for SQL Server? Or even Windows Server?

    I know VRRP is networking, just how the concept works I would like to plan the new environment to allow little to no downtime so tills can remain up and running either during maintenance or server failure.

    Thanks!!

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • I would not recommend pursuing replication. It's very old technology that gets almost no love from Microsoft at all. You honestly sound like a perfect candidate for Availability Groups. Yes, they are set up by database, but you can set them up for all the databases in question. No worries. Further, you can make the secondary a place for reads (all writes have to go to the primary) to help distribute the load on the system. There's a Stairways series on AG linked up above. I'd suggest reading through that to get started.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • Hi, we are using cluster due to unexpected restart of data centre now SQL node 1 is working fine but on node 2 SQL server service s not running.error 1067 .The process terminated unexpectedly.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply