July 1, 2022 at 2:26 pm
I'm looking into implementing availability groups for our organization after we upgrade from SQL Server 2008 R2 to SQL Server 2016 to replicate several copies of our databases. Does anyone have experience setting this up? I have a few questions....
If a SQL database is corrupted (Encrypted by a ransomware virus, for instance), what does the replication do?
2. Do the Windows Servers in question have to be in a cluster (windows server failover cluster) to utilize Availability Groups in SQL 2016?
3. Are there any problems with intentionally taking one node offline in order to only sync with it once a week
4. Are there any ways to have further security layers between the replicating nodes? (again presuming one node is compromised and hit with ransomware)
We are interested in this feature for both high availability and potential fast recovery if hit with a major ransomware attack, which includes concerns over attempting to prevent multiple nodes from being encrypted. Of course we have backups, and of course the major security concerns are at the file permissions/OS/Active Directory levels, and of course we have backups, but with increasing ransomware attacks in our area, and with our gullible end users, we are trying to preemptively shield ourselves as best as possible.
Thanks in advance for any advice.
July 2, 2022 at 3:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
July 4, 2022 at 1:56 pm
This was removed by the editor as SPAM
July 6, 2022 at 1:15 pm
Hi,
I have some of your answers, but also a question. Why use SQL2016 a) when it is almost out of support, b) when SQL2017 adds automatic initialisation and SQL2019 adds the ability to do AO without needing a Windows cluster or a domain. The more recent versions add useful functionality that definitely makes managing AO easier. As you are asking about design decisions IMHO you should be looking at SQL2022 functionality as you are unlikely to get to production implementation before SQL2022 is generally released.
1) If your master gets corrupted by storing valid but unwanted data via INSERT or UPDATE this will be (as you should expect) accurately replicated to all secondaries.
If your master gets corrupted by file-level changes that are done outside of SQL then it will not get replicated. AO replicates the effect of SQL statements, not file-level data. However, there is very little software around that can update open files, so while SQL Server keeps your files open they should be safe from most file-level attacks. If the attack causes SQL to crash then the DB files could get corrupted by an attack, but SQL is down so no data would be replicated.
2) Yes the servers need to be in a Windows cluster for AO in SQL2016
3) Yes there are problems in taking a node offline. A marker is maintained for the oldest not replicated log position and this will not get updated if a node is offline and replication to it is paused. The impact is that DB log files will grow.
The need for a periodic refresh is typically best met by restoring a backup. A (perhaps poor) alternative is to use a Distributed Availability Group that is set up while you synchronise and dropped for the rest of the week.
4) I do not know of any middleware that could mediate between master and target when you use an AG.
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 6, 2022 at 1:23 pm
If your database gets hijacked and the data gets compromised on the primary node then it will replicate any data changes to your secondary node.
Yes, the windows servers have to be in a cluster for AGs to function. However, distributed AGs may be something to look into as it may give you some other options.
Taking a node offline intentionally for extended periods of time is not a good solution. Reason being is that in your primary SQL Server all of the databases in the AG will keep all data/structure changes in your transaction log queued up waiting for the secondary to come online. If you do a lot of data/structure changes the transaction log will get quite large. And when you DO bring up your secondary the sync time can take hours depending on how much changes need to be pushed to the secondary.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply