2

I've been proposed a concept of using two SQL servers using AlwaysOn as a form of replication.

With the Primary receiving all data, the secondary being a readonly server for a reports source.

Since this feels like the proposal is uncertain as there is no information on configuring something like this, would anyone know if this is a good or terrible idea?

ADDED NOTE: There is no clustering or AG listener. Servers are grouped, but accessed and addressed directly.

Joseph Morin
  • 45
  • 5
  • This is exactly how we use our AlwaysOn Availability Group, there are all kinds articles about this? – Derrick Moeller Feb 27 '19 at 16:22
  • So each server is pointing to the specific server and not an AG listener or cluster role? – Joseph Morin Feb 27 '19 at 16:24
  • It's an expensive way to do log shipping, or mirroring IMHO. And what do you mean there is no clustering? As far as I know, [it's a requirement](https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/prereqs-restrictions-recommendations-always-on-availability?view=sql-server-2017) for it to be in a WSFC. – S3S Feb 27 '19 at 16:26
  • What servers? Do you mean connection strings? Standard read/write connections use the AG listener, read only connection strings also point to the listener but are redirected by `ApplicationIntent`. – Derrick Moeller Feb 27 '19 at 16:28
  • Yes. That is what I expect the implementation "should be". But I'm being proposed something a little different. IE, From My understanding, You could have 2 SQL servers say SQL01 and SQL02. You create an AG group and listener called SQLAG. You connect to SQLAG and all is taken care of. What I'm being proposed is to create the availability group, NOT create the listener and connect to the SQL01 and SQL02 directly. – Joseph Morin Feb 27 '19 at 16:30
  • WSFC is a requirement, I doubt you'll be able to finish the installation without it. – Derrick Moeller Feb 27 '19 at 16:31
  • Tag your version. – S3S Feb 27 '19 at 16:33
  • Done. SQL Server 2012 – Joseph Morin Feb 27 '19 at 16:39
  • @JosephMorin, was your question addressed?. If so, can you please mark it as answered? – Alexander Volok Mar 23 '19 at 10:46

1 Answers1

3

Starting SQL Server 2017 there is no need for Clustering or Listener to deliver a solution for your scenario.

Few things to consider though:

  • AlwaysOn enables READ_COMMITED_SNAPSHOT isolation level on a primary server. This means overhead on TEMPDB and extra 14 bytes per row on every row change
  • In the case of Asynchronous Mode, the data recency on a secondary server can be close to the primary server.
  • Versions older than SQL Server 2017 require WSFC.

Therefore, AlwaysOn AG readable secondaries have pros and cons in comparison with log-shipping:

  • Pros:
    • No need to interrupt connections because no need to restore logs
    • Data can have a nearly real-time recency
  • Cons:
    • Enterprise Edition only
    • 14 bytes overhead per changed row on the primary replica, therefore consider to change fillfactor from 100 to 90 to avoid page splits overhead
    • Way harder to maintain

Regarding your question:

would anyone know if this is a good or terrible idea?

AG readable secondaries are definitely worth of POC trial, especially if your company has required skillset/resources

(Disclaimer: this post is based on my personal opinion)

Alexander Volok
  • 5,630
  • 3
  • 17
  • 33