If you haven't heard, PASS is running a contest where you could win conference registration to this year's PASS Community Summit or, if you have that covered, 4 nights coverage of hotel costs. All you have to do is enter by July 1. Check the link for details. Here's my best thing I learned, which was from the 2004 PASS Community Summit:
It was a packed, standing-room only presentation. The late Ken Henderson was talking on how SQL Server allocated memory, and was talking about the MemToLeave region. He discussed how large execution plans can tap memory from this space and how any such allocations require contiguous memory. So if MemToLeave is fragmented and you need a large amount of memory, a contiguous block may not be available, generating an error. I slotted away this knowledge, never thinking I would use it.
Fast forward a couple of years when one of our teams kept reporting that one of their nightly processes was failing with an error indicating that it couldn’t allocate contiguous memory. My mind went back to the Henderson talk. The error indicated it couldn’t allocate 229 MB of contiguous memory. When the default setup had only 256 MB total, it wasn’t surprising. Some were blaming SQL Server and asking a case be opened with Microsoft. I pointed to the amount of memory and asked what needed that much. We looked and found a horrendous query in a looping piece of code. It was being built dynamically, adding an AND clause to the WHERE each time it processed a record. This was to exclude said record and the process had tens of thousands of rows to process nightly. I can’t imagine what that execution plan looked like. We had a likely culprit and forced them to rewrite the process. After they did, the process didn’t fail again.
We had my organization's semi-annual combined IT and financial meeting this morning. At the end of these meetings awards and certifications are announced. I had earned an award but I wasn't recognized. I'm a bit disappointed, and I can understand why, but I also know that I shouldn't be. Oversights happen and the things I did to earn the award in question I did because I wanted to do them, not because of the award itself. I enjoyed doing those things and in my mind that should be reward enough. So I'm struggling with myself over the fact that I am disappointed.
To put things in perspective, I'm disappointed about not having my name called whereas the guy who was sitting next to me will likely have to pull an all-nighter to facilitate a customer migration to new servers. And this is after he was up working until 1 AM this morning prepping for the move that will happen tonight. Not only that guy, but two other guys I know will be working like crazy. With that sort of reference point, not having my name called is a really silly reason to be sad.
This got me to thinking about how often we let emotions drive us. Some emotion is good. We should feel passionate about what we are doing. I was passionate about doing those things which earned me the award in the first place. That's emotion. I think if we're using emotion to motivate us to do better, that's fine. But when emotion holds us back from our best, that's a different story entirely. I have had a little talk with myself about how it won't help to be disappointed and that if I were in the same position and there was no award, I still would have done those same things and enjoyed doing them.
It also made me realize that I do wish I had a local mentor to talk to, someone I could pull aside to help me see things in their proper perspective. I was able to see things as they are and make corrections, but that's not always going to happen. Andy Warren has written a lot on mentoring and it has spurred my thinking about the importance of mentors, especially lately. I think that's one thing great mentors are able to do: help folks see things in the proper perspective. In IT our work is so involved and it can consume us. So we can lost perspective on its importance in our life. Likewise, we can have situations like mine today. And we can react wrongly and let affect our actions, our mood, our life. There's no real reason to let that continue. But sometimes it really takes another person, someone we trust and someone we know who's looking to help us grow, to get that through our heads.
Early last week, my church suffered a lightning strike that did quite a bit of damage (relatively speaking) to computer and media equipment. I spent a lot of time last week during off hours working the issue along with some other very knowledgable folks. We're not out of the woods yet, but we were up and running and were able to cover a funeral last Friday and worship services on Sunday. In thinking about it all, it brought me back to business/disaster recovery. Since a church is a relatively simple model, I figured I would blog about it to hopefully stir some thoughts for more complex situations and conditions.
Understand What You Need and When You Need it By, Before the Disaster
This one should be self-explanatory, but I've seen cases where it has not been done. I'm the junior high youth pastor at my church and we're not very heavily computerized like some churches. So no one had really considered this thoroughly for us. We carry cell phones if members need to reach us. As long as we have a building with seating, we can hold most of the services we "provide." So our essential recovery is rather limited in scope. But if you're a business of any size, you need to do this before a disaster does hit. In our case we made a quick evaluation of what was critical for services. Getting all of our lighting back was essential. Sound and A/V for the funeral and services was most pressing after that. Establishing network connectivity, especially Internet connectivity for a couple of the systems followed. Everything else was after those key priorities.
Assess the Damage
After the lightning strike, one of the first things we needed to do was assess the damage. Actually, we took a slightly different approach, in that we assessed what "systems" didn't work. Knowing what is damaged tends to be a little trickier, because if you have multiple components/systems in-line, you've got to test 'em to determine what's actually broke. This is what we noted were down:
Once we knew what was down, we started looking at the component parts. For instance, not all the phones were down. So we took the ones that were and tried to connect them to the lines that were working. No dice. When we plugged them in to the power bricks that were working, they were dead. So we knew the phones themselves were history. With respect to networking, the DSL router was fried. It didn't even power up. We had another power cord that was known good with the same power characteristics and that didn't work. The DSL router was also a switch. So I grabbed a known good switch and tried to network the computers. No Internet, but access to printers and the like could be had... except that didn't work, either. I suspected the NICs on those computers, especially after one of the office computers didn't recognize its NIC any longer. However, another did recognize its NIC, but I couldn't get connectivity. I suspected the wiring, especially since I had one component I believe to be good not working when connected to the switch. In any case, you get the idea. We did this for all the components.
Have Your Insurance, Service Provider, and Hardware/Software Reselleres Contact Information Ready
Once we had determined there was some damage, one of the church staff placed a call to the insurance provider to verify the process for submitting a claim. Before we did anything with the equipment, we wanted to know how to proceed. We were able to get the information and start handling the equipment appropriately. The last thing we wanted to do was handle the equipment in such a way that the insurance company could say, "You violated subsection A of your agreement and we won't reimburse." We also contacted the DSL provider and had a new DSL router sent. Part of recovering from a disaster is filing the claims to be able to pay for equipment that has to be replaced. And part of it is understanding where you can get the replacement equipment.
Determine Work-Arounds
We had an action plan on how to proceed with recovering capabilities (we're still executing on it). In one case, the Internet connectivity, once we got the DSL router in, like most nowadays, it had wireless capability. Thankfully, though it looks like our phones/networking got hit by the lightning, once the new router came in, it was apparent that we still had DSL service. I was able to get that connected and running fairly quickly. However, since I'm almost positive the wiring is shot but I can't be sure about the NICs, we ran out and grabbed a couple of USB wireless NICs to install to provide some capability for Internet connectivity within the church. I knew there was a strong signal throughout the top floor because I did a walk around with a laptop and made sure of it. There are plenty of tools out there for this purpose. But a rather simple thing to do is walk around connected and just see if you can hit particular web sites. That's the low tech solution and it works just fine. Because I didn't have my normal security tools on this particular laptop, I went with the low tech option. The USB wireless NICs worked great and we had two systems on-line on the Internet.
Keep a Running List of Unresolved Issues and Reprioritize As Necessary
One of the applications we use for AV was built for two monitors. We were able to determine the second output of the video card had been damaged, which meant we were down to the single monitor. While the second monitor was good, we were able to verify the projector was hit, too. We had a spare projector, but no one had done the research on how to get the application running on a single monitor. We had temporarily used PowerPoint and hand-typed lyrics in, but we wanted to use the app because it handled this and handled it well. So after Sunday morning, we looked at where we were and noted that we still hadn't fully restored that capability. So before evening service we sat down and did that research. While running on a single monitor isn't ideal, it's do-able until we swap out the soundboard computer within the next week.
Determine What Can Be Done to Avoid the Disaster or Respond Better to It (Lessons Learned)
In our particular case, we had surge protectors and the like in place, but we're talking lightning here. Some of it was sufficient based on the exposure of the equipment, but some of it wasn't. We're looking at the building and seeing what else can be done, but there's only so much that can be done in this situation. That's one of the reasons it is smart to carry insurance. But if you have a failure, use it as an opportunity to learn. What could have been done to avoid the situation, if any? Is it reasonable for the business? What was done that could have been done better to recover? Were the recovery procedures satisfactory? Is there something else that needed to be done? Once you've looked at the situation, go back to your recovery plan and make the appropriate changes.
When the announcement for Bing came out, I didn't immediately go over and check it out. As a matter of fact, I didn't even look at it. My reasoning was simple: Google was working well and therefore I didn't see any reason to switch. But this past Saturday, Paul Nielsen of SQL Server Bible fame tweeted the following:
Microsoft search engine Bing honors D-Day on their front page. Goggle "honors" Tetris.
To me, D-Day is a bit more important in world history that Tetris. So I clicked over and saw an image of the beach and the land behind it, where Allied forces came ashore 65 years ago. You can still see the image if you go to the Bing page and hover towards the bottom right of the photo. You should get left and right arrows, which allow you to navigate back and forth to see the images that have been posted. The D-Day one was Saturday's. Imbedded in the image are a couple of highlights which can take you to more information on that fateful day.
Now the fact that Bing was honoring D-Day and Google wasn't isn't the reason I switched. However, the difference in their choices is the reason I considered Bing. I took a look at it, ran a couple of searches and was reasonably satisfied with what I got back. So since I was already there, I decided to configure it as my default search provider for both IE7 (sorry, I've not upgraded) and Firefox on my personal laptop. I figure I'll give it a run for a few weeks and see how it does. If I don't like the results, I'll switch back. If I do, I'll leave it alone. If it works great, I'll look at switching over my other systems, too. If you're interested in doing the same, you may want to visit the following link:
FAQ: How to add some Bing to your browser (ComputerWorld)
This one isn't a technical post, but it's entirely appropriate to those of us in the IT field. Today was a stark reminder about priorities and what they should be. Those who follow me on Twitter probably saw that I tweeted about a tragedy that affected one of the families in my church. In a nutshell, the unexpected did happen and someone did pass on fairly young. There's a lot of folks taking it pretty hard, my family included. One of the things I tweeted was the following:
A reminder that life can end unexpectedly. Live life to the fullest today and prepare fully for eternity now. There may not be a tomorrow.
If you aren't a person of faith, then the preparation for eternity part is taken care of, so far as you're concerned, but it is still important to live life to the fullest. That doesn't mean be foolish and careless and reckless. But it does mean looking at priorities and thinking about what's truly important. If something is important to you, it should get the proper amount of time, attention, and care based on its priority. For me, my priorities basically follow the pattern:
Sometimes these things go together. For instance, I love to play boardgames. So do my boys. And that's something we do together. But if I have to give time and effort, I will do so based on the previous list. Now there are always times where times will be slightly out of balance. For instance, if I have to work extra hours to complete a project, then it gets done. But over the last few years I've experienced that when you rob one priority to give time to a lesser priority, no one is happy and nothing gets done as well as it should.
As an IT professional the job / professional reputation is easiest to spend the most time on. If you like your craft (and I do), working with technology can be fascinatng and it can be easy to immerse, to the detriment of the other priorities. I've done it and regretted it after the fact. In the last two or three years I've worked very hard to correct that. I know others in the same boat who have done likewise. And the more we get our time and effort and care to match our priorities, the happier we are.
With that, I'll get off my soapbox. But if you haven't thought about what's really important to you, now might be the time to do so along with some self-evaluation to how your time and efforts match up with those priorities. I have found that regularly reassessing my time and effort has helped a lot in making sure I am giving the right emphasis where it needs to be. And I'm very glad to have started that practice in my own life.
When it comes to securing a system, it's important to understand how it might be attacked. That's what surface area is all about. The surface area is the parts of the system which are exposed. For instance, in the case of a default instance of SQL Server 2005 or 2008, we typically see two TCP ports open:
We also typically see the Shared Memory protocol enabled, meaning an attack is possible if you can get on the server itself. Since the DAC is certain limitations as to what can be run (but it also allows access to things that you can't get to with a normal connection), the connection using TCP/1433 or Shared Memory might be preferable. And in some cases we see Named Pipes on as well. If that's the case, this protocol can be used as well. All of this can be checked with the SQL Server Configuration Manager. And all of this is part of a SQL Server's surface area. If you're in charge of administering a SQL Server and you've never checked all of this out, there's no time like the present. And depending on what other features you have enabled, you might see more (such as a Service Broker or HTTP endpoint which exposes more TCP ports).
But what about once you establish a successful connection? What then? I've already mentioned Service Broker and HTTP endpoints, which can expose even more access to SQL Server. Within the context of SQL Server there is the ability to enable other features which increase the surface area. For instance, you can flag DAC so it's also available remotely. That means I don't have to compromise the server SQL Server is running on any longer to attempt to connect to the DAC. I can try and hit it remotely. You can allow certain features like OLE automation or access to xp_cmdshell, which are not normally accessible to any users, not even member of the sysadmin fixed server role (though they certainly have the capability of turning these on). It's important to know what features are on and which are off. In SQL Server 2005 there's SQL Server Surface Area Configuration (SAC) but in 2008 the tool is no longer present. Instead you've got to use Policy Management with the settings you care about. The advantage in 2008 is SQL Server can enforce these settings. SAC can read when you start it up and it can apply changes to settings, but it's not a continual monitoring type of tool. That's the reason 2008 uses Policy Management to maintain surface area configuration.
The point of all of this is to understand how an attacker might get into your system. the starting point is understanding the surface area: what's on and what's not. You need that to go to the next step, which is figuring out how an attacker might use an exposed interface or feature to attack said system. For instance, once I've determined that SQL Server is up on TCP 1433, how might the attacker go about using that? If there's a web server in the DMZ which connects to that SQL Server, then by compromising the web application I may be able to come in on that port. That's the attack. But since I know what's exposed and a potential attack, I can think of appropriate defenses. For instance, I can have IDS/IPS looking at the network traffic for certain key things where that web server talks to that SQL Server on TCP 1433. If I see xp_cmdshell, I know that's not right. And I can flag it and do something about it. But it all starts with me understanding the surface area.
