Should I use Database Mirroring?

So I have the following scenario on my hands:

I have a product database that runs for a specific app, all inserts into the database come in through three separate connections.

Recently I have developed a new extension to the database in order to create predictions using SSAS.

My boss wants me to run a field test by creating a copy of the product database to see how well the predictions perform.

My problem is twofold:

According to Microsoft documentation of SQLServer 2012 the database mirroring feature will be removed in future versions: https://msdn.microsoft.com/en-us/library/ms189852.aspx The server is currently running 2012 but it might be upgraded in the future so I feel like this method is a dead end.

Should I be trying to use Database Mirroring for this scenario?

What is a good alternative for this way of Database Mirroring?

I think that mirroring is not the the best solution in this scenario, and discontinuation announcement is not even the main reason, especially if you're planning to maintain the test environment only for limited amount of time.

Mirroring is a high-availability solution that was designed to provide hot/warm standby db in case of failure, or offload reporting to exact copy of the database (with snapshots). So the following properties of mirroring are simultaneously its' drawbacks:

• Both databases will be fully synchronized; you cannot select part of the db for mirroring, and may end up with high volumes of not needed data being transfered from principal database, reducing performance and increasing storage requirements for mirror.

• Changes in principal db are reflected instantaneously on mirror db, impacting production db and increasing network traffic (as opposed to single after-hours updates).

• You can only read from mirror db (through snaphot), so you won't be able to insert your own test data.

So if you don't need to update the test db from production environenment very often, the simplest solution would be to just manually restore full backups on your test db. You can even schedule your own COPY_ONLY backups and restores if you need your test db to be up to date.

You can also go with Merge Replication, which has these advantages over Mirroring:

• Synchronization schedules: it won't impact production db during working hours

• Filtering: you can choose what is being replicated

• Conflict resolution: you can apply your own logic for handling conflict, if you want to populate your db with own test data along with production data.

Snapshot Replication and Transactional Replication are also a viable options here.

Hope this helps, and let us know what you finally chose.

StackExchange Database Administrators Q#93989, answer score: 6