5

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?

Improve this question
2
  • 2
    I'm not quite sure I understand exactly how you're using mirroring in this situation. But keep in mind that mirroring still exists in SQL Server 2014 and since they haven't announced a replacement yet (Standard Edition customers can't use Availability Groups) it will almost certainly continue to work in the next version, too. I wouldn't use a deprecation announcement that applies to 2 or 3 versions from now as a reason to not use a technology now, just be aware that you will have to make some kind of transition for some future version, someday. I doubt the transition will be hard.
    – Aaron Bertrand
    Feb 27, 2015 at 12:03
  • 2
    If I read you correctly you want to create copy of database, in that case mirror server is in recovery and you would not be able to run anything on it unless you create snapshot(which is enterprise feature). Backup and restore would also create copy of database for you
    – Shanky
    Feb 27, 2015 at 12:06

1 Answer 1

Reset to default
6

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.

Improve this answer
5
  • My problem is that the DB will receive continuous updates and the predictions need to be checked in real time. Running this on a DB has worked fine so far but I need to be able to specifically have all inserts that go into database A also go into database B in near real-time(or as close as I can get)
    – MSB
    Feb 27, 2015 at 12:32
  • 5
    Log shipping is also an option.
    – Aaron Bertrand
    Feb 27, 2015 at 13:07
  • @MSB You can use Transactional Replication if you need the data to be synced in near real-time. It basically works as continuous log shipping, but you can also use schedules.
    – AdamL
    Feb 27, 2015 at 13:10
  • @AdamŁuniewski Thank you for the input, I will try the method and let you know how it goes.
    – MSB
    Feb 27, 2015 at 13:13
  • It seems to be exactly what I have been looking for, thank you :)
    – MSB
    Mar 6, 2015 at 13:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.