Compare load balancing options for SQL Server 2000

SQL Server supports up to 32,767 simultaneous connections per single instance, but you should never attempt to run an application supporting this many users on a single server. It would bring the server to its knees. If you have more than several hundred concurrent users, you typically need to determine a game plan for distributing the load among multiple database servers.

SQL Server 2000 doesn't support out-of-the-box solutions for load balancing, but there are several ways to spread user activity across multiple servers. These load balancing options include:

TABLE OF CONTENTS    Backup and restore    Log shipping    Transactional replication    Database partitioning    Distributed partitioned views

Backup and restore

Return to Table of Contents

The easiest load-balancing solution involves separating online transaction processing (OLTP) and reporting functionalities by backing up the transactional database and restoring it to a separate reporting server. Most reporting environments can afford to have data a few hours old; you could back up data on the transactional server once (or several times) daily, then copy the backup to the reporting server and restore it there.

The following table shows the pros and cons of backup and restore solutions for load balancing:

Load Balancing pros and cons
Pros	Cons
Very easy to implement and maintain Allows both read and write access to the reporting database	Does not provide up-to-the-minute data Database is not accessible while being restored; no reports can be generated

Log shipping

Return to Table of Contents

Log shipping is very similar to backup and restore. Instead of running a full backup several times daily, log shipping is a way to restore transaction log backups continuously after the initial setup. The initial setup involves taking a full database backup, then restoring it on the reporting server.

The following table summarizes the pros and cons of log shipping:

Log shipping pros and cons
Pros	Cons
Very simple to set up and maintain Transaction logs can be restored faster than the full backups Database is available longer than with backup and restore Reporting database has more up-to-date data than backup and restore	Reporting database is available intermittently Data changes are not allowed on the log shipping destination server

The advantage of log shipping over simple backup and restore is that transaction logs can be restored considerably quicker than full backups, and the total time the database is unavailable is relatively small. You will also be applying transactions to the reporting server frequently; the reporting database will be closer to real time than it would be during a full backup and restore. However, you are likely to restore transaction logs more than several times daily, so the reporting database will be available intermittently. If you have reports that take a long time to run, log shipping restore might terminate such reports so the database can be restored. Yet another drawback of log shipping is that the database on the reporting server must remain in read-only mode. This should be acceptable for reporting applications.

Transactional replication

Return to Table of Contents

Log shipping is very similar to backup and restore. Instead of running a full backup several times daily, log shipping is a way to restore transaction log backups continuously after the initial setup. The initial setup involves taking a full database backup, then restoring it on the reporting server.

Transactional replication is a way of moving transactions from the primary server to the reporting server either continuously or on schedule. By default, replication translates transactions into INSERT, UPDATE and DELETE stored procedures affecting a single row. For example, the INSERT statement affecting 10 rows will be translated into 10 executions of a stored procedure that adds a single row to the affected table. Small transactions are committed very fast thereby making the data on the reporting server close to real-time.

The following table summarizes the pros and cons of transactional replication

Transactional replication pros and cons
Pros	Cons
Database is available continuously Near real-time data Read-write activity is supported	Requires much planning Data Slightly more complicated to configure than log shipping or backup and restore solutions Some maintenance is involved for changing database schema

Database partitioning

Return to Table of Contents

Load-balancing options discussed so far in this article separate the reporting and OLTP functionality, but that may not meet your needs, particularly if you are unable to handle all concurrent users with a single transactional server. You may have to partition your application so a subset of your users is directed to each transactional server. For instance, in some environments you can spread your user base by region: northerners connecting to a server (or a group of servers) in New York; midwesterners connecting to servers in Chicago; southerners connecting to Dallas and so forth. Another alternative is to partition your customers by last name. Users' last names starting with A through L could connect to one server and M through Z to another.

The following table summarizes the pros and cons of partitioning:

Partitioning pros and cons
Pros	Cons
Disseminates both read and write activity Each database is independent and can exist in a geographically separate location on a different network Since each database is independent, there are virtually no limitations to what you can do within each database	Requires much planning and knowledge of the application Application must have a way of identifying users and directing them to the right server You can customize your application to a certain user base that connects to a particular server

Distributed partitioned views

Return to Table of Contents

Distributed partitioned views (DPVs) implement the partitioning idea through a view defined on tables residing on multiple servers. Each table contains a subset of all data. When a DPV is queried, SQL Server automatically detects a particular server where the data satisfying this query happens to be stored. So the distributed partitioned views are transparent to the application. They are also relatively simple to set up.

Setting up DPV involves defining linked servers for each participating database server, creating tables on each server and defining check constraints on each table that makes up the view. Distributed partitioned views do have a few limitations, which you can read about in SQL Server online documentation.

The following table summarizes advantages and drawbacks of DPV:

Distributed partitioned views pros and cons
Pros	Cons
Partitioned views are transparent to the application. No need to set up separate data sources for each partition Separates both read and write functionality	Limitations of data types and column types All tables participating in the view must have the same structure Having views on every table in your database could be a maintenance nightmare Customizing your application on a single server could be difficult

About the author: Baya Pavliashvili is a DBA manager with HealthStream, the leader in online healthcare education. In this role, he oversees database operations supporting over one million users. His primary areas of expertise include performance tuning, replication and data warehousing. You can reach Pavliashvili at baya.pavliashvili@healthstream.com.

More information from SearchSQLServer.com