Welcome to MSDN Blogs Sign in | Join | Help
How to create SQL Server 2005 Clustering with Virtual Server 2005 (Part 5)

This is the last part of the series. Once you have configured windows cluster and MSDTC, installing SQL is piece of cake. I already mentioned in part 1 that you have to download ISO (SQLEVAL.ISO) which we would need to attached as CD drive which would have setup files. I have copied the image on E:\Virtual Server\SQLEVAL.ISO

We also need to create domain account and domain groups in domain controller.  So, logon to domain controller. Go to Start > Run > DSA.msc and follow below screen-shots.

AD Users and Computers

Give the details of account (i.e. SQLSVC)

 SQLSVC Account

Set the password after clicking next button. Make sure that "Password never expires" is checked and "User cannot change password" is checked.

SQLSVC Account Password

One account is created, lets add a Group and add SQLSVC into the group.

SQLSVC New Group

Give Group Name "SQL Admin Groups"

SQLSVC Group Name

Right click on  "SQL Admin Groups" properties and add "members"

SQLSVC New Group Members

Now you are done with Domain controller.

Now, let's mount ISO image to MyNode1 and MyNode2. To do this, go to Virtual server console.

CD_DVD

Click on CD/DVD and fill details as below

CD_DVD_Properties

Above has to be done on both the nodes. Once you do that, you can see SQL Installation CD mounted on MyNode1 and MyNode2.

Lets Start SQL setup now.

  1. Log off from MyNode2. This is required to avoid running into known bug.
  2. Log on to MyNode1.
  3. Move all the resource groups to MyNode1 using Cluster AdminMyNode1 Active Groups 
  4. Go to DVD drive and double click on setup.exe from Servers folder
  5. Accept EULA. Next.
  6. Click 'Install' and wait for SQL Server to configure components and setup the installation. Once the required components (.NET Framework 2.0, MS SQL Native Client and the SQL Server 2005 setup support files) have been installed click 'Next'
  7. Click 'next' when prompted with the screen 'Welcome to the Microsoft SQL Server Installation Wizard. The installation will then proceed through the 'System Configuration Check'- click 'next' to proceed.
  8. Enter Registration Information, click 'next'
  9. Components to Install- Select all components (SQL Server Database Services, Workstation components) , then click 'next' components to install 
  10. Since this is the first installation of a new database instance for SQL server, select 'Default Instance'.
  11. Provide Virtual Server Name (VirtualSQL) VirtualSQL
  12. Provide Virtual SQL Server IP Address (192.1.1.6) in Next screen and click on Add. Then Next   
  13. VirtualSQLIP
  14. Provide SQL Group in Next screen as shown below Cluster Group Selection
  15. Click Next on Below Screen
  16. Select Nodes
  17. Provide Administrator Password in next screen and click Next
  18. Provide SQL Service Account created earlier on domain controller(sqlsvc) SQLSVC Provide
  19. Provide Domain Group which we created earlier (SQL Admin Groups) SQLSVC Group Provide
  20. Authentication Mode. Choose Mixed Mode (Windows Authentication and SQL Server Authentication), provide sa password and click 'next'
  21. Keep the default settings (SQL Collations- Dictionary Order, case-insensitive, 1252 character set) and click next
  22. Error and Usage Report Settings. Leave defaults (none checked). Click 'next'
  23. Check installation Settings, click 'install'.
  24. During the install process you can always see what is happening on other node.
  25. Once the installation process is completed, click 'next'
  26. And finally... Finish.

Now we are done with the setup and we should see the resources in cluster admin under SQL Group.

Any feedback is greatly appreciated.

Posted: Friday, June 13, 2008 9:07 AM by blakhani

Comments

Nigel said:

Great document! I was getting a problem with MSDTC failing at failover, your MSDTC guidance got that fixed.  Thanks

# June 18, 2008 1:04 PM

rhinze said:

Great series! Thank you for the work you put into all five articles!

One question: you mention the previous creation of the user, sqlsvc, and the group,SQL Admin Groups. But I cannot find that reference anyway else in the series. Are there any specifics to their creation? This is exactly where sql setup is failing on me, as it cannot 'see' and verify either the user or the group.

Any help would be appreciated.

Regards,

Rick Hinze

# June 24, 2008 5:08 AM

blakhani said:

Hi Rick,

Thanks for the feedback. I have added details to created domain account and group in the beginning of 5th part of series. Hope you will find it useful

Regards,

Balmukund

# June 30, 2008 3:21 AM

Satish Kumar said:

Hi

I am still child in this tech, i am using you r document to complete my cluster installation

thank you so much if i need u r help i will ask u definetly , i think u will not say no to help in this

thank you

Satish

# August 26, 2008 2:26 PM

blakhani said:

Hi Satish,

Thanks for the comment. Feel free to ask question on this blog and I will reply to them as soon as possible.

Thanks,

Balmukund

# August 26, 2008 2:36 PM

Jide said:

ALl I can say is youve given me something to do over the weekend and Im thankful for it!

Thanks for sharing your knowledge mate.

# August 27, 2008 10:42 AM

blakhani said:

Hi Jide,

I am happy to hear that you would be using my blog to learn something new and help your business grow.

In case, you feel you need help please feel free to comment and I will reply them ASAP (unless I am not sleeping)

Thanks,

Balmukund

# August 27, 2008 12:10 PM

WilliamB said:

part 5 step 1 mentions log off node2. When should this node be re-started and re-logged on?

Excellent article and well worth the study.

Thanks

# November 23, 2008 5:49 PM

blakhani said:

Hi William,

Thanks for the feedback.

This step is required as work around of issue mentioned in KB.

http://support.microsoft.com/kb/910851/en-us

Once SQL Setup is complete, you can log back in.

Did I answer your question?

-Balmukund

# November 26, 2008 2:14 AM

Robin said:

Hi Balmukund,

Thank you very much for guide.

Really an EYE Opener for me regarding the virtual HD stuff as it eliminates the third party iSCSI Target software.

Thanks again!

br,

Robin

# January 5, 2009 3:43 PM

Rama said:

Hi Balmukund,

Nice article, I've tried it on my virtual server. The system Configuration Check is success , but in the last part I always got an errors when tried to install SQL 2005,

Microsoft SQL Server 2005 9.00.1399.06

==============================

OS Version      : Microsoft  Windows Server 2003 family, Enterprise Edition Service PACk 2 (Build 3790)

Time            : Mon Feb 2009

SQLNODE1 : The current system does not meet the minimum hardware requirements for

this SQL Server release. For detailed hardware and software requirements, see the readme file or SQL Server Books Online.

Task did not appear to start on machine: \\SQLNODE2

My cluster admin just doing fine, both of node bring online.

JFYI my VS node spec is:

-RAM 512

- Shared disk : 16 GB for system (C), 3 GB for Data (X), 512 MB for Quorum(Q) and 512 MB for MSDTC (M)

Do you have any suggestion?

Thanks,

Rama

# February 9, 2009 6:26 AM

blakhani said:

As per error it looks like you are running setup from SQLNode1. So few things to check..

1. If you are running setup from CD disk (ISO Image), make sure that you able to access \\SQLNODE1 from SQLNODE2 and the setup files share. It would be better if you can copy the setup files on C drive and then run the setup.

2. Make sure all sessions are logged off from SQLNODE2. There is a bug in SQL Setup which will cause setup to fail if you have any session open on remote node. http://support.microsoft.com/kb/910851/en-us

Let me know if it does not help.

# February 9, 2009 9:03 AM

Rama said:

Hi,

Thanks, that's a good idea, I've tried to copy source CD to C:  and running setup smoothly till finish :)

A few to ask, after setup finish if i opened the SQLNODE2 i can't find the shared disk (Q , M or I), from device management i found unknown/unreadable for 3 shared disk except C: . Is that a normal condition?

And how to test SQLNODE1 fail over to SQLNODE2?

Many thanks for the answer.

Rama

# February 11, 2009 5:34 AM

blakhani said:

That's the behaviour of cluster (share nothing model). Disk would be visible to the node which owns the disk.

To test failover, you can open cluster Administrator (start>run>cluadmin) and do "Right click, Move Group" on SQL Group to check failover.

Thanks

Balmukund

# February 11, 2009 6:00 AM

Manish said:

Such a beautiful article, what a beautiful explanation, WOW. Nice job balmukund keep it up.

# March 11, 2009 6:07 AM

hninwailwin said:

Hi,

We definitely need to install SQL 2005 enterprise edition for 2 node clusters? I tried many times SQL cluster with another VMware but not successful.So need to use microsoft virtual server?

Thanks

# May 5, 2009 5:18 AM

blakhani said:

Hi hninwailwin,

I have used Virtual Server because I know how to configure it. I am not an expert in VMWare but I have seen many customer using VMWare.

As per http://support.microsoft.com/?id=956893 few Virtualization products are supported by Microsoft.

Please remember that if you are using two guest machine on same host machine and making cluster between them then it is currently not supported as of today (also explained in KB above)

Could you please let me know what kind of error you are facing?

Regards,

Balmukund

# May 5, 2009 5:27 AM

hninwailwin said:

Hi Balmukund,

Thank for your reply.I faced the problem is that after installing the window cluster, MSDTC service is terminated automatically.So I install msdtc.exe from command line.But one time only msdtc group online and when I move MSDTC group to node2,it is fail. And when I move SQL group to node2, it is also SQL server and agent resource can't bring online. I tried many times.Now I very disappoint and my company also blame me. So I also want to know need to use SQL 2005 enterprise edition or standard edition? I use VMware. It is VMwares fault? But I can create file share group and it can do fail over very well.

Thanks

# May 5, 2009 5:51 AM

hninwailwin said:

Hi Balmukund,

Yes I used 2 VMware in same host and making cluster between them.

Thanks

# May 5, 2009 5:55 AM

blakhani said:

As I said many customers using VMWare and not sure if its fault. Is there a possibility is that the media you have for OS is corrupt?

> What is the error in event log when you try to bring resources online.

> Try below...

msdtc -uninstall

msdtc -install

msdtc -resetlog

Make sure you configure network dtc access as described in KB http://support.microsoft.com/kb/899191

Guest clustering is not supported by Microsoft as mentioned in http://support.microsoft.com/?id=956893

SQL Standard edition and Enterprise, both would work on cluster. Standard supports only two nodes along with other limitations.

# May 5, 2009 6:09 AM

hninwailwin said:

Hi Balmukund,

When I create the MSDTC group, I got the error is that can't create the MSDTC group because it is already exists. I found this MSDTC group in Cluster group so I deleted the MSDTC resource from cluster group. After that I can create the MSDTC resource in MSDTC group . Is it normal?

Thanks

# May 6, 2009 9:12 PM

blakhani said:

That's perfectly normal on windows 2003. YOu can have only one MSDTC resource per cluster.

This has changed in Windows 2008 where you can have multiple clustered MSDTC.

http://technet.microsoft.com/en-us/library/cc754933(WS.10).aspx

# May 7, 2009 12:14 AM

hninwailwin said:

Hi Balmukund,

I got this error in installation setup. SQL server browser can't startup service.

Please what is this? I need to create 3 users and need to choose this all users for 3 services?

Thanks

# May 7, 2009 1:18 AM

blakhani said:

you need to do that in Active Directory (Domain Controller)

What is the error you are facing? Please post the exact error message.

# May 7, 2009 1:32 AM

hninwailwin said:

Hi Balmukund,

Thanks alot. I can failover now. Really thanks.

Something is if without installing IIS, can we install SQL server? And then I installed IIS for installing SQL server but after that I unstall IIS,will SQL server working fine and will clustering failover working fine?

Thanks

# May 7, 2009 4:25 AM

blakhani said:

IIS and SQL ENgine are not linked to each other.

SQL Server Reproting Services is the dependent component. So, if you are not using Reporting Services then you can play with IIS, it won't harm.

-B

# May 7, 2009 12:00 PM

KiKi said:

Hi,

When I istall SQL cluster, after installing database services,it is go on to the installation of workstation components. At that time I got the error. "Remote Setup fail". Please help me. I checked on the node2, task scheduler service also running.

Thanks

# May 8, 2009 3:26 AM

blakhani said:

may be you are hitting into known issue.

Please make sure that you log off (not shutdown) from node 2 while running setup from node 1.

http://support.microsoft.com/kb/910851

You receive error messages when you try to set up a clustered instance of SQL Server 2005

# May 8, 2009 6:20 AM

Anurag said:

Hi,

The Remote Setup is failing during installation of workstation components. The task scheduler services are running on both the nodes and I am logged off from node1. I have checked the setup log files and following contents are present on node 2.

Clustered feature detected: SQL_Engine

Clustered feature detected: SQL_FullText

Loaded DLL:C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\sqlsval.dll Version:2005.90.1399.0

Source File Name: datastore\cachedpropertycollection.cpp

Compiler Timestamp: Fri Jul 29 01:13:49 2005

Function Name: CachedPropertyCollection::findProperty

Source Line Number: 130

----------------------------------------------------------

Failed to find property "IPResources" {"VirtualServerInfo", "", "VirtualSQL"} in cache

Source File Name: datastore\cachedpropertycollection.cpp

Compiler Timestamp: Fri Jul 29 01:13:49 2005

Function Name: VirtualServerInfo.IPResources

Source Line Number: 113

# June 18, 2009 3:13 AM

blakhani said:

Hi Anurag,

Are you sure that Workstaion components are failing on remote node?

SQL Setup on MyNode1 will NOT install workstation on MyNode2. Looks like failure if for database services.

Please send me complete log folder (after zipping it) to blakhani(at)koolmail(dot)in

Thanks.

# June 18, 2009 4:08 AM

Anurag said:

Hi,

I am running SQL Setup on Node2 only. Another important thing is that after adding node2 to the cluster and restart of the virtual cluster nodes, the Cluster service did not get restarted on the nodes and I had to manually restart the cluster service on both nodes.

Also, I do not know how to copy the log files from the virtual nodes to the host for email purpose.

Thanks

# June 18, 2009 6:32 AM

GAMAPE said:

Excellent  !!!. I set up my own SQl cluster.. now I have a test Cluster environment  cool..

Just a Question, I have some software on my local physical drive that I would to install on these virtual cluster. Any Idea how to do it?

again Thanks for what this article , it's great....

# June 29, 2009 10:54 AM

blakhani said:

Hi GAMAPE,

Thanks for the comment.

Is the application/software is cluster aware? If that makes a service then you can add the service in cluster.

Right Click on the Group and add new resource of type "Service" and provide details.

Regards,

Balmukund

# June 30, 2009 12:32 AM

GAMAPE said:

Thanks Balmukund

Can you help me with this : how can I link/access my physical hard drive from the virtual server/cluster?

regards

GamaPe

# June 30, 2009 1:28 PM

Brian said:

Hey great post, Im installing as per your guide but finding the option to create a sql server failover cluster is grey and I can not select the check box.

Im using 2003 Ent with SP2 and all patches which I though was ok.

All previous steps outlined in your guide have been done and both nodes are online.

Any ideas why this is not working ?

Thanks

# July 8, 2009 10:21 PM

blakhani said:

Hi Brian,

Few reasons which I can think of may cause "create a SQL failover cluster" greyed out would be

1. Second option would be enabled only if you are checking first option "SQL Server Database Services"

2. Setup is thinking that you are installing on Standalone box. It could be because of cluster service not running. Please check that you are able to see both nodes up in cluster administrator.

3. Any warning in "System Configuration Check" screen?

4. Installing editions other than Enterprise, Enterprise EVAL, Developer and Standard?

If none of above help, please send me "c:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG" folder after zipping it to my email id below.

blakhani(at)koolmail(dot)in

# July 9, 2009 1:01 AM
Leave a Comment

(required) 

(required) 

(optional)

(required) 

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Page view tracker