CODEWITHSUNDEEP
sqlsql-serveramazon-web-services
AAA
AAA

Reputation: 2450

Read only replica on a different server using SQL Server 2019

What options exist through SQL Server or vendor tools including AWS that would allow me to take an existing SQL Server with multiple databases and create a read only replica of a different very large (multiple terabyte) database on that same server?

Our requirement is the databases need to live on the same server and we want to stay on SQL Server.

Linked Servers cause performance concerns when executing complex joins and distributed always on appears to require all databases on source database to be replicated.

Example:

**Server A**
DB1
DB2
DB3
DB4
DB5 *(replicated read only version)*

**Server B**
DB5 *(source, 1tb+)*

What option am I missing?

Upvotes: 1

Views: 3924

Answers (4)

TheGameiswar
TheGameiswar

Reputation: 28938

Our requirement is the databases need to live on the same server and we want to stay on SQL Server.

Lets see what options SQL Server provides by default.

1.Transactional Replication: You can set up Transactional replication and by definition it is read-only.

By default, Subscribers to transaction publications should be treated as read-only, because changes are not propagated back to the Publisher. However, transactional replication does offer options that allow updates at the Subscriber.

2.You can create snapshot of the database and this snapshot can be queried for reporting purposes. But beware of limitations and below performance issue if I/O is your bottleneck.

Performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.

As per my understanding, those are the only options which can help.

You can also use a custom solution which goes some thing like below.

  1. Restore a copy of database first
  2. As per your schedule, you can update destination database tables based on changes,but this can be very complex based on your changes

Linked Servers cause performance concerns when executing complex joins. Your destination database is read only copy of source,not sure why you need to read them by joining SQL Server.

Upvotes: 3

Stephen Morris - Mo64
Stephen Morris - Mo64

Reputation: 101

I wouldn't use Transactional Replication as the database won't be Read-Only

Seems like a classic use case for creating an AlwaysOn Availability group between Server B (Primary) and Server A. In this case I think you will want to use a Availability Group without a Cluster - see the article at - https://www.sqlshack.com/read-scale-availability-group-in-a-clusterless-availability-group/ for an introduction.

Server A will then have a read only copy of DB5

Of course this is based on my understanding of your scenario from the few words you gave above, for detailed pros and cons you could look at the article below although note it is getting a little old and a lot of things have been updated especially for availability groups. Of course if you only require a subset of the data from DB5 suddenly transactional replication looks like a much better fit - moving 10% of 1TB has a lot of advantages.

https://www.sqlskills.com/blogs/jonathan/availability-group-readable-secondaries-just-say-no/

Upvotes: 0

Henrik Staun Poulsen
Henrik Staun Poulsen

Reputation: 13884

May I suggest taking a step back, and ask yourself as to why you need another read-only database? This will use some of the RAM in the server, and you can never have enough RAM...

If you enable Read Committed SnapShot Isolation Level Locking, then readers do not block writers.

You could also look into the Database Snapshot that "TheGameiswar" suggests. This will also conserve RAM.

Both suggestions use extra TempDB, so you need to ensure you get that on the fastest storage you can get.

Upvotes: 3

Vaccano
Vaccano

Reputation: 82517

We have had a scenario similar to this at my company.

Transactional Replication can solve this, but getting it up and running is the hard part. Taking the snapshot needed to start the replication locks up the source database fairly hard.

The solution we found was to take a backup of the source database (DB5 on Server B), and restore it on the target server (DB5 on Server A). Then (via some public, but less well known commands), you attach the replication to that newly restored copy of DB5. This way all changes since the backup was made, and all ongoing changes are replicated to the read only copy.

That way you can keep DB5 running fine on Server B, and have a read only replication copy on Server A.

If you need the actual commands I may be able to go dig them up.

Upvotes: 2

Related Questions