Scaling Up your database server is to support increasing numbers of users or applications. As the number of users increases, responsiveness can be affected by concurrency issues when multiple transactions attempt to access the same data.
SQL Server provides numerous isolation levels to support a variety of solutions that balance concurrency with read integrity. For row level versioning support, SQL Server 2008 includes a read committed isolation level that uses the READ_COMMITTED_SNAPSHOT database option and a snapshot isolation level that uses the ALLOW_SNAPSHOT_ISOLATION database option. Additionally, the Lock Escalation setting on a table enables you to improve performance and maintain concurrency, especially when querying partitioned tables.
Scaling Out provides great answers to the inherent limitations in scale-up architecture and is no stranger to Murphy's Law. Currently at this point in the technology lifecycle, scaling out requires increased management overhead that is potentially as great as the performance gains it offers. Even so, scaling out might be a viable solution to database implementations that have reached the limits of SMP scalability.
Scalable Shared Databases Data warehouses are typically used by multiple consumers of read-only data, such as analysis and reporting solutions, and can become overloaded with data requests, which reduces responsiveness. To overcome this issue, SQL Server 2008 supports scalable shared databases, which provide a way to scale out read-only reporting databases across multiple database server instances to distributes the query engine workload and isolate resource-intensive queries. The scalable shared database feature enables administrators to create a dedicated read-only data source by mounting copies of a read-only database on multiple reporting servers. Applications access a consistent copy of the data, independent of the reporting server to which they connect.
Data Dependent Routing When a company decides to scale out its database structure into a federated database, it must determine how to divide the data logically between the servers and how to route requests to the appropriate server. With SQL Server 2008, you can implement data dependent routing as a service by using Service Broker to route queries to the appropriate locations.
Peer-to-Peer Replication can provide an effective scale-out solution in which identical copies of a database are distributed to locations throughout the organization, so that modifications made to the local copy of the data are propagated automatically to the other replicated copies. SQL Server 2008 helps you to reduce the time taken to implement and manage a peer-to-peer replication solution with the new Peer-to-Peer Topology wizard and visual designer. By using peer-to-peer replication you can enable applications to read or modify data in any of the databases that are participating in replication. While previous versions of SQL Server required administrators to stop activity on published tables on all nodes before attaching a new node to an existing node, SQL Server 2008 enables new nodes to be added and connected, even during replication activity.
Query Notifications Most enterprise applications are based on a three-tier architecture in which data is retrieved from the database server by one or more application servers (often a Web farm), which is in turn accessed by client computers. To improve performance, many application servers cache data to provide quicker response times to users. One limitation of cached data is the need to refresh the data, because if the data is not refreshed frequently enough, users can receive stale data that is no longer accurate. Refreshing data more frequently adds overhead which can ultimately slow down the performance on the application server. SQL Server 2008 helps applications to use application cache more efficiently by using query notifications to automatically notify middle tier applications when the cached data is outdated. The application server can subscribe to query notification so that it is informed when updates that affect the cached data are performed on the database. The application server can then dynamically refresh the cache with the updated data.
Scalable Shared Databases for Analysis Services Although SQL Server 2005 Analysis Server cubes are usually read-only databases; each instance maintains its own data directory. Although you can create multiple copies of an Analysis Services database by synchronizing cubes across multiple servers, the cube synchronization process introduces latency that may be unacceptable in many business environments. SQL Server 2008 Analysis Services overcomes these issues by supporting a scale-out Analysis Services deployment in which a single, centralized read-only copy of the Analysis Services database is shared across multiple instances and accessed through a single virtual IP address.