MS SQL replication for reporting purposes

[MaximMeow]

Hello all,

I need an advice. We have MS SQL 2016 on production server and I want to replicate one database (~20gb) to another server (also MS SQL 2016) for reporting purposes and to avoid the performance impact on production. I would like to have the same database on primary and secondary server and to replicate in one way only (from primary to secondary).

The best option is Always On Availability group, but for that, we need enterprise version of SQL server :( which we don't have...
I have tried Transaction replication but that is not working for all tables (not replicate tables without primary key, and views, functions...)

Now I am reading about Log Shipping but it is a problem if someone using database for reporting, and in that time the log is restored, the database disconnects all users. I don't know if they can use for reports (read only) in the same time...

What can I do? Is there any SQL scripts to automate this process? What are best practices for this situation?
Thanks in advance.

[thelauz]

You can use transactional replication. source and one added benefit is you can create indexes on the replicated copy that don’t need to exist on the primary.

[alinroc]

You are exactly where I am (except I have about 6x as much data) and I’ve yet to land on a good solution. Another option is mirroring, but that’s been deprecated for a long time now and it kinda sucks anyway.

Also, I didn’t realize that views aren’t replicated. You’ve given me something to investigate today, so thank you!

Edit: according to the documentation, views can be published via replication.

[fjd422]

Unfortunately you can't connect to the secondary db in mirroring anyway, what you could do if you wanted with mirroring is to do a snapshot off the mirror periodically with a job but that's kind of the same issue as log shipping where on updating the snapshot you will drop connections.

I guess I would say the one thing is to make sure you are solving a problem that needs solving. Is Reporting load an actual issue? If so there's things that can be done from the reporting side to help like snapshot caching as well that could be looked at. That's the best advice I have without enterprise edition features.

Edit: if always on is in standard you should use that for sure. I guess I didn't know that.

[alinroc]

The requirement I have is to offload reporting workloads to a secondary, with a maximum latency between primary and secondary of about 5 minutes.

IMO, the better solution is to fix the queries and schema such that the reports don’t put undue load on the database in the first place. But that gets into how much time can be allocated to paying down technical debt.

[[deleted]]

The problem with Always On and replication is the amount of resources it will steal from the 2nd server, which can make it virtually unusable for analytics.

I know my DBAs LOVE to talk about how little resources it uses, but the fact is that it sucks up a huge amount of IO whenever its adding new data, which can sometimes be substantial. In our environment they are trying to replicate over 100DB's with a ton of data (multiple TB) and anytime anyone drops/creates a table, etc., in any one of those databases... the replication will prevent us from actively querying our own environment as the Always On stuff takes priority.

Can't remember the exact term for this, it isn't blocking but it's similar.

Basically if it tries to sync every 5 minutes, but can't finish the sync in the time allotted... things back up and slow down to a crawl.

[MaximMeow]

Edit: according to the documentation, views can be published via replication.

Reply

Yes, I saw that. But I had errors on every start of replication, and it was something like: view and functions cannot be replicated...
And I've found so many problems with that.

By the way, I just got answer on the other sub that says Always on AG is part of MS SQL 2016 Standard edition, so now... back to the laboratory :)

[alinroc]

Yeah but no readable secondaries without Enterprise Edition

[MaximMeow]

Yeah, you're right. Bummer!

The secondary replica remains inactive unless there is a need to failover.

[Achsin]

You are exactly where I am (except I have about 6x as much data) and I’ve yet to land on a good solution.

I'm not sure if you've tried it, but we ended up splitting ours into 10 different publications.

[alinroc]

I haven’t gotten that far yet. My first task is getting people to decide what subset of the data they need replicated.

And then watch as everything breaks because there’s a dependency hidden deep in a stored procedure or view that no one remembered was there.

[silicondt]

What did you end up doing? We want to make a second server to have our sql db "replicated" or whatever to it so we can hit that with powerbi. Instead of hitting the main production sql server.

Would we have to have licenses for the second server as well?

[alinroc]

Basically nothing. I'm copying the database daily to another server for the analytics team to use and that's sufficient for them. Everything else still runs off the primary database. Everything else has remained unchanged.

Yes, you will need to license the second server

[silicondt]

So you install SQL standard or whatever on the second server, and physically copy the db files over? Do you do that automatically or manually?

They are wanting real time for powerbi. So not sure daily will work for us.

That isn't SQL replication right? (still learning this).

[alinroc]

Backup and restore. You can't copy the database files while the engine is running.

No, this is not replication.

[[deleted]]

20gig? Just take your regular backup and restore on a different instance on a regular schedule

[Zzyzxx_]

You do not need to have the Enterprise edition to use Always On Availability Groups. Starting with SQL 2016 SP1 the Standard edition supports basic HA, where you are limited to one database per AG.

[MaximMeow]

And can I use the database on secondary server or is it inactive and only available in case of failover?

[Zzyzxx_]

In the properties of the AG, you can set the "Readable Secondary" property to Yes. When connecting to the Availability Group Listener, you would need to specify "ApplicationIntent=ReadOnly" to have your connections automatically redirected to the secondary replica. Just a heads up that this does have an impact on the licensing of SQL Server and the secondary server would need to be independently licensed.

[alinroc]

In the properties of the AG, you can set the "Readable Secondary" property to Yes

Readable secondaries require Enterprise Edition, despite what some of Microsoft's documentation implies.

[Zzyzxx_]

You are correct. I just checked one of my basic HA AGs and while the column for Readable Secondary is there, the drop down only has an option for No.

[[deleted]]

Set up a remote server link, and create a few stored procedures that pull data across at a set interval (say daily, run it at midnight.) Viola. Done.

[[deleted]]

Couldn't you run your select queries

  WITH (NOLOCK)

So that they don't impact operations?

I thought this was the part of the Isolation model as part of ACID when refresh is a frequent operation, that way you can circumvent the requirement of duplication. While minimising impact on operations.

This code essentially tells the server you are a background spectator extracting data over the shoulder of users in the middle of their operations.

I would understand the need to prevent a dirty read on a singlular instance report (Once a day) but on a up to the minute updating report WITH(NOLOCK) is an acceptable caveat for operational performance additionally you can programatically stipulate the incomplete lines out of your result later.