Getting the most out of SQL Server 2008

By David Worthington

April 1, 2009 —

SQL Server 2008 has been credited for its markedly better scalability and performance, but it also has an assortment of capabilities that fundamentally change how enterprise developers interact with the database.

Microsoft made several changes to SQL Server from version 2005, ranging from the introduction of new developer tools to fulfilling long-sought developer requests in coding, tools, management and performance monitoring.

Among the added capabilities is a new merge operator in Visual Studio 2008, a “great concept” that has been needed for years, said Chris Menegay, a principal consultant for Notion Solutions and a Microsoft regional director (recognized by Microsoft’s Developer Platform evangelism group for technical expertise).

Merge can join a data source with a target table or view, then perform multiple actions (insert, update, delete) against the target based on the results of that join, according to Microsoft TechNet.

"The merge command can do in one line of code what would have taken 10 before," when they would have had to write multiple CASE statements, said Tim Huckaby, CEO of InterKnowlogy, a .NET solutions provider. "From a plumbing perspective, merge changes how to do all of our updates."

Menegay said that programming managers should be certain that merge is supported in their code generation tools and unit tests before they adopt it. "It is a core change to how people interact with the database, and managers should stop people from doing it until everyone on the team does it."

Huckaby was less cautionary, stating that while merge makes programming easier, developers will not go back and refactor or remove old code that works just because Microsoft released a new feature. "In my opinion, it would be used for new applications," he said.

He added that merge would be particularly useful for business intelligence and data warehouses for pulling data from a transaction database for storage.

Another one of the most welcomed changes is the inclusion of a data type for storing dates, said Menegay. "[SQL Server] 2005 made doing internationalization more difficult. You used to have to parse out everything that you did," he said.

"There was nothing like formatting a date from SQL in C#," Huckaby remarked.

Microsoft also added a hierarchical data type that makes it easier to build queries by eliminating confusing recursion functions, said Menegay. He also praised Visual Studio's new data-value change-auditing capabilities, which he says will help developers examine transactional history.

"Management might want an application to take notice when someone changes the value from a time clock," he explained.

Another change that could ease things for developers is the inclusion of IntelliSense code completion in SQL Server's Management Studio, said Bob Erickson, executive vice president of Interlink Technologies, a fully owned subsidiary of Mediterranean Shipping, which has been featured by Microsoft in SQL case studies. "It saves remembering the syntax of data schemas and code," he explained.

Tooling up
Microsoft has also made it simpler for enterprises to follow agile development processes without worrying about database design, said Huckaby. "Language Integrated Query allows developers to query the database themselves without having to worry about…database people. They [Microsoft] also have the ADO.NET Entity Framework," which is an "amazing way" to map data, he added.

Likewise, Visual Studio 2008 makes it easier for developers who aren't database professionals to model a database without ever touching SQL Server, Huckaby noted, adding that databases do not design themselves.

"Microsoft is doing a darn good job giving application developers plumbing and tools, but it still takes design." he said. "Scrum and Agile work so well because people are forced to talk and design as a group, as opposed to gurus in each camp meeting in the middle."

Likewise, integration with Windows APIs may benefit some developers. SQL Server 2008 includes a feature called Filestream that allows developers to take unstructured data stored on NTFS-formatted drives in Windows systems but reference it in the database, Huckaby said. That makes it easier for developers to maintain applications because the data lives in one place, he explained.

"Nowadays, Web applications with pictures and streaming media typically have relational data stored in SQL Server and movies stored on streaming media servers. SQL Server 2008 puts it all in one database," said Huckaby.

Changing management
In the same vein, users no longer have to manage SQL servers separately from each other. "Customers have had to perform management, synchronize and patch servers one by one," said Patrick Hynds, president of security consultancy CriticalSites and a Microsoft regional director.

"There is now a policy-based management system that people will benefit from if they look into [it]. It treats SQL servers as a single entity," he explained.

Configuration servers make it possible for developers to execute queries against a set of servers by creating groupings, Erickson added. "It's one of the top out-of-the-box features."

Additionally, clustering servers has become more straightforward, Hynds said. Previous editions of SQL Server had problems when there were differences between environments, whereas SQL Server 2008 checks for differences, he explained.

"You could survive with clusters before, but clustering is matured now and easier to live with," and with time, "clustering will become less the domain of specific experts," Hynds said.

Enterprises will also benefit from a new activity monitor in SQL Server's Management Studio that provides at-a-glance views of performance issues, said Erickson. "They can see the most expensive queries running, get a picture of resources, and then start planning action to correct the problem." A resource governor also balances resource allocation.

Performance collection helps developers understand which queries are taking the longest, added Hynds.

Erickson noted that there is a "sleeper" feature in SQL Server 2008 called extended events. While extended events does not have a user interface, administrators can still write scripts with event triggers to use it to capture detailed information from the engine with little performance impact, he said.

"SQL's trace features create a performance problem and can cause a heavy load. Events at the engine level have an impact that is not even noticeable, even at the call stack," he said.

Other management features that could give SQL users immediate benefits are backup and table compression, said Erickson. Compression saves "tremendous" disk space and speeds up backups and restores, he said. "Backups that are running on a 2005 systems and restored on 2008 even run faster," he noted.

More complete data encryption has made SQL Server 2008 more compliant to the Health Insurance Portability and Accountability Act, Hynds said. "If someone stole a disk, the files and database are encrypted. I bet the average developer doesn't know [about it]."

Scaling up
Although SQL Server 2008's developer-oriented features are compelling, they pale in comparison to the markedly better scalability and performance that it offers, said Huckaby.

Its performance increase is especially heavy in business intelligence analysis services, said Erickson. "The processing of cubes runs a lot faster. It more intelligently deals with cells in terms of processing, and that translates to faster performance."

"SQL Server has had a reputation for productivity and [being] easy to use," said Hynds. "Now it offers scale, performance and maintainability. That is the key to being more competitive."

"Of all of the teams at Redmond, this is one of the teams that has its act together," said Huckaby. "Upgrading is a no brainer; there is nothing racy about it—just do it," he said. He also noted that customers that have enterprise licensing agreements own version 2008 already.

Erickson recommends that enterprises ease into an upgrade by starting with a test server that, for instance, keeps compatibility with SQL Server 2005. Once developers verify that their full applications are working, they should turn on 2008 mode and see if it still works, he said.

Related Search Term(s): Microsoft, SQL Server

Share this link: http://www.sdtimes.com/link/33370

Microsoft readies small business and mid-market server offerings Microsoft's Essentials series of Windows Server products will become available in November, bringing with it a price increase for Small Business Server, as well as a new mid-market product.

Why program Microsoft SQL Server from .NET? Microsoft's José Blankley gives three situations in which programming the database from .NET is a sound option. He says that .NET is good for building core database constructs, for instance.

Microsoft ships SQL Server 2008 Nearly six months after its gala launch event, SQL Server 2008 is now in the hands of customers. Its arrival formalizes the ADO.NET data entity framework and the server also includes new facilitates for data warehousing.

Add comment

Name*
Email*
Country

Comment
Preview

News on Monday
more>>
SharePoint Tech Report
more>>

Download Current Issue
ISSUE 3/15/2010 PDF

Need Back Issues?
DOWNLOAD HERE

Receive the print Edition?

Google Code turns 5
Google Code Turns 5, and adds a Paxos Algorithm to make the system more stable and reliable.
03/17/2010 11:16 AM EST

Test your Visual Studio 2010 know-how
Microsoft is offering free beta certification exams for Visual Studio 2010.
03/17/2010 11:08 AM EST

Microsoft lifts the hood on IE9
Microsoft is previewing IE9.
03/16/2010 01:10 PM EST