CODEWITHSUNDEEP
sql-serversql-server-2012alwayson
Joseph Morin
Joseph Morin

Reputation: 45

SQL AlwaysOn - What if you don't use it as a cluster/failover?

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.

Upvotes: 2

Views: 497

Answers (1)

Alexander Volok
Alexander Volok

Reputation: 5940

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)

Upvotes: 3

Related Questions