May 13, 2016 at 4:35 pm
We are implementing a project and need to setup a new SQL server.
The server probably will be used only for this project at least now.
It has database and KPI/SSRS reports, and some ETLs.
There are two options so far.
One is install db, ssis, ssrs on the same box.
Other option is to install db, ssis one one server, ssrs on another server. (2 licenses)
Expect 3-5 concurrent users during peak times (seasonally), 1-2 concurrent users on average during normal business hours (
•Workload will include ad-hoc queries and large range-scans typical of analytical work
•Large ETL loads during off-peak hours (nightly or weekend)
Expect DB size in 20 GB in the next 12 months
My question is which choice is better assuming this server is only for this one project? Option 1 or option2?
Thanks,
May 13, 2016 at 7:38 pm
sqlfriends (5/13/2016)
We are implementing a project and need to setup a new SQL server.The server probably will be used only for this project at least now.
It has database and KPI/SSRS reports, and some ETLs.
There are two options so far.
One is install db, ssis, ssrs on the same box.
Other option is to install db, ssis one one server, ssrs on another server. (2 licenses)
Expect 3-5 concurrent users during peak times (seasonally), 1-2 concurrent users on average during normal business hours (
•Workload will include ad-hoc queries and large range-scans typical of analytical work
•Large ETL loads during off-peak hours (nightly or weekend)
Expect DB size in 20 GB in the next 12 months
My question is which choice is better assuming this server is only for this one project? Option 1 or option2?
Thanks,
There's a couple unknowns the biggest one is, what kind of boxes are we talking about? Based on what you've said: 20GB in 12 months, 3-5 concurrent users during peak times, ETL running in the evening and weekends... I'm always a fan of scaling out and having more server than I need but, based on your requirements, one server with 8 CPUs 64GB, access to at least 6-8 fast disks on your server/LUNs on your SAN should be more than enough. That's based on what you've said - there's a ton of unknowns.
The caveat is that your ETL and the SQL that supports your ETL annd repots is designed correctly and you follow some industry best practices. I've accomplished a lot more with a lot less.
-- Itzik Ben-Gan 2001
May 16, 2016 at 9:21 am
Thanks,
The server will be a VM not physical. The host will be on SAN disks for data, log, backup drives.
I plan to put on a 8 core, but 16 GB box, are you recommending 64 GB?
May 16, 2016 at 10:13 am
sqlfriends (5/16/2016)
Thanks,The server will be a VM not physical. The host will be on SAN disks for data, log, backup drives.
I plan to put on a 8 core, but 16 GB box, are you recommending 64 GB?
You could probably get by with 16GB but on 2014, especially if you plan to exploit 2014's in-memory capabilities, I would say that 32GB is a safer bet. I recommend 64GB because memory is not very expensive and yields a nice return on investment. You want to allocate a high amount of minimum memory for your SQL box so it's always available when the system starts up. After than you still want enough memory for SSIS to run multiple steps in parallel.
-- Itzik Ben-Gan 2001
May 16, 2016 at 10:22 am
Thank you Alan.
The edition we are going to use is Standard, I guess in-memory is not available, but sounds like 32 bits still is a good and appropriate choice.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply