Thursday, October 4, 2012

It been quite a while that I created a SQL Server 2003 R2 Cluster. Though of recreating it. This is considered fairly advanced topic and is related to high availability (HA), business continuity, and disaster recovery.

First and foremost: Bare-metal(read: physical) host machine with enough memory/space (~4GB/20GB) – works well if you’ve gotta work horse!

VMWare WorkStation v7.0 or above would be great

Windows Server 2008 R2, trial edition

SQL Server 2008 R2 Enterprise Edition, trial

StarWind Software for Cluster/Node configuration (freeware)

The above is run and test for following specs: Intel Core i7, 6GB Memory

What will be IP address scheme?

Lets use following IP address scheming. Note that you can use any that you want, but for the sake of simplicity and the ease of use, since we all are very well familiar with 192.168… so is one of the reason.

Windows Server 2008 R2 Domain Controller:

IP: 192.168.1.110

Subnet: 225.225.225.0

Gateway: Switch/Router IP (Etisalat or Du if you have?)

DNS: 127.0.0.1

Node 1: SQL Server Cluster node

IP: 192.168.1.111

Subnet: 225.225.225.0

Gateway: Router IP

DNS: 192.168.1.110

Private IP: 192.168.2.11

Node 2: SQL Server Cluster node

IP: 192.168.1.112

Subnet: 225.225.225.0

Gateway: Router IP

DNS: 192.168.1.110

Private IP: 192.168.2.12

Windows Cluster IP: 192.168.1.113

SQL Server VIP: 192.168.1.115

SQL Server VIP (Active/Active): 192.168.1.116

MSDTC IP: 192.168.1.114

SQL Server Analysis Services VIP: .118

Notes: Checkout my another post for quick glossary.

VIP, is Virtual IP, assigned to the resources within the cluster.

Active/Passive: Usually used for database clustering

Active/Active: Usually used in application servers

STEP 1: Setup Domain Controller:

Open the domain controller OS from the VM workstation.

STEP 2: Run windows update

We need to run windows update just so the operating system is up-to-date with the latest patches and service packs.

Rename computer name to something valid, for instance DC-W2008R2

STEP 3: Install Domain Controller

Go to run, type dcpromo.exeand enter to execute; you will see following popup.

Followed by following screenshots:

Create a new domain forest

Select windows 2008 R2 for the forest functional level, as shown in the figure below. Domain functionality activates features that decides the scope of function application, to the whole domain. There multiple domain functional levels (three to 4). Please note, there are cases where bringing the forest to a lower functional level from higher OS level, that is, process in irreversible. SO, you might like to do your thorough research and decision making, before you proceed with this option.

Check the DNS Server box. Hopefully you know that a DNS server is a naming server; and what that really means is that, it is a server that converts a domain name (google.com, microsoft.com, or izlooite.blogspot.com, etc) to actual IP address.

Clicking on next button will popup a confirmation windows.

Active Directory settings, configurations folder

Provide domain controller password

Completing DC setup

STEP 5: Create Zone

What is zone? Above the top, know that every domain and subdomain is considered a Zone.

Now we need to create a zone. Go to DNS manager and click on create new zone.

Just so if you do not know, a DNS contains list of domain names (also called DNS records), and to keep DNS records in order DNS Zone is created. It is a combination of A-MX-CNAME-NS-TXT, for instance following is a sample DNS record for my weblog:

Anyway, lets open the DNS manager from Start—>Administrative Tools—>DNS

Now you may ask what is Forward Lookup Zone, or what is Reverse Lookup Zone; to keep it simple, just remember, when you provide a domain name to DNS it resolves the name and provides you with IP address, this is called Forward Lookup.

Conversely, when you provide IP address to find out the domain name or host name, is called Reverse Lookup Zone; and it written in reverse order. It has more into it, but for now for the sake of this article just remember,

Now select Reverse Lookup Zone, right click to create a new zone. Please remember this is our Windows Server 2008R2 Domain Controller machine, on which we just installed the DC setup. You will see following screen to setup the Zone. Just click next.

Here we need to provide the Zone Type and the Storage, where it will keep the zone information. By default, Primary Zone is selected and Store the Zone in Active Directory option is checked, which is what we want. So just click next.

Next we need to select Zone Replication Scope, which means how the DNS information will be replicated throughout the network across all the server machines. We will choose the default option “domain controllers in this domain”, and click next.

Next choose the IP address scheming, which is IPv4 or v6. I have chosen v6 for this example, but you can go ahead and try v6 if you want. In this case it is better you have all the machines set for v6, and you wouldn’t know much difference in configuration but on the packet level 64bit IP addresses are being used. I doubt many company using v6.

Then, we need to provide the Zone, and for this we are using same old: 192.168.1.0…255. Notice, in the red highlighted box in figure below, how system calculated the reverse proxy as you typed in the Network ID, and also notice that it is in reverse order, as described above. Click next…

Now we will add the hosts (Nod1 and Node2) in forward lookup. In the same DNS Console, right click forward lookup and select New Host, like following figure:

We will provide following configuration:

Hostname: node1 and IP: 192.168.1.111. If you want you can refer above to section “IP address scheme”. Just so you are aware of the scheming that I have chosen. And check the box for “host PTR record”.

Know that, PTR (host PTR records) records are used for the reverse DNS lookup, that is, they have your IP address and want to know the host name. Here PTR stands for pointer.

Do that for node 2 as well.

You will have following items available as a result: both nodes

Now while we added the hosts in forward lookup and checked the box for “host PTR record”. The entries will appear in the reverse lookup section as well.

We are done with the DNS Zone setup.

STEP 6: Create Cluster Administrator

Here we need a user (create or use an existing one) that will act as cluster administrator and will be used from other machines to bring those machines in this cluster. Hope you get the idea.

Open active directory console and add a user, say cluster-administrator, and assign the group Domain Admins.

OK, here at this point in time, you are done creating the domain controller, congratulations! (0:

STEP 7: Add Nodes to Cluster

This step is fairly simple, all you have to do is to go where you go to change the computer name, and there you have option of Workgroup or Domain. So in this case you will have to choose Domain, and provide IZLOOITE.PK there, and it will ask the domain controller administrator to login and validate.