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:
TABLE OF CONTENTS Backup and restore Log shipping Transactional replication Database partitioning Distributed partitioned views |
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:
|
|
|
| Pros | Cons |
|
|
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:
|
|
|
| Pros | Cons |
|
|
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:
|
|
|
| Pros | Cons |
|
|
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:
|
|
|
| Pros | Cons |
|
|
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:
|
|
|
| Pros | Cons |
|
|
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
- Tip:Log shipping a replicated database
- Book excerpt: Understanding replication methods: Snapshot, transactional and merge
- RSS: Sign up for our RSS feed to receive expert advice everyday
This was first published in September 2005
Join the conversationComment
Share
Comments
Results
Contribute to the conversation