My Top 10 PASS Summit Tips

Next month I will attend my third PASS Summit in Seattle.  I previously attended the Summit in 2006 and 2008.  This year I am volunteering as part of the PASS Orientation Committee.  Each member of the Orientation Committee has a group of 8 “first timers” that were randomly assigned to them.  The idea is that we will reach out to these folks, help them get a feel for the conference, and (hopefully) enhance their conference experience by putting them on a path to getting to know other attendees, answering questions they might have, etc.  I thought that the Orientation Committee was a great idea as soon as I read about it.

I am about to reach out to my group to introduce myself and thought it would be nice to have something to point them to before actually meeting them in November.  I decided to write up a top 10 list of Summit tips (some specific to Seattle Summits).  Here they are:

1. Use the Gray Line Seattle Downtown Airporter to get from the airport to your hotel.  The bus goes from the airport to downtown Seattle and drives around to all of the various hotels.  You just jump out at your hotel.  At $25 round-trip, the price is right.  There is also a good chance that you will bump into a fellow PASS attendee or two on the bus.
2. Bring your walking shoes and explore downtown Seattle.  I found a nice walking tour guide of the area on an MSDN blog before I went in 2006.
3. Arrive as early as you the day before the conference starts.  Use that first afternoon to do any tourist type stuff you might want to do while in Seattle.  In my experience, there is not much time or energy for that type of thing once the conference gets going.  The conference days are quite long and draining.  Evening social events will eat up most remaining time and energy.
4. Talk to the sponsors.  While I cannot vouch for all of them, I have met some very cool folks by way of chatting with the PASS Summit vendors.  In particular, seek out the folks at the Redgate and SQL Sentry booths.
5. Bring business cards.  Many DBAs do not normally think to bring business cards with them.  However, swapping cards with someone after an interesting chat in the hallway is much, much easier than jotting down contact information on your notebook.  You will very likely meet someone at PASS with whom you want to exchange contact information.
6. Get on Twitter if you are not already there.  The PASS Summit is huge on Twitter.  Follow the main hash tag #SQLPASS while you are at the conference for news, insights, and entertainment.  If you are new to Twitter and want some tips on how it works, check out Brent Ozar’s free Simple Twitter Book.
7. Put some thought into which sessions you plan to attend ahead of time.  There are quite a few to choose from at every time slot.  The fifteen minute break between sessions is not the best time to decide where you are headed next.  Take the schedule builder for a spin and narrow your decisions down a bit ahead of time.
8. When selecting sessions to attend, step out of your comfort zone now and then.  While you certainly want to pick up some information that you can apply to your job as soon as you return, the PASS Summit is also a good opportunity to step outside your particular area of focus (if you have one) and mix it up a bit.
9. Do your best to keep your focus on the Summit and not the little fires that break out back at your office while you are away.  Neither you nor your employer will see a maximum return on your Summit investment if you are continually missing learning and networking opportunities due to other distractions.
10. Socialize and network.  For the most part, while you are at the PASS Summit you are surrounded by folks who enjoy working with, learning about, and discussing SQL Server.  That is something many of us don’t find everyday.  Take advantage of it!

http://www.sqlsentry.com/

Presenting to Hampton Roads SQL Server Users Group, Thursday, April 15

I am reviving my “Integration Services in the Real World” presentation from last weekend’s SQLSaturday #30 in Richmond for the Hampton Roads SQL Server Users Group (HRSSUG) this Thursday, April 15, at 6:30 PM.  More details available here.

“Integration Services in the Real World” Slide Deck

I want to extend a HUGE “thank you” to everyone who attended my presentation at this weekend’s SQLSaturday #30 in Richmond.  I think I was lucky to be the only one talking about SSIS as the room was packed.  I really appreciated everyone’s attention and participation.  I only noticed one guy in the front fall asleep and I saw the same guy fall asleep in another session later in the day so not bad at all!

To those who stopped on the way out or came up to me later in the day to mention that they enjoyed the presentation, I really cannot say how much that meant to me.  This was my first time stepping out of my comfort zone and giving a presentation.  If one person had said something nice about my presentation, that would have meant the world to me.  The fact that maybe 10 or 15 did was absolutely incredible.  Even the guy who fell asleep gave me a “thumbs up” on the way out.  Maybe he was just looking for someone with a soothing voice and mine sufficed.

I would also like to thank Tim Mitchell (blog | twitter).  At one point, Tim and I were both presenting SSIS topics.  I felt a bit nervous to share the SSIS bill with Tim as he is quite an experienced and polished presenter.  I reached out to Tim both as an opportunity to pick his brain for advice and as an opportunity to network with someone before the conference since I did not expect to know too many folks ahead of time.  Tim was gracious enough to schedule a call with me and spend the better part of an hour answering my questions, giving me pointers, and reassuring me that I was on the right track.  I was disappointed that he was not able to come to the rescheduled event, but look forward to meeting him sometime in the future when our paths cross.  Thanks Tim!

Finally, I would also like to thank Jessica Moss (blog | twitter), one of the conference organizers.  Jessica spent some time talking with me when I ran into her at SharePoint Saturday here in Virginia Beach back in January.  At the time, I was a bit nervous regarding my approach for my presentation.  Jessica was very encouraging.  She reassured me that my plans for the session fit what they were looking for at the conference and that my abstract did a decent job of conveying those plans.  Knowing that a conference organizer and experienced speaker knew exactly what I was planning and was happy with it really boosted my confidence.  Thanks Jessica!

My slide deck is attached to this post.  During the presentation, a request was made for me to make one of my projects available for download.  Not considering this ahead of time was probably the biggest rookie mistake that I made.  I am still considering whether I am going to be able to do this.  I am torn at the moment because the project in question was not strictly a demo in the sense that it did nothing but explain a concept or illustrate a method.  It was a finished product.  It was something my employer paid for me to develop and deliver.  I am going to give this some more thought and will follow-up in another post.

Update:  Here is the link to the deck:  Integration Services in the Real World

SSIS: Catch ExecuteProcess Task Output

In my SQL Server Integration Services (SSIS) projects, I often use ExecuteProcess tasks for various purposes.  I use them to kick off batch files (often batch files that were created earlier in the project) and command-line utilities such as WinZip and WS_FTP Professional.

However, it recently dawned on me that I had gotten in a very bad habit regarding how I used these tasks.  I never took the time to capture and log the output of the ExecuteProcess task.  This output is often valuable for troubleshooting runtime errors.  Capturing it and logging it is so easy that there really is no excuse not to do so.

All that you need to do to capture the standard output from your process it to assign a string variable to the StandardOutputVariable property of the ExecuteProcessTask.  That’s it.  Then you have the output and can log it as you wish (e.g., pass it to a Script task and use the FireInformation method of the IDTSComponentEvents interface).  While you are at it, do the same for the StandardErrorVariable property of the ExecuteProcessTask.  Now, should you need to troubleshoot an issue with your task, you should have all of the information that you should need.

Microsoft Connect Suggestion: Change default behavior for FILEGROWTH argument in CREATE DATABASE statement

I recently filed a suggestion on Microsoft Connect for SQL Server to change the default behavior for the FILEGROWTH argument of the CREATE DATABASE statement to work similarly to that of the SIZE argument.

According  to Books Online,

“When size is not supplied for the primary file, the Database Engine uses the size of the primary file in the model database. When a secondary data file or log file is specified but size is not specified for the file, the Database Engine makes the file 1 MB. The size specified for the primary file must be at least as large as the primary file of the model database.”

However,

“If FILEGROWTH is not specified, the default value is 1 MB for data files and 10% for log files, and the minimum value is 64 KB.”

This prevents the DBA from proactively changing the default FILEGROWTH for data files created by an application that issues CREATE DATABASE statements without specifying the FILEGROWTH argument.  Worse yet, the default that it leaves you with is 1 MB for the primary data file.  While there may be no such thing as the perfect default for this setting, I really struggle to see any scenario where 1 MB is ideal.

One scenario where 1 MB is absolutely not ideal and the application is frequently creating databases is SharePoint.  SharePoint creates its content databases without specifying a FILEGROWTH setting.  If not subsequently changed, you could have a database that grows 1 MB at a time and is primarily used to stores files (each of which might be up to 50 MB).  These content databases tend to grow quite large over time as well.

Of course, there are things that a DBA can do to manage this including:

• Changing the FILEGROWTH settings as soon as the database is created.
• Growing the primary data file in the model database quite large to increase the time before the likely first autogrow.
• Monitoring file usage and manually growing files in large increments off hours before they grow automatically.

However, having some control over the default would be helpful in cases where there is no full-time DBA or an overworked DBA would like to proactively manage these settings.  I believe it would be even more useful if SQL Server extended this behavior to log files as well for both the SIZE and the FILEGROWTH arguments.

If you agree, please vote at Microsoft Connect.

Come on out to SQLSaturday #30 in Richmond

As I mentioned a couple of weeks ago here, I will be giving a case-study style presentation on SQL Server Integration Services at the upcoming SQLSaturday event in Richmond on January 30, 2010.  I will be presenting several of the SSIS projects that I have built at work.  Through a review of these projects, we will discuss some of the advantages SSIS presented as opposed to alternate approaches or technologies that I could have employed, look at exactly how I accomplished these things in SSIS, and lay out some best practices for SSIS development.  My slot on the schedule is at 11:00 AM, on Track 1.

It will be a day of firsts for me.  This will be the first opportunity that I have had to attend a SQLSaturday.  I have heard about these events for sometime now and think its great that we have one within a couple hours of where I live for the first time (I believe).  I had a blast at the recent SharePoint Saturday here in Virginia Beach and am not really a SharePoint guy (just play one at work quite often.)  So, I know a similar event focused on SQL Server will be a lot of fun.

This will also be my first time speaking at an event like this.  I’m quite nervous, of course, but looking forward to it.

I would like to encourage any of you among my legion of devoted blog followers who will be near the Richmond, VA, area on January 30th to sign up to attend.  It is a free event and I’m sure a large turnout would help to make sure other such events take place in our area in the years to come.  Register here to secure your place!

T-SQL Tuesday #002: Checking for SET FMTONLY ON

This post is my submission for the second T-SQL Tuesday, the theme of which is A Puzzling Situation.

A number of SQL Server tools execute queries with SET FMTONLY ON in order to obtain information about the result sets returned by those queries.  SQL Server Reporting Services (SSRS) does this to get the list of fields returned by the query so that you can lay them out on your report as desired.  SQL Server Integration Services (SSIS) does this in several places, including within the Data Flow task when you create your source in order to gather the meta data of the fields that will make up your data flow.

You can run into problems when complex queries are run with SET FMTONLY ON.  The most common problem might be with temp tables.  If your query is a stored procedure that creates a temp table and then subsequently references that temp table, you will get an error.  When running with SET FMTONLY ON, the statement that creates the temp table is not actually executed.  Therefore, when the statement that references the temp table is compiled, it generates an error due to referencing an object that does not exist.  See the following example.

CREATE PROCEDURE dbo.p_FmtOnly

AS

SELECT GETDATE() AS theDate

INTO #tDate

SELECT theDate AS [fulldate]

, DATEPART(MONTH, theDate) AS [month]

, DATEPART(YEAR, theDate) AS [year]

FROM #tDate

GO

If you execute this stored procedure with SET FMTONLY ON, you will receive the following error:

Msg 208, Level 16, State 0, Procedure FmtOnly, Line 7
Invalid object name ‘#tDate’.

The most common workaround for this problem is to essentially inject “SET FMTONLY OFF” into the query.  For example, if you had an SSRS report that called the procedure above, the actual query would be “SET FMTONLY OFF; EXEC dbo.p_FmtOnly”.   However, sometimes you might be dealing with a stored procedure that takes a long time to run or for some other reason you do not want it to run for real at design time.  In this case, the SET FMTONLY OFF workaround does not work for you.

My puzzle for this post is a challenge to come up with a way inside the stored procedure to capture whether SET FMTONLY is ON.  If it is on, the procedure should just return a single row of hardcoded values of the same data type as the real final result set.  If SET FMTONLY is off, then the real stored procedure should be run.  It would look something like the following (note, this code does not work and only serves to explain the challenge):

CREATE PROCEDURE dbo.p_FmtOnly
AS
DECLARE @bFmtOnlyOn AS BIT

SET @bFmtOnlyOn = ???

IF (@bFmtOnlyOn = 0)

SELECT GETDATE() AS [fulldate]
, DATEPART(MONTH, GETDATE()) AS [month]
, DATEPART(YEAR, GETDATE()) AS [year]

ELSE

BEGIN

SELECT GETDATE() AS theDate
INTO #tDate

SELECT theDate AS [fulldate]
, DATEPART(MONTH, theDate) AS [month]
, DATEPART(YEAR, theDate) AS [year]
FROM #tDate

END
GO

A colleague of mine was able to come up with a clever way to solve this problem.  I thought it would make an interesting puzzle for the T-SQL Tuesday event to see how many other techniques folks might come up with.

CREATE PROCEDURE dbo.FmtOnly
AS
SELECT GETDATE() AS theDate
INTO #tDate SELECT theDate AS [fulldate]
, DATEPART(MONTH, theDate) AS [month]
, DATEPART(YEAR, theDate) AS [year]
FROM #tDate
GO

Not All SSPI Context Errors are Created Equal

I got excited when I saw the title of this recent post on the Microsoft Customer Service and Support (CSS) SQL blog.

A few months ago, we were seeing “Cannot Generate SSPI Context” errors intermittently on a number of our systems that connect to SQL Server via Windows Authentication, including SharePoint.  These errors would come in a barrage that would last about 15 minutes, causing a system outage during that time.  However, by the time the outage was reported and we went to troubleshoot the problem, things were working again without any intervention on our part.

It was difficult to try and pinpoint a particular cause.  The affected systems use different service accounts from the same domain so it did not appear to be account-related.  While the connections were failing, SQL Server was still running and able to perform other tasks such as writing backups across the network.  The occurrences were at various times and it seemed to affect one server at a time rather than all at once.  It seemed to be related to something along the lines of domain controller replication or bottlenecks but I could not prove it.

