Learning SQL Server 2005

I've gotten a few emails and posts lately on how someone should go about learning SQL Server 2005. At first I'd start to point people to whatever current sources exist, but then I thought that perhaps it's worth a few minutes to list some places and ideas about how to tackle things.

And maybe reduce my email load slightly 🙂

I've divided this article up into the major sections that I think most DBAs will need to work with in SQL Server 2005. Notice that I haven't included Analysis Services. If you currently use this, you're a lot smarter and I and probably are way ahead of the curve already. I honestly don't really work with or understand the BI side of the house, so you'll have to query those guys.

I've compiled a list of various resources in each section, and then some general resources at the end. Some of the information at the end is a repeat of what's in the article, but if you don't want to read the rest of it, jump down to the end.

One short plug, the January issue of the SQL Server Standard was devoted to Yukon with 8 articles on various aspects of what's changed. Pick up the PDF today!!

Administrative Changes

Administration probably has more changes than most of the other areas. The paradigm shift with the CLR will be tough for many, but you could still develop against SQL Server 2005 much as you have with SQL Server 2005. You won't be taking advantage of the product, but you could do it.

Administration has fundamentally changed. The tools are new, the connections will be new, isql/osql are gone, it's a new world. So with that, let's look at a few resources that can help you get a handle on the product.

First, another plug, check out the first look at Yukon from yours truly. It's not the best intro, but it's one and it's mine 🙂 It's dated, but you'll get an introduction to the product. There's also a intro at Database Journal. You might want to install the current CTP, or pray that they add 2005 to the virtual labs, as you read through these. They'll help you to get oriented.

From there, the basic docs at MSDN and Technet are a good place to go as well. An Overview of SQL Server 2005 Beta 2 for the Database Administrator presents a high level view of some of the new features. If you're wondering about the tools, Enterprise Manager is gone, but Database Journal has a good two part series on tools and utilities.

Snapshot isolation is going to be a hot topic and one that will need some explanation. SQL Server 2005 Beta 2 Snapshot Isolation is a good place to start. This is one of the heavily touted features that should improve scalability by working on concurrency, so be familiar with it.

SQL Agent has changed and there's a white paper on that if you make heavy use of this scheduler. What's New in SQL Server Agent for Microsoft SQL Server 2005 Beta 2 tells about the changes in security, scripting additions, and the new subsystems. It's kind of an outline view, so don't expect too much. I haven't seen much else on this, so maybe I'll tackle some changes :).

Security

This has changed quite a bit. In the January issue of the SQL Server Standard, Brian Kelley wrote a great article on encryption in SQL Server 2005. Since this is a topic that lots of people have been looking forward to, it's a great start.

Database Journal also has a 4 part series on security in the new product. (one, two, three, and four)

Two slightly more formal articles are Security in SQL Server 2005 and Inside SQL Server 2005 Security .

Since security has been such a hot topic and with the changes to encryption, the addition of the schema to objects, the many more types of objects in which you can grant security and a paradigm shift from the SQL 7/2000 model, this is an area that you do want to at least be familiar with.

Development with SQL Server 2005

Development for SQL Server 2005 is kind of interesting. On one hand you can probably move the vast majority of what you've done over from SQL Server 2000 and it would work fine. You can continue to build your T-SQL applications and just take advantage of a slightly better query optimizer and more efficiently coded storage engine pieces.

On the other hand, the paradigm for developing inside SQL Server 2005 has vastly changed if you want to take advantage of the new features. There are two articles that I like for looking at some of these changes. The first is An Overview of SQL Server 2005 for the Database Developer and the other is SQL Server 2005: The CLR Enters the Relational Stage.

This first article is just as it sounds, an overview of the changes. The .NET integration and the CLR are mentioned, but there is a very light treatment of when to use these and then to stick with T-SQL. There are short paragraphs on many of the features, Web services, ADO.NET, MARS, SMO, etc. The second article takes a more in depth look at how the CLR fits into T-SQL and how you might want to take advantage of the new capabilities. A detailed example of a query in T-SQL and then a few .NET implementations may help you examine when to use each.

On some other topics, there is a white paper on MSDN about Multiple Active Result Sets (MARS) in SQL Server 2005. Since this is a new technology and one that could help you understand it. There is a look at how things worked in SQL Server 2000 and the changes. Having multiple result sets may not speed up your application, but it might. You still will have one multiplexed thread on the server, but it might make some things work faster for your application.

It's slightly dated, but there are a good places to look for the new managed stored procedures. First is an article at YukonXML.com called Managed Stored Procedures and User Defined Functions - Part I. Maybe he'll get to part 2 one day. There's also one at DevX called Writing Managed Stored Procedures in SQL Server 2005

There are tons on ADO.NET, but I won't really go into them here since they're mostly for the developers rather than the DBA. And I haven't read them since they're not really my area.

If you do DMO development, you'll want to look at our January SQL Server Standard issue, A Quick Introduction to SQL Server Management Objects (SMO), or Getting Started with SMO in SQL 2005. SQLDBATips, the last link, has a number of SMO articles on different topics that you might be interested in.

Integration Services

Integration Services (SSIS) has replaced DTS and is now a full blown programming environment. There wasn't much to see in the first beta, but after the second beta and some demonstrations at PASS, this might be one of the most exciting parts of SQL Server 2005.

One of the best articles and most interesting, might be Lessons from Project REAL, from the MSDN group and the SQL Server development team. This was an effort to find some of the best practices for a number of BI tasks, but quite a few on the ETL phase, which takes advantage of Integration Services. There's also a good DTS for SQL Server 2005 FAQ available.

The best site so far, since most of the others are fairly fragmented and only treat a small part of this product, is SQLIS.com, the site run by Darren Green and Allen Mitchell, the two that built SQLDTS.com a few years back. The content is coming slowly, probably since the product could change quite a bit before RTM, but this is a great place to check. We link to their new articles from SQLServerCentral.com, so you'll see them pop up in the daily newsletter from time to time.

There aren't too many other sites, but quite a few blog entries here and there. Jamie Thomson's blog is the only one that I consistently see writing SSIS information, but there are others from time to time. We try to compile them at Database Daily, so look for the blogs section in that newsletter each week. Jamie also wrote Transactions in SQL Server 2005 Integration Services on this site.

XML

Honestly, this is my weakest area with the product. OK, BI may be worse, but this is a close second. So I'm including some interesting links I've found, but I can't really vouch for the quality.

Conclusion

There's still lots of time before the final RTM version of SQL Server 2005. Last estimates have it still coming out this year, but late Q4. November seems to be a time that a great many products are launched, so I'd look for it there, though the PDC or PASS Summitt are outside possibilities.

A few more areas that you might be interested in are HTTP Endpoints and Web Services and the Service Broker, but since I haven't been looking in that area, I don't have much to say.

Hopefully I've given you some resources to get you started down the path of preparing for SQL Server 2005. The best advice I can give you is to setup a VM or spare machine to run it. Practice makes perfect, and trial and error will be the way that most of us get familiarity with the product.

Resources

New resources appear all the time, so searches will turn things up. We try to cull the best articles and information each week for release on SQLServerCentral.com for learning content and Database Daily for more timely content and blogs.

©dkranch.net 2005

Return to Steve Jones' home