How to setup a SQL Failover Cluster with VMware WorkStation

I wanted to setup a SQL Cluster environment with VMware workstation just to play with it and check out some of the features.   I don’t have a SAN (SCSI) setup at home so I need to virtualize that.  What I do have though is a 6 core 16 GB Ram work station that is fully capable of running this so here we go…..

Minimum Requirements

Host Machine

• Processor – 2 – 4 Cores
• Memory  – 8 GB’s

Client Machines

Domain Controller  (Required for VSAN and Clustering)

• 1 –2 Cores
• 1 GB Ram

Virtual SAN (iSCSI)

• 1 –2 Cores
• 1 GB Ram
• 200-500 GB Virtual Hard Disk

SQL Servers (2)

• 2 –4 Cores
• 2 GB Ram

Setup Operating Systems (Virtual Machines)

See my post here for details on how to setup a base virtual machine to make this process easier. 

1.  Setup a domain controller and create a domain (see here for tips on setting up the domain controller)

- create a domain level admin user account for usage later. Example – sqladmin

2.  If a Virtual SAN is required – see this post for detailed steps on setting this up.

3.  Create 1st SQL Virtual Machine

• Create Linked Clone
• Add a second host only network adapter
• Rename machine to SQL1
• Join to Domain
• Enable the Failover Clustering Feature (Administrative Tools – Server Manager – Features – Add Feature – Failover Clustering)
• Install the iSCSI client software but do not create the drives yet – details

4.  Create 2nd SQL Virtual Machine using the above steps

At this point all machines should be up and running.

Setup the SAN Disks

Reference this post for details on the steps below

1.  3 Drives must be created on the iSCSI target (order is critical)

• 5 GB drive – label Quorum
• 10 GB drive – label MSDTC
• 100 + GB Drive – label Data

2.  Connect each of the drives to the SQL1 Machine and format NTFS

3.  Connect each of the drives to the SQL 2 Machine (no formatting required)

The disks should now be available for usage in the cluster

 

Setup the Cluster

We want to test the configuration before proceeding so we can fix any changes ahead of time. 

Validate the cluster

1.  Launch the cluster manager (Administrative Tools – Failover Cluster Manager)

2.  Click on Validate a Configuration

3.  Click next  – then enter the dns names of the two servers in the Select Servers Wizard

4.  Once selected – run all of the tests and ensure they all pass.  If not – you will need to address these before continuing.

5.  Only after validating the configuration should you continue. 

Setup

1.  Select the two servers for the cluster

2.  Provide an access point for the cluster (Name that it can be accessed by)

3.  Complete the wizard.  Once the cluster is created – move to the next step.

4.  At this point the two nodes should appear in the cluster manager with a status of Up.  Under the Storage section you should see all 3 drives.  The first drive should by under the Quorum section with the other two drives not being used.  If they are not online – right click and select "bring this resource online”

5.  Go to Services and Applications – right click and select “Configure a service or application”

6.  Select the DTC and click next

7.  Give it a name, and then select the 2nd disk we created for storage.  Complete the wizard. 

At this point we are ready to install SQL Server

Setup SQL Server

SQL 1

1.  Put in your SQL Server 2008 R2 disk or mount the ISO file.

2.  Launch the installation and go to the SQL Server Installation Center.

3.  Select Installation – New SQL Server failover cluster installation

4.  Ensure all of the prerequisites pass. 

5. Walk through the wizard

• Select DB Features

• Set your instance configuration and your public Cluster Name

• Select your cluster disk

• Under Service Accounts – use the Domain user you created above
• Complete the rest of the configuration based on your preferences

Once the installation is complete – you can now connect to SQL server.  Time to setup the other half. 

SQL 2

1.  Put in your SQL Server 2008 R2 disk or mount the ISO file.

2.  Launch the installation and go to the SQL Server Installation Center.

3.  Select Installation – Add note to a SQL Server failover cluster

4.  Walk through the steps and select the cluster to attach to from the SQL 1 setup.

5.  Once installation is complete – you now have a failover ready. 

SQL Server should now show up under the Services and Applications section in the Failover Cluster Manager. 

How to create a Virtual ISCSI SAN Disk with VMware Workstation and Server 2008 R2

This post explains how to setup Windows 2008 R2 to be a virtual San that can be connected to from other machines.  This is helpful for providing shared storage for testing items like clustering.

Note: This should NOT be used in a production environment.

Setup Target

1.  Setup a base 2008 R2 VM with all of the latest updates.  I normally have one of these on hand and create linked clones from it to save space.  See my post here for details on setting this up.

2.  If you do not have a domain controller – you must setup one up.  Again I use the base VM mentioned in Step  to create a domain controller off of.  See my post here on how to setup a Domain Controller with VMware without upsetting your network environment.

3.  Now you are ready to create the Operating system.  Create a linked clone from the base, rename the machine name (I will use VSAN for machine name), and join the machine to your domain.

4.  Download the Microsoft iSCSI Software Target 3.3 from the following site.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=45105d7f-8c6c-4666-a305-c8189062a0d0

5.  Copy iscsitarget_public.msi to the VSAN machine and launch the installer.  Follow the dialogs and perform the standard installation.

6.  Now launch the administrator via Administrative Tools – Microsoft iSCSI Software Target

7.  Right click on iSCSI targets and Create iSCSI target

8.  Give a valid name to your Target (I’ll call this SAN1)

9.  On the iSCSI Initiators Identifiers screen – this can be confusing.  For the IQN you will want to give it a name that your Client machine will use.  Since we haven’t set that up yet – we have to guess (and come back later to fix if needed).  The syntax for this is as follows.

iqn.1991-05.com.microsoft:machinename.domainname.com

The italicized words will need to be changed to match your environment.

10.  Click finish to complete.

11.  Right click on the newly created target in the tree and create a virtual disk

12.  Walk through the wizard providing a file name, space (note it’s in megabytes), and description.

13.  Make sure to Check the ‘Enable this target storage services’ checkbox” in the SAN1 properties. Otherwise the Discover Targets doesn’t work when configuring the clients

At this point you now have a valid SAN disk available to your client.

Setup Client

This can be setup on most of the newer operating systems.

1.  Login to the OS as an administrator

2.  From the download earlier – get the following file for installation

iscsitargetClient_public.msi

3.  Start the installation and follow the defaults.

4.  Now launch the program via Administrative Tools – iSCSI Initiator

5.  Go to the Discovery Tab and click Discover Portal… Type in the IP Address of the VSAN machine we created in above and click ok.

6.  Go to the targets Tab and click refresh.  You should see an entry in the Discovered targets list box.  If the status does not show as connected – highlight the item and click Connect.

7.  Now go to the Volumes and Devices tab.  Click Auto Configure to automatically make the SAN drive available.

Now if you go into disk management you can see the drive.  Bring it online, initialize it, and create a partition to start using it.

How to setup a base Virtual Machine for use in VMware Workstation

This post discusses setting up a base Virtual Machine to allow creating multiple machines in a timely manner. 

Create Base Image

1.  Create a new virtual machine for 2008 R2.  I normally use the following base specs

• 1 – 2 processors
• 1 Gig of Ram
• 60 gig System Drive (Dynamically Expanding)

2.  Launch the VM and install Server 2008 R2

3.  Update the VM with all of the latest patches.  I normally install .net 3.5 SP1 (enable) and .net 4.0 at this point

4.  Sys Prep the machine so when cloned – it will launch with a new SID so it can be joined to a domain without the need for major changes

• Launch C:\Windows\System32\sysprep\sysprep.exe
• Set to the settings below and click ok

5.  The machine is now shutdown.  Take a snapshot and label “sysprep”.

You are now ready to clone this machine to another machine

Clone Machine

1.  Right click on the VM Tab and select Clone

2.  Choose the snapshot created above

3.  Select your location (I like to use a sub folder inside the base VM folder for ease of use when migrating to a different machine / disk / etc). 

4.  Once the clone is complete – modify the hardware settings as needed (Number of processors, memory, etc)

5.  Launch the machine and rename the Computer name to a desired name

WPF DatePicker not updating SelectedDate Property–Part 2

Well me last post here addressed the first issue but caused major issues with the validation and updating of dates at the appropriate times.  It was more problematic than the original issue.   It was time to find another solution.

Summary of Issue

To recap the issue – I have a DatePicker that is inside a WPF User Control that is hosted in a C++ application that also has WinFormHost Controls in the user control. The issue occurs when typing in a date (not using the date picker).  Anytime you type in a date and then directly click on the WinFormHost or the native application – the focus changed event does not fire on the WPF DatePicker which could potentially cause underlying properties to not update.  This resulted in data loss and validation events not being fired. 

I decided to inspect the source code of the date picker to see what was happening internally.  In a nutshell, when the internal text box loses focus – it parses the string and sets the SelectedDate property.  Now I have an alternative.

Work Around / Hack 

I created a method (below) that forces the property to be updated when ever this method is called.  I added multiple events around the user control to intercept the mouse / keyboard events and then call this method. 

         /// <summary>
        /// Forces a refresh of the date picker UI
        /// </summary>
        public void SetDatePickerProperties(bool ignoreFocus)
        {            
            // This is a hack - WPF datepicker not losing focus with interops.
            
            if (ignoreFocus)
            {
                string s1 = dpEstImplDate.Text;
                DateTime? d1 = ParseText(s1);
                dp1Date.SelectedDate = d1;
                PerformManualValidation();
            }
            else
            {
                // Only perform this if date picker has focus
                if (_dp1HasKeyboardFocus)
                {
                    string s = dpEstImplDate.Text;
                    DateTime? d = ParseText(s);
                    dpEstImplDate.SelectedDate = d;
                    PerformManualValidation();
                }
            }
        }

 

Below is the parsing method being used to get the date from the text box. 

/// <summary>
        /// This method was pulled and modified from Source of WPF Toolkit
        /// </summary>
        /// <param name="text"></param>
        /// <returns></returns>
        private DateTime? ParseText(string text)
        {
            DateTime newSelectedDate;            
            try
            {
                if (DateTime.TryParse(text, out newSelectedDate))
                {
                    return newSelectedDate;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
            return null;
        }

Here are some of the event handlers used to ensure the properties are always updated.  I also have a mouse leave event on the main user control that performs the same functions. 

        private void dp1Date_LostKeyboardFocus(object sender, KeyboardFocusChangedEventArgs e)
        {
            SetDatePickerProperties();
            _dp1HasKeyboardFocus = false;
        }

        private void dp1Date_GotKeyboardFocus(object sender, KeyboardFocusChangedEventArgs e)
        {
            _dp1HasKeyboardFocus = true;
        }

Oracle 11g2 Minimum required components for use with .Net

The following instructions are for setting up an oracle client with the minimum required components to be used with .Net applications. 

 

1.  Download the latest Oracle 11g2 client software from Oracle. 

Note – Be sure to determine which bit level you need if using a 64 bit operating system.  If your apps are compiled to 32 bit specifically then you must use the 32 bit oracle client.  If they are compiled to run natively – then you must use the 64 bit oracle client software.

2.  Extract the zip file to a directory

3.  Open the folder and launch the setup.exe file as an administrator

    

4.  Select the custom option in Step 1 of 8 for what type of Installation you want and click next

    

 

5. Now choose the Oracle Data Provider for .NET option and click next

    

6.  Complete the remaining steps using the default options

7.  Once complete – navigate to the following folder

     [installation path]\product\11.2.0\client_1\network

8.  Create a new folder named admin

9.  Copy an existing sqlnet.ora and tnsnames.ora file into the admin directory

10.  Test the installation with a .net application or you can use one the OracleConnectionTesting utilities provided on this blog here