8 errors that are easiest to make when purchasing SQL Server hardware

Creating a new SQL Server system can be tricky. SQL Server is a product that really focuses on hardware, and its performance has a lot to do with how you configure the server, especially how to configure your server's storage subsystem. With this in mind, we list some of the most likely errors that people make when they are purchasing SQL Server hardware:

1, choose DIY route. Don't buy parts to assemble your own SQL Server computer unless you are only working on a development computer for a production-only environment. Typically, servers, especially SQL Server, require a processor, chipset, memory controller card, and so on, to match the parts very well. For example, you need components that support high temperatures, and they must be designed to work together. This is not to say that it is impossible to build your own server, but it is easier to buy a complete set of opportunities, and there will be a manufacturer's after-sale support.

2, no performance expectations. You can't simply build a SQL Server system unless you know what kind of load it will take. Of course, you can do this, but you will face the problem of insufficient performance or excessive redundancy, both of which cause a certain amount of money wasted. If you're experiencing a lack of performance, you'll need to improve your server's processing capacity at some point in the future, which means you'll have to spend more money on upgrades (this is related to your initial configuration, and upgrades may not even work). If you're experiencing excessive redundancy, you're actually spending more money than you need or even expect. Use existing databases, applications, or even vendor benchmarks to get some performance expectations, understand how many transactions you expect to handle per second, and understand the corresponding hardware size.

3. When buying a disk, consider only capacity, regardless of disk performance. Yes, SQL Server usually requires a lot of disk space. However, if the disk processing technology is not fast enough, all disk space is useless. Configuring several drives into a RAID5 array may meet your space and redundancy requirements, but if the array does not move data at a certain speed, it will be a major performance bottleneck for your system. If you can't afford a fast disk with the capacity you need, you won't be able to meet the needs of SQL Server.

Ideally, database files and transaction logs should be placed on different disks (or on different arrays), and SQL Server should access them through different channels, such as a disk controller card or a storage area network (SAN) connection. A temporary system database may also require its own stand-alone disk or array if it is used more frequently.

4, selected the wrong RAID scheme. RAID 5 scenarios are slower to write data. Most RAID controllers attempt to overcome this problem by caching data in the controller's memory (the controller memory is usually backed up for security considerations), but a busy SQL Server database may fill the cache space and reach a bottleneck. RAID 10 is an optional option. It costs more than RAID 5, but it is connected to disk mirroring with a data stripe that provides higher redundancy and faster read and write speeds.

5, the purchase of too few drives. If you need XGB or XTB storage space, you will want it to provide as many physical disks as possible to get the fastest throughput. That's because the more disks you have (both small and large), the better performance than having a few high-capacity disks. For striped array scenarios (both RAID 5 and RAID 10 are supported), each disk provides some contribution to SQL Server performance. For example, if you can choose to buy 5 fast 1TB hard drives or 20 250GB hard drives, 20 250GB of hard drive performance (assuming that these disks are configured as striped arrays with the same speed and transmission rate as the drives), it will definitely exceed the performance of the 5 1TB hard drive.

6. Use the disk controller without battery. If you rely on a disk controller to buffer write instructions (for example, a RAID 5 array), make sure the motherboard has a battery. Regularly monitor the server power self-test (with detection notification) screen to ensure that the battery (usually the lithium battery for the watch) is continuously powered.

7, blindly trust the SAN. Sans are not the perfect choice in all cases. You must ensure that there is sufficient throughput that SQL Server does not share resources with so many other servers and applications, competing for bandwidth and throughput. SQL Server requires fast storage access, which is the biggest performance bottleneck for most SQL Server hosts. Make sure you know the configuration of the San (for example, to know if it is RAID 5 or RAID 10, remember the error mentioned earlier), and know its throughput and other details, just as you would like to know the information directly attached to the store.

8. Choose 32 bits. In a 32-bit Windows environment, it is more difficult for SQL Server to take advantage of more than 3GB of memory, and it must adopt some page-extension techniques that are not as efficient as the original means of accessing large amounts of memory directly. If you choose 64-bit hardware, run a 64-bit operating system on it. Also, be aware that Windows Server 2008 R2 and subsequent versions of Windows are available only for 64-bit versions.

Many of these errors seem to be related to storage, and SQL Server storage is one of the easiest areas to focus on, with too much focus on storage capacity and less attention to other factors, such as throughput. In particular, the SAN, storage becomes something like "Our private cloud services", like a big magic box in the air, where the data is.

Of course, SQL Server performance is not just about storage, it's about other factors, such as processor architecture and server memory capacity. To handle problems and analyze performance from the details. Avoid making these errors when purchasing hardware for SQL Server, so that you can get a better performing host.