Troubleshooting was further complicated by the fact that we were dealing with an issue that was the very definition of intermittent.  We still have not identified a particular cause either.  After about a month of this happening periodically (and at least several times a week), it just seemed to go away.  I do not think it has occurred in at least two months now.  Of course, I am happy not to be dealing with the outages, but having it go away without figuring it out is quite frustrating as well.

When I saw “Cannot Generate SSPS Context” in the CSS blog title, I got my hopes up that it might shed some light on possible causes of our issues.  I did not have to read too far, however, before realizing that their issue was different.  As the post describes, their issue was reproducible.  Reproducible SSPI context issues tend to be related to multi-hop scenarios or service account password issues.

If any of my thousands of loyal readers has any ideas on what may have caused our SSPI context errors, please leave a comment!

Speaking at SQL Saturday Richmond

I am on the Docket!

I received the official email letting me know that one of the two abstracts that I submitted to SQL Saturday Richmond was accepted.  As of now, I am speaking on SQL Server Integration Services (SSIS) at 11:00 AM, on Track 1.  I assume that could be subject to change.  My session is titled “Integration Services in the Real World”.

I was a bit relieved to see that only one of the two abstracts that I had submitted was selected.  I had submitted two to improve my chances of being selected (as suggested on the submission site).  However, since this will be my first time speaking at an event such as this one,  I am thankful to only have to prepare for one session.

Integration Services in the Real World

The idea behind the session is to show how SSIS has become an indispensable tool for me as a DBA who is often asked to do things that are not strictly database-related.  In the absence of SSIS, if you ask a DBA to tune a query, restore a database, or grant permissions to an object, the tools are there for those tasks.  However, ask the DBA to create a process that runs every night, extracts user data from a custom LDAP-provider, merges it with data from a SQL database, writes it to an XML file, zips the XML file and then sends it to a service provider via secure-FTP, and you might get a puzzled or dismayed look.  There are ways to do these things without SSIS, but they tend to be clunky, difficult to maintain, and do not always perform optimally.

With SSIS, I have a tool that enables me to meet these types of odd requirements (which are quite common in my workplace) quite elegantly, often with very little code, and free from some of the performance constraints of the alternatives.  I intend to illustrate this by walking through some of my real-life projects.  I will describe how I might have gone about meeting the requirements without SSIS, discuss some of the problems with those approaches, and demonstrate how I accomplished the same thing in SSIS.

My hope is that my presentation will:

• Encourage folks to consider SSIS as more than just an ETL tool for data warehousing projects.
• Illustrate some of the advantages SSIS has over other technologies that people may be using to accomplish some of these tasks.
• Highlight some of the best practices that I have adopted over the past several years working with SSIS.

Uninstalling SQL Server Manually

I have uninstalled SQL Server quite a few times for various reasons.  SQL Server’s uninstall program has consistently proven effective and easy to use, something atypical of uninstall programs in general.  Just go to Add or Remove Programs in Control Panel, locate Microsoft SQL Server, right-click, and select Uninstall.  Step through a few wizard-style screens accepting the defaults and you are done.

Last week, I ran into a bit of a roadblock in the rosy scenario above.  I was asked to reinstall SQL Server 2005 on a server that had originally been installed by a vendor.  No problem, right?  I went to Add or Remove Programs and… uh oh.  I’m not sure how it ended up this way, but SQL Server was not listed.

I searched online for a way to uninstall it manually and stumbled on a Knowledge Base article titled How to uninstall an instance of SQL Server 2005 manually.  That sounded promising!  At first glance, the process looked a bit overwhelming.  However, it turned out the that entire list of steps perhaps only applied to the worst-case scenario.  Quite a bit of the article was specific to Reporting Services as well, which I did not have installed.  In my case, just a couple of steps from the article were enough to get the job done quite easily.

Step 1:  Install the SQL Server Setup Support files.

This can be done quite easily by running Setup from the SQL installation disks.  The first thing Setup does is install these support files.  Then, just cancel the installation leaving the support files installed.  You can verify whether or not the support files are already installed by looking for them in Add or Remove Programs.

Step 2:  Kick off the uninstall via the command line.

Running the following command from the KB article will begin the uninstall process:  “%ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\ARPWrapper.exe /Remove”

Step 3:  Walk through normal uninstall steps of the Setup program.

I had expected the command above to kick off a command line style uninstall but was pleasantly surprised when it kicked off the GUI-based version of Setup just as you would get with Add or Remove Programs.  According to the KB article, it is the same command that Add or Remove Programs executes.

Step 4:  Repeat as necessary for all instances that you are uninstalling.

I performed these steps three times as I was uninstalling two SQL instances and one Analysis Services instance.  Since the uninstall in Step 3 removes the SQL Server Setup Support files, each iteration must begin again at Step 1.