How to create SQL Server 2005 Clustering with Virtual Server 2005 (Part 5)

How to create SQL Server 2005 Clustering with Virtual Server 2005 (Part 5)

Rate This
  • Comments 66

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.

Leave a Comment
  • Please add 7 and 5 and type the answer here:
  • Post
  • 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

  • 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 connection

    I 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

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

  • I cannot see Network Adapters in MyComputer->Manage->DeviceManager

    Even 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.

  • 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.

  • 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

  • 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 ?  

  • 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

  • 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

  • What do you see in event logs during failover to node1?

  • The system event log gave me an Event ID :1069.

    See link below, Error message #1.

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

    The registry information for InstanceName and VirtualServerName still exist. I have no idea where else to look.

    Your assitance is much apprciated.

    Thanks,

    Lilo

  • 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

  • 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

  • Thanks for the comments Rafikul. NIce to know that issue is resolved.

    -Balmukund

  • 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 takeover

    the 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.

Page 4 of 5 (66 items) 12345