SQLServerPedia

While working on a PowerPack for PowerGUI I needed to create a bunch of icon files from bitmaps files so I started with a quick web search. I didn’t find any PowerShell scripts suited to the task, but did find an excellent  C# WinForm by Haresh Ambaliya:

http://code.msdn.microsoft.com/Convert-Image-file-to-Icon-c927d9f7

Although the C# app is useful, it operates on a single file rather than whole bunch of files, so I quickly turned out a PowerShell called ConvertTo-Icon which I also posted on PoshCode:

function ConvertTo-Icon
{
    [cmdletbinding()]
    param([Parameter(Mandatory=$true, ValueFromPipeline = $true)] $Path)
�
    process{
        if ($Path -is [string])
        { $Path = get-childitem $Path }
�
        $Path | foreach {
            $image = [System.Drawing.Image]::FromFile($($_.FullName))
�
            $FilePath =  "{0}\{1}.ico" -f $($_.DirectoryName), $($_.BaseName)
            $stream = [System.IO.File]::OpenWrite($FilePath)
�
            $bitmap = new-object System.Drawing.Bitmap $image
            $bitmap.SetResolution(72,72)
            $icon = [System.Drawing.Icon]::FromHandle($bitmap.GetHicon())
            $icon.Save($stream)
            $stream.Close()
        }
    }
�
 }

Using the Convertto-Icon  function against my directory of bitmap files I was to create my icon files:

PS D:\Icons> Get-ChildItem *.bmp | ConvertTo-Icon

This is often the pattern I follow when I need to create a script, first look to see if anyone else has already done it and if not look for C# examples which easily be translated into Powershell.

Today on July 1st 2011, I was pleasantly surprised to be recoginzed as a Microsoft Most Valuable Professional for contributions to the PowerShell technical communities:

To the many who have helped me, Microsoft, and the PowerShell community, thank you!

–Chad Miller

This post is my contribution to T-SQL Tuesday, hosted this month by Allen Kin (blog | twitter).

A first step in any disaster recovery planning is inventorying your database servers. Although having having an up-to-date list of SQL Servers sounds simple enough the reality is in an enterprise environment SQL Servers are bit like mushrooms—they suddenly crop up and when a server is no longer needed they are taken down. A secondary issue to deal with is verifying firewall settings. In an enterprise environment or really in any company that cares about security, internal layered data center firewalls are used between networks, web servers and user segments. The addition of layered firewalls has added a new troubleshooting requirement for DBAs–not only ensuring a server is up, but also verifying various machines can connect to needed endpoints. The point is once you inventory and collect your list of SQL Servers you need to check they are still alive and various services can be reached by remote machines. Once you find servers which cannot be connected to, then its time to research why. Has the server been decommissioned? Is it connection issue? And finally if the server has purposely been taken down then we need to remove it from inventory. What we need is a dynamic inventory and a way to automatically check endpoints…

Adding Servers to Your Inventory

In order to handle the inventory piece we can leverage Central Management Server (CMS). I’ve previously blogged about several ways you can use PowerShell and T-SQL to dynamically add servers to your CMS inventory and even update your RDCMan list:

• Dynamically Register SQL Instances in SQL Server Management Studio 2008
• Building A Remote Desktop Connection Manager List
• Building A Remote Desktop Manager Cluster List

The post on dynamically registering SQL Servers is interesting in that I’m leveraging System Center to find new SQL Servers and add them to a LostAndFound CMS  group. I’ll then manually go through and move the servers to their appropriate groups. There also are many other uses for a CMS, you can even use CMS list to drive your Policy-Based Management collection as described here:

PBM and PowerShell

Verifying Servers in Your Inventory

As for verifying connectivity and checking whether the server is still alive and connect through various firewalls, I’ve a new script called Test-SqlConnection.ps1 and posted to PoshCode.

The script executes against the CMS list and can perform several tests:

• Ping
• WMI
• SMB
• Port
• SQL
• Database
• SSIS
• Agent

Aside from Database and Agent tests each checks for connectivity by making a connection to the service, pinging an IP or connecting to a port. A simple colorized HTML report is produced (see sample output below) for each test.

Getting Started

1. Download Test-SqlConnection and save as Test-SqlConnection.ps1
2. Change the script level variable $Script:CMServer to your CMS name
3. Source the functions

. ./Test-SqlConnection.ps1

Running Tests

Execute tests as follows:

Test-Main “Ping”
Test-Main “WMI”
Test-Main “SMB”
Test-Main “SQL”
Test-Main “Database”
Test-Main “Port”
Test-Main “SSIS”
Test-Main “Agent”

Sample output:

Testing a specific CMS group is supported. In this example I’m testing all servers in a CMS group named prod:

Test-Main "SQL" "prod"

You can also test a single server or list of servers using the individual functions without using a CMS:

Test-SQL 'Z003\SQL1'
get-content ./servers.txt | Test-SQL
Test-SQL 'Z003\SQL1','Z002\SQL2'

Additional sample output can be found here.

Failed connections should be researched and servers no longer online should be removed from inventory. Because there’s a bit of research involved, I choose to remove servers manually.

As a follow up to my SQLRally 2011 Scripting Guy Guest Blog Post which dealt with Excel and Access files, this post explorers working with delimited text files using the ACE driver.

What about Import-CSV?

One of the first things I do when working with PowerShell is first look at the built-in cmdlets. PowerShell has a cmdlet for working with delimited text files  called import-csv. Using import-csv you can even specify a delimiter other than a comma using the –Delimiter parameter. Import-Csv is good enough for many scenarios which involve delimited text, but there a few areas like fixed width and write back which using an ACE and OLE DB handles and import-csv does not.

Setup

Just as described in the my previous post you need to go to Microsoft Access Database Engine 2010 Redistributable, and download AccessDatabaseEngine.exe or AccessDatabaseEngine_x64.exe, depending on your operating system.

For testing purposes we’ll create a simple CSV file:

get-psdrive | export-csv ./psdrive.csv -NoTypeInformation -Force

CSV files

Connecting to a text file and querying the data is pretty easy using the ACE drivers and as long as the file is comma separated no special setup is required (more on other delimiters in moment):

$filepath = "C:\Users\u00\bin\"
�
$connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$filepath`";Extended Properties=`"text;HDR=yes;FMT=Delimited`";"
�
$qry = 'select * from [psdrive.csv]'
�
$conn = new-object System.Data.OleDb.OleDbConnection($connString)
$conn.open()
$cmd = new-object System.Data.OleDb.OleDbCommand($qry,$conn) 
$da = new-object System.Data.OleDb.OleDbDataAdapter($cmd) 
$dt = new-object System.Data.dataTable 
$null = $da.fill($dt)
$conn.close()
$dt

Note: One of the odd things about using a text file as a data source is that unlike Excel and Access files in which you list the full path to the file, with text files you only specify the directory where the text file is located i.e. “C:\Users\u00\bin\”. The text file is listed in the query i.e. “select * from [psdrive.csv]”. In this way the concept of the “database” is really the directory and files are analogous to tables.

Other Delimited Files

What if your text is delimited by something other than a comma? Let’s create a semi-colon delimited text file to test:

get-psdrive | export-csv ./psdrive.csv -NoTypeInformation -Force  -Delimiter ";"

Because the delimiter is a semi-colon you’ll need to create a special file named schema.ini in order to describe the delimited file. Next you’ll need to place the schema.ini file  in the same directory as the text file. Add the following to the ini file:

[psdrive.csv]
Format=Delimited(;)
ColNameHeader=True

Now we can use the same code as demonstrated for a CSV file to query our semi-colon delimited file.

Notes:

• You can specify multiple files in schema.ini with each file having a new section i.e. [filename]
• There are many additional options which can be used to describe the delimited file including fixed-width. See this on Connecting to a Text File for details.
• If you’re a SQL Server professional you may wonder if can create a linked server using the ACE drivers, well you can but there seems to be bugs several bugs that prevent you from connecting to anything other than Excel files when running x64: https://connect.microsoft.com/SQLServer/feedback/details/587897/connecting-via-a-linked-server-to-an-access-2010-database-file. Despite making sure I had various settings specified I could not get the ACE driver to work against a text file through a linked server on an x64 machine while PowerShell + ACE worked fine.

Ed Wilson (Blog|Twitter) aka Scripting Guy has series of SQL Server related posts the week of  May 2nd 2011 including my guest blog post. The post, Use ACE Drivers and PowerShell to Talk to Access and Excel, demonstrates querying Excel and Access files from PowerShell and loading the data into a SQL Server table. There several ways to get data from Excel and Access, but I find using ADO.NET to be the most straight forward.

An important consideration when using ADO.NET against Excel and Access files is selecting the right OLE DB drivers. In the post I talk about using Access Control Entry (ACE) drivers.  ACE is completely free, and it even includes a 64-bit version. For SQL Server professionals having a 64-bit driver for Excel and Access is a big deal as ACE’s predecessor, JET only supported x86. ACE is included with Office 2007 or higher and Office 2010 has a 64-bit version. If you don’t have Office or you’re installing on a server go to Microsoft Access Database Engine 2010 Redistributable, and download AccessDatabaseEngine.exe or AccessDatabaseEngine_x64.exe, depending on your operating system.

I’ve mentioned this in the post, but I think this is a key takeaway which I’ll restate–When you have ACE drivers, there is no reason to use the old deprecated JET drivers—even for older versions of Microsoft Access and Excel. A common mistake I see, even with seasoned developers, is to drop to JET for .mdb and .xls files when you don’t need to. I’ve even made this mistake myself.

I found a helpful blog post on MSDN from the CSS SQL Server Engineers that talks about different data providers and discusses a migration strategy.

My guest blog post Use ACE Drivers and PowerShell to Talk to Access and Excel doesn’t delve into other uses of the ACE driver including working with delimited text files which I’ll blog about in a future post.

If  you live in Central/South Florida or are planning a trip, we’ve got several IT community events worth checking out…

May

SQLRally in Orlando on May 12th through May 13th. Although SQLRally isn’t a free event, but the pricing is extremely low at only $299 if booked by April 12th.  I really admire SQL PASS for putting together a low price 2-day event with top notch speakers and sessions.

June

The third annual Tampa SharePoint Saturday is on Saturday, June 11th. This is a free one-day event similar to SQL Saturdays and Code Camps. I’ve never been to SharePoint Saturday, so this will be first for me. I’m also planning on presenting a PowerShell session.

July

After a successful Tampa IT Camp, I’m helping put together the first annual South Florida IT Camp on Saturday, July 23rd. This event will be even bigger than Tampa. We’re planning on having an optional low-priced pre-con in addition to the full free one-day IT Camp event. The South Florida IT Camp is being organized by Adnan Cartwright and the Florida IT Server Group. I’m planning on presenting a PowerShell session and I’ll be posting more details about the event.

In part 1 I provide an overview of concept of IT Camp, how the plan was developed by borrowing ideas from similar events, and how we did on venue, sponsors and attendees. In this blog post I provide more details on overall event feedback.

Sessions

SharePoint, PowerShell and SQL topped the list of sessions which were mentioned by attendees in their feedback forms.  The Intro to SQL for IT Pros session wasn’t DBA related, but more admin related giving them an overview of how things work and what to look at when there are issues. What I found interesting about the SQL session is the similarity to PowerShell sessions at SQL Server events, both are not core to each other jobs which is why I think DBAs favor PowerShell presentations and sysadmins appreciate SQL Server sessions. They each get to see an overview of technology they may not work with regularly.

One of the attendee comments asked for Windows 7 and Windows Server OS related sessions. Having an Windows Server 2008 R2 or Windows 7 session seems kind of obvious now. I’ll try getting a core OS session added to the agenda next time. Along the same lines  I felt my own presentation on SQL Server PowerShell Extensions was out of place and an intro session PowerShell session would have been better. I also felt I didn’t do as good of a job with my presentation as I could have as the work in organizing the overall event meant I didn’t devote as much time as I should have preparing. My advice to anyone organizing a one-day IT community event—don’t present a session unless you really need to fill out the schedule.

A couple of presenters asked for repeating sessions. My take on repeating sessions (a common practice at larger multi-day conferences) is  that we are already asking a lot  of our presenters, but if there’s a willingness to repeat by them then I’m OK with it. Next time, I’ll leave this as option for presenters.

One of the comments I received mentioned they would have liked hands on labs. At first I thought this was very odd, I mean a hands on lab—no does those at code camps, SQL Saturdays or SharePoint Saturdays. However, this shows a couple of things. First we’re reaching someone who has never attended an IT community event (or else why would they ask for hands on labs?). This person is probably an IT Pro and there just hasn’t been IT Pro community events before.  Second, hands on lab are usually part of some type of optional  pre-conference training. SQL Saturdays will often have an one-day, low cost ($99) optional pre-con they will advertise with their events. Since there’s interest we’ll see if we can arrange a pre-con for IT Camp next time.

Location

A few attendees traveled from out of town and mentioned they would like to see an IT Camp in their cities: Jacksonville and South Florida. The good news is South Florida IT Camp 2011 is already scheduled for Saturday, July 23rd 2011 at Nova Southeastern in Ft. Lauderdale-Davie. As for Jacksonville –if you’re an IT Pro, interested in kick starting a Jacksonville IT Camp contact me and I’ll help  and for that matter really for any city —The way I setup http://itcampsaturday.com/ using WordPress MU, I can quickly create a sub-sites for an IT Camp very quickly: (http://itcampsaturday.com/tampa, http://itcampsaturday.com/southflorida ). The site needs a little CSS work to format things nicer, but the general organization with registrations, schedule, sessions and sponsors is there.

Schedule

Some attendees remarked they would have like to had seen the schedule prior to the event. At first this comment confused me. We had the schedule posted online three week prior to IT Camp. It would have been sooner but we needed an extra week to round up speakers. We also included a printed schedule in the attendee bags. After thinking about the comment it occurred to me, IT Camp like SQL Saturday, SharePoint Saturday, Code Camps and even the big multi-day conferences; registrations for the event opens before the schedule is published. What I neglected to do was notify those who had registered for event the final schedule had been published. Since I used Eventbrite for registrations a simple email out to attendees informing them the schedule is posted would have worked. This is something I will definitely do next year.

Session Downloads

What to do with session downloads which includes presentations slides and any demos?  Conferences will often have deadlines and a set PowerPoint template. At these paid events, speakers are expected to upload their session downloads prior to the event. This approach simply doesn’t work for free IT community events which are often called un-conferences. My take is speakers should post their content on their blogs.  Community-driven events are NOT conferences as such we don’t have the resources to do content aggregation. It has been my experience that because community are NOT conferences, there aren’t things like deadlines being enforced for content upload which  means we see a very low content submission rate when we’ve tried to.  Making speakers responsible for posting content to their blog also helps increases their community exposure driving traffic to their blogs. A few folks had asked what if the speaker doesn’t blog—my first thought is why would a speaker not have a blog? Really you should start one today even if you only use it for posting your presentation content. Go on, it takes 5 minutes to setup a WordPress account. Not up to starting a blog? We’ll at least setup a DropBox account to share files. And if you’re really serious about starting a blog, check out Brent Ozar’s (blog|twitter)  How to Start a Blog. If you don’t want to do any of these, I’ve had speakers just handout out their contact info so they can email them slides and demos scripts.

As an event organizer the expectation that speakers are responsible for their content should be clearly communicated. You should also indicate sessions downloads will be made available by speakers to your attendees (something I didn’t do until after the event). Next year I’ll be sure to include a email to attendees informing them sessions downloads are available from the speakers as well a note in the attendees handouts.

To make it easier for attendees to find speaker blogs I created a quick web page with just speaker blogs and bios:  http://itcampsaturday.com/tampa/2011/03/21/speaker-blogs/. Someone had a really good idea to include a one-page printed sheet in the attendee handouts of speaker blogs. This is something I’ll be sure to do next year.

Lunch

This brings us to lunch, the one and only area we were rated as fair.  Lunch is the biggest expense of any IT community event including IT Camp and its also has the highest potential for waste. At the Tampa IT Camp I went with 40 pizzas, half pepperoni and half cheese. I figured I could feed about 160 people with 3 slices each. The cost was around $300. There were 10 or so pizzas left after lunch, but they were eventually eaten. If we step up to boxed lunches which includes a sandwich, chips and cookie then we’re at $8 a person times 160 = $1,280, nearly $1,000 more than pizza.

Why didn’t we go with boxed lunches over pizza? The problem is IT Camp had almost a 50% no show rate, while I was calculating a 30% no show rate based on typically what is seen at other IT community events. We would have had about $300 in wasted food had we gone with box lunches. I’m told the other IT community events also get a 30% to 50% no show. If you actively manage cancellations by reminding attendees to cancel you can drive the number closer to 30%, but still this is very high.

The reason for the high no show rate with IT community events is simple. It’s a free a event on a Saturday! I’m sure most people who register don’t purposely plan on not attending, but the day of the event they wake up to nice Saturday morning and think what should I do today, go to IT Camp or go to the beach?—such is the reality of community events.

As an organizer you want to provide a better quality lunch however your attendees who said they were going to show up, didn’t and now you’ve wasted money when running these events on a small budget is hard enough. What can be done to address wasting money on lunch?

I’ve seen two ways IT community events have been able to step up lunch while at the same time limiting waste. First charges an optional small fee for lunch, around $10 collected at time of registration. If you choose not to pay the $10, well then you  don’t get a lunch ticket. I think this is good compromise in order to provide better lunch. The second way I’ve seen an IT community event deal with this is really venue specific. There are some venues that have onsite food services and only charge you per head for the folks that actually show up. This is different than a catering approach which requires an exact number ahead of the event and again is very event specific. The only event I’ve heard of this working at is South Florida SharePoint Saturday hosted at Nova Southeastern University. Nova has a food court and if you haven’t been to a college in a while they resemble mall food courts more than the old cafeteria style food you’re used to seeing. They’ll give you vouchers which you’ll hand out and only pay for the used vouchers.

The voucher approach really isn’t an option unless we change venues and I’m not sure I want to collect money from people for lunch. I’m not sure how we’ll handle this next year, but at least I have a couple of ideas.

On Saturday, March 19th 2011 we held the first ever Saturday “code camp” style event for the IT Pro (Sys Admins) community which we called IT Camp. IT Camps are a free, one day learning event for anyone seeking professional development. This event serves IT professionals and students with a focus on IT Pro (i.e. Sys Admin) related technologies. IT Camps offer a conference style learning environment free of charge to attendees and is open to presenters of all backgrounds and expertise. This blog post is about the lessons I learned organizing an IT community event…

The Idea

In December, Blain Barton, a Microsoft Senior IT Pro Evangelist who sponsors my Tampa PowerShell User Group,  told me about an idea he had to start the first ever IT Camp to meet the needs the IT Pro community. I had been to many SQL Saturdays and code camps (like the Tampa Code Camp)  and heard about SharePoint Saturdays, but IT Pros really didn’t have an similar event for them. The idea intrigued me so I volunteered to organize it.

The Plan

Although I’ve spoken at or sponsored at ten SQL Saturdays or Code Camps I hadn’t organized a large IT community event so my first step was to talk to my local IT community leaders:

• Andy Warren (blog|twitter) – SQL Saturday founder, helped organized dozens of  events including Orlando SQL Saturday.
• Keith Kabza (twitter) – organizes Tampa Code Camp
• Michael Hinckley (blog|twitter)  — organizes Tampa and South Florida SharePoint Saturdays
• Pam Shaw (blog|twitter) – organizes

I could have expanded the list to more people, but I really felt it was important to seek the advice of  folks who are familiar with local Tampa IT community. I would sit down with each person and ask them to tell me about how they organized events. One of the interesting things about each person’s event is there similarity, but at the same time they are also slightly different.

SQL Saturdays

SQL Saturdays have a strong central organization which at first was provided through the leadership of Andy Warren, but has since transitioned to  SQLPASS.  They help local chapter leaders kick start a SQL Saturday. They provide a web presence to handle speaker submittals, scheduling building and attendee reservations. They provide mentorship and have a really nice public wiki on organizing a SQL Saturday, most of which equally applies to any IT community event. The wiki was helpful to me as I could read about guidelines and lesson learned from the SQL Saturday camp all in one place. I still walked through the budget with Andy and Pam so I could try figure how much money I would need to raise.

SQL Saturdays centralize their web presence, an idea I really like. I know they have to deal with the same types of issues as any IT community event: registration, speaker submittals, sessions publishing, schedule building, sponsorship list and providing a way to contact the organizer—so I spent some time seeing how their site is organized. Now, I’m not saying I ripped off their site. If you look at SQL Saturday site, any code camp site or the SharePoint Saturday site they all have a similar organization to them.

SQL Saturdays folks have their stuff to together the only issues I’ve seen is around check-ins the day of the event which really made me thing about how to address this issue (hint: use EventBrite!)

One of things Andy and Pam mentioned was to plan on a 30% no-show the day of the event. Andy had some good advice on what it means to have a successful community IT event simply, did your attendees learn something. Some of the folks who attend IT community events will not have the opportunity to go paid events and these community events are all they will get to go to. Teach them something and you’ve succeeded.

SharePoint Saturdays

I haven’t attended a SharePoint SharePoint, but plan to go to my first one, the Tampa SharePoint Saturday on June 11th 2011.I did chat with Michael Hinckley who provided some advice including:

• Use EventBrite – This a free service for selling tickets to any events. The site handles event registration, event reminders, and check-ins. Now that I’ve used EventBrite, I totally agree there service is awesome! Like any technology there some gotchas. I plan on writing a short post about my EventBrite experience.
• Take care of your speakers with a speaker dinner – The day of event many of speakers who traveled  may not be able to attend after event party. The dinner is an opportunity to network. Its also customary to show your appreciate to speakers for taking the time to present at the event. Many of speakers will have come from out of town, spending their money in travel. The trick with speakers dinners is choose some place nice which is group friendly. One of the problems with large group and getting preferred pricing is that you have to guarantee a certain number of people if not everyone shows up then you having to pay the per plate price.

Like SQL Saturday I like the centralized website provided by SharePoint Saturday. Its also cool their site is built on SharePoint . The site organization is similar to what you would see on SQL Saturday or Code Camp. My understanding of SharePoint Saturdays is that a committee/group of volunteers help kick start an event in a new location by providing mentoring and web presence.

Code Camps

Code Camps have the least central organization, in fact they seem to be entirely organized by the local developer users groups. To me, code camps feel like an “unconference” where its almost like a really big user group meeting. They also seem to be able to run on the cheap. These guys are the grandfathers of the whole Saturday code camp events and SQL, SharePoint and IT Pros owe them a tip of the hat for doing it successfully for so long.

Keith helped me especially with getting hooked up on event insurance, some recommendations on lunch and break down of budgeting. Keith also allowed me to adapt his sponsorship packet to IT Camp.

One thing that is different about code camps is they seem to enjoy building out a code camp site in whatever is the latest technology. Some spend more time theirs than others. For me, I’d rather just have a group site like SQL Saturday or SharePoint Saturday I can use without having to spend a lot of time developing one.

I chose to develop the IT Camp Saturday site using WordPress. The reason being is I’m not a full-time developer, I’m comfortable using it, and you can find cheap hosting for WordPress sites easily. It’s also very simple to create multiple sub-sites so I can add other locations, but for now its just http://ITCampSaturday.com/Tampa. I took some flack from some of the hard-core Microsoft developer types for not using some type of Microsoft related technology , but again I don’t consider myself  a developer. If you’re not going to code up a site this leaves using some type of CMS and with over 11 million installations, 20K pluggins, and plenty of inexpensive hosting–it’s hard to beat WordPress. At some level I think there’s acceptance of WordPress within the Microsoft community. I know plenty of folks who use Microsoft technology all day long, but choose WordPress as their blogging platform. It’s interesting to see what happened to Microsoft Live Spaces users who through a Microsoft partnership became WordPress users. That said I’m not a technology zealot if there’s an easy/cheaper way for a non-programmer to create an extensible site–let me know and I’ll take a look at it. I do think it would be nice to get some kind of hosted SharePoint and uses it as a basis for the site, just because SharePoint is an IT Pro related technology.

The Venue

One of the big concerns for an IT Community event is the venue. Cost and location are the deciding factors. When it comes to a venue for a Tampa IT community event, we’re very fortunate to have formed a partnership with K-Force. They’ve hosted SQL Saturdays, SharePoint Saturdays, Code Camps and now IT Camps for several years all free of charge. There facility and people are great to work with. As a sponsor they provide the facility (which includes internet access and projectors), cleanup, security and people to coordinate with. This is a very smart business move on their part. They’re able to have group of 100 to 300 people from the local IT marketplace come to them. If you happen to be looking for job, well there are couple of recruiters you can talk to and if not well, they’ve built some good will and branding. K-Force is a good example of IT community and business partnerships.

The Sponsors

I still needed to  find sponsors to help with all the incidental costs of the event. This is a tricky area especially for new event. The sponsors my code camp, SQL Saturday and SharePoint Saturday colleagues worked with really don’t apply to an IT Pro focused event, plus vendors that do one event may be unwilling to sponsor another event in the same market.

I had a list of potential sponsors from my own network and suggestions from other folks. I would say the “no-show” rate for sponsors was even higher than attendees, maybe 60% of the potential sponsors did not pan out. Part of the issue with getting potential sponsors to commit is my own lack of experience in fund raising or sales—you need to somewhat persistent.

I would contact a vendor and ask them if they are interested in helping sponsor the IT Camp. They’d agree and I’d send them the sponsorship package and then crickets. They wouldn’t get back to me. I took this as meaning they just weren’t interested once they saw the commitment levels we were looking for and moved on to the next sponsor. Looking back on it what I should of done is gotten a yes/no answer through more follow up. It seems kind of silly that I wasn’t more aggressive in pursuing sponsors , but lesson learned.

I ended up with three sponsors: Microsoft, Raymond James and Shavlik. I can’t thank them enough for their commitment.

Another area with sponsors I felt I could have done more—is somehow driving the attendee/sponsor interaction. Unfortunately the sponsors weren’t raffling items which is typically a draw for attendees, but even so I’ve seen the SQL Saturday folks use a bingo-style event raffle card which requires collecting stamps/signatures from sponsors. The SharePoint Saturday folks in South Florida also had a novel idea of having sponsors give out lunch tickets.

This may seem a little gimmicky, but the reality is without sponsors we wouldn’t have free IT community events. Sponsors need to feel they got some value out of it especially if you want them to come back next year. Now that I’ve had to organize sponsors I realize this even more and vow to interact with each sponsor at the community events I attend.

The Speakers

Finding speakers to fill 4 tracks with 6 sessions for a total of 24 sessions was a big concern for me. I really didn’t think we could do it, but the Florida IT community didn’t let me down. This turned out to be the one area that exceeded my expectations. Although this was an area of stress—”will we have enough speakers” is constantly on your mind. One thing this made me realize is I need to get my session submittals ASAP for events I plan on speaking rather than waiting to the last minute—doing my part to reduce organizers stress.

The Attendees

Even before we opened up registration I spoke with several people who were skeptical an IT Pro-focused event would draw very many people. I don’t blame them, I wasn’t really wasn’t sure either. IT Pros tend not to have strong user groups communities when compared to our developers and SQL brethren. Running a first year event is especially difficult in that you rely on word of mouth marketing and since it hasn’t been done before you’re not sure you’ve reached potential attendees.

We did the typical emails, blog posts and whenever we had live meetings with IT folks announced the event. Still I ran into people who mentioned—”if only I had known in advance I could have brought more  people.” I was sure to get their contact information, so that next year they’ll know.

EventBrite makes it easy to keep an eye on the registrations numbers. We didn’t pass more than 50% of the registrations until a few weeks prior to the event:

The day of the event we hit 114 attendees which is roughly a 50% no-show. When I spoke with Andy about no-shows  he mentioned SQL Saturdays try to remind attendees to cancel if they aren’t able to attend this is how they are able to get to a 30% no-show rate. Next year I plan on actively managing cancellations.

How Did We Do?

I did short re-cap on the IT Camp Saturday site so I won’t re-post it here.  There’s more details I’d like to share, but I’ll save this for part 2…

I was recently asked a question on restoring a database using PowerShell with the following requirements

