June 24, 2008 at 1:12 am
Not able to find document about how to install sql server 2005 on windows 2008 failover cluster, wondering if anyone has the experience and will to share ? thanks.
ddfg
June 24, 2008 at 5:55 am
I would imagine the process is similar to windows 2003, assuming that we are picking this up at the SQL Server installation and the windows cluster is already setup and configured. I assume that the SQL installation process will recognise the cluster and enable the the virtual server installation and provide you with the cluster install options.
I have never done this though so would be interested to see if it differs.
Gethyn Elliswww.gethynellis.com
June 30, 2008 at 8:45 am
I just installed my first SQL Server 2005 x64 Enterprise Edition virtual server on a Windows 2008 Enterprise Edition Cluster. It is basically the same install as on Windows 2003. The major issues I noticed were compatibility issues for the installation of the SSIS and Business Intelligence Studio option. After the installation it will not allow you to do the following:
1) Create a Maintenace Plan - This throws some strange errors. I did find a work arround by opening the Studio and creating the SSIS package manually.
2) Use Database Mail - you get the generic 'Activation Failure' error when looking at the DB mail log.
I have opened a case with Microsoft for these issues.
Visual Stuido 2005 is not compatible with Windows 2008 or Vista.
My personal opinion is that you will need to install Visual Studio 2008.
July 7, 2008 at 6:11 am
Did you get a solution from Microsoft ? - I have struggle with issue No. 1 for some 3 days now - just keep getting to the point that applying SP2 doesn't seem to update the mssqlsystemresource db.
/Rasmus Glibstrup
July 22, 2008 at 5:19 am
I finally had all my issues resolved with SQL 2005 on a Windows 2008 Cluster.
There were several issues:
1) Issues with SSPI handshakes when the service account was trying to use Kerberos over TCP/IP registering SPN(s) when bringing SQL service online in 'Failover Cluster Management'
This was resolved by granting write and read access for the SQL startup account to add and remove SPN(s) (Server Principal Names) from AD.
2) Problems with SP2 update. The service pack would install properly but when creating a maintenance plan it would fail.
This was resolved by manually running the query sysdbupg.sql located in the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install folder.
3) Problems with Database Mail using Basic Authentication.
Remove mail profile and account. Add the Mail profile and account back using the option 'Windows Authentication using Database Engine Service Credentials'.
4) Removal of the BUILTIN\Administrators will cause the SQL Service not to start via Failover Cluster Management even though the SQL Startup account is a system admin on the SQL Server.
This was resolved by adding NT AUTHORITY\SYSTEM as a 'sysadmin' to SQL Server.
I have come up with a standard build sheet for installing SQL 2005 on Windows 2008 Cluster: (This only works if you have granted your SQL startup accounts the ability to read and write SPN(s) in Active Directory).
1) Verify Cryptographic service, Remote registry, & Task Schedule are running on both nodes.
2) Verify no connections on passive node.
3) Configure Network Transactions for local MSDTC on each node. (http://technet2.microsoft.com/windowsserver2008/en/library/4532d3d5-a406-492e-9e74-daa70c6591461033.mspx?mfr=true)
4) Create 'Empty Service or Application' in Failover Cluster Management console rename to SQL Virtual name.
5) Assign appropriate LUN(s) to newly created Service/Application.
6) Install SQL Server 2005 (RTM).
7) Install SQL Service Pack 2.
8) Post SP 2 Hotfix (Build 3050).
9) Install Visual Studio SP1.
10) Visual Studio Post SP1 Hotfix.
11) Install SQL Performance Dashboard add-in.
12) Add SQL Startup account to 'Lock Pages in Memory' Local Security Policy.
13) Add NT AUTHORITY\SYSTEM as a 'sysadmin' to SQL Server.
14) Remove Builtin\Administrators from SQL Server.
Microsoft provides a great webcast on the topic "Microsoft SQL Server 2005 Failover Clustering on Windows Server 2008"
March 4, 2009 at 7:56 am
Great help..
I built a Win08/SQL205 cluster with SP3, but for some reason when I fail to Node1, the databases are not running (even though all the services are running just fine). Any suggestions?
March 4, 2009 at 12:39 pm
Here's my 2c on cluster install. Another good Microsoft resource is
Pre-requisite
1.Hardware and Software Components are certified under the Windows Server Logo Program for Windows 2008 – necessary for Microsoft support
2.Cluster Validation Wizard – Run all tests – necessary for Microsoft support
3.Installation media for SQL Server 2005 and SP2 - minimum requirement for Windows 2008
4.Shared Disks – data, log, tempdb
5.Windows Domain Service Accounts
6.Domain Groups – domain accounts should be in this domain group
7.Virtual IP Address
8.Unique Virtual name for SQL Server
9.Unique Instance name for named instances
10.Admin should have local administrator access on all nodes
11.Reporting Services – Enable ASP.Net and IIS – Configure Server Role (KB938245)
12.RS is not part of clustered service – will need to run independently
13.Stop all anti-virus, monitoring and terminal server services on all nodes
14.Ensure that remote registry, windows cryptographic service provider, task scheduler is running
15.Log-off all RDP connections to secondary nodes before startup – will cause setup failure
16.Move all installation files to the local drive maintaining the file layouts
-D:\SQL Server 2005\Servers – will cause setup failure
-D:\SQL Server 2005\Tools – will cause setup failure
17.The service accounts need to have read and add SPN
18.MS-DTC should be setup as a clustered resource before installation of SQL Server 2005
Pre-Installation
19.Create cluster group for SQL Server – new for Windows 2008
-Start ? Administrative Tools ? Failover Cluster Management
-Right Click Services and Applications, select More Actions, select Create Empty Service or Application
-Rename (“SQL Server Group”) the new container (when online) with the planned name for the SQL Instance
20.All storage resources are in a group called “Available Storage Group”
21.Add the storage group to “SQL Server Group”
-Click the new cluster group, click Add Storage from right pane
-Add the storage (all) to the “SQL Server Group” cluster group and it hit OK. It will show online
Start Installation (on the node on which the SQL Server Group is online under Failover Cluster Management)
22.Click Install
23.Click Run Program – This screen tells you that SQL Server 2005 is compatible with Windows 2008 when you install SQL Server service pack 2.
24.Accept Agreement
25.Install Pre-requisites
26.System Configuration Check – review warnings or errors
27.Components to install check Create a failover cluster
28.Enter Unique Virtual instance name
29.Enter Virtual IP Address
30.Select the SQL Server Group cluster group – this is where the installation will be done
31.Select the nodes that will be part of the cluster
32.Enter account that is a local admin on all the nodes – why do we need this?
33.Enter service account information
34.Enter domain group which these service accounts reside in
35.Select Collation and Authentication
36.Start installation – select node from drop down to view status
37.Message for Full Text Search failure - NTLM security support provider service provide doesn’t exist on Windows 2008 on which FTS relies –FTS will come online after SQL Server 2005 SP2 is installed
38.Compatibility issue for VS 2005 – Install SP1 for VS 2005
39.Reboot if required
Post Installation
40.Verify SQL Server resources and dependencies are created properly
-IP Address will not have any dependency
-SQL Network Name will have dependency on IP Address
-SQL Server Resource dependent on disk and SQL network name resources
-SQL Agent dependent on SQL Server
-SQL FTS dependent on Disk
-Right Click SQL Server resources and verify the dependencies
-
41.Verify SQL Server cluster group failure
-Right SQL Server Group and select Move Group and verify it comes online on Node 2
42.Add additional clustered disk to SQL Server as dependency
43.Install Client Tools and Integration services on the secondary node – this doesn’t install during the engine installation on the secondary node
44.Change SQL Server resource properties
-Right click SQL Server resource and go to policies tab
-Select If resource fails, attempt restart on current node
i.Period for restarts – 15:00 (default)
ii.Max. restarts in the specified period – 1 (default)
-Select If restart fails, failover all resources in this service or application
-Select If all the restart attempts fail, begin restarting again after the specified period
45.Install SP3 (C drive 400 MB free space on both nodes) on active node
46.Post installation the FTS will come online
47.Note the SQL Server build number on the active node. Failover the group to node 2 and verify the build number is the same and all resources are online
48.The SSIS service can be clustered or un-clustered. To cluster the service, refer to http://support.microsoft.com/kb/942176 Microsoft doesn’t recommend clustering the SSIS service. To leave the service un-clustered, follow the steps below
-Edit the xml config file for the SSIS service and add in nodes for each SQL instance so that SSIS can access each instances msdb database directly. You'll want to do this on both nodes, then restart SSIS on both nodes.
-Before
-After
Known Issues and Recommendations
49.If you leave out any features during initial installation, adding them to the cluster at a later date will have to be done via the command line. Install it and disable it rather than leave it to out
50.When removing the “Builtin\Administrators” account from SQL Server ensure that the cluster service account is added to SQL Server to perform the IsAlive check
51.Use same cluster service and SQL service accounts for all clusters in the domain
52.Cluster service and SQL service accounts should be local administrator
53.MSCS Cluster Service account or NT Authority\System must have sysadmin rights to SQL Server if Full Text Search is installed
54.Configure less critical services to not fail over the entire cluster on failure.
55.SQL Server 2005 is not Windows 2008 firewall aware. – Add inbound rules for UDP and TCP port
Check List
56.Cluster Names
-Node 1:
-Node 2:
-Windows cluster(virtual):
-SQL Server cluster(virtual):
57.Cluster IP Addresses
-Node 1:
i.Primary NIC (public network):
ii.Secondary NIC (private network):
iii.iSCSI NIC:
-Node 2:
i.Primary NIC (public network):
ii.Secondary NIC (private network):
iii.iSCSI NIC:
-Virtual OS cluster:
-Virtual SQL cluster:
-SQL Server domain service account:
-SQL Server domain group:
-Cluster Administrator account:
58.The individual performing the installation should have local administrator to all nodes
September 9, 2009 at 12:41 pm
I recently ran into an issue where SQL 2005 wouldn't install anything on a windows 2008 cluster and wanted to share the Microsoft solution. It all came down to a security setting that had recently been pushed out.
Windows 2008 have a new policy:
Network access: Do not allow storage of credentials or .NET Passports for network authentication = Enabled
This policy setting is seen in this registry location:
KEY: HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa
VALUE: disabledomaincreds
SETTING: 1
SQL Server 2005 cluster installation requires the ability to cache credentials to run the Remote Task Scheduler on the passive nodes of the cluster during the setup process. This policy will not allow this to happen. This is not a problem when doing a stand-alone SQL 2005 install , nor when doing a SQL 2008 cluster install as it doesn't use Remote Task Scheduler when installing on the passive nodes.
? A similar issue is mentioned in the below KB article .
912998 Error message when you try to automatically deploy agents in MOM 2005: "A specified logon session does not exist. It may have already been terminated"
http://support.microsoft.com/default.aspx?scid=kb;EN-US;912998
Steps from Article:
1. Click Start, click Run, type gpedit.msc, and then click OK.
2. Under Computer Configuration, expand Windows Settings, expand Security Settings, expand Local Policies, and then expand Security Options.
3. In the Policy pane, right-click Network access: Do not allow storage of credentials or .NET Passports for network authentication, click Properties, click to select Disabled, and then click OK.
4. Click Start, click Run, type regedit, and then click OK.
5. Locate and then right-click the following registry subkey:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa
6. Verify that the disabledomaincreds value is set to 0.
7. Restart both nodes of the cluster.
November 12, 2009 at 9:05 am
It is amazing to me what hoops all of you had to jump through.... I am NOT looking forward to my next install soon of ACTIVE/ACTIVE Win2008 cluster with SQL 2005... Thanks for the good notes to all that contributed.
February 5, 2010 at 8:50 am
February 6, 2010 at 9:15 pm
I fell into the same trap the other day with the "Network access: Do not allow storage of credentials or .NET Passports for network authentication = Enabled" registry setting, and also had confirmation on the upper-case vs. lower-case server node names. Bottom line, use UPPER-CASE. Don't expect any hotfixes for this.
December 16, 2010 at 3:36 pm
Nodes within the cluster have computer names that have all lowercase characters or that have mixed case characters. You cannot create a maintenance plan or perform a DatabaseMail operation. Check this http://support.microsoft.com/kb/978308
January 21, 2011 at 8:32 am
Hi SA-1,
Just want to say that your very detailed post has helped me out so much with my current install of Sql Sever 2005 on Win 2008.
Thank you for posting this!
Scriv.
April 26, 2011 at 3:35 am
Looping
Thanks
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply