3

I have an SQL Server 2014 Enterprise Edition with a lot of data. I would like to mirror the data from that to at least 3-4 servers. The mirrors are all SQL Server 2014 Standard Edition (no more money is available for Enterprise licenses).

How do I mirror the data from my main box (with the Enterprise Edition license) to other boxes? I tried the mirroring feature, but it seems that it only allows single mirror. I could you use Always On Availability groups, but that would require that all mirrors also be Enterprise Edition (unless I am reading the docs wrong).

At least one of the mirrors needs to be there almost real-time (1-2 minute delay is fine) data replication. The other mirrors could have 1-2 hours delay.

So what are my choices?

P.S. All the secondary servers are just read only.

P.S. The purpose of the mirrored boxes are partially to off-load readonly queries to them. These mirrors need to have near real-time data replication. Another purpose is for analytics, which is a heavy load. Today everything is on the same box and we are forced to do analytics at night so as not to disrupt users and there is just not enough time.

P.S. The servers are nearby each other - on the same subnet, connected via a 10Gb link.

P.S. Our license also allows a no cost upgrade to SQL Server 2016 when it becomes available. Does that change anything?

  • Log shipping can help with 1-2 hours delay scenario. For 1-2 minute delay, well, you may need to open up the wallet a little, or be ready for angry users when you kick them out every two minutes to apply the next log. – Aaron Bertrand Mar 23 '16 at 19:05
  • I'd suggest looking at a hybrid between mirroring and log-shipping. Mirroring will allow you a hot-standby for failover. Log shipping will allow you to have read-only databases. – Max Vernon Mar 23 '16 at 19:43
  • Neither Mirroring, nor Basic Availability Groups in SQL Server 2016 (with Standard Edition) will provide a solution since the secondaries in both cases cannot be read from. You could experiment with Log Shipping and crank one of the secondaries up to every 5 minutes for near real-time reads but that has its own set of issues (restoring the DB every 5 minutes interrupts reads) – IanG Mar 23 '16 at 22:05
2

Ok here is what I'd suggest:

Transactional replication If unfamiliar, it can be set up relatively easily using the UI. This link will help: (http://www.codeproject.com/Articles/715550/SQL-Server-Replication-Step-by-Step)

Mirroring + log shipping would work alright also, as others have suggested.

If you have something like automated ETL queries you need to provide for, you can drop and recreate the snapshot of the mirror database prior to the query running. In the past I have added the drop and recreate snapshot as a step before running an SSIS package or whatever. If you are needing to make the mirror available for ad-hoc querying for a number of users, you can always work out a schedule with your users (like say every 30 minutes) to drop and recreate the snapshot such that it is almost completely up to date. Depends on the specific use case but I'm sure you can see what I'm getting at and tailor it to your needs. Make sure you have instant file initialization enabled for the SQL Server service for the snapshot file to be created instantly.

EDIT - Just realized snapshotting isn't supported in Standard edition, so mirroring won't help you.

Ultimately AlwaysOn is a great choice for your needs but if you have issues with editions then I'd just go with replication.

1

If you intend to be able to failover to your warm standby servers then you could be out of luck unless you go down the route of log shipping with a delay.

SQL Server 2016 will support Availabilty Groups in Standard Edition, but that might not help you as I'm not sure I'd be happy with that version disparity between Enterprise and Standard, even if it's supported.

Maybe look into replication solutions outside of SQL Server. You could create a makeshift replication procedure in Integration services no problem, but I'm not sure you'd achieve your ideal synchronisation time.

Best of luck.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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