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

    Requires Free Membership to View

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:

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

Backup and restore

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

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

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

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

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

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



This was first published in September 2005

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.