Archive Shuttle 11.5 - SQL Best Practices

Types of storage device

It is vital to ensure the storage does not become a bottleneck. By following Microsoft SQL Server best practices, it can be ensured that the SQL server is suitably sized. Avoid using network-based storage for the database files.

In most cases, RAID-based storage will be needed to achieve the storage requirements. To maintain performance and reliability, consider hardware-based RAID rather than software-based RAID. To achieve redundancy on striped arrays while maintaining performance, consider the RAID scheme carefully.

RAID levels 5 and 6 are popular, cost-effective methods of achieving redundancy while maintaining striped disk read performance. However, writing incurs a cost of four to six physical operations per write. A poorly sized RAID-5 or 6 implementation can significantly reduce the performance of write-intensive activity. Correctly sizing a RAID-5 or 6 implementation to maintain write performance may become more costly than RAID-1+0, and therefore a RAID-1+0 scheme should be considered.

In the case of local or direct attached storage, use multiple controllers supporting multiple channels to distribute the load between the multiple storage locations and provide sufficient throughput. The controllers should also provide a battery-backed read and write cache to aid performance. A minimum of 512 MB controller cache is recommended for local or direct attached storage.

Before using partitions on a storage area network (SAN), consider the I/O load together with any other applications that are already using the SAN to ensure that the performance can be maintained. Ideally, discuss the implementation with the SAN hardware vendor to ensure that it can achieve optimum performance. Typically, LUNs should be created across as many suitable disks as possible, using entire disks rather than partial disks to prevent multiple I/O-intensive applications from using the same disks. When the HBA is configured on the host, ensure that the Queue Depth is set to an optimal value. This should be discussed with the storage vendor.

When creating a basic NTFS volume on a storage device, it is very important to align the volume with the device sector or stripe unit boundaries to prevent unnecessary disk operations that can significantly impact performance. (Dynamic volumes cannot be aligned at time of publication). See the TechNet article “SQL Server best practices” for more information and using the diskpart tool to create and align volumes. This article also recommends that both log and data partitions are formatted with 64 KB allocation unit sizes.

In most cases, you should create a single volume on each disk array to avoid contention at the disks between the partitions.

Each database requires the disks to be arranged for two different purposes; the database data files and the transaction log files. The data files require good random access, and therefore a striped array of many disks should be used. The log files require good sequential write performance, so each log file should be placed on its own high-speed array with good transfer rates.

Arrange the SQL server storage to accommodate the different types of data, distributing the load as appropriate. The following arrangements of storage might be considered for each data requirement:

If multiple database files are located on one partition, it may require regular file defragmentation to maintain performance.

Sizing considerations

Archive Shuttle uses a single Directory Database, and potentially multiple Item databases (depending on the number of source environment Vault Stores that are configured to migrate).

The Directory Database size requirement is 500 MB. However, to allow for temporary transaction log growth, it is recommended to ensure at least 2 GB is available for the database and logs.

Each item database has an initial storage requirement of 2 GB; 1 GB for the data file, and 1 GB for the transaction log.

The Archive Shuttle Item databases will grow depending on the items that are being migrated. A basic sizing guide for each item database is 1024 – 1500 bytes for each item collected plus 1 GB for static data, transaction logs and temporary data fluctuations.

In an environment when many source environment Vault Stores are considered for migration, Archive Shuttle recommends that you configure the SQL Server setting relating to the default database and transaction log location to a drive or folder with sufficient storage space. Archive Shuttle also recommends that all of the Archive Shuttle databases (Directory, and Item databases) have the SQL autogrowth feature enabled. Further, in this type of environment, Archive Shuttle recommends that you move the databases and transaction logs for the Item Databases to separate storage areas.

Virtualized infrastructure

There are important aspects to consider when installing SQL Server in a virtualized infrastructure. Follow the recommendations of the hypervisor vendor and Microsoft when sizing and configuring the environment.

The primary objective is to ensure that the resource requirements described above are dedicated to the virtual machine to ensure minimum impact to the performance from intermediate layers or co-existing guests.

The hypervisor should be type-1 (system-provided) to ensure the minimum impact on hardware resource requirements.

Note the following general guidelines:

·In a typical virtualized infrastructure, local disks might be used for the hypervisor and SAN-based storage for the guest operating system images and data file locations. The operating system and data storage partitions should be independent dedicated locations, as described above.

·Disk partitions should be aligned with the device sector or stripe unit boundaries to prevent unnecessary disk operations that can significantly impact performance.

·The disk partitions to be used for the database log files should be created as recommended by the hypervisor vendor for sequential access (possibly raw hard disks).

·The disk partitions to be used for the database data files should be created as recommended by the hypervisor vendor for random access (most likely virtual hard disks).

·Virtual hard disks should be created as fixed size and not dynamic.

·Avoid the use of hyper-threading by the hypervisor.

·Avoid the use of virtual machine snapshots, which can impact performance.

·The memory requirements recommended above should be dedicated and prioritized to the virtual machine to prevent dynamic allocation or sharing.

·The number of processor cores as recommended above should be exclusively dedicated to the virtual machine, and the processor priority and bandwidth set to provide the virtual machine with full utilization of the selected CPUs.

Maintenance plans

There are three aspects of SQL Server Maintenance Plans to consider:

·Database backups

·Index rebuilds and Updating Statistics