Monday, April 30, 2007

ApexSQL Log 2005.04, part II: Smoother user experience

This is the 2nd part of my "ApexSQL Log 2005.04" series which I started here. In the first one I blogged about some of the most important new features and fixes, this time I'm going to mention most important things we did to improve user experience (especially for new users)

Integrated client-side and server-side setups into one setup

Starting with version 2005.03 we have provided a standalone server-side setup for our customers. However, this hasn't been convenient enough so we went one step further and we will now provide one unified setup for client and server-side components.

There are now three setup options:
1. Client application (GUI and CLI) and server-side components on a local server
2. Client application (GUI and CLI)
3. Server-side components on a local server

The only thing worth noting here is that setup can install server-side components only on a local server (this includes virtual servers in failover cluster) and not on a remote server.


Problems with SQL Server run by non-administrator account

In previous versions of the software our server-side components needed high level of privileges which of course led to problems in environments with restricted privileges of the account running SQL Server service. In version 2005.04 we took this problem head on and have significantly lowered the level of privileges needed for server-side components. In the process we have also solved another of problems springing from inability of the account running SQL Server to back-connect to the server itself. There is one known issue left here: Connection Monitor still needs login privileges for the account. In future versions we will allow manual configuration of Connection Monitor connection parameters.

We also had problems with logging on server-side components when lacking privileges. In previous versions server-side logs were stored in System32 (or SysWOW64) folder but in some configurations the account running SQL Server service lacks privileges for writing into system folders. Version 2005.04 stores all server-side logs into "LOG" subdirectory of SQL Server (which, in retrospect, is the ideal place for log files!)

For the record, I think that restricting privileges to SQL Server service (and other services) to bare minimum is a great security practice and one that we certainly try to encourage.

Problems with bad @@SERVERNAME

ApexSQL Log uses @@SERVERNAME to identify server's real name in some situations. But this leads to problems if you change server's network name after SQL Server was already installed. When that happens @@SERVERNAME continues to return the old server name even after the service restart so ApexSQL Log doesn't have access to real machine name which in turn leads to all sorts of problems. This is surprisingly (for me) common situation and we often had to help users fix @@SERVERNAME values. Starting with version 2005.04 this has been fixed. We still use @@SERVERNAME for some internal stuff but connection is now always done through server name as input by user. This also solved the problem with accessing servers available only through IP address or listening on a port other than 1433.

We also solved the same problem but on server-side with Connection Monitor. Connection Monitor has to back connect to SQL Server and used to obtain its server name from @@SERVERNAME. It doesn't any more - it now always connects to 127.0.0.1 with instance name if available retrieved directly from command line that ran SQL Server.

In case you are wondering, to fix @@SERVERNAME so that it returns the correct machine name you can do the following:
1. Execute the following script

sp_dropserver ''
go
sp_addserver '', local
go

2. Restart SQL Server service

No comments: