Blog Host: Kevin Kline - Director of SQL Server Solutions, Quest Software READ ENTIRE BIO

These features are very powerful and represent a huge step forward in getting your database up and running quickly. Here's a quick laundry list of these new backup and recovery features and information on where you can find out more about them.

Backup and restore error reporting
In older versions of SQL Server, if a backup or restore process encountered an error, it would stop. Now in SQL Server 2005, the BACKUP and RESTORE statements have a new option called CONTINUE_AFTER_ERROR, which enables you to keep processing despite an error. That means you can get a database up and running despite a small, single error. If you get multiple errors, this option lets you assess the scope of the problems before continuing.

Early restore access
SQL Server 2005 Enterprise Edition allows you to access the database after the roll-forward phase of the restore operation. In the past, you couldn't access the database until after the roll-forward and the roll-back phase. Unfortunately, this feature is only available in Enterprise Edition. But if you're running a mission-critical application, it's worth it.

EMERGENCY option
In the old days, when a database was marked as suspect, you pretty much had to recover from your last good backup. Now, if a database is marked as suspect during recovery, it can now be placed into EMERGENCY mode. EMERGENCY mode opens the database in read-only mode to all members of the sysadmin fixed server role so that you can diagnose problems and/or retrieve any available data.

Online restores
You can now access databases when a partial database restore is being performed on a database file or page. You won't be able to access the part of the database being recovered, but you will have access to all other data. In earlier versions of SQL Server, no one could access a database during any part of the restore operation.

If you want to avoid those late nights that I'd experienced trying to get my databases back up and running, then you should definitely learn more about SQL Server 2005's backup and recovery features. For more information on high availability and recovery, check out these articles on the SearchSQLServer.com and DatabaseJournal Web sites.

Enjoy,
Kevin

P.S. I'll be speaking at the PASS conference next month. I hope to see you there!

Posted by Kevin Kline Multiple Active Result Sets (MARS)
25 AUG 2005 21:49 EDT (01:49, GMT)
SQL Server 2005 and Visual Studio 2005 introduce the ability to return multiple result statements simultaneously from a single connection. Older versions of SQL Server could only return one result set from one statement per connection at any given time. That meant that no new statements could execute until all the already active result sets were retrieved.

Microsoft calls the environment under which statements execute the batch execution environment. There are a number of elements that make up the overall batch execution environment, including:

• The current database context
• Any execution state variables, such as @@ERROR, @@ROWCOUNT, @@FETCH_STATUS, @@IDENTITY, etc.
• The Security context (i.e., the user or application role)
• Any SET option values that are enabled, like ANSI_NULLS, DATE_FORMAT, LANGUAGE, TEXTSIZE, etc.
• Top-level temporary tables

There's a lot for developers to learn about MARS if you haven't read up on the topic yet. For more information, the check out the TechNet article Multiple Active Result Sets (MARS) in SQL Server 2005 and another article on MSDN.

Enjoy,
Kevin

P.S. I'll be speaking at the PASS conference next month. I hope to see you there!

Posted by Kevin Kline Database partitioning
23 AUG 2005 23:39 EDT (03:39, GMT)
Partitioning tables and indexes have been around in other database platforms for a long time. So, in a sense, SQL Server is just catching up to the other major database platforms with the release of SQL Server 2005.

In SQL Server 2005, table and index partitioning is used to improve performance on extremely large tables. Since each table and index partition can be accessed independently from the others, you have a powerful new way to divide up the I/O that might otherwise encumber an entire table, such as index rebuilds.

SQL Server 2000 had some limited partitioning capabilities using a feature called Distributed Partitioned Views (DPV). But DPVs proved to be cumbersome and hard to manage because each partition of the DPV had to reside on a separate SQL Server instance. I only saw it implemented in the field in a few shops and often without much enthusiasm.

SQL Server 2005, on the other hand, now has a rich array of partitioning capabilities. In essence, you define a partitioning function that determines where each row of a partitioned table or index will reside. For example, you might commonly partition on a date column (such as ORDER_DATE) or on an alphanumeric column (like CUSTOMER_NAME).

Creating and managing partitions are a little more difficult than creating a table. But it's certainly not the near rocket science of pre-SQL2K5 days. If you're struggling with managing a VLDB or are trying to find new ways to improve performance (particularly when the database is I/O bound), then I encourage you to explore database partitioning in SQL Server 2005.

For more information, the Database Journal has a good how-to article by Marcin Policht. Kim Tripp, one of my favorite speakers at PASS and other events like Microsoft TechEd, has a great white paper on partitioning, too.

Enjoy,
Kevin

P.S. I'll be speaking at the PASS conference next month. I hope to see you there!
Posted by Kevin Kline SQL Server Migration Assistant (SSMA)
22 AUG 2005 22:28 EDT (02:28, GMT)
The SSMA is a set of tools used to migrate users from other database platforms to SQL Server 2000 and 2005. The SSMA automates almost every aspect of database migration thereby reducing the time, cost and risks of a major migration project.

When I was an enterprise DBA, we had to do two major projects converting Oracle databases to SQL Server. The easy part was translating the data types of one schema to the other and then transferring the data. The hard part (and it was very hard) was in translating the copious amounts of Oracle PL/SQL code within triggers and stored procedures into comparable code on SQL Server.

Now, Microsoft has introduced a toolkit that will greatly help! The initial release addresses Oracle versions 7.3, 8, 8i, 9i and 10g. But future versions will address the other major database platforms.

The SSMA contains these major components:

• Migration Analyzer -- evaluates the complexity of the migration project.
• Schema and Data Migrator and SQL Converter -- performs database conversion and provides a powerful IDE for cross-platform database developers.
• Migration Tester -- provides automated validation of the migration process.

To get started on learning about the SSMA, go to the Microsoft SQL Server Migration page. You can find white papers, methodology information, FAQs, the product download itself and much more.

As an aside, Microsoft is holding the Microsoft Cost Chopper competition. I'm honored to say that I'll be acting as one of the judges on this contest. So if you've migrated (or will be migrating) from Oracle to SQL Server, please enter the contest! I'd love to see your entry!

Cheers,
Kevin

P.S. I'll be speaking at the PASS conference next month. I hope to see you there!
Posted by Kevin Kline Common Language Runtime (CLR)
19 AUG 2005 21:49 EDT (01:49, GMT)
The CLR is a feature of some controversy. So what is this feature and what is the controversy? The CLR is a means by which developers and DBAs can write stored procedures using .NET languages like C# and VB.NET rather than Transact-SQL. Many developers love it, since they're very familiar with these languages, and become productive right away. Many DBAs, on the other hand, don't like it because they're usually required to support and debug stored procedures on the servers they are responsible for. But most DBAs are good with Transact-SQL and usually don't know the .NET programming languages. Thus, for DBAs, the CLR represents an opportunity to either 1) take on more responsibility without the tools and education to manage the new feature set or 2) lose control of an important aspect of their SQL Servers to developers.

Another irony of the CLR is that IBM's DB2 product actually implemented the CLR before Microsoft did. However, if you know DB2, then you know that it only recently supported stored procedures at all and even then in a fashion very similar to the CLR. Thus, implementing CLR stored procedures was not really a big leap from the way that DB2 already utilized stored procedures, whereas Microsoft had to devise a whole new means of compiling and caching CLR stored procedures.

CLR does have some benefits compared to Transact-SQL. It can perform certain types of algorithm-intensive operations much faster than Transact-SQL. If it were my database, I would probably only allow CLR coding on certain user-defined functions. Aside from that one situation and in most situations I've seen, Transact-SQL is the better way to go, especially because Transact-SQL handles set processing of data so much better.

You can read an interesting comparison of the CLR and Transact-SQL by Randy Holloway at DevX.com. For Microsoft's take on CLR programming, read Balaji Rathakrishnan's article on MSDN. (Balaji is the team lead within the Microsoft dev team for SQL Server's CLR and Transact-SQL features.) There's another good article about the CLR at SQLTeam.com, run by fellow SQL Server MVP Bill Graziano.

Cheers,
Kevin

P.S. I'll be speaking at the PASS conference next month. I hope to see you there!
Posted by Kevin Kline Database snapshots
18 AUG 2005 20:48 EDT (00:48, GMT)
Database snapshots are another new availability in SQL Server 2005. (This feature has been around in Oracle for quite a while now.) Database snapshots provide you with a very quick and easy way to revert to a specific point in time and, hence, a known point of data consistency.

You can take many snapshots on a source database and they continue to exist until explicitly dropped. Database snapshots provide an incredibly efficient way to build reporting databases quickly and easily. It is possible to use snapshots to improve performance since you can separate the I/O generated by a reporting application that hits a snapshot from the I/O generated by a general purpose OLTP application hitting the source database.

Snapshots are read-only and static copies of the source database. The snapshot, which must always reside on the same server instance as the source database, records the current state of the source database as if all active transactions were rolled back. Any transactions that occur between now and the time the snapshot was take can be recovered by reverting to the snapshot at the time it was taken.

Database mirroring can be implemented in one of three modes: high availability, high performance and high protection. Each mode offers a different mix of speed, availability and recoverability.

For more information, read Ron Talmage's white paper, Database Mirroring in SQL Server 2005. The Professional Association for SQL Server hosted a TechNet chat about database mirroring back in October 2004.

Cheers,
Kevin

P.S. I'll be speaking at the PASS conference next month. I hope to see you there!
Posted by Kevin Kline Service Broker
17 AUG 2005 22:47 EDT (02:47, GMT)
Service Broker is sort of a stealth feature in SQL Server 2005. I'd suggest that the majority of people studying SQL Server 2005 have really paid more attention to the flashier features in the beta product.

So what is Service Broker? Service Broker is a means of ensuring guaranteed asynchronous messages using message queues and messaging routes between two or more SQL Servers. That may not sound dynamic or exciting, but it really is profound in its long-term implications because many enterprise applications contain an asynchronous, queued component. I believe that, over time, Service Broker will become a significant new platform to extend the capabilities of many existing applications and will, in fact, become a platform for a whole new range of applications that weren't possible with earlier versions of SQL Server.

For more information, read Roger Walter's white papers, An Introduction to Service Broker and Building Reliable, Asynchronous Database Applications Using Service Broker.

Cheers,
Kevin
Posted by Kevin Kline Database mirroring
16 AUG 2005 21:09 EDT (01:09, GMT)
One of my favorite new features in SQL Server 2005 is called database mirroring. Database mirroring is a feature that keeps your database up and running, that is, a high-availability feature.

As the name implies, mirroring works for a given user database. The database must be in full recovery mode and cannot be in simple or bulk-logged recovery mode. Mirroring works by maintaining two copies of a SQL Server database on two physically separate servers. The server available to the user is called the principle database, while the other copy (which cannot be in use at the same time) is called the mirror database. Once configured, every transaction that occurs on the principle database is then applied on the mirror database.

Whenever the principle database experiences a crash, the mirror database is able to step in and service the user requests. On top of that, mirroring provides a very easy-to-manage alternative to failover clusters or log shipping.

Compared to failover clustering and log shipping, one especially appealing aspect of database mirroring is that there is no special hardware required. Unlike clustering, the mirror database server need not be on identical hardware. Thus, database mirroring can be both easy to set up and cheap to configure.

For more information, read Ron Talmage's excellent white paper about database mirroring and check out the TechNet and PASS chat.

Cheers,
Kevin
Posted by Kevin Kline The SQL Server 2005 feature countdown
15 AUG 2005 18:09 EDT (22:09, GMT)
Over the next couple weeks, I will present a number of features and capabilities within the upcoming release of SQL Server 2005 that are, to me, the most important, exciting and life-changing for SQL Server DBAs. How many features are in the upcoming release? When SQL Server 2005 was originally scoped out, it included literally hundreds upon hundreds of new features, both major and minor.

However, as time marched on, many features proved to be too unwieldy or not well defined in the mind of the customer and were winnowed out of the release. For those of you who have been involved with SQL Server 2005 before the beta program launched, you've seen a succession of features de-scoped from the product as it moved from initial definition through each of the beta cycles.

On the other hand, the features that remained have grown in sophistication and capability. Many of them are truly groundbreaking and will make your life much better and much different. In general terms, the features for SQL Server 2005 fall into three broad categories -- business intelligence, core database and developer enablement. Over the next two weeks, I'll be describing features from all of these areas.

The first feature I'd like to discuss is something I noticed as different from the very earliest days of development for SQL Server 2005. The new feature wasn't even in the product itself, because I and many others began to see the fruits of Microsoft's labors on this feature from the very earliest days. What was this new and improved feature? Remarkably better documentation and educational material!

I've been a part of the SQL Server beta program since release 6.0. And I can tell you from first-hand experience that the pre-release educational content for SQL Server (and even many other database platforms) has never been better! In fact, several years ago, Microsoft conducted a 1000-person airlift to the Madenbauer Center in Bellevue, WA. All of the attendees were presented with a large number of NDA educational sessions. These sessions were then recorded and put on DVD for the attendees. As time went on, these education sessions where turned into online sessions and white papers that you can take advantage of today.

For example, you can go to the TechNet SQL Server Tech Center and view a virtual cornucopia of SQL Server 2005 white papers, documents and e-learning sessions for topics of interest to the DBA. If development is more your style, you can go to the for educational content that is useful for developers.

There are also oodles of webcasts and blogs for you to check out -- all with useful and remarkably good information, and all fully supported and endorsed by Microsoft.

Finally, if you haven't already started to think about the beta, I strongly encourage you to consider downloading the June 2005 CTP release of the SQL Server 2005 Beta. This is the best way to learn about the next release of SQL Server -- by actually using it. So take a look today and start learning!

Cheers,
Kevin
Posted by Kevin Kline