325
Problem
I am a SQL Server DBA and I am very comfortable with the design and development side of SQL Server. I have been complimented by my own team on many of my database designs as well as on much of the behind the scenes development that I have done in SQL Server. Unfortunately, the tides have turned a little bit at work and we now have a stronger focus on the infrastructure components with some new people on the team. I know the basics about hardware, but I get lost on some of the newer terms that some of the new folks bring up. Can you help define some of the key hardware related items and the significance in SQL Server?
Solution
Let’s try to break down some of the recent hardware trends that might be brought up related to SQL Server and outline some of the impacts to the SQL Server environment. We have only a handful of topics in this tip and with the hardware markets changing so rapidly, do not be surprised that what is new today is old tomorrow. Just the fact of life with processing power doubling every 1 to 2 years, the hardware technology changes rapidly and get rolled into environments at a rapid pace.
Platform Technologies
- 32 Bit vs. 64 Bit – The biggest over arching platform aspect is the 64 bit technology. The predecessor was the 32 bit platform that was able to scale but had some inherent limitations, whereas with the 64 bit architecture the natively addressed resources well exceed the practical limit of most applications depending on the hardware platform and Windows version selected. If you have an application that needs to scale, start with the 64 bit platform. Your application and most notably your users will have met their match.
- Blade Servers – A low profile or thin server that resides in a blade enclosure with 1 or more CPUs, 1 or more GB of memory, controller cards, NICs and either has 1 or 2 local disks or is connected to a SAN or NAS device to store the SQL Server databases.
Central Processing Unit (CPU)
- Hyper Threaded CPUs – Two simultaneous threads on the CPU which Windows logically recognizes as 2 CPUs. The advantage in using hyper threading is that multiple threads run simultaneously which improve the CPU response time and minimize idle time.
- Socket – The device that connects the physical CPU to the mother board. The reason the socket has become important is primarily related to licensing for particular products including SQL Server. Not too long ago all licensing was based on a per CPU paradigm. Now that a CPU can have multiple threads (see Hyper Threaded CPUs) or cores, the logical CPUs compared to the physical CPUs can become confusing. As such, with some vendors the CPU sockets are used to determine licensing as compared to the cores. When it comes down to licensing, make sure you know the number of sockets and cores per CPU as well as if the CPUs are hyper threaded, to make sure you have appropriate licensing.
- Dual Core CPUs – On a single socket, two independent microprocessors exist which gives the machine the power of 2 CPUs on a single socket. This is also another example of where SQL Server shows two logical CPUs in the machine as compared to one physical socket.
- Multi Core CPUs – As an example, Intel and AMD recently released a 4 core CPU which means that a single sockets has 4 microprocessors as the next step up from the Dual Core CPUs.
Disk Drives
- External Chassis – An external device with one or more cages that stores disk drives which can be access by one or more machines directly as local disk drives.
- Storage Area Network (SAN) – Centralized storage (disk drives) that can be vertically or horizontally partitioned and presented to a finite number of machines (i.e. SQL Servers). Some of the SAN technologies have front end cache on the SAN to improve the data access by retrieving a cached value (memory) of the data as opposed to retrieving the data from the physical hard drives. Disk drives can also be added to the SAN and then presented to the SQL Servers which streamlines the process to add storage for specific machines across the enterprise. In many circumstances, the NAS (Network Attached Storage) devices are blurring across vendors, but historically the NAS devices were intended for file level access similar to a file server.
- Redundant Array of Inexpensive Disks (RAID) – Although this technology and some of the others in this tip have been around for a while, people still seem to want to know the differences among the RAID sets. In general the RAID set provides redundancy so that N number of disks can fail, but the RAID set remains functional and can process user requests with or without degradation depending on the hardware or software solution. Although, I have seen most RAID configurations managed by a hardware device, software versions are available as well, but are typically considered less optimal as compared to the corresponding hardware version. So let’s take a look at each RAID definition and some additional key points:
- RAID 0
- General – Disk striping without parity
- Minimum number of disks – 0
- Maximum number of disks – Many, but the limitation is ultimately based on the vendor’s specifications i.e. cage, rack, closet, SAN, etc.
- Redundancy – None – If 1 disk drive fails, the entire array fails
- Purpose in SQL Server – Backups, batch file processing, etc.
- RAID 1
- General – Disk mirroring
- Minimum number of disks – 2
- Maximum number of disks – 2
- Redundancy – Ability to lose a single disk and continue operations, but will need to replace the failed disk and rebuild the parity between the new set of disks
- Purpose in SQL Server – Since a RAID 1 set is the most basic form of data protection, with the least amount of disk drives, at times all SQL Server functions are supported. When it comes to performance, RAID 1 sets are ideal for transaction log processing because the processing is primarily sequential. In addition, if a SQL Server instance has intense TempDB processing, in some circumstances TempDB is moved to a separate RAID 1 set, but that is to isolate the processing, even though the TempDB processing is typically more random then sequential.
- RAID 10
- General – Disk mirroring in a disk stripped set
- Depending on the vendor, RAID 10 could be implemented and named as RAID 1 + 0 or RAID 0 + 1, so the general premise may be the same as RAID 10, but some of the underlying implementation details may be different, so check with the hardware vendor for the exact details.
- Minimum number of disks – 2 or 4
- Maximum number of disks – Many, in multiples of 2, but ultimately based on the hardware vendor’s specifications. I know most vendors can at least support a RAID 10 set of 4 or 8 disks. Beyond that number it really boils down to the hardware device used.
- Redundancy – As long as 1 disk remains functional in each mirrored set, the array is operational.
- Purpose in SQL Server – Performance. If a user defined database has intense processing demands, in general the best performance would be from the RAID 10 set.
- General – Disk mirroring in a disk stripped set
- RAID 5
- General – Disk stripping with parity
- Minimum number of disks – 3
- Maximum number of disks – Many, but either in an odd or even number, but ultimately based on the hardware vendor’s specifications. I know most vendors can at least support a RAID 5 set of 10+ disks. Beyond that number it really boils down to the hardware device used.
- Redundancy – Ability to lose a single disk and continue operations, but 2 disks result in an array failure.
- Purpose in SQL Server – Ideal for supporting user defined databases that are read intensive applications, which most are.
- RAID – As a note, some hardware vendors also have other RAID configurations, so please contact them for the details or share them with us and we will update the tip.
- Additional information about RAID: http://www.acnc.com/raid.html
- RAID 0
- Hot Spare – A disk drive in the chassis that can move to an online state in case of a failure in any of the arrays (RAID sets).
- Parity – Means of providing fault tolerance between the disk drives to ensure a single disk failure does not yield an inaccessible drive and/or data.
- 10K vs. 15K RPM – The drive speed for the disk drives with the general premise that the faster the better, but at a higher cost.
- iSCSI – Considered slower and much lower cheaper than Fibre Channel connectivity, iSCSI (Internet Small Computer System Interface) is a protocol used with SAN and NAS devices. This protocol uses TCP\IP for sending and receiving the IO requests to the disk subsystem.
- Fibre Channel – Considered the high speed connectivity between a SQL Server machine and a SAN storage device. Connectivity from the SQL Server to the SAN is via a HBA, cabling (fiber-optic or copper) to a switch and then to the SAN. In many circumstances, there is redundancy between the SQL Server and SAN to ensure no single point of failure.
- HBA – Host Bus Adapter which is a device installed in the SQL Server to communicate with a SAN or NAS device. The HBA acts as interface to manage the IO requests from the SQL Server and responses from the SAN or NAS device.
- SATA – Serial ATA (Advanced Technology Attachment ) which is a standard to read and write to local disks (internal cages) or direct attached storage devices (DAS\DASD).
Memory
- Hot Swappable Memory – For years, it has been possible to change a failed disk drive either as it is degrading or after failure. Now the same concept is going to be applied to memory and processors according to this article – Microsoft demos Longhorn ‘hot swap’ feature In addition, Longhorn has been officially dubbed ‘Windows Server 2008’ in the next release of Windows. For systems where the combination of uptime and high performance are critical, this new hardware feature should get more systems closer to the 5 nines of availability.
- Cache – Memory for temporary storage of data that is expected to be used again in the near term. From a hardware perspective, CPUs and hard drive controllers typically have cache to improve the overall processing. For example, L2 or L3 cache on CPUs is between 1 to 256 MB of storage to increase the speed of the CPU processing.
Virtualization
- Host – A single piece of hardware that typically has its own operating system and dedicated CPUs, memory, disk drives, etc. that is running a number of virtual machines.
- VM (Virtual machine) – One host typically has multiple virtual machines. Each virtual machine operates as a separate physical entity on the network with its own NetBIOS name, IP address, operating system, applications, etc. From an functional perspective, the virtual machine is no different than a physical machine, they have all of the same functional components, but do not necessarily have the same functional underlying hardware components.
Next Steps
- This tip is by no means a comprehensive list of hardware impacting a DBAs job on a daily basis. This is only scratching the surface. If there are other hardware related technologies that you work with on a regular basis that benefit your SQL Server environment like tape libraries, hardware encryption, etc., then shoot us an email (tips@mssqltips.com) with your input that we will share with the rest of the community.
- If you are unsure about how a specific hardware vendor implements one of these technologies, consider reviewing their web site or contact sales\support to obtain the correct understanding for that vendor’s implementation of the technology.
- Depending on the size of your organization and your personal desires, hardware advancements can be very exciting or just a box that you know exists and not much else. Regardless, knowing the basic building blocks and their impact to SQL Server is a valuable set of baseline knowledge.
