 |
Blog
|
|
|

|
|
 |
Blog |
 |
| -->
 |
January 9th, 2012
Recall back in January 2011 that I profiled the cool tool, FineBuild for SQL Server, which helps you configure and quickly deploy installations of SQL Server. Not only does FineBuild help you install a new SQL Server using the settings and configuration options you desire, it goes a few steps further by ensuring that any additional programs, tools, and utilities are also installed.
Ed Vassie, the creator of FineBuild, has revised and improved the tools since I last wrote about it. Ed has naturally added a number of bug fixes and documentation improvements. Several new features… [READ MORE]
Tags: CodePlex, DBA, SQLMag, Tools, Tools & Scripts Posted in Administration, SQL Server, SQLMag, SQLServerPedia Syndication, Tips & Tricks, Tool Time | No Comments »
January 6th, 2012
Looking back on 2011, I’m surprised by two occurances. First, I got a lot of work done, despite myself. My biggest obstacles to high-performance are all self-derived; procrastination, disorganization, and plain ol’ laziness. Second, I’m surprised I survived my personal travails. I’ve had my fill of frowns this year, from wayward children to caring for sick loved ones to self-inflicted injuries in 2011. I’m glad to be closing the door on a few of those chapters and look forward to better times in 2012.
Here’s a run-down on my professional activities over the course of 2011:
- Articles: 3
- Conference Spoken: 14
- Customer Calls: 124
- Customer Visits: 7
- Magazine Columns: 14
- PASS Chapter Presentations: 12
- Pre-cons/Full-day Seminars: 7
- SQL Saturdays: 4
- SSWUG Sessions: 8
- Webcasts: 16
Plus, I got to got on an awesome SQLCruise and was featured on Richard Campbell’s RunAsRadio show at least once (Richard’s blog | twitter). (I was thinking that I’d been on twice in 2011. But that other appearance may have been in late 2010. My records aren’t clear.)
I was also put in charge of the SQLServerPedia portion of the DBPedias sites. Some statistics there:
- 133 contributing bloggers
- 4,500 blog posts added in 2011 (out of a total 9,000 blog posts)
- 57,000 content items added in 2011 (out of a total 138,000 content items)
- Monday-Thursday all Pedias average 8,700 visits combined
- 1.9 million visits in 2011 (out of a total 3.8 million visits to Pedia sites since SQLServerPedia was started in 2008)
2011 was also my year to jump into Twitter. By years end, I had accumulated:
- 3,452 Tweets
- 531 Following
- 2,656 Followers
- 230 Listed
I think that my increase in tweets had a direct correlation on my decrease in blog posts. Ironically, I have accumulated even more topics to blog about (I’ve somewhere around 630 nascent blog posts), but simply run out of time to put them into WordPress. My blogging activity for 2011 was down to 77 entries, about half what I wrote in 2010.
I’ll talk a little about my plans for 2012 in another post. I hope to see you following me on Twitter soon! Thanks,
Kevin
Tags: Career, Professional Development Posted in Conferences, Interviews, Presentations, SQLMag, SQLServerPedia Syndication, TCD blog post | No Comments »
January 2nd, 2012
I do a lot of public speaking over the course of the year at many different conferences and events. I always try to carve out time during and after the presentation to take questions from the audience. While many of these questions are de riguer, I often get questions that can only be described as “How do I handle this … <insert IT horror story here>?”

These stories often turned out to be more interesting than the question or the answer in and of themselves. For example, it’s a common public speaking best practice to repeat a question back to the attendee. This helps ensure that you fully understood the question and, in case of a session recording that’s picked up only on the microphone, that the question is also recorded. But when you’re immediate response, as the speaker, is “Your manager told you to do WHAT?!?”, you know you’ve hit a zinger, as in “Your manager told you that backups aren’t important?!?”
These stories came to be so fun, in the time-honored tradition of slowing down to carefully examine a car wreck on the highway to the point of clogging all other traffic, that I started to make IT Horror Stories a part of my regular presentation portfolio. And I never have to repeat myself since something new and horrible aways seems to be happening and, in many situations, conference attendees specifically seek out these sessions just so they can air their grievances.
Want to share your IT Horror Story? I’ll give you a free eBook for any that I post here!
In our first installment of IT Horror Stories, I bring you a little lesson from my friend and coworker, Richard Douglas (blog | twitter), a SQL Server enthusiast living in the Maidenhead UK region. Richard writes:
The background story is that I was in a meeting with a few managers and they announced (as they tend to do) that in 20 minutes they were going to start UAT’ing on a machine I hadn’t heard of (let’s call it PC101) I asked what this
machine was as it wasn’t listed on my last estate audit using MAP (Ed: the Microsoft Assessment and Planning too, found here. I wrote it about on my SQL Server Pro magazine Tool Time column).
The manager told me that it was just a PC not a server with one spindle and only 2GB of RAM on Win 7 32bit OS to hold a suite of databases with a total size of 300GB with TDE enabled to boot – and they were going to be doing user testing on this!!!

I told them there was no way that this machine was going to be usable and the users would take a bad view of the new features because of the poor performance. So I was given the challenge of doing what I could to improve performance - in 15 minutes.
Straight away, I rushed over to desktop support to see what spare machines they had lying about. Luckily, they had some spare machines for new starters. So I managed to grab a bit of extra RAM and a hard drive from another machine. We had trouble attaching the extra drive into the machine. It just wasn’t going to fit. So we ended up putting the drive on top and taped it on so it wouldn’t get knocked. All the log files were moved to the second drive to try to eliminate some of the disk contention and we also added a USB flash drive to make use of Readyboost.
Of course, the users still complained about performance. But I like to think that we helped things a little and it’s a great story of British ingenuity!
Manager: Of course we can get top-of-the-line performance with a little PC under a desk somewhere with minimal RAM, CPU, and IO capabilities.
ITPro: Are you kidding me? We might’ve been able to make it fast if we’d done a little planning beforehand. But this is rolling out RIGHT NOW!
Manager: Well, see what you can do with it.
ITPro: Ok. What can I spend to upgrade components?
Manager: Nothing.
ITPro: Gurgle… < Makes clutching motion at throat as if dying>
Follow me on Twitter! Enjoy,
-Kev
Tags: IT Horror Stories Posted in IT Horror Stories, Performance, SQLMag, SQLServerPedia Syndication, TCD blog post | No Comments »
December 8th, 2011
One of the things that drives me crazy as I’m getting older is that my brain is losing the capacity to differentiate version numbers. As I speak travel around speaking with customers and at conferences, I find my self saying things like “I can’t recall if this problem was fixed in SQL Server 2000 or 2005. But you don’t have to worry about that any more.” Or things like “That feature was added in SQL Server 2008 R2, eh, or was that version 6.5. DOH!” followed by a loud slapping sound as I whap my palm into my forehead.
The Internet doesn’t forget either. Recommendations that were once helpful, if not outright essential, now are neutral or even downright bad. So now, whenever I put together new presentations, I always spend a lot of time in research, reassessing my knowledge on the topic. (That doesn’t mean that I’ll extemporaneously say it wrong, because I speak in an off-the-cuff and rapid style. But at least my notes are usually correct).
Case In Point
Take backup and recovery (B&R), for example. I’ve been writing about and performing B&R for years. I’m even part of the team that builds the most popular B&R tool in the SQL Server space. It’s an extremely important part of what I do. And yet, even after spending a couple days re-researching topics (yet again), I still manage to get a few of the more specific details wrong because of changes over the years in the feature set. How so?
During an Expert’s Perspective webcast last week (and available on-demand) covering the top backup and recovery mistakes on Microsoft SQL Server, I mis-explained the details concerning how SQL Server performs a differential backup. A very clever SC on our team who was also attending the webcast pointed out to me after the session that I’d described differentials working at an 8k page-level when, in fact, they work at the extend-level (that’s a block of eight 8k pages). #FACEPALM!
When describing the differential backup I said that a bit was flipped on each page header and SQL Server would only back up those pages. Instead, whenever a page is changed a bit is recorded on the differential change map (1bit per extent), the backup process then queries these map pages and backs up those extents that have been marked as changed.

The Research Reveals
The cool revelation here is not that I can admit my mistakes. The take-away from this blog post are all the great articles I read writing my latest slide deck. These blogs and articles were so good that I needed to spread the word. Add these to your must read list:
Virtual Log Files must be tamed!
Backup and Recovery Myth Busting
Other Good Sources
And just in case you need a refreshing in the basics of SQL Server backup and recovery, check these out:
Tags: Backup & Recovery, DBA, Quest Software, SQL Server 2008 R2, Video Posted in Administration, Challenges, Databases, On-Line Resources, Presentations, SQL Server, SQLMag, SQLServerPedia Syndication, Tips & Tricks, Video | 1 Comment »
November 2nd, 2011

I hate to admit it, but I’m a hoarder. Yes, like those insane people on the A&E TV show. Only my hoarding is all virtual. For example, take the image above. That’s just a tiny part of my Google Reader home page. That’s a tiny part of my Google Reader home page AFTER reading most of the day on a recent Sunday. I still had thousands of entries to go after hours of reading. On top of that, I subscribe to some technical e-newsletters. I’m a member of quite a few LinkedIn discussion groups, each of which produce daily and weekly newsletters. (I only subscribe to the weekly versions). Then there’s Twitter too. In all, there are THOUSANDS of interesting floating through the ether which I’d been trying to collect and, occassionally, read. Bah humbug!
The Downside of an Information Hoard
You might think that, as an IT professional, hoarding information is good for you. After all, the more you know, the more effective you are at your job, right? Uh – no. I’ve discovered a few downsides to keeping all of this information around. First, I lose time on administrivia, uh, I mean administration. I can’t think of a time when a search on Google has not produced the information that I’m interested in. So by spending time keeping up with my blog feeds, adding new blog feeds, deleting old ones, and so on, I lose time administrating something that really doesn’t need to be administrated.
Second, there’s the time it takes to read all of these entries – many long hours to read thousands of entries per month. I’ve actually figured out a little trick to make this go a lot faster. What’s that trick? Well, previously, I used to start reading my entries and then clicking “Next Entry” each time. Even when I skipped a lot of entries, just slogging through them all was a big time drain. Now, I simply select large swathes of entries that I’m not interested in and click “Mark as Read” without ever opening them. Works great! I’ve also gotten a lot more aggressive about dropping bloggers and RSS feeds that offer low value. A lot of bloggers have popped up who only recount things which are available in Books On-Line. Why spend any time on that at all? I’m looking for strong insight, experiences, and analysis – not simple technology facts. (A follow on thought to this tip is that “Bloggers Should Write Meaningful Article Titles!”)
Third, and more importantly, information hoarding takes a big emotional toll on me. Maybe it’s a factor of just how my brain works and is completely inapplicable to you. But in my case, I always carry a subtle nagging feeling when I have unread entries in my various accounts. Even when I know that these entries are optional and that it’s not necessary for me to read any of these things, I still feel like I should read them. Basically, it makes me feel like I should be working all the dang time and that makes me feel anxious. Anxiety makes me less productive and more prone to burn-out. And anxiety bleeds through my work hours into my home life. It makes it hard to enjoy a movie with the kids or some gardening in the yard. All because there’s that feeling that I haven’t gotten the hoard processed yet.

