VMWARE and Windows Infrastructure Design (4) - MS SQL

Step 1 Project Scope
1. Application specific The list of applications require SQL
2. As a service

Step 2 Decide Roles
1. Database engine
2. Integration services
3. Analysis services
4. Reporting services
5. Master data services

Step 3 Design Database Engine
1. Capacity and performance requirements
  a. Disk storage database, transaction log, indexes and tempdb
  b. IOPS and Throughput Smaller drives are better than one big one
2. Database in New/Existing Instance
  a. Regulatory requirements
  b. Memory isolation
  c. Fault tolerance
  d. Authentication
  e. Security concerns
  f. Support requirements
3. Instance on New/Existing server
4. Number of servers
  a. Scaling out
    i. Scalable shared database
    ii. Peer-peer replication
    iii. Linked servers and distributed queries
    iv. Distributed partition views
    v. Data-dependent routing
    vi. Service-oriented data architecture
    vii. Service broker

Scale-out solution	Update frequency	Ability to change application	Data partitionability	Data coupling
Scalable shared databases	Read only	Limited or no change required	No requirement	No requirement
Peer-to-peer replication	Read mostly, no conflicts	Limited or no change required	No requirement	No requirement
Linked servers	Minimize cross-database updates	Minor changes	Not typically required	Important to have coupling
Distributed partition views	Intended mostly for read	Some changes may be required	Very important	Limited impacts
Data-dependent routing	Frequent updates okay	Significant changes possible	Very important	Low coupling may help some applications
Service-Oriented Data Architecture	Frequent updates okay	Changes usually not required	Not required, unless combined with DDR	Low coupling between services required
Service Broker	Frequent updates okay	Significant changes possible	No requirement	No requirement

5. Placement of each new instance
  a. Location of instance
  b. Physical or virtual
  c. Existing or new hardware
6. Select hardware

Step 4 Integration Services Infrastructure
1. Resource requirements
2. Integration services packages locations
3. Number of SSIS servers
4. Placement

Step 5 Analysis Services Infrastructure
1. Resource requirements
  a. Disk storage
  b. IOPS and throughput
  c. Memory and processor
2. SQL server version
3. Scalable shared database used
4. Scaling needs
5. Cluster
6. Placement

Step 6 Reporting Services Infrastructure
1. Resource requirements
  a. Disk storage ReportServer Database ReportServerTempDB Database
  b. Memory 2-4GB per core
2. Placement of databases
3. Scaling and fault-tolerance approach
4. Placement of servers

Step 7 Master Data Services Infrastructure
1. Resource requirements
  a. Services database
  b. Services web server
2. Scaling and fault-tolerance
3. Placement of databases
4. Placement of web servers

Step 8  Perfomance Enhancement
1. Use Parallelism MAXDOP=1 if SQL server performance is suffered, using such as BI, replication, along with the Parallelism cost configuration;
2. Increase transmit coalescing size in VMXNET NIC from 0 to 128 in physical host;
3. Increate Disk.SchedNumReqOutstanding from 32 to 64 in physical host to avoid I/O command queuing;
4. Lock SQL server Buffer Pool Pages in memory using local group policy security settings.