So you’ve got a bunch of machines that you want to run a quick query against. SQL Server 2008’s Management Studio gives you a quick and easy. Open up the Registered Servers in Management Studio and select a group of servers. Then right click on the group and click on the “New Query” option.
This will open a new query window where you can run a query against all the servers that are online in that group. In my sample query shown below you’ll see that I ran SELECT @@VERSION against all the servers. When I ran this 3 of the 6 servers in the group were online so three servers were able to return data.
Now if you look at the messages tab (look down) you’ll see which servers the query ran against, and which servers it failed against. It also tells you what accounts the query was run using (based on the connection info for each server).
Now you can’t merge data together in a temp table as everything in the query window will be run against each server. It simply displays the information together. You can pull a single value like I showed above, or you can query a table.
Now when querying from a tables on multiple servers you’ll need to make sure that the schema for those tables are identical otherwise it will only return the data for the first table that it queries. An error will be shown on the messages tab telling that you that the schemas don’t match if this is the problem.
So a couple of weeks ago Arnie Rowland started a project to give away a couple of MSDN licenses and some other goodies to some deserving out of work developers. The big requirement being that they do something worthy of getting the stuff. Continued »
On Monday August 9th, 2010 at the Microsoft Office in Irvine, CA I’ll be leading up an all day “Storage and Virtualization for the DBA” training session. In addition to a great day of training, one lucky attendee will walk away with a brand new XBox 360. There are lots of other great giveaways.
You must sign up to attend, and seats are limited. Be sure to sign up soon as seats are given away on a first come first served basis.
If you don’t know what KMS is, it’s the nightmare that Microsoft has unleashed upon the Windows Admins of the world since the release of Windows 2008 R1. In the good old days you stuck the Windows license key on the server and it connected to Microsoft to validate the license. Apparently that wasn’t good enough for Microsoft so you now have to have a Key Management Service (KMS) running on a server on your network.
In theory getting KMS working is a piece of cake. You just install windows on a server, put the KMS key in the machine as the license key and move on.
In the real world there is a little more to it.
For each different OS that you have to need to add the license key to the KMS server. Now you’d think that there would be a nice interface that shows you your licenses, how many you’ve used etc right? Yeah, not so much. Key management is done completely via a VB script which sits in the Windows\system32 folder.
So to install the new license key you have to run slmgr.vbs with the -ipk switch. Then run it again with the -ato switch so that you can activate the license.
cscript slmgr.vbs -ipk XXXXX-XXXXX-XXXXX-XXXXX-XXXXX
cscript slmgr.vbs -ato
Now if the license activates you are good to go, however if it doesn’t you have a problem you now need to get the info from the system so that you can call Microsoft. This requires using the -dlv parameter so that you can get the Activation Id from the application.
cscript slmgr.vbs -dlv
Then use the -dti parameter and the Activation Id that you just got to get the Installation Id. This is the number that the phone system will want from you to get the license activated. Check the phone.inf file to get your local phone number.
cscript slmgr.vbs -dti xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
Now the Microsoft phone system will give you back a bunch of numbers. Note them without dashes in a notepad doc. Then use slmgr.vbs with the -atp parameter passing it the value you get back from Microsoft and the Activation Id that you got before.
cscript slmgr.vbs -atp 000000000000000000000000000000000000000000000000 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
At this point, unless you’ve fubar’ed any of these parameters your license will be activated.
Now if you’ve been using VMware to deploy templates of Windows you’ve probably got Customization Specifications setup. And you’ve probably got either your MAK or KMS keys entered in there. Both of those would be the wrong keys to use. You should be using the keys listed on this web page as these are the keys that tell Windows that it is a KMS client and that it should connect to your local KMS server.
Now if you’ve put your KMS key into all of your virtual machines as the key, you need to clean up your DNS as well. Within your internal DNS servers under your domain then _tcp subdomain you’ll see a bunch of domains under _VLMCS. You’ll want to make sure that you only have SRV records with this name pointing to your actual license server or servers.
On the machines which you have already deployed using your KMS keys, you’ll need to reset the keys on those machines. This can be done with the following commands, just put in the correct key from the page listed above.
cscript c:\windows\system32\slmgr.vbs -upk
cscript c:\windows\system32\slmgr.vbs -ipk PutYourKeyHere
cscript c:\windows\system32\slmgr.vbs -ato
This will remove the current license key, add the KMS client key, then register the computer with the local KMS server.
Now isn’t that easy?
UPDATE 2010.07.27: Corrected the scripts from csharp to cscript.
Register for Quest’s virtual SQL Server training conference. Sign up today to get free education-right from the convenience of your desk.
SQL Server experts Kevin Kline, Brent Ozar and Microsoft insider Buck Woody, will give you tips and tricks to take the pain out of tuning performance for your databases. You’ll walk away with mad skills and unique insight that will make your job easier than you ever imagined.
Event: Virtual Training for SQL Server – Performance Monitor and Wait Events
Date: Wednesday, July 21, 2010
Time: 7:45 a.m. Eastern (4:45 a.m. Pacific/ 12:45 p.m. United Kingdom/ 1:45 p.m. Central Europe)
Duration: Eight sessions to choose from, each 45-60 minutes
Can’t make it to the live training day? Be sure to register and you’ll be eligible to view the sessions on-demand for 30 days.
P.S. If you can’t make it to the actual event, the conference will be available on demand until August 21.
So on Monday August 9th, 2010 I’ll be giving a preview presentation of my Storage and Virtualization for the DBA precon. Basically I want to give the presentation in front of a live audience before I go to PASS and have people pay to see it. So if you are going to be in the Irvine area (or if you can be in the Irvine area) on August 9th, you’ve got a shot to see it for free.
Now you’ll have to turn in a survey at the end, but I’ve got some incentive for that. Everyone who submits a survey will be entered into a drawing for some great prizes.
So far the prizes include:
1. A copy of “Professional SQL Server 2008 Internals and Troubleshooting” by Christian Bolton, Justin Langford, Brent Ozar, James Rowland-Jones, Jonathan Kehayias, Cindy Gross, Steven Wort
2. One of two copies of Windows 7 Ultimate x86.
There are only 70 seats available so be sure to sign up if you want to attend. The full details are available via the sign up link.
A special thanks to Microsoft for offering to host this event for me so we can have this event for free.
The jist of the project is
To recap, we are inviting unemployed or underemployed developers to propose a software project for a non-profit agency, school, or church. The idea is that we will provide a package of the latest software, tools, and training resources to help you improve your skills, get up to date with current technologies, gain practical experience, potentially earn a recommendation for your efforts, and in general, enjoy the feeling of accomplishing something useful for others. We are not giving out a ‘free lunch’, just supporting your efforts to personally gain from your own ‘sweat equity’.
The selected project will receive:
O’Reilly Press: three (3) books of the winner’s choice,
APress: three books(3) of the winner’s choice,
Please submit your project information via Google Docs.
The selection for July will occur on July 30, 2010 so make sure that you’ve got your project submitted before then.
This months T/SQL Tuesday topic is about learning and teaching. It’s been a while since I’ve written one of these, so I decided that this was a good one to try and get back into it. The guidelines are pretty wide open, so I went with the how I learn best, and how I like teaching the best.
I do my best learning by doing, not to say I don’t get something from lectures, but I get more from doing. I know a lot of people out there that are the different from me in this regard. I think what really helps me the most is when doing a lab and something goes wrong. That’s when I can really dig into the product or whatever it is that I’m working with and really see how it works.
When it comes to teaching, interactive lecture style is really the only way I know how to do it. This lets me do my best to customize the session to the needs to the group that is sitting in the session so that they are able to get the most out of it. And that’s really the goal, that the people who are listening to the session get the most possible information out of it.
So I recently got an email from my NetApp sales rep telling me how awesome the Flash Cache is on the NetApp arrays. The email was really short and to the point.
Not sure if you’ve heard about how we’re leveraging cache to augment storage performance.
Here’s a recent article…
NetApp Customers Purchase More Than a PetaByte of Flash Cache for Greater Performance and Storage Efficiency.
Then there was a link to a press release telling about how NetApp customers have purchased a PetaByte of Flash Cache for their systems.
If you don’t know what the NetApp flash cache is, its a flash based IO card (kind of like a Fusion IO card) that the NetApp array uses as cache for reads. Each flash cache card gives you either 256 or 512 Gigs of cache that is used to speed up reads. You can put up to 4 TB of flash cache per NetApp array.
There’s two ways that you can take the statement that NetApp Customers have purchased more than a PetaByte of Flash Cache (which is VERY expensive to purchase). The first is that NetApp customers have such high IO loads that they need this cache layer to get the performance level. The second is that because NetApp arrays are all RAID 6 (yes I know that NetApp calls it RAID DP but the DP just stands for dual parity, which is RAID 6) that to get the write performance that others can get with RAID 10.
Given that so many NetApp customers are purchasing the Flash Cache, 5000 units have been sold since September 2009 and it’s shipping in 20% of the units that you can cram it into (according to the NetApp press release) this leads me to believe that its more about the later than the former. If that many customers needed this level of performance this soon after the option became available this leads me to believe that the NetApp arrays just weren’t able to give the level of performance that people needed until this Flash Cache can deliver.
But that’s just my take on the marketing spin.
UPDATE 7/12/2010: Corrected the post to show that NetApp’s flash cache only speeds up reads. Thanks to TechMute for pointing out the error in the post.
So a few years ago a new Storage concept was introduced to market. That platform is know known as the IBM XIV platform. What makes this system so different from every other storage platform on the market is that the system doesn’t have any hardware level RAID like a traditional storage array does. What the system does is assigns 1 Meg chucks of space in pairs on disk throughout the system so that there is always a redundant copy of the data.
The hardware that makes up the system is relatively standard hardware. Each shelf of storage is actually a 3U server with up to two CPUs (depending on if the shelf is a data module or an interface module, I’ll explain the differences in a little bit) and 8 Gigs of memory for use as both system memory and read/write cache. Because of this architecture as you add more shelves you also add more CPUs and more cache to the system.
As I mentioned above there are two kinds of shelves, interface modules and data modules. There are effectively the same equipment with some slight additions for the interface modules. Each module is a single chip quad core server with 8 Gigs of RAM and 4 one Gig Ethernet ports for back-end connectivity (I have it on good authority that this will be increasing to a faster back end in the future). Each shelf contains 12 1TB or 2TB SATA hard drives spinning at 7200 RPM. The Interface modules have a second quad chip CPU, 4 four gig fibre channel ports, and 2 1 Gig iSCSI ports.
Now the system comes with a minimum of 6 shelves which gives you 2 interface modules, and 4 data modules. From there you can upgrade to a 9 shelf system which gives you 4 interface modules and 5 data modules. After you have the 9 shelf system you can upgrade to anywhere from 10 to 15 shelves with new interface modules being added 11 and 13 shelves. There’s a nice chart in this IBM PDF (down on page 2) which shows how many fibre and iSCSI ports you get with each configuration.
All these modules are tied together through two redundant 1 Gig network switches which use iSCSI to talk back and forth between the shelves. My contacts at IBM tell me that the haven’t ever had a customer max out the iSCSI back plane, but personally I see the potential for a bottleneck. Because of how distributed the system is, I can see this, but if things don’t balance across the interface modules just right I can see a bottleneck potential here (my contacts tell me that the next hardware version of the product should have a faster back plane so this is something they are addressing). There’s a nice picture on this IBM PDF which shows how the modules talk to each other, and how the servers talk to the storage modules.
The really nice thing about this system is that as you grow the system you add processing power and cache to the system as well as fibre and iSCSI ports so that should really help eliminate any bottlenecks. The downside that I see here is the cost to get into the system is probably a little higher than some of the competitor products as you can’t get a system with less than 6 shelves.
How it works
From what I’ve seen this whole thing is pretty cool when you start throwing data at it. When you create a LUN and assign it to a host the system doesn’t really do a whole lot. As the write requests start coming in it starts writing two copies of the data at all times to the disks in the array. Now as the data is written one copy of the data is written to an interface module, and one copy of the data is written to a data module. This way even if an entire interface module were to fail, there would be no loss of data. That’s right, looking back to the hardware config, we can loose all 12 disks in a shelf and not loose any data, because that data is duplicated to a data module. So if we had the largest 15 shelf system with all 6 interface modules, we could loose 5 of those 6 interface modules and not loose any data on the system. Now if we had a heavily loaded system we might start to see performance problems as we start to max out the fiber on the front end ports, or the 1 Gig back end interconnect ports, until those interface modules are replaced but that’s probably an acceptable problem to have as long as the data is intact.
Because there is no RAID there’s no parity overhead to deal with which keeps everything nice and fast. Because the disks aren’t paired up in a 1 to 1 like they would be in a series of RAID 1 arrays if a disk fails the rebuild time is much quicker because the data is coming from lots of different source disks, so the odds of a performance problem during that rebuild operation is next to nothing.
The system is able to keep everything running very quickly because every LUN is evenly distributed across every disk. When you create a LUN within the management tool it correctly sizes the LUN for you for maximum performance. While this will cost you a few gigs of space here or there the performance benefits are going to greatly out weight the lost storage space; especially when you remember that these are SATA disks, so the cost per Gig is already very low.
My Thoughts on the System
Now I’ve only had a couple of hours to work with one of these units. I’d really like to get access to one for a week or two to really pound on the system and really beat the crap out of the system to see what I can really make the system do (hint, hint IBM).
The potential IO that this system can serve up to a host server, such as a SQL Server, is massive. Now once you load up a few high IO servers against it the system should be able to handle the load pretty well. The odds are getting a physical hot spot on one disk is pretty low since the LUNs aren’t laid out in the same manor on each disk (in other words the first meg of each LUN isn’t on disk 1, the second meg of each LUN isn’t on disk 2, etc).
The management tools for the XIV system are pretty cool. They make data replication between two arrays very easy. It’s just a quick wizard and the data is moving between the systems. One thing which is very cool with the management tools where this system is a step above other arrays is that the XIV is aware of how much space has been used in each LUN. This makes disk management much easier as companies where the storage admins don’t have server access, and the server admins don’t have access to the storage array can each monitor free space from their respective sides which gives a better chance of someone seeing full disks quicker and being able to do something about it quicker before it becomes a problem.
Like every system with RAID if you loose the right two disks you’ll have some data loose. If you have a standard RAID 5 RAID array if you loose any 2 disks in the array then you loose all the data on the array. If you have a RAID 10 array if you loose a matching pair of disks then you loose everything on the array. With the XIV system if you loose two disks you’ll probably be ok, as the odds that you loose two disks that have the same 1 Meg block of data on it are very slim, but if you did loose those two disks before the system was able to rebuild you could loose the data on that LUN, or at least some of the data on the LUN. Now IBM’s docs say that the system rebuilds from a failed disk to a hot spare within 40 minutes or less (page 4), but I’d want to see this happening under a massive load before I would put my stamp on this.
Overall I would say that the XIV platform looks pretty stable. With what I’ve heard about the next generation of the hardware it appears that most if not all of the issues that I see with the platform appear to be resolved. The one thing which I’d really like to see would be three copies of each block of data through out the system; as the odds of loosing three disks all containing the same 1 meg block of data would be next to 0. Maybe this will be a configuration option with the 2TB disks, or maybe when the 3TB disks come up (when ever that happens). But then again, I’m a DBA so I love multiple copies of everything.
Now I’m sure that some of the other storage vendors have some opinions about the XIV platform, so bring it on folks.