Wednesday, March 24, 2010
The Controller
Now we get to the meat of the matter. You want a virtual cluster, the first thing you have to do is create your own portable domain. Start with a plain vanilla install of Windows 2003 R2 Standard on a semi-default VM. (1 GB RAM, 2 cores, 2 NICs, 128GB dynamically expanding VHD file). I chose this because it had the smallest disk and memory footprint of any current supported Microsoft Server product. I created the VM with a single dynamically expanding VHD, one fixed 16 GB VHD, and two NICs. One NIC is connected to the outside world and the other one is part of an internal-only network. The first NIC is set up as a DHCP client. We will get to the other one later.
I actually tried this with Windows 2008 R2, but it failed miserably. Not sure whether it was 2008 R2 or the fact I tried to use cloned VMs in the cluster. Clustering is one place where NewSID would really come in handy. Too bad Microsoft bought and buried it.
Load and Patch the OS (hence the need for the outside connection).This is a good time to go get dinner. Maybe a movie too. There are close to a hundred patches that need to be downloaded and applied. Avoiding that mess was why I put so much time into trying to get the 2008 R2 version working. Maybe next time. Don’t forget to add the extensions for VMLite (or whatever virtualization product you prefer).
Set a fixed IP address on the internal-only NIC. Do not give it a gateway. Put the same IP address for the NIC and for the DNS Server. This IP should be in a range that is never available on your public network. You will need all the addresses in the range available. See the previous post for the exact settings I used.
I chose 10.97.230.1 as the server. The rest of the 10.97.230 range is what I will use later. For the curious, those numbers are based on elements of my home address. Not truly random, but good enough for this project.
Do not bridge the network connections. I never allowed the cluster nodes direct access to any public network.
Format the fixed VHD and leave it alone for now.
Promote the VM to a Domain Controller. If you have never done this, don’t worry. The only meaningful decision is what to call the new domain. I prefer a bogus name that does not correspond to a real Top-Level Domain (TLD). .com, .biz., .net, .org are all TLDs that we know and love. I chose .test as the TLD since it is descriptive AND it does not exist in the real world. The domain is called MicroAD. This gives me MicroAD.Test as my domain.
During the promotion process, you will be prompted to install DNS as part of the Domain creation process. You want to accept this option. The installer will automatically assign this DNS server as the authoritative owner of the MicroAD.test DNS domain (not to be confused with the MicroAD.test Active Directory domain.)
For the rest of the DCPROMO process, just accept the defaults.
Now let’s make our IP address management easy. Add the DHCP Role to the server. Add the server (10.97.230.1 in this case) as the default gateway to assign to DHCP clients. Here is where you have to be VERY careful and bind it ONLY to the Internal NIC. Trust me, your network admin will NOT like an extra DHCP server “helping” out on her network. Go ahead and create a range of 10-20 IP Addresses in your scope. You might find other uses for a pocket domain controller <cough> Mirroring </cough> than just for building a cluster. And Clustering in SQL 2008 and Windows 2008 R2 fully supports DHCP addresses.
Now we have three of the five key roles ready. Two more to go.
Next comes file sharing. Since your cluster node VMs will not have access to any outside, you have to have some way to get files into these VMs. I simply go to the root of C: and create a “Shared” folder. I then share it out and grant full control to “Everyone” to both the share and to the underlying NTFS folder. This will be immensely useful for Service Packs, demo databases, and any other software that isn’t packaged as an ISO that we can mount to the VM.
Finally we need to create a block-level multi-connect storage device. The kind folks at Starwinds Software (http://www.starwindsoftware.com/) graciously gave me a non-expiring demo license for expressly this purpose. Their iSCSI SAN software lets you create an iSCSI target from nearly any storage medium. Refreshingly, their product does exactly what they say it does. Thanks.
Remember that 16 GB VHD file? That is where we are going to carve into our LUNs. I created an iSCSI folder off the root, just so I can keep everything organized. I then carved 5 ea. 2 GB iSCSI targets from that folder. I chose a fixed VHD for performance. I tried this earlier with a dynamically expanding VHD, but too many layers of abstraction and sparseness combined to make it unusable even for a demo. Stick with a fixed VHD so there is a one-to-one mapping between abstract and physical storage. If you read the previous post, you know what I named these iSCSI LUNs and why.
Yes, I do have some left over space. Always leave yourself room for future growth or options.
This gets us up to where we can actually build the nodes and install SQL. As with most clusters, the real work happens long before the individual nodes get installed and configured. At least it does if you want the cluster to be a true high-availability platform.
Tuesday, March 09, 2010
Along with a lot of my colleagues, I went to SQLSaturday #33 in Charlotte this last weekend. Overall a really good event, especially for a first-time organizer. There is some controversy over certain events where my name got mentioned so I thought I would clear the air.
Before I get to the core controversy, let's get the details out of the way.
The Microsoft Offices in Charlotte were an excellent venue for this event. I really appreciated the Microsoft employees that helped out by letting us in and out of normally secure areas. This is definitely above and beyond on their part.
Thanks to the organizers (especially Greg and Peter) for the great hospitality they showed to the speakers.
Now for the specifics. Like most events of this type, there was a raffle at the end for some cool swag. As a speaker I got raffle tickets just like any other attendee. The raffle was clearly promoted as "must be present to win". The problem is that for various reasons, the raffle kicked off immediately after the last speaker finished in the largest room. That room was across the parking lot from all the other rooms for the event. I happened to have one of the last sessions of the day, and not in the main room. I also ran long since the audience was very interactive and there were a lot of follow-up questions. (BTW, thanks to everyone who came and stayed for my session. Sorry it cost you the chance to win too.). My name was drawn for an very nice piece of swag (iPod Touch if you insist). Since I wasn't there, I didn't win. Several folks mentioned I was still speaking and was "here" (as in at the event) just not "here in the room".
Yes, I was mad when I found out about it. I think that was handled poorly. I personally lost out as did my audience (dunno if anyone specific lost anything, but it is the idea that counts). It was a mistake.
Mistakes happen. Nobody acted maliciously. Heck, the guys running the event who made the decision are my friends and remain so. I got over my mad. We talked about this privately and we are all OK with what happened. I am not going to let a gadget get in the way of a couple of good friendships.
I think the mistake was mostly due to a lack of unity between the venue buildings Pam Shaw had a similar challenge in Tampa a few weeks ago, including a speaker who ran long on the last session (not me that time). She had a couple of teenage volunteers to act as gofers/runners. They counted heads in sessions, pointed people to last-minute room and session changes, and generally helped connect the organizers to what was actually happening. Note that this was not Pam's first SQLSaturday event. She knew but the knowledge had not been institutionalized. We (The SQL community in general and SQLSaturday organizers in particular) now know how essential gofers are to success.
I know I spent most of this post focusing on the controversy, but I wanted to clear everything up. I don't want to let a minor mistake, made in good faith, overshadow what was a tremendously good event for the community.
As for the iPod Touch, someone in the SQL community is enjoying it, so it is not a total loss. And if losing out on it is the price I pay so we can learn this, then that is what a community leader does. Consider it a gift. Besides, I really wanted a Zune 120 :)
Thursday, February 25, 2010
Denny Cherry tagged me to write about my best MacGyver Moment. Usually I ignore blogosphere fluff and just use this space to write what I think is important. However, #MVP10 just ended and I have a stronger sense of community. Besides, where else would I mention my second best Macgyver moment was making a BIOS jumper out of a soda can. Aluminum is conductive and I didn't have any real jumpers lying around.
My best moment is probably my entire home computer network.
Every system but one is hand-built, usually cobbled together out of spare parts and 'adapted' from its original purpose.
My Primary Domain Controller is a Dell 2300. The Service Tag indicates it was shipped to the original owner in 1999. Box has a PERC/1 RAID controller. I acquired this from a previous employer for $50. It runs Windows Server 2003 Enterprise Edition. Does DNS, DHCP, and RADIUS services as a bonus. RADIUS authentication is used for VPN and Wireless access. It is nice to sign in once and be done with it.
The Secondary Domain Controller is an old desktop. Dual P-III 933 with some extra drives.
My VPN box is a P-II 250 with 384MB of RAM and a 21 GB hard drive. I did a P-to-V to my Hyper-V box a year or so ago and retired the hardware again. Dynamic DNS lets me connect no matter how often Comcast shuffles my IP.
The Hyper-V box is a desktop system with 8GB RAM and an AMD Athlon 5000+ processor. Cost me less than $500 to put together nearly two years ago. I reasoned that if Vista and Windows 2008 were the same code then Vista 64-bit certified meant the drivers for Vista would load into Windows 2008. Turns out I was right.
Later I added three 1TB drives but wasn't too happy with how that turned out. I recovered two of the drives yesterday and am building an iSCSI storage unit. (Much thanks to Starwind. Great product). I am using an old AMD 1.1GhZ box with 1.5 GB RAM (cobbled together from three old PCs) as my storave server.
The Hyper-V box is slated for an OS rebuild to 2008 R2 once I get the storage system worked out. maybe in a week or two.
A couple of DLink Gigabit switches ties everything together.
Add in the Vonage box, the three PCs, the Wireless-N Access Point, the two notebooks and the XBox and you have gone from MacGyver to darn near Rube Goldberg.
The only thing I really spend money on is power supplies and fans. I buy top-of-the-line for both.
I even pull and crimp my own cables.
Oh, and if my kids hose up a PC, I have all of their data on a server elsewhere. Every PC and laptop is pretty much interchangable for email and basic workstation tasks. That helps a lot too.
Of course I will tag SQLVariant.
Thursday, February 18, 2010
Part 2 - Where I plan my Cluster.
Tuesday, January 26, 2010
This past Saturday I enjoyed the hospitality of the Tampa SQLSaturday (#32) team. My presentation was “Clustering for Mere Mortals”. Unlike many clustering presentations, I had demonstration content. This resulted in the most asked question being “Do you have instructions on how you built that?” This is the first part of those instructions.
Here is what the cluster consists of (Virtually):
One Windows 2003 R2 (x64) Server acting as Domain Controller, DNS Server, DHCP Server, File Server and iSCSI Target Server
Two Windows 2008 R2 Servers as cluster Nodes.
All this runs on a Lenovo W500 laptop with an Intel Core 2 Duo T9600 processor, 8 GB RAM and a 320 GB Internal Hard disk running Windows 7 Enterprise (x64). The total disk footprint is slightly under 50 GB for all virtual guests.
One of the toughest decisions was choosing a virtualization platform. I didn’t want to run Windows Server 2008 R2 on my notebook since that took away Hibernation and Sleep modes, plus getting the drivers for WiFi, Bluetooth, etc. working is a real pain. VMWare was out since I wanted this to run Microsoft VHDs I downloaded for training as well as my new demo cluster. Virtual PC was out since I needed 64-bit guest capability to run Windows 2008 R2 as a guest. I found a winning candidate in VMLite. VMLite is a Windows implementation of Sun’s VirtualBox platform. It supports VDI, VMDK and VHD disk formats as well as 64-bit guests. Incidentally, it is also free.
Based on prior experience with the above virtualization products, I chose to create a VM folder off of the root of my C: drive. Below that I created a Machines folder and a HardDrives folder. Set I then these as default locations in VMLite. I also copied the VMLiteGuestAdditions.iso file to this location so it was easy to find later.
Like most Sun products (cough Java cough) it is semi-hostile to Windows users. Still, this was the option with the lowest suck factor so I started building. VMLIte does have a few quirks you have to learn to work around. VMLite uses a UUID to identify virtual disk files to its Virtual Media Manager. Evidently unique file names and paths are not adequate, probably because XML isn’t involved. So if you copy a VHD file it will not allow you to “register” it with the Virtual Media Manager without a “mother may I?” step. Many of the VHDs downloaded from Microsoft also have identical UUIDs, even ones for different training classes. Common base OS image is the most likely explanation. Anyway, to fix this is actually pretty simple
Open a command prompt
Change directories to the VMLite executable folder (C:\Program Files\VMLite\VMLite Workstation on my system). Look for the VBoxManage.exe file.
Type: VBoxManage InternalCommands SetUUID <path\Filename to VHD> Note: Do not put quotes around the path and filename.
Using this technique you can now add any VHD file to the Virtual Media Manager tool in VMLite .
Interestingly enough, VMLite will not create a VHD file when you define a new Virtual Machine. It natively will create a VDI (Sun Virtual Device) file. You can use the Windows 7 Disk Management tool to create a VHD file with the correct characteristics and then detach it from the Host OS for use in a VM.
There are some other unexpected behaviors of VMLite (remember I did mention user-hostile?) but no show-stoppers. Finding them will be left as an exercise for the student. J
Start building OS base images and generally working in VMLite to get familiar with it. At this point, you should be able to create new VMs, import existing VHDs, and generally do basic functions and navigation within VMLite. We still have a ways to go to build a cluster, but we have a solid foundation to start on.
Thursday, December 03, 2009
I just got this question from a blog reader:
We have a SQL 2005 cluster active/passive. Under my understanding, it would be neccesary to apply patches into a Testing environment, if working fine, proceed to the production environment... but, is it recommended to patch both nodes the same day? or instead it would be better to patch Node1 (active) and wait some days before patching Node2 (passive)?
I would like to know your opinion on this matter !
Thanks a lot for your support.
The answer is you always must patch all nodes at once using SQL 2005. Rolling upgrades/patches was introduced in SQL 2008 The patch process for SQL 2005 and lower is disruptive in that it takes the entire instance offline on all nodes to apply binary and internal patches. Very risky and a major reason you test this on a practice cluster first. When you apply the patch to the test cluster, you are not only testing the patch itself, but your procedures for applying and removing it if necessary.
Hope this helps.
Tuesday, November 10, 2009
The 2009 PASS community summit was a fantastic week. There is something about Seattle (rain) that makes this conference special(hills). Only the top folks get to present and they bring their best game. Learning here really is a lot like taking a drink from a fire hose, except you always want more.
The best part of the Summit is being in a group of fellow database professionals. Folks who understand a SELECT joke. Let's face it, most of us DBAs work alone or nearly so. Only a few large organizations have more than one or two of us on the payroll. Being surrounded by a couple of thousand geeks with the same technical specialization is very unusual for us. And we like it.
So if you did go, be sure and get the DVD set. If not, then start making plans for next year. If your company won't pay, consider it an investment in yourself. And don't forget the pre- and post-conference sessions. Those are some of the best deep-dive training classes around.
Once you go, you get hooked. Content, networking, and just the experience of the PASS Summit are some of the best parts of this profession. Come on and join us, won't you?
Tuesday, October 20, 2009
Long time, No see. This space has been a bit quiet and I apologize for that. Seems I have been a bit busy getting out and earning a living. Tough times all over.
Anyway, the 2009 PASS (www.sqlpass.org) board elections just closed. There has been a lot of controversy over this election with a non-SQL professional seeking a board member position. Pro or con, I am not getting into that argument. I see merits on both sides.. I will say that the nominating committee failed the PASS membership badly. The failure was not in allowing this person to run. On the contrary, I think a controversial candidate is great since it lets the members decide exactly where the organization should go.. The failure was in limiting the slate to just four candidates.for three open positions. I do recall from history and civics classes that some political systems have the exact number of candidates as offices, but I don't remember the exact term for such a system. "Democracy" certainly isn't the right term. Community organizations should have leaders chosen by the community. This is not the case with this "election".
Note that I am not criticizing the quality of the candidates selected. Some of them I know personally and consider friends. Others are professional colleagues that I know of and respect. The problem is four people and three positions is a final round of musical chairs, not a board election.
Since I am a problem solver by nature (If you are not, consider a career change from DBA. You won't like it and you won't do well) I will propose a solution:
The nominating committee function should simply be to properly vet the candidates. It should establish that whatever claims for personal and professional achievements a candidate claims are factually correct. It should establish minimum criteria for candidates and publish those qualifications. Those qualifications should be things like being a full-up member of the organization AND a local chapter for at least three years and attended at least two prior PASS summits (European ones count). Maybe some level of volunteer community commitment, but I think that gets us too close to the weeds in measuring just how different volunteer efforts should be compared. Simple, basic, fundamental requirements.
Then let the members decide.
After all, this is a community organization. Isn't it?
Thursday, March 19, 2009
Anyone who has spent time in the computer business and has some grasp of hardware issues understands fragmentation. For those whose concept of hardware is limited to “it’s the part I can kick”, here is a quick and painless overview.
I like analogies so let’s think of a disk drive as a very large, tall building having many rooms and many floors. The disk head is you. Your task is to run around and “read” something from particular rooms (blocks). A simple thought exercise would suggest it is easier to read rooms “in order” on a floor rather than scattered around the building. You can “read” all the rooms on a hallway far faster than the same number of rooms on different floors or wings. Hence the difference between “sequential” and “random” I/O. Blocks or rooms, it all works the same. One trick used when benchmarking systems is to partition only part of each disk and assign that to a RAID set. This causes the heads to “short-stroke”, leading to faster seek times. This would be like only using the first ten floors of a large building so the elevator only has to move a short distance. Inefficient in space, but faster for throughput.
Fragmentation comes in two forms in SQL; internal and external. Internal fragmentation occurs when the various SQL data structures are not in order within a particular SQL data file. External fragmentation occurs when the SQL data file itself gets scattered in multiple sections over the physical disk. Internal fragmentation can be corrected by reindexing the database, something most of us have running automagically. External fragmentation is not so easy to fix. You have to defragment the disk with SQL Server offline so the data files sections can be moved around and strung back together. However, you can avoid External fragmentation very easily by following a few simple rules. Note that the issues for SQL Log files are a bit different but the same rules apply.
Rules to Avoid External Fragmentation:
1) Never Use Auto-Expand for data files. Note that I do not say disable auto-expand, just never actually use it. Pre-expand your databases in large increments to handle several months’ worth of growth at a time. Leave Auto-Growth as a “safety valve” to keep unexpected allocations from killing your server, but don’t rely on it to manage data file space allocations.
2) Never Shrink data files. Shrinking inevitable leads to later growth. Allocate once and leave it. Shrink also causes massive Internal fragmentation in a SQL database. The shrink algorithm moves data to new locations but does not attempt to keep data in contiguous segments. Auto-Shrink lets you do this on a regularly scheduled basis. How helpful.
3) Keep SQL disks dedicated to SQL Data only. Creating and optionally destroying many data files causes physical fragmentation on the disks. To go back to the building analogy, most disks allocate new from new. That is instead of reusing old rooms freed up from deleted files, the file system simply grabs the next unused room (or rooms) after the last used one and keep moving. This is nice until the first pass through the building is complete and stuff is all over the place.
Fragmentation brings us back to a fundamental dichotomy in SQL Server management. You can optimize for low space utilization or high performance but not both. Considering disk space is relatively cheap, I know which one I always go for.
Thursday, January 01, 2009
Looks like the Great Zune Massacre of 2008 was a day 366 issue. Again, someone forgot to throw out the code from the lowest ten percent of the Stack-Rank system. Sorry to sound harsh, but this is type of thing will flunk you out of Programming 101. I don't even want to get started on the QA failure, but it is even worse.
Semi-Kudos to the Zune Team. There was a notice on the support page shortly after the original post acknowledging the problem and indicating they were working on it. Microsoft also got word out through its MSNBC subsidiary as a news item, linked to the support page. I still say the warning should have been more prominent and on the main page, not the support page, but that is relatively minor.
My Zune is recharging and should be OK.