Author: Doug

2011 was the year of #SQLFamily

Doug

As the name suggests, SQL Family feels like family.  There is no other professional organization in the world that supports a product line as well as #SQLFamily.  My introduction to #SQLFamily was in the summer of 2011 when I decided to get back into SQL Server full-time after going to the dark side, management, for the two previous years.  I had a strong background in SQL Server 2000 & 2005 but not the full-time experience under 2008 & 2008 R2.

As I started searching for training opportunities for SQL Server, I came across Pragmatic Works.  Every Tuesday and Thursday throughout the year, they have a one hour web cast on all areas of SQL Server.  This allowed me to catch up on what was new under 2008 and brush up on the daily DBA tasks that I was accustomed to.  Each of the presenters had a personal blog and twitter address that had even more content over the session that was offered.  This got me interested in blogging for myself and starting to use twitter.  As I started searching for ways to get started in blogging and using twitter, I came across Brent Ozar (blog, twitter).  He built a great guide to help understand what twitter was all about.  I wasn’t interested in following celebrities or sports figures, I just wanted to use it for SQL Server.  It just so happened that Brent was a DBA and a photographer.  This one-two punch was just the right mix to start me on my way into WordPress and Twitter.

As I started following Brent, I started reading posts from him and other SQL Server professionals about the passion the SQL Server community had for helping others.  This was perfect for me as I started on my way to becoming a full-time DBA.  Each new blog entry or twitter post gave me a new understand of SQL Server and how strong the community was.  This also introduced me to PASS and the local user groups that were offered in my area.  As I started attending the local events, that same passion within the on-line community was equally as strong at the local level.  This allowed me to network with other SQL Server DBAs and get their input on ways to get back into the field full-time.

The local PASS events lead me to SQL Saturday.  I was able to attend the Atlanta #89 event in the fall of 2011.  This was very eye-opening for me.  There were over 400 people gathered for a full day of free training on a Saturday.  I was finally able to meet a few folks that I had only meet via twitter.  The highlight for me was hearing Bob Ward from Microsoft talk about wait types.  His session was level 500 and then some.  It was cool to see the inter-workings of SQL Server from one of the people who has access to the source code.

By the fall of 2011, I was already talking to a few companies about DBA positions and felt confident about finding the perfect DBA job.  As I accepted my current DBA role, I thought back to the family that got me there.  With out #SQLFamily, this would have not been possible.  This has given me the drive to give back to the community so others out there can find their perfect DBA role like I did.  My first step is our local PASS chapter and presenting during the summer of 2012.  I’m also working on blogging more regularly throughout the month, so others can learn from my view of being a SQL Server DBA.

I love being a part of the #SQLFamily.  Looking forward to a great year in 2012!

iPhone 4 IOS 5 Upgrade Error 3004 & 3014

Doug

I was excited to see the iOS 5 update was available yesterday (10/12/11) so I did what any normal person would do and quickly connected my iPhone 4 via iTunes and clicked the Check for Update button.  The download was about 740MB and didn’t take too long to pull down from Apple’s site.  The install appeared to be going well until it started the restore process and bombed out with an error code 3004/3014.  Did some research via Google and didn’t find an exact fix but found plenty of other people experiencing the same problem.  I was able to get past the verification stage with Apple, but the restore was failing every time I tried update the phone.  I was stuck in an infinite loop.  The phone would have the factory default “Connect to iTunes” message, but every time it went to restore the iOS 5 image it failed.  I finally ran across a few articles taking about pulling the iOS 5 image down directory from Apple and applying that image instead of the one that was pulled down from iTunes.  You can find the iOS 5 image links to all the Apple devices compatible with iOS 5 here.

Once I had the iPhone3,1_5.0_9A334_Restore.ipsw file (specific to my at&t iPhone 4) I connected the phone back to the USB cable and this time, held down the Option key and clicked Restore within iTunes under my device.  This brought up a finder window that enabled me to select my own iOS 5 image to use.  This restored the phone to the original factory defaults (with iOS 5) and got my phone back up and running.  Once iTunes discovered the same phone had been previously backed up on the same machine, it let me select the backup image to apply.  This process took a while.  All the settings, apps, music, and videos had to be copied back to the iPhone.  Once that completed (~1.5 hours) I was back in shape!

I miss Steve…

September’s Inaugural Triad SQL BI PASS User Group

Doug

I attended the inaugural Triad SQL BI PASS User Group last week hosted by Inmar in Winston-Salem NC.  DataMasters provided the pizza before the meeting and Wayne Snyder [twitter] from Mariner USA was the guest speaker that talked about the top 10 reasons to upgrade to SQL Server Denali.  Melissa Wittner [triadsqlbi@gmail.com] is the Chapter Leader for the new group and did a wonderful job getting the group start and the meeting organized.  You can visit the chapter’s website here.

Future meetings:

  • October 23rd Performance Point
  • November 29th Dashboards
  • December 8th Social @ Foothills Brewing Company
Top 10 Reasons to Upgrade List:
  1. SSIS Enhancements
  2. Master Data Services Enhancements
  3. Data Quality Services
  4. Column Indexes
  5. SSRS w/ SharePoint
  6. SSRS Data Alerts
  7. PowerPivot Enhancements
  8. DAX (Data Analysis Expression) Language
  9. SSAS Tabular Model
  10. Project Crescent
Wayne spent the next hour doing a deep dive demo for some of the items in the top 10 list.  Wayne has spent the last 9-12 months working in Denali and it shows.  He was able to show the group all aspects of SSIS, column store indexes, and PowerPivot.  This has energized my interest in Denali and trying a few of these new features in my lab.  I’m looking forward to attending this user group on a regular basis.

F11 and VMware ESXi 5 on my Mac

Doug

