SQL Server with Mr. Denny

March 29, 2012  2:00 PM

SQL Server Replication and DR

Denny Cherry Denny Cherry Profile: Denny Cherry

Every once and a while people ask me if they should use SQL Server Replication to get data to a DR site.  And typically to them my answer is “probably not”.  The reason that I say that is for a couple of reasons.

1. If there are triggers on your tables, replication doesn’t have a way to ensure that the triggers will be there on the remote site.

2. If you need to add tables, procedures, views, etc. you have to reinitialize the subscription to add the new articles to the subscriber.

3. The failback story is pretty much a mess.  Assuming that you do have to fail over to your DR server failing back isn’t exactly the easiest thing to do.  Basically you have to take another outage while you move the database back.  That or you have to resetup replication in the other direction.

Needless to say that these are some pretty good reasons to not use SQL Server Replication to get data to your DR site.  Especially as there are so many better options such as Database Mirroring, Log Shipping, storage replication, third party storage replication and soon enough AlwaysOn Availability Groups.

If you are using SQL Server Replication to replicate data from your production site to your DR site I urge you to look at the other options which are available to you and you should strongly consider moving to one of the other technology options.


March 26, 2012  2:00 PM

Want to know the quickest way to get your recruiting email deleted?

Denny Cherry Denny Cherry Profile: Denny Cherry

We all get emails from recruiters.  I know that I’m shocked on a day that I don’t get them.  Personally I love reading these emails as they are usually pretty damn funny.  But I’m seeing a very disturbing trend in these emails.  This trend is making job candidates pay to attend out of town job interviews.

If I was looking for a new position, and I saw the magical line “candidate must pay for all interview travel expenses” showed up in a job posting I’d just keep on moving.  If you don’t want to interview out of town candidates then don’t.  It’s really not all that hard to figure out.  Now the thing that really pissed me off about this specific job posting was that the company was willing to hire a remote employee, but they expected the remote employee to pay for all travel costs for the interview.  So does this mean that if hired I would be expected to pay my own way to company meetings?

If you are willing to hire a remote employee part of that includes paying for the candidates expenses to get out to see you for the job interview.  There’s nothing that says that you need to bring every candidate out to see you for the in person interview.  There’s something called a phone screen which is how you filter out the people you don’t want to bother to interview in person.

One of the costs of doing business with a modern distributed workforce is paying for a few extra plane tickets when hiring for a new position.  So suck it up, stop being so cheap and pay for the flights and a night in the hotel.  When you are hiring someone you need to put your best food forward so that you look as good as possible so that you can attract the best possible talent.


March 22, 2012  2:00 PM

How I use sp_whoisactive and sp_who3

Denny Cherry Denny Cherry Profile: Denny Cherry

On occasion I’m asked to come and look into some SQL performance problems on systems.  Normally when I’m doing this the customer doesn’t have any third party tools installed and they are actively having problems, and these problems are leading to lost revenue or at least really pissed off customers and/or employees.  As the systems are currently in a system down situation there’s usually no time to pitch, buy, install and configure a performance monitoring solution from one of the many vendors.

When I run across these sorts of problems the first thing that I do is put sp_whoisactive and sp_who3 on the server.  As these are just stored procedures getting approval to install them is usually pretty easy.

Once the stored procedures are installed sp_whoisactive will pretty quickly tell you the stored procedures which are having performance problems.  If the output is showing a CXPACKET wait type that’s when sp_who3 comes into play.  Running sp_who3 with a SPID number after the procedure like “exec sp_who3 125” will give you the wait type for each thread within the process.

When doing this recently on a system sp_whoisactive showed me that CXPACKET was the wait type.  After digging into the process with sp_who3 I saw that one of the threads was waiting on SOS_SCHEDULER_YIELD.  I then used sp_whoisactive to get the execution plan showing me the missing index which needed to be created.  In this case there was a clustered index on the table which was being scanned.  Based on the page count output from SET STATISTICS IO we were scanning the entire table every time the query was run.  This massively expensive query was causing the query to parallelize and the run time to go insanely high.

Once we added the index we figured out the man hours wasted per day by having the index missing.  We assumed that the procedure was run 3 times per second over the course of a 12 hour period and the query was taking 3 seconds to complete before the index was added.  After the index was created the query was running in just a few milliseconds.  This single query running for 3 seconds, 3 times per second, for 12 hours comes out to 108 man hours wasted while this one query was being run.

Using two community written stored procedures and a few minutes of troubleshooting time 108 man hours per day are now being saved for that specific company.

Hopefully you’ll find these stored procedures to be useful in your performance troubleshooting.  They aren’t hard to use, but they sure are useful.


March 19, 2012  2:00 PM

Windows 8 Boots With No Network

Denny Cherry Denny Cherry Profile: Denny Cherry

So I’ve run into a bit of a strange issue with Windows 8 on my laptop that I wanted to share in case anyone else runs into the same problem.  My basic problem is that about 50% of the time when my laptop boots up into Windows 8 it can’t find the network.  I know that the WiFi card is working correct because it can see the list of networks in my area, I just can’t actually talk to any of them.  If I do an ipconfig from the command line I can’t even see the LAN or WiFi network adapters even though they are showing up correctly in the network adapter window in the control panel.  (All pictures can be clicked on to view them full size.)

As you can see the WiFi card found the WiFi network there was just no TCP information coming down from it.

What I was able to figure out is that there’s some TCP/IP setting which isn’t being saved correctly (or at some times is being wiped out from the registry for some unknown reason).  To fix the problem in the Network Connections window (shown above) double click on of the network icons (I used the WiFi one).  To verify the problem is the same you should see no bytes being passed at all.

If you click details the box which would normally have all sorts of useful information will be blank.

Close the Network Connection Details and on the Network Status (WiFi Status in my picture above) click the “Properties” button.

In the list in the middle scroll down until you find “Internet Protocol Version 4 (TCP/IPv4)”.  Select this and click the properties button.  This will show you a window similar to the following.

If you have values specified in this screen that are different from my settings shown in the screenshot DO NOT CHANGE THEM!  If you change these settings without knowing what you are doing you won’t be able to get online and only the person who setup your network will be able to help you fix the problem!

Now click the “OK” button on this screen (without changing anything), and click the “Close” button on the prior screen.

You will notice that under the Activity section the numbers are no longer 0, which means that data is flowing through the network card to the rest of the network (and in my case to the Internet as well).  If you click the “Details” button you’ll see you now have an IP address and everything is happy again.

If you look at the output from ipconfig again we’ll see that the network adapter is now listed and is receiving IP address information.

If you do end up running into this problem yourself, remember these steps as you’ll probably have to do this pretty regularly.


March 15, 2012  2:00 PM

My First Day with Windows 8

Denny Cherry Denny Cherry Profile: Denny Cherry

So I’ve taken the plunge and installed Windows 8 Client Preview on one of my laptops.  The install process was pretty simple and straight forward.  The installer found my WiFi card so I was able to get everything up and running right out of the box.  The next thing to do was to install SQL 2012 and Office.  I started with SQL 2012 and it installed without issue.  Office 2010 installed without issue as well.

Thankfully Windows Live Mesh also installed without a problem and was able to sync up the My Documents folder to my desktop and other laptop so everything showed up on the laptop just as I expected it to be.

The lack of the classic start menu is a little annoying.  You can however hit <Windows>+W to get something pretty similar up on the screen.  I’ve only had an hour or so to play with Windows 8 so far, so I haven’t done a whole lot with it yet.  Task manager is pretty cool looking with a lot of additional information being included.

Now so far I pretty much hate the “Start Menu”.  Everything is sitting there with no folders.  Needless to say this isn’t exactly efficient with a bunch of applications installed.

I like the changes to the Windows explorer with the ribbon.  I think I actually like it better than the old drop down menus.    One great new feature is that Windows 8 can, without any 3rd party software, mount ISO images to make it easier to install downloaded software.  Is makes life much easier when installing all the various Microsoft software that one uses to work, as Microsoft distributes most everything on MSDN and TechNet as ISO files these days.

So by default (if Windows found a network when installing) the Windows accounts are tied to your Windows live account (if you don’t have a live account it’ll either prompt you for one or make the accounts local accounts like they used to be).  This allows you to sync your settings between your Windows 8 computers.  Once I’ve got several Windows 8 machines this will probably be handy.  Until then this feature doesn’t really do anything for me.

As I use it more I’ll post another update.


March 15, 2012  3:40 AM

Windows 8 does have a Start Menu, sort of

Denny Cherry Denny Cherry Profile: Denny Cherry

Everyone says that Windows 8 doesn’t have a start menu, and it’s true there’s no Start Button on the task bar like in the last several versions of Windows.  However if you press <Windows>+W you’ll get a sort of start menu.

There’s an Apps button on that menu, which when pressed will give you the pretty classic looking Start Menu.

I’m sure that I’ll be using this feature pretty often as most apps don’t have a Metro icon.


March 12, 2012  6:16 PM

SQL Saturday 120 Storage and Virtualization Pre-Con

Denny Cherry Denny Cherry Profile: Denny Cherry

Interested in my SQL Bits pre-con, but you just can’t make it out to London for SQL Bits?  Have I got a deal for you.  It has just been announced that I will be giving my Storage and Virtualization pre-con at SQL Saturday 120 on March 23rd, 2012.

