 |
|
| SQL Server Tips: |
|
|
|
DATABASE MANAGEMENT AND ADMINISTRATION
Compare load balancing options for SQL Server 2000
By Baya Pavliashvili
09.22.2005
Rating: --- (out of 5)
|
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
[IMAGE] Backup and restore
[IMAGE] Log shipping
[IMAGE] Transactional replication
[IMAGE] Database partitioning
[IMAGE] Distributed partitioned views
[IMAGE][IMAGE] Backup and restore[IMAGE] 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
Pr...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
os 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
[IMAGE][IMAGE] Log shipping[IMAGE] 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.
[IMAGE][IMAGE] Transactional replication[IMAGE] 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
[IMAGE][IMAGE] Database partitioning[IMAGE] 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
[IMAGE][IMAGE] Distributed partitioned views[IMAGE] 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
|
|
|
|
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
