5

Recently Microsoft released Technical Preview 4 for Configuration Manager (1601). Per TechNet, "Configuration Manager now supports using a SQL Server AlwaysOn availability groups to host the site database. When you install a new site, you can direct setup to use the availability group instead of a normal instance of SQL Server."

They then note "Successful configuration and use of availability groups requires you to be comfortable with configuring SQL Server availability groups, and relies on documentation and procedures provided in the SQL Server documentation library."

Currently, the best practice for creating fault tolerance and to avoid a single point of failure for the Configuration Manager Site Database is to install SQL replicas at each management point. I've watched several overview videos (from 2013) to get a better understanding of how AlwaysOn works.

In the DBA community, is AlwaysOn a better solution to providing fault tolerance and avoiding a single point of failure over SQL replicas?

Full discloser: I'm learning Configuration Manager and have very basic knowledge of SQL Server so any advice you have or suggestions would be greatly appreciated.

0

Availability Groups have rapidly become Microsoft's key HA strategy. They are slightly easier to setup and maintain than SQL Server Failover Clustering but provide slightly different benefits and drawbacks. FCIs are purely for SQL Instance level HA solutions whereas Availability Groups provide HA at the Availability Group (group of databases) level.

They are not without their faults or restrictions but their simplicity, flexibility and integration with Azure make them compelling to use for SQL Server, SharePoint and Systems Center HA (where supported).

0

I wouldn't say that AlwaysOn is "a better solution to providing fault tolerance and avoiding a single point of failure over SQL replicas" - actually I am of the opinion that SQL replication has a different purpose other than fault tolerance, but let's stick with your question.

As with every technology, each has it's advantages and drawbacks, however, when comparing AlwaysOn with SQL replication for availability purposes, the from my point of view biggest drawback of AlwaysOn - need for n*2 storage space - is nullified as with replication you also need the same storage space on your subscribers (in case you have to replicate the whole catalog, which probably is the case with Configuration Manager).

As you write that you only have basic knowledge of SQL Server:

  • If you have all your SQL instances in the same Domain/Network and all Configuration Manager Sites equally have access to this infrastructure, I would probably opt for AlwaysOn
    • as soon as you have your endpoints up and running - and there are a lot of resources around on how to do this - you can do everything using the GUI. I advise on using the "Generate Scripts" option, so you can see the statements executed behind the scenes
  • In other cases I would suggest to go with replication as this has been around for a long time and there is a lot of documentation and knowledge available online

(In case the Sites are seggregated (e.g. Cloud- and On-Premise): there is even the possibility to use "Domain Independent Availability Groups", so theoretically you could indeed do it completely without replication https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/domain-independent-availability-groups?view=sql-server-2017)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.