We have 3 databases on a standard edition of SQL server 2019.
Now we would like to set up it for HA and DR. Since we are running standard edition, I know there is some limitation of the basic availability group. I am wondering if it is worth to purchase Enterprise license considering cost. Or just go with standard availability group
I understand that Basic AG is that we can only put one database into a group, but we can create multiple AGs, each one has a database. In our case, we will need to create 3 availability group, and each has a database. The sizes of the databases are not big. about 5 gb-20 gb. They are all related, when failover we want them to failover at the same time or almost same time which can have a gap of 5-30 minutes. Is it easy to implement? any bad effect?
Or we should buy enterprise license to implement it easier?
Thanks,
November 11, 2022 at 7:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
November 12, 2022 at 4:51 pm
With Standard Edition you are limited to 2 nodes in the cluster - if you need both HA and DR then you would need at least 3 nodes or you would need to make sure the network connectivity between data centers is able to support synchronous mode.
Basically, you need to decide which is more important - HA or DR.
If DR is more important then you can setup a secondary in the other data center, configure a BAG for each database with manual failover. Since this would be a manual failover you control when that happens so all databases are failed over at the same time.
If HA is more important then you can setup an FCI cluster in the local data center. The FCI cluster can then be failed over between those 2 nodes as needed. The FCI cluster uses shared storage so there can only ever be one node active.
If both are necessary then you must upgrade to Enterprise Edition. When looking at the cost - make sure you consider how much it will cost to be down if you need to recover. If the system is not available for a couple of hours while you restore backups - and potentially a loss of data - with minimal cost to the organization, then the cost of Enterprise Edition may not be worth it. However, if the business is down for a couple of hours and any data loss translates to revenue loss - then the cost of Enterprise Edition is a reasonable cost.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 13, 2022 at 12:21 am
I'm no expert on the subject because we have an "Infrastructure Group" where I work but I wonder if a simple "Clustered Server" would do the trick here. They have on-prem clustered servers (and they're in separate racks) AND they have DR (not sure how they're doing the DR but the Cluster Quorum does seem to come into play).
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2022 at 5:51 pm
I'm no expert on the subject because we have an "Infrastructure Group" where I work but I wonder if a simple "Clustered Server" would do the trick here. They have on-prem clustered servers (and they're in separate racks) AND they have DR (not sure how they're doing the DR but the Cluster Quorum does seem to come into play).
A simple 'Clustered Server' is an FCI (Failover Cluster Instance) where SQL Server is installed into the cluster. In this configuration you need shared storage managed by the cluster. Only one node has access to that storage at any point in time and a failover moves the resource to the other node. Note: this does change some with SQL Server 2022.
In an AG cluster - there is no shared storage and SQL Server is NOT installed into the cluster. Each node has access to its own storage and SQL Server is running on every node. SQL Server manages which node is primary and set to read/write.
There are many methods to handle DR - such as storage replication, AlwaysOn (AG) and even the normal backup/restore. As long as the one you utilize meets your RTO/RPO then it is a valid solution for the organization.
A lot of organizations also rely on VMWare for their DR solution. I have not dug into the particulars - but it seems to me that could be a problem if the replication to the secondary data center is not current when a disaster occurs in the primary data center. But again - if the solution meets the RTO/RPO then it is valid.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 14, 2022 at 6:27 pm
For my case, we are planning to setup the secondary node node2 in another data center in another location away from our main data center. Mostly for disaster recovery purpose, if our main district center has a disaster, we can use the server on the second location. But we like also to keep the database synchronized, not necessarily in minutes, but maybe in hour.
Also another question related, I plan to install standard edition on the two nodes, to test, if not working well I will upgrade to enterprise edition. I hope the upgrade works for the two nodes for the cluster, just as I do for standalone edition upgrade.
Thanks,
November 14, 2022 at 6:35 pm
Hi, As Jeff Williams said: "If DR is more important then you can setup a secondary in the other data center, configure a BAG for each database with manual failover. Since this would be a manual failover you control when that happens so all databases are failed over at the same time."
Can I ask why it is to setup manual failover, if the disaster happens on our main data center , node1 is not available, how can I do the manual failover from node1 to node2( in another location)?
Also if I do hourly sychronization, will the sychronization for 3 databases on 3 availibilty groups affect performance of production server -Node1?
Thanks,
February 10, 2023 at 12:14 am
Usually if secondaries are located over distance you would use an asynchronous commit. If auto failover occurs you have the potential for data loss. Ideally you would use do a planned manually failover to prevent this.
If you had more than one node say two at dc 1 then you could have auto failover to give you that local resiliency and the manually failover for DR purposes edition dependant of course.
I believe the following may have the full details.
In the event of a dc outage you can promote the secondary to the primary. But there will be the potential for data loss in the event you DC completely dies.
Hi, As Jeff Williams said: "If DR is more important then you can setup a secondary in the other data center, configure a BAG for each database with manual failover. Since this would be a manual failover you control when that happens so all databases are failed over at the same time."
Can I ask why it is to setup manual failover, if the disaster happens on our main data center , node1 is not available, how can I do the manual failover from node1 to node2( in another location)?
Also if I do hourly sychronization, will the sychronization for 3 databases on 3 availibilty groups affect performance of production server -Node1?
Thanks,
Like Wecks wrote, if you have Server in another DC, sync method can have little delay, so usually is using async method, but still depends on networks and your requirements, so you should test with production workload.
About failover, you can do automatically failover, but in my company we also using manually and normally you should do failover from secondary server, not from primary, because primary can be in bad state and for example not working, so depends on situations.
And honestly I am not sure if its possible to do some hourly sync or some time,never tried or search this. I guess all is in real time, but only wait or not wait for commit.
Some scheduling you can do for example with Replication. But I am not fan of this feature.
----
Asynchronous-commit mode: In this mode, Primary replica sends the transaction log blocks to a secondary replica, but it does not wait for the acknowledgement for transaction commit. It is suitable for disaster recovery solutions
Synchronous-commit mode: In synchronous-commit mode, the primary replica waits for the transaction commit from a secondary replica. Once it receives the confirmation, SQL Server confirms to the client
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply