This is the first part of a series of articles where I take you through my exploration of SQL Server 2005 Beta 2. In this part of the series, I’m going to cover what is SQL Server 2000’s strongest asset, its tools. The tools like Enterprise Manager and Profiler give you the quickest way to get to the market with your product. The tools in SQL Server 2005 have begun an evolution to where you will not recognize it as SQL Server. This article provides a high-level overview of my first impressions of the tool suite and the installation. Keep in mind that I’m only touching the surface and that this can cover several articles.

Installation of SQL Server 2005

You can download SQL Server 2005 by either going to http://www.betaplace.com or going for MSDN subscribers, it’s in the subscriber section of that site. Beta 2 of SQL Server 2005 is incredibly easy to install. It asks even fewer questions than SQL Server 2000. One thing I do like about the install is it does an analysis of your environment before installation of the core software to make sure that you have the type of environment where SQL Server is installed. For example, if you don’t meet the minimum hardware requirements, it will give you a warning.

The first stage of the installation will begin to install the core support files like the .NET Framework 2.0. This part of the installation takes quite some time and it provides you no options. After the initial support files are installed (required), it starts to analyze your machine as I mentioned before. The analysis provides you warnings that you can ignore or errors that you can’t ignore. For example, a warning may be ASP.NET is not installed so you can’t install the Reporting Services component.

You are then directed to a screen where you can select what components you’d like to install (DTS, SQL Server, Analysis Services, etc). This section is not very granular and it does not provide you other place to install the bits other than the system drive. To optionally install the files elsewhere, you have to click Advanced. After that, you’ll be able to check that you’d like to install samples and optional components. You can also specify the drive you’d like to install the programs on for some components but not all. This is a bit flaw for me since we generally only have a few gigs available on the system drive and store all apps on a different drive.

You’ll then be asked the standard collation questions and SA password. The SA password must now be a strong password and it will not let you move forward until you specify a strong one. I like this! After that, the installation pretty much coasts on cruise control. You’ll see a list a confirmation screen of what is going to be installed and then it’ll provide you a status screen, which checks a green checkmark as each component is successfully installed.

There is an interesting remark to make after installation. When you go to remove components, everything is broken up in the Add Remove Programs section. A list of components may vary based on what you have installed but for a base SQL Server installation (with DTS), I had:

• Microsoft SQL Server Setup Support Files
• SQL Server Tools
• SQL Server 2000 Data Transformation Services (DTS)
• SQL Server 2000
• Microsoft SQL Server Native Client
• .NET Framework

Some of these components like DTS are 500 MB in size! Overall, a flawless installation and everything removed easily through the Control Panel.

Installation Review Summary (too much to list)

Pros: Easy installation with little configuration questions, secure out of the box, provides a single installation for all optional SQL Server component like DTS, Analysis Services and Reporting Services

Cons: Does not give you the option of what drive to place some components

Tools

Now that you have SQL Server installed, let’s go ahead and poke around at what you have. I spread my installation out over several servers. For example, Analysis Services was on one server and SQL Server, DTS and Reporting Services was on another. Here’s how my servers were configured:

My Server:

Compaq DL380

Dual 1.1 GZ processors.

2 GB of RAM

This is where I’ll probably have to spend 12 articles describing how things work now. So, let me take a high level swing at things now though. Almost everything is now compiled on a .NET platform and you now use Visual Studio to do most tasks. Profiler is still an exception.

Tools are now broken into different components based on the task. For example, Enterprise Manager and Query Analyzer have been merged into SQL Server Management Studio. Business Intelligence tasks like DTS, Reporting Services and Analysis Services can be managed through the BI Workbench. What’s especially nice about tools like this is you can do some tasks offline in the plane and then deploy to your server quickly. I especially like this deployment model myself.

As far as the SQL Server API, SMO has replaced DMO. SMO is much more efficient in managing SQL Server. For example, pulling back a list of tables in your database is a much cheaper operation on the CPU. This shows as a benefit when you expanding objects in the SQL Server Management Studio. Once you click on Databases, you’ll see (expanding) and then you can move forward doing something else why it pulls back the thousands of databases that are on your server. In other words, everything is non-modal.

Operation tasks are now centralized in the SQL Computer Manager. This program lets you start and stop services and change who starts the various SQL Server services. It also replaces SQL Server and Client Network Utilities. It does all this management through WMI or SMO without making a connection to the SQL Server.

An less visible item in this new release is tutorials and Books Online. You can learn SQL Server quickly by following the tutorials. You also have dynamic help to guide you through writing a query as you write them. Also the Database Tuning Advisor is the new revision of the Index Tuning Wizard on steroids.

One huge area of disappointment was the speediness of the beta. The relational engine seemed to be as quick as previous releases but the tools were very slow. It’s very important to note here is that beta 2 typically focuses on functionality and beta 3 is where Microsoft tunes the tools. The tools are also very RAM hungry. Keep in mind that SQL Server 2005 tools are built on Whidbey (Visual Studio 2005), which is in Beta 1.

Tool Review Summary

Pros: Stylish new interface, works more efficient in a large enterprise with hundreds of databases, Reporting Services has now been merged into the same toolset. Profiler has taken a huge step forward.

Cons: The design tools were very slow on my servers and hungry for resources, diagrams were been pulled from B2

Conclusion

When it comes to first impressions, keep in mind that this is still Beta 2. It will be a broad beta with 500,000 copies downloaded. It’s important to let Microsoft know what you think in the newsgroups and features that you feel you can’t live without. Even after this release goes to general availability (GA), this is the first step in the evolution of the tools. They may not get it perfect in this release but over time, it will improve.