In-Depth
Exploiting SQL Server 2008 Through Code
SQL Server 2008 has a ton of new DBA features, but if you really want to make this thing go, just crank out a little code.
SQL Server 2008 is mostly in the domain of system and database administrators. But it's also a repository for data used by applications, which brings the product to those who aren't afraid of a little code. This new release has a lot of goodies that support application development directly.
I'll walk you through several of the new and improved features that I believe are most useful and interesting for the code savvy.
Management Studio Gets Several Enhancements
Since SQL Server 2005, Management Studio has been an extended version of Visual Studio. In 2005, the implementation was useful but a bit half-baked. In SQL Server 2008, Microsoft has made Management Studio a worthy environment for both administrators and developers. (Members of both groups who prefer command-line interfaces can also use the extensible Windows PowerShell.) Many features will be familiar to users moving to SQL Server 2008, but there are also many new features that make working with SQL Server much easier.
For coders, probably the nicest new feature in Management Studio is IntelliSense. Long a staple in Visual Studio, IntelliSense lets you write code in a Query Editor Window and reduce the number of times you have to go to Books Online to look up syntax or spelunk Object Explorer to find the name of that stored procedure you need. IntelliSense in SQL Server 2008 works largely as it does in VS, providing you with a list of objects and methods as you type code.
The second-most-coveted new feature in Management Studio is T-SQL debugging. You can now debug code directly from within Management Studio, which provides all of the features you expect, including the ability to step through code, view and change local variables, watch expressions and set breakpoints.
[Click on image for larger view.] |
| Management Studio now includes IntelliSense and syntax-error squigglies, features that will make developers far more productive when writing SQL code. |
Debugging in Management Studio is nowhere as deep as it is in VS, but it is functional and provides a lot of debugging tools. Of course, you can still enter a T-SQL debugging session from within VS, which means you can have the best of both worlds.
Object Explorer Window Now Useful
Management Studio's Object Explorer has long provided a nice view into the many persistent and virtual objects in a database and server. But the Object Explorer Details window, which by default appears to the right of Object Explorer when you first start Management Studio, was less than useless in SQL Server 2005. For the most part, it just displayed the same list of objects shown in Object Explorer. The tab took up space, and many users simply closed the window.
| Missing in Action: Features Dropped from SQL Server 2008
|
|
Software features are a lot like government regulations. Once a feature or regulation is implemented, it's almost impossible to get rid of it. It's certain that no matter how insane or arcane it is, someone out there is relying on it and will cry bloody murder if it goes away.
Nevertheless, Microsoft has dropped a few features and deprecated many others.
The Surface Area Configuration tool is history. It was a one-stop tool for enhancing the security of a database server, saving you the time of figuring out how to enable various features. You can still lock down SQL Server, but you must now do so with other tools such as SQL Server Configuration Manager, or through policy management.
Distributed Management Objects (DMOs) are now a legacy component. This means that you'll have to install this component separately if you want to use this feature to access the server and database programmatically. Regardless, you should migrate your code to use Server Management Objects as soon as you can, because DMOs are likely to not appear at all in the next version of SQL Server. The same goes for any Data Transformation Services packages you're still using: Be sure to migrate them to Integration Services.
The Web Assistant system stored procedures, such as sp_makewebtask and sp_runwebtask, are no longer available. These procedures let you create Web pages from within SQL Server automatically, but never gained any traction in the real world.
SQL Server 2008 also includes a lot of behavior changes that produce different results from statements and functions. A major change is that the REPLACE function now preserves trailing spaces rather than trimming them. If you rely on the old behavior, you should scan all your code and add an RTRIM function to drop the spaces.
All of these discontinued, deprecated and changed features are well documented in Books Online in the Backward Compatibility section of Upgrading to SQL Server 2008, along with recommendations on what new features to use instead.
–D.K.
|
In SQL Server 2008, Object Explorer Details often provides useful information, such as when you select the Databases node in Object Explorer. The views are highly customizable, letting you display exactly the information you find most useful.
Management Studio has a lot of other new features, and I discover more every day. Two more I recently discovered include the ability to query multiple servers by defining a server group, and the ability to configure the number of rows returned when opening a table to select or edit its contents.
2008 Adds New T-SQL Data Types
One of the sexiest new features in SQL Server 2008 is spatial data types. If you've ever worked with spatial data in a database, such as latitudes and longitudes or locations in a grid, you've probably developed your own types to support basic operations and conversions. It's not trivial code. But now, SQL Server 2008 has built-in support for two kinds of spatial-data systems. The geometry types support planar, or "flat-earth," coordinate data. The geography types store ellipsoidal data that stores locations on the earth's surface, a flattened sphere. Whether you're storing GPS data scattered around the globe, or need to store the coordinates that define complex shapes on a rectangular surface, you'll find a lot of features in these data types, along with dozens of useful methods.
[Click on image for larger view.] |
| The Object Explorer Details window lets you search for objects within a database or across all databases on a server using a wildcard search. |
It's common to store hierarchical data in a database, even though relational databases don't support hierarchies easily. You can create hierarchies with self-joins, but you generally need to do all the work. SQL Server 2008 introduces the HierarchyID data type, which greatly simplifies working with hierarchical data, complete with functions that make it easy to navigate hierarchies. It doesn't make data hierarchies a substitute for the native structure of XML data, but it does simplify operations. SQL Server maintains the structure of the data, supports random insertions and deletions, and supports location-based comparisons. You can index the data either breadth-first or depth-first, depending on the nature of the data and how your applications access it.
T-SQL Improvements
T-SQL in SQL Server 2008 hasn't received any major changes, but the new version includes many features that make code simpler and more efficient. There are a few syntax enhancements that developers will like, including a couple that make T-SQL seem more like a "real" programming language. You can now declare and initialize variables in a single statement.
One of my favorite new T-SQL features is table-valued parameters (TVPs). This one feature will single-handedly save you from a lot of ugly T-SQL code. Have you ever had to pass several pieces of data as a parameter to a stored procedure? Maybe it was a comma-delimited list or some other array-like structure. You'd have to write some nasty parsing code to split up the values, then probably use a loop to process the data. SQL Server 2005 introduced a table data type, but you couldn't pass it to a procedure.
TVPs solve these kinds of problems elegantly by letting you pass -- as the name suggests -- a table-valued parameter to the procedure or function. Then, in the body of the procedure, you can use the set-based features of SQL to process the data, such as by inserting it into a persistent table.
| Learn More About SQL Server 2008 |
|
Even though SQL Server 2008 is not a revolutionary improvement -- certainly not to the extent that some earlier versions were -- there's a lot to learn about all the new and different features. Here's a list of resources that I've found useful in learning about it. A number of the members of the SQL Server team have blogs, many of which have some great nuggets of information. Many are geared for admins, but most have plenty of good information for developers. Here are a few worth checking out:
Carpe Datum: Data Flotsam and Jetsam by Buck Woody
SQL Server Storage Engine, a group blog by members of the storage engine team
Microsoft SQL Server Development Customer Advisory Team, by the people who go out and solve customer problems
Laurentiu Cristofor's SQL Server Security Blog
SQL Server Engine Tips: Guidelines, best practices, TSQL and SQL programming tips and tricks
You can also find several handy non-Microsoft blogs. Some of the more prominent third-party blogs include:
SQLBlog.com: Some of the leading SQL Server experts on the planet blog here, including Kalen Delaney, Andy Leonard, Adam Machanic and many others. There are other independent SQL Server blogs, but this blog is a great single-stop resource.
Simple-Talk: Although Simple-Talk is affiliated with Red Gate Software Ltd., a commercial SQL Server and .NET tools vendor, the site has some great, free content from both Red Gate developers and others.
Erland Sommarskog's Web Site: Erland Sommarskog is a SQL Server MVP and a fount of information. His Web site isn't a traditional blog, but rather an outlet for many long articles he has written about SQL Server.
Blogs aren't the only good sources of information on SQL Server 2008. You can also find a list of white papers here. This site is a great resource and includes papers that cover a variety of topics. Some are high-level marketing types of papers, but most have a good level of technical detail. Some are brand-new for 2008, while others are revised from 2005.
–D.K.
|
Administrator Features Help Developers
SQL Server 2008 is a server application, and most of its features are focused on making it robust no matter what kind of loads applications throw at it. It's chock-full of administrative-support features that make it incredibly easy to install, manage and secure the database. Usually, there's a difference between the features that administrators and developers are interested in or use during the course of a typical day, but there are a handful of administrative features in SQL Server 2008 that are useful to developers.
One such feature is partition switching. Developers and admins have long used table partitions to store subsets of data in various tables, usually for performance or data-storage reasons. A common scenario is to store each calendar year's worth of transactions in a separate table and put each of the tables in a different file group. You can create a UNION query to extract and summarize the data when you need to access all the data, such as to create a report that spans all time. This works, but it requires some work to set up and often requires modifying code when adding a new partitioned table when a new year begins. (There are lots of other ways to do this.) You can use this kind of scheme to archive old data while keeping it available for analysis.
With partition switching, you can add a table as a partition to another table that's already partitioned, remove partitioning to create a single aggregated table and switch a partition from one partitioned table to another. You could always set up your own scheme to implement these features, but in SQL Server 2008 you can perform these tasks using the ALTER TABLE and ALTER PARTITION statements. The data itself is not changed or moved. The only thing that changes is the metadata for where it's stored. There are a slew of requirements to make partition-switching work, but they basically boil down to the fact that all of the involved tables must be identical in nearly every way.
| 2 Super SQL 2008 Admin Tips |
|
By Eric Johnson
There's no need to turn to third-party tools to keep your databases as small as possible. Compression is in SQL Server 2008 now.
SQL Server 2008 provides a feature that-in my opinion-has been far overdue: backup compression. For too long, if you wanted the benefit of compressed backups you had to look to a third-party tool. Now, backup compression is built right into SQL Server 2008, and what's even better is that it's easy to use. All you have to do is append the WITH COMPRESSION option to your backup statements and you're off to the races. This bit of code will back up the AdventureWorks2008 database using compression:
BACKUP DATABASE AdventureWorks2008
TO AdWorksBackup
WITH COMPRESSION
Using compression with SQL Server Management Studio is just as easy. Simply set the compression option on the Options page of the Backup Database dialog.
You may be asking: How effective is this compression? The answer can be tricky because it depends on the structure of your database and the type of data being stored. When I backed up my copy of the AdventureWorks2008 database, which is using about 700MB of disk space, I got a 147MB compressed backup file. Compare that to the 636MB file I got when not using compression.
Here's what else is cool: You can change the default compression behavior of your entire server. On the Database Settings tab of the Server Properties dialog, you can select the Compress Backup option. Alternatively, you can run the following T-SQL code:
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'backup compression default', 1
RECONFIGURE
The first command enables advanced options, and the second will make compression the default for all backups. If you go this route, you won't have to change a thing about your backup scripts in order to take advantage of compression. Now, just a simple BACKUP DATABASE statement will use compression. To run a backup without compression when it's the server default, simply use the WITH NO COMPRESSION option.
I hope you find this to be a useful feature. It's great for saving disk space and you no longer have to zip backup files before moving them over the network just to improve the copy time.
The Advantage of Application Roles
You've seen them, but have you really ever used them? Application roles have been around forever, but have you really ever implemented them in SQL Server? For that matter, do you really know what they are? Here's a quick look.
First let's look at how application security can be implemented in SQL Server. There's debate over which model is better, and I'm not endorsing any specific one. Regardless of whether you use SQL Server Logins or Windows Authentication, you still have to decide whether an application will use a single log-in to access SQL Server and all appropriate database objects, or if you will allow users to have their own log-in.
Whichever approach you take, each has its pros and cons. But let's look at the disadvantage to users having their own log-in. The biggest issue is that each user
log-in has access to your server and to one or more databases. Does the user need to delete data as part of his job? If so, that user will have this right whether he logs in via an application or directly to the server. Often, the application controls what can and can't be deleted based on a set of business rules; these rules usually don't exist on SQL Server. In short, if each user has their own log-in, they can access SQL Server directly and potentially cause damage.
This brings us to application roles. You create them and assign permissions to them just like regular database roles, but you can't put users in them.
Application roles provide the best scenario for application and user security. Here's how they work: You set up each user with an account on SQL Server with practically no rights. All they should be able to do is log in to the server and run a system stored procedure called sp_setapprole. This procedure accepts a couple of parameters, including name and password for the application role. Running sp_setapprole will immediately endow the user with all the permissions that you set up on the application role for the current session only.
What does this mean for security? As long as the password for activating the application role is only known to the application, your users won't have any rights when they log in to SQL Server directly. In order to have the permissions they need, they'll be required to use the application that knows the password and can unlock the permission for the application role. Now you can have SQL Server manage individual log-ins and still have a secure environment that uses the rules and filters in place within your applications.
Eric Johnson, SQL Server MVP, is the owner of Consortio Services LLC, a Colorado-Springs, Colo.-based provider of IT systems management and technology consulting. Johnson is also the president of the Colorado Springs SQL Server User Group.
|
Full-text searching has long been a feature that held a lot of promise but never seemed to get traction. One of the reasons is that it always seemed like an add-in that wasn't fully implemented. But with SQL Server 2008, full-text search is completely integrated into the database instead of being stored externally. Portions of full-text indexing and querying are now integrated into the query optimizer, so performance is much better, and there are more tools to extract useful data from the database. You might want to consider dumping all that gnarly T-SQL code you wrote over the last decade to give users flexible searches into their data and implement full-text searches instead.
[Click on image for larger view.] |
| The MERGE statement lets you insert, update and delete data in a table with a single statement. |
SQL Server 2008 supports Windows PowerShell, an enhanced, extensible scripting shell interface for developers and administrators who love the command line. SQL Server includes two PowerShell snap-ins that expose the hierarchy of database and server objects as paths (similar to file-system paths). On the surface, this sounds a bit like an abomination, but it can simplify getting around the database object model. Another snap-in implements a set of PowerShell cmdlets for performing a variety of actions, such as running sqlcmd scripts. PowerShell's a powerful tool, but if you love your mice and GUIs, you can opt not to use it.
SQL Server 2008 offers a lot to love for a developer. It isn't a revolutionary release, but it has enough great features to make it a slam-dunk upgrade as soon as your neighborhood system and database administrator lets you.
There's a ton of new stuff to learn in SQL Server 2008, though, so be careful to get up to speed on what's new and different.