Balmukund

My first experience with hyper-V (Few BIOS setting)

I decided to write a new blog series to Install SQL Server 2008 on Windows 2008 cluster in Virtual environment. Since I got brand new Windows 2008 64 Bit machine, i decided to use Hyper-V to play around. I could install Hyper-V without any issue but I saw below series of error in Event log

Erro1: Hyper-V launch failed; Either VMX not present or not enabled in BIOS.
Error2: Hyper-V launch failed; at least one of the processors in the system does not appear Error3: to provide a virtualization platform supported by Hyper-V.
Hyper-V launch failed; No-execute (NX) or DEP not enabled on processor 0x0 (check BIOS settings).

All of them were pointing to something wrong with BIOS setting. My search to internet ended in many links but finally I could make it work by setting below in BIOS settings on my DELL machine. (below specification)

System Model    OptiPlex 755

Processor    Intel(R) Core(TM)2 Duo CPU     E6550  @ 2.33GHz, 2327 Mhz, 2 Core(s), 2 Logical Processor(s)

1. Security > Execute Disable (set to On)
2. Performance > Virtualization (set to On)
3. Performance > VT for Direct I/O Access (set to On)
4. Performance > Trusted Execution (set to Off)

After making above changes, I rebooted the machine and then I was able to use Hyper-V and create new virtual machine.

Stay tuned, I am starting my work to new blog series to install SQL 2008 Cluster on Windows 2008 Cluster.

SQL Server 2008 Evaluation Edition Expired? Believe me, I already upgraded it. What’s up?

Once you install evaluation edition of SQL Server 2008 and then perform in place upgrade to full version, you may get below error message when you open SQL Server Management Studio.

For better search, below is the text of the message

Microsoft SQL Server Management Studio:
Evaluation period has expired. For information on how to upgrade your evaluation software please go to http://www.microsoft.com/sql/howtobuy

Have a close look at error… This is for Management Studio. This means that this is the problem with management studio only. If its not yet expired, inside of Management Studio, going to Help > About... still shows this the expiration countdown.

This is identified as a known issue where SSMS is not upgraded when you do edition upgrade for SQL Server 2008. To workaround the problem you can hack registry keys. Set CommonFiles registry value to 3 in HKLM\SOFTWARE\\Microsoft\Microsoft SQL Server\100\ConfigurationState Registry Hive. You will then need to reinstall (if the tools are not installed currently) or run edition Upgrade. Reference: connect bug

This is already fixed in SP1.

Update: 12 May 2009.

KB article published for same issue. http://support.microsoft.com/kb/971268

Script to Change Service Account

Sometime back I was asked by one of my customer who wanted to change the startup account for SQLServer Service and SQLBrowser Service without manually going to SQL Server Configuration Manager and changing password there.

By the way, did you know...

1. You should NOT change the password from Services.msc for SQL Server. Only supported way to change it is from Enterprise Manager (in SQL 2000) and Configuration Manager (in SQL 2005 onwards)
2. If only service account password is changed using configuration manager, it does not need a restart of SQL Server Service.

Below is the script which could be used. Save the below script to .vbs file and make changes to reflect your instance name/Service name and account.

set sqlsvr = GetObject("WINMGMTS:\\.\root\Microsoft\SqlServer\ComputerManagement:SqlService.ServiceName='MSSQLSERVER',SQLServiceType=1")sqlsvr.SetServiceAccount "LocalSystem"

set browsersvr = GetObject("WINMGMTS:\\.\root\Microsoft\SqlServer\ComputerManagement:SqlService.ServiceName='SQLBrowser',SQLServiceType=1")

browsersvr.SetServiceAccount "NetworkService"

You can make changes to modify SQL Agent account as well. Hope this helps.

Resources - Migration from SQL 2000/SQL2005 to SQL 2008

I will keep on adding the resources which I would find useful to help customer upgrading from SQL Server 2000/SQL Server 2005 to SQL Server 2008

White paper written by experts SQL Server 2008 Upgrade Technical Reference Guide

SQLCAT Team Blog http://blogs.msdn.com/sqlcat/archive/2008/12/08/ultimate-guide-to-upgrading-to-sql-server-2008.aspx

MSDN Link Resources for Upgrading to SQL Server 2008

SQL Server UE team blog http://blogs.msdn.com/sqlserverue/

Upgrade Advisor http://www.microsoft.com/downloads/details.aspx?familyid=F5A6C5E9-4CD9-4E42-A21C-7291E7F0F852&displaylang=en

How do I define a policy in SQL Server 2008 to evaluate if a Trace Flag is enabled ?

Someone posted this question in one of the internal forums and I found this interesting so thought of posting the solution. We are not allowed to add new facets in Policy Based Management and I could not find any facet which checks for trace flags.

While researching I found Blogs from Dan Jones (PBM Program Manager) and looked at below post http://blogs.msdn.com/sqlpbm/archive/2008/07/03/executesql.aspx

So to solve the given problem, I create a stored procedure in master database.

USE [master]

GO

create proc [dbo].[TF_Policy]

as

begin

set nocount on;

CREATE TABLE [dbo].[#tbl_TraceFlagSet] ([TraceFlag] INT NULL ,      [TraceFlagStatus] BIT NULL,      [Global] INT NULL,       [session] INT NULL ) ON [PRIMARY];

insert into #tbl_TraceFlagSet (TraceFlag, TraceFlagStatus, [global], [session]) 

exec ('dbcc tracestatus(-1) with NO_INFOMSGS');

Select count(*) from #tbl_TraceFlagSet;

end

GO

So this procedure would give 1 if any trace flag is enabled as zero. Now, I can use this in ExecuteSQL to define condition. I named it as 'CheckTraceFlag"

finally, policy would check this condition "CheckTraceFlag"

I have tested the policy and I can see failure once I enable trace flag.

What was the date on Last Monday?

One of my friend asked me logic/Query to find date of last Monday.

Everyday of the week can be identified as a number i.e. Sunday is 1, Monday is 2 and so on...

Since he wanted to find out date on Monday, it is 2nd day of week. Below is the logic

Monday Of The Week = Given date - (Day number of given week - 2)

We are using 2 because we want to find Monday. So the SQL query would be..

declare     @date datetime

set         @date = getdate()-10 -- I have take any date

select      @date -  (DatePart(dw, @date)-2) -- This is the logic which I explained earlier

Have Fun!!!

Note: This depends on what the datefirst variable is set to. This would work only if DateFirst is 7 which is the U.S.English default.

Workstation components installed but only the Configuration Tab shows. What's up?

Q. Where is Management studio? I installed all components in SQL Setup.

Q. Workstation components installed but only the Configuration Tab shows.  What's up?

I have come across this situation many times where someone is trying to install SQL Server 2005 and even after successful installation SQL Server Management Studio is missing even if workstation components was selected in setup screen.

Reason: If you have installed Visual studio, it installs SQLEXPRESS (free edition of SQL Server 2005).

• Go to Control Panel > add/remove program.
• Choose Microsoft SQL Server 2005 and click Uninstall (don’t worry we are not removing SQL Server)
• Uncheck “Remove SQL Server 2005 instance components”
• Check ONLY “Workstation Components” under “Remove SQL Server 2005 common components”.

Refer below screen

• Click Next
• Click Finish.

Now you are good to install Workstation components again.

Refer KB http://support.microsoft.com/kb/922670 (How to use the Add or Remove Programs item in Control Panel to add or remove components for stand-alone installations and clustered installations of SQL Server 2005)

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.

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

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

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

Give Group Name "SQL Admin Groups"

Right click on  "SQL Admin Groups" properties and add "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.

Click on CD/DVD and fill details as below

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 Admin 
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'  
10. Since this is the first installation of a new database instance for SQL server, select 'Default Instance'.
11. Provide Virtual Server Name (VirtualSQL)
12. Provide Virtual SQL Server IP Address (192.1.1.6) in Next screen and click on Add. Then Next   
13. 
14. Provide SQL Group in Next screen as shown below
15. Click Next on Below Screen
16. 
17. Provide Administrator Password in next screen and click Next
18. Provide SQL Service Account created earlier on domain controller(sqlsvc)
19. Provide Domain Group which we created earlier (SQL Admin Groups)
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.

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

In previous parts(Part 1, Part 2 and Part 3), we have completed windows clustering. Now, before installing SQL Cluster we have to configure MSDTC. We also need to make some shared drive which we would use to keep SQL Server databases and MSDTC. We are going to use Disk M for MSDTC and Disk X for SQL Server. We have already done the steps for Disk Q in part 2 (Create Quorum Disk section). I will reproduce the steps for Disk M and X here.

Configure Shared Disks

1. Turn off MyNode1 and MyNode2

2.  Choose Virtual Disks > Create > Fixed Size Virtual Hard Disks

3. Provide Details for M Drive (250 MB) and hit "Create" button

4. Same way we would create X drive (2 GB) and Hit "Create" button.

5. Below is what you would see in Windows Explorer

6. Lets attach the disks to MyNode1 and MyNode2 (They are off at this moment) I am showing for MyNode1. Please follow same for MyNode2 also.

Click on Hard Disks

Hit OK and it would attach the disks to MyNode1. Follow the same for MyNode2

7. Turn on ONLY MyNode1.

8. Login to MyNode1 and Start > Run > DiskMgmt.msc. Cancel on the screen if you get any.

9. You should see two new Disks which are Not Initialized. Right Click and choose Initialize Disk.  Then you create partition by Right Click New Partition. Below is final screen shot which you should see.

10. Now Turn Off MyNode1 and Turn On MyNode2

11. Go to DiskMgmt.msc on MyNode2 and assign drive letters (M for 250 MB drive and X for 2 GB Drive)

12. Open Cluster Administrator (Start > Run > CluAdmin)

13. Create New Group called MSDTC (File > New > Group) and provide details

14. Choose Both Nodes as preferred owner. You might get an error at last screen but that's OK because MyNode1 is offline.

15. Create a new "Disk" resource under MSDTC Group (File > New > Resource) Choose details as below screen shot.

17. Choose M Drive in "Disk Parameters" screen. and finish.

18.  Now you should see a Disk resource under MSDTC Group. Right click and choose "Bring Online"

19. Follow 13 to 18 again to Create new groups called "SQL Group" and add "Disk X"

20. Up to here we have completed shared disk configuration for MyNode1 and MyNode2. Let's start MyNode1 and test fail over for new groups.

21. Before we install SQL Server, we have to configure MSDTC

Configure MSDTC as Resource

1. On MyNode1 and MyNode2 follow below steps
   • Open Control Panel on the cluster node
   • Double-click Add/Remove Programs
   • On the left side of the screen, double click Add/Remove Windows Components
   • Under Components, highlight Application Server and click Details
   • Select Enable network DTC access, and then click OK
   • Click Next
   • Click Finish
2. Add "MSDTC IP Address resource" in MSDTC Group
   • Right click MSDTC Group and select New > Resource
   • Type a some name such as MSDTC IP Address
   • In Resource Type, choose IP Address
   • Click Next
   • Click Next on Possible Owners screen
   • Click Next on Dependencies Screen
   • Give IP Address as 192.1.1.5
   • Click Finish
   • Right click the MSDTC IP Address resource and select Bring Online.
3. Add "MSDTC Network Name" resource in MSDTC Group
   • Right click MSDTC Group and select New > Resource
   • Type a some name such as MSDTC NN
   • In Resource Type, choose Network Name
   • Click Next
   • Click Next on Possible Owners screen
   • On Dependencies Screen, move MSDTC IP Address and click on Add to bring on Right window. Now hit Next.
   • Give Name as MSDTC
   • Click Finish
   • Right click the MSDTC NN resource and select Bring Online.
4. Add "MSDTC" resource in MSDTC Group
   • Right click MSDTC Group and select New > Resource
   • Type a some name such as MSDTC
   • In Resource Type, choose Distributed Transaction Coordinator
   • Click Next
   • Click Next on Possible Owners screen
   • On Dependencies Screen, choose "MSDTC NN" and "M Drive" and click on Add to bring on Right window.

• Click Finish
• Right click the MSDTC resource and select Bring Online.

Above is the screen shot of "MSDTC group". Make sure you have matching "Resource Type" as shown in the screen.

Test fail over for MSDTC group. This finishes all Prerequisites to install SQL Server on Cluster. Get ready to install SQL Server in last part of the series.

You can follow http://support.microsoft.com/kb/899191 to make changes to MSDTC Services.

Lets move on to last part of the series

SQL 2005 Cluster Setup Checklist

I know there are many resources on various web sites to install SQL Server 2005 on Cluster. Below are the three documents which I always share with my customers.

Guide to Creating and Configuring a Server Cluster under Windows Server 2003 http://www.microsoft.com/downloads/details.aspx?FamilyID=a5bbb021-0760-48f3-a53b-0351fc3337a1&DisplayLang=en

SQL Clustering White paper: .  http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en

Another great blog by Arvind on similar topic http://blogs.msdn.com/arvindsh/archive/2009/01/03/sql-server-2000-2005-failover-cluster-pre-installation-checklist.aspx

Below is the template you should fill before stating cluster installation. These will be required through out the process of installing windows cluster and then SQL cluster. You can take printout and make hard copy for your ready reference.

Any comments are most welcome.

Who dropped objects from database?

Sometime I have been asked that how to find out who dropped/altered/created table/stored procedure. Well, SQL 2005 has out-of-the-box solution to this problem.

Schema Change History is the report which would give details. This report takes data from default trace which is enabled by default in SQL Server 2005 default installation. Below command will help you in finding current status.

USE master;
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'default trace';
GO

If its enabled you would see run_value as 1.

More about default trace can be read here

Since default trace will be maximum 100 MB in size you may not get the data which is pretty old. Every restart also creates new file. So, if your SQL Server has restarted five times after someone dropped the object, you will not be able to see it here.

SQL Server 2005 has many other "Standard Reports" which can be found in SQL Server Management Studio.

Enjoy!!!

<Tags> Who dropped my database, when database was dropped, someone deleted database objects, tables, procedures, list of dropped objects in SQL Server 2005, Schema Change history Report</Tags>

Script : Map All Orphan Users to Logins else create login and map

Once of my customer asked for an "automated" script which would find all the users, which are not mapped to login and map them. Below is the logic which can be used.

<<<<<<<<<< PLEASE READ DISCLAIMER >>>>>>

If (login not exists)
create login and map login with user
else
map login with user

<Replace MYDB and Password as appropriate>

Idea taken from http://support.microsoft.com/kb/274188

Below is the script for above Logic

USE MYDB
GO
SET NOCOUNT ON
-- Declare Variables
DECLARE  @user_name  NVARCHAR(128),
         @login_name NVARCHAR(128),
         @err_msg    VARCHAR(80),
         @str        VARCHAR(250)

-- Find all users in the database MyDB which are orphan.
DECLARE FIX_LOGIN_USER INSENSITIVE CURSOR  FOR
SELECT   NAME
FROM     SYSUSERS
WHERE    ISSQLUSER = 1
         AND (SID IS NOT NULL
              AND SID <> 0x0)
         AND SUSER_SNAME(SID) IS NULL
ORDER BY NAME
OPEN FIX_LOGIN_USER

FETCH NEXT FROM FIX_LOGIN_USER
INTO @user_name
WHILE @@FETCH_STATUS = 0
  BEGIN
    SELECT @login_name = NULL
    SELECT @login_name = LOGINNAME
    FROM   MASTER.DBO.SYSLOGINS
    WHERE  LOGINNAME = @user_name
    IF (@login_name IS NULL)
      BEGIN
        SELECT @err_msg = 'matching login does not exists for ' + @user_name     
        PRINT @err_msg
        PRINT 'creating login for ' + @user_name
        SELECT @str = NULL
        SELECT @str = 'exec master.dbo.sp_addlogin ' + +'''' + @user_name + '''' + ' ,' + '''password@123''' + ' , ' + '''MyDB'''
        SELECT @str
        EXEC( @str)
        PRINT 'created and now fixing ......'
        EXEC SP_CHANGE_USERS_LOGIN
          'update_one' ,
          @user_name ,
          @user_name
        IF @@ERROR <> 0
            OR @@ROWCOUNT <> 1
          BEGIN
            SELECT @err_msg = 'error creating login for ' + @user_name
            PRINT @err_msg
          END
      END
    ELSE
      BEGIN
        PRINT ' Only fixing ......'
        EXEC SP_CHANGE_USERS_LOGIN
          'update_one' ,
          @user_name ,
          @login_name
        IF @@ERROR <> 0
            OR @@ROWCOUNT <> 1
          BEGIN
            SELECT @err_msg = 'error updating login for ' + @user_name
            PRINT @err_msg
          END
      END
    FETCH NEXT FROM FIX_LOGIN_USER
    INTO @user_name
  END
CLOSE FIX_LOGIN_USER

DEALLOCATE FIX_LOGIN_USER

GO

SET NOCOUNT OFF

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

DISCLAIMER: SCRIPT IS PROVIDED "AS IS" WITHOUT WARRANTY REPRESENTATION OR CONDITION OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO CONDITIONS OR OTHER TERMS OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. THE USER ASSUMES THE ENTIRE RISK AS TO THE ACCURACY AND THE USE OF THIS SCRIPT.

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

In Part 1 and Part 2, we configured till setting up Quorum drive on MyNode1.

First we need to change the IP address of heartbeat network which we added to MyNode1 and MyNode2.

Below is the screen shot of heartbeat IP on MyNode1. We need to give IP as 10.10.10.1 (once you hit tab, subnet would come automatically, 255.0.0.0)

This would be displayed as "Local Area Network 2", you can rename it to private or heartbeat for easy identification.

Follow the same thing on MyNode2. Provide heartbeat IP address as 10.10.10.2

Once IP Setup is done, "Turn Off" MyNode2 and Login to MyNode1. First we are going to install One node cluster and later add MyNode2 in this cluster (we will call this as SQLCluster)

1, Go to Start>Run>Cluadmin

2. Select "Create New Cluster" from the options.

3. Hit OK

4. on "Cluster Name and Domain" screen, provide the domain name as MyCluster.com and Cluster Name as "SQLCluster" and Hit "Next"

5. In Next screen, Provide Name as MyNode1 and Also click on Advanced button to select "Advanced (minimum) configuration"

6. Next screen will analyze the settings, this may take a while to finish, You may get one warning since we selected "minimum configuration", just ignore that and hit next.

7.  In Next screen, you will be prompted for IP Address. Give it as 192.1.1.4

8. Next screen is for Cluster Service Account. For easiness of this lab, I am using Administrator account. In real world, you should create a separate account in domain.

9. Hitting Next will show proposed cluster configuration.

Make sure that you hit on Quorum and select Q: as shown in above screen shot.

10. Once above step is complete, we have successfully installed, one node cluster. Screen Below...

11. Now, remember that MyNode2 was Turned off by this time.At this point, Turn On MyNode2 and let it boot up completely. Don't Login to the node.

12. Come back to MyNode1 and Select File > New > Node

13. In below screen, provide MyNode2

14. Keep Moving forward and you will get below screen.

15. Keep going....

16. Finally.... you are done with Windows clustering

Click for Part 4

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

In Part 1 we have made our Virtual network ready which is having a domain controller and two member servers (MyNode1 and MyNode2)

Lets start making cluster...

Create Quorum Disk

For cluster we need a Quorum disk. Below are the steps to make a Quorum virtual disk. In the menu of Virtual Server, click on "Create" under "Virtual Disks" and select "Fixed Size Virtual Hard Disk".

After clicking there, you should get below screen. Fill in the details as shown. E:\Virtual Server\Q-Drive.vhd is the path which I have entered. Lets give size as 250 MB.

And click on Create. you should see vhd file under path which we provided in above screen.

Create heartbeat Network

Click on "Create" under "Virtual Networks" and provide below details as shown below.

Give Name as HeartBeat and "Network adapter on physical computer" to be select as "None (Guests Only)"

Click on "OK"

Add SCSI Adapters

Now to attach this to MyNode1 and MyNode2, we need to Turn Off those machine.  Go to "Master Status" link and select link to "Turn Off"

Once done, we need to choose "Edit Configuration" as shown below:

At “MyNode1” Configuration page, click on SCSI adapters as shown below

Then you will get a screen like below

Click on the button and fill information as below. make Sure that you check "Share SCSI bus for clustering" and ID as 7.

And Click "OK"

You should see below on final screen.

Above Steps to be done as MyNode2 also. Difference is SCSI adapter ID. For MyNode2 it should be ID as 6.

here also, Click OK once added.

Add Network Adapters

Go back to "Edit configuration" Page for MyNode1 and this time we will choose "Network Adapters"

You will get below screen

Click on "Add Network Adapter >>" and Provide "HeartBeat" network.

Hit "OK"

Follow the same steps with MyNode2 configurations

Hit "OK"

Add Quorum Disk to Both Nodes

Below steps are self explanatory.

Go to Edit Configuration for MyNode1 and click on "Hard disks"

One you click, you will get below screen. Fill in the location of Q Drive which we created earlier. (E:\Virtual Server\Q-Drive.vhd)

Now, Start MyNode1 and log on to the node. we need to format the drive which we attached in above step and call it drive Q.

Clicking on Disk Management would result in below screen.

Select all default setting and finish the Wizard (don't make any modifications)

Then Right click on the Disk and assign drive letter Q: to it (as below)

On Finishing this you would be able to see Q drive in My Computer.

This finishes shared disk on MyNode1. Shutdown this Node because in order to attach and configure the shared disks to the cluster nodes, you must ensure that the other node is turned off (In our case MyNode1 should be off).

a. Logon to MyNode2 and Right-click My Computer, click Manage, and then expand Storage.

b. Double-click Disk Management.

c. The shared drives are listed, but have not been assigned a drive letter. Right-click the drive and select Change Drive Letter and Paths.

d. Click Add.

e. Use the drop-down box to change the drive letter to Q, and press OK.

Now you should be able to See Q drive in My Computer on MyNode2. This finishes setting up shared disk for Both the Nodes.

Ahh.. this page has lots of screen and it would be slow to load. I am continuing in Part 3.

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

After delivering SQL Training, I realized that there is a need to write a blog which talks about Installing SQL Server 2005 cluster on Virtual Environment. Many DBA wants to see how Cluster looks like and they don't have capability to buy/see actual cluster.

There is an easy solution to the problem by using Virtual Server 2005.

I will try to be as descriptive as possible. In case something is not clear, please provide feedback.

Prerequisites

You also have to download Microsoft Virtual Server (http://technet.microsoft.com/hi-in/bb738033(en-us).aspx)

You also need to download ISO image for SQL Server 2005 evaluation edition (http://www.microsoft.com/downloads/details.aspx?FamilyID=6931fa7f-c094-49a2-a050-2d07993566ec&DisplayLang=en)

You should also have virtual hard disk of operating system. (Download available http://www.microsoft.com/downloads/details.aspx?FamilyID=77f24c9d-b4b8-4f73-99e3-c66f80e415b6&DisplayLang=en)

Above link has three files to download. You have to download all files. Once you download content from above link, you need to extract to a folder in local machine using WIN2K3R2EESP2.part1.exe. I extracted in (E:\Virtual Server)

Please refer the readme file in above folder after you download.

NOTE: THIS SETUP WILL WORK ONLY FOR 30 days(depends on Read Me)

One you extracted the files, make three copies of virtual hard disk. One for Domain Controller, second for node1 and third for node2. I have created files as below:

E:\Virtual Server\MyDC\MyDC.vhd

E:\Virtual Server\MyNode1\MyNode1.vhd

E:\Virtual Server\MyNode2\MyNode2.vhd

Once you have downloaded and installed Virtual Server you should see new menu item in Programs.

Now, we will configure the Virtual Servers. First we need to Create Virtual Machine so that we have three separate virtual servers available. Make sure you set the Search Path at below screen.

On Left Side Menu choose Virtual Server > Server Properties > Search Path

Create Domain Controller

below is the screen shot of "MyDC" setting. Information which I filled in is

Click on Create.

Now, Go to Master Status and try to "Turn ON" the domain controller (I know its not yet a DC but in next step we are going to make it)

One you log in to MyDC, rename the machine to MyDC (by right click on My Computer > Properties) You need to restart the computer after renaming.

Next step is to run "dcpromo" command to make this machine as domain controller. Start>Run>dcpromo

Accept all settings as default, below are few which you need to modify.

During setup if it asks for windows installation CD, please point it to C:\WindowsInstallationFiles\I386 folder. Also, when prompted, please provide IP address as 192.1.1.1. You will be prompted to restart the machine.

When you can logon screen after reboot, "Log on to" should have MyCluster selected.

Now we have one machine ready which will act as domain controller for our cluster setup.

Create Member Servers

You are now having one Domain Controller in your Virtual Network. Now, lets add two member servers in the domain. As we did earlier, we have to add rest two vhd to server. Below screen shot is for Node2.

After configuration on Virtual Servers, follow below steps

1. Rename the nodes to MyNode1 and MyNode2 (still in Workgroup).

My Computer > Right Click > Properties > Computer Name

Then Restart MyNode1 and MyNode2

2. Disable the Firewall on all three machines.

Control Panel > Windows Firewall

Select "Off" from the UI.

3. Change the IP address of machines.

MyNode1 - 192.1.1.2 (DNS Server 192.1.1.1)

MyNode2 - 192.1.1.3 (DNS Server 192.1.1.1)

4. Join the machine to domain (MyCluster.com) Below screen shot.

This finishes setup of one domain controller and two member servers.

Now we have to make MyNode1 and MyNode2 as single cluster.

lets move on to Part 2