Author: way0utwest

  • Inserting Binary Data

    There are a number of articles on dealing with the insertion of binary data into SQL Server using BULK INSERT, OPENROWSET, and even Java. However I saw someone asking recently how to insert data dynamically and thought that was an interesting question.

    If you are working with some front end language, this is easy with the various ODBC/OLEDB/ADO/etc interfaces into SQL Server. These languages allow you to specify parameters as a binary stream.

    However if you need to insert this data in T-SQL, what can you do? I know that there is a CAST function and that should work with varbinary. Let’s test something:

    CREATE TABLE Binarytest
    ( id INT
    , note VARBINARY(500)
    )
    ;
    INSERT BinaryTest SELECT 1, CAST( 'A' AS VARBINARY)
    
    SELECT note, CAST( note AS VARCHAR) FROM BinaryTest

    This returns:

    binary1

    I also know that I can directly take binary data in SQL Server and work with it, as long as I specify it’s binary. So I can do this:

    INSERT BinaryTest SELECT 1, 0x41

    If I now query my table, I get:

    binary2

    Not the easiest format to work with, but if I construct binary data somehow, I can work with it in T-SQL if I need it.

  • Impressive Accomplishments

    What things make you think a candidate is a superstar?
    What things make you think a candidate is a superstar?

    Most of us have had an interview or two in our lives. We’ve had to assemble a resume or CV, create cover letters, and practice answering questions in preparation for an interview. In all of these things, we are looking for ways to market our past work and present it in the best light as evidence that we will make a good employee in our next position.

    We each have our own experiences, successes, and failures, but we aren’t bound by what’s happened in our career so far. We have the ability to shape our brand for the future by making changes in the way we approach our career from this day forward. I have a talk and blog on this subject, but I certainly don’t have all the answers. I’m always looking for more ideas and suggestions to help people and wanted to ask for your ideas this week.

    What would impress you most about a job candidate?

    Is there something that you’ve seen or heard about in the past that impressed you? If a candidate had written a book, or created some software, would that make you look upon them favorably? I know that a candidate needs to present themselves well in an interview and be able to write SQL, or manage an instance, but when you are examining dozens of resumes, is there something that stands out?

    Take a moment and tell us what has impressed you in the past, and whether it was an indicator of a good employee or not.

    Steve Jones


    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Home Power – Generators

    A slightly off topic post, but since I was asked recently, I thought I’d take my reply, make a post and have it handy for people to refer to.

    As technology people, electricity is important to us. Without it, we can’t get a lot done. I’ve lost power at work, and home, and it always drives productivity down. I can’t help you at work, but this is a look at how you might handle things at home.

    Short Version

    The short version of this post, for those that don’t want to read my lengthy rambling is this:

    Get a UPS. You need this no matter what.

    1. You can use a small, portable generator, the kind used for construction. Home Depot/Lowes/etc. has a bunch in the $300-500 range that will power your computer. They may not keep your fridge running, but this will work.

    2. You can hardwire a generator port on your house. This is a transfer switch, and this post from Honda talks about it. I’d guess this is around a $400-500 electrician bill,but YMMV. The upside is you can use any generator. Note you still need to manually power on the generator.

    3. Automatic switch with permanent generator – It’s not really permanent, but it’s a $1500-2500 device that runs on natural gas or propane. There might be diesel ones, but I haven’t looked at those. You will still have around a $400 electrician bill and maybe a plumber bill for the gas hookup. This automatically kicks on and picks up powering the house (or circuits) when the street power goes out.

    4. Alternative Energy system (solar, wind, etc.) – You can do this, but you need some type of energy storage. When a grid-tied system loses power from the grid, you must shut down your system, OR disconnect it from the grid. The reason is that you cannot energize these lines when someone is working on them. You can essentially use a reverse transfer switch that shuts off your connection to utility power when that source is lost and you continue to run your system or you have battery storage or both. Glenn Berry has a solar PV system, but not with battery storage. Costs vary widely, but

    Longer Versions

    I’ll talk about each option and what I thought of them. Note that I went with #1 to power some ranch things and #3 for the house/home office.

    We had really flaky power when we moved here. We’d lose 5-10 minutes every few days, which wasn’t too bad, but we’d go down an hour or so at times, one time 8 hours. Between my wife working at home with calls and horses, we needed something.

    Again, note that no matter what you do, you need UPSes. I can’t say this enough because almost every generator I’ve seen, from small 1kw gas to 50kw diesels, produces dirty power.

    Portable Generator, Manual Plugs

    This is option #1 above. Basically you put a generator on your patio/deck/outside and connect it to specific devices with circuits. For our house, this would have worked for the office, but not for our well/water pump, so this wasn’t an option we wanted.

    However when we built an arena and needed to provide power for lights, this made sense. Ten fluorescent fixtures need about 1200W of power. I hung the lights, hardwired two circuits into a shed with simple 110V plugs. A 3500W generator, portable for other projects, can be started and run the lights when needed. Since we are looking at around 100 hours of operation a year, this made the most sense.

    And I got a generator I can use around the ranch to power tools for repairs. Smile

    This can work, and you can use extension cords. However keep in mind that you need to run extension cords, which can be a hassle, especially with kids/pets. Weatherproofing can be a hassle as well. Do you want to run this when it’s storming outside? Rain/snow? We don’t. You might.

    You also have to go start this thing. That’s a hassle. Filling it, which is like filling a lawnmower with gas, isn’t hard, but it’s a hassle.

    Lastly, if power is an issue for you, will this keep your fridge/heat/AC going? That might be a decision point you need to think about.

    On the positive side, this is portable. You can take it if you move. It’s also scalable. Adding another generator, or three, is easy, and you can invest over time as your needs change. Fuel is also ubiquitous, and easy to store. You can even use fuel for other needs (mower, car, etc).

    Not a bad solution, but not automatic, so this didn’t work for the house for us. YMMV.

    Portable Generator, Transfer Switch

    The next stage up from using extension cords is to have a transfer switch put onto your house. As I posted above, this is a better summary than I could write.

    This is the same process as above, but there are circuits which are hardwired to the transfer switch. When you use the generator, it powers those circuits. When the generator is off, the utility powers them.

    This is a much better solution than above for your house. You’ll pay some money to get the switch and an electrician out, but it works well. You can take the generator with you if you move, or need it elsewhere. The next person living there can use it or not.

    However, the downsides apply. Fuel management, weatherproofing, manual start. If you go this route, I’d think about some sort of roof or cover on the generator that would allow you to let it run in inclement weather. That’s when I’ve lost power. This can be under an overhand for a deck/patio, but note that this still needs to be relatively close to your electrical panel.

    If we didn’t need to have automated switchover, I’d have gone this route.

    Automatic Whole House Generator

    Since we could be on vacation, we needed an automatic generator. The winters in Colorado mean we need to heat water for the horses, and since we have a well, we need to get water.

    I got a Guardian Generator for our house. We looked at the house, checked the circuits and load, and compromised on size. A  25kw unit would power the whole house, but this was just for emergencies and a 25kw generator uses a lot of power. A 7kw one might have worked, but we thought 10kw, with a little more capacity would get the fridge, office, barn powered with a little to spare. We keep our bedroom going along with the kitchen and office, and if we lose power the kids pile into our room at night.

    The process here involved me pouring a concrete pad near my electrical panel. IT was rougly 4’x2’. You need this to rest the generator on. Fuel is also required. Out in the country, we don’t have natural gas; we have propane. I have a 1000 gal tank that feeds the house, however it was about 500’ from my electrical panel. The trenching and piping would have cost me $1500 or more. I elected to use 100lb propane tanks instead.

    I poured another pad and sunk a pole in it to chain the tank to. This is important since you don’t want your tank moving, falling, and rupturing a propane line. I hired a plumber to come and install piping from the tank to the generator. It’s not hard, and if I had to do it again, I’d do it myself, knowing I need a pressure regulator and the a certain type of piping. I would, however, have a plumber come inspect my work. It would be worth the $50 or so to ensure I didn’t have propane flowing in the wrong places.

    If you have natural gas, you definitely need a certified plumber to route a line from your supply to the generator. Since gas usually flows even when power is out, this works well. I have friends closer to town that have gone this route and it works fine.

    After this, we had an electrician install the generator, but if I were doing it again, I’d buy the generator at Home Depot, pour my pad, screw it into the concrete (not really that hard), and mount the panel on the wall. Then I’d call an electrician for the next part because I don’t like thick wires. 110V is easy to work with; 440V or 240V scare me.

    The install involved the electrician turning off power to the house, He then "moves" the circuits you want to protect from your main panel to the generator panel. It’s a piece of conduit from the generator panel to your home panel, and they essentially wire the breakers into the generator box instead of your box. This can be undone later if you want. The generator also gets a breaker in your panel that feeds its panel.

    Once this is done, you need to turn on the generator and test things. If it’s working, you shut it down, and you are set. Getting the generator going is like a lawnmower, albeit a larger one. It’s a small engine, about 500cc. Maintenance is about the same, change the oil and air filters periodically (about once a year or so). There is also a car battery in here that starts things up.

    When you have street power, it flows through the 100W breaker into the generator panel, through those breakers, out to the circuits you want powered by the generator. There’s a physical breaker in there powered by an electromagnet. It actually drops a couple inches, in about 2s, when power from the street is lost. This opens the starter circuit for the generator, which turns over, and also routes power from the generator to the 10 circuits I’ve designated instead of from the street.

    Total cost for me was about $4500, but I could have reduced that to about $3200 or so by doing more of the setup myself and just having the generator electrical work contracted. Even the propane was fairly simple once I’d seen it done.

    The downside is this is semi-permanent. I can move it, but I’d pay an electrician to undo the panel, and I’d have to move a large device. Not much harder than moving a motorcycle, but I’d probably be tempted to sell it with the house and up the price of the house $2k or so.

    The upside is this is automatic. It comes on within about 15s and takes load from the UPS. Nice for us, and good if we’re gone. The people we have watering the horses don’t have to worry and we have heat running in the house and the fridge works fine.

    In the five years I’ve had this, it’s come on numerous times. It self tests once a week for about 5 minutes, but we’ve had it come on and run for a few hours at various times. We’ve not had any complaints from people watching the horses, so either we haven’t lost power while on vacation, or they didn’t notice.

    The one night that made this all worthwhile was about a year after we installed this. Our neighbors had rented a boxing match and my family went over to their house. About a minute into the match, the power went out. When we walked outside, the whole area was black. Except for our house.

    We went home, glad we had power that night.

    Alternative Energy

    <rant>

    We seriously looked at both wind and solar power systems. I believe in trying to be more sustainable and using renewable sources. We still regularly re-evaluate if we want to invest in wind, solar, or even geothermal systems. The price of electricity and propane continue to rise. While I don’t think we’ll run out of fossil fuels in my lifetime, I do think that we should make switches where it makes sense and extend out our supply of fossil fuels.

    The cost to value ratio hasn’t been good enough for us to make the switch yet, but it’s getting close. I’d want a 10-12KW system and I’m looking at a $30k+ investment, which is a lot. Right now that’s decades of time to payback.

    </rant>

    That being said, there are a few things you need to understand about power generation and power outages. When I investigated wind and solar systems, I found out that they don’t do me any good in a power outage. Since they power my house, and I still need utility power at night or on calm days, these systems can power the grid as well. That’s good. I can sell power back to the power company by law.

    That’s bad if a lineman is working on a downed line. It’s dangerous for him, and so if I have a power generation system, by law, I have to disconnect it from the grid. My automated generator does that. You can add this to your solar/wind system, but it’s more $$.

    The other thing is that if it’s at night, or calm, then your system doesn’t have spare power in a power outage. You can counter that with batteries, but batteries are pricey. They add a lot of cost to the system to power your house. You might think you can just disconnect stuff from your electrical system, but there are a few things you don’t want to disconnect: the fridge and heat. Those eat a lot of power, at least the fridge does. They can exhaust batteries in a relatively short time, and for us, that’s not acceptable. Horses need dependable water.

    That doesn’t mean this won’t work for you. You can get by with relatively little battery power if you want to power portions of your house when you are home. You might not care about vacations, and you might like being insulated from rising costs.

    I’d recommend this, but analyze your life well. The payback period is long, though you can add this to a sale price in your house. Understand that you’re essentially taking a loan of some sort if you don’t have $30k lying around, at a fixed rate, and using that to trade out over time the (potentially) rising price of electricity. Depending on your location, utility and what the world does, this is a gamble. I think it’s probably a good gamble, but not one I’m quite ready to make. I’d rather insulate my house and go geo-thermal first, but that’s me.

    Conclusion

    There’s not right answer here. Like any database question, the answer for what you should do depends. It depends on how much investment you want to make, what hassles you want to deal with, and the configuration of your space.

    We have special concerns with horses that outweighed some of the other things we worry about. If I were in a suburban neighborhood, I’d opt for #2 with an overhang or large shed near my panel.

    Hopefully most of you don’t have power issues, but if you work at home regularly, or plan on it, think about these things.

    Lastly, since I heard from someone recently that had power issues, I’ll mention this. Kids matter. When we had infants and toddlers, the ability to have power, heat meals, etc. was more important. My wife and I can eat beans from a can for a few days. Little kids can too, but life deteriorates quickly. In Colorado we’ve learned you can’t always get to a motel if there are problems as well. We’ve been snowed in, even in suburban neighborhoods where houses are less than 20 feet apart for days.

    If you have little kids, or plan on them anytime soon, take that into consideration. A generator, even scenario #1 above, is a nice piece of insurance, just in case. In a pinch, it will even run your coffee make, another essential piece of equipment.

  • Bring Solutions

    Solutions are the most important thing you can develop at your job.
    Solutions are the most important thing you can develop at your job.

    What’s your job? Most of you will answer “DBA”, or “developer”, or “manager”, or something that corresponds to your title. That’s partially true, but for the most part if you are in Information Technology, I think your job is to solve problems. You should ensure that your systems, or your code, or whatever you work on, is running smoothly for the people that use them.

    That isn’t always possible. There will be times that things break, that bugs crop up or systems fail. When that happens, people don’t want to hear about the inherent problems, or even what went wrong. They might ask, and they might be curious, but really what they want to know is when will the system be fixed and what will it cost.

    I’ve never had a manager that was really interested in the problems that caused a failure when systems were down. I’ve rarely had managers interested in potential problems I’ve noticed in our organization. Managers might be curious about the issues, but they don’t want you to bring them problems. They want you to bring them solutions when you see a problem, multiple choices to fix things, and a recommendation for the option you feel is best. They want a solution that works well, doesn’t cost a lot, and can be implemented quickly.

    Competition for jobs gets more intense all the time and the employees that look for problems and provide solutions without being asked will be the most valuable. They will stand out from their peers and are more likely to not only keep their jobs during down times, but will get asked to solve the interesting and challenging problems.

    Steve Jones


    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.