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:
·Index rebuilds and Updating Statistics
|
|
NOTE: The information provided is for guidance only. Often a SQL DBA team will have their own plans and procedures in place for applications relying on databases. |
Database backups
Archive Shuttle recommends recommend that you perform regular backups of the Archive Shuttle Directory Database and the Item Databases. These databases contain important data relating to the configuration of the migration as well as the progress, down to the individual item level.
|
|
NOTE: It is a best practice to perform nightly full backups of each of the databases. This will ensure the shortest recovery time. |
Other backup types are supported; however, it may lengthen the recovery time if a database restore is needed.
Details for configuring database backups for specific versions of SQL Server is outside the scope of this document.
Index rebuilds and statistics updates
Archive Shuttle recommends that you perform weekly index rebuilds on the Archive Shuttle Directory Database and each Item Database. This is particularly important when Item Gathering is being performed where a large amount of data is being added to the Item Databases.
Archive Shuttle contains a new Index Fragmentation page in the Admin Interface. This page shows the current levels of Index Fragmentation in the Archive Shuttle databases. This page also color-codes the rows to help highlight any potential problems, as follows:
Yellow Page count > 1000, average fragmentation 10-30%
Red Page count > 1000, average fragmentation >30%
|
|
NOTE: Perform online Index Rebuilds in SQL Server weekly. |
Details for configuring Index rebuilds for specific versions of SQL Server is outside the scope of this document. It is important to note that there are editions of SQL Server where the Index rebuild operation can be performed with the index online, and there are editions of SQL Server where the Index rebuild will cause the index to be made offline while the operation is performed. According to Microsoft documentation (referenced below), online index operations can be performed in SQL Server Developer Evaluation and Enterprise Editions.
http://technet.microsoft.com/en-us/library/ms186880(v=sql.105).aspx
If the edition of SQL Server in use for the migration does not support online index rebuilds, make sure to schedule the rebuild operations when there is a low level of activity from Archive Shuttle.
It is also recommended to do the Update Statistics maintenance task, following an index rebuild.
|
|
NOTE: In many environments performance has been seen to dramatically increase if index rebuild and statistics updates are performed daily. Check the index fragmentation page frequently to see if this will help in a specific environment. |