SQL Server 2008 R2 Failover Clustering in vmware
Download
Report
Transcript SQL Server 2008 R2 Failover Clustering in vmware
James KONG
[email protected]
Directly attached SCSI storage is no longer
supported for Failover Clustering in Win2k8R2.
SAS(Serially Attached SCSI Storage), Fiber
Channel and iSCSI are supported
Needed at least 4 disks
One for the quorum disk(at least 512MB)
One for the MSDTC(MS Distributed Transaction
Coordinator)(as small as 1GB)
One for the SQL Server system DB
One for the user DB
The iSCSI storage will be used with the help of an iSCSI Software Initiator
to connect to a software-base target.
Windows 2008 Server R2 Failover Clustering
If you make a mind to use iSCSI as your shared
storage, a dedicated network should be used so
as to isolate it from all other network traffic. ->
you can access the iSCSI storage through the
dedicated network card on cluster nodes
MSDTC(Distributed Trnasaction Coordinator)
iSCSI Initiator software enables connections of
a Windows host to an external iSCSI storage
array using network adapters.
If you use other versions of MS-Windows, you
need to download and install the iSCSI Initiator
prior to connecting to an iSCSI storage
FreeNAS will be used as iSCSI storage
emulator
Start → Administrative Tools → iSCSI Initiator
The targets have been
connected using the
iSCSI Initiator, you can
now bring the target
disks online, initialize
them, and create new
volumes using the Server
Manager.
Launch ‘Add Roles’ wizard from Server
Manager
Select Incoming Remote Transactions and
Outgoing Remote Transactions. These options
will be used by MSDTC
Simple and easy work to add the Failover Cluster
feature in Win2K8 R2
You have to do this work on both nodes to complete
the Failover Clustering
To accomplish the Windows 2008 Server R2
Failover Clustering, user account must be
change from workstation user type to domain
user type and the nodes, also, have to run as
domain members
Failover Clustering feature needs a domain
user account
Launch ‘Failover Cluster Manager’ from
‘Administrative Tools’
Click ‘Validate a Configuration’
If everyting in the result is fine, start ‘Create a
Cluster’ from ‘Failover Cluster Manager’`
There are no option to select the disk
subsystem that you can use as a quorum
disk(“witness” disk) in the ‘Create a Cluster’
wizard. By default, the wizard will use the first
available disk as the witness disk.
You can check it in the ‘Storage’ node under
the ‘Failover Cluster Manager’ console
Select the name of the cluster what you want to
change under the ‘Failover Cluster Manager’
console
Right mouse click on the selected cluster, select
More Actions, and click Configure Cluster
Quorum Settings…
In the wizard, select the Node and Disk
Majority (recommended for your current
number of nodes) option. The displayed
contents will depend on how cluster configured.
The Microsoft Distributed Transaction
Coordiantor(MSDTC) is a transaction manager
that permits client applicants to include several
different data sources in one transaction and
which then coordiantes committing the
distributed transaction across all the servers
that are enlisted in the transaction
SQL Server uses the MSDTC service for
distributed queries and two-phase commit
transactions
Open ‘Failover Cluster Manager’ console on any of
the cluster node
Right mouse click on Server and Applications and
select Configure a Service or Application
In the Service or Application dialog box, select
Distributed Transaction Coordinator(DTC)
In the Client Access Point dialog box, enter the
name and IP address of the clustered MSDTC.
The IP address should be different from the
one that the Windows 2008 Server R2 cluster is
already using.
In Select Storage dialog box, select the disk
subsystem that will be used by MSDTC. These
disk subsystems have to be defined as available
storage in cluster.
The available disk must be allocaed dirver
letter and both clusters should be running.
Validate the installation of MSDTC by expanding all the
nodes related to Failover Clustering. Especially note the
Storage node
Launch ‘SQL Server
Installation Center’ from
the installation media
Click the ‘New SQL
Server failover cluster
installation’ link at
installation step
The installation program
examines the ‘Setup
Support Rules->Files’,
which identify problems
that might occur when
you install SQL Setup
support files(7 rules).
After entering the ‘Product Key’ and checking
the ‘License Terms’, in the ‘Setup Support
Rules’ dialog box, click install. Validate all the
checks. If the results show a few warnings or
errors, make sure you fix them before
proceeding. For example, the public network
cards should be first on both nodes in the
Networking binding order. Also, disabling the
NetBIOS and DNS registration is good to avoid
network overhead(Microsoft KB 955963)
Select the components what you want to install
from the Feature Selection dialog box.
Enter the ‘SQL Server Network Name’ and
select ‘Default instance’ in the ‘Instance
Configuration’ dialog box.
The Instance ID is used as the Instance Name
by default. If you want to run multiple
instances, change the Instance ID to
distinguish each instance among them.
Detected SQL Server instances and features
on this computer section would make sense if
there are other instances running on server
In the Disk Space Requirements dialog box, check
that you have enough space on your local disks to
install SQL Server binaries
Check the available resources on Windows
Server Cluster. A new Resource Group will be
created on Windows Server Cluster for SQL
Server.
Select the available disk resource groups that
are on the Windows Cluster for SQL Server to
use in the Cluster Disk Selection.
In the Cluster Network Configuration enter
the IP address and subnet mask that SQL
Server cluster will use. Deselect the DHCP
column as you will be using static IP address.
In Cluster Security Policy dialog box, select
the default value of User service
SIDs(recommended). If you want to use
domain group, select it and enter DB Engine
and Agent group id.
Enter credentials for SQL Server service
accounts in ‘Service Accounts’ tab. Note that
the startup type is set to manual for all clusterware services and can’t be changed during the
installation process.
Select the appropriate ‘Authentication Mode’ in
the Database Engine Configuration dialog box.
On Data Directories tab, enter the system and
user db files path. By default, the first shared
disk in the cluster will be showed.
In the Cluster Installation Rules dialog box,
verity that all checks are successful.
Congratulations! This concludes the
installation of a SQL Server 2008 R2 Failover
Cluster.
Successful installation and configuration of the
node presents a fully functional failover cluster
instance.
Dispite of a fully functioning SQL Server
failover cluster, it doesn’t have HA(highavailability) because of only one node in the
failover cluster.
It is necessary to add a node on a SQL Server
2008 R2 Failover Cluster for HA. If the first
node(SQL Server installed node) fails to
operate, the client couldn’t get a service from
SQL Server cluster.
The number of nodes can be added in a failover
cluster depends on the editions of SQL Server.
A Standard edition supports up to 2 nodes
A Enterprise edition supports up to 16 nodes
(practial limit for the Enterprise edition for
Windows Server 2008 R2)
From now on, SQL Server 2008 R2 will be
setting up on the 2nd Windows Server Failover
Cluster node.(similar to the first installation, most of
the screenshots will be omitted)
In the SQL Server Installation Center, click the
Add node a SQL Server failover cluster at the
Installation step.
Setup Support Rules, Product Key, License
Terms, Setup Support Files will be omitted
In Cluster Node Configuration step, verify
that the information for the existing SQL
cluster is correct
In Service Accounts step, verify that the
information is the same as configuration for the
1st node.
In Error Reporting, Add Node Rules, Ready to
Add Node step, verify that all things are good
and successful.
If you click ‘Next’ button from Ready to Add
Node, Add Node Progress will install the 2nd
SQL Server in the 2nd Windows Server Cluster
node.
SQL Server 2008 R2 failover cluster add node
operation is completed without any errors.
Check the
properties of ‘SQL
Server’ in ‘Services
and applications’
under Server
Manager
Cluster Node 1
name : Win2k8x64_1
IP: 192.168.100.101/24
192.168.100.99/24
Virtual Host
VIP : 192.168.100.103/24
Cluster Node 2
name : Win2k8x64_2
IP: 192.168.100.102/24
Domain Controller(Win2k8x64_dc)
domain name : woori.com
IP : 192.168.100.100/24
MSDTC
machine : Win2k8x64_1
IP: 192.168.100.104/24
SQL2k8R2 Cluster
IP: 192.168.100.105/24
192.168.100.99/24
SQL2k8R2Cluster
VIP : 192.168.100.105/24
SQL2k8R2 cluster node added
name : Win2k8x64_2
Domain Controller(Win2k8x64_dc)
domain name : woori.com
IP : 192.168.100.100/24
Domain Controller
Windows Failover Cluster node 1
Windows Failover Cluster node 2
Windows Failover Cluster server
MSDTC
SQL Server R2
Failover Cluster server
Stop Cluster
node 2 NIC
and trying to
connect to
SQL Cluster
from
Domain
Controller is
successful.
(Node 1
(192.168.100.101)
has been
hosting the
MSDTC and
SQL Server)
Change the hosting
of MSDTC and SQL
Server from node 1
to 2(moving apps or
services.) namely
change current
owner from node 1
to 2
Stop Cluster node 1
NIC and trying to
connect to SQL
Cluster from
Domain Controller
is successful.
One of tasks of DBA is to apply patches on the
engine. It is not as easy as applying patches on
a stand-alone SQL server.
It is important to note the proper time.
Patching passive node is the first. After
completing on the passive node, make it the
active node. If all services including SQL server
service and dependent services are up, then
apply the patches on the new passive
node(originally active node).
Failover Clustering
(http://www.microsoft.com/windowsserver2008/en/us/failover-clusteringprogram-overview.aspx)
Installing a SQL Server 2008 R2 Failover
Cluster(http://msdn.microsoft.com/en-us/library/ms179410.aspx)
Installing SQL Server 2008 on a Windows
Server 2008 Cluster(http://www.mssqltips.com/tip.asp?tip=1687)
rPath Linux(http://www.rpath.com/corp/)
Openfiler(http://www.openfiler.com/)