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

ApexSQL Log 2005.04, part I

As I announced back in March ApexSQL Log 2005.04 is now in QA. It took more time that I thought back then but for a good reason: we went back to our keyboards and did more damage on new issues reported by some customers - especially scaling for very large transaction log files (50 Gb and greater.) In any case 2005.04 version is in QA right now, it's looking great and I hope it will be out soon. In the meantime I'm going to do a small series of posts on improvements that 2005.04 brings starting right now.

Reconstruction of UPDATE operations

Here's the problem of UPDATE reconstructions in a nutshell:
1. In general case, when logging an UPDATE statement, SQL Server just logs what was changed and into what.
2. These before/after state can correspond to everything from sub-field parts to cross-field parts of a row.

So from 1 and 2 comes the following definition of the problem:

To reconstruct fully what happened on a field level in an UPDATE statement one needs to know the state of the row in which the UPDATE statement occurred.

The difficult part of UPDATE reconstruction is finding that original state of the row. We have greatly improved this in version 2005.04 and I will blog more on this later on together with 2005.03/2005.04 examples.

Memory footprint and performance scaling

Storage is getting cheaper, processing power abounds, bandwidths are improving and this all leads to larger databases and higher transactions counts. Both of these increments - larger databases lead to larger MDF files which we use in our recovery process and more transactions lead to larger transaction logs - are beginning to weight on our "last year's" technology. Hence we felt it necessary to redesign the way memory is used by the application in order to allow greater scaling. We also wanted to improve the user experience with the application playing nicely with system resources (well, with memory and disk space at least - as with most other applications we want as much CPU and I/O as we can get.) In this kind of software there is a constant tension between just how much memory we should use (so that we don't have to re-read a lot - which slows things down of course) vs. how does our memory and I/O usage affect the ability of the system on which it is running and its own ability to successfully finish auditing. All of these things (better scaling in memory and performance, playing nicely with the rest of the applications) we have improved greatly in 2005.04. I plan to blog in detail on this soon.

Support for transaction log backups converted from 3rd party backups

Use of 3rd party backup tools is getting more frequent and some of the people using 3rd party backups are also our customers (or want to become one.) However, there is a problem with some 3rd party backups and their converters to MTF (Microsoft Tape Format) files as the converted MTF files do not always match files that SQL Server would have produced. This used to confuse ApexSQL Log but starting with version 2005.04 the application handles correctly these inconsistencies and will now read backups that doesn't perfectly match SQL Server's backups.

Support for reading transaction logs of SQL Server 7/2000 under SQL Server 2005 and vice versa

Format of transaction log changed significantly between SQL Server 2000 and 2005. In ApexSQL Log versions prior to 2005.04 it was not possible to read SQL Server 7/2000 transaction logs when connecting to SQL Server 2005 server or vice versa. However, as migration toward SQL Server 2005 is accelerating (which I believe from anecdotal evidence) there is more and more need for auditing of old transaction logs on newly migrated servers. This situation happens in two instances:
1. When migration is done by detaching the db files from old SQL Server and then attaching them to SQL Server 2005. In this case old transactions are still in the transaction log file but in the format of the previous version of SQL Server.
2. When the need arises to audit old transaction log backups (or detached transaction log files) and the new server is all that we have left available.
Starting with SQL Server 2005.04 we handle both of these cases seamlessly. We also handle reading of SQL Server 2005 transaction logs on SQL Server 7/2000 - just in case anyone ever needs that.

Auditing progress in GUI

With the current version of the software users can't really tell how much more will they have to wait before the results come in. This is , especially for large data sets that we are processing, and I'm sorry we never got around to fixing this prior to 2005.04. But the good news is - it's fixed and I think that audit progress bar is now informative and helpful. The progress is split into two parts:
1. First 50% of the progress are dedicated to initial processing of the transaction log sources we are auditing. Here's a typical shot:


2. Second 50% of the progress are dedicated to filtering of transaction log sources according to the parameters set by the user. However, even when the there is no progress in the number of matching entries, the time in log that is currently being analyzed is shown. Here's a typical shot:

This makes it easy to understand what's going on, where the application is and just how much more (approximately) there is to go.

I think that's it for today. But this isn't all - I'll blog more on 2005.04 soon.

What's the difference between database version and database compatibility level?

Paul Randal, over at SQL Server Storage Engine blog, discusses in his post the difference between database version and database compatibility level. When working with ApexSQL Log this difference is important since any db on SQL Server 2005, even with compatibility level of 70 (SQL Server 7) or 80 (SQL Server 2000), still has the same structure of system tables as a db with the level of 90 (SQL Server 2005). This matters in three cases:
1. When we are doing DDL analysis/recovery since we have to look for changes in different tables under SQL Server 7/2000 and SQL Server 2005 due to complete redesign of system tables in the latter version.
2. When users are directly auditing changes made to system tables (which is sometimes necessary.)
3. When auditing transaction log backups from one version of SQL Server on another. In ApexSQL Log 2005.03 transaction logs from SQL Server 7/2000 could not be read at SQL Server 2005 or vice versa. With the upcoming 2005.04 version this will be done seamlessly.

Of all of these cases only the 3rd is really problematic since we depend on SQL Server to provide us with meta-data for all the tables including system tables. So when we see an operation on say "sysobjects" table from a SQL Server 2000 transaction log attached to SQL Server 2005, we aren't able to reconstruct it since SQL Server 2005 lacks meta-data for "sysobjects" table. This case is very rare but even so we will try to solve it after 2005.04 by building in meta-data for system tables of all three versions of SQL Server that we support.

Btw, in case anyone is wondering how you can audit SQL Server 2005 transaction logs on SQL Server 7/2000 database (considering that it cannot be attached), it can be done by auditing transaction log backups or detached transaction logs.

Update

I have updated Links section with some Software Development and SQL Server links. I have also added Tools and C++ sections with some links. I'll be adding more of these in the future.

Neighborhood poisonings

In other related news to my cat Estrella poisoning, three weeks ago I adopted my first puppy ever.... which has also been poisoned and has been in the vet clinic since Monday. He hasn't eaten any solid food since then and it is still unclear when will he be able to come back home. These poisonings are just the latest in a long string of animal poisonings in my neighborhood. Last year another one of my cats died from it along with at least two more neighborhood cats and one dog. I managed save Rah, my tomcat, even though he was already in his death throws when I found him by pure chance (I managed to take him to the clinic just in time.) Since then he had another poisoning (another poison) and is now suffering from chronic renal failure. This year we had another neighborhood cat die just last month. And now this... This time I have located between 6 and 8 food baits with poison, all one or two houses from mine. Couple of months ago I have also found bait inside of my own garden. So far we have no idea who's doing the poisoning.

Ok, that's it for today's bad news. Next up - something I *wanted* to blog about today.

Estrella

One of my cats died last night due to poisoning. Estrella ("Star" in Spanish) came to live with me a bit over 3 years ago. She was a good momma cat and a loving companion. She will be remembered as a warm, sweet, dear nuisance whenever she wanted to be petted (which was often.) Here she is, two days after her kittens were born:

Estrella, you will be greatly missed. There is a hole in our lives where you used to be and the sadness in our hearts. We will not forget you. I hope that you have found your "Door into Summer".