WebTech

Position filled!

At work we are looking to increase the size of our development team. Are you passionate about databases? Do you also write high-quality stored procedures? If so, then the following job might be for you. Use my contact page to get in touch in the first instance, or feel free to ask questions in the comments section.

Microsoft SQL Server DBA required with good T-SQL skills, XML/XSLT an advantage

Small but highly productive development team seek a talented Microsoft-orientated database administrator and T-SQL programmer to take control of the company's database infrastructure and SQL programming requirements. The successful applicant's role will include:

• Programming high-quality transactional stored procedures.
• Maintaining the company's existing Microsoft SQL Server 2005 and 2008 databases whilst driving policy on backups, redundancy, security and replication.
• Designing and implementing new relational databases to Fifth Normal Form.
• Rationalising existing database infrastructure and driving policy on future database infrastructure.
• Providing support and advice to the application developers, troubleshooting and implementing ad-hoc query requests from around the business.

Applicants should provide examples of their T-SQL stored procedure and database authorship skills upon application (e.g. a few code samples and a database diagram). Applicants may be asked to complete a written test.

This role will suit someone with experience, initiative, flexibility, attention to detail and a strong desire to create high-quality stored procedures and data models.

In return we will provide a flexible working environment in a small but friendly team working on projects for clients including Vodafone, Orange, Motorola and Sony Ericsson. Benefits include 28 days paid holiday (including Bank holidays) and a pub across the road.

I just wanted to blog about a SQL Server 2008 fulltext indexing problem that stopped fulltext search working on all of the production and development servers at work. The problem started on the 4th February with an internal application but has since escalated itself to all 2008 SQL servers on Windows Server 2008. I don't have a fix yet, and we're talking to Microsoft at the moment, but the timing of the problems coincides quite closely with some specific Windows Updates. There are four known symptoms:

Symptom 1
All fulltext queries timeout after 10s with the message:

Symptom 2
The SQL Server ERRORLOG log file largely consists of the following block of error messages repeated thousands of times, usually at a frequency of 1 block per second:

Symptom 3
The FDLAUNCHERRORLOG log file consists of the following block of error messages repeated thousands of times, usually at a frequency of 4 blocks per second:

Symptom 4
The SQLFT* log files consist of lines of following the form (the embedded PK GUID changes for each line) repeated thousands of times, usually at a frequency of 2 lines per second:

Platform notes
The problem is seen when using SQL Server 2008 Standard on Windows Server 2008. The problem is not seen on SQL Server 2005 Express with Advanced Services on Windows Server 2003. The problem appeared on the production server around 1 month after the initiaL SQL install, the problem immediately appeared on an identical development platform upon restoring the affected database from the production environment.

Table structure
The table being indexed ('ArticleContent') has 61583 rows with the following structure (ignoring foreign keys and constraints):

The [content] field holds well-formed and validating HTML 4.01 Strict markup with the note that the HTML, HEAD (and all its children) and BODY elements are not present, only the children of the BODY element are present. The table does not appear to have any locking issues as during fulltext population I can perform updates on the data. The database collation is Latin1_General_CI_AS.

Fulltext index
I have a fulltext index created as follows:

In addition to the manual scripting of index creation, I've also tried creating/editing this via the various Management Studio GUIs. I've also tried different languages, different fields, different tables. All rows are eventually 'processed' during a rebuild, but the above mentioned log files contain one error per tuple and the indexed item count is always zero.

SQL Services
The SQL services run as follows:

Name                                            State      Start Mode Log On As
SQL Server Integration Services 10.0            Stopped    Manual     NT AUTHORITY\NetworkService
SQL Server (SQL_SITE)                           Running    Automatic  NT AUTHORITY\NetworkService
SQL Server (SQL_SITE_IO)                        Running    Automatic  NT AUTHORITY\NetworkService
SQL Full-text Filter Daemon Launcher (SQL_SITE) Running    Manual     NT AUTHORITY\LOCAL SERVICE
SQL Server Agent (SQL_SITE)                     Running    Automatic  NT AUTHORITY\NetworkService
SQL Server Agent (SQL_SITE_IO)                  Running    Automatic  NT AUTHORITY\NetworkService
SQL Server Browser                              Stopped    Manual     NT AUTHORITY\LOCAL SERVICE

I've played around with various Windows users including a Local admin, a Domain user, Local Service, Local System and Network System to no avail. I've played around with granting and resetting child permissions for the local admin and all SQL* users/user-groups full control to data folders, binn folders, you name it. I've granted these users, via the local security policy, the Log-on Locally right, to no avail. My sys-admin has checked for group policy issues and he couldn't find any.

Additional notes
I've played around with the following during various stages of index creation to no avail:
sp_fulltext_service 'restart_all_fdhosts'
sp_fulltext_service 'verify_signature' {, [0|1};
sp_fulltext_service 'load_os_resources' {, [0|1};

I'd be ecstatic if anyone has any ideas on what I can try next to troubleshoot this, but will also update this post when we get a fix as there's so little information about this problem out there at the moment.

Update
No fix yet, but the problem is reproduceable:

1. Freshly install Windows Server 2008, but don't install any Windows Updates
2. Install SQL Server 2008
3. Restore a database exhibiting the problem, or create your own with an FT index. Verify that fulltext search works
4. Install all Windows Updates and reboot.
5. Advance the system clock by at least 2 months (or probably whatever period you have set as the default password expiry period - normally 42 days)
6. Reboot and verify that fulltext search no longer works and displays the behaviour in the above post.
7. Setting the clock back to normal and rebooting does not fix the problem.

We haven't tried the process without the Windows Updates part and it is quite possible that Windows Update has nothing to do with it and that really the problem is solely that the password on a user account used to execute the Fulltext Daemon has expired. Unfortunately we can't find the user account in question.

We're currently working around the problem by rebuilding servers anew and configuring default password expiry to be much more than 42 days.

Update
See the comments for a workaround to this problem.

My latest work project (TMTX) recently launched with two sites, one for Orange:

and one for Sony Ericsson:

Two weeks ago I was rather surprised to get an email from CrossCountry Trains that started "Dear Jenny" and informed me of “exciting changes” to their website.

A couple of days later I received another email from CrossCountry Trains but this time addressed to "Dear Andrew". Someone had sent a test email to all of CrossCountry Trains' customers by accident.

Now, let this be a lesson to every mailling list manager — when writing a test email or creating test-user details, use a real name and plausible sounding details. Because it's just less painful if those details accidentally make it out into the big wide world and they say “Dear Jenny” instead of “Dear Mrs Badger Flaps”.

If you're in the position of buying a new server and are thinking of installing Windows Server 2008, then a handy piece of information is that IIS7 sucks. IIS7 looks to have been rebuilt and has little resemblance to IIS6 and its predecessors. Whilst IIS6 and below had GUI problems, they were known minor problems.

I've only being using IIS7 for two days and these are the problems I've encountered, problems that if I'd known about in advance they I would have stuck with Server 2003:

• IIS7 has no GUI for importing or exporting the individual configuration of virtual directories, applications or their pools — the functionality introduced in IIS6.
• In IIS6 if you set a property on a child object of an application (say you have a CSS folder and you want its caching behaviour to be different from other folders), then you set the same property on the application root itself, IIS6 warns you that you have an override on a child object and do you want to remove the override or keep it? IIS7 doesn't check this — it allows you set both without a warning, but here's the best bit, IIS7 can't handle this. When you browse to content in the folder in question, IIS7 fails with a runtime error about duplicate config settings — this is a failure that you only see when you're browsing the website. This is incredibly bad design. Furthermore because the GUI can't handle the problem either you have to go digging around in the config files that IIS7 litters around your website, but if the error occurs in the C:\Windows\system32\inetsrv\config\applicationHost.config file then you'll encounter the joy that means you can't open the file, even though you're an administrator (my work around is to open Notepad and drag the file onto that, then you can't save it so you have to save it elsewhere and then copy the file into the folder, at which point you have to OK the security error dialogues.
• Error pages in IIS7 are confusing, to the point where I'm still confused 2 days later and don't know how to get the same functionality I want as IIS6. In IIS6 if you want custom error pages you go to the appropriate tab, pick an error and set the URL or file you want to serve instead of the default IIS message. In IIS7 this looks to be the same, so I've set my own paths in the ‘Error Pages’ pane. However, IIS7 has a setting that allows you to prevent detailed error messages from being sent to non-local web surfers — something that looks to be very good practice, except it seems to work 180 degrees from how I expect. Under the ‘Edit Features Settings…’ item on the context menu are the options that allow you to serve only custom error pages, detailed errors only, or a mix of detailed if a local surfer or custom if a remote web surfer. So, having previously set my own custom error page paths I check the ‘custom error pages’ option. However, when I purposely browse to a page that sends a 503 response I don't see my 503 page, but a plain text 'Server unavailable' message. When instead I check show ‘Detailed errors’ my custom error pages now show up instead, most of the time. This is very bad because if the server encounters an error that isn't handled, it spills its guts with a verbose built-in error page. I just want the damn thing to work like IIS6 — I set my own error handlers for almost every error IIS6 can throw and never see the guts of the application spewed over the web. This is so badly implemented in IIS7 I don't know how to proceed to give my end users friendly error messages that behind the scenes email me to let me know there's a problem (HTTP 500s mainly), and to ensure that if my application does fail in ways I haven't anticipated that it doesn't spill its guts publicly, I really don't know.
• Too much in IIS7 requires me to look in the help system (that's a usability red-flag if ever there were one), but when I do go to the help system the help documentation there often regurgitates the text in the part of the application that I'm stuck on with messages akin to the help messages you see in BIOS screens such as “PCI delayed writes” with accompanying help documentation of “Enable/disable PCI delayed writes”. In other words the help system often provides no help whatsoever.

IIS7 has been out for a year, and in my opinion it's not production-ready, and don't get me started on Server 2008 itself (which is Vista) and the re-invented GUIs that try to give the same functionality as earlier Windows versions but do it less clearly, less intuitively — it's like they decided that it would be good for the hell of it to break the usability regimes of earlier versions of Windows. Users accustomed to existing Windows GUIs expect things to work in certain ways. I want consistency for my productivity, I want less clutter, I want readability, I want short and concise GUI text on options and messages, I don't want information overload when encountering trivial errors. Most of all though I don't want to be patronised with “are you sure you want to click this, this requires administrator rights” dialogues that you encounter for pretty much everything you ever want to accomplish with a server OS. These dialogues are such bad usability (because after the 10th one you stop reading them and always click ‘yes’ but still feel pissed off because they're interrupting your work flow) they'll be taught in usability classes for years on what not to do.

Update
Update on the error pages. My testing was using a page that sends 503s, but during configuration the 503 handler had disappeared entirely (not even the default was there) so IIS7 was behaving the way it was.

Position Filled!

For a limited time only you can apply to work directly with me at TMTI Ltd as a web programmer. Here's the job advert:

Visit the TotalJobs website via this link to apply!

Update — Position Filled!
We're looking for even more staff! This time with more of a slant towards SQL. So if you or someone you know is a dab hand with Microsoft T-SQL (you could happily write a stored procedure with a mixture of 10 joins and optimise it without breaking a sweat and the mere mention of the Universal Table doesn't send you running for cover) then get in touch via the contact page and I can send you the details. It'd also be great if you knew some advanced Javascript / JScript, or would be willing to learn some and already have a background in C++ or Java) then that would help.

AVG 8.0 has been released and I've duly upgraded to it from 7.5. Unfortunately the new ‘Web Shield’ component in AVG 8 yields dire performance in the Opera browser (version 9.27). I was beginning to blame it on the browser, my ISP, some of the sites I use (Bloglines, BBC News and Scrabulous) or a problem with WinXP but suddenly remembered I'd upgraded AVG a couple of days before and indeed disabling the ‘Web Shield’ feature in AVG returns Opera to its normal sprightly self.

Unfortunately turning off the feature shows an exclamation icon for AVG in the system tray which is rather annoying, so I've just hidden the AVG icon entirely through ‘Customize (sic) Notifications’ in XP. Hiding the AVG icon is a bit annoying because I notice it not being there and so from time to time ponder whether AVG is running at all, though at least I don't have to look at the displeasingly gaudy new AVG icon.

Not to worry though, turning off ‘Web Shield’ isn't much of a loss if you use Opera, using Opera in the first place is probably your best shield from web-based exploits amongst modern browsers.

Update
In later versions of AVG 8.0 you can disable ‘Web Shield’ and the ‘Link Scanner’ and this doesn't show as an error via the AVG systray icon.