1. Take a database backup file i.e. DatabaseName.bak
2. Derive the database name from the backup file name
3. Disconnect any user connected to the database
4. Relocate (move) the physical files to SQL Serve r instance the default data and log directories

If you had to develop a script like this from scratch you’ll find you would need to dig deep into various SMO classes—a non-starter for most novice PowerShell users. Fortunately thanks to the CodePlex project SQL Server PowerShell Extensions (SQLPSX) there are base functions which make this task much easier. Let’s take a look at the script, Restore-Database.ps1 then an explanation. Note: The following script requires SQLPSX version 2.3.2.1 or higher:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
param($sqlserver, $filepath)
�
import-module sqlserver -force
�
$server = get-sqlserver $sqlserver
�
$filepath = Resolve-Path $filepath | select -ExpandProperty Path
$dbname = Get-ChildItem $filePath | select -ExpandProperty basename
�
$dataPath = Get-SqlDefaultDir -sqlserver $server -dirtype Data
$logPath = Get-SqlDefaultDir -sqlserver $server -dirtype Log
�
$relocateFiles = @{}
Invoke-SqlRestore -sqlserver $server  -filepath $filepath -fileListOnly | foreach { `
    if ($_.Type -eq 'L')
    { $physicalName = "$logPath\{0}" -f [system.io.path]::GetFileName("$($_.PhysicalName)") }
    else
    { $physicalName = "$dataPath\{0}" -f [system.io.path]::GetFileName("$($_.PhysicalName)") }
    $relocateFiles.Add("$($_.LogicalName)", "$physicalName")
}
�
$server.KillAllProcesses($dbname)
�
Invoke-SqlRestore -sqlserver $server -dbname $dbname -filepath $filepath -relocatefiles $relocateFiles -Verbose -force

What this script does is take two parameters, a SQL Server and the database back file. The line:

1
$filepath = Resolve-Path $filepath | select -ExpandProperty Path

Get the full file path so if the script was called with just a relative path i.e Restore-Database.ps1 databasename.bak. The script uses PowerShell Get-ChildItem cmdlet to grab the full path. The next line simply grabs the basename i.e. file name without path or extension. The next two lines use SQLPSX functions to grab the default data and log file directories for the SQL Server instance:

1
2
$dataPath = Get-SqlDefaultDir -sqlserver $server -dirtype Data
$logPath = Get-SqlDefaultDir -sqlserver $server -dirtype Log

The next several lines restore the file list only, this is an option to simply retrieve the physical and logical files that are part of the database backup. Because the requirement is relocate the files to the default directories, the script collects the logical and physical names into a hashtable called $relocationFiles. One thing to notice the script makes use of [system.io.path]::GetFileName static method to extract just the file name portion of the files which is appended to default directory path.

Finally with the our new file location information, file path to the backup file, were ready to disconnect any users and restore the database with relocatefiles option:

1
2
$server.KillAllProcesses($dbname)
Invoke-SqlRestore -sqlserver $server -dbname $dbname -filepath $filepath -relocatefiles $relocateFiles -Verbose -force

We’ve released a minor update to SQLPSX which includes includes several bug fixes/enhancements as well as one new module. Here’s an excerpt from the release notes:

Added MySQLLib Module

Mike Shepard created a MySQL module for querying MySQL databases. This is similar in concept to the adolib and OracleClient modules.

Modified adolib Module

• Changed SQLBulkCopy to use System.Data.Common classes rather than SQLClient-specific classes for platform interoperability
• Fixed issue in new-sqlcommand

Modified OracleClient Module

Added OracleBulkCopy which allows you to bulk load data into Oracle

Modified SQLServer Module

Added FileListOnly to Invoke-SqlRestore function

Modified PBM Module

Fixed issue in PBM module when writing to Windows Event log

Modified SSIS Module

Added ProtectionLevel parameter.  The package protection level can now be changed/specified as part of the copy process

Credits

My thanks to

• Bernd Kriszio (blog|twitter) for his OracleClient enhancements as well as feedback on adoblib
• Mike Shepard for adding functionality and maintaining his adolib as well as branching out SQLPSX to include basic query support for MySQL.
• Eric Humphrey (blog|twitter) for contributing a patch to the SSIS module which adds support for specifying the package protection level during copy processes.