通过

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.

Comments

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

  • Anonymous
    June 23, 2008
    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

  • Anonymous
    June 29, 2008
    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 usefulRegards,Balmukund

  • Anonymous
    August 26, 2008
    HiI am still child in this tech, i am using you r document to complete my cluster installationthank you so much if i need u r help i will ask u definetly , i think u will not say no to help in thisthank youSatish

  • Anonymous
    August 26, 2008
    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

  • Anonymous
    August 27, 2008
    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.

  • Anonymous
    August 27, 2008
    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

  • Anonymous
    November 23, 2008
    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

  • Anonymous
    November 25, 2008
    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-usOnce SQL Setup is complete, you can log back in.Did I answer your question?-Balmukund

  • Anonymous
    January 05, 2009
    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

  • Anonymous
    February 08, 2009
    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 2009SQLNODE1 : The current system does not meet the minimum hardware requirements forthis 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: \SQLNODE2My cluster admin just doing fine, both of node bring online.JFYI my VS node spec is:-RAM 512Shared 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

  • Anonymous
    February 09, 2009
    As per error it looks like you are running setup from SQLNode1. So few things to check..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.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.

  • Anonymous
    February 10, 2009
    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

  • Anonymous
    February 10, 2009
    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.ThanksBalmukund

  • Anonymous
    March 10, 2009
    Such a beautiful article, what a beautiful explanation, WOW. Nice job balmukund keep it up.

  • Anonymous
    May 04, 2009
    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

  • Anonymous
    May 04, 2009
    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

  • Anonymous
    May 04, 2009
    The comment has been removed

  • Anonymous
    May 04, 2009
    Hi Balmukund,Yes I used 2 VMware in same host and making cluster between them.Thanks

  • Anonymous
    May 04, 2009
    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 -uninstallmsdtc -installmsdtc -resetlogMake sure you configure network dtc access as described in KB http://support.microsoft.com/kb/899191Guest clustering is not supported by Microsoft as mentioned in http://support.microsoft.com/?id=956893SQL Standard edition and Enterprise, both would work on cluster. Standard supports only two nodes along with other limitations.

  • Anonymous
    May 06, 2009
    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

  • Anonymous
    May 06, 2009
    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

  • Anonymous
    May 06, 2009
    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

  • Anonymous
    May 06, 2009
    you need to do that in Active Directory (Domain Controller)What is the error you are facing? Please post the exact error message.

  • Anonymous
    May 06, 2009
    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

  • Anonymous
    May 07, 2009
    The comment has been removed

  • Anonymous
    May 07, 2009
    The comment has been removed

  • Anonymous
    May 07, 2009
    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/910851You receive error messages when you try to set up a clustered instance of SQL Server 2005

  • Anonymous
    June 17, 2009
    The comment has been removed

  • Anonymous
    June 17, 2009
    The comment has been removed

  • Anonymous
    June 17, 2009
    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

  • Anonymous
    June 29, 2009
    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....

  • Anonymous
    June 29, 2009
    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

  • Anonymous
    June 30, 2009
    Thanks BalmukundCan you help me with this : how can I link/access my physical hard drive from the virtual server/cluster?regardsGamaPe

  • Anonymous
    July 08, 2009
    The comment has been removed

  • Anonymous
    July 08, 2009
    The comment has been removed

  • Anonymous
    September 14, 2009
    Hi Balmukund,Its just been a day since I installed the virtual machines. But I have started getting messages that your evaluation period has expired...please activate.Any idea?

  • Anonymous
    September 15, 2009
    Hi,I am not sure if the clock starts after installation or download. I can't help here.Regards,Balmukund

  • Anonymous
    September 15, 2009
    Hi Balmukund,Is this node configuration active/active or active/passive?

  • Anonymous
    September 15, 2009
    Hi Misra,Its Active/Passive.Please refer to comments by Suhas on below Blog for clarification about Active/Active and Active/Passive terms.http://blogs.msdn.com/blakhani/archive/2008/06/09/sql-2005-cluster-setup-checklist.aspxRegards,Balmukund

  • Anonymous
    September 17, 2009
    The comment has been removed

  • Anonymous
    September 17, 2009
    To answer the second question, you would not be allowed to keep database files on C drive as its not shared. It has to be shared.For first question,Does it come online after 30 min?Which resource takes most of the time? Regads,Balmukund

  • Anonymous
    September 22, 2009
    Hi Balmukund,When I restarted the machines I could install w/o any problems.I have another question to you - can I make one make a single machine as both a domain controller and a node for clustering.

  • Anonymous
    September 22, 2009
    Hi Misra,Nice to hear that issue is resolved.It is not recommended to install SQL on DC.Moreover, you can't have one node in cluster to be a DC.Regards,Balmukund

  • Anonymous
    October 21, 2009
    Hi Balmukund,When I create the virtual machines (as specified in part 1) and start the machine I am not able to see the network in My Network of the VMs.Please let me know what should I do to make the network connections visible in VMs.Thanks

  • Anonymous
    October 21, 2009
    Hi Misra,I hope you meant that you want to bring these machine to same network as host machine.You have to do "Add Network Adapters" and select proper network.Hope this helps.Balmukund

  • Anonymous
    October 21, 2009
    Balmukund,Earlier I had created a backup of the VMs and when I used them I could not see any Network Connections in My Network of the VMs.I then created a new VM and at the time of creation I chose connected to "Internal Network".I then started up the machine, I couldnt see the network connectionI then did "Add network adapters", I cannot still see anything in Network connections when i logon to the machines.Please guide me what could be wrong, is it some service or some configuration that I have missed. Because earlier when I did the setup, all went fine.Thanks

  • Anonymous
    October 21, 2009
    To add to the above comment, when I clcik on My Network Connections I can only see "New Connection Wizard"

  • Anonymous
    October 21, 2009
    I cannot see Network Adapters in MyComputer->Manage->DeviceManagerEven though I have added vitual network adapter in the VM configuration.Also all of a sudden previously working setup also stopped working because there are no network adapters in th VM in logged state. But when I check the configuration in virtual server page I can see the network adapters configured.Appreciate your help in this regard.

  • Anonymous
    December 21, 2009
    Hi Balmukund,It was really a great article to start with.I am enjoying it.I was able to setup everything till part 4 including the config of MSDTC Group & associated resources.Before installing SQL Server I tested the fail over of the groups/resources seems to be working..But before i go to next step do you have any check list to test the failover functionality? Can you pl's help me.What should we need to expect when you turn off Node1 ? What happens to the cluster service which was mainly running in Node1?..Any body pl's let me know..Thanks in advance.

  • Anonymous
    December 21, 2009
    Hi kman,Thanks for the comment. Nice to know that its helping many people.Lets say group is owned by Node1 and then you shutdown Node1, cluster should failover all resources of that group to Node2 (if its configured correctly)Now, if you turn on Node1 back (remember, resource are one Node2) cluster would NOT attempt to bring it back to Node1 unless you have configured failback in group properties.Reason: Moving resource back would be another downtime and you may not want to do that in business hours. Failback can be set to "immedietly" or "between x to y" time.Hope this answers your question.Balmukund

  • Anonymous
    December 22, 2009
    Balmukund,Thank's for your response.As you mentioned in Part:4 configuring MSDTC As Resource on Node1 & Node2..I have a doubt? I am assuming that i need to do MSDTC Resource Setup on both nodes with different MSDTC IP Address's ? i.e Node1 - 192.1.1.5 and on Node2 192.1.1.6 am i correct ?  

  • Anonymous
    December 22, 2009
    Hi Kishore,Nope.MSDTC is a clustered resource and would have only one IP, network name, resource and disk.It should be able to failover between nodes to work properly.Hope this help.Balmukund

  • Anonymous
    March 07, 2010
    Hi Balmukund,Great documentation. Thanks.During the SQL installation everything went well, until I failed the SQL server to Node2 then failback to Node1. This time the SQL Server has fail status on Node1. I am getting a generic error 1069. I check the registry entry for the InstanceName and VirtualServerName both exist.By any chance you have any idea what is causing this problem.Thanks your help is much appreciated.Lilo

  • Anonymous
    March 07, 2010
    What do you see in event logs during failover to node1?

  • Anonymous
    March 08, 2010
    The system event log gave me an Event ID :1069.See link below, Error message #1.http://support.microsoft.com/kb/883732The registry information for InstanceName and VirtualServerName still exist. I have no idea where else to look.Your assitance is much apprciated.Thanks,Lilo

  • Anonymous
    July 19, 2010
    Hi,Nice post. Really great help during my installation. But when I install the SQL, I got a page where I have to put the virtual server name & then Virtual IP in SQL Server 2005 cluster. Could you please tell me from where I get this IP. From IT dept I got the IP & when i put that IP address ,I have got a message that the disk is full.I am a SAP Basis consultant & wanted to install my SAP in this cluster environment.Please Advise.Kind Regards,Rafikul

  • Anonymous
    July 19, 2010
    Hi,Good news that I am able to resolve the issue.When I analyze the issue I got that the group which I selected was not assigned to any resource group or node. After assigning the resource I took restart of both the node . After that its working fine.Kind Regards,Rafikul

  • Anonymous
    July 19, 2010
    Thanks for the comments Rafikul. NIce to know that issue is resolved.-Balmukund

  • Anonymous
    December 12, 2010
    Hi blakhani,I have one question.The cluster works fine when i pause node1 it take around a minute for node2 to take over and when i pause node2 and starts node1 node1 takes very long time takeoverthe main problem is that after restarting node1 the sql server service does not start and when trying to start the service manually an error arises : cannot recover the master database.any help is appreciated.

  • Anonymous
    March 12, 2011
    Well explained and helpfull. Thank you.

  • Anonymous
    April 13, 2011
    Great ?Great...I love your patience

  • Anonymous
    July 10, 2011
    Nice article. I learnt how to work on clusters...

  • Anonymous
    November 17, 2011
    it's a great notes for sql holders mr.Balmukund i hve doubt on fail over clustring how to install cluster in windows service pack 2 at 2005 sql server

  • Anonymous
    November 17, 2011
    Ramesh,Can you please explain the question?

  • Anonymous
    July 02, 2012
    Hi, Do you have any step by step documents which explains SQL server 2005 cluster install on Windows 2008 R2 ? or Can you share steps which I need to be aware of during this installThanks