Service Broker in SQL 2005 can be used to create queues for asynchronous processing. During peak load an order entry application can write to a queue which will be processed after the peak load passes. A shipping application could process the queue at a quiet time or even during the peak load with minimal impact on the throughput of the order entry application. Applications architected with service broker in mind can handle cyclical loads in a more scalable manner than if they were done synchronously.
Peer to peer replication can be used to implement a scale out topology where a group of SQL Servers will present identical data sets to the client. Instead of 1000 users connecting to a single SQL Server, they could connect to one of 10 SQL Servers in the peer-to-peer topology – each with an identical up to date data set. This scale out topology reduces contention and improves performance.
Table Partitioning can in some cases improve performance by distributing a large monolithic table into different file groups. The different file groups will reduce disk contention, and should the table partition be built around the queries offer much better performance.
Snapshot Isolation Level will present to each client a read only snapshot of the data at the time the transaction starts. This Isolation level allows greater concurrency.
Dig Deeper on Database software management
Related Q&A from Hilary Cotter
Learn the new components of Windows XP SP3 including black hole router detection and network access protection. Compare XP SP3 features to Vista and ... Continue Reading
Learn why Oracle VM would be chosen over Hyper-V or VMware and read an expert's recommendations for implementing Oracle VM. Continue Reading
Learn how Web services, as the latest incarnation of service-oriented computing, can offer numerous benefits to you and your clients. Continue Reading