July 4, 2022 at 8:47 am
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!!
July 5, 2022 at 3:52 am
What would be the problem with giving the application the IP address associated with the Availability Group Listener? If this would be a multi-subnet AG, you could update the application with the other IP address for the listener after failover.
You could use transactional replication, but every table has to have a primary key before that would be an option. When you say you want "real time failover" I don't entirely know what you mean. Do you mean you want automatic failover if there is a problem or do you mean that you want to be able to failover and not have data loss or be behind on data on the secondary and have to wait for some process to catch up the secondary?
July 11, 2022 at 9:26 am
As always, the job of the DBA is to present the technical opportunities along with their risks and costs to management and get them to tell you in writing which option to implement.
There are a number of ways to tackle this problem. All of them have some issues. I have used all three of block-level replication, SQL replication and Availability Groups, and my choice for lowest cost and risk is AGs.
If you set up your servers as a distributed Windows cluster you can use a standard AG and listener to deal with HA for your application databases. There are two main ways to setup the distributed cluster: a) common subnet within a vpn; b) local subnets on each node. Most people today do the local subnet route as it is 'easier' to manage so I will assume you go this way.
You will need an Enterprise license to do AGs with a listener, but my experience is the cost is worth while. When you install SQL remember to select the extra option for the distributed cluster. Also manually set the TTL for the listener to a low value both in DNS and its equivalent in the cluster properties - particularly important in a distributed cluster to allow your apps to connect after a failover.
After the install you can test it by adding all your user DBs to the same AG and you should find these fail over cleanly, and you can access them all via the listener name.
Now for the main problem - system DBs. There is no AG support in SQL2019 for system DBs, and also none using replication. You will need to build a process for copying over everything your organisation adds to master, msdb and model. This includes logins and rights for master and jobs for msdb, plus any changes to all of these. Possibly your best option for this is to exploit the DBATools procedures - these have a wide base of people maintaining them and work well. However you will have to build your own process to run them. Most items can be put into SQL jobs and run on a schedule with with restricted authorities. However, some items will need to run using both sysadmin and Windows local admin rights, the lowest risk for these is to run them manually. Dealing with system DBs is the worst aspect of using AGs but overall AGs IMHO give both the lowest risk and cost.
Migrating to this gives a different issue. The easiest way is to use DNS aliases. I prefer a two-level approach to aliases. A) Each set of HA servers has its own Failover Alias, this should be targeted at the AG listener. B) Each set of applications and databases has its own Application Alias. This should be targeted at the Failover Alias for the HA set that is hosting its DBs. All application connection strings need to be altered to use the relevant application alias. This can all be finished before you start the build of your new servers.
Migration needs testing, often repeatedly until you are confident everything will work. When you do the final cut-over, you then change the failover alias from pointing to your old server to pointing at the new AG listener. No connection string changes should be needed at this stage.
Your aim is good, to reduce business risk and improve resilience. Whatever way you do this will need careful planning and support from management. You will learn new skills and make some mistakes. However, the end results should be appreciated way above your line management level.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 18, 2022 at 6:35 am
This was removed by the editor as SPAM
August 6, 2022 at 9:40 am
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 https://routerlogin.uno/?
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!!
I got this,..
August 6, 2022 at 12:53 pm
First thing, you need to be very clear about the business problem and what you are trying to solve. Eventually you will have to ask for money to get this implemented and money people are interested in business problems, not technology.
Having said that, two options come to mind
A Distributed availability group (dAG)
This typically would have a vrtual guest cluster server at location 1 running SQL cluster, with the same at location 2. You have a single AG on each cluster that hlds all your databases. You then define a dAG between the two locations to keep all your data synchronised. Failover within a cluster allows for SQL maintenance with minimal downtime, and failover between locations can be completed in under 5 minute.
B Windows stretch cluster covering both locations
Again have two guest machines at each location to deal with SQL maintenance. The big difference here is that a single Windows cluster and SQL cluster spans and single AG all locations. You use Windows cluster options to prevent unwanted failovers between locations.
I have set up and ran option A but have not touched option B. Both options allow you to use the other location for read-only workload if you have the SQL licensing for that. You should look at both options and work out how well the risk profiles fit your business.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 6, 2022 at 12:58 pm
To link your applications to the servers use DNS aliases. I have used a two-tier setup of Application aliases and Failover aliases. See my post from July 11 for more details of this
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 6, 2022 at 4:20 pm
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply