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?
Best Answer
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.