Eric Wauters i Facto Business Solutions NV Development Manager April 17 2008 Agenda Basics Native DB Server SQL Server Terminal Services Clients amp NAS Network Agenda Basics Why pay attention to hardware ID: 802330
Download The PPT/PDF document "Hardware Recommendations to make install..." is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Hardware Recommendations to make installations hum
Eric WautersiFacto Business Solutions NVDevelopment Manager
April 17, 2008
Slide2Agenda
BasicsNative DB ServerSQL ServerTerminal ServicesClients & NASNetwork
Slide3Agenda
BasicsWhy pay attention to hardware?Native vs. SQL ServerCore ComponentsNative DB Server
SQL ServerTerminal ServicesClients & NAS (Navision Appl Server)Network
Slide4Basics - Why paying attention to hardware?
Performance Tuning Areas:Application: 80-90%Infrastructure: 10-20%You need a solid baseQuick fix
Slide5Basics - Differences Native – SQL Server
Microsoft Native DB
Microsoft SQL ServerSingle logical db - multiple files (up to 16)Multiple logical databases - each db multiple filegroups - each filegroup multiple filesDB Size limit 128 Gb
(256 or 512 with special agreement)
No real DB size limit
Single processor
Multiprocessor
32 bit only
32 bit and 64 bit
1GB RAM only
Large RAM support
Not cluster aware
Cluster aware
Backup: Client based or HOTCOPY
Various backups strategies
Interfacing through Client, NAS, C/ODBC,C/FRONT
Various interfaces (API, ODBC, etc...)
Feature rich:
Reporting Services, Analysis services, Integration Services, Alerts, Jobs, Security, ...
Slide6Basics - Differences Native – SQL Server
Different server options need different hardware
Slide7Basics - Similarities Native – SQL Server
Index is an index, Lock is a lock, Block is a block, Deadlock is a deadlockGet data fast, Write data fast!
Slide8Basics - Core Components
Core Components
Slide9Basics - Core Components
Memory (RAM)Processors (CPU)Disk Subsystem (RAID)NetworkServer settings
Slide10Basics - Core Components - Storage Types
RAID 0RAID 1RAID 10RAID 5
Slide11Storage Types - RAID0
9
8765
4
3
2
1
9
8
7
6
5
4
3
2
0
0
1
Logical Layout
RAID0 Physical Layout
Slide12Storage Types - RAID1
9
8765
4
3
2
1
0
Logical Layout
RAID1 Physical Layout
9
8
7
6
5
4
3
2
1
0
9’
8’
7’
6’
5’
4’
3’
2’
1’
0’
Slide13Storage Types - RAID1
9
8765
4
3
2
1
0
Logical Layout
RAID10
Physical Layout
9
8
7
6
5
4
3
2
1
0
9’
8’
7’
6’
5’
4’
3’
2’
1’
0’
Slide14Storage Types - RAID5
9
8765
4
3
2
1
8-9 parity
8
7
6
4
4-5 parity
2-3 parity
2
0
0
1
Logical Layout
RAID5 Physical Layout
9
6-7 parity
5
3
0-1parity
Slide15Agenda
BasicsNative DB ServerHardware recommendationsServer SettingsSQL Server
Terminal ServicesClients & NAS (Navision Appl Server)Network
Slide16Native – Hardware Recommendations
Limited possibilities due to limited support.1Gb RAM1CPU coreSingle database per server
Slide17Native - Hardware Recommendations
Dedicated ServerCPU1 CPU for NAV (If necessary: second CPU for +40 users)RAM1,5 Gb (1 for NAV; 0,5 for OS)DISKSOS – dedicated RAID 1
fdb file – dedicated RAID 1.fdb > 5Gb Split over multiple RAID 1 RAID 1015K RPM
Slide18Native - Server Settings
A few tips:All database files: same file sizeDedicated fysical disks per fileUse commitcacheMake sure you use a UPSIf creating extra database file:Backup/delete db/create db/restore!
DB Cache
Slide19Agenda
BasicsNative DB ServerSQL ServerHardware recommendations
Server SettingsTerminal ServicesClients & NAS (Navision Appl Server)Network
Slide20SQL Server – The Basics - Architecture
CursorsNAV translates C/AL commands into client side ODBC cursors that are sent to SQL Server Row BasedThese cursors are “Row Based” and not “Set Based” so NAV Selects, Updates, and Inserts one row at a timeNDBCS.DLLTranslates C/AL code into TSQL statements
C/ALDetermines the syntax of the TSQL
Slide21SQL Server – The Basics - Interaction Diagram
Slide22Components Costs
Queries costs:Transported over networkCompile execution planExecute plan
Read data, write dataCache plan and dataStore locks, release locksSend back over network
Network
Network
CPU
CPU
Storage
RAM
RAM
Slide23SQL Server – Recommendations - Core Components
Memory (RAM)Processors (CPU)Disk SubsystemServer Settings
Slide24SQL Server – Recommendations - Memory
Importancememory < > disks = ns compared to ms!CACHE!
Slide25SQL Server – Recommendations - Memory
32 bit vs 64 bit32 bit directly addresses up to 4Gb of memory (232 = 4.294.967.296 bytes = 4Gb)
64 bit can address an “indefinite” amount of RAM (264 = 18.446.744.073.709.551.616 bytes = 18.446.744.073,7 Gb)
Slide26SQL Server – Recommendations - Memory
32 bit – how to use more RAM?Boot.ini:/3GB: Default windows install: 2 Gb reserved for kernel (OS)2 Gb for usermode
With /3Gb: 1 Gb reserved for kernel (OS) 3 Gb for usermode/PAE“Physical Address Extensions”memory address extension that enables support of greater than 4 GB of physical memory
Slide27SQL Server – Recommendations - Memory
32 bit – how to use more RAM?SQL Server:AWE: to acces memory over 4Gb“Address Windowing Extensions”Address Memory over 4Gb as nonpaged memorydynamically map views of the
nonpaged memory to the 32-bit address spaceYou must specify /PAE in boot.ini
Slide28SQL Server – Recommendations - Memory
32 bit – Server settingsMin/Max SQL Server MemorySet the same for ‘constant’ behaviourMemory
Check the Windows Server and SQL Server editions limitations/3Gb/PAEAWE
< 2Gb
No
No
No
2
-
4Gb
Yes
No
No
4
– 16Gb
Yes
Yes
Yes
> 16Gb
No
Yes
Yes
Slide29SQL Server – Recommendations - Memory
32 bit - Warning when using AWE:Imposes overheadAdds initialization timeMemory above 4Gb is only for data caching, not for:Plan cachingSort spaceLock memory
Slide30SQL Server – Recommendations - Memory
What about x64?Support for large RAM - no boot.ini changesNot only data caching, also:Plan cachingSort spaceLock memory=> “Always Rowlock”
Slide31SQL Server – Recommendations - Memory
Windows - Maximum MemoryWindows 2003 (32bit)Windows 2003 Standard = 4GBWindows 2003 Enterprise =
64GBWindows 2003 Datacenter = 128GBWindows 2003 (64bit)Windows 2003 Standard = 64GBWindows 2003 Enterprise = 1TBWindows 2008 (32bit)Web Edition: 4GBStandard Edition: 4GBEnterprise Edition:
64GB
Datacenter
:
64GB
Windows 2008 (64bit)
Web Edition:
32GB
Standard Edition:
32GB
Enterprise Edition:
2TB
Datacenter
:
2TB
Slide32SQL Server – Recommendations -Memory
SQL Server Maximum MemorySQL 2000Desktop Engine = 2GBStandard Edition =
2GBEnterprise Edition = 64GBSQL2005Express Edition = 1GBWorkgroup Edition = 3GBStandard Edition = Operating System maximumEnterprise Edition = Operating System maximum
Slide33SQL Server – Recommendations - Memory
IMPORTANT On x64 SQL Server platforms you must give the SQL Service account the “Lock Pages in Memory” privilege in order for SQL to use the available RAM
KB Article 918483
Slide34SQL Server – Recommendations - Memory
CONCLUSION
Memory Recommendations
As big as you can afford
Rough guidelines
CCU
<6
6-50
51-150
151-250
RAM
2 Gb
4 Gb
8 Gb
16 Gb
Slide35SQL Server – Recommendations - CPU
CPU recommendationsTypically NOT a bottleneckCores vs SocketsDisable Hyper ThreadingRedundant
CCU<6
6-50
51-150
151-250
CPU
Cores
2
2
4
8
Slide36SQL Server – Recommendations - CPU
Windows - Maximum CPU SocketsWindows 2003 (32bit)Windows 2003 Standard = 4CPUWindows 2003 Enterprise =
8CPUWindows 2003 Datacenter = 64CPUWindows 2003 (64bit)Windows 2003 Standard = 4CPUWindows 2003 Enterprise = 8CPUWindows 2003 Datacenter = 64CPUWindows 2008 (32bit)Web Edition = 4CPU
Standard Edition =
4CPU
Enterprise Edition =
8CPU
Datacenter
=
32CPU
Windows 2008 (64bit)
Web Edition =
4CPU
Standard Edition =
4CPU
Enterprise Edition =
8CPU
Datacenter
=
32
CPU
Slide37SQL Server – Recommendations - CPU
SQL Server Maximum CPU SocketsSQL 2000Desktop Engine = 2CPUStandard Edition =
4CPUEnterprise Edition = 32CPUSQL2005Express Edition = 1CPUWorkgroup Edition = 2CPUStandard Edition = 4CPUEnterprise Edition = Operating System maximum
Slide38SQL Server - RAID Recommendations
Three Spindles is a must!WinOS,Page File,SQL Program files,etc.Pagefile.sysTransaction LOG100% writeWrites all the time (“write ahead”)Database File(s)Read: 80-90%, Write 10-20%
Writes at checkpoints
Slide39SQL Server - RAID Recommendations
What about TempDB:Used for large operations (e.g. Reindex), mainly out of hours.In SQL2005: often 5-10% of IOs on TempDB files.
Slide40SQL Server - RAID Recommendations
OS, SQL Program files, etc = RAID1LOG File = RAID1DB Files = RAID10(TempDB = RAID1)Small disks, not for capacity but for IOPS !Do NOT use RAID5.
Slide41SQL Server -
Disks SummaryBiggest bottleneckThink “Spindles” not “Capacity”
As fast as possible15kRPMRAID 1/RAID10
Slide42SQL Server -
Disks Summary
Slide43SQL Server – Server Settings
MDOPAuto Create StatsAuto Update StatsAuto Shrink – Auto Grow
Slide44SQL Server –
Disk AlignmentA volume cluster may be created over a stripe unit boundary instead of next to the stripe unit boundary. This causes two disk groups to be accessed when a single volume cluster is updated on a misaligned partition.
KB Article 929491
Slide45Agenda
BasicsNative DB ServerSQL ServerTerminal ServicesClients & NAS (Navision Appl Server)
Network
Slide46Terminal Services
Memory64 MB per Dynamics NAV user + 1 GB for OSExample: (100 x 64) + 1000 = 7.4 GB or 8 GB CPU10 -15 users per CPU coreBased on client activity; Manufacturing and so on Disk1Gb
per userBased on activityNetwork1 Gigabit Ethernet
Slide47Agenda
BasicsNative DB ServerSQL ServerTerminal ServicesClient & Navision Application Server (NAS)
Network
Slide48Clients and NAS
RAM256 MB or greaterCPU2.0 GHz or greaterDiskIDE (1 GB of drive space)Network100 Megabit (No hubs)
Slide49Agenda
BasicsNative DB ServerSQL ServerTerminal ServicesClient & Navision Application Server (NAS)
Network
Slide50Network
1 Gigabit BackboneSQLTerminal ServicesBizTalkIISApplication Servers100 MegabitClientsNo hubs only switches
Slide51Other Storage Options -
DAS/NAS/SANDirectly Attached StorageNetwork Attached StorageStorage Area Networks
Do not use NAS
Slide52Agenda
BasicsNative DB ServerSQL ServerTerminal ServicesClient & Navision Application Server (NAS)Network
Slide53What about NAV 2009?
New architecture Middle TierOnly SQL Server
Slide54Resources
MicrosoftPartnersourcehttp://www.microsoft.com/dynamics/partnersource.mspxWindows & SQL Server editions:http://www.microsoft.com/
Storagesearch.comhttp://www.storagesearch.com/xtore-art1.htmlSQLPerform Ltd.http://www.sqlperform.com
Slide55Q&A
Thank you for your attentionEric Wauterseric.wauters@ifacto.be
www.waldo.be