May Already

Can't believe it is May already; or that I haven't posted here since February.  Yikes, and Sorry - looking for a job while working full time at a place that is winding down makes for a very busy schedule.

To date, I have had 15 interviews.  Another tomorrow.   I had 4 with one company and hope for the best.  Looks like I will need it by June 1.

On a good note, I attended SQL Saturday 118, won a book in the raffle on SQL 2012.  I signed up for SQL Saturday 119, but not sure if I can swing it quite yet.  I followed a bunch of the speakers and have read many of their blogs.   I really liked the optimizing TempDB talk - Eddie Wuerch just seemed really comfortable and knowledgeable.  I have already used some of the scripts from "Its a PowerShell Life."   Tim Ford spoke on a day in the life of a DBA and tips for efficiency, and then I noticed I have a book he co-authored on Performance Tuning.

I have been reading Izik Ben-Gan's books on T-SQL Querying and T-SQL Programming; I've got 24 hour SSIS and the thick SSIS book, the 2012 book and Professional Programming in SQL 2008 ... all told 10" of SQL books to read, and that doesn't include the 70-432 textbook. --deep breath--

I shall finish them before fishing season is over.  It opens on Saturday... the kayak is prepared, the milk-crate is loaded and fitted with new pole holders.  Its going to be a great Summer!

More Take-Aways from TechOnTap

One of the extra benefits of http://www.techontap.org/ 's TechOnTap V1.1 was some book give-aways brought by Grant Fritchey and supplied by RedGate.   Thank you AGAIN. I got SQL Server Statistics by Holger Schmelling.  Everything below is from the book's first chapter discussing the basics, but I add a few of my own comments.

The Setting - Blood Center of Wisconsin - 

I give blood platelets on a regular schedule, about once a month; this benefits 3 Luekemia or cancer patients, helps me by taking the sticky clotting agents out of me and giving me about 2 hours to read.  There are studies that show up to a 30% reduction in heart disease for regular donors of blood.  I encourage all of you to also 1-800-BeAHero.   Only about 5% of the population who can give, do.  Whole blood donations may take only 1/2 hour.  Do it.  Be a Hero. End of speech.

Cool Queries in the book -

This is a very useful table as a row provider... the author credits Itzik Ben-Gan's Inside Microsoft SQL Server 2005 T-SQL Querying [I]

I added Use StatisticsTest; (my first run put it on the Master. Oops.)

Select * from Numbers
where n > 4999990
 reveals: 10 rows
 two columns, the primary key which has a sequential record number starting at 4999991 and a corresponding n column that has the same number as the ID up to our 5000000 in the tenth row.  This might indeed be handy.

What's up with sys.trace_event_bindings ?
http://www.sqlnotes.info/2011/12/09/sql-server-tracing-system-1-dmvs/  this unfortunately did not enlighten me much, as a newbie nor did  http://technet.microsoft.com/en-us/library/ms175073.aspx    Looks to me like since we are using the meta data built in Row_Number in our select, that we have to use this other variable, but I am curious about the order by and the from as sequence using it as b1 and b2...  Perhaps you can enlighten me with an explanation of the invocation of sys.trace_event_bindings in a comment below.
------
I renamed the T0 to Tzero to be more readable; T0 is just too to-looking.

(you can look at the estimated and actual row counts by highlighting a select statement row and <CTRL>+L and then hovering over the operator)  I think the most interesting thing about the select c=2000 is the cached size of the entire query is 24b, and it computes a scalar value for the =2000; but that's probably just me.


                                   

TechOnTap Recap Part A - Brian Lewis

I spent this last Saturday at the TechOnTap v1.1 (Virtualization) in Appleton WI with 30 other colleges from all points Wisconsin and beyond. You can read about it here and Jess Schultz Borland's Blog about it here.  I could not agree with her assessment more.   Perhaps a few better sight lines to see the other participants might have been in order, but for a first try, this was really well done.

