New tools for managing and administering SQL Server 2005

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.

Microsoft and SAP alliance site

I don’t know anything about enterprise resource planning (ERP) products, except that SAP are a big player in this space (and that Microsoft runs its business on SAP with a 1.7Tb SQL Server database – pretty much the only non-Microsoft product in use there). Last night, Mat Stephen mentioned the Microsoft/SAP Alliance website and, after having taken a look this morning, the technology section (including details of how to integrate SAP and Microsoft products) looks pretty useful to me.

Windows Vista only a replacement for XP – Windows Server is still codenamed Longhorn

A few days back, I commented on Microsoft’s announcement of Windows Vista (formerly codenamed Longhorn), speculating as to whether this new name included the next generation Windows server product. It seems not, at least according to the Microsoft Windows Vista Beta 1 fact sheet.

I’m one of the 10,000 beta testers for Windows Vista (not a particularly exclusive club I know…) as well as Longhorn Server and Internet Explorer 7 so I guess I’ll blog some more about Vista after I’ve used it for a while – in the meantime Paul Thurrott has a Vista FAQ on his SuperSite for Windows.

Finally, I’m starting to understand the possible uses for a camera phone…

For a few years now, it’s been becoming increasingly difficult to buy a mobile phone handset which was both stylish and a good phone without also getting a whole load of distractions (games, camera, etc.). Now, I have a smartphone (a Nokia 6600), which is connected to my private number and my work phone (a Nokia 6310i), which is just really good at doing one thing – making and receiving telephone calls (although it does also have infra-red, Bluetooth and GPRS connectivity, all of which are useful).

Throughout all of this, I’ve been skeptical about the need for mobile messaging services (MMS) – the idea that anyone (other than teenagers, who either have too much pocket money, or are running up huge bills for their parents to pay) would want to send low quality photos to one another from their mobile phone. Recently though, I’ve begun to change my mind…

I have a couple of blogs – this (technology focused) one and another for my geographically dispersed friends and family to keep up to date on what is happening in the Wilson family. Moblog pictureFor the last year or so, not much has happened in the Wilson family that hasn’t been focused around my son, so when we were on holiday a few months back I had a bit of dilemma – I didn’t want to take my main camera to the beach, but I did want to grab some photos of him experiencing sand and surf for the first time. That’s when the camera in my mobile came in useful. At 640×480 (VGA), the images are never going to be great for printing, but they are just fine for display at 72dpi on a computer screen.

Now it seems that mobile blogging (moblogs) have become a bit of a phenomenon. According to a Nokia moblog backgrounder:

“Blogs are one of the fastest growing phenomenon on the Internet with over 6.5 million web logs available and thousands more coming online every day. Blogs are like diaries which allow people to publish their thoughts and opinions or simply act as a record of their lives. Blogs can be accessed by friends, family or anyone on the web. The sharing of photos is currently one of the most important drivers for creating weblogs… Moblogging takes this phenomenon to the next level, allowing people to use their mobile phones to instantly publish their life experiences on the web. You can post pictures, video and text from your camera phone directly to the web instantly and then share your memories with family and friends.”

It’s this phenomenon (along with “traditional” digital cameras and a PC) that’s driving the incredible growth of sites like Flickr, Moblog and Phlog.

But it’s not just ordinary (pseudo-)geeks like me that are getting on the moblog bandwagon (actually, I’m not quite there yet, but might be soon…) – Sony Ericsson and American Photo Magazine teamed up with photographer Robert Clark for his Image America project. Admittedly that was as much for Sony Ericsson to promote their latest camera phone but there’s also an interesting article from a BBC reporter on his experiences as he reported back from his US Road Trip family holiday via a moblog using Flickr and Blogger.

If only moblogging had existed when I was travelling around Australia a few years back…

Grabbing screenshots using the Microsoft virtual machine remote control client

I just discovered this and think it’s really useful…

I’m in the process of documenting a client’s server configuration, using a virtual machine with a VPN connection to the client’s network and then a remote desktop protocol (RDP) connection to their servers. Because the VPN is within a virtual machine, I’m constrained by the limitations of the Microsoft virtual machine remote control (VMRC) client and thought I could only take full screen screenshots, using VMRC’s Remote Control | Special Keys | Send Print Screen menu commands. What I found (completely by accident) is that if I use the mouse to send the print screen command, the whole screen is captured; however, if I use the keyboard (Alt+R | right cursor | down cursor | carriage return) it acts like an Alt+PrtSc, and only the contents of the active window are copied to the clipboard.

I’m not sure if this is true for all clients (I haven’t tested further), but my setup was:

  • Windows Server 2003 SP1 host.
  • Virtual Server 2005 (v1.1.465.0 SE).
  • Windows XP SP2 guest (with virtual machine additions 13.206 installed), VPNed into the client’s network using the Cisco Systems VPN client (v4.6.02.0011) and then RDPed into a Windows 2000 SP4 server (RDP client v5.1.2600.2180).

Configuring web proxy auto discovery for Internet Explorer clients

Over the last few weeks, I’ve been looking at using web proxy auto discovery (WPAD) to let a client’s PCs automatically discover the location of their Microsoft ISA Server 2000 web proxy servers through the Internet Explorer client. Note that WPAD is used by web proxy clients and firewall clients use winsock proxy auto detection (WSPAD).

Microsoft knowledge base article 296591 gives background information on WPAD (for WSPAD see Microsoft knowledge base article 260210) but basically, what is involved is:

  • A properly configured web proxy client (i.e one which has automatically detect settings checked in the Internet Explorer LAN connection settings) queries the DHCP server for option 252, which identifies an HTTP address for a file called wpad.dat which is ISA Server’s dynamically generated proxy auto configuration (PAC) file.
  • If a DHCP server does not respond with option 252, the web proxy client attempts to access http://wpad.domainsuffix:80/wpad.dat (or http://wpad.domainsuffix:80/wspad.dat for the firewall client). To locate this URL, the remote client queries its configured DNS servers for wpad.domainsuffix – obviously issues with incorrectly configured domain suffixes will prevent automatic discovery from working. Microsoft knowledge base article 307502 also indicates that the WPAD address is case sensitive.

It should be noted that WPAD is not supported for clients that connect to the LAN with any type of dial-up connection.

To set up WPAD, three steps are involved, as detailed in Microsoft knowledge base article 309814 (Windows 2000) and Microsoft knowledge base article 816320 (Windows Server 2003):

  • The web proxy servers must publish automatic discovery information (which might require the web proxy service to be restarted).
  • DHCP (and optionally, DNS) needs to be configured to send the WPAD URL to the web proxy client (as detailed in Microsoft knowledge base article 252898).
  • Finally, the clients need to be set to automatically detect settings.
  • We planned to roll out WPAD on a site-by-site basis, using DHCP (adding a DNS entry would affect all clients) and everything looked good using DHCP alone (no DNS installed) in my test environment; however the existing route used for production clients to access the Internet is direct via the firewall, and so the clients failed to use the DHCP-assigned WPAD information as the direct path was working (that’s the theory – it is difficult to diagnose the DHCP traffic to that level of certainty, other than using a network monitor and examining packets).

    One possibility for the failure is described in Microsoft knowledge base article 312864 but I could not replicate this behaviour in testing and as it is only linked from the Windows Server 2003 version of the knowledge base article describing configuration of firewall and web proxy client auto discovery, I am not convinced that the article applies to clients using Windows 2000 DHCP servers.

    The current plan is to use a group policy object, filtered by group membership, to manipulate client proxy settings and use http://proxyarray.domainname.suffix/wpad.dat as an automatic configuration script. This has the advantage that we can control who can access the Internet (take a user out of the group to remove their proxy access – once the direct path has been removed), but does not use WPAD at all.

    One comment which my client made was that the wpad.dat file which ISA Server uses looks complex compared to the .PAC files used by the parent company’s web proxy servers. We could have used a simple .PAC file, but the major advantage of wpad.dat is that it is updated dynamically to reflect changes in the proxy server configuration.

    Searching for a Visio stencil or template?

    A couple of nights back, I was documenting the rack configuration for a client’s data centre. Easy enough using a rack configuration tool from one of the major hardware vendors, except that most of us have multi-vendor rack contents and use Microsoft Visio to record the details. Enter the index of Visio download sites. Using this I was able to locate and download Visio stencils for Compaq/HP hardware, although Visio stencils for Dell servers seem to be a bit thin on the ground…

    We are not afraid: photo blogging at its best

    This is a technology blog and as such, I don’t cover politics. I do sometimes work in London though. As do many of my friends and family. And I do like it when somebody uses technology to push home a message – like that WE’RE NOT AFRAID of terrorism.

    We are not afraid

    Here are some of my favourites from the galleries on the We’re Not Afraid photo blog site.

    We are not afraid
    We are not afraid
    We are not afraid
    We are not afraid
    We are not afraid
    We are not afraid
    We are not afraid
    We are not afraid

    Get the message?


    London bomb victims book of condolence
    British Red Cross London Bombings Appeal

    Announcing Windows Vista

    Microsoft have announced the name for the next version of Windows (formerly codenamed Longhorn) – Windows Vista.

    Windows VistaI’m not overly impressed with the name (how about Windows 2006?) but looks like they are going with it. What’s not clear is whether this is just the client version, or the next Windows server release too. Perhaps I’ll find out more when I finally get access to the beta in a couple of weeks time.