The Solution to the Information Hoard
I mentioned a couple specific techniques for thinning out the hoard in paragraph two. To summarize, first, subscribe to only those bloggers, feeds, and newsletters which add actual understanding to your life. Second, open and read only those entries that matter and skip the rest. Those are techniques for dealing with lots of entries to read. But my last problem, the anxiety issue, was a little bit harder to solve.
Like a lot of internal ways of handling life’s problem, the answer is simple but not easy. Similar life problems with simple but difficult solutions might include feelings of guilt (the solution is confession) or anger (the solution is forgiveness, either of yourself or for the other party). So what’s the solution to anxiety? Here’s my thought process – the answer to my information hoard is about values. What do I mean? I value these various things because they make me better at my job. Losing things of value causes me some anxiety. My anxiety has its roots in the feeling that I’m letting things of values (these various blog entries) slip through my hands. Why would you ignore things of value, or even worse, get rid of them?!? On the other hand, if you asked me what I really and truly valued most in this world, I’d answer with “my family and loved ones”. But again, do I actually demonstrate this priority with my time? Not nearly enough. Therefore, the answer is to properly appraise the value of my time. When I think about it rationally, I think that this sort of reading is worth about 30 minutes per day, maybe a full 60 minutes when there’s something really important to learn or someone really significant to listen to.
That’s it. After 30 minutes, I can walk away from any sort of reading guilt-free. So what’s my new solution to the information hoard and the anxieties that it’s been producing? A quick check on the writers and topics I care about the most and then “MARK ALL AS READ”. Yes, there will be more to read tomorrow. But now I no longer carry an ever increasing load saying to myself “Someday I’ll get to that”. I feel better already.
Feedback Requested
What’s your strategy to dealing with your information hoard? Are you still keeping everything you ever produced or read digitally? Does my approach sound reasonable and workable to you? Or am I off base?
Thanks!
-Kev
-Follow me on Twitter
Tags: Career, Plays Well With Others, Productivity Posted in Challenges, Opinion, Professional Development, SQLMag, SQLServerPedia Syndication, Tips & Tricks | 1 Comment »
October 31st, 2011
When you’re developing new Transact-SQL code or modifying some existing code, do you just launch directly into programming?
I know that I did just that, for years. It wasn’t until I was trying to performance tune some existing code that I realized I hadn’t actually taken caching of data and execution plans into account. So all those modified stored procedures that I was so proud of might not actually be faster than the first generation of procedures because I hadn’t checked to ensure that I was testing cached programs against uncached programs (and, by extension, the data used by those programs). That’s easy enough to fix with a test harness. Test harness were originally an actual, physical harness used by engineers to clamp down parts of an electrical or mechanical device they were prototyping. Ours is no different. It locks down all of the assumptions about our code (like my early, false assumption that I didn’t need to clear the caches) and adds a metric or two for good measure – literally – so we can better measure what’s happening in that code.
Here’s what my test harness looks like:
/* Transact-SQL test harness by Kevin Kline, http://KevinEKline.com, Twitter at kekline */
/* Flush dirty pages from the buffer to the database files. */
CHECKPOINT;
/* Flush the data cache and procedure cache, respectively. For DEV environments only! */
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
/* Enable statistics tracking for IO and timings. Remember, SET commands remain enabled during a session until disabled. */
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- Whatever SQL code you'd like to process goes below.
SELECT SalesOrderID
FROM Sales.SalesOrderHeader H
WHERE CustomerID = 344
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
/* Textual Execution Plans, if desired.
SET SHOWPLAN_TEXT ON;
SET SHOWPLAN_TEXT OFF;
*/
I also like to include the execution plans a lot of the time. You might wonder why I don’t save the execution plans for the GUI in SSMS? Well, I’m a big advocate of scripting in general because I like to automate activities. By pulling the execution plans using scripts, I can use SQLCMD to schedule a large number of query executions during the evening and have the results ready for analysis when I come back into the office in the morning. Workin’ smarter, not harder, Baby!
So how does this test harness work for you? Do you use other elements in yours? If so, share your experiences here!
Thanks,
-Kevin
-Follow me on Twitter
Tags: DBA, Developer, Performance, SQL Server 2008 R2, Tools & Scripts, Transact-SQL Programming, Troubleshooting Posted in Databases, On-Line Resources, Performance, SQL Server, SQLMag, SQLServerPedia Syndication, TCD blog post, Tips & Tricks, Tool Time, Transact-SQL Programming | No Comments »
October 27th, 2011
Data files containing comma separated values, or CSV, are some of the most common data formats used for data representation and storage outside the database. When it comes to loading CSV data into the database, many options exist, however, few make it as simple as CSVexpress, powered by expressor software. I recently visited www.csvexpress.com to check out just how simple it could get. In short, CSVexpress offers a repeatable and quick way to load any CSV file into SQL Server (or any other database). For those whose data quality is not as pristine as it should be, CSVexpress also offers a wide variety of built-in functionality to repair the data issues. These are in addition to the data transformation components available out of the box, but let’s not get ahead of ourselves.
The first thing I notice when I visit CSVexpress is that there are some video tutorials available on the main page. I found it pretty straightforward to load a CSV file into the database without watching the tutorials beforehand. However, by watching the tutorials I was able to learn more about some neat features and functions that I had not previously noticed.
For my test, I grabbed a simple CSV data file containing the following data:
City,User_ID,Name,Street_Address,Status
“Dallas”,47,”Janet Fuller”,”445 Upland Pl.”,”Trial”
“Lyon”,38,”Andrew Heiniger”,”347 College Av.”,”Active”
“Dallas”,43,”Susanne Smith”,”2 Upland Pl.”,”Active”
“Berne”,22,”Bill Ott”,”250 – 20th Ave.”,”Active”
“Boston”,32,”Michael Ott”,”339 College Av.”,”Trial”
“New York”,41,”Bill King”,”546 College Av.”,”Deleted”
“Oslo”,45,”Janet May”,”396 Seventh Av.”,”Active”
As you can see from the diagram below, the import of the data to create a schema was not difficult at all:
Once the schema is configured, I can create the following simple data flow to move data from my CSV input file to my target table in SQL Server:

While there are other tools available for performing similar tasks, CSVexpress makes it very simple and intuitive. However, as I mentioned earlier, where it starts getting really interesting is when you need to pre-process and clean-up the data prior to loading it. Whether it involves enriching the data from external data sources or web services, or identifying and repairing bad data, CSVexpress maintains a simple interface for all of that.
The best part – it’s all free of charge. The version you can download from www.csvexpress.com is expressor’s free Community Edition. expressor also offers a licensed Desktop and Standard Edition with even more advanced features, which are available for a 30-day trial. As a matter of fact, at the end of November, expressor will be introducing Salesforce support into their commercial editions and CSVexpress will feature a 30-day trial version. You will be able to load Salesforce just as easily as if loading to SQL Server, or download your Salesforce contact, lead, and opportunity data and transform it before generating the right CSV output file (or files) that meets your daily, weekly, and monthly Excel reporting and analysis needs. Now that’s easy!
Tags: Data Cleansing, Data Quality, DBA, ETL, Master Data Management, SQL Server 2008 Posted in Administration, Cool Technologies, Databases, SQL Server, SQLMag, SQLServerPedia Syndication, Tips & Tricks | 1 Comment »
October 26th, 2011
In light of the 99% and “Occupy Wall Street” movements, the Great Recession, historic levels of partisan rancour, terrorism, environmental havok, and continuous global warfare, I am reminded of the great Mahatma Gandhi in “Young India”, 1925, and his Seven Social Sins.
Who can point to a list of social sins better than this:
Politics without principles
Wealth without work
Pleasure without conscience
Knowledge without character
Commerce without morality
Science without humanity
Worship without sacrifice
It seems like our society and our daily news broadcasts depict a nation whose actions can ONLY be described in light of this list.
This sort of wisdom reminds me to be humble, and to seek the greater good for myself and my community, at each new opportunity.
What does this list make you think of?
-Kev
Tags: Ethics, Opinion Posted in Ethics, Opinion | 1 Comment »
October 21st, 2011
I’d recently experienced a situation where I was getting repeated login failures to a SQL Server where I knew that I had the correct user name and password. Each time, I’d get error 18456 in response.
“Login failed for user ‘<user_name>’. (Microsoft SQL Server, Error: 18456)”.
The challenge when troubleshooting this error message is that you may have a problem with SQL Server or you may have a problem with Active Directory or Kerberos, if you’re using one of those authentication technologies.
A False Trail
When times are good, and you’re able to make a connection, you can always query sys.dm_exec_connections, to see how you’re connecting, for example, using NTLM rather than Kerberos. But that doesn’t help us when we can’t connect to the server at all.
In other situations, your problem might be caused by duplicate SPMs in Active Directory. MVP Russell Fields documented a nice solution for ridding Active Directory of duplicate SPNs here. Microsoft Support also mentions some troubleshooting steps forauthentication problems here. Ok, that helps. But it’s not my solution.
MVPs to the Rescue
Fortunately, my MVP buddies Edwin Sarmiento (blog | twitter) of Canada and Bitemo Erik Gergely (blog) of Hungaria had already discussed and solved the problem for me!
Something Erik pointed out, but hadn’t occurred to me at first, is that if you’re getting this SQL Server error message then you’ve actually reached the server and probably aren’t having a full disconnect error. The second thing that Erik pointed out is the importance of the state element of this error message. A lot of the time, you can simply ignore the
state element of an error message. But not this time. As it turns out, state is the key to solving the problem. For example, a state of 18 indicates that the password must be changed.
Microsoft provides a pretty good description of the states of error 18456 here, but it leaves out a few things. (You’ll get more useful info if you read all of the comments too). But
again, Erik comes to the rescue by providing a complete and concise list of error 18456 states here.
Microsoft Improves the Documentation
Even better for all troubleshooting situation involving state information, Microsoft has now added state descriptions for errors in Books Online, including error 18456. When you look in your SQL Server error log, you see the state of the error and be able to make an accurate deduction about the nature of the error!
Hope this helps,
-Kev
-Follow me on Twitter
Tags: MVP, SQL Server 2008 R2, Troubleshooting Posted in Administration, Challenges, Databases, On-Line Resources, SQL Server, SQLMag, SQLServerPedia Syndication, TCD blog post, Tips & Tricks | No Comments »
October 14th, 2011

