How to utilize the maximum capacity for the SQL Server 2012 Std ?

All replies

  • Hello,

    There are a few things to think about before even stepping into production:

    1. What are the HBAs rated bandwidth? How does this compare to the violin rated bandwidth?

    2. How are the HBAs setup? How is the MPIO driver configured for the HBAs?

    3. What is the maximum throughput of the processors and memory? How does that compare to HBA throughput and storage throughput?

    4. What is the tested amount of bandwidth when stressing the storage subsystem? Is this close to the calculated value?

    When dealing with consolidation, it's not necessarily about the number of instance but if there is a need. For example, if there is a sharepoint farm, I would make that it's own instance and set the processor usage on it manually (as well as the others) so that no other instance stomps on it with parallelism (SharePoint should be set for disabled parallelism). Is there a security need to create a new instance?

    This also heavily depends on the size and usage of certain databases. If 100+ databases are small business unit level databases with few users, sure it might be perfectly fine or overkill... if it's 100 DW sized databases or highly OLTP, there may different ways needed to setup the instance and files... The key here is that it all depends and requires testing.

    I'm sorry this isn't more of a "here is how you do it", but each installation is a little different depending on the needs. The general need of (how I read it) "I need 100 seemingly random and unknown usage, size, and workload databases that are assumed to be all on the same instance of SQL Server will need to be consolidated into a signle or multiple instance with no implied security, patching, or SLA requirements on this beefy server of which I'm not sure there is any need for HA or DR." It's a bit open ended. Hopefully I've given some topics and questions to think about.


    Sean Gallardy | Blog | Twitter

    Wednesday, October 30, 2013 12:33 AM
  • Thanks Sean,

    I know that this is could be confusing for you, as for me, I have been asked to do it on the Standard Edition only due to the licensing cost issue.

    The workload will be varying from the normal departmental application, SharePoint single site and also some Data Warehousing at night.

    Do I need to create separate LUN or DB instance for DW and OLTP and also normal Transactional departmental DB ?

    /* Server Support Specialist */

    Wednesday, October 30, 2013 1:31 AM
  • Hello,

    I would look forward to see how this appliance integrates with a patch management software like System Center. Since it uses VMWare as a core.

    I would try to know which databases are more write-intensive and which ones are more read-intensive, and then try to balance them across instances, if possible. Sometimes some applications required named instances created with specific names.

    Take in consideration maximum memory supported by SQL Server 2012 Standard is 64 GB (4 instances = 256 GB). Balance memory usage across instances using max server memory option and reserve some memory for the operating system.

    One concern I have about this appliance is that I don’t see it is ISO 27002 and PCI compliance. I don’t see it on the Gartner’s Magic Quadrant also.

    I would create separate storage for the Data Warehouse. A Data Warehouse usually has a scan based workload, while traditional transactional databases usually require a seek-based I/O infrastructure. SAS 15K drives are great for a Data Warehouse.

    Hope this helps.


    Alberto Morillo

    Wednesday, October 30, 2013 3:19 AM
  • Alberto, Yes it is part of the PCI-DSS compliance initiatives as well, with the Violin Memory array, I can encrypt the Data at the LUN level.

    This is a physical server SQL database machine, which will also host the VMware VCenter, SAP Business Objects DS & BI applications as well.

    How to make sure that the Instances using the different memory address location or given its fair share of the 62 GB (which leave 8 GB for the Windows Server 2008 R2 OS).

    and also the cpu core as well ?

    I know that it would have been easier if this DB server is deployed as VM instead.

    /* Server Support Specialist */

    Wednesday, October 30, 2013 5:06 AM
  • Hello,

    I would recommend you to request the best practices of the manufacturer, about how to best operate this appliance.

    One thing you could configure to balance CPU usage across instances is the affinity mask option.

    Hope this helps.


    Alberto Morillo

    Wednesday, October 30, 2013 1:13 PM