One of Microsoft's most long-awaited releases, SQL Server 2005, has finally hit the market and brought with it significant new features and changes from previous versions.
In this article, I will go over the various editions of SQL Server 2005 and provide you with an overview of the new management console.
In future articles in this series, I will:
- Go through a sample SQL Server 2005 deployment
- Provide you with a tutorial on managing and using Reporting Services in SQL Server 2005
- Provide a tutorial with tips for the SQL Server 2000 admin making the leap to SQL Server 2005
SQL Server 2005 Editions
Like its predecessor, Microsoft SQL Server 2005 comes in a number of editions, each targeted at a different environment. In the case of this latest edition, Microsoft has released a total of six 32-bit editions of the product. In addition, Microsoft provides 64-bit alternative editions, which support both Intel (both x86-64/AMD64/EM64T and Itanium) and AMD 64-bit architectures.
The six editions of SQL Server 2005 are:
- Mobile Edition (32-bit only): For SQL Server 2000, Microsoft provided a Windows CE edition of the product. For the SQL Server 2005 edition, renamed the Mobile Edition, Microsoft has expanded the mobile capabilities of the product.
- Developer Edition (32-bit & 64-bit): The Developer Edition has all of the power of the Enterprise Edition (discussed below) with one key difference: By virtue of the license agreement with this edition, deployments into production environments are prohibited.
- Express Edition (32-bit only): The SQL Server 2005 Express Edition is the replacement product for the SQL Server 2000 Desktop Edition, and provides software developers with a free database that can be distributed royalty-free with their product. The Express Edition can be downloaded from Microsoft's site at no cost. The Express Edition does have a few limitations, however. First, the product can use only a single processor, limiting its use in large installations. Second, only 1 GB of RAM is useable by the product. Finally, an Express Editions database can be no larger than 4 GB in size.
- Workgroup Edition (32-bit only): The Workgroup Edition doesn't have a direct SQL Server 2000 counterpart, and is designed for smaller organizations that don't need a mega-powerful database. The Workgroup Edition’s primary limitation is its restriction to using only up 3GB of available RAM in the system, making it unsuitable for significant database deployments. Other limitations include lack of partitioning capability (described below), parallel processing (also described below) and indexed views (also described below). The Workgroup Edition also lacks significant high-availability, management, and analysis features that are present in other editions of the product. Even with these limitations, a Workgroup Edition database does not have an imposed size limit.
- Standard Edition (32-bit & 64-bit): With a direct SQL Server 2000 counterpart, Microsoft targets the Standard Edition at small and medium businesses that may use, but do not heavily depend on, e-commerce services, and other business-related database needs. The Standard Edition starts to include features necessary for an enterprise database, including 2-node clustering, unlimited RAM support, up to 4-processors, unlimited database size, and reasonable business intelligence capabilities.
- Enterprise Edition (32-bit & 64-bit): The flagship product of the line, the Enterprise Edition provides a scalable database server environment for any size business. The Enterprise Edition does not have any CPU, RAM or database size limits, allows for multimode clustering, online indexing, Oracle replication and much more.
Much of the data in Table A was gathered from Microsoft's SQL Server site, but I have extended it to provide you with more information about each feature, and summarized many of the features available in each edition of SQL Server 2005.
Table A
| Feature | Description | Express | Workgroup | Standard | Enterprise |
| Scalability and Performance | |||||
| Number of CPUs | The number of host CPUs that SQL Server can use | 1 | 2 | 4 | No Limit |
| RAM | The amount of RAM that can be used by SQL Server | 1 GB | 3 GB | OS Max | OS Max |
| 32-bit Support | 32-bit platform support | X | X | X | X |
| 64-bit Support | Does the edition support a 64-bit OS? If not, a 32-bit edition can still be run on a 64-bit OS using Windows-on-Windows |
|
| X | X |
| Database Size Limit | The maximum database size supported by SQL Server. | 4 GB | None | None | None |
| Partitioning | Partitioning allows you to break a database up into smaller chunks to improve performance. |
|
|
| X |
| Parallel Index Operations | Parallel indexing (as opposed to serial indexing) can provide a huge performance boost for larger databases since several database indexing operations can be run simultaneously. |
|
|
| X |
| Availability | |||||
| Mirroring | Advanced high availability solution that includes automatic fast failover and automatic client redirection in the event of a failure SQL Server 2005 uses new extended log shipping to accomplish this goal. |
|
| X | X |
| Failover Clustering | Allows you to build a highly-available, fault-tolerant database system |
|
| 2 nodes | X |
| Online Indexing | Allows multiple queries or table or index operations to take place even while indexes are being rebuilt. |
|
|
| X |
| Online Page and File Restore | Allows a page or a file to be restored while the system is still operational and unaffected database remains available |
|
|
| X |
| Fast Recovery | The database is available for use during a rollback. |
|
|
| X |
| Management | |||||
| Auto Tuning | Automatically tunes database for optimal performance |
|
|
| X |
| Express Manager | Simple Management Tool that works with the Express addition of SQL Server 2005 as well as with SQL Server 2000, including MSDE (Finally! A decent GUI for MSDE!) | X | X | X | X |
| Database Tuning Advisor | Automatically suggests enhancements to your database architecture to improve performance |
| X | X | X |
| Full Text Search | Fairly straightforward! |
| X | X | X |
| Programmability | |||||
| T-SQL Enhancements | Includes exception handing, recursive queries, and support for new datatypes | X | X | X | X |
| User-defined Types | Extend the server with your own custom datatypes | X | X | X | X |
| Native XML | Includes XML indexing and full-text XML search | X | X | X | X |
| XQuery | Ability to query a collection of XML information | X | X | X | X |
| Notification Services | Allows the building of advanced subscription and publication applications |
| X | X | X |
| Integration and Interoperability | |||||
| Integration Services with Basic Transforms | Provides graphical extract, transform, and load (ETL) capabilities |
|
| X | X |
| Integration Services Advanced Transforms | Includes data mining, text mining, and data cleansing |
|
|
| X |
| Replication | Both Merge and Transactional replication. | X | X | X | X |
| Oracle Replication | Transactional replication with an Oracle database as a publisher |
|
|
| X |
| Web Services (HTTP Endpoints) | Support for native Web services, WSDL, and Web authentication |
|
| X | X |
| Business Intelligence (BI) | |||||
| Data Warehousing | Helps organizations harness and make use of ever-expanding stores of historical data. |
|
| X | X |
| BI Development Studio | Integrated development environment for building and debugging data integration, OLAP, data mining, and reporting solutions |
|
| X | X |
| Analysis Services (OLAP Engine) | Includes advanced OLAP capabilities including KPIs |
|
| X | X |
| Proactive Caching | Provides automated caching for greater scalability and performance |
|
|
| X |
| Data Mining—Standard Algorithms | Includes decision trees and clustering |
|
|
| X |
| Data Mining—Advanced Algorithms | Five additional algorithms including neural networks, Naïve-Bayes, time series, association and sequence clustering |
|
|
| X |
| Reporting Controls and Wizard | Both Windows and Web-based report controls are delivered with Visual Studio 2005 | X | X | X | X |
| Reporting Engine | A built-in, web-based reporting engine, complete with a scheduler |
|
| X | X |
| Data-Driven Subscriptions | Supports large-scale, customized report delivery |
|
|
| X |
| Scale-out Web Farms | Addresses scalability in the Reporting Services component |
|
|
| X |
| Infinite Drilldown | Allows a user to drill down all the way to the source data, which helps to better analyze the information. |
|
|
| X |
| Report Builder | New end user ad-hoc query and reporting client |
|
|
| X |
Reporting changes
If you’re a SQL guru, you’ve probably either used or been exposed to SQL Server 2000 Reporting Services, Microsoft’s free add-on to SQL Server 2000. With the SQL 2000 version of the tool, you needed to perform a separate installation of Reporting Services, and management was very separate from SQL Server itself. With the 2005 release, Microsoft has combined this reporting tool right into the product installation, and provided a management console integrated with the overall system’s console.
SQL Server 2005 Management Studio
From a visibility perspective, by far the biggest change in SQL Server 2005 is the SQL Server Management Studio, which replaced the venerable Enterprise Manager found in previous versions of the product. In fact, Management Studio rolls Query Analyzer, Analysis Manager, Reporting Services management, and Notification Services into this single tool as well.
Even better, the SQL Server 2005 Management Studio can be used to manage SQL Server 2000 instances, providing you with a powerful centralized management console. If you want to enjoy the full range of enhancements in the Management Studio, though, you need to be running SQL Server 2005.
From the get-go, you'll see the changes Microsoft made for the management studio. When you start the program, the first screen you’re greeted with is a login screen which asks you to authenticate to the database server.
Figure A |
 |
| The SQL Server Management Studio login screen. |
After logging in, you get to see the Management Studio in all its glory.
Figure B |
 |
| The Management Studio main window. |
With the Management Studio, Microsoft made an effort to include some features and design elements from Visual Studio into the product. For example, if you’ve used Visual Studio at all, you’re familiar with the concept of projects. A Visual Studio project is (usually) a collection of files that are related to an overall goal. The term "project" in the Management Studio is interchangeable with the word “solution”. In either case, a project in this sense is a group of scripts that act as a single entity.
With this release of SQL Server, and the accompanying changes to the management tool, it is even more apparent that the line between “programmer” and “database administrator” is blurring somewhat.
The interface
The Management Studio is a fairly typical-looking interface, but there’s a lot beneath the hood. Like most applications, the Management Studio is broken up into a couple of different sections with the menu bar running across the top of the window.
The menu bar in the Management Studio contains a wealth of options above and beyond those found in the old Enterprise Manager. I’ll start with the Tools menu, which contains two SQL Server performance analysis tools: the SQL Server Profiler, and the Database Engine Tuning Advisor.
The SQL Server Profiler is a tool that shows you what your server is doing—basically, how much of a load are your individual queries placing on the system? With this tool, you can quickly track down processor-intensive queries, memory-intensive queries and slow-running queries and either rewrite them, or, at the very least, come to understand why your database system may slow down from time to time. While this tool was available in SQL Server 2000’s Query Analyzer tool, the SQL Server 2005 version of this tool adds some functionality, such as aggregated views and the ability to allow non-admin users to use the tool.
The Database Engine Tuning Advisor builds on SQL Server 2000’s Index Tuning Wizard by providing advice on database indexes, partitions and views and helping you to analyze the potential results of the modifications you make. This is a very simplistic look at this feature, though. In reality, it does much, much more which is beyond the scope of this article. For more information about the Database Engine Tuning Advisor, visit the Microsoft MSDN site.
Immediately below the menu bar, you’ll find the Standard toolbar that houses the most common options used in the Management Studio. With Query Analyzer, an incredibly useful tool in my opinion, eliminated, Microsoft chose to place query functionality front-and-center in the Management Studio. In fact, including the New Query button, the first six buttons on this toolbar handle various query tasks, including a multitude of analysis services queries, general database queries, and SQL Server Mobile queries. Other buttons on the toolbar provide additional functionality, including access to registered servers, templates and more.
In a default Management Studio view, the left-hand portion of the main window is used by the Object Explorer. If you take a look, the Object Explorer somewhat resembles the main view in the old Enterprise Manager. However, Management Studio’s Object Explorer provides a huge wealth of information and capability in this single window. Note that, when you select an object in the Object Browser, the details for that object appear in the Summary window to the right.
Figure C |
 |
| Anything and everything you need to do with your database is conveniently available. |
Just about every object in the Object Browser has a shortcut menu with tasks associated with the object type. For example, if you right-click an index, you get options that allow you to rebuild, reorganize, or disable that index, or to create a new index. Or, you can just click the Properties option on the shortcut menu to get all available information about that object. In Figure D below, I’ve selected the properties of a primary key in the sample Northwind database. Note that Properties pages do not open in the right-hand window, but, rather, open up in a new window.
Figure D |
 |
| This properties window provides one-stop shopping for key properties manipulation. |
On the Properties window shown in Figure D, note that there are multiple pages of information available for this primary key. Other object types have even more pages of options. For example, an index has six pages of information for you to peruse.
Beyond just the servers and databases themselves, the Object Browser provides you with a launch point to other related services, including Reporting Services
In short, the Object Browser window provides you with a place to just about completely manage your SQL servers, databases, users, and even Reporting Services. To access management for related services, such as Reporting Services, Integration Services, Analysis Services, and SQL Server Mobile, click the Connect button in the Object Explorer.
Figure E |
 |
| The Connect button changes the component of SQL Server 2005 that you’re managing, but all of the management stays within the context of the Management Studio. |
When you choose a new content area by using the Connect button, you need to authenticate again to the SQL Server after which you will get an Object Explorer option to manage that service. In Figure F, I’ve opted to manage the Reporting Services component and opened the overall properties page for that service. In a future article, I will provide some in-depth information for managing SQL Server 2005’s Reporting Services component.
Figure F |
 |
| Manage Reporting Services and all other SQL Server functions from within this single tool. |
I’ve talked a lot about the Object Browser, and you’ve seen some of the results in screen shots. But, there’s a lot more to this window. In the screen shots you’ve seen thus far, the right-hand part of the main window has been relegated to providing information. However, this section of the window plays a critical role in managing your SQL server.
For example, when you click the New Query button on the standard toolbar, a query editor is instantly provided in the right-hand pane. Further, by way of its context-sensitive nature, the Management Studio also provides you with the SQL Editor Toolbar, which is very useful while you’re building queries.
For those of you that loved Query Analyzer in SQL Server 7 & 2000, you’ll probably like this, since much of the functionality from these older tools is now right here at your fingertips along with the rest of the SQL management tools. In Figure G below, I’ve created and executed a simple query. Note that the useful tools, such as the Parse function, are included on the SQL Editor Toolbar. Also note that, like Query Analyzer, the results of your query are shown to you immediately.
Figure G |
 |
| Much of Query Analyzer’s functionality is built right into the server management tool now. |
Configuration Manager
While the Management Studio is an excellent replacement for Enterprise Manager, it does not have all of the capability required to fully manage the actual services that make SQL Server 2005 work its magic. For this and other tasks, Microsoft provides the SQL Server Configuration Manager. The main purpose of the Configuration Manager (Figure H) is to manage the services and network configuration for SQL Server.
Figure H |
 |
| Configuration Manager lets you manage the SQL services and network configuration without having to rely on other utilities. |
This tool is fairly simple in that it allows you to enable and disable specific services without having to go to Service Manager and let’s you change the order of the various network protocols (Named Pipes, TCP/IP, VIA, and Shared Memory).
Streamline
Microsoft has made great strides with SQL Server 2005, and has included significant high-end features to make it a possible contender even for high-end applications that were previously owned by Oracle and IBM’s DB2. That said, Microsoft has varied the editions enough so that organizations that don’t need all of the functionality of the Enterprise Edition don’t have to pay for it. The new management tools included in the product streamline the various tools needed by older versions of the product. The tight integration with Visual Studio 2005 is evident as soon as you look at both products side-by-side, and helps Microsoft reinforce the developer-focused nature of both products.
