July 13, 2018 at 11:17 am
Hi,
I am trying to understand what is the criteria to decide that we need to implement Always On Availability
I am trying to see if we need the enterprise edition or can go with a standard edition of SQL Server 2016 .
Our databases are not 24/7 busy. We plan to go for Full Recovery model .
Thanks!
July 13, 2018 at 2:52 pm
I am not an expert on all the options, others will give you more information but I can tell you why we use AG. Our setup is two virtual servers running on different physical hosts.
It allows us to deal with a problem or perform maintenance on one virtual machine or physical server and still have the database available. Note that you do lose any existing connections if you switch servers so that needs to be acceptable to the users or build into software processes if your running automated processes.
We don't need 24/7 availability but being without the database for hours would be a problem.
We don't have the ability to provide 24/7 service which, I think, something like FCI will give you.
Note that standard only gives you basic availability group so you can only have one database in each group. That could be an issue if a user or application needs several databases and they need to be on the same server.
Others will probably give you better info, the above is just based on our setup.
July 24, 2018 at 2:58 pm
as1981 - Friday, July 13, 2018 2:52 PMI am not an expert on all the options, others will give you more information but I can tell you why we use AG. Our setup is two virtual servers running on different physical hosts.It allows us to deal with a problem or perform maintenance on one virtual machine or physical server and still have the database available. Note that you do lose any existing connections if you switch servers so that needs to be acceptable to the users or build into software processes if your running automated processes.
We don't need 24/7 availability but being without the database for hours would be a problem.
We don't have the ability to provide 24/7 service which, I think, something like FCI will give you.
Note that standard only gives you basic availability group so you can only have one database in each group. That could be an issue if a user or application needs several databases and they need to be on the same server.
Others will probably give you better info, the above is just based on our setup.
Thanks for the response. This will help . We have SQL Server 2016 Standard. We have a production server that will have 4 databases , and another reporting server that will have one database . for the reporting server we were planning replication from Prod to Reporting but got an advise to instead use AlwaysOn AG .
July 24, 2018 at 3:21 pm
I've never used replication so I can't compare replication and AG's.
It looks like you want to have one database on two servers and run queries on both at the same time?
Others, with more experience, might be able to confirm but as far as I know, Standard won't let you do that. Only one server is available for queries at one time.
If you need to run queries on more than once instance of the database, within an AG, at the same time then, I think, you need 'read-only routing' which is an Enterprise only function.
Apologises for all the 'I think'. I just wanted to make you aware of a possible issue that you can check on further and others are likely are likely to be able to confirm either way.
It might be that something like replication works better for your scenario, unfortunately, I don't have the knowledge to be able to advise.
You mention several databases. If you might want to include more of them in the availability group in the future then watch out for the fact that, on Standard, you can only have one database in an availability group. You can have multiple availability groups. Depending on your situation it could be possible to run like that, we do.
The disadvantage I've seen is that if you need all the databases on the same server and one automatically fails to the other server than you either need to move the others manually or setup a script to do it for you. I've seen scripts but never used them. Manual failover is fine for us. There could be other disadvantages, unfortunately I can only base my comments on experience, rather than formal training.
Thanks
Reason for edit: Typing error
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply