August 23, 2012 at 4:36 am
Hello folks
I'm not a DBA. But I've been tasked with speccing up an environment for a client.
Data is stored for 15 years and is currently 200GB after running for 7-8 years. They're on SQL2000 and looking to move to a new, virtual environment. Their existing server is an old box where the reporting is directly from the main data store. This can cause slow downs and isn't ideal in a real time data capture situation.
So...the ideal scenario is to have a Live Server and a Reporting Server. Looking at a Hex core dedicated box (within VM ware). Live having affinity to 4 cores and Reporting to 2 cores.
Due to cost, we'd ideally use Standard Edition
What's the best/fastest method for replicating the data from Live to Reporting? It must have no/minimal impact upon the Live server and be as "real time" as possible. 0-5 minutes ideally.
Any suggestions or thoughts?
I've posted this elsewhere for more exposure. Thanks in advance.
August 23, 2012 at 9:07 am
Well, with Standard Edition, Readable Secondary's are not available, which pretty much leaves you with replication.
However, are you suggesting that both will be on the same physical server, with the same overall resource limitation, but split between either two instances of SQL Server or two VMs?
I'd question this approach if you're not physically separating the hardware. It does give you some level of Quality of Service to each instance (only CPU), but at the expense of increased overhead from running two instances and shipping data to the secondary, wasted RAM from both having to hold the same data in the buffer cache, wasted storage from holding two copies of the databases and a non-dynamic split of resources (e.g. one instance could be not using CPU and the other instance cannot utilise it).
With the Read Committed Snapshot Isolation Level, reporting could run from the same server and have a consistent dataset without being blocked by the OLTP workload or vice versa and you'll likely get better overall system performance and less headaches.
August 23, 2012 at 10:27 am
Hi HowardW
Thanks for the reply.
The servers are going to on a VMWare setup (3-5 servers). So, not really sure if we'll run into problems with two instances.
The main question is about the replication method Live > Reporting server. The isolation level is certainly worth exploring.
There's no way to get an almost-live copy of the Live server without impacting upon either the Live or the Reporting server?
Thanks again 🙂
August 23, 2012 at 10:35 am
Ok, apologies, I thought you were talking about two VM's on a single machine.
With standard edition, you have limited options, which pretty much boil down to:
1) Set up transactional replication for all the tables you want to report from
2) Do the equivalent through SSIS/Linked Server/whatever
Log shipping with STANDBY doesn't really cut it as a reporting instance as it will boot users out every time a new log is restored over and you can't use mirroring/alwaysOn as read only mirrors rely on snapshots, which is an Enterprise only feature.
In a virtualised environment (assuming it's configured flexibly enough), I'd still lean towards allocating more resource to a single instance and using an appropriate isolation level rather than separating them and replicating data.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply