July 14, 2021 at 1:30 pm
hi folks
i have a sql 2016 std edition database, and i want to add two things to it
sql has moved on a bit thankfully since the last time i configured this, and i am trying to get my head around what the best solution for me is from High Availability/Replication/Mirroring
any advice/guidance appreciated!
cheers
Mal
July 14, 2021 at 6:03 pm
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 14, 2021 at 9:08 pm
Standard Edition supports basic availability groups - which are very limited. You can only have one secondary - it cannot be read only - and it only allows a single database in the BAG. A database snapshot could be used against the secondary - but that doesn't provide near real-time data.
The question is - what do you mean by general reporting purposes. Does that require near real-time data - or data that is up to date as of the beginning of the day, or something else?
If you don't need real-time data, then replication or log shipping might be an option - or the BAG with a database snapshot. I would be extremely careful with a BAG and a database snapshot - as now you have both the BAG and the snapshot maintaining deltas on the data as well as the additional sparse file for the snapshot.
If real-time access is a needed requirement - it might be worth the extra cost of purchasing Enterprise Edition. You can then have a DR node in a secondary data center, an HA node local and a reporting node. The reporting node can be much small (less CPUs and memory) - which equates to less money for Enterprise Edition for the reporting node.
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
July 15, 2021 at 3:19 pm
thanks for input folks
the reporting isn't time critical down to seconds or even a few minutes behind but the budget for sql Ent probably wont exist right now, working of snapshots doesn't sound ideal, which i guess leaves me with replication or log shipping.
log shipping i have setup before and had reasonable success with, but would replication be more efficient? as in always available to read? as if my memory is correct, with log shipping the secondary server was temporarily unavailable when the logs where being restored
thanks
mal
July 21, 2021 at 2:03 pm
As a fellow standard user, I feel your pain 🙂
We use logshipping for one server at our DR datacenter and replication from our production server to our reporting server. So, unfortunately that meant two servers for each need. To have a readable secondary you just have to have enterprise as others have said.
But even with enterprise, and with a readable secondary in an availability group, I believe you would still need to have different indexes for the different workload a reporting server would need. Since the readable secondary would be a replica of your primary you would need to apply report indexes on your primary so they get to your secondary if I'm not mistaken.
With log shipping you can have the secondary in standby mode which allows reads, but again I don't think you're going to be able to create indexes for your tables on the secondary so report queries would need to be carefully written to take advantage of indexes that would exist on the primary.
July 21, 2021 at 2:21 pm
As a fellow standard user, I feel your pain 🙂
We use logshipping for one server at our DR datacenter and replication from our production server to our reporting server. So, unfortunately that meant two servers for each need. To have a readable secondary you just have to have enterprise as others have said.
But even with enterprise, and with a readable secondary in an availability group, I believe you would still need to have different indexes for the different workload a reporting server would need. Since the readable secondary would be a replica of your primary you would need to apply report indexes on your primary so they get to your secondary if I'm not mistaken.
With log shipping you can have the secondary in standby mode which allows reads, but again I don't think you're going to be able to create indexes for your tables on the secondary so report queries would need to be carefully written to take advantage of indexes that would exist on the primary.
Correct. If you want to tune the secondary for reporting, and indexes are typically part of that, replication is the only option.
Unless you are planning on creating a data warehouse, or a secondary database that is populated by an ETL/ELT
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 21, 2021 at 2:33 pm
The problem with creating indexes on the subscriber is that if you ever did failover to it for DR purposes, then all your CRUD statements in the app are going to all be different.
Publisher may have 1/2 indexes to maintain for each Ins/Upd/Del statement, where as Subscriber may have 20 to satisfy all the different reports which happen, so you're going to get application latency when doing that.
Replication is also not suitable as whole database recovery is required rather than selective objects
Replication administration grows complicated when volume of data and number of DML transactions increases
How do you ensure distribution is highly available
Requires extra disk space for saving snapshots and replication transactions
Have to change application connection string to an available node if the node it is connected to becomes unavailable
Replication architecture builds an interim queue in the distribution database, causing higher latencies
Replication in my eyes shouldn't be used as a HADR solution.
If it was me it would be a Basic Availability Group, Log Shipping, or some form of ETL/ELT
July 21, 2021 at 2:38 pm
Although you cannot create additional indexes on the read-only secondary, you can create ETL/ELT type processes that extract data from that read-only secondary into a separate database. This won't be up to date information in those other tables, but generally you don't need current data for reporting.
Most reporting is for the prior day or month - or current year to date (not including current date), month to date (again - not including current date), etc. Those tables can then be optimized for that type of reporting with appropriate indexes for the range of reports using those tables.
That leaves the near real-time reporting requirements, which generally are for current day only (or should be) and the existing indexes on the production tables should be adequate for those types of queries. But even if not, the queries should run quickly enough that adding new indexes to production won't be needed.
If you cannot move to Enterprise - then replication is the only option available to support near real-time reporting requirements, or - you allow those reports to be directly against the production system. If you do run those types of queries directly on production - you should investigate RCSI or allow snapshot isolation. That would allow those queries to be run with less impact - but it needs to be tested in your environment.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply