What's new in SQL Server Yukon
Yukon is Microsoft's codename for the next version of SQL Server, due to be released in 2004. Tim Anderson talks to Euan Garden, Microsoft's Product Unit Manager for SQL Server Tools.
Euan Garden
Tim: What do you see as the most significant aspects of Yukon?
Euan: There are several. SQL CLR (Common Language Runtime) is significant, which is the ability to host CLR-based languages inside SQL Server. It gives developers language choice and architectural choice. We've tried to make it as seamless as possible to write code for the middle tier, move it into the database, and move it back out again. Depending on how you've written your code, only one or two lines need to change to run code middle tier versus server tier. There's also the language flexibility. We have an email alias, sqlwish@microsoft.com, where we get 20 or 30 feature requests a day. We had a lot of requests about 3 years ago to put VB in the database. We took a slightly different approach, instead of focusing on a language we focused on a platform, and we think that developers and customers are getting a much richer development platform because of that.
The investments we're making in business intelligence are huge. Reporting services are coming, and there'll be a version for SQL 2000 as well as a version for Yukon. The SQL Server 2000 version will ship before the end of this year. Because developers tend to build reports, the tool is integrated into Visual Studio .Net. In the new DTS (Data Transformation Services), we've made an investment there to provide a rich, performant environment. There's also the analysis services and data mining.
I think the XML-based technologies are huge as well. The new XML data type, the new XQuery-based technologies. And then there's the bread and butter, the database engineering, more scalability, more reliability, partitioning, faster backups, more reliable backups. We have to do that in every release. Gordon Mangione's, the VP of SQL Server, says that he never has to worry about that. It's in the genetic makeup of the team that in every release there's better scalability, reliability, security.
My own personal favourite, because it's the one I'm most responsible for, is the new management tools. The tools have some of the most significant changes that we've made in the management environment for SQL Server since 96 or 97.
Tim: What about support for command-line tools?
Euan: We're still going to support that operational guy who wants to run in the command-line environment. In Enterprise Manager, with every dialog, you can see and copy the script it generates. You don't have to run it, you get it to generate the script. The other thing is that the new command-line environment is very rich. You no longer have to mix batch files and osql, our command line tool. The majority of work that people were doing in batch files you can now edit in SQL Workbench, giving you version control and rich capabilities. We've actually pulled in more work in the operational command-line environment in this release than we have done since maybe SQL Server 4.2.
Tim: Is T-SQL still being developed?
Euan: Absolutely. We're actually putting more investment into T-SQL in this release than we did in SQL 2000 and in some ways more than we did in 7.0. The key thing is choice. T-SQL is absolutely the right language to use for certain circumstances. In dataset based operations and relational type operations it will always beat out VB.Net or a C#, because they're having ultimately to call T-SQL to do a SELECT, UPDATE or INSERT, but through a programming API. Ultimately it all ends up in some form of T-SQL. T-SQL is still absolutely the best language, and we're adding structured exception handling in this release. In a lot of places in SQL 2000 you have to use magic stored procedures to do things. We're adding these to the T-SQL grammar, so it's just part of the SQL language now to do some of these things.
There are cases however where T-SQL is not as strong. A good example is integer or floating point type numeric calculations. One of those is crypto. Let's say you get data in from your customer with their credit card number, and you want to encrypt that before you store it in the database. Doing that in T-SQL is impossible. Today you would have to write an XP (Extended Stored Procedure) in C++. That's a challenging prospect. Also, the XP architecture in SQL Server is not as secure or robust as we'd like it to be. Now I can write that in VB.Net in 10 or 15 lines, using the Framework classes which are very powerful in this space. I can write a trigger to do it, or write a stored procedure. Or, if I wanted to, I could write an encrypted credit card data type and add it to my system. Then whenever you did an insert it would take in clear text and it would store in the database an encrypted version. The CLR programming environment allows us to do stored procedures, functions, triggers, user defined data types, and user defined aggregates. It's not just stored procedures.
Tim: So I could create a User-defined function in a CLR language and call it from T-SQL?
Euan: Absolutely. Even a CLR object has a little T-SQL stub around it, that presents the metadata correctly to the rest of the environment. For example I can CREATE PROC AS EXTERNAL as opposed to CREATE PROC AS with T-SQL in it. And in the EXTERNAL command we point at a class and a method name inside an assembly. There's a T-SQL stored procedure header, it uses T-SQL types, we've got nullable types, and we use all of that inside the stored procedure.
One of the demos with the Whidbey version of Visual Studio shows how you can set a breakpoint in T-SQL, plus a breakpoint in managed code. We have an example where we step from a trigger, which calls into a stored procedure which is in managed code, which calls into a function in T-SQL, which uses a data type which is written in managed code, and you can step through all of that in the debugger and it's totally seamless.
Tim: Can I use my own custom objects in that managed code?
Euan: There are three security provision levels. There's Safe, External Access, and Unrestricted. Safe means you have access to in-proc data access and nothing else. So it's great for doing stored procedures and functions and data types and triggers. You can't leverage much of the framework and you can't access memory, and you can't access disk. From a DBA perspective that means I can't hang the system, I can't have permission problems going into the file system.
In the middle security bucket we open up a large portion of the framework. You can leverage the framework, and you can leverage your own classes if you want to. You can go off the box and access the file system and things like that.
In the unrestricted bucket you can do things with memory and threading, but you don't run in nearly as secure a sandbox. In the first two we tightly control memory and threading and security permissions, and it allows us to make SQL Server more reliable and scalable and predictable.
There are some caveats around what you do to get into the first two security buckets, the base one being whatever compiler you use must generate what we call verifiable IL. So, today we support VB.Net, C# and managed C++. We're working with other teams in Microsoft to see if their languages will work, and we're working with external language vendors to see if they generate verifiable IL.
Tim: What about J#?
Euan: It's an obvious candidate. It's not one we've committed to yet, but we still have some time to go in working with the different teams. We're being stringent in the requirements because what we want is to make sure that the system is really stable and reliable. For example, when you start running out of memory we must handle it correctly, recovering that memory so we don't just fall over. That's the focus for us, and what it means is that language vendors may have to make changes to be able to work, or they won't work in the first release and we'll work with them over the long term. But we're committed to VB.Net, C# and managed C++ in the initial release. We'd love to get J# in there, and we'd love to get Perl.Net, Cobol.Net and others.
MSDE, XML support and more: click here for part 2
Copyright Tim Anderson 25th July 2003. All rights reserved.
Sign up to be notified of future articles
Inside Microsoft SQL Server 2000 by Kalen Delaney
Details here for US
Details here for UK
SQL Server books
Bestselling SQL Server books