SQL Server 2005 Scale-UpScale-Out


SQL Server 2005 Scale-Up/Scale-Out

The SQL Server 2005 database engine is a fourth-generation robust server that can manage terabyte-sized databases accessed by thousands of users concurrently. Additionally, when running at its default settings, SQL Server 2005 has features such as dynamic self-tuning that let it work effectively on laptops and desktops without burdening users with administrative tasks. SQL Server 2005 on a hand-held device extends the SQL Server 2005 programming model to mobile Windows devices and is easily integrated into SQL Server 2005 environments.

SQL Server 2005 works with Windows Server 2003 fail-over clustering to support immediate fail-over to a backup server in continuous operation. SQL Server 2005 also introduces advances log shipping introduced with SQL Server 2005, which allows you to maintain a warm standby server in environments with lower availability requirements.

Scale-Out vs. Scale-Up with Microsoft SQL Server

DBAs tend to think “scale-up” before “scale-out” when database servers begin to show signs of stress. When server response time starts to degrade because of increased workload or larger databases, the usual reaction to the performance issue is to add bigger, faster, and more expensive hardware.

Hardware vendors currently double performance of their devices every 18 to 24 months, so it may appm that throwing hardware at the load problem is a solution, but there is point of diminishing return. There are also many associated problems with constant hardware upgrades. First, hardware does have its limitations. Given that hardware performance doubles every two years and you have the money to upgrade your hardware every two years, what do you do when you max out your new system after only 12 months? Do you just suffer with poor performance for the next year? That is probably not an option, especially after that expensive upgrade.

Even though hardware vendors are making eight-processor systems for the Intel platform, with gobs of RAM, as well as fiber-meshed, SAN-connected computers, the problem of scalability still exists. Sooner or later, you will have to wait for your hardware vendor of choice to release the next version of super-hardware to catch up with your desired performance counters. It gets more complicated: when a system reaches a certain point, further scaling up becomes so expensive that the cost is not worth the reward. Even beyond the hardware and compatibility issues, you’ll probably encounter problems with software accessing the new hardware when you’re trying to scale up past a certain point.

Scaling out, however, still requires you to add servers. But instead of bigger hardware you are simply adding more systems, which are pretty cheap, and getting cheaper. Scaling out solves the scale-up scenario for database servers. Here, the design is not share-everything architecture; it is share-nothing architecture. Essentially, share-nothing architecture means that each computer system of the cluster operates independently Each system in the cluster maintains separate resources (CPU, memory, and disk storage that other systems cannot directly access).

Federated Database Servers

SQL Server 2005 improves on the support for updatable, distributed partitioned views. These views can be used to partition subsets of the rows in a table across a set of instances of SQL Server, while having each instance of SQL Server operate as if it had a full copy of the original table. These partitioned views can be used to spread the processing of one table across multiple instances of SQL Server, each on a separate server. By partitioning all or many of the tables in a database, you can use this feature to spread the database processing of a single Web site across multiple servers running SQL Server 2005. The servers do not form a cluster, because each server is administered separately from the others. Collections of such autonomous servers are called federations of servers. Federations of servers running SQL Server 2005 are capable of supporting the growth needs of the largest Web sites or enterprise database systems that exist today

To improve the performance and scalability of federated servers, SQL Server 2005 supports high-speed system area networks such as GigaNet. (Distributed database architecture is the subject of Chapter 8.)

The Handling of Very Large Databases

SQL Server 2005 has high-speed optimizations that support very large database environments. SQL Server 2005 can effectively support terabyte-sized databases.

The T-SQL BACKUP and RESTORE statements are optimized to read through a database serially and write in parallel to multiple backup devices. Sites can also reduce the amount of data to be backed up by performing differential backups that back up only the data changed after the last backup, or by backing up individual files or file-groups. In SQL Server 2005, the time required to run a differential backup has been improved, making it proportional to the amount of data modified since the last backup.

Multiple bulk copy operations can be performed concurrently against a single table to speed data entry. The database console command utility statements are implemented with reduced locking requirements and support for parallel operations on computers with multiple processors, greatly improving their speed.

Operations that create multiple indexes on a table can create them concurrently.

Intra-Query Parallelism

When running on servers with multiple multiprocessors, or CPUs, SQL Server 2005 can build parallel execution plans that split the processing of a SQL statement into several parts. Each part can be run on a different CPU, and the complete result set can be built more quickly than if the different parts were executed serially.




Microsoft SQL Server 2005. The Complete Reference
Microsoft SQL Server 2005: The Complete Reference: Full Coverage of all New and Improved Features
ISBN: 0072261528
EAN: 2147483647
Year: 2006
Pages: 239

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net