As I was installing ESXi v5 in my lab I came across a prompt to press F11 to accept the license agreement, that I fully read :).  On my Mac I was using iDRAC v6 Enterprise via the browser to install the ISO image.  When I went to press F11, my Mac’s desktop was shown.  I have the full Apple USB keyboard so I tried using the fn key with F11 and without, each time with no success.  I thought I’d start in the System Preferences section under Keyboard.  Under the Mission Control section there was a short-cut for F11 and F12 that was grabbing the keystroke before it was sent through the browser.

Once I un-checked the “Show Desktop” and “Show Dashboard” I was able to reply to the license agreement prompt with F11.

Doug Purnell
sqlnikon@gmail.com

September’s Triad SQL PASS User Group

Doug

I attended a great session September 15th 2011 at the Triad SQL PASS User Group with Jason Hall from SQL Sentry demonstrating the advantages of SQL Sentry’s Plan Explorer over the built-in SSMS plan explorer.  You can find Jason under @SQLSaurus on Twitter. The meeting was hosted by Dynamic Quest, and everyone’s favorite pizza and soda was served before the meeting started at 6:30pm.

Jason went through 7 example queries to show how the SQL Sentry Plan Explorer reporting results compared to SSMS.  Sentry’s product is free and integrates directly into SSMS.  Here are some other advantages with Sentry’s Plan Explorer:

  • Features color scaling from yellow to red so you can easily identify problem areas.
  • Features separate I/O and CPU costs to identify disk vs processor intense queries.
  • The plan viewer is must more condensed than SSMS’s view.  This enables you to see the whole plan much easier.
You can download Plan Explorer for yourself here and test things out.
Doug Purnell
sqlnikon@gmail.com

Understanding SQL Server Instances

Doug

Being raised within SQL Server 6.5 & 7.0, SQL Server instances were not available in those versions.  I never fully understood the advantage of having multiple instances on the same server so I never embraced it.  I always had a single, default instance per physical server.  Once I got involved with virtualization a few years ago, having multiple instances of an OS running on a single physical server finally got me thinking about multiple instances of SQL Server.

My lab at home has been the perfect place to install, configure and understand multiple SQL Server instances.  I have two servers running ESXi 4.1 U1 and VM’s to support a small Windows Domain.

I started with a copy of Windows Server 2008 R2 64bit as the guest OS.  I installed my first copy of SQL Server 2005 Standard under a named instance of SQL03\SQL2005Standard (I currently don’t have a standard instance of SQL Server installed on this VM).  I then proceeded to install SQL Server 2005 Enterprise, SQL Server 2008 Standard & SQL Server 2008 Enterprise all as named instances on the same VM as SQL03\SQL2005Ent, SQL03\SQL2008Standard, SQL03\SQL2008Ent, respectively.

Here is what I learned about supporting multiple instances of SQL Server from a DBA point of view:

  • The default instance of SQL Server, known as MSSQLSERVER, runs under TCP port 1433
  • Under Windows Server 2008, the firewall is enabled by default so you need to create inbound rules to allow outside connections to be able to reach SQL Server.
  • SQL Server Browser service is responsible for taking requests for named instances and forwarding them to the correct port, whether it’s dynamic or statically configured.  If there are no named instances of SQL Server, the Browser service is not required.
  • The SQL Server Browser service runs under UDP port number 1434.  This will need to be opened under the firewall.
  • With out the SQL Server Browser service running, you would need to provide the IP and Port number (xxx.xxx.xxx.xxx:yyyy) in order to connect to a SQL Server instance.
  • To connect to a SQL Server instance by name, you use the <computer name>\<instance name> syntax.
  • By default, named instances are set to dynamic ports.  This causes an issue trying to open ports on the Windows firewall because each time SQL Server is restarted a new port can be set.  With the firewall enabled, it’s best to switch to static ports for all named instances and then create inbound rules for those TCP ports.
  • If you prefer to use dynamic ports, you can also exclude the sqlserver.exe process from the firewall.  This would allow inbound connections for just the SQL Server process and thus all clients to connect to dynamic or static ports without having to create individual rules for each TCP port number.
  • To set an instance to a static port, open SQL Server Configuration Manager (SSCM) and drill through SQL Server Network Configuration and expand the Protocols tree for your named instance.  Right click on TCP/IP and select Properties.  Click on the IP Address tab and scroll all the way to the bottom of the list until you see ‘All IP’.  Clear out the TCP Dynamic box and under TCP Port, provide the desired TCP port number.  To switch back to dynamic port allocation, just clear out the TCP Port box and place a zero in the TCP Dynamic box.  Any time you make a change to the Protocol settings, you will need to restart the SQL Server service for the named instance.
  • TCP and UDP ports are registered with a national directory.  You can reference them by using this Wikipedia article.  I chose to use TCp ports 1435 through 1438 for my instances.  Well known ports are from 0 – 1023, Registered ports are from 1024 – 49151, and Dynamic ports are from 49152 – 65535.
  • To see a list of ports the server is using, run ‘netstat -an’ from the command prompt.  Use this data along with available ports from Wikipedia to determine what ports are available on your system.
I hope you find this useful when you try to install SQL Server with multiple instances when the Windows Firewall service is enabled.

The Wonderful World of Blogging!

Doug

Welcome to my blog.  My name is Doug Purnell and I live in Greensboro, NC.  I’ve been in the technology world for the last 20 years ever since graduating from Appalachian State University with a Computer Science degree.  I started in the Visual Basic 4.0 & SQL Server 6.5 world back in the early 90’s and have loved IT ever since.

My goal is to try to convey my experiences in the IT world from SQL Server, VMware, and the data center.  I’ll even try to throw in a few thoughts on Apple products, Nikon Photography and my weekend cookouts on the smoker.