Author: Robin Dewson
Editor: Simon Robinson
Reviewer(s): Kunal Cheda, Teun Duynstee

Introduction

It has been five years since the release of SQL Server 2000, and a major revision has now taken place-in fact, it could be said that SQL Server 2005 is almost a totally new product. From the installation procedure to the inclusion of new functionality like Service Broker , there are a great deal of new concepts to learn. SQL Server is still a database server that's optimized for T-SQL code and set-based processing. Today though, it has been given greater flexibility and power via integration with the .NET Framework, a total rewrite of Data Transaction Services (DTS), further data optimization functionality, new data types, and the inclusion of tools such as Reporting Services within the core SQL Server features (which tools were once merely "add-ons").

As of the writing of this article, SQL Server 2005 is a mere few months from final release. There are monthly Community Technology Previews (CTPs), that contain bug fixes, being released for beta testers, MSDN subscribers, and others in the field. SQL Server 2005 is a major advancement of technology, security, and stability for Microsoft's premier database product. There is a great deal for a developer or database administrator to take on board and get up to speed on – ranging from the inclusion of .NET technology (which takes SQL Server to a par with Oracle and its inclusion of Java), further integration of Notification Services and Reporting Services as well as Web Services, and not forgetting security enhancement,. This article details the major changes within SQL Server 2005. From the installation process, to new tools for server administration, to .NET integration and other new functionality, this and the follow-up articles will prepare you in the areas you'll need to concentrate your learning on.

This articles series provides a concise overview of the major new features and changes in SQL Server 2005. The topics covered in the articles are as follows:

• This first article examines the various new tools and features that come with SQL Server 2005.
• The second article covers changes and additions to the T-SQL language.
• The third and final article explores .NET integration.

Installation Changes

The first change that you'll notice concerns the installation of SQL Server 2005. Instead of having to run the install procedure several times for different components like Analysis Services and full-text search, as you had to do with SQL Server 2000, you can now select whether all these tools are to be installed up front. The same applies to new or newly integrated tools such as Reporting Services, Notification Services, and Broker Services. The only precondition is that you match the install criteria (for example, Reporting Services requires IIS to be installed). Once you select which products you wish to install (or remove), you can then refine your selections just as you would with something like Microsoft Office. A screenshot from the installation process is shown in Figure 1.

Figure 1. Installation feature selection

There are still several screens for options such as collation sequence and the location of Reporting Services virtual directories, and it's still possible to have default or named instances, just as you would expect. Also, it's possible to install SQL Server on the new Virtual Server 2005 technology. Virtual Server 2005 runs on Windows 2003 servers and allows corporations to run more than one self-contained operating system on a server.

Note that installation of SQL Server requires the installation of the .NET Framework 2.0 on your server, whether you want to use the new .NET technology or not.

During the installation, it's also possible to define different Windows accounts for different services. This means that it's easy to avoid giving the various services an account that has system administrator privileges. This is shown in Figure 2.

Figure 2. Installation service account setup

Database Management Tools

In this section, you'll learn about the main tools used to manage SQL Server databases, including submitting queries and running stored procedures. In SQL Server 2000, these tasks were largely the domain of Enterprise Manager and Query Analyzer (QA), or of the the command line tool OSQL for people who preferred to work at the command line. All these tools have been superseded in SQL Server 2005 by the tools I'll now discuss.

SQL Server Management Studio

After SQL Server installation is complete, you may notice that there's no SQL Server Enterprise Manager under All Program in the Start menu, and no separate QA. SQL Server 2005 has changed the interface to SQL Server, Analysis Services, and DTS (which is now known as SQL Server Integration Services ( SSIS ). QA has been removed as a separate tool and is now integrated within the Management Studio , which is the replacement for Enterprise Manager. For me, the changes are mainly cosmetic. It's possible to change between servers such as a reporting server, and the DTS server, but as a tool, you still have the objects in a tree view on the left, MMC-style, that you can navigate through. Some improvements are evident, such as when you're building tables or adding or modifying a column - in such cases, the properties are available in a properties window, just as in Visual Studio (VS). The main thrust of Management Studio is to incorporate SQL Server in to the VS look and feel.

Figure 3 is a screenshot taken from my current Management Studio install. On the right, I have a few query panes open, with each tab defining the connection and to which database it's currently pointing to: the first on the left, connected to the master database, two others without any connection, and finally a summary page, which is similar to the summary page that exists within SQL Server 2000.

I have yet to be conviced that there are enough advantages to incorporating QA within Management Studio to have made the change worthwhile (particularly because QA was previously available on its own when it was a separate tool). However, one nice feature I noticed is that you now have IntelliSense when writing SQL. There are also changes within QA that I like-for example, each query pane can have different connections, and therefore you can check out whether code works under different connections. In the previous QA, you'd have to disconnect the whole server to do this. Now it's simply a matter of switching panes.

Figure 3. SQL Server Management Studio with query windows
This figure has been reduced in size to fit in the text. To view the full image Click here

The greatest changes within Management Studio can be seen in the left-hand panes of Figure 3. These two panes look similar to SQL Server 2000 Enterprise Manager, but instead of the server being the root node with all the server objects below, as was the case in Enterprise Manager, there are now two separate tree views. The top view, called Registered Servers (also known as the server explorer ), lists all the known SQL Server–defined servers that the installation is aware of. Here, you can see every server you have ever connected to and still have a connection object for. By right-clicking within this explorer and selecting New/Server Registration , you can see the dialog box shown in Figure 4. If you've used an ADO.NET connection before, then this dialog should be familiar to you, but it's also similar to what existed in SQL Server 2000.

Figure 4. SQL Server registration properties

Once a server is registered, selecting it will alter what's shown in the Object Explorer window. While in the past you would have had one long list of server objects for each server, the new Object Explorer window has condensed this.

There have been a number of other changes from Enterprise Manager to Management Studio, many of which I'll cover as I go through the remainder of the article.

sqlcmd

You'll also find that there is a new command window–based utility for SQL Server 2005, called sqlcmd . This replaces ODBC SQL (which was known as OSQL), and is used for connecting to SQL Server without any graphical interface. It can be used for running T-SQL queries and scripts. Also, it can have greater privileges than connecting to SQL Server via the Management Studio. sqlcmd also has greater flexibility and access to more functionality than OSQL since it can include VBA scripting.

Competitor databases (like Sybase, for example) have the ability that almost no matter in what state your server was, such as stopped due to a transaction log being full, you could always get an administrator database connection to kill processes, or commands to try and get the server back on its feet without the need for it to be recycled. This functionality has now been introduced in SQL Server 2005, but it's only available through sqlcmd either on the local machine with the SQL Server instance on, or from a remote machine away from the server. By simply using a switch ( -A ) when invoking sqlcmd, you can create a connection and log in at the command prompt as the system administrator account.

Figure 5, which follows, shows a command prompt window that has sqlcmd switches displayed, many of which are the same as they were within OSQL. The figure displays a partial set of results from executing sp_who , a system stored procedure that lists all the current connections.

Figure 5. sqlcmd command window
This figure has been reduced in size to fit in the text. To view the full image Click here

SQL Server Integration Services (SSIS)

When it comes to importing data into SQL Server from an external data source (for example, information in an Excel spreadsheet or data from Oracle), some changes have taken place. Prior to SQL Server 2005, you had to use a tool called DTS. This area of SQL Server has been rewritten from the ground up and has been renamed SSIS. SSIS is still used for importing from external data sources, but instead of the whole package being crammed into one design screen (as was the case in SQL Server 2000 DTS), SSIS allows a more controlled and flexible solution.

When you access SSIS, you'll notice a new menu option within the Object Explorer. This is shown in Figure 6, which follows.

Figure 6. Connecting to SSIS

To build an SSIS solution, you still need to have data sources from which to retrieve data. However, the list of possible sources has changed, and the data source destinations are now are under their own tool box items. To connect to a SQL Server instance, you'd now, for example, use a DataReader source. There are also many more possible transformations, each built around more well-defined, less generic transformations. This makes your SSIS package less reliant on VBA code to complete transformations than was the case with DTS.

With SSIS, instead of there being just one design surface on which every DTS object is placed (the design surface is simply the area used for building your SSIS solution, and that replaces the single window in SQL Server 2000), SSIS consists of three design surfaces, an explorer tab, and a runtime surface.

The first tab is the Control Flow design surface. This is used like the workflow items that were used prior to SQL Server 2005, and it shows the order in which tasks are performed. This tab also shows when there are repeated workflow items to perform via the FOR loop and the FOREACH loop container. In this tab, you'll also see the more familiar tasks from SQL Server 2000.

The second tab is the Data Flow design tab, which is used for data-specific functionality, as you would have used for transformations, VBA code, and so on. It is also on this tab that you define where the resultant data will be loaded to.

The third tab is the Event Handlers design tab; this is used to indicate what happens when a task succeeds or fails, or to list the actions on many different events. In the past, you could have only a Completed or Failed route, and then you had to execute a task on that event. Now there are many different possible events (such as OnProgess , OnPreExecute , and so on), which means there are many more possibilities for recording and completing actions, as well as options to inform a developers (or people monitoring the system) of what is happening.

There is a fourth tab, known as the Package Explorer tab. This is like the options within a DTS package, which would allow you to define pre-created global variables like the package name, version, etc. It also shows details about the package in a tree view.

When the package runs in "design mode," a progress tab is added. This details what's happening within the package at specific times.

It's impossible to show an example of an SSIS package within this article, as it would would take too long, but this section should give you a flavor of how much has been altered. This article can't do SSIS any justice-it really needs a whole book.

Snapshot Isolation

In general, database systems can be split into two groups, online transaction processing ( OLTP ) and online analysis processing ( OLAP ). OLTP systems tend to be those like sales order entry, banking, trading, or booking systems. These systems have a high proportion of write-based transactions and therefore need to add data fast. This can mean a low number of indexes, small tables in terms of row and column count, and even covering indexes. OLAP-based systems have few writes, which usually occur as part of a batch update and exist primarily for reading data. Typical OLAP systems include data warehousing systems, archiving systems, etc. As these have a high number of data reads, they have a large number of indexes; flat, wide tables; and a potentially large number of rows.

As data is added to OLAP systems, it can take longer and longer to add more data or rebuild indexes (often done during the overnight load or statistics rebuild)-and during these times, the data is unavailable for a consistent view. Instead of having this downtime, it's possible to take a complete snapshot of the database prior to the data being loaded in its regular batch update. As the batch modifies the tables to add any new data since the previous batch, snapshot isolation takes a copy of the data page prior to the update and stores it.

If a problem occurs during the batch update, pages can be reapplied to the main database from the snapshot isolation database.

To switch on snapshot isolation, you execute a statement similar to the one in the following code snippet, in which isolation for AdventureWorks is being switched on.

ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON

This may not take effect immediately and will only action when all active transactions have successfully completed. Therefore, you should action this while the system is quiet. You can check the state of the isolation by checking the system view, sys.databases .

SELECT snapshot_isolation_state,snapshot_isolation_state_desc
FROM sys.databases
WHERE [name] = 'AdventureWorks'
GO

Database Tuning Advisor

The Index Tuning Wizard within SQL Server 2000 has been replaced with a new and more sophisticated tool, the Database Tuning Advisor ( DTA ). As it happens, you can use this new advisor to work with SQL Server 2000 databases as well. The DTA can advise on indexes, partitioning, and which physical design structures to keep within the database.

This advisor can tune not just one database-it's now possible to create a workload that cuts across multiple databases. This is ideal for when the workload involves tuning an archiving process-for example, when moving data from the OLTP system to the OLAP system, or between your customer database and your order processing database. In such cases, the DTA would advise and recommend indexes etc. on that workload. If the advisor, when processing your workload, comes across an event that can't be tuned, the event is written out to the tuning log with the reason why.

The areas just mentioned are only a few of the areas this advisor can work with. Another new area includes the ability to tune user-defined functions (UDFs). Also, you can define how long SQL Server should run the workload T-SQL. The advisor can also use an XML file as the input workload file, and can generate the tuning recommendations as XML output.

In Figure 7, you can see how the graphical interface has changed (there is a command-prompt version of this advisor as well called dta.exe ). To use the command prompt, a workload file that's run through the tuning advisor is required.

Figure 7. Using the Database Tuning Advisor to check workload

Figure 8 shows the different tuning options that can be selected for the tuning advisor to advise on.

Figure 8. Tuning options of the Database Tuning Advisor

SQL Management Objects (SMO)

In the past, you may have used SQL Distributed Management Objects (SQLDMO). This is a collection of objects that allows you to write code to solve problems surrounding SQL Server. The replacement for DMO is SQL Management Objects ( SMO ). SMO still provides objects that allow you to manage SQL Server, but it has been improved in the areas of performance, objects available for use, and management of SQL services via the SQL Server WMI. An SMO application can be written if you want an external interface to SQL Server. For example, I use a system from Computer Associates called Autosys. This is a scheduling system for running batch jobs and providing more functionality than you can get within SQL Server 2000. An application using SMO can be written to remove Autosys, and execute processes and control SQL Server 2005 via this external interface.

Service Broker

