This time around I’ve for four sessions. Three that I’ve put together, and one that is a group pannel.
“Storage for the DBA” is a session I’ve given a few times at SoCal Code Camp where we go into the basics of storage, and how it relates to SQL Server.
“There’s more to know about storage?” is a followup to the “Storage for the DBA” session where we will go into the design techniques that the various storage vendors have used to create their respective platforms. From there we’ll move into some of the more advanced features you can use with storage arrays that turn the from the large JBOD that most people think of them into advanced storage devices worth every penny that they cost.
In the DACPAC session we’ll look at this new feature called Data Tier Applications to see how they work, when they should be used, and when they shouldn’t be used.
The panel discussion will have a large number of SQL Server professionals from the Southern California area including myself, Andrew Karcher (Blog | Twitter), Lynn Langit (Blog | Twitter), Bret Stateham (Blog | Twitter), and several more all there to answer your questions about SQL Server.
I hope to see you there at Code Camp.
It occurred to me that I haven’t ever posted the slide decks for the Portland SQL Saturday. Sorry about that.
With the release of Office 2010 uses of Blackberrys have a new problem to deal with. The blackberry desktop manager software which you install on your computer is only a 32bit application, so if you install the 64bit version of Outlook (or Office) then the Blackberry desktop manager can’t talk to Outlook in order to get your Exchange settings so that it can talk to the Exchange server and configure your Wireless sync.
Now the official fix from RIM is to downgrade to Office 2007 or older and wait for them to get around to releasing a new version of the desktop manager (my phrasing, not theirs).
If you have Windows 7 then you have a pretty easy to configure solution available to you. That solution is Windows XP Mode. Download Windows XP Mode from Microsoft and install it. This will give you a 32bit VM to work with. Install Outlook 2007 on the VM (if you have an MSDN or TechNet account you can download it from there) and then install the Blackberry software on the VM.
After you have everything installed, connect the phone to your computer. In the Windows XP Mode VPC click on the USB drop down menu and attach the RIM device to the VM. Fire up Outlook and configure it to connect to your Exchange server using your normal account, then fire up the blackberry software and configure it like normal.
I just got done doing this on my machine, and it works great.
Now if you are using Windows Vista this will be a little harder. The Virtual PC that works with Windows Vista doesn’t support USB, however VMware Workstation does support USB, but VMware Workstation has to be purchased. The same basic technique can then be used, but you’ll need to install the OS instead of just downloading an already configured OS.
Now because you are running within a VM everything will be a bit slower, but it’ll let you get the job done.
Managing the space used by your SQL Server transaction logs is pretty easy.
If the database is in full recovery mode then do transaction log backups on a regular basis. Typically every 15-60 minutes.
The second option is to put the database into Simple recovery mode. If you need the ability to do point in time recovery then you can’t use simple recovery mode at all.
Now there is something that you shouldn’t ever do. Truncating the log and shrinking the file is the first thing that shouldn’t ever be done (contrary to popular opinion).
If you are doing log backups, and your database log file is growing to a large size, then you need to get more space for your disk. Shrinking the log won’t do anything for you as the SQL Server will simply grow the file back out to a larger size. All you are doing is giving yourself a false sense of security when it comes to how much free drive space that you have.
There are also some internal technical reasons to not do this as well which you can read a little about on Paul Randal’s blog. (If Paul tells me he’s got a more specific post I’ll post that link as well.)
So a while back I got a new laptop and also upgraded by desktop from Windows 7 32bit to Windows 7 64bit. Everything worked great, but there was one thing which took me some time to get working correctly. That one thing was the EMC Navisphere software which I use to manage our EMC Storage Array.
I’m sure the first question is “Denny, how hard can it be to install some management software?”. Well first tough guy, you don’t install Navisphere, it is a Java applet which is launched via accessing the web server which runs on the storage array. You feeling less smug? Good. Now lets move on.
On both my machines use FireFox as my primary browser. On both machines I accessed the array which triggered the download and installation of Java. So far so good, right? The Java applet never fully launched. I was greeted by a blank window within the browser instead of the login screen that I was expecting. After Navisphere didn’t do anything useful I tried opening it in Internet Explorer both 32bit and 64bit. Neither one worked and they simply gave me the same blank screen.
I tried uninstalling Java and manually downloading it and installing the same version as well as a newer version with the same absolutely useless results.
After trying this a few times, I finely uninstalled Java yet again (I’ve noticed about the only thing which Java does quickly is uninstall) I installed Java automatically via my Internet Explorer 64bit with the same results yet again.
This time I opened the Java control panel and tried to manually upgrade Java. This threw an error. So I tried opening a command prompt with administrative rights and navigated to the Java folder (C:\Program Files (x86)\Java\jre1.5.0_06\bin) and ran jucheck.exe which causes Java to check for a new upgrade. I then got the magical little icon in my system tray which said that Java had an upgrade available. So I upgraded Java and magically Navisphere started working.
I don’t know why the auto upgrade wasn’t working correctly but now that it’s been upgraded everything it working and I’m able to manage the storage array, which is kind of important.
Hopefully by now everyone knows that they should be aligning your disks. Now when you use a Storage Array to present the LUNs to the server you may have the option of offsetting the LUN so that you don’t need to offset the disk. If you don’t plan on using any of the advanced clone or snapshot features within the array then this will be fine to do.
However if you plan on taking a clone, or snapshots, or using some sort of array to array based replication this can cause you some performance problems. The reason for this is that when you offset on the array, it simply creates the first block of the LUN at the 32nd block instead of the first block. This works great for a single LUN. However if you decide to take a clone of the LUN, the clone won’t be aligned as the clone will be copied starting at the first block. If the volume is aligned at the server level, the alignment will be copied to the clone and if the clone is mounted everything will respond as expected. However with the alignment set at the array when you mount the clone performance will not work as expected.
P.S. This is based on an EMC CX array. Different arrays will work differently.
If you are using an EMC CX storage array, and you plan on using MirrorView to replicate data between two storage arrays there are a few catches which you need to keep in mind that aren’t all that obvious unless you read through literally hundreds of pages of documentation.
When you go to use MirrorView the ports which you will be using have been defined already, and can not be changed without resetting the entire storage array. Now depending on what hardware is installed when the array is first powered up this will determine which port you can use for MirrorView.
If you have the base cards which come with the system, then FC0 will be the port which is used for MirrorView. If you have an expansion fiber card installed in the system, then port FC4 (the port labeled 0 on the expansion card) is the port which will be used. Now, the catch here is that FC0 will be a 4 Gig port. Currently with the newest CX4 arrays you can’t change the cards which the system ships with from 4 gig to 8 gig ports. So if after time you find that you need more bandwidth between the arrays for Mirrorview, pretty much your only option is to move your hosts off of the FC0 port. If the only thing using FC0 is MirrorView and you still need more bandwidth you’ll need to contact EMC support and work with them to come up with a solution short of resetting the array to the same state you received it in.
Now the same applied to the iSCSI MirrorView port. If you get the array with no expansion cards, the iSCSI port with be port 0. If you get the array with an iSCSI expansion card installed on first power up then iSCSI port 3 (the port labeled 0 on the expansion card) will be your iSCSI MirrorView port.
Now another catch that you may not be aware of, MirrorView is SP specific. So if your LUN is hosted by SPA which you setup the MirrorView replication it has to go to SPA on the remote array. Now if your LUN trespasses to the other SP, the MirrorView replication will stop until the LUN moves back to the hosting SP. If you need to move the LUN to the other SP, then you’ll need to remove MirrorView from the LUN and reset it back up.
One of the big questions out there is how do I know if I should use RAID 5, or RAID 10, or something else?
The answer is usually something abstract like “if you have a lot of writes then use RAID 10, otherwise use RAID 5”. We’ll I’ve finely gotten some numbers from someone. These numbers are all unofficial and your mileage may vary.
On a typical RAID array (JBOD, DAS, etc) if your disk will have a higher than 10% change rate then you’ll want to look at a RAID 10 array. Now if you are using an EMC array (keep in mind I got these numbers from Dell/EMC) then you’ve got more leeway. The recommended number to stay below on an EMC CX line of arrays is 30%. So if your data change is less than 30% you should be OK on a RAID 5 array, higher than that and you’ll want to move onto a RAID 10 array.
Now if you want the extra read performance that RAID 5 gives you, but you want more redundancy than RAID 5, take a look at RAID 6. It is only slightly more expensive per Gig (especially when working on larger RAID arrays as their are more disks) as you have double parity. While there is a little more overhead for RAID 6 over RAID 5, this additional overhead is typically only an extra 2% to use RAID 6.
Now obviously these numbers are for when the RAID Array is running at full capacity. The lower the load you are putting on the RAID Array, the higher the percentage of changes you can use and still safely use a RAID 5 (or RAID 6) array.
So you’re going along with your work and you need to add a new server to your fiber channel switches. However you don’t have any more ports left on the switch. You’ve got two options, buy two new switches and link each pair of switches (first existing switch to one new switch, second existing switch to the other new switch). So logic tells you that you’ve got 4 Gig ports on the switch, and 4 gig cables, so if you plug one cable between the two switches you’ll get 4 Gigs of bandwidth between the switches right?
Not so much. When you ISL the switches together (that’s the fancy technical term for connecting two fiber channel switches in a single fabric) you only get 50% of the bandwidth through the cable. So in order to get the full 4 Gigs of bandwidth you’ll need to string two cables between each pair of switches. You’ll see more clearly in the diagram below.
You’ll see in the diagram that Existing Switch 1 connects to both sides of the storage array, and it connects to New Switch 1 as well. Existing Switch 2 also connects to both sides of the storage array as well as to New Switch 2. You’ll see that in the diagram you don’t connect Existing Switch 1 to New Switch 2 or visa verse.
Now if you have a sever which will need more than 4 Gigs of bandwidth that will be plugged into one of the new switches you’ll want to connect more than two cables between the switches so that you can get the full 8 Gigs of possible bandwidth between the server and the storage array.
Like Ethernet switches you can bond these ports between the switches together into a single virtual switch via the built in trunking options within the switch. As each switch vendor has a different way to do this (and those methods vary depending on the OS running on the switch) I won’t do into the specifics here, but your support desk with your vendor should be able to provide you with support in doing this.
One last thing to keep in mind when ISLing these switching together is that some vendors has a license fee to enable the ISL features so keep that in mind when doing your planning.
P.S. This diagram is based on an EMC CX3 Storage Array, but it is perfectly valid for any dual head fiber channel storage array.