Something for (Almost) Nothing

When you upgrade to SQL Server 2005, you get some of its best benefits immediately

It's done! We launched SQL Server 2005, and celebrations around the world have marked the event. I hope many of you reading this column were able to attend one of the more than 100 launch events or get involved in the launch in some other way.All of us on the SQL Server Development Team are proud of the product we've shipped and hope that everyone who uses it is as excited as we are about all the great new things that SQL Server 2005 makes possible. Because SQL Server 2005 is the first new SQL Server version in five years, I'll take the next few months to look at many of the details you need to know to upgrade and take advantage of the product's great new features.

The topic of upgrading to SQL Server 2005 is always interesting. Some people consider upgrading to be only about moving an existing system to a new product version. Some people call this process migration; but when I think about migration, I think about moving databases from a different database product to SQL Server. SQL Server has many tools and features that will help you plan and execute a successful upgrade. (For an overview of how to plan a smooth upgrade, see Darshan Singh's November 2005 article "Step Up to SQL Server 2005," InstantDoc ID 47785.). For our purposes, though, let's move the discussion away from the nitty-gritty of physically upgrading the database and instead focus on how to get the most out of the new version and how long it will take to achieve the rewards. Upgrade doesn't stop once you've moved the database to SQL Server 2005 but continues until you're taking full advantage of all the features that are appropriate for your particular environment.

When I first started thinking about upgrade benefits, I thought about a time-versusreturn comparison—in other words, for the amount of time I spend upgrading, what reward do I get? Initial discussions with members of the product team (primarily Euan Garden) produced the concept of looking at upgrade in steps of 0, 30, 60, and 90 days, in which for each step, you'd implement a set of features that will add significant benefit to your system. After spending some time with this concept, we decided that in reality, it's never possible to state exactly how long a step will take.

As we began to prepare content for the SQL Server 2005 launch,I worked with other industry experts, including Kimberly Tripp.We discussed the time-versus-return concept and thought about using it for describing the upgrade process. Kimberly loved the concept but was also wary of specifying set time values for the various features and the returns they produce. Instead, she suggested keeping the same basic idea but using different terminology for each step: Upgrade Immediate, Minimal Work to Leverage, and Design and Architect.

Because many of you might not yet have had a chance to really delve into the newly launched SQL Server 2005, I want to focus this month's column on the Upgrade Immediate benefits, or what you get for free, right away, when you upgrade to SQL Server 2005. It turns out that you actually get huge benefits for practically no work at all, just by moving to SQL Server 2005. I assume that many people are using SQL Server 2000, but if you're still using SQL Server 7.0, the upgrade benefits grow exponentially. Upgrade Immediate benefits apply to DBAs or simply improve the overall health of your system. Let's look at some benefits that will have the greatest affect.

One interesting new feature that SQL Server 2005 provides is Dynamic Management Views (DMVs), which provide database views that contain details about the current state of the system and make it easier to diagnose database problems. DMV information isn't stored in the database but is collected every time you request a view.As soon as you install SQL Server 2005, you have access to DMVs that will help you understand exactly what's happening inside the database and let you diagnose problems that might have occurred during the upgrade process or that might have been lying dormant in your database since its inception.

Also, you can immediately take advantage of the much-improved security in SQL Server 2005. For example, administrators can now choose to enforce Microsoft Windows?style policies for all standard logins, which means administrators can implement a standard set of policies in both the OS and the database. In addition to policy enforcement, SQL Server 2005 also offers more advanced metadata security, which takes effect as soon as you upgrade and lets users see only metadata for objects that they have permissions on. So, not only is your server better protected against unauthorized logins but the database is more secure against unauthorized action by authorized users. And not only does your server and metadata stay more secure at the database server, but SQL Server 2005 also offers new ways to secure data that you transfer across the wire. SQL Server 2005 can auto-generate a self-signed security certificate, which it uses to encrypt the login packet of a network transmission.This certificate generation happens automatically, even if you haven't explicitly requested encryption of wire transmissions, thus making the system more secure by default.

SQL Server 2005 also provides many out-of-the-box features that will help make your database more available. Some require a little work, but others are immediately available to you. For example, if your database is set up to use not just a single data file but also secondary, non-primary data files, a new feature called Partial Database Availability will let your database remain online even if one of these secondary files becomes damaged. In addition, the ability to perform more granular restores—and to do them while the database is online—significantly adds to the availability of the database.With granular restores, you can restore a Page, File, Filegroup, or complete Database.

The SQL Server 2005 business intelligence (BI) space also includes some interesting"freebies." Probably the most interesting feature for most developers and DBAs comes in SQL Server 2005 Reporting Services. When SQL Server 2000 Reporting Services users want to print a report, they must export their report to a printable format (PDF,TIFF, or Excel) before performing the print operation. With SQL Server 2005, users can print the report directly by using the new Print button on the HTMLViewer toolbar. In addition to the new Print functionality,end users of SQL Server 2005 Reporting Services now get the ability to easily sort data in a report without having to re-execute the database query that produced the report. Both of these features are available to Reporting Services users the moment you upgrade to SQL Server 2005.

As you can see, right away you have huge incentives to upgrade to SQL Server 2005. But the goodies don't stop there.If you're willing to invest a little time and do a little more work, the rewards you'll reap will continue to grow.Next month,I'll look at the benefits you can get from the Minimal Work to Leverage (30-60 day) option.These benefits step outside of pure server improvements and affect the way applications interact with each other.

Until then I hope that you all have a copy of SQL Server 2005 and will be finding something useful to do with it during the Holiday season. Maybe you can use it as an excuse to get some time to yourself when you have had enough festive cheer.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.