December 23, 2022 at 8:27 pm
Suppose I have 2 WSFC clusters, each having 1 FCI (2-node + 1 FileShare).
Fci1 (node1 & node 2)
Fci2 (node 3 &node 4)
There is a database on fci1 which I want to place on fci2 in near real time sync.
It looks like read scale ag is the solution for me (https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/read-scale-availability-groups?view=sql-server-ver16)
Does read scale ag support both sides being fcis?
Or should atleast 1 side be standalone, other side can be fci?
Or should both sides be standalone instances?
December 24, 2022 at 9:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
December 30, 2022 at 7:28 am
The short answer is Yes, you can do this.
At my old place we had two sites, each with a Windows cluster. Each Windows cluster had a SQL cluster installed with all user databases in an AG. The two sites were linked by a distributed Availability Group (dAG).
This setup gave us a read-only copy of our data at the dAG secondary. It also helped us when applying patches - we could fail-over the cluster at each site without affecting the dAG.
All of this ran as guest servers under Hyper-v. This allowed us to move the SQL instances to different physical hardware, which happened a few times each year.
We also used DNS aliases within all our connection strings, so that no connection string needed to be changed on any type of failover. The only thing that changed was the target for a DNS alias.
For us the two-site operation was there to support BCO. This allowed us to use a single SQL license to cover the whole setup. If you want to use the RO cluster for real work then it would need it's own license.
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
December 31, 2022 at 8:25 am
Yes you can use a normal AG across the two sites. If you use SQL2023 you can additionally use a contained AG which has the advantage of replicating all jobs, certificates, logins etc that are created when connected via the cAG.
We chose to use a dAG between sites as we thought it might remove some risk. It means a problem at the secondary site is less likely to cause an issue at the primary site. We once had a conflict between the network and a builder with a JCB that meant we had to drop the dAG for a few days.
Using a dAG also helped in BCO testing. We could isolate the secondary site and force it to accept a failover to allow the testing to proceed. The primary site was not affected, apart from nothing getting replicated. None of this is possible if a single AG spans both sites.
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
January 4, 2023 at 10:25 am
I have only 1 data center.
It has got 3 windows clusters. Each cluster is 2node+1file share.
Svr1 and svr2 is used for oltp workload, and svr3 is used for reporting workload. All 3 servers have sql sever 2019 enterprise edition.
ETL extract data from dbs located on svr1 and svr2 and dump the data into db on svr3.
I want to offload load from svr1 and svr2 by having readable copies of a few dbs from svr1 and svr2 made available on svr3.
I think what I need is the read scale AG, which doesn't require any clustering.
For this, do I need to setup normal AG or distributed AG? Please can you guide me.
January 4, 2023 at 12:28 pm
I think you should choose ehatever you feel most comfortable with. There will be new things you have to learn to get this working OK. Set up a test environment to see how the various options work.
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
January 5, 2023 at 6:29 am
You use the SQL server enterprise which allows you to use High Availability groups. You may use a separate SQL installation and allow them to communicate with each other for ETL processes. That being said, you can alternatively use SQL server replication (transactional ) to make the data available to the SVR3. There are other third part software for replication. You may use database mirroring or log shipping.
As the other person mentioned, you need to setup a test platform to benchmark the best solution.
High availability solution provides the ability to allow your applications continue to process without interruptions.
(provided in Enterprise version only)
It could be cheaper to use a datacenter version of SQL server or even Standard version of SQL server.
DBASupport
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply