Skip to content

SQL Bits XII

Last week was SQL Bits in the UK, and it was a blast. I think this might be my favorite conference, despite the long trip for me to get there. This is my third SQL Bits, and each has been more fun than the last.

This one was in Telford, which is NW of London, W of Cambridge, and a two hour drive from Red Gate. There’s not a lot around there, though Grant did want to stop at the Ironbridge Gorge, which we did.

Photo Jul 17, 8 33 00 AM

The event is a lot of fun, with three different days and different feels. Thursday has pre-cons, and there were a lot of people walking out as I arrived Thur afternoon. Everyone I talked to that presented a pre-conference session was happy with the day, and as I queried people on Friday and Saturday that had paid for one, all felt it was worth the time and money.

Photo Jul 19, 7 53 23 AM

Thur night was quiet for me, hanging out with friend and chatting while a pub quiz (trivial pursuit type, bingo event) took place in the conference center. It’s a nice mix of professional and casual that seems to excite me at SQL Bits. We learn a lot, and there are great sessions, but it’s not as strict and big a deal with badges being constantly checked as happens at some other conferences.

Friday was a day I didn’t have to present, so I walked around learning. I saw sessions on SSDT, Unit Testing, security, and more. It was interesting, and Andreas Wolter is a scary security presenter. His talk made me want to build an alert for anyone querying @@version on my systems. That seems to be the start of ways in which hackers can learn about what to attack inside your instance. Simon Sabin presented a great security session on encryption in which he also deployed changes to a live system without interrupting things. That was really cool.

Photo Jul 18, 5 53 25 AM

Friday night was the main party, which always has a theme, and is a lot of fun. Last year Red Gate gave me a costume, and I ended up looking like this:

SQLBitsXI_a

This year the theme was Steampunk, with an older carnival type flyer. I wondered what that would mean, and I have to say that I was surprised when I walked into the party.

Photo Jul 18, 2 18 23 PM

Yes, there was a carousel, along with a two story slide, various entertainers, and carnival booth games. Lots of people dressed the steampunk way, including Grant. Here’s one that was taken, showing the types of outfits.

sqlbits2014

It was a little loud, but a lot of fun, and I stayed much longer and later than I planned. Saturday morning was hard to wake to, especially as I needed to pack up and leave.

Saturday at SQL Bits is the free day. I had two sessions, and while I sat in a few others, I also needed to prep. It had been quite some time since I’d actually worked with one of my sessions and the technology.

My branding session had a packed room and went over well. People asked a few questions, and hopefully I helped a few people. My CI session wasn’t great. The room was packed, but my VM acted up. I suspect that some of the changes to the config of HyperV at Red Gate caused a few issues, and I couldn’t demo everything I wanted to. Some of my processes failed when I didn’t want them to, and passed when they did. My apologies to the attendees is coming soon.

I didn’t stick around Saturday night as I had to get back to Heathrow for an early Sunday flight. Things were uneventful from then on for me.

I met lots of new people, as well as seeing many more that I’ve known for years and only see in the UK. The event was run as smoothly as any I’ve experienced, and was a lot of fun, with quite a few spouses and even some kids there.

Thanks to everyone that attended and huge thanks (and kudos) to the organizers. I’m already looking forward to next year and hoping to get to speak again.

Apologies to my CI for Databases Attendees

I have to apologize to everyone that attended my Continuous Integration for Databases talk at SQL Bits. I ran over, a few demos didn’t work, and my machine flaked more than expected. I know those things happen, but I still need to apologize.

While there were problems with my demos, I should have moved on and gone through things a little quicker as well. I could have cut out, or cut down a few concepts, to get through things quicker. The session is really built for 75 minutes, but that’s no excuse. I need a 60 minute, smoothly delivered, version.

I also should have run through everything completely after some changes at Red Gate. I only set up part of it, which was a mistake. I don’t know if I’d have caught anything, but I might have.

I’ll work on getting some video and written content of the presentation up here, and hopefully that will help you understand the concepts, and perhaps start to implement CI with your own databases.

Powershell Tips–Showwindow

Looking for help is a pain in Powershell. I type something like