Another new component to SQL Server 2005 is Service Broker . Service Broker is an asynchronous process that uses TCP/IP to exchange messages between either the same or different instances of SQL Server. It's also used as part of the .NET integration for working with DDL triggers. This process uses encrypted messages sent over the network to ensure that each message is secure and can't be intercepted; this ensures sending messages over the WAN or Internet can be completed. In the past, if you were trying to build a secure, reliable, and scalable queuing system, you would have had to look at third-party solutions like Tibco. In SQL Server 2005, Service Broker now performs this role.

Service Broker queues send out messages when required-once the messages have been sent, they can continue on with their work while Service Broker delivers the message. If the receiving process is busy or not ready to process the incoming message, SQL Server doesn't have to wait on the receiving process and hold up its work-it can move on.

Reporting Services

Reporting Services was launched as an add-on for SQL Server 2000. It's now fully integrated into SQL Server 2005. Although I find it's still not as powerful as Crystal Reports, it gets better and better with each version. The improvements in this version allow interactive sorting of reports in which you can change the order of the data while viewing. It's also now possible to print multi-page reports from Internet Explorer. Prior to SQL Server 2005, you had to export the reports first. A minor downside to the new version is that there's a downloadable ActiveX control that can remove visual elements before printing. With Service Pack 2 of Windows XP, ActiveX controls like these are disabled by default, and you therefore need to specificy this control to run. Users can also create ad hoc reports using an authoring tool called ClickOnce.

There are also other improvements, including changes to the expression editor, a new Analysis Services query designer, the ability for developers to embed databound server controls, and the ability to build reports via SSIS.

Notification Services

Perhaps you have an application for which it would be ideal to send out an email or an SMS text message when something happens-for example, an application that would notify traders of news whenever a government announcement was issued that could affect the price of gold. Or perhaps you want to send out a start-of-day set of news headlines. Notification Services is the ideal product to do this.

Notification Services came out with SQL Server 2000, and like Reporting Services, has been integrated into SQL Server 2005. It has also been upgraded with a new API, and you can now create user-defined conditions to send out notifications, rather than having to make do with predefined conditions. The more powerful SQL Server 2005 Notification Services could present a better way to deliver mail content to subscribers of a newsletter, or could even be be used in preference to RSS feeds.

DDL Event Notifications

DDL actions such as CREATE TABLE , ALTER DATABASE , and DROP PROCEDURE can be trapped, monitored, and actioned within SQL Server 2005 via a DDL trigger. A DDL trigger is like a DML trigger in that it can either execute T-SQL code or a .NET assembly to execute the processing required. This can be used to great effect by database administrators for monitoring and executing specific processing when particular actions are performed. For example, you may have to deal with a new security initiative called Sarbanes Oxley (this is something I'm dealing with constantly at the moment). If a DDL trigger receives a notification that a new user has been added to the database, then Sarbanes Oxley requires not only authorization for this addition, but also that the user has been successfully added. In such cases, a DDL trigger could be written for logging these actions in a security database, for example.

Full-Text Search Enhancements

Full-text searching prior to SQL Server 2000 was good, but was restrictive in some areas-many of which have been addressed in this version. First, it's now possible to create a full-text search query that references a remote linked server. For example, if you have your company's main information (such as customer information) on one server, and other information (say, online orders) on another server, a full-text search is still possible. In the past, full-text searches on remote linked servers weren't possible-not only because of the different servers, but also because it wasn't possible to search over multiple columns. In SQL Server 2005, this multiple-column restriction has also been removed. Full-text searching prior to SQL Server 2005 could be done with either one column or all columns-and nothing in between. It's also now possible to put full-text queries against views that have been set up with full-text capabilities, as long as one or more of the base tables have been set up with full indexing.

Prior to SQL Server 2005, once a catalog for full-text searching had been created, it had to be backed up separately to SQL Server. SQL Server 2005 now provides the ability to back up these catalogs in the same backup process as the database (or, if you wish, as a separate job). When a database is detached, the full-text catalog will also be detached, and will re-attach when the database is re-attached.

Performance has been improved through an upgrade to the search engine MSSearch. For each instance of SQL Server, there is one MSSearch engine; this, combined with improved performance, should provide greater stability for the retrieval of data.

Conclusion

After reading this first article, you should have a good idea of what to expect when you install SQL Server 2005, as well as some insight into the tools that replace the functionality of previous versions of SQL Server. I've also covered new tools such as Service Broker. There are some areas that have background changes that you won't initially notice, such as the SQL Server Management Studio for CLR integration. Improvements in areas like the Database Tuning Advisor, the rewrite of DTS, and CLR integration (which topics I'll discuss in the third article of this series) has made SQL Server a database that is right beside its main competitor, Oracle.