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.
What you will learn?
- How to install and configure Windows Server 2008 R2 Cluster?
- How to install and configure Domain Controller?
- How to install and configure Quorum Disk?
- How to install and configure Fail Over Clustering?
- How to install and configure SQL Server 2008 R2?
So, we will walk through following steps.
Required steps towards SQL Server 2008 R2 Cluster
- Build a node for Windows Server 2008 R2 Cluster with Domain Controller
- Install SQL Server 2008 R2 on Fail-Over (tip: Fail-back) cluster.
- Build two nodes (Windows Server 2008 R2)
- Install SP1 on both nodes
- Setup Active-Active Cluster
- Setup Cluster: SQL Server Analysis Services (SSAS)
- Setup Cluster: SQL Server Integration Services (SSIS)
Do you have what it takes? (read: pre-requisites)
- 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: 184.108.40.206
- 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: 220.127.116.11
- 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: 18.104.22.168
- 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.exe and 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.
STEP 4: Create a DNS Server
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:
izlooite.blogspot.com. NS dns1.somehosting.com. ; NS=Name server for izlooite
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,
- Forward = Hostname to IP
- Reverse = IP to hostname
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…
Next, select default option “allow secure dynamic updates” and click next, and finish.
You can right click and check the configuration settings.
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.
Next Article: Part 2/2 – Completing Installation/Configuration SQL Server 2008 R2 Cluster (Active/Passive) SetupBlogger Labels: Server,Domain,Controller,Drive,Part,Cluster,Though,topic,disaster,recovery,Windows,Quorum,Disk,Fail,Over,Build,node,Install,nodes,Setup,Active,Analysis,Services,SSAS,Integration,SSIS,requisites,Bare,machine,memory,horse,VMWare,WorkStation,edition,Enterprise,StarWind,Software,configuration,specs,Intel,Lets,Note,Subnet,Gateway,Switch,Router,Etisalat,Private,MSDTC,Notes,Checkout,glossary,Virtual,resources,Passive,database,servers,STEP,Open,system,Rename,computer,instance,Create,Select,scope,decision,option,MSDN,Directory,Functional,Levels,Check,confirmation,settings,configurations,folder,Provide,password,Above,Reference,Name,Zones,manager,Just,combination,CNAME,Anyway,Start,Administrative,Tools,Forward,Lookup,Reverse,article,Hostname,Here,Type,Storage,information,Primary,Replication,controllers,example,difference,packet,Notice,proxy,Network,Click,Console,Host,pointer,items,Administrator,user,Hope,Admins,Workgroup,IZLOOITE,Installation,sake,popup,blogspot