help get-process

execute this and I’m scrolling up and down. Things scroll off the screen and I have to muddle through, meanwhile, my code is at the bottom of the window.

posh_help

Even when things stop at the end of the page, like the old man pages in Unix, it’s a cumbersome way to work through something. However there’s a better way.

help get-process –showwindow

If I type this, all of a sudden I get a window of help.

posh_help_b

I can resize the window, move it, keep it handy on the section that’s relevant when I’m trying to write a piece of code.

A very handy tip I got from one of the TechEd 2014 sessions.

Quick Tips–SQL Prompt Aliases for Every Table

I love SQL Prompt, and think it’s a great productivity tool. Even before I worked at Red Gate, I love the tool and had a copy before Red Gate bought the technology from the original developer. Recently I’ve run into a few people that weren’t aware of some of the ways in which it can help you. This is a quick look at one of the ways I use SQL Prompt.

Aliases

Aliases are used to make code more readable, and shorten the amount of code that one needs to write. Typically we use these to give a short name to a table. Instead of:

aliases_12

We could use an alias. Note the “p” after the table below and the change in the column list.

aliases_13

Automated Aliases

SQL Prompt can automate aliases for me. Under the Options dialog, there is an Aliases selection (shown below).

aliases_1

Note that I’ve checked the “Assign Aliases” box. This is not checked by default, but once I check it, I get aliases. Let me write a query.

aliases_3

I’m about to select my Orders table from the Prompt drop down. Once I click Tab, I’ll get this:

aliases_34png

SQL Prompt has added the alias for me. It’s a lower case “o”. If I add another table:

aliases_5

I hit tab at this point and I get:

aliases_6

I have a new alias of “o2”. Not terribly creative, but it works.

I have some options for changing these around. Suppose I want to make these upper case to stand out. I can change this in options:

aliases_2

Now I add a third table:

aliases_7

I hit tab:

aliases_8

My new alias is an upper case “P” for the Product table. That gives me a bit of differentiation for my tables.

I, however, do not like the “AS” keyword. I typically just space my alias after the table. I can change that in options:

aliases_9

I’ve unchecked the box and now I add a new table.

aliases_10

When I hit Tab, I’ll get a new alias, upper case, but no AS.

aliases_11

These are not terribly intuitive aliases, but this does at least clean up your code a bit, so when you see all the column names they aren’t spread way to the right with table names like “ProductDescriptions”.

aliases_14

Sabbatical–First Reflections

My company, Red Gate software, has given me a 6 week sabbatical. I’m documenting the time with all the posts under a tag if you want to follow along.

I’m back in the UK as I write this, having flown over on the last day of my sabbatical. Things were slightly cut short, but I can’t complain at all after having six weeks off.

I wanted to reflect a bit on the sabbatical right away, and then again later. I’ve set reminders in my calendar for the end of the year as well as next spring to think about what this time meant for me.

I’ve broken this down in to a practical section that looks at the plan and how things went and then a more reflective section that examines the value and meaning of the sabbatical.

Learning and Growing

When I planned this sabbatical, I didn’t have any burning desire to accomplish a single thing. That’s not quite true as I’ve wanted to work on a book, but since I write for a large part of my job, I decided not to make that a sabbatical project. Instead I decided to tackle three things:

  • Take a woodworking class
  • Volunteer for Habitat for Humanity
  • Build a Flagpole

Going into the sabbatical, I wasn’t sure what to expect. I thought I could volunteer three times a week, take a class a couple nights a week, and quickly build my project. It turns out, like most software estimates, I was wildly optimistic about the time and effort things would require. I also hadn’t accounted for the setbacks that invariably occur.

Woodworking Fundamentals

I signed up for a class at a local community college, which happens to host one of the premier woodworking programs in the US. While I have built various projects out of wood for years at home, I’ve never had any formal training on how to use tools and work with wood. I’m completely self-taught, and as I quickly found out, I had no idea what I didn’t know.

The class was initially a bit of a disappointment as it dealt primarily with hand tools. I had hoped to gain some skill with machines, as I have a number at home. However I quickly found that tasks like flattening a board, and hand chopping mortises weren’t as daunting as they seemed and I picked up a few skills I might never have learned otherwise. I also learned to appreciate craftsmanship and working slowly, marking nad measuring in a new way.

The class turned out to be a little lecture and lots of lab practice, trying to build skills. It certainly has made me appreciate the speed of skills, but also mourn the crude ways in which they work. Hand tools are much better for many tasks, though they can be slow. I’m only a little over halfway through class, with it continuing through the end of the month. The next couple weeks when I get back will have my busy at night, trying to finalize my project. I’ll continue blogging about that project under the same tag, in case anyone is interested.

Habitat for Humanity

I’ve told the story before, but a few years ago I was at dinner with a few couples and someone asked everyone what they’d do if they won the lottery and didn’t have to work. There were a variety of answers, but I struggled with the question. I enjoy working and enjoy my job, but when pressed, the thing that came to mind was working for Habitat to help the world.

I’ve always admired their mission, and I think helping people find a good, stable, shelter for their lives is important. With that in mind, I decided to volunteer for the Denver affiliate for Habitat. I attended an orientation, and then browsed the volunteer opportunities. In my six weeks, I volunteered 9 times, working at the ReStores, building new houses, fixing old ones, and deconstructing a kitchen for a renovation (and the cabinet donations).

It was a great experience, and I enjoyed the chance to work with a variety of Habitat employees, Americorps volunteers, and many local people that were volunteering their time. I learned a few skills an d tricks, and while I’m not sure I made a big difference, I know I made some small ones.

I also had the chance to do some good, hard, physical work outdoors. That’s something I’ve done relatively little of in the last decade, and I learned that it’s hard. I was quite worn out some of those days, and I’m not sure I could have volunteered three times a week with my other projects.

Flagpole

The one project I set for myself was building a flagpole out of wood. I’d seen this done on The New Yankee Workshop, and I planned to challenge myself to follow the plan and build the pole. With a DVD and measured drawing, I set out to construct a flagpole.

When I watched the DVD and thought about it, it seemed as though I should be able to build the pole in 2-3 weeks. However I didn’t count on the delays and obstacles that come into play. Weather was a big one for me, with more rain, on more afternoons, that I can remember in recent years. Those delays cut short my work on quite a few days.

In the end, I managed to complete the pole and get it painted, though there’s more work to do. I ordered hardware for the pole and still need to construct a base to hold it upright, both things I expect to handle in the next 3-4 weeks as well. The future plans have a flag flying by Labor Day.

Was It Worth the Time Off?

This is a question that Andy Warren posed to me a few weeks ago. My wife also asked me last week, “Was it great?”

Those are tough for me to answer. On one hand, it was a busy time, with lots of things happening each week. It felt somewhat surreal, as though I’d moved on to a new phase of my life. I slipped right into going to school, getting up early or construction commitments, and working on a large project around the house.

On the other hand, it was very relaxing in that I didn’t have great pressures on me. While I’d committed to Habitat projects, I moved a few around, and I knew I could cancel things as needed. I also paced myself with the flagpole, moving slower than necessary to avoid rework where possible.

I was proud of myself that I managed to avoid email and work. I didn’t check of log onto SQLServerCentral during the time, except the weekend I flew to SQL Saturday #286 – Louisville. Even then I didn’t process or clear much email and as I return to work today, I’ve got nearly 1,000 emails to clear out from the six weeks. I did continue to check Twitter a bit, though I tried to avoid reading technical pieces that were linked and avoided work discussions.

I did miss work a bit, and I’m certainly ready to come back. I slipped back into my job, checking a things on Sunday (while waiting for paint to dry) and getting ready to fly to Red Gate and SQL Bits this week.

However I do feel refreshed. I’m relaxed and unworried about SQLServerCentral, which was not always the case in the past when I’ve taken vacations. I also know some of my worldview has changed. I appreciate my job, the success I’ve had, and I do think that I’ve matured a little in terms of how I see the world.

Will this make me a better DBA/Developer/Editor/Writer? It’s hard to tell, but I’m certainly ready to dive in and grow my skills, working to educate people about SQL Server, as well as evangelize Red Gate’s products and ideals. I’m also grateful that I had the time off, and I appreciate more and more that Red Gate is a fantastic company.