First things first, Wayne Snyder is rolling off the board of directors for PASS this year. We’d worked together, shoulder to shoulder along with Joe Webb (blog | @joewebb) and other outstanding members of the SQL Server community, for many years of on the PASS board of directors and I’m certain that my tenure on the board and as president of the organization would’ve been nothing but trouble had Wayne not been there, covering my blind side(s), at every turn. Here’s my tribute to Wayne Snyder:
If you were to mention “Wayne Snyder” to me, I’d instantly start to grin and, probably, nod a little bit. Wayne is the kind of leader who always comes to mind with overpowering and emotional warmth. Sometimes when you visualize a memory of a person, you see them in your mind’s eye stooped over a console deep in thought or pontificating at a meeting somewhere deep in corporate America. But when I recall Wayne, I always see an image of Wayne smiling with his arms out wide as if he’s going to wrap you in the biggest, most comforting, Southern-fried, big brother hug you’ve had all year. And that image is loaded with all kinds of deep positive connotations: supportive, enthusiastic, sincere offer you thoughtful conversation, honest convictions, and straight answers.
To use an analogy, some leaders are only the “thermostat” of their organization – they set the temperature for everyone else. But Wayne was also the “thermometer” as well – he showed what temperature at which our organization was running. And that temperature is warm. As a PASS member, you knew within a heartbeat that it was ok to give a shout-out back to the speaker in a crowded auditorium, that there were no stupid questions, that it was ok to be the one who knew the least in the room because, in fact, he was the guy who knew the least in the room once and here he was to help you become the one who knew the most in the room! I honestly can’t count the number of people who Wayne recruited into the ranks of PASS simply by being Wayne.
Thank you, Wayne, for your many years of service to our community. And thank you most of all for acting as the wellspring of our communities exuberant, uplifting, and just plain fun attitude of embodied in our motto of “Learn. Grow. Share”. No one does it better than you.
Now, it goes without saying that Dr. Dewitt’s keynote is one of the singlemost anticipated sessions of the entire event. Why? As Dr. Dewitt mentions himself, the hallmark of his sessions are a semester of graduate school IT learning distilled into one hour of awesomeness. There are lots of great resources discussing NoSQL on the internet (and I’ve pointed out a lot of them in the past). But who wouldn’t rather leapfrog months of on-the-side research learning about NoSQL by enjoying Dr. Dewitt’s keynote? Watch the streaming video at this SQLPASS link.
And if you’re here at the PASS Summit on Day 3, I hope to see you in my two sessions this afternoon:
Crash! Boom! Bang! 10 Ways to Blow Up Castle SQL Server and the Techniques that Catch Them (DBA-318)
Enterprise Database, Administration and Deployment, Regular Session (75 minutes) in 3AB
Are you a Linchpin? Career management lessons to help you become indispensible. (PD-200)
Professional Development, Regular Session (75 minutes) in 4C4
Follow me on Twitter!
Tags: FutureWatch, Hadoop, MapReduce, SQLPASS Posted in BigData, Conferences, Databases, NoSQL, PASS, Presentations, SQLMag, SQLServerPedia Syndication, TCD blog post | No Comments »
|
|
 |
|
|