Scaling the SharePoint SQL Server 2000 Database Structure

Particular concerns exist for SharePoint's SQL Server 2000 database in regard to scalability. Scaling a SharePoint solution is not complete unless considerations into the database are presented. Consequently, an understanding of how SQL can be scaled and what factors affect its scalability are required.

Understanding SQL Server Versions

For environments that need to achieve maximum scalability for SharePoint's SQL Server, it is recommended that SQL Server Enterprise Edition be installed with the Windows 2003 operating system. Unlike previous operating systems, SQL Server Enterprise Edition on Windows 2003 Enterprise Edition offers new levels of database performance as a result of improved technologies such as improved CPU load-balancing and scheduling, faster file system, and improved input/output (I/O) performance. Equally important, the Enterprise Edition is needed for all clusters because it has many features specifically designed for improved clustering. Understanding what the different versions of SQL Server provide is important. The following list summarizes what each version offers:

• SQL Server Standard Edition Supports up to four processors and 2GB of memory on Windows Server 2003 Standard Edition. It is sufficient to run SharePoint databases but does not provide any redundancy or database-specific scalability options.

• SQL Server 2000 Enterprise Edition This version scales best. It not only supports all the features of SQL Server Standard Edition, but it also scales up to eight processors and 32GB of memory on Windows 2003 Enterprise Edition (32 bitand 64GB of memory on Windows 2003 Enterprise Edition 64 bit). SQL Server Enterprise Edition can also scale up to 32 processors and 64GB of memory on Windows 2003 Datacenter (32 bit) and 512GB of memory on Windows Server 2003 Datacenter (64 bit) Edition. In addition, SQL Server 2000 Enterprise Edition ships a 64-bit version designed to run on highly scalable Itanium-based servers.

NOTE

SQL Server Enterprise Edition requires Windows Server 2003 Enterprise Edition or Windows 2000 Advanced Server. SQL Service Pack 3A is also required if SQL Server 2000 is installed on Windows 2003.

Optimizing Disk Layout for SharePoint's SQL Database

To achieve higher performance on hard drives, disk subsystems, or on SAN configurations isolate the transaction log from the SQL Server database files. SQL Server transaction logs should be stored onto their own RAID 1 volume, whereas SQL Server database files should be stored on separate RAID 5 or RAID 0+1 volumes. I/O operations to the transaction log are approximately 100% sequential and approximately 100% writes. Therefore, isolation increases performance because sequential writes of the database and transaction log can take place simultaneously. The Event Management database files should be configured on RAID 5 volumes for increased fault tolerance. It is also beneficial for Search to utilize separate controllers for the database and transaction logs to increase database performance.

Utilizing Log Shipping to Scale SQL Server

Log shipping is a recommended solution for creating redundant copies of databases from a source SQL Server to another target SQL Server. Normal procedure of log shipping involves the transaction logs being backed up from a source server, copied across to another target server, and finally restored. Log shipping requires SQL Server Enterprise Edition and is completed by using regular SQL Server Backup and Restore commands. These commands can also be automatically created by using a Database Maintenance Plan Wizard, which is included in SQL Server.

To provide high availability on SharePoint mission-critical databases, log shipping is adequate because it is inexpensive and relatively easy to administer. This is the most cost-effective method available for creating redundant databases compared to significantly higher costs associated with hardware cluster.

On the other hand, log shipping offers a slower and manual failover process that is not seamless. Therefore, log shipping may not be the best solution for providing an organization with high availability compared to Windows Clustering. All SharePoint database connections also need to be manually changed to reflect the name of the new target SQL Server.