SQL Saturday is bringing you this pre-con for just $99.  All this is available without leaving the good old USA.  Sign up now at http://sqlsat120precon.eventbrite.com to reserve your seat and I’ll see you there.

If you can’t make it to the pre-con don’t forget to register for the full day of training at SQL Saturday 120.  We’ve got some great local speakers as well as a ton of speakers from around the US who are flying in just to see your smiling face in their sessions.  So don’t stand them up.  Some of the out of town speakers include Kendra Little, Jeremiah Peschka, Randy Knight, Grant Fritchey, Aaron Nelson and Audry Hammonds.


March 12, 2012  2:00 PM

AlwaysOn and Error 41006 (and error 41158)

Denny Cherry Denny Cherry Profile: Denny Cherry

When setting up AlwaysOn Availability Groups you may receive Error 41158 which references error 41006 when you attempt to join the the replica to the Availability Group.  What these errors in a nut shell mean is that it ain’t going to work with your current configuration.

Assuming that you ran through your SQL Server installation and went next, next, next through the install this result is to be expected.  The reason for this is that your SQL services are all running under local accounts which don’t have the ability to log into each other.  There’s two solutions to this problem at this point.  1 is supported, the second isn’t.

Option #1 – aka. The Supported Option

Reconfigure the SQL Services which will be hosting the Availability Group Replicas to run under a single domain account.  Restart the services.  Give the SQL Account that the services are running under sysadmin rights.  The replicas should sync up automatically at this point.  If they don’t you can use the ALTER AVAILABILITY GROUP command to join the AG.

Option #2 – aka. Totally Unsupported, but works great for a demo

Add the domain computer account for each of the nodes of the cluster to each others SQL instance so that they can log in.  For example the four computers which I use for my demo are called ALWAYSON1, ALWAYSON2, ALWAYSON3, and ALWAYSON4.  So on machine ALWAYSON1 I added the domain accounts BACON\ALWAYSON2$, BACON\ALWAYSON3$, and BACON\ALWAYSON4$ as members of the sysadmin fixed server role (again this is for my demo lab so I’m going for working not secure).  On machine ALWAYSON2 I add BACON\ALWAYSON1$, BACON\ALWAYSON3$, and BACON\ALWAYSON4$ and so on for machines 3 and 4.  Once that was done the replication should being syncing up automatically.  If they don’t either use ALTER AVAILABILITY GROUP or use the UI to force retrying.


March 9, 2012  8:00 PM

Still time left to sign up for SQL 2012 training

Denny Cherry Denny Cherry Profile: Denny Cherry

Some seats are still available for my SQL Server 2012 class which kicks off March 19th, 2012 in Los Angeles, CA.  If you are planning on deploying SQL Server 2012 in the near future this training class is for you.  But don’t wait to get signed up.  The sooner you get signed up the better off you’ll be.

This training class is 4 days long and will be focusing on 4 key areas of SQL Server 2012.

  1. Planning and Installation
  2. Mission Critical Deployments (aka High Availability, programming and migrations)
  3. Breakthrough Insights (aka BI)
  4. Manageability and Security

This full four day class includes not only lecture, but lots of hands on labs which are only available through this class.  All this is available for just $1200 which covers all four days.

So get signed up today!

March 8, 2012  2:00 PM

Less SQL Saturday’s for me this year

Denny Cherry Denny Cherry Profile: Denny Cherry

I’m sad to say that I’m going to have to cut back on the number of SQL Saturday’s that I’m going to be able to attend this year.  It’s not because I don’t love PASS, or SQL Saturday, or the attendees as much as I did before, because it’s not.  I’m just so busy that I’ve been royally screwing up the whole work live balance thing so far this year so far.  In the first two months of the year I’ve been home for something like 10 days, and 4 of those I was sick with the flu (by the end of March I’ll be home for 2-3 weeks total depending on if a trip happens or is canceled).

Between work and the conferences like Tech Ed, SQL Days 2012, etc. that I’ll be at I just need to make sure that I’ll be at home at least a little bit so that Kris doesn’t kill me.

I’ll be at my local Code Camps and SQL Saturday’s (I’ve even got to leave SQL Saturday Huntington Beach early for my flight to SQL Bits) for sure, I’ll be in Atlanta for sure.  If there’s one the weekend before or after PASS I’ll try and hit that one.  Other than that I’m afraid that I’ll probably have to keep it pretty light.  Hopefully next year I can cut back all this other travel and get back on the SQL Saturday circuit a bit more.

Hopefully I’ll see you at one of the few SQL Saturday events that I’m able to attend, or one of the bigger conferences.  SQL Saturday 120 is next followed by SQL Bits.


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: