Installation of SQL Server 2005 X64 On Windows 2003 R2

W2K3R2_SQL2005_FIX.md

README

This is a document of the steps taken to install SQL Server 2005 on Windows Server 2003 R2.

Background

I am trying to create a fail-over cluster installation of MS SQL Server 2005.

I followed the instructions on MSDN. They do not work (for me).

Installation to both nodes in the cluster fails as the target drives are only available on the active node in the cluster group. The setup process does not move the group to a node before starting the installation on it. Nice.

Attempting installation on a single node in the cluster group also fails. This is where this document comes in handy.

When installation of the database engine fails it is uninstalled. This does not make debugging easier.

Server Specification

• Processors

  • 2 X 3.896 GHz

• Memory

  • 5120 MB

• CD/DVD Drive 1 (IDE 1:0)

• SCSI Controller 0

  • LSI Logic

• SCSI Controller 1

  • LSI Logic

• Network Adapter 1

  • VMnet2

• Network Adapter 2

  • VMnet3

• Hard Disk 1 (SCSI 0:0)

  • 32.00 GB
  • OS

• Hard Disk 2 (SCSI 1:0)

  • 624.00 GB
  • V:
  • SQL-DATA

• Hard Disk 3 (SCSI 1:1)

  • 208.00 GB
  • U:
  • SQL-LOG

• Hard Disk 4 (SCSI 1:2)

  • 48.00 GB
  • T:
  • tempdb

• Hard Disk 5 (SCSI 1:3)

  • 512 MB
  • M:
  • DTC

• Hard Disk 6 (SCSI 1:4)

  • 512 MB
  • Q:
  • Quorum

• Hard Disk 7 (SCSI 1:5)

  • 250 MB
  • D:
  • SQL-INSTALLER (Sucks)

Hosts

There are two hosts in the cluster group

1. 2k3-sql-05de01a

• IP Address
  • 192.168.2.71 LAN
  • 192.168.3.71 Heartbeat

2. 2k3-sql-05de01b

• IP Address
  • 192.168.2.72 LAN
  • 192.168.3.72 Heartbeat

Cluster Administrator

There are three cluster groups

1. Cluster Group
   • Cluster Disk (Q:)
   • Cluster IP Address
     • 192.168.2.76
   • Cluster Network Name
     • 2k3-sql-05de01m
2. MSDTC
   • MSDTC
   • MSDTC Disk (M:)
   • MSDTC IP Address
     • 192.168.2.77
   • MSDTC Network Name
     • 2k3-sql-05de01t
3. MSSQL
   • MSSQL Data Disk (V:)
   • MSSQL Installer Disk (D:)
   • MSSQL Log Disk (U:)
   • MSSQL Tempdb Disk (T:)

The 'm' is for cluster management and the 't' is for distributed transaction coordinator.

Domain Accounts

First	Middle	Last	Login
Cluster		Service	cluster
SQL	Server	Database	ssde
SQL	Server	Agent	ssag
SQL	Services	Analysis	ssas
SQL	Services	Reporting	ssrs
SQL	Services	Notification	ssns
SQL	Services	Integration	ssis
SQL	Server	Browser	ssbr
SQL	Server	Full Text Search	ssft

The accounts

• ssde
• ssag

are made Local Administrators on both of the cluster nodes.

Setup

I run the installer selecting

• Database (fail-over)
• Notification Services
• Integration Services
• Workstation Components

I complete the cluster information

• IP Address LAN / 192.168.2.70 / 255.255.255.0
• Network Name 2k3-sql-05de01
• Cluster Group MSSQL / V: Drive

I customize the accounts using those created on the domain earlier.

Go!

Fail

When it gets to the database engine it tells me it has failed. I can retry or cancel.

I'll retry ...

Analyze Errors

I now run the SQL Server Database Engine from the console.

C:\Documents and Settings\swenmouth>cd "\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn"

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>sqlservr.exe -c 2012-02-09 01:31:56.21 Server Authentication mode is MIXED.

2012-02-09 01:31:56.30 Server Microsoft SQL Server 2005 - 9.00.1399.06 (X64)

    Oct 14 2005 00:35:21

    Copyright (c) 1988-2005 Microsoft Corporation

    Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

2012-02-09 01:31:56.30 Server (c) 2005 Microsoft Corporation.

2012-02-09 01:31:56.30 Server All rights reserved.

2012-02-09 01:31:56.30 Server Server process ID is 3040.

2012-02-09 01:31:56.30 Server Logging SQL Server messages in file 'V:\Micro soft > SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.

2012-02-09 01:31:56.30 Server Registry startup parameters:

2012-02-09 01:31:56.33 Server -d V:\Microsoft SQL Server\MSSQL.1\MSSQ > L\DATA\master.mdf

2012-02-09 01:31:56.33 Server -e V:\Microsoft SQL Server\MSSQL.1\MSSQ > L\LOG\ERRORLOG

2012-02-09 01:31:56.33 Server -l V:\Microsoft SQL Server\MSSQL.1\MSSQ > L\DATA\mastlog.ldf

2012-02-09 01:31:56.33 Server Command Line Startup Parameters:

2012-02-09 01:31:56.33 Server -c

2012-02-09 01:31:56.51 Server SQL Server is starting at normal priority bas e (=7). This is an informational message only. No user action is required.

2012-02-09 01:31:56.51 Server Detected 2 CPUs. This is an informational mes sage; no user action is required.

2012-02-09 01:31:56.85 Server Error: 8316, Severity: 16, State: 1.

2012-02-09 01:31:56.85 Server Cannot open registry key 'HKLM\SYSTEM\Current ControlSet\Services\MSSQLSERVER\Performance'. SQL Server performance counters ar e disabled.

2012-02-09 01:31:57.60 Server Using dynamic lock allocation. Initial alloc ation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an infor mational message only. No user action is required.

2012-02-09 01:31:58.15 Server Attempting to initialize Microsoft Distribute d Transaction Coordinator (MS DTC). This is an informational message only. No us er action is required.

2012-02-09 01:32:00.75 Server Attempting to recover in-doubt distributed tr ansactions involving Microsoft Distributed Transaction Coordinator (MS DTC). Thi s is an informational message only. No user action is required.

2012-02-09 01:32:00.83 Server Database Mirroring Transport is disabled in t he endpoint configuration.

2012-02-09 01:32:01.00 spid5s Starting up database 'master'.

2012-02-09 01:32:01.80 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.

2012-02-09 01:32:02.58 spid5s SQL Trace ID 1 was started by login "sa".

2012-02-09 01:32:02.91 spid5s Starting up database 'mssqlsystemresource'.

2012-02-09 01:32:02.91 spid5s Error: 17207, Severity: 16, State: 1.

2012-02-09 01:32:02.91 spid5s FCB::Open: Operating system error 3(The syste m cannot find the path specified.) occurred while creating or opening file 'D:\W INDOWS\TEMP\mkmastr_result\mssqlsystemresource.mdf'. Diagnose and correct the op erating system error, and retry the operation.

2012-02-09 01:32:02.96 spid5s Error: 17204, Severity: 16, State: 1.

2012-02-09 01:32:02.96 spid5s FCB::Open failed: Could not open file D:\WIND OWS\TEMP\mkmastr_result\mssqlsystemresource.mdf for file number 1. OS error: 3( The system cannot find the path specified.).

2012-02-09 01:32:03.01 spid5s Error: 5120, Severity: 16, State: 101.

2012-02-09 01:32:03.01 spid5s Unable to open the physical file "D:\WINDOWS\ TEMP\mkmastr_result\mssqlsystemresource.mdf". Operating system error 3: "3(The s ystem cannot find the path specified.)".

2012-02-09 01:32:03.33 spid5s Error: 17207, Severity: 16, State: 1.

2012-02-09 01:32:03.33 spid5s FileMgr::StartLogFiles: Operating system erro r 2(The system cannot find the file specified.) occurred while creating or openi ng file 'D:\WINDOWS\TEMP\mkmastr_result\mssqlsystemresource.ldf'. Diagnose and c orrect the operating system error, and retry the operation.

2012-02-09 01:32:03.35 spid5s File activation failure. The physical file na me "D:\WINDOWS\TEMP\mkmastr_result\mssqlsystemresource.ldf" may be incorrect.

2012-02-09 01:32:03.38 spid5s Error: 945, Severity: 14, State: 2.

2012-02-09 01:32:03.38 spid5s Database 'mssqlsystemresource' cannot be open ed due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

2012-02-09 01:32:03.40 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is requir ed.

Okey dokes - it thinks the database is somewhere it is not.

FIX

Create the missing directory

C:\Documents and Settings\swenmouth>mkdir D:\WINDOWS\TEMP\mkmastr_result

Copy the files

C:\Documents and Settings\swenmouth>copy "V:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.*" D:\WINDOWS\TEMP\mkmastr_result

V:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.ldf

V:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.mdf

    2 file(s) copied.

Try Starting SQL Server (Again)

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>sqlservr.exe -c -f -T3608

2012-02-09 01:40:09.26 Server Authentication mode is MIXED.

2012-02-09 01:40:09.27 Server Microsoft SQL Server 2005 - 9.00.1399.06 (X64)

    Oct 14 2005 00:35:21
    Copyright (c) 1988-2005 Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

2012-02-09 01:40:09.27 Server (c) 2005 Microsoft Corporation.

2012-02-09 01:40:09.27 Server All rights reserved.

2012-02-09 01:40:09.27 Server Server process ID is 956.

2012-02-09 01:40:09.27 Server Logging SQL Server messages in file 'V:\Micro soft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.

2012-02-09 01:40:09.27 Server This instance of SQL Server last reported usi ng a process ID of 2044 at 2/9/2012 1:40:07 AM (local) 2/9/2012 7:40:07 AM (UTC) . This is an informational message only; no user action is required.

2012-02-09 01:40:09.27 Server Registry startup parameters:

2012-02-09 01:40:09.27 Server -d V:\Microsoft SQL Server\MSSQL.1\MSSQ L\DATA\master.mdf

2012-02-09 01:40:09.27 Server -e V:\Microsoft SQL Server\MSSQL.1\MSSQ L\LOG\ERRORLOG

2012-02-09 01:40:09.29 Server -l V:\Microsoft SQL Server\MSSQL.1\MSSQ L\DATA\mastlog.ldf

2012-02-09 01:40:09.29 Server Command Line Startup Parameters:

2012-02-09 01:40:09.29 Server -c

2012-02-09 01:40:09.29 Server -f

2012-02-09 01:40:09.29 Server -T 3608

2012-02-09 01:40:09.29 Server Warning: The server instance was started usin g minimal configuration startup option (-f). Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically. After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the serve r.

2012-02-09 01:40:09.29 Server SQL Server is starting at normal priority bas e (=7). This is an informational message only. No user action is required.

2012-02-09 01:40:09.29 Server Detected 2 CPUs. This is an informational mes sage; no user action is required.

2012-02-09 01:40:09.45 Server Using dynamic lock allocation. Initial alloc ation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an infor mational message only. No user action is required.

2012-02-09 01:40:09.49 Server Support for distributed transactions was not enabled for this instance of the Database Engine because it was started using th e minimal configuration option. This is an informational message only. No user a ction is required.

2012-02-09 01:40:09.51 spid5s Warning ******************

2012-02-09 01:40:09.51 spid5s SQL Server started in single-user mode. This an informational message only. No user action is required.

2012-02-09 01:40:09.51 spid5s Recovering only master database because trace flag 3608 was specified. This is an informational message only. No user action i s required.

2012-02-09 01:40:09.51 spid5s Starting up database 'master'.

2012-02-09 01:40:09.66 spid5s 2 transactions rolled forward in database 'ma ster' (1). This is an informational message only. No user action is required.

2012-02-09 01:40:09.73 spid5s Snapshot isolation or read committed snapshot is not available in database 'master' because SQL Server was started with one o r more undocumented trace flags that prevent enabling database for versioning. T ransaction started with snapshot isolation will fail and a query running under r ead committed snapshot will succeed but will resort back to lock based read comm itted.

2012-02-09 01:40:09.73 spid5s 0 transactions rolled back in database 'maste r' (1). This is an informational message only. No user action is required.

2012-02-09 01:40:09.73 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.

2012-02-09 01:40:09.80 spid5s Server started with '-f' option. Auditing wil l not be started. This is an informational message only; no user action is requi red.

2012-02-09 01:40:09.82 spid5s Server name is '2K3-SQL-05DE01A'. This is an informational message only. No user action is required.

2012-02-09 01:40:10.10 Server A self-generated certificate was successfully loaded for encryption.

2012-02-09 01:40:10.10 Server Server is listening on [ 'any' 1433].

2012-02-09 01:40:10.12 Server Server local connection provider is ready to accept connection on [ \.\pipe\SQLLocal\MSSQLSERVER ].

2012-02-09 01:40:10.12 Server Server named pipe provider is ready to accept connection on [ \.\pipe\sql\query ].

2012-02-09 01:40:10.12 Server Server is listening on [ 127.0.0.1 143 4].

2012-02-09 01:40:10.12 Server Dedicated admin connection support was establ ished for listening locally on port 1434.

2012-02-09 01:40:10.16 Server SQL Server is now ready for client connection s. This is an informational message; no user action is required.

2012-02-09 01:40:10.18 spid5s Recovery is complete. This is an informationa l message only. No user action is required.

Yay. This time it turns on.

Inspect file locations

C:\Program Files\Microsoft SQL Server\90\Tools\binn>sqlcmd -U sa -d master -s "|" -W

Password:

1> SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files;

3> GO

name|CurrentLocation|state_desc

----|---------------|----------

master|V:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf|ONLINE

mastlog|V:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf|ONLINE

tempdev|D:\WINDOWS\TEMP\mkmastr_result\tempdb.mdf|ONLINE

templog|D:\WINDOWS\TEMP\mkmastr_result\templog.ldf|ONLINE

modeldev|D:\WINDOWS\TEMP\mkmastr_result\model.mdf|ONLINE

modellog|D:\WINDOWS\TEMP\mkmastr_result\modellog.ldf|ONLINE

MSDBData|D:\WINDOWS\TEMP\mkmastr_result\MSDBData.mdf|ONLINE

MSDBLog|D:\WINDOWS\TEMP\mkmastr_result\MSDBLog.ldf|ONLINE

(8 rows affected)

Okay. We can see that

• tempdb
• model
• msdb

are thought to be somewhere they're not.

Update file locations

Here are a sequence of alter statements that will right the wrong file locations.

In between every section restart SQL Server at the command prompt (with Ctrl^C and re-run).

mssqlserverresource

ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= 'V:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.mdf');
GO

ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= 'V:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.ldf');
GO

master

ALTER DATABASE master MODIFY FILE (NAME=master, FILENAME= 'V:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf');
GO

ALTER DATABASE master MODIFY FILE (NAME=mastlog, FILENAME= 'V:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf');
GO

model

ALTER DATABASE model MODIFY FILE (NAME=modeldev, FILENAME= 'V:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\model.mdf');
GO

ALTER DATABASE model MODIFY FILE (NAME=modellog, FILENAME= 'V:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\modellog.ldf');
GO

msdb

ALTER DATABASE msdb MODIFY FILE (NAME=msdbdata, FILENAME= 'V:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdbdata.mdf');
GO

ALTER DATABASE msdb MODIFY FILE (NAME=msdblog, FILENAME= 'V:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdblog.ldf');
GO

tempdb

ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, FILENAME= 'T:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\tempdb.mdf');
GO

ALTER DATABASE tempdb MODIFY FILE (NAME=templog, FILENAME= 'T:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\templog.ldf');
GO

Try Again

Back in the setup process

1. Click Retry
2. Watch it fail
3. Read event log

Product: Microsoft SQL Server 2005 (64-bit) -- Error 29503. The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually." The error is (1069) The service did not start due to a logon failure.

Local Security Settings

The permissions from the Domain Security Group are not being picked up. Manually add permissions.

Permission	Code	ssde	ssag	ssas	ssrs	ssns	ssis	ssbr	ssft
Log on as a service	SeServiceLogonRight	Y	Y	Y	Y		Y	Y	Y
Act as part of the operating system	SeTcbPrivilege	Y	Y
Log on as a batch job	SeBatchLogonRight	Y	Y
Replace a process-level token	SeAssignPrimaryTokenPrivilege	Y	Y
Bypass traverse checking	SeChangeNotifyPrivilege	Y	Y				Y
Adjust memory quotas for a process	SeIncreaseQuotaPrivilege	Y	Y
Create global objects	SeCreateGlobalPrivilege						Y
Impersonate a client after authentication	SeImpersonatePrivilege						Y
Member of "Local Administrators"		Y	Y

Try Once Mode ...

It works! Update the local security policy on the b node now.