So I arrived home from my first SQL Bits conference, which was the 10th SQL Bits event (they do two a year). I’ve got to say that I had a great time at the conference. SQL Bits, if you aren’t away is a three day event with the first day (Thursday) being all day sessions, and the second and third days being normal hour long sessions. Day two (Friday) is only for paying attendees while day three (Saturday) is open to anyone who registers and there is no cost to attend.
I was able to have sessions on all three days, doing my “Storage and Virtualization for the DBA” pre-con on Thursday, a session on AlwaysOn on Friday and a session on Virtualization on Saturday. Stacia Misner (blog | @StaciaMisner) and I also had a joint session on Satuday which was part 1 of an all day session which we did at pass which explores the BI side of the SQL Server workload and how those BI processes impact the OLTP database and the EDW/ODS/Reporting databases as data is loaded into them, reports are run and OLAP cubes are updated. (We briefly slowed a link at the end of the slide deck, which points to this page for some additional reading.) Hopefully Stacia and I will be invited back to give the rest of this presentation at the next SQL Bits session (thankfully we were able to end at a pretty good place this year).
A few of us made our way to London a few days early (as well as other cities) in order to try and make it as easy as possible to kill the jet lag before the conference started (I don’t think there is anything worse than trying to give an all day presentation when you are 8 hours off of your normal timezone). Kris (my wife) and I spent a few days before SQL Bits doing a little sight seeing with Stacia, Erika Bakse (blog | @BakseDoesBI) and Adam Machanic (blog | @AdamMachanic). We were able to see some of the great parts of London like the very old Tower of London parts of which date back to the 1200s or so as well as Westminster Abbey and we walked around by the Parliament building.
One of the very cool things that we did was on Wednesday when Stacic, Erika, Kris and I met up with Buck Woody, Jen Stirrup, Lara Rubbelke and a couple of others (I just can’t remember who else was there, I know that Jen and Stacia took pictures of the group) met for lunch in London at “Ye Olde Cheshire Cheese” which was the pub frequented by Charles Dickens while we wrote many of the works for which he is famous for. It was a great lunch with great friends in a pub which has been around for hundreds of years (except for when it burnt down in the great London fire). According to WikiPedia there has been a pub in that location since 1538 and it was last rebuilt (according to WikiPedia and the sign at the pub also shown on WikiPedia) in 1667.
One thing that SQL Bits did, which they did for the first time was invite the speakers and sponsors spouses/guests/etc. out for the afternoon on Saturday so that they wouldn’t be stuck sitting around the hotel for the day. The outing, which Kris attended, was lunch followed by a matinee showing of a London play. Kris said that she had a great time and she made some new friends (hopefully she remembered to collect email addresses). This was SQL Bits way of showing some thanks to the speakers partners for loaning them out for the weekend (and giving them some incentive to want to come to the conference and see what it is that we all do at these events as most of the time our partners want to avoid the conferences like the plague).
Something that I thought was really interesting about London was the mixture of the very old buildings mixed in with the ultra modern looking all Glass buildings. As an example I was standing on the wall of the Tower of London looking over the river Thames looking at the gate where prisoners were brought through, and right across the river were several brand new all glass sky scrapers looking down on us. You can see this a little in the below picture (click to view full size) which shows the entire front side of the Tower as well as some new buildings at both the left and right edges. (Just before leaving for London I picked up a new Android Nexus cell phone, running Android 4 and it’s got a kick ass panoramic photo mode built in which is what I used to take this picture.)
After hours during the conference there were of course some fun activities as well as the sigh seeing around London, which I hadn’t been to since I was a little kid. There were some pretty rare sights to be found…
Surrounded by some of the other attendees and speakers.
Now I’m not going to say that it was a little late when these pictures were taken, because it wasn’t. It was actually pretty early … in the morning. This all happened about 3am and we were still going strong and most importantly we were all there the next day right when we needed to be so that the conference could continue on without a hitch.
Below are a few random pictures from our sigh seeing that I wanted to share with everyone.
The statue in front of Buckingham Palace.
The front of Buckingham Palace.
Some chocolates that I know for a fact that Paul Randal would love to have.
In closing, thanks again to the SQL Bits team and all the attendees. I had a great time, and I hope to be able to attend the event next time.
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.
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.
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.
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.
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.
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.
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.
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.
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.
- Planning and Installation
- Mission Critical Deployments (aka High Availability, programming and migrations)
- Breakthrough Insights (aka BI)
- 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!