All in all, it was a good six weeks of growth and change for me. I’m not dying for another six weeks off again, and I’m not sure what I’ll spend my next sabbatical doing, but I am sure that I’ll take one again in another five years if I have the chance.

 

I learned more about Habitat, and admire them more than before. They don’t give away houses; they sell them at cost, setting payments and carrying mortgages that enable homeowners to succeed in their lives.

I’ve also contacted their speakers bureau, thinking that my skill as a public speaker might help Habitat Denver continue to grow in the future.

Sabbatical Day 34 – The End

My company, Red Gate software, has given me a 6 week sabbatical. I’m documenting the time with all the posts under a tag if you want to follow along.

Today is the last day of my sabbatical, and a cut short one at that. I leave at 11 for the airport to fly to the UK for business overnight. Not quite what I expected, especially with a trip in the middle over a weekend for work, but it is what it is and I’m OK with that.

Today is actually no woodworking or volunteering. I was up early packing things, trying to find my computer/work stuff and get it in a bag, then dropping my daughter off at a volleyball camp, handling some bank paperwork that had to be notarized, and then heading to the airport.

It’s amazing to think it’s been six weeks. I made it a little over halfway through a woodworking class (more coming next two weeks), volunteered and learned a lot about Habitat, and built a flagpole from scratch.

I’ll review the six weeks on the flight, but it’s been a fun ride.

Sabbatical – Day 33 – Paint

My company, Red Gate software, has given me a 6 week sabbatical. I’m documenting the time with all the posts under a tag if you want to follow along.

This is it for the sabbatical. Technically I have tomorrow, but I fly to the UK and have a few errands to run in the am, so I won’t get a chance to do anything else.

However I did "finish" the pole today in a sense. It has a first coat of paint on it, and technically it could be raised if I had a base.

The day started with more primer. My daughter helped me get another coat on the pole after I filled a few holes and sanded the entire thing down with 220 grit sandpaper. We primed it all over and then headed to the store.

Photo Jul 13, 2 23 45 PM

When we got back, I put a coat on 2/3 of the pole before heading off to work on other chores. I had a lot I wanted to get done, and I forgot a few supplies, but I kept busy for an hour before flipping the pole and putting a first coat on the rest of the pole.

That was it. I had to just cover up the pole and tie it down for a week. I head to the UK tomorrow for a week, so I won’t be able to sand and paint it again until next weekend. Then I’m hoping to go visit a friend and see about getting the base built from metal.

Sabbatical Day 32 – Catchup with Paint

My company, Red Gate software, has given me a 6 week sabbatical. I’m documenting the time with all the posts under a tag if you want to follow along.

I’m almost out of time and I leave Monday for the UK. So it’s another Saturday catch-up day.

I started the day with a trip to the store. I had to go out anyway, so I grabbed a few more sanding belts and got back to work on things.

Photo Jul 12, 1 59 17 PM

Using some new 36 grit sandpaper, I first hit a few rough spots where one of the 3 boards making up a side was taller than others. This went quickly, giving me a relatively smooth surface.

Then I switched to 80 grit and went up and back, flipping the pole after each passage until all 4 sides were done at that level.

Photo Jul 12, 2 00 29 PM

I next went to 120 grit, which is good surface prep. It’s slow going and messy, as I cut lots of wood dust from the tops, along with some epoxy, but as I worked each side, it was becoming more and more of a pole.

Photo Jul 12, 2 01 10 PM

The first two grits also helped me round the top more, breaking and easing the edges on all sides, even where the router didn’t cut deep. At the end, I had a heck of a pole.

 

Priming

I wanted to get moving, so I decided to prime the pole. However I first needed to fill in a few spots, which I did with wood filler.

Photo Jul 12, 2 24 23 PM

Then I started with the first coat of primer. I’m actually thinking to do a couple here, and the plan I had was to do the top and one side, then go back and do the other side and the top again.

Photo Jul 12, 3 18 51 PM

