Everybody's heard of SQL Server 2005's big new features: CLR integration, database mirroring, SQL Server Integration Services (SSIS), and the new SQL Server Reporting Services with Report Builder. With so many changes, it's easy to miss less obvious features. These five easily overlooked features add significant functionality to SQL Server 2005.
Import Export Wizard
In the final SQL Server 2005 community technology previews (CTPs) the Import Export Wizard mysteriously disappeared from the Start menu, making it look like the feature is gone. To find the Import Export Wizard, type dtswizard at the command prompt or open the Business Intelligence Development Studio (BIDS) and right-click the Project window.
Generate Script Wizard
A powerful SQL Server 2005 feature is the Generate Scripts Wizard. To run this wizard, open SQL Server Management Studio, then right-click a database and select Tasks, Generate Scripts. The wizard can script out all of the objects in the database including permissions, collation, and constraints. You can use this handy feature to save a database schema for versioning or create duplicate databases. And you can save the scripts to Query Explorer, the clipboard, or a file.
SQL Server Management Studio represents a huge change from SQL Server 2000 Enterprise Manager. Right away, you'll notice an initial dialog box that connects you to a SQL Server instance. However, you might not notice the dropdown menu that lets you connect to and manage the Database Engine, Analysis Services, Reporting Services, SQL Server Mobile, and SSIS. Selecting an option adds a new set of management nodes to the Object Explorer window.
Snapshot isolation isn't a feature that you can find in a menu; it's a programming feature that's designed to enhance database concurrency. With SQL Server 2000, concurrency is based on locking, which can cause blocking problems and impede performance. Snapshot isolation uses row versioning to avoid blocking scenarios. With snapshot isolation, when a read operation refers to rows modified by another transaction, the read retrieves the "snapshot" version of the row that was created when the update transaction started.
Peer-to-Peer Transactional Replication
One of the most important SQL Server 2005 changes is peer-to-peer transactional replication. If you've ever struggled with setting up immediate updating subscribers for transactional replication, you'll immediately appreciate the bidirectional replication capability of the new peer-to-peer model. Peer-to-peer transactional replication is designed to let changes made in different databases be replicated back and forth between multiple servers. If one server is unavailable, you can set up the system to route transactions to the remaining servers.