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]
[TABLE]
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:
[TABLE]
[TABLE]
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:
[TABLE]
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 restor
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
e. 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.
[TABLE]
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
[TABLE]
[TABLE]
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:
[TABLE]
[TABLE]
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:
[TABLE]
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