Brian Lewis (Blog / Twitter) caused a bit of laptop envy in me (2 i7's and a portable SAN?) and also answered my questions about some HyperV and VHD issues.  In passing, he mentioned a SysInternals Utility called Disk2VHD which you can download free here.   I love SysInternals utilities PMon, ProcessExplorer, the PStools, RegMon, Autoruns, PSkill, and PSShutdown are all tools I use often.

I knew immediately that I needed to get this Disk2VHD utility and give it a go on my main XP sp3 machine.  It is vital to so many things that I do with some legacy software, it will be interesting to see how it runs in a VM.

As with so many SysInternals tools, you can run it from a command line or use the simple GUI that pops up.  It is fairly intuitive, and yet it did not run for me until I fixed a few problems.

Disk2VHD uses Volume Shadow Copy Service VSS, and this needs to be turned on - so I went out and checked this via right-click on My Computer > Manage > Services and found it to be triggered by my first run of the utility, but set to manual.  Just to be sure, I set it to automatic, stopped and restarted it.

Disk2VHD also uses Microsoft Software Shadow Copy so I went through the same steps - and then dutifully rebooted my machine and reran the utility.   Error 12302 shows up in the event log. pfft. "Error Snapshotting Volume" - sometimes it would just flash, fail and disappear.  From the command prompt, it would also fail and stop.  Here is where being familiar with the event viewer is really helpful.  This App writes to the application events, oddly enough.

Que the batman trumpets ->to the google(onBing ...ehem) we go...  A B and C each gave me a piece of the answer.   I had to get the two services going and then go find the offending HKEY_LOCAL_Machine\System\CurrentControlSet\Control\ContenIndex\Catalogs that had a missing location registry entry.  Mine turned out to be a Visio I had uninstalled (apparently only partially).  Removing this allowed the utility to run.

As you can see, I have a C: and a D: disk, the others E: G: and H: were partitions on the USB attached hard drive, and I sent the resultant partitioned VHD to H:  Also, we have progress bar and estimated time of completion. which is good to know.   I have to be somewhere tonight, so I am going to let it run and come back later...

To be Continued....

The first set you see above was an XP with two physical hard drives.  It ran immediately, but I soon noticed that all of the extra stuff I had on it would not work well in a VM and began to uninstall anything that was codec, video, sound production related.   I suppose I could boot to vhd, but why would I want to when I have it in a real machine with two screens?   Good to have a back up if this up and dies though.

My second machine is a PhenomIIx4 based which I am dual booting Windows 7 home edition that came with it, and also Windows 2008r2 Data Center with the HyperV role.  The Windows 7 disk turned out to be 48G; but when I set up the VM to use it, it is throwing the error "Missing Operating System"  Nuts.  Next we'll try booting to VHD with it and see if that works at all.  Otherwise I'll make another attempt at it - the goal is to reclaim the hard drive space that one is taking up, and still be able to boot to it or have the HyperV  VM version of it.

While I was waiting for Disk2VHD to install, I downloaded and set up Windows 8 developer in a VM.  Going to hopefully use this VM as a receiver for SPADE.  Stay Tuned.

sqlSPADE Presentation Scheduled for March

I always felt bad when John our WISSUG leader(http://wisconsin.sqlpass.org) would ask for help, and suddenly the sound of crickets would fill the air.   It isn't that we don't want to help, many do help; it is just that none of us really wants to expose our own ignorance.   I am always grateful for the people who choose to speak, prepare demos, take questions and then I am amazed when they are able to answer the questions... or speak those words that men find it so hard to say: 'I don't know'.  The good thing is ignorance is curable for the diligent, and the persistent.  

Kevin Kline who spoke a few years ago at WISSUG, (http://kevinekline.com/) tweeted about SPADE - http://sqlspade.codeplex.com/ Scripted PowerShell Automated Deployment Engine for SQL Server, and knowing that Jon is going to speak on PowerShell topics in March, I thought I can do installations... so I volunteered to present sqlSPADE to our group.

So I have downloaded all the files and am setting it up for execution in a VPC environment.   I know I am going to learn a bunch in this process.  I expect it to be really valuable for my future, and hopefully others too. :)

"SPADE simplifies the process of standing up a new SQL Server instance by applying standard Operating System pre-configurations, Installing SQL Server and then applying post-configurations and creating standard objects.

SPADE is a tool that is designed to speed up your standard deploymets of SQL Server. You may be saying "But I can already do an unsattended install"...but that's not all that's involved in most server builds. There are Operating System configurations like Microsoft Distrubuted Transaction Coordinator (MSDTC), Local Security Policy and others. I'm sure that you also have standard SQL objects that need to be deployed like Stored Procedures, Agent Jobs, Operators, etc. All of this can be done by SPADE automatically by running 1 simple PowerShell script."

I think I heard somewhere tonight "Simple doesn't necessarily mean easy" maybe it was @onpnt who said it at the @madpass tonight.   I am looking forward to this project.

No good, very bad, then ok but messed up day.

The best laid plans.   Interview downtown at 9am.   Snow and ice storm don't scare me.   Until someone stops unexpectedly in front of me, and I can't avoid sliding into them, having just passed into my second hour of travel for a normally 25 minute trip.  Busted the front plastic cowling in my truck, missed my appt. pfft.

Came home to try to get some work done.  Had a remote PDC with a going-bad hard disk.  Time to transfer the DHCP, FSMO, set up the VPN and change the IP's and make the member DC the PDC.  This plan came together so well, only one person knows, because he did the final IP changes for me on site.   The best Sysadmin tasks are properly performed when nobody notices. Lovely.

Started to sit down to study SQL and hear, HEY DAD THE GARAGE DOOR is messed up!  I head up to look at a now trapezoidal shaped 4 part metal 400# door hanging like a leaf 6" low on one side and the roller off the top on the other side.   OH BOY says I.

Seems one of the spring cables picked today to rust through, and then in trying to get the darn thing down, we bent our vertical rail on the other side into a twisted pretzel.

Just in case this ever happens to you, the method requires another person (dragged my son away from the Biggest Loser and then Parenthood... eventually).

I carefully unscrewed the hinges on the top section and let it hang till help came.  Clamps help to hold it in place.   Took it off and laid it down.  Then did the same with the second top section.  Lifted the two remaining up on the rails as high as they went, got my 3# hammer and an adjustable wrench to bend and pound the twisted rail into a reasonable facsimile of straight.  I popped the cable that was now slack off the side that was still good.

I was surprised that the side where the spring was still working was the pretzel side.  hmmm.


So having dragged my son into the cold again, I reassembled the door in place and clamped it down to lock it.

Did I mention that Windows 7 started to blue screen today?  I hope that is the third thi

Database Mail using External SMTPs #fail

It is Saturday morning.  The teenagers are fed.  I made pancakes, added blueberries to my own, and since I am on a health kick this year too, I only topped mine with applesauce, but somehow they still added up to nearly 1100 calories. (holy crap!).  They got the melted butter and syrup on oven-warmed plates.   I even cleaned the entire mess up and washed the dishes, so my wife is happy too.  With a belly full of blueberry pancakes and a fresh pot of peppermint mocha coffee I have about two hours to blog before the droopies set in and my mind turns to mush from carb overload, or the honey-do list is presented for immediate service.

Today's task will be to install Database Mail.  For those of us who are new to SQL, there used to be SQL mail, it was dependent on Exchange or some other MAPI system.  You may run into that in older instances, but for my purposes, I really want to move forward, so that's it on that.

You still need to have an SMTP server though.  So, if you are like me running servers that are going to go away in a couple months, you want to do so for FREE, and on a permanent server if possible.  (I love free; my wife usually agrees that I can afford it).   To the GOOGLE:>> insert batman trumpets>>  "Free SMTP Server"   And there it is - "How to use Gmail as your SMTP server".     Somehow, keeping my server clean of this service appeals to me.  

http://lifehacker.com/111166/how-to-use-gmail-as-your-smtp-server

I see a couple caveats in my first perusal of the instructions here: if your network "lets you connect to an outside SMTP server"; this one could bite us.   The second they mention is that the reply is overridden, but we are not going to send mail back to an alert, so this is no problem.   Update 3 is interesting for our purposes too.  If we set up an email specifically for this purpose, we can use that account name or set a different default, like DBALERT or something.  Hmmm.

Sidenote:

http://www.softstack.com/freesmtp.html

Has a few possibles individual servers, each appears that it would need to be installed.  This is not an endorsement of any of them, just a note that if you spend more than the 2 minutes I have, you will probably find one that suits your needs; I have only clicked on the first three links.  Maybe I'll install a few on the VPC side, which will install and then go away immediately and perhaps blog on this again in the future.  It sucks to be curious about everything - Let's stay focused on SQL.  

Yep.  Indeed that was too easy.  Failed on every try, even with the corrected ports; probably the TLS limitation that I don't see intuitively how to set.  Normally, I make things more complicated than they are.  But when it seems to easy, it probably is.  

We'll have to do this by the book.

MLK Day 2012 life after football

“If you can't fly then run, if you can't run then walk, if you can't walk then crawl, but whatever you do you have to keep moving forward.”  ~ MLK


A good quote for beginners - you can't begin unless you try to begin. 


Started to post Saturday, but bogged myself down in secondary stuff... learned lots of the ways that Database mail doesn't work in trying to avoid setting up a local SMTP; hopefully most of them.  I guess I'll set up an SMTP  server after all.


The saga continues tonight, in life after football.  Packers #fail. (**sigh**)

Decided to install a named instance just to go through the process again.

There is a hyerlink in the Successful install page http://go.microsoft.com/fwlink/?linkId=87843 for the version specific SQL Sample Databases.  And there is an installation summary that you can review as well, that comes up in a notepad, with machine properties, product features discovered, package properties and user input settings.  This is all saved in a configuration file  on c:\Programfiles\ microsoft sql server\100\setup bootstrap\log\a variable here\systemconfigurationcheck_report.htm

I also found some great resources on SQLskills.com - like corrupted databases... and MP3's on some Master SQL topics that I can listen to on my little mp3 player while working out.  I'll post on the back ups later.

W2k8r2-HV and VPC installs

The install on HyperV went swimmingly.  I used all defaults, on my w2k8r2 VM template, but had my 2008 DC open and added the service accounts in the service accounts OU (trying to use least privilege best practices).  I bet this will cause me some pain later, but those are the kinds of things I will need to know.  The host is not a member of the domain, but the client VM is.   I ran the install of the AdventureWorks sample databases that can be found here:http://msftdbprodsamples.codeplex.com/releases/view/55926   Boy, that was easy.

Every where I look says you should use Windows Authentication rather than SQL or mixed mode only.  Why?  One word: Groups (well, security groups).  If you have lots of users you will want them to also have least privilege, so you have domain Organizational units, Groups, GPO's, and perhaps NTFS layers of security all to look forward too.... well some of them for sure. 

One of my random thoughts here, is how am I going to simulate all of this access and load in the future?  hmmm. 

The vpc version is using a differencing disk, and the darn child disk trips the activation sequence in Windows 2008 every time i over write it.  So, we are probably going to let that one be, use it only for quick tests and concentrate on the HyperV version instead, where the activation NAG doesn't pop up each time you run it.  Anyone ever over come this?  Am I missing something?

Tonight, I have a 2 hour work-out planned and then we shall see about running some SQL backups of the AdventureWorks databases.

So where to begin?  I have the Microsoft SQL Server 2008 Training Kit from Microsoft Press and a stack of techlabs that begin with installation of the product.  I am a systems admin so I am familiar with Windows Servers, and I intend to install this one in a HyperV virtual machine, and also for good measure, a standard VPC virtual machine.   The HyperV will be connected to my 2008 domain and we'll try to set up domain service accounts.  So this is my task for tonight.  I'll post again with any problems I run into.

One guy's journey from Normal to Normalized...

Its a new year.  I need to do a couple things:
 a)  Get fit
 b)  Get more SQL knowledge - if I want to be a DBA, I need to BE a DBA
 c)  Get a full time SQL job (well that's the goal)

Each of the above will require daily effort and commitment.  So the blog is to chronicle the daily progress of my journey from normal to normalized.

75% of all statistics are made up off the top of someone's head.  That being said, 90% of acquired knowledge happens because we desire it to happen.

There are books and books and books on MS SQL.  I have a ton of them.  But I come with a small cup to this ocean of information, and seem to get stymied at every turn.  But I wondered if anyone has ever really chronicled their own progress like this, and thought it might be enlightening to do so, for others who start at the ocean as I am without much of a paddle or a plan.

So the goal is to start in 70-432 materials and plow through them, and try to explain some of the white paper style geek-speak into something for normal people.  Wish me luck.  I will need it.