My budget for a data center (hardware,OS licences, SQL Licences WithOut CALS ) is around $50K~$60K.
I have needs for around 200 users and my apps works only works in Microsoft environment (Window Server and SQL Server).
I've talked to a lot of "so called" experts and every one had different vision for my data center, as much I talking with vendors I am more puzzled how I should design my data center, perhaps this is because I did not meet yet any SQL Server expert who knows what to do.
My living area is so poor whit IT Specialist.
My app is not mission critical and We can afford down time up to 1 working day (8 hours).
I now have 1 main database (80GB-DATA and 20GB of SQL Server log) for writing transaction
That database is transactional replicated by SQL Server to second physical SQL Server web servers from there reading large reports.
I have around 200 users which over WEB server communicating with my SQLs
Here is what I considered so far to do.
-
First scenario
3X Physical servers (no storage, no SAN, no Virtualization) one server will act as Web Server other two is going to be SQL server whit 2012 standard edition each server will have 96GB ram and Intel Xeon 6C Processor 2Hz. In this scenario I can afford IBM enterprise SSD discs I can have at TWO SQL servers 8 X 250 GB ssd SATA disc. First server will replicate database to other.
-
Second scenario
2X physical servers one IBM or HP storage with 10~14 pieces of 600GB 10K SAS disc in storage. All of that virtualized by vmware and running on one SQL Standard Server. without SQL replication ,but whit 200GB of ram in SQL-os.
Personally I do not have experience with virtualization and storage systems. I more like SQL Server replication.
Here is some questions which I seek to have direction which way to go.
Should I avoid virtualization for machines which is dedicated to running SQL Server ?
Is SQL Server 2012 ready for production? I know that this is perhaps silly question but one of IT guys with plenty of certificates say that they always running production instances on second to last Microsoft platform, I am really confused how is He strong in claiming that they not going to instal and configure MS Servers (OS, SQL Server) on 2012 generation.
Do I need SSDs beside plenty of RAM. Unfortunately I can't do much in redesigns queries!
Here is what I have captured during 3 hour monday peek time.
Reporting Database (replication subscriber)
This is transactional DB (replication publisher)
Trasaction DB
Best Answer
As well as the replication strategy to improve availability the though the replica taking over if the primary fails, you must factor in a god backup strategy - in what follows I'm going to assume that you already have this (if not, you need to factor it into your costings and a really good backup solution for your business critical data is not something you should try buy/create cheaply).
If you are happy with this arrangement in terms of resilience and general performance, and you are just upgrading the servers mainly to replace old hardware, then go with option 1 and upgrade the current design. Remember that if you build a different infrastructure you may run into problems you've never seen before and if you do they may turn up at the least convenient time for your business. If you don't have the knowledge/experience to fix things fast (and it isn't available in anyone else in the company you can call on) this could be very expensive in terms of business lost due to downtime and paying an expert to fly in and help.
That very much depends on your applications write patterns. Given you have ~80Gb of data you might find all your common working set could be held in RAM most of the time so for read heavy loads you will see little or no difference between the SSDs and spinning metal for day-to-day operations as the drives won't be touched for most reads. That data held in memory will be lost in some circumstances (such as when SQL server is shut down for what-ever reason) and it will take a while for the working set to get loaded and at this point the SSDs will help quite noticeably. For writes data will still of course need to hit the disk, so for write heavy loads the SSDs will help.
Do remember that Standard Edition will only use up to 64Gb of memory per instance (see http://msdn.microsoft.com/en-us/library/cc645993.aspx#CrossBoxScale) so you may not see all that 96Gb get used in either box.
Am I reading this wrong, or are you expecting your data size to increase a fair bit over the active service life of the new hardware? In RAID10 those SSDs will give you ~1TB of space which is considerably bigger than the ~100Gb you are using now, and that large collection of spinning metal even more so (3Tb+ unless you configure a redundancy level over 2:1), so you might be over-spending here for your near-/mid-term needs.