SQL Server 2012 Active/Active Cluster in Hyper-V

By Paul Brewer, 2012/02/13

  Rate this |   Join the discussion |   Briefcase |   Print

digg

inShare

출처 : http://www.mssqltips.com/tip.asp?tip=1687

Written By: Edwin Sarmiento -- 2/13/2009 -- read/post comments -- print --  

Rating:  Rate 

Problem
In a previous tip on SQL Server 2008 Installation Process, we have seen how different SQL Server 2008 installation is from its previous versions. Now, we have another challenge to face: installing SQL Server 2008 on a Windows Server 2008 Cluster. Windows Server 2008 has a lot of differences from its previous versions and one of them is the clustering feature. How do I go about building a clustered SQL Server 2008 running on Windows Server 2008?

Solution
There have been a lot of changes regarding clustering between Windows Server 2003 and Windows Server 2008. It took quite a lot of effort for us to build a cluster in Windows Server 2003 - from making sure that the server hardware for all nodes are cluster-compatible to creating resource groups. Microsoft has redefined clustering with Windows Server 2008, making it simpler and easier to implement. Now that both SQL Server 2008 and Windows Server 2008 are out in the market for quite some time, it would be a must to prepare ourselves to be able to setup and deploy a clustered environment running both. Installing SQL Server on a stand-alone server or member server in the domain is pretty straight-forward. Dealing with clustering is a totally different story. The goal of this series of tips is to be able to help DBAs who may be charged with installing SQL Server on a Windows Server 2008 cluster.

Prepare the cluster nodes

I will be working on a 2-node cluster throughout the series and you can extend it by adding nodes later on. You can do these steps on a physical hardware or a virtual environment. I opted to do this on a virtual environment running VMWare. To start with, download and install a copy of the evaluation version of Windows Server 2008 Enterprise Edition. This is pretty straight-forward and does not even require any product key or activation. Evaluation period runs for 60 days and can be extended up to 240 days so you have more than enough time to play around with it. Just make sure that you select at least the Enterprise Edition during the installation process and have at least 12GB of disk space for your local disks. This is to make sure you have enough space for both Windows Server 2008 and the binaries for SQL Server 2008. A key thing to note here is that you should already have a domain on which to join these servers and that both have at least 2 network cards - one for the public network and the other for the heartbeat. Although you can run a cluster with a single network card, it isn't recommend at all. I'll lay out the details of the network configuration as we go along. After the installation, my recommendation is to immediately install .NET Framework 3.5 with Service Pack 1 and Windows Installer 4.5 (the one for Windows Server 2008 x86 is named Windows6.0-KB942288-v2-x86.msu). These two are prerequisites for SQL Server 2008 and would speed up the installation process later on.

Carve out your shared disks

We had a lot of challenges in Windows Server 2003 when it comes to shared disks that we will use for our clusters. For one, the 2TB limit which has a lot to do with the master boot record (MBR) has been overcome by having the GUID Partition Table (GPT) support in Windows Server 2008. This allows you to have 16 Exabytes for a partition. Another has been the use of directly attached SCSI storage. This is no longer supported for Failover Clustering in Windows Server 2008. The only supported ones will be Serially Attached Storage (SAS), Fiber Channel and iSCSI. For this example, we will be using an iSCSI storage with the help of an iSCSI Software Initiator to connect to a software-based target. I am using StarWind's iSCSI SAN to emulate a disk image that my cluster will use as shared disks. In preparation for running SQL Server 2008 on this cluster, I recommend creating at least 4 disks - one for the quorum disk, one for MSDTC, one for the SQL Server system databases and one for the user databases. Your quorum and MSDTC disks can be as small as 1GB, although Microsoft TechNet specifies a 512MB minimum for the quorum disk. If you decide to use iSCSI as your shared storage in a production environment, a dedicated network should be used so as to isolate it from all other network traffic. This also means having a dedicated network card on your cluster nodes to access the iSCSI storage.

Present your shared disks to the cluster nodes

Windows Server 2008 comes with iSCSI Initiator software that enables connection of a Windows host to an external iSCSI storage array using network adapters. This differs from previous versions of Microsoft Windows where you need to download and install this software prior to connecting to an iSCSI storage. You can launch the tool from Administrative Tools and select iSCSI Initiator.

To connect to the iSCSI target:

1. In the iSCSI Initiator Properties page, click on the Discovery tab.

   

2. Under the Target Portals section, click on the Add Portal button.
3. In the Add Target Portal dialog, enter the DNS name or IP address of your iSCSI Target and click OK. If you are hosting the target on another Windows host as an image file, make sure that you have your Windows Firewall configured to enable inbound traffic to port 3260. Otherwise, this should be okay.

    

4. Back in the iSCSI Initiator Properties page, click on the Targets tab. You should see a list of the iSCSI Targets that we have defined earlier

    

5. Select one of the targets and click on the Log on button.
6. In the Log On to Target dialog, select the Automatically restore this connection when the computer starts checkbox. Click OK.

   

7. Once you are done, you should see the status of the target change to Connected. Repeat this process for all the target disks we initially created on both of the servers that will become nodes of your cluster.

Once the targets have been defined using the iSCSI Initiator tool, you can now bring the disks online, initialize them, and create new volumes using the Server Manager console. I won’t go into much detail on this process as it is similar to how we used to do it in Windows Server 2003, except for the new management console. After the disks have been initialized and volumes created, you can try logging in to the other server and verify that you can see the disks there as well. You can rescan the disks if they haven’t yet appeared.

Adding Windows Server 2008 Application Server Role

Since we will be installing SQL Server 2008 later on, we will have to add the Application Server role on both of the nodes. A server role is a program that allows Windows Server 2008 to perform a specific function for multiple clients within a network. To add the Application Server role,

1. Open the Server Manager console and select Roles.
2. Click the Add Roles link.  This will run the Add Roles Wizard

   

3. In the Select Server Roles dialog box, select the Application Server checkbox. This will prompt you to add features required for Application Server role. Click Next.

   

4. In the Application Server dialog box, click Next.

   

5. In the Select Role Services dialog box, select Incoming Remote Transactions and Outgoing Remote Transactions checkboxes. These options will be used by MSDTC. Click Next

   

6. In the Confirm Installation Selections dialog box, click Install. This will go thru the process of installing the Application Server role

   

7. In the Installation Results dialog box, click Close. This completes the installation of the Application Server role on the first node. You will have to repeat this process for the other server

We have now gone thru the process of creating the cluster at this point. In the next tip in this series, we will go thru the process of installing the Failover Cluster feature, validating the nodes that will become a part of the cluster and creating the cluster itself. And that is just on the Windows side. Once we manage to create a working Windows Server 2008 cluster, that's the only time we can proceed to install SQL Server 2008.

Next Steps

• Download and install an Evaluation copy of Windows Server 2008 for this tip
• Start working on building your test environment in preparation for building a SQL Server 2008 cluster on Windows Server 2008
• Read Part2, Part3 and Part4 

Readers Who Read This Tip Also Read

• Understanding why there is still downtime for SQL Server with Microsoft Clustering
• Installing SQL Server 2008 on a Windows Server 2008 Cluster Part 4
• Installing SQL Server 2008 on a Windows Server 2008 Cluster Part 3
• Installing SQL Server 2008 on a Windows Server 2008 Cluster Part 2
• Updating the Passive Node in a SQL Server 2005 Cluster
• More...

MySQL Clustering Config

Posted October 6, 2008
Filed under: Uncategorized | Tags: cluster, Clustering, MySQL |

I’ve had two positive comments to my previous post so I figured it was time to write an update regarding how my work has been going.

The MySQL clustered database is part of a large project I’ve been working on for the last 2 months. The basic server setup is two clustered JBoss 4.2.3Application Servers running on top of two clustered MySQL 5.0.67 servers. There is a 3rd Server which is a backup which currently only runs the MySQL manager ment console. I’ve noticed that even if there is high load on the 2 NDB data nodes, the Management console does not do much.

The four main servers run 2 Dual-Core AMD Opteron(tm) Processors with 8 gig of ram.

Even though alot of my work has been to rework my own code in order to optimize and improve it, alot of time has been spent looking for a configuration for the MySQL cluster that would cope with the load as I found that it was quite easy to kill the cluster in the beginning.

The config I am currently using is below and following are some results of the load testing I’ve been doing.

[TCP DEFAULT]

SendBufferMemory=2M

ReceiveBufferMemory=2M

[NDBD DEFAULT]

NoOfReplicas=2

# Avoid using the swap

LockPagesInMainMemory=1

#DataMemory (memory for records and ordered indexes)

DataMemory=3072M

#IndexMemory (memory for Primary key hash index and unique hash index)

IndexMemory=384M

#Redolog

# (2*DataMemory)/64MB which is the LogFileSize for 5.0

NoOfFragmentLogFiles=96

#RedoBuffer of 32M. If you get "out of redobuffer" then you can increase it but it

#more likely a result of slow disks.

RedoBuffer=32M

MaxNoOfTables=4096

MaxNoOfAttributes=24756

MaxNoOfOrderedIndexes=2048

MaxNoOfUniqueHashIndexes=512

MaxNoOfConcurrentOperations=1000000

TimeBetweenGlobalCheckpoints=1000

#the default value for TimeBetweenLocalCheckpoints is very good

TimeBetweenLocalCheckpoints=20

# The default of 1200 was too low for initial tests. But the code has been improved alot

# so 12000 may be too high now.

TransactionDeadlockDetectionTimeout=12000

DataDir=/var/lib/mysql-cluster

BackupDataDir=/var/lib/mysql-cluster/backup

[MYSQLD DEFAULT]

[NDB_MGMD DEFAULT]

# Section for the cluster management node

[NDB_MGMD]

# IP address of the management node (this system)

HostName=10.30.28.10

# Section for the storage nodes

[NDBD]

# IP address of the first storage node

HostName=10.30.28.11

[NDBD]

# IP address of the second storage node

HostName=10.30.28.12

# one [MYSQLD] per storage node

[MYSQLD]

[MYSQLD]

Here are some numbers of what I’ve been able to get out of the MySQL cluster. One iteration of my code results in:
12 selects
10 inserts
10 updates
4 deletes
This might sound like a lot, but it is a service oriented application that relies on persistent JBoss queues and demands 100% redundancy so even if the server dies, it will pickup where it died and does not loose any data.

My first benchmark was 10 000 iterations which is the current load I can expect on the application over the course of an hour i.e.
12 000 selects
10 000 inserts
10 000 updates
4 000 deletes
This took a total of 93 seconds producing just over 100 iterations per second.

The second test to really put the application and environment to the test was to run 100 000 iterations. This test completed in roughly 15 minutes producing around 110 iterations per second. This is about 10x the load we’d expect to see during the course of one hour but it is nice to see the setup has the ability to grow quite a bit before we need more hardware. 

I am currently working on setting up a third test which will run 100 000 iterations every hour for 10 hours producing 1 millions rows of data.

Possibly related posts: (automatically generated)

·         I’m half way to being MySQL certified

·         Oracle buying Sun, therefore adquiring MySQL!!!!!

·         Tips & Tricks from MySQL Experts

 출처 : http://bieg.wordpress.com/2008/10/06/mysql-clustering-config/