2. T-SQL Enhancements
Although Yukon integrates the CLR with the SQL Server database engine, T-SQL isn't going away. CLR database objects are a great solution for database objects that require complex logic, but T-SQL is still the language of choice for row-oriented data access. One welcome enhancement to T-SQL in Yukon is the inclusion of full IntelliSense support. IntelliSense provides interactive parameter prompting and smart command completion for all T-SQL commands edited within SQL Server Workbench, which I discuss later.
Another area of T-SQL that Microsoft has improved is the TOP clause. In SQL Server 2000, you were forced to use a constant value in conjunction with the TOP clause. In other words, you could select only the TOP 5 or TOP 10 rows, in which the value of 5 or 10 was a constant. With Yukon, the enhanced TOP function lets you use an expression in conjunction with the TOP clause. The expression can be any allowed T-SQL expression, including a variable or scalar subquery. INSERT, UPDATE, and DELETE statements support the TOP clause.
Another important advance in Yukon's T-SQL is improved transaction-abort handling. Although you can abort a transaction in SQL Server 2000, you can't maintain the complete context of the aborted transaction, so you can't completely recover that transaction. In Yukon, new Try...Catch...Finally statements have been added to the T-SQL language. These new statements let you capture transaction-abort errors with no loss of the transaction context, so you have the option of completely recovering that transaction.
3. FileStream Support
Another important enhancement in Yukon is the addition of FileStream support, which adds a new dimension to SQL Server's ability to support binary large objects (BLOBs), character large objects (CLOBs), and other large objects (LOBs). Yukon's new varbinary(max) data type enables FileStream support. Microsoft introduced the new varchar(max) data type to make working with these large data types the same as working with standard string data. With SQL Server 2000 and earlier, you had to use an entirely different programming model to access BLOB, CLOB, and LOB data that was stored in the database. With Yukon's FileStream support, you can use the same programming model for varchar(max) data that you use to work with standard varchar data.
The varchar(max) data type is an extension to the image, ntext, nvarchar, text, varbinary, and varchar data types. Like the image, ntext, and text data types, the varchar(max) data type supports as much as 2GB of data. However, unlike the image, ntext, and text data types, the varchar(max) data type can contain both character and binary data. However, the most important difference is that the varchar(max) data is stored in the file system outside the SQL Server database. FileStream support maintains the linkage between the objects stored in the file system and their references stored in Yukon's relational tables.
4. Database Mirroring
Probably the biggest new feature in the area of availability is Yukon's support for database mirroring. Database mirroring protects against database or server failure by giving Yukon an instant standby capability. Database mirroring provides database-level failover. In the event that the primary database fails, a second standby database can be available in 2 or 3 seconds. Database mirroring ensures zero data loss. The mirrored server will always be in sync with the current transaction that's being processed on the primary database server.
You can set up database mirroring so that the database failover occurs automatically or manually. The manual failover mode is good for testing, but you'll probably want your production databases to fail over automatically. Database mirroring works with all standard hardware. You don't need any special systems, and the primary server and the mirrored server don't need to be identical. Database mirroring's impact on transaction throughput is minimal. In addition, unlike high-availability clustering solutions, you don't need any shared storage between the primary and mirrored servers.
As Figure 1 shows, database mirroring involves three main systems: the primary server, the mirrored server, and the witness. The primary server is the system providing the database services. Depending on the configuration of the mirrored server, the primary and mirrored servers can seamlessly switch roles. The witness acts as an independent third party that helps determine which system will assume the role of the primary server. Each system gets a vote as to which server will be the primary server; two identical votes determine the winner. This point is important because the communications between the primary and mirrored servers might be cut off, in which case each server would elect itself as the primary server. The witness would then cast the deciding vote.
Database mirroring works by sending transaction logs between the primary and mirrored servers. So, in essence, database mirroring is a real-time transaction log shipping application. You can set up database mirroring for one or more databases.
Prev. page
1
[2]
3
4
5
next page 
