I haven’t looked at structured query language (SQL) since I was at Uni’, back in the early 1990s, and don’t intended to start now; but with a major new SQL Server release due from Microsoft in November, I’ve been building up my knowledge of the product from an infrastructure perspective. Yesterday evening, I was at a Microsoft TechNet UK event, where Matthew Stephen (one of Microsoft UK’s IT Pro Evangelists) presented the tools for managing and administrating SQL Server 2005 (codenamed Yukon) along with a few of the new features for administrators.

At the centre of SQL Server 2005 administration is the new SQL Management Studio, which replaces SQL Enterprise Manager and uses a Visual Studio project metaphor, featuring a much improved user interface showing registered servers, an object explorer (with a tree view of the databases – similar to Enterprise Manager) and a summary pane. New features include:

• An integrated SQLCMD mode, allowing SQLCMD queries to be run from within SQL management Studio (as long as they are preceded with !!).
• Online database restoration and fast recovery, with only the data currently being overwritten being unavailable.
• Online indexing (so that queries can still be performed whilst indexes are rebuilding).
• Integration with Visual Studio 2005 and the Microsoft .NET Framework v2.0, allowing the use of user-defined functions, data types and procedures, using any common language runtime (CLR)-compliant language.
• A dedicated connection for administrative access (allowing an administrator to connect to a server and diagnose problems even if access is generally being prevented by, for example, blocking).

In common with all new Microsoft products, SQL Server 2005 will be delivered “secure by default”, meaning that some configuration changes may be required in order to administer some legacy services. Now, when connecting to a server using the SQL Management Studio, the default connection method is using TCP/IP, and Windows authentication is the preferred authentication model.

The SQL Server Web Data Administrator is available today for administering SQL Server databases where no management tools are available. It runs on either Microsoft Internet Information Services (IIS) or the lightweight Cassini portable web server and allows either Windows or SQL-based authentication to connect to a server for management purposes, import and export (queries, stored procedures, etc.) and a T-SQL query editor. Because it is lightweight, the Web Data Administrator is ideal for use over slow network links.

SQL Server 2005 Express Edition replaces the Microsoft SQL Server 2000 Desktop Engine (MSDE) – Microsoft’s free (but functionally limited) version of SQL Server 2000. SQL Server Express 2005 is still limited, but the constraints are different to MSDE, now allowing use of a single CPU (although it can make use of multiple logical CPUs – i.e. with a multi-core CPU), 1Gb of RAM and a 4Gb database size, with no limit on the number of concurrent sessions, making it ideal as a database to support a web site.

The SQL Server Express Manager can be used to administer SQL Server Express or MSDE databases and is based on the Microsoft.NET Framework v2.0. With a tree view of database objects and wizards for common tasks, SQL Express Manager can be used to administer either local or remote databases and includes a T-SQL query editor.

SQLCMD uses the OLEDB interface and replaces ISQL (DB-Library) and OSQL (ODBC) tools (although OSQL still exists and SQLCMD parameters are backwards compatible with OSQL). Offering batch functionality (including command line variables and nested scripts) and script chaining, SQLCMD is a powerful command line tool for SQL Server administration and queries.

SQLiMail is a replacement for the former MAPI32-based SQLMail. Including its own SMTP client, SQLiMail makes use of the new SQL Broker provided with SQL Server 2005 and because it is no longer MAPI-based, has no reliance on Outlook. Multiple profiles can be attached to an SMTP account along with multiple host databases. SQLiMail also supports clusters and 64-bit SQL Server installations.

One of the new features of SQL Management Studio is the creation of maintenance plans (each one made up of sub-plans). The maintenance plan designer has a similar appearance to SQL Server Integration Services (SSIS) (a subject on which Jamie Thomson frequently blogs) featuring a drag and drop visual design workspace which allows tasks to be joined to build up a complex workflow. It is also possible to view the resulting T-SQL statements.

SQL Profiler also has a host of new features, not least the ability to grant trace access to developers (without requiring SA access). Also featuring a new selection user interface, SQL Profiler includes a host of new events (so that pretty much every activity can be audited), assisting with the compliance issues faced by many organisations today. SQL Service Broker activity can also be traced and traced can be redirected to an XML file for programmatic analysis. Working in real-time, SQL Profiler includes templates to assist in profiling and results can be filtered (e.g. by SQL Server internal process identifier). One particularly impressive feature (which Mat didn’t show last night but which I’ve seen him demonstrate before) is that ability to overlay SQL Profiler data on top of a performance monitor graph, allowing analysis of the effect of a SQL operation on server performance.

Finally, the features for monitoring SQL Server 2005 are enhanced with a new log file viewer (allowing SQL logs to be viewed in combination with Windows event logs), activity monitor (e.g. allowing locks to be viewed in real-time, by process, etc.) and an updated SQL Server Agent which can be used to set alerts, now with multiple proxies (so different logins can be used for different services) and new agent user roles.