Balmukund

SQL Server 2005 Express setup failure with error - An error occurred during encryption.

Recently I have worked on one of the case where SQL Server Express Edition was failing with Error

The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually

This is generic error and can be caused by many reasons. We need to look at SQL Error Logs / Event Logs and find out actual reason. From Event Log error was “The process terminated unexpectedly”. Not very useful in this case.

Next, we looked into SQL Error Log

2009-11-16 15:47:17.26 spid5s      Error: 15209, Severity: 16, State: 1.
2009-11-16 15:47:17.26 spid5s      An error occurred during encryption.

Above was the cause of SQL Startup failure. Basically there was an unhandled exception raise by SQLServr.exe process and terminated. If you search on support.microsoft.com for this error, this is the KB Error message when you try to install SQL Server Express or SQL Server. If you are using NTFS then first check KB before processing further.

In our case KB was not applicable because it was FAT file system and there is no option to set security as mentioned in KB. To troubleshoot further we have taken crash dump of SQL Server process using DebugDiag. You can refer to documentation to see how to take crash dump but I would show you what we got from the dump.

kernel32!RaiseException+0x53
msvcr80!_CxxThrowException+0x46
sqlservr!TurnUnwindAndThrowImpl+0x13c
sqlservr!ex_raise2+0x48f
sqlservr!ex_raise+0x68
sqlservr!RaiseCryptoError+0xda
sqlservr!CreateSrvMasterSecret+0x2d2
sqlservr!LoadSrvMasterSecret+0x139
sqlservr!LoadServiceMasterKey+0x11
sqlservr!StartUp::StartResourceDB+0x3ca
sqlservr!StartUp::OpenDBsAndRecover+0x4d8
sqlservr!StartUp::InitDBMS+0x287
sqlservr!SOS_Task::Param::Execute+0xe2
sqlservr!SOS_Scheduler::RunTask+0xb9
sqlservr!SOS_Scheduler::ProcessTasks+0x141
sqlservr!SchedulerManager::WorkerEntryPoint+0x1ab
sqlservr!SystemThread::RunWorker+0x7f
sqlservr!SystemThreadDispatcher::ProcessWorker+0x246
sqlservr!SchedulerManager::ThreadEntryPoint+0x143
msvcr80!_callthreadstartex+0x1b
msvcr80!_threadstartex+0x66
kernel32!BaseThreadStart+0x37

In layman terms, we were trying to create self generated certificate using Crypto API provided by windows. When we looked further, we found exception as 0xc000003a. This means STATUS_OBJECT_PATH_NOT_FOUND.

We took procmon (available at http://technet.microsoft.com/hi-in/sysinternals/default(en-us).aspx) and found interesting stuff

3:47:17.2609980 PM    lsass.exe    CreateFile    C:\WINDOWS\system32\Microsoft\Protect\S-1-5-18\User\Preferred    PATH NOT FOUND   
3:47:17.2612661 PM    lsass.exe    CreateFile    C:\WINDOWS\system32\Microsoft    NAME COLLISION   
3:47:17.2615210 PM    lsass.exe    CreateFile    C:\WINDOWS\system32\Microsoft\Protect    PATH NOT FOUND
3:47:17.2617194 PM    lsass.exe    CreateFile    C:\WINDOWS\system32\Microsoft\Protect\S-1-5-18    PATH NOT FOUND
3:47:17.2619209 PM    lsass.exe    CreateFile    C:\WINDOWS\system32\Microsoft\Protect\S-1-5-18\User    PATH NOT FOUND

So I can see PATH NOT FOUND, above this I can see NAME COLLISION. This essentially means that there is a object already existing. When we looked at customer’s machine, we found a file called “Microsoft” (Ideally, this should be a folder) which was causing SQLSetup to not to create a folder and hence failing. Lsass.exe is responsible for many things (refer documentation in KB308356)

To resolve the issue, we have renamed Microsoft file to Microsoft_1 and then as expected, sqlservr.exe was able to start and setup worked like a charm.

If you have any such issue, ProcMon is the first thing to look at along with Event Logs and SQL Error Logs.

Hope this helps someone on the earth (and beyond)!!!

- Balmukund

Did you know? – SQL 2008 Management Studio shows missing indexes for a query in human readable format?

set nocount on
go
USE [tempdb]
GO
-- create a table with two columns
CREATE TABLE [dbo].[MyTest](
    [i] [int] IDENTITY(1,1) NOT NULL,
    [j] [char](8000) NULL
) 
GO
-- inserting 10000 rows
INSERT INTO dbo.MyTest (j) VALUES ('A')
GO 10000    -- this is another cool feature to run a batch in loop
-- below is to get the plan without running the query
SET SHOWPLAN_XML ON
GO
SELECT * FROM MyTest WHERE I = 5
GO
SET SHOWPLAN_XML OFF

Once you run above code and look at results in grid mode, you will see a hyperlink as below.

Clicking on hyperlink would open another windows in SSMS as shown below.

Hope this helps to someone in the world.

As always, comments are welcome.

- 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. This works in SQL 2008 as well.

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

If you want to see who dropped a database, you can right click on Server Icon and go to Reports > Standard Reports > Schema Changed History. This Reports output would changed based on where you right clicked it (on server icon or database name)

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, see who dropped databases, how to find who dropped the database in SQL server 2005 objects dropped</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