Sql-server – can you setup replication on mirrored SQL database

enterprise-editionmirroringreplicationsql-server-2017

Just moved to SQL2017 Ent. Currently, do transactional replication for 1.5TB DB. the replicated database is used for data analytics, mainly Power BI reports. Need near real-time data. When I have issues, initializing the DB is a pain due to size, I initialize from a backup but it takes too long and it is a manual process. was wondering if mirroring is set up, can I replicate the mirrored database, meaning can I set up full transactional replication on the mirror. This way I could initialize from a snapshot on the replicated DB instead of from a backup.

is the mirror only for failover? Can I make the mirror an active read-only copy?

Best Answer

Mirroring has been deprecated for a while - I wouldn't build out things with it at this point.

I think you may be better off spending time preventing issues that cause you to have to re-do your replication, but aside from that note I'd recommend looking at using Availability Groups instead of, or with, transactional replication.

For using with transactional replication:

When a published database is aware of Always On availability groups, the distributor that provides agent access to the publishing database is configured with redirected_publishers entries. These entries redirect the originally configured publisher/database pair, making use of an availability group listener name to connect to the publisher and publishing database. Established connections through the availability group listener name will fail on failover. When the replication agent restarts after failover, the connection will automatically be redirected to the new primary.

and

When an Always On availability group containing a database that is a replication subscriber fails over, the replication subscription might fail. For transactional replication push subscribers, the distribution agent will continue to replicate automatically after a failover if the subscription was created using the AG listener name.

are relevant.

But you could also potentially just use an asynchronous replica in an Availability Group to offer an up to date copy to your BI users as well.

You have a lot of options here and without knowing more details, I'm hesitant to recommend anything specific. But the documentation here is good and should be able to help you devise where to go next.

Related Question