In-Depth

SQL Server 2005 Is the One to Beat

Super-fast performance, robust toolset and better security options make Microsoft's latest database management system a must for most shops.

• By Joanne Cummings
• 05/01/2006

SQL Server 2005's new SQL Server Management Studio (SSMS), its revamped Reporting Services, granular security controls, native Web services and XML support are just some of the new features that have users talking -- that and its speedy performance. Although most are taking a cautious approach to fully implementing SQL 2005 -- citing a lack of good documentation and strong migration tools (see "SQL Server 2005 Wish List") -- many have no qualms about an eventual upgrade. Most say the investment in both time and money will be worthwhile.

"It's absolutely worth the upgrade. The whole SQL Server 2005/Visual Studio 2005 integration is extremely developer-centric," says Jeremy Grecco, SQL Server DBA at Gainesville State College in Gainesville, Ga. "It shows that Microsoft went out and spoke with developers about what was needed, what was lacking in previous versions and how it would best suit developers -- and it delivered."

The Need for Speed
The first thing users notice about SQL Server 2005 is over-the-top performance, especially on a 64-bit platform. Although the Enterprise Edition of SQL Server 2000 supported 64-bit processors, SQL Server 2005 offers much more.

For example, while SQL Server 2000 maxed out at 64 processors and a database size of a million terabytes, the Enterprise Edition of SQL Server 2005 has no practical limit on either of those factors. SQL Server 2000 could address a maximum of 512GB of memory. SQL Server 2005 doubles that, fully leveraging the 1TB of addressable RAM supported by Windows Server 2003 Datacenter x64 Edition.

Brandon Haag, director of IT at Gainesville State College, recently went into production with the x64 Edition of SQL Server 2005 running on Windows Server 2003 x64 Edition. "We did some performance testing and it was unbelievable. I didn't have enough data to actually do loads that took more than a second or two," he says. "We ended up going back about five years and loaded it up with about 2.8 million records. Only then would it finally take a few seconds." Haag says he benchmarked the performance at 44,000 records per second on his dual-processor 64-bit machine with 4GB of RAM.

Studio Time
In SQL 2005, Microsoft replaced the old Microsoft Management Console (MMC)-based management utility with SQL Server Management Studio (SSMS). SSMS is an integrated toolset that combines what the Enterprise Manager and Query Analyzer tools used to do. This is proving to be another popular change. Developers like the new look and feel of the management interface, which is much like that of Visual Studio 2005, and having it all in one place enhances productivity.

Typically, my philosophy would be to hang back a bit, especially for a high-profile, high-availability database. But I haven't seen anything in SQL Server 2005 that would lead me to wait. -- Jeremy Grecco, SQL Server DBA, Gainesville State College

Alan Archer, an IT specialist for a government agency, says the combined tool saves him between 20 percent and 30 percent of his development time right off the top. When he writes a query with Query Analyzer, he no longer has to switch out of it and go into Enterprise Manager to check on the names of tables or columns. "Before, I ended up switching back and forth a lot [between Query Analyzer and Enterprise Manager], but in SSMS, everything is there," Archer says. "It's really nice not having to switch between applications."

Timothy Carroll, network manager at XS Inc., a Web and e-commerce software group in Research Triangle Park, N.C., agrees. "The [SQL 2005] management interface is much better than 2000," he says. "With the Query Analyzer and Enterprise Manager integrated into one package, you can just go from tool to tool and immediately get results."

Carroll does miss the MMC plug-in, however. "I like to have all my management tools in one MMC console, but Microsoft moved it out and made it its own stand-alone application," he says. "I think I see the reasoning behind this because it does so much more than the MMC plug-in did, but it would've been nice to have more control over some of the database features, user management and stuff like that through the MMC."

Developing on Overdrive
Grecco says that overall, SQL 2005 is a huge productivity boost. He figures his application development time on the new platform has been cut in half, thanks to several of the new features like Integration Services, Web services support and using the Common Language Runtime (CLR) within the database.

"I do a lot of work with DTS [Data Transformation Services], so I was very pleased with how DTS has evolved into Integration Services," Grecco says. "Before, with 2000, I would have to program some tasks manually using VBScript, but now there are several predefined tasks, for doing things like FTP in a reliable manner. In 2000, the FTP task really didn't do FTP very well. But now it's a predefined task and it works great."

Grecco also likes SQL 2005's new support for HTTP endpoints. Endpoints make it easier to develop and support Web services directly within the database, without needing IIS or some other Web server. "Putting the endpoints into the database lets you expose SQL Server as a Web service itself, without relying on MS XML or some toolkit to be installed," Grecco says. "It simplifies the process."

He also likes how Microsoft has integrated the Common Language Runtime (CLR) into the database. Introducing CLR into SQL 2005 helps developers write stored procedures, triggers, user-defined functions, user-defined aggregates and user-defined types using .NET languages like VB.NET and C#.

"I'm calling Web services from inside stored procedures within SQL Server 2005, and I'm using custom programmed stored procedures using the Visual Studio interface to do that," Grecco says. "I've placed these assemblies inside the database, registered them and I'm actually calling them from stored procedures." These are specific to SQL 2005 in terms of how it works with the CLR and .NET Framework.

All XML, All the Time
SQL 2005's native-XML support is another big hit. Archer is currently testing SQL 2005's Reporting Services with XML. After working through a small hiccup, he expects the new XML functionality will give his agency a lot more flexibility.

"We publish a lot of our stuff on internal Web sites and we use XML to call it. We also have our archive files in XML format," Archer explains. "So I think the XML rendering in the Reporting Services will be key."

Archer began playing with this feature a couple of weeks ago, although his first attempt printed the output as raw XML. "After I figured that out, it was great," he says. "I can see the new Reporting Services with the XML rendering opening up a whole new frontier for us." He plans to use the XML rendering to let users search archives, which are currently stored in XML, and generate reports on the fly with a Web browser.

The introduction of more granular security controls is an important development. Instead of having services like endpoints, Transformation Services or Reporting Services turned on by default, everything is turned off. This instantly makes SQL 2005 more secure.

Granularity is also a theme for user access. "You can get very granular, right down to the command for each object in the database," says Walter Crosby, a developer at Everyday Wireless, a company that provides GPS-based school-bus tracking. "You have to be careful with it because you could end up going nuts and spending most of your time just doing security management."

Others say the granular security controls make it easier to ensure that the right users have access to the right data. "I can delegate to users more responsibility without giving them system administrator rights," Carroll says. "That alone is a big plus to me."

Native encryption capabilities also earn praise. "Encryption is a big plus," Grecco says. "The student data we work with is sensitive by nature. Sometimes we have social-security numbers and the fact that we can encrypt those natively means we don't have to incorporate a third-party add-on to go ahead and perform that encryption for us."

To Wait or Not To Wait
The overall performance of SQL 2005 and its new features add up to a robust system that many companies will want to adopt, but when? "Typically, my philosophy would be to hang back a bit, especially for a high-profile, high-availability database," Grecco says. "But I haven't seen anything in SQL Server 2005 that would lead me to wait. It's stable and I haven't seen the number of bugs that would warrant waiting."

For some, the advantages of adopting SQL Server 2005 right away are many. "If someone's doing straight ANSI SQL and they aren't using any of the new features, then the benefit to going to 2005 isn't really there," Grecco says. "But for us, we're taking advantage of the CLR inside the database and we're taking full advantage of native XML in the database. And we got results right off the bat."