EDITOR'S NOTE: Portions of this article have been adapted from the upcoming book SQL Server "Yukon" New Features (McGraw-Hill).
Planned for delivery in late 2004 or early 2005, Yukon, the next version of Microsoft SQL Server, marks the end of a 5-year development cycle for Microsoft. The company has added so many new features to Yukon that it's impossible to list them all in one article. So, here are 13 valuable gold nuggets you can expect to find in the next major release of SQL Server.
1. CLR Integration
Without a doubt, the most significant new feature in Yukon is the integration of the Windows .NET Framework's Common Language Runtime (CLR) with the SQL Server database engine. Integrating the CLR with the database engine lets developers and DBAs create SQL Server database objects, such as stored procedures, triggers, user-defined functions (UDFs), and aggregates. This new feature directly addresses one of the few remaining shortcomings—the inability to use an OOP language to create database objects—that SQL Server has had in comparison with competing relational databases, such as DB2 and Oracle. With Yukon, you can use Visual Basic .NET, Visual C# .NET, Visual C++ .NET, Visual J# .NET, or any other .NET-compatible language to create database objects. Because the .NET languages are modern and fully object-oriented (OO), they're better suited to address complex business problems than the procedural T-SQL language is.
To use this new feature, you create an assembly by using a new SQL Server project type that Whidbey, the next version of Visual Studio .NET, will provide. (Microsoft plans to release Whidbey at the same time as Yukon.) Then, you load that assembly into Yukon and use an extended version of T-SQL's CREATE PROCEDURE, TRIGGER, or FUNCTION statement to create the new .NET-based database object.
The integration of the CLR with the SQL Server database engine is more than just skin deep—the database engine actually hosts the CLR in process. Yukon handles all the required memory management. The CLR database objects access the SQL Server database by using an updated version of ADO.NET in conjunction with a new SQL Server .NET Data Provider.
Unlike T-SQL assemblies, which don't have any native facilities for referencing resources outside the database, .NET assemblies are fully capable of assessing both system and network resources. Therefore, developing secure .NET assemblies is important.
In Yukon, Microsoft has integrated the user-based SQL Server security model with the permissions-based CLR security model. Following the SQL Server security model, users can access only those database objects (including .NET assembly objects) to which they have user rights. The CLR security model extends this security measure by providing control over the type of system resources that .NET code running on a server can access. You specify the CLR security permissions when you create the assembly. Specifically, you use the WITH PERMISSION_SET clause of the CREATE ASSEMBLY statement. Table 1 summarizes the CLR database security permissions that you can apply to SQL Server database objects.
As Table 1 shows, the SAFE permission restricts all external access. The EXTERNAL_ACCESS permission allows some external access of resources through managed APIs. Yukon impersonates the caller to access the resources. You must have the new EXTERNAL_ACCESS permission to create objects that use this permission. Only systems administrators can create objects with the UNSAFE permission because this permission allows external access to any resource, including the file system and registry.
Previous
Register
