Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

  1. 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.

  2. 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)

Replication

This is transactional DB (replication publisher) Trasaction DB

share|improve this question
    
Your down-time requirements are modest which simplifies things. I'd begin with thinking on the lines of a single physical server rather than two, and with your budget and database size, I'd set aside any thoughts of SAS rather than SSD. It's not obvious to me why you need vast amounts of RAM: is your read-only replicated database receiving queries on the entire dataset? –  Jack Douglas Sep 2 '13 at 13:22
    
@JackDouglas If I go with one physical server, What will be my failover (recovery) device. Replicated database is in READ_ONLY mode , I have few more tables inside for denormalizing data (data mining) and It is receiving queries on entire dataset –  adopilot Sep 2 '13 at 13:30
    
I mean one physical server running: you will need another as a standby as you say but it can be cold. What total storage will you need? 10~14 x 600GB sounds like a lot for two 100GB databases? –  Jack Douglas Sep 2 '13 at 13:33
    
Are your downtime requirements on the replicated database the same? –  Jack Douglas Sep 2 '13 at 13:34
1  
Your "IT guy with plenty of certificates" should go take a hike.... seriously.... SQL Server 2012 already has a first Service Pack out, and it's rock-solid! Those comments are totally unfounded, and don't be impressed by "many certificates" - those can be achieved without knowing much about a topic, really... –  marc_s Sep 2 '13 at 16:37

1 Answer 1

Currently I am running my business on 2x SQL servers which have 38 GB of ram at all

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.

Do I need SSDs beside plenty of RAM

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.

I now have 1 main database (80GB-DATA and 20GB of SQL Server log)
I can have at TWO SQL servers 8 X 250 GB ssd SATA disc
or HP storage with 10~14 pieces of 600GB 10K SAS disc in storage

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.

share|improve this answer
    
My main database is 80GB but It is not normalized, and I have a lot missing index, I except that it will grow up at leas 0,5TB when I will have space to demoralize it, Like extincting sells data per days,weeks etc. –  adopilot Sep 2 '13 at 18:04
    
Unless you need to for specific performance reasons, try to avoid denormalising your data. Improving your base table+index design is usually the better way to go. If you need to denormalise for reporting purposes, try to avoid doing this on the live database and do it on copy specifically for such reporting copy instead (you may want to look into transforming the data as you copy from live to reporting copies, instead of just maintaining a direct replica: search around the term ETL (Extract/transform/load), and perhaps OLAP (Online analytical processing) too, for more pointers regarding that. –  David Spillett Sep 2 '13 at 18:11
    
(BTW: you should add that extra sizing information to your question, then other people who might post a different answer are more likely to see the extra data and use it to make their more relevant) –  David Spillett Sep 2 '13 at 18:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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