I completed one coat, the flipped it over and did the same thing on the other side. This was all "four" sides get two coats.

Photo Jul 12, 3 34 34 PM

The last coat wasn’t completely dry when weather came in and I had to hurriedly cover things as the rain started to fall.

I need to order my truck now that I know the top size (2 1/2") and then also get more paint. Hopefully I can paint things tomorrow sometime and then start working on the base.

Sabbatical Day 31 – Rounding

My company, Red Gate software, has given me a 6 week sabbatical. I’m documenting the time with all the posts under a tag

Finally, it’s starting to look like a flagpole.

I was hoping my router bit would arrive early Friday, but I actually got lucky and the UPS guy brought it late Thur night as I was heading out with my daughter on an errand. With that in mind, I got up Fri morning and started to work.

A Setback

I was prepping things in the morning and realized I had a problem.

Photo Jul 11, 2 12 37 PM

My variable speed router has these wings for a collet and they prevent a large bit, such as this one, from being used. I have another router, but it isn’t a plunge, nor variable speed, and with a bit this large, you want it spinning relatively slow.

I checked a few sites and many people have recommended just cutting these off. I’ve never used them in the 4 years I’ve had this router, so I decided to do just that.

Photo Jul 11, 2 13 04 PM

I grabbed my Dremel tool and added a metal cutting bit. I put the plunge base in a vise and went to work. Having cut metal before, I learned to put on long pants, long sleeves, get gloves, and use a hat with eye protection. My full face plastic visor was handy. It was slow, but I cut the wings off.

Photo Jul 11, 2 26 38 PM

Now I was ready to work.

Rounding

The idea was to take the 45 degree cuts I’d made on each side and apply the roundover bit to create a pole. I set up the router with my bit and started at the top of the pole.

Photo Jul 11, 2 56 06 PM

I went to work, cutting down each side and back up the other.

Photo Jul 11, 3 02 40 PM

With a couple of trips, things were looking good.

Photo Jul 11, 3 03 54 PM

I flipped it and managed to rout all sides. One wasn’t quite a clean cut as I think I cut a little deep with the circular saw, but overall things looked good.

Photo Jul 11, 3 23 52 PM

Wow, a pole.

I started to sand the whole thing with 80 grit on the belt sander, but weather came in with some rain drops, so I bailed.

Administering Securely

This piece was originally published on Jul 8, 2009. It is being re-run as Steve is away on sabbatical.

The other day I noticed a post where someone was asking a question that I’ve seen asked often. I still haven’t seen a great solution, and I expect this question will continue to be asked for some time to come. The poster asked how to securely set up a database to prevent administrators from accessing the data.

Prior to SQL Server 2005 this was pretty much impossible. The sysadmin group was considered to be like a god inside the SQL Server and could access most anything. With 2005 there are more restrictions you can place on the administrator, and with the addition of encryption capabilities, you can prevent casual access to certain data.

However it’s not easy, and most of the time the system administrator still has access to data for tuning, troubleshooting, disaster recovery, etc. That makes sense sometimes, and I feel that you really have to trust your administrator with a lot of responsibility and discretion. Sometimes, however, it’s just not appropriate for the person that runs the server to see other data. Salary information, among other data, sometimes just isn’t the business of the administrator.

I don’t know how you handle this. Does the administrator just get access to the database as a container, able to attach it, back it up, restore it, and perform basic functions? Are they limited to setting security for users, but unable to access the objects themselves? I’m not even sure that’s a valid way to handle things since the administrator can always set up a dummy account for themselves, or change someone’s password and access data.

To me the best solution for secure administrator access is to have a second person audit all actions performed by a sysadmin. Kind of a default trace for sysadmins that is always running, and is not accessible to the administrator. Even then, it’s probably only something that works in larger companies where you have enough people to dedicate to the task of reviewing things. Maybe policy based management (PBM) will help here at some point, limiting the access to data by administrators in an easy to understand manner that a manager of some sort can understand and audit.

There’s no good solution now other than to trust your administrators to responsibly manage data, and that means hiring responsible people for the job.

Steve Jones

 

Follow

Get every new post delivered to your Inbox.

Join 4,513 other followers