SQLSaturday #29 Give-Aways

March 30, 2010 Leave a comment

BigEarBunnyAward_2010_03_27.jpg Actor and comedian Jerry Seinfeld once quipped “According to most studies, people’s number one fear is public speaking. Number two is death. Death is number two. Does that sound right? This means to the average person, if you go to a funeral, you’re better off in the casket than doing the eulogy.”

I used to be in that majority. I used to fear public speaking but then I realized that I’m really just getting together with some other people and chatting about something that we all enjoy.

Thats why I really love a very interactive crowd; it’s more like a conversation than a monologue. I’d much rather speak to a group of folks who actively listen, who ask questions, and who bring up new ideas.

So when I talk, I look for opportunities to solicit audience participation. For example, at SQLSaturday #29 in Birmingham, Alabama, this past weekend I awarded prizes. The prize? A “Bunny Big Ears” to represent their active listening and then asking a good question. Congratulations to the two people who received them!

The other prize, though admittedly not quite as nutritious as Bunny Big Ears, that I promised to give-away was a free copy of Windows 7 Professional. Just as I did with the sessions, I wanted to reach out and engage the Twitterverse for the prize. So rather than selecting the winner from a hat at the end of the sessions, I randomly selected a winner from the comments left on my blog post.

And the winner is: Michael Cherry!

Congratulations Michael! I hope you enjoy the software.

Filed under Community, Events, Social Media

SQL Saturday #29 Presentations & Twittering

March 26, 2010 21 Comments

presentations_2010-03-26.jpg

Tomorrow, I’m presenting a couple of sessions at SQLSaturday #29 in Birmingham, Alabama. Here are the presentation materials I’ll use.

Reaching Out to the Twitterverse

While I was in Seattle last fall for the PASS Community Summit, I saw a couple of presenters effectively reach out to the Twitter community while giving their sessions. They had someone tweet important points for them as they presented their session. Then, as the Twitterverse posted questions in response, the designated liaison would voice them and tweet the presenters answers. It seemed to work out really well.

So, I’m considering trying that for tomorrow’s sessions – if I can find an attendee who doesn’t mind acting as the liaison.

Question: Would that be of interest to you? Would you like to follow some of the sessions for tomorrow’s SQLSaturday via Twitter?

I’ll let you know what the Twitter tag will be if it works out.

I hope to see you there, or at least hear from you via Twitter during the session.

Filed under Community, Events, Optimization, SQLServerPedia Syndication, T-SQL

Adding a Column to Every Table

March 24, 2010 8 Comments

Robot_2010_03_24.jpg

I am Script. I will update your database.

Despite the best laid plans, sometimes circumstances or project scope change and the definition of one or more tables in your database must be altered after they have been created. It’s just a fact of life for a DBA.

Change Happens

Frequently these changes affect more than one database in your environment. The development, test, and eventually even the production databases must have their definitions updated to reflect the newly implemented changes.

For cases like this, I generally prefer to script out the necessary changes rather than using the Management Studio tools. Scripting reduces the likelihood of an unintentionally errant change while updating the 100+ tables in the database. If one database or table is done correctly, you can be assured that the rest are in order as well. That’s not to say you shouldn’t test; you should. But the changes will be much more consistent when they are implemented via a script rather than by hand.

Scripting ALTER TABLE Statements

Recently I was tasked with adding a column to every user table in a database. There were lots of tables in this database and doing it by hand was not a good alternative. Of course if the changes were successful in Dev, they would need to propagated to Test and Production as well. So, I used T-SQL to dynamically create a script for me.

There a number of ways to accomplish this. A cursor or a while loop come to mind. However, since I knew that the new column name did not exist in any of the tables, I could use a shortcut and make use of the sp_msforeachtable system stored procedure. I described its use in a prior blog.

The first step is to use the stored procedure to generate T-SQL code to implement the change. For this example, we want to add a datetime column called Date_Created to each table in the AdventureWorks database. In a new query window, type the following code.

USE AdventureWorks;
EXEC sp_msforeachtable
‘PRINT ”ALTER TABLE ? ADD Date_Created DATETIME DEFAULT GETUTCDATE();”’ ;

When this script is executed, the Messages pane of the query window is filled with a list of DDL (Data Definition Language) statements to implement our change. The output is shown in the following figure.

foreachtable_2010_03_24.jpg

After reviewing these DDL statement to ensure they are indeed what we wish to do, we can then copy and paste them into a query window for execution.

Of course this implementation doesn’t have error handling. If a column by that name already exists, the DDL statement will fail for that table. If you are unsure whether a column already exists, you should use one of the other implementations (cursor, while, etc) so you can check for the presence of the column before issuing the ALTER TABLE statement.

I hope you find this useful. If you have any favorite scripting techniques or comments on this one, please feel free to share in the comments section below.

Filed under SQLServerPedia Syndication, T-SQL

Licensing SQL Server Reporting Services

March 19, 2010 1 Comment

license_please-2010-03-19.jpg

I have my license here somewhere, officer.

Licensing models can sometimes make database modeling seem trivial. Per processor or per seat? Single core, dual-core, multi-core processors? Multiple instances on one server? Virtualizing servers on a single server? Active / Passive clustering? It can get very confusing, very quickly.

Recently, I was asked by a former student about licensing for a SQL Server Reporting Services instance. In the proposed scenario, the Report Server would be installed on one server and the back end database would be installed on a separate, remote server. How many licenses of SQL Server are required?

In short, each server where a Business Intelligence component is installed requires a valid SQL Server license. So, a remote database deployment scenario where the Report Server is installed on one server and the ReportServer database is installed on a separate server requires two licenses of SQL Server.

But don’t take my word for it. Here are a couple of links that will help clarify licensing requirements.

Got any licensing stories you’d like to share? I’d love to hear about them.

Filed under Reporting Services, Resources, Software, SQLServerPedia Syndication

Speaking at SQLSaturday #29 in Birmingham, AL

March 15, 2010 Leave a comment

microphones-2010-03-15.jpg

On Saturday the 27th of March, I’m going to be presenting two sessions at SQLSaturday #29 in Birmingham, Alabama.

  • 1:30 PM: Tips and Tricks for Writing Better Queries. Transact-SQL is not a very difficult language to learn. As long as the syntax is correct, it can be quite forgiving. However to truly get the best performance from your SQL Server, careful consideration should be given to the structure and logic of the queries. In this session, we’ll discuss some Transact-SQL tips and tricks that can be employed to help you write better queries, allowing your server to perform better.
  • 2:45 PM: SQL Server Locking & Blocking Made Simple. A good working knowledge of how SQL Server makes use of locking and transaction isolation levels can go a long way toward improving an application’s performance. In this session, we will explore SQL Server’s locking methodology and discover techniques for enhancing query response times.

If you’re not familiar with SQLSaturdays, you can learn more about them here.

I hope to see you there!

Filed under Events, SQLServerPedia Syndication

My Experience with a Virtual Assistant using TimeSvr

March 12, 2010 22 Comments

Dewey_Defeats_Truman_2010_03_10.jpg

Predicting the future with bold certainty is not for the faint of heart. It’s a hard job. A few get it right; most just get egg on their face.

Want some examples?

  • In 1948, the Chicago Daily Tribune gambled that Dewey would beat Truman in the US Presidential election and ran the presses before the final result was certain, a mistake that literally made front page news.
  • In 1936, the New York Times asserted that “A rocket will never leave the earth’s atmosphere.” Wrong.
  • Tradition attributes “There is a world market for maybe 5 computers.” to Thomas Watson of IBM in 1943. Wrong again.

Finding More Time

In a similar vein, pundits and marketing folks predicted that the personal computer would revolutionize the way we work, that we would get more done in less time, and that the work week may actually shrink to 25 or 30 hours as a result of our increased productivity.

Ha! I wish that was the case! Most people that I know are working longer and harder than ever. There just seems to be much more to do in our professional and personal lives just to keep up, not to mention trying to get ahead.

einstein_2010_03_11.jpg

Hey Buddy, do you have the time?

With so many activities and only a finite amount of time to devote to them, something has to give. You can either stop doing some of the activities. Or, you can somehow find more time. Since most people either really enjoy their activities (leisure) or feel compelled to keep at them (work), finding more time is the more appealing option.

But how to find more time? Unless you get into Einstein’s General Theory of Relativity, time is a fixed asset. Everyone has exactly the same amount of time during the course of a week: 168 hours.

The answer: offload some of your activities to someone else, also known as delegation. But what if you don’t have anyone that reports to you? What if you don’t have an assistant to whom to delegate?

Enter: the Virtual Assistant.

Using a Virtual Assistant

With this in mind, I recently decided to try a Virtual Assistant. This is an industry that has really gained in popularity over recent years. If you’re not familiar with the term, essentially it’s like having a personal assistant that’s on call when you need him. A Virtual Assistant can pretty much do anything that doesn’t require their physcial presence.

There are varying levels of experience and skills available from Virtual Assistant companies. Some are highly skilled and can help you with such activities as keeping your financial books, updating your web page, and even drumming up new leads for your business. Other Virtual Assistants focus on doing the more mundane tasks for you like making travel arrangements and doing internet research.

Since this was my first experience with a Virtual Assistant, I decided to go with the a company that specializes in taking care of the many short duration but very distracting tasks that come up throughout the day.

After a fair amount of research on the internet (a task I would have loved to have delegated to a Virtual Assistant), I discovered a relatively new comer to the business, TimeSvr that repeatedly received great reviews from their clients. I decided to give them a try. I won’t fully restate their service offerings here; you can visit their web site for that. Sufficed to say that for 69 USD, you receive up to 8 tasks daily that require 15 to 20 each to complete.

My Trial Period with TimeSvr

I signed up with free three-day trial period with the company on Monday morning. It took just a few minutes and was relatively painless. Shortly thereafter, I received a welcome email describing the many ways I can submit tasks: the dashboard, Skype, email, and a telephone call are all available.

My First Task: A Clarification

I decided to test out the timeliness of their responses so my first request was very easy. I asked for some clarification on their services. Within an hour, I received a very polite email explaining exactly how they can help me to save time during the day. I was impressed since I know that the company is based on the other side of the world and it was night time there.

My Second Task: Location Information

For my second request, I asked my Virtual Assistant to find the closest UPS drop-off location that would accept a box of a certain size. Within 20 minutes, I received another email providing me with the location address. The email even included the name of the person that my Virtual Assistant spoke with confirming that they’d accept packages of the size I’d indicated.

My Third Request: Internet Research

The third task I asked of my Virtual Assistant was a bit more difficult. I asked him to find some possible course materials for a technical class that I may deliver to a client. I gave him a basic outline with a list of topics that I’d like to cover. I also explained some other preferences I have.

This request took much longer to complete; in fact it took nine hours to for me to receive a reply. I can’t say if the research took that long or if there may have been some kind of technical glitch in their system that prevented my Virtual Assistant from becoming aware of my new task.

I did however submit a fourth task before this one was complete and within one-half hour of delegating that task, this one was also completed.

My Forth Request: Internet Searching

My fourth request was very straightforward. I asked for a recipe for cooking pizza in a Dutch Oven. I made it easy for them by providing some potential locations for the information.

In half an hour, I received links to several recipes, along with a link to some instructions for cooking with Dutch Ovens. A nice touch, I thought.

Parting Thoughts

Overall, I was very impressed with the timeliness and quality of services that I’ve received from TimsSvr. Every person I’ve communicated with has been very professional and pleasant.

I’ve only used the Dashboard to submit tasks so I cannot comment on the other means. However the Dashboard has proven very convenient. It even has a good mobile front end page too.

70 USD isn’t cheap, but for what you get from TimeSvr, it’s worth it. The way I see it, if it saves me one hour a month the service has more than paid for itself.

As an aside, I didn’t receive any free compensation for this review. However my Virtual Assistant did have a hand it helping me to put it together. I asked him to find the pictures for me and to find me a list of predictive quotes that later proved to be false. Not bad, eh?

Oh, and one more thing. I’ve referred through this post to my Virtual Assistant. In actuality, it’s a team of people that field my requests. You can, if you prefer, actually contract with a specific individual within TimeSvr. I may consider that one day but for now this arrangement meets my needs.

Now some questions for you:

  1. Have you used a Virtual Assistant?
  2. Would you be comfortable using a Virtual Assistant for more complex tasks?
  3. What are some tasks that you’d like to outsource?

Filed under Consulting, Productivity, Professional Development, SQLServerPedia Syndication

SQL Server Locking & Block Scripts

March 8, 2010 1 Comment


A couple of weeks ago, I gave a presentation to the PASS DBA Virtual Chapter on SQL Server Locking & Blocking Made Simple.  The original post had a link to the presentation materials, but I neglected to attach the demo scripts. So, here they are:

To review them, click on the link and open the Microsoft Word document. (For some reason, my blog site won’t allow me to post text files.) Copy and paste the contents of each file into a new query window in SQL Server Management Studio and experiment all you wish. You’ll need the AdventureWorks database; it’s available for download on CodePlex.

If you have any questions or comments, feel free to post them below in the comments section.

Filed under DMVs, Optimization, SQLServerPedia Syndication, T-SQL

Mitigating Consulting Risks

March 2, 2010 5 Comments

Hazardous_Bridge_2010_03_02.jpgIn my last post on consulting, Isn’t Consulting Risky?, I contended that in many respects, when done with sufficient forethought and planning, consulting may be even less risky than a traditional full-time job. I know that sounds counter intuitive and I hope to elaborate and clarify that a bit in this post. But before I do, if you haven’t read the prior post, I’d encourage you to do so; it really lays the groundwork for how consulting affords you the ability to mitigate risks.

Diversity Is Good

With few exceptions, diversity is widely acknowledged to be a good trait in most cases. Ecosystems with a wide range of plants and animals tend to do better and be more resilient in the long term than large monoculture farms or environments. The same is true for retirement accounts; financial advisors recommend a diversified portfolio because it tends to outperform, on average, an account with just a few stocks or bonds. And in business, a well functioning team composed of people from many different backgrounds usually provides much a greater insight and a broader perspective that produces better results than a very homogeneously composed team. Simply put, diversity is good.

And what’s true for nature, finance, and management teams is true for your consulting business, too. A consulting practice with multiple clients and sources of revenue tends to weather economic storms better than those businesses whose sole focus is one client or one type of revenue.

Diversity Doesn’t Just Happen

Diversity usually doesn’t just happen. It’s not something that tends to naturally occur in businesses. Diversity must be planned. It must be intentional. And it must be nurtured.

Left alone, businesses naturally tend to gravitate toward those one or two things where success is found. It’s the path of least resistance. But as renowned management consultant and author Tom Peters once quipped “Success begets failure.” His point is that when we succeed in one area, we tend to stop innovating and begin to try to reproduce that success over and over again.

I think the same can be said for consulting. When we find a niche that works, we tend stay in that niche and ignore other opportunities. And I think we do this at great risk to the longevity of our business. But what happens when that niche is no longer a safe and sure bet? There are plenty of examples; all you have to do is look for them.

A Risky Business

Think back a few years when technical training companies were thriving. There were scores of them in every major market in the U.S. They were offering training to IT Professionals and to Career Changers. It was a great time to be a Microsoft Certified Trainer (MCT). MCT’s were in high demand. Basic economics teaches us that high demand plus low supply equals high prices. Many consultants became MCTs and focused their business exclusively on the highly profitable training business.

Unfortunately, the economic phenomenon known as the dot-com bubble burst. Many training companies went belly up as the demand for training evaporated. And those MCTs that were bringing in the big bucks all of a sudden found themselves to be a commodity as their colleagues where willing to take less and less for their services.

A similar story can be told about all of the COBAL programmers in the lead up to the Y2K. And there are other examples.

So this begs the question, “How can I, being a sole practitioner, add diversity to my consulting practice?”

How Can I Diversify?

Fortunately, diversifying your consulting practice can be done without taking a huge departure from your core business. Diversifying involves looking at your core business from different perspectives and identifying areas where you can implement relatively minor changes to your approach or offerings to broaden your surface area. That way if any one aspect of your business is adversely affected for whatever reason, there are other areas already in place to help you absorb the impact.

Consider some of the following ways to diversify your consulting practice.

  • Work with Multiple Clients. Many consultants work with one client at a time. And that’s fine. However, juggling multiple clients simultaneously provides you with a buffer should any one client default on their invoices, unexpectedly cancel your agreement, or otherwise give you the old heave-ho with little advanced notice.
  • Vary the Types of Projects. Establishing good and long term relationships with a handful of clients and mixing in a string of new, shorter term projects or engagements from new or former clients allows you to maintain a good mix of projects. Some may want you to come in and diagnose a performance problem, others may want you to provide ongoing support and/or maintenance of their systems, while still others may have a long list of things for you to do when you have the time.
  • Offer Complementary Services. Consider broadening your offerings to include technical training, development, mentoring, and technical auditing. These give clients different ways to leverage your core competencies.

Now it’s your turn.

  1. Do you agree with the premise of diversifying your consulting practice?
  2. In what ways have you mitigated some of the risks associated with consulting?
  3. How have you broadened the offerings of your consulting practice?

Filed under Consulting, SQLServerPedia Syndication

Follow

Get every new post delivered to your Inbox.