SQL Server 2008 R2 cluster setup fails on mount points

(Update from Monday morning, the workaround shown below works for me)

Another day, another set up problem (sigh) I was installing a second SQL 2008 R2 instance on a new cluster today. The first one had gone well without incident but the second one had a crucial change in that it used mount points. It was a fairly complex install with 8 mount points under the root volume and it took a while to complete all the setup configurations. When it got started it failed with the following selection of errors:

1. A dialog box with the error

Wait on database engine recovery handle failed. Check the SQL Server error log for potential causes.

2. The setup finished and pointed me to the usual error log files in the setup directories. In the summary log file I saw an equivalent error

Detailed results:
Feature: Database Engine Services
Status: Failed: see logs for details
MSI status: Passed
Configuration status: Failed: see details below
Configuration error code: 0x4BDAF9BA@1306@24
Configuration error description: Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.
Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20120420_114647\Detail.txt

3. When opening the referred to detail.txt file I found pretty much the same again

2012-04-20 12:17:28 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigRC and scenario ConfigRC.
2012-04-20 12:17:28 Slp: Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.
2012-04-20 12:17:28 Slp: The configuration failure category of current exception is ConfigurationFailure
2012-04-20 12:17:28 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigRC and scenario ConfigRC.

4. The text file pointed me to the SQL Server error log of the partially installed instance. When I opened this, I found the “real” cause

2012-04-20 16:00:51.51 spid8s Clearing tempdb database.
2012-04-20 16:00:51.52 spid8s Error: 5123, Severity: 16, State: 1.
2012-04-20 16:00:51.52 spid8s CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15100) while attempting to open or create the physical file 'O:\data\tempdb.mdf'.
2012-04-20 16:00:51.52 spid8s Error: 5123, Severity: 16, State: 1.
2012-04-20 16:00:51.52 spid8s CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15105) while attempting to open or create the physical file 'O:\data\tempdb.mdf'.

The SQL Server error code 5123 is why the instance couldn’t start, as it’s unable to create a tempdb database data file, and no SQL Server can start without tempdb being present. The “real” error though is the reason for this occurring which is error code 5 from the OS when SQL runs the createfile API. This is one that everyone probably recognises which is “access denied”. So the bottom line here was that I was getting access denied on the root of my mount point for tempdb (which is where I had placed the data files as part of setup configuration).

I checked through the other parts of setup and the rest of the system databases had managed to write to the root of other mount point drives (which seemed strange), and of more relevance, I noted that the SQL Server service accounts had been granted full access to the mount point root directories as part of the setup, so theoretically there ought not to be a permission error!

I spent a few hours digging around, tried the install again with new mount points and a clean position, but encountered the exact same problem. Eventually I figured that it was due to the fact that there are “issues” with the permissions on root mount points. This is distinctly not well documented within official MS sources (or not that I could find) and certainly not within the official pre-reqs.

http://msdn.microsoft.com/en-us/library/ms189910(v=sql.105).aspx

The best I could find was a connect item here

http://connect.microsoft.com/SQLServer/feedback/details/569895/installation-of-sql-server-2008-r2-on-mount-points-fails

a blog post here

http://getyouriton.blogspot.co.uk/2009/08/serious-gotchas-with-mounted-drives-or.html

and a forum post here

http://social.msdn.microsoft.com/Forums/en/sqlsetupandupgrade/thread/da05e467-6852-4e2d-8b8f-d253b479acfc

However the accepted workaround is that you should create a sub directory under the root of the mount point and then you’ll be fine. I’ll be trying this method next week as its 8pm on a Friday now and time for the weekend. If it works I’ll probably post a bug requesting that the documentation gets changed to point this out, as it would have me a whole lot of time.

As always, good luck with your setup……

Example of SQL Server collation usage and problems

I was discussing with some developers this week why SQL Server collation mattered and what are typical things that can go wrong. This is a huge topic and is an often over looked feature of database and server design, especially when you want to build applications that run in multiple geographic locations and that should accept multiple language inputs. Being as I’ve worked extensively with the Finnish Swedish collation from my time working in Sweden, I have a reasonable level of understanding of some of the issues that can come up.

I wrote the following script to demonstrate an example of what can happen if you don’t get your collations correct. It’s probably the most common example I’ve seen over the years.

1. You have a SQL Server that is running one of the main Latin collations for English, be it UK, US, current windows collation or legacy SQL Server ones.

2. You have data from multiple languages stored in unicode columns such as nvarchar

3. You sort or search on the data and those people who have used data from non English languages that have special or extra characters in the alphabet, do not get the results that they expect.

This script only shows a fraction of the things that can actually go wrong, but it provides a simple demonstration of sorts and searches producing unexpected results. It creates its own database and tables on any test server you may choose to run it on (and then deletes them afterwards). It will work as a demo on any server as long as it’s not already running a Finnish Swedish collation.


use master
go

--i'm creating a non-default latin collation as per the legacy product I was looking at
--in this case however the results are the same as if you're running the more common default windows server collation 'Latin1_General_CI_AS'
create database collation_test collate SQL_Latin1_General_CP1_CI_AS
go

use collation_test
go

select SERVERPROPERTY('collation') as 'server collation'

select collation_name as database_collation from sys.databases where name = 'collation_test'

if exists
(
select name from sys.objects where name = 'collation_test' and type = 'u'
)

begin
 drop table collation_test
end

create table collation_test
(
test nvarchar(10)
)

set nocount on
go

insert collation_test (test) values ('aaaa')
insert collation_test (test) values ('ääää')
insert collation_test (test) values ('åååå')
insert collation_test (test) values ('öööö')
insert collation_test (test) values ('bbbb')
insert collation_test (test) values ('zzzz')

set nocount off
go

print 'select the results from the tables in differing collations'
print 'in Swedish alphabet the characters åäö follow after Z as characters 27 to 29'
print''
select test as "order in latin collation"
from collation_test
order by test

select test as "order in Finnish_Swedish_CI_AS collation"
from collation_test
order by test collate Finnish_Swedish_CI_AS

print 'do searches on the table'
print ''

select test as "search in latin collation"
from collation_test
where test > 'b'

select test as "search in Finnish_Swedish_CI_AS collation"
from collation_test
where test > 'b'
collate Finnish_Swedish_CI_AS

--clean up
use master
go
drop database collation_test
go

The bottom line is that if you want to do collations and mixed languages properly, you need to think about this stuff carefully at the design stage.

Some thoughts and follow up to KAM careers fair at KTH Stockholm

The last 3 weeks of my UK day job have been a tad boring, but this is the case sometimes. I’ve just been trying to get a task out the door which involved lots of large TSQL code for reporting and data analysis. This type of thing leaves me cold and is not something I get involved with very often (well not in this century at least, I used to do this stuff in ’97/98 but hey….it was a favour for someone and it’s nearly done now!) anyway fortunately I was in Stockholm last week to talk at the Royal Institute of Technology careers days (Kungliga Tekniska högskolan Arbetsmarknadsdag for those Swedish speakers amongst you) which was a fantastic time.

I met some seriously intelligent people and enjoyed chewing the fat with many of them about starting out a career in the IT sector. I hope that I provided a rather different outlook to some of the speakers and exhibitors at the conference as I left school at 17 with no higher education whatsoever (let alone a bachelors or masters degree) and it took me 6 years or so to even get someone to give me a job remotely close to IT and another 2 to actually get myself placed in a real IT job in a software house. Add to this my rather varied career paths since which have veered between investment banks to year one start-ups and Microsoft, and I hope that I gave a different perspective to some of the students. it certainly felt like a success and the company I was representing (Basefarm – who I still consult for regularly) received a large number of applications for internships and employment.

As well as being on the Basefarm stand all day just chatting to whoever came along, I did a talk in the afternoon entitled “This much I know……” where I expanded on some of the above thoughts and compared and contrasted a number of different companies, company types, job types and locations and what they were like to work for, and how they had helped or hindered the development of my career. Hopefully those present learnt a few tips to help them mould their own career paths. I try to not make the talk into a lecture, because I don’t want to give the impression I know too much, or that I’m teaching people how they should act, it’s far more an approach of hoping that people will pick up tips and trends based on some of the things I’ve experienced along the way (both good and bad).

I’ll probably be doing similar stuff at other universities in Sweden over the next year so, but I’d be happy to be involved in any UK based ones as well, so if you’re organising a careers fair in the UK, please drop me a line if you’d be interested in having me along. My current employer in the UK, dotDigitalGroup, has taken on a large number of IT graduates over the years and I think it’s a testament to the company the number of technical employees that they still retain where it’s their first job after university. It’s quite uncommon in my experience for an organisation to be able to maintain such loyalty and I think it speaks volumes for how much people like working there. I think we’d be able to share some interesting career thoughts and possibilities with UK based students, so feel free to drop me a line if this sounds interesting to you.