Event Review: Technical Overview of SQL Server 2005 Scalability Features (Session TNT1-146)
Welcome to the material supporting page for this session. The materials on this page have been selected as the best matches to the content covered in the live event session.
Session Outline
This session examines several new features in SQL Server 2005 that can increase the scalability of many database solutions. We will look at tables and indexes and how they can be segmented into partitions on disk so they can be accessed separately and so many operations will no longer need to read the entire object on disk. This session also covers the improvements to replication that can increase scalability in Microsoft SQL Server 2005. Merge replication, in particular, features many new improvements that can significantly improve performance. In addition, true peer-to-peer replication topologies are now possible that can increase scalability and availability for both read and write transactions. We will also look at another new feature, database snapshots, which allow for a consistent point-in-time view of data and, while not a scalability feature, can increase scalability when used with database mirroring. The session also covers locking and blocking, which are often a major obstacle for scalable database solutions. Optimistic concurrency controls reduce locking by allowing read queries to query row versions of data that are maintained in tempdb. We will also cover several other improvements to SQL Server 2005 that can also increase scalability.
Session Agenda
- Partitioning
- Replication
- Database Snapshots and Database Mirroring
- Optimistic Concurrency Controls
- Other Scalability Enhancements
Session Media
This session consists of a Windows Media presentation and demonstrations:
| Download the full session
Download the full multimedia recording of this session. |
| Download all slides and transcripts
Download just the slides and transcripts of this session. |
| Demo: Partitioning Tables and Indexes
Configure table and index partitioning on two tables. These two tables already exist but will be dropped, the partitioning information specified, the table recreated, and the data bulk loaded into the tables. Execute a script to move data from one table to another table by switching a partition from the first table to the second table. Finally, view partition information and modify the partitioning, including using methods of removing partitioning from a table and alternate methods of adding partitioning to a table.
Download |
| Demo: Configuring and Using Peer-to-Peer Replication
Configure peer-to-peer replication between two instances of SQL Server 2005 on separate Windows Server 2003 servers. Execute a script that creates new customers and orders split between the replicated databases on the two instances of SQL Server 2005. Then verify that all the data was successfully replicated to both of the database replicas.
Download |
| Demo: Creating a Secondary Reporting Server Using Database Snapshots
Configure a database to be mirrored to a second instance of SQL Server 2005. Then create a database snapshot from the mirror database in the mirror database SQL Server 2005 instance. Finally, query the snapshot to verify that it is available for read-only queries for historical reporting.
Download |
| Demo: Increasing Transactional Scalability by Using Optimistic Concurrency Controls
Learn how the SQL Server 2005 default and earlier SQL Server versions handled locking for SELECT queries. Enable the two new Transaction Isolation Levels that do not acquire shared locks when reading data. Find out how these new Transaction Isolation Levels are implemented in SQL Server 2005.
Download |
| Demo: Increasing Scalability Using General Scalability Enhancements
Query and create indexes on tables with both standard and persisted computed columns. Create and use indexed views in situations not possible in SQL Server 2000. Then create an index that contains a column that is not part of the index key for performance and to avoid the index key length restrictions. Finally, execute a stored procedure that updates table in the AdventureWorks_Demo5 database while performing several index operations. Some of the index operations use the new ONLINE option.
Download |
Related Links
Use the following resources to learn more about topics covered in this briefing.
Third-Party Books
- Microsoft SQL Server 2005 New Features
Summary: Written by Michael Otey, senior technical editor of SQL Server Magazine, this authoritative resource introduces the new features in the Yukon release, providing a jump start for IT professionals transitioning from SQL Server 7 and SQL Server 2000.