The Bit Bucket

I posted another podcast over the weekend. Late last week, I managed to get a show recorded with Adam Machanic. Adam's always fascinating. In this show, he's talking about what he's found regarding increasing query performance using parallelism. Late in the show, he gives his thoughts on a number of topics related to the upcoming SQL Server 2014.

Enjoy!

The show is online now: http://www.sqldownunder.com/Podcasts

 

I have to say that I continue to be frustrated with finding out how to do things in Windows 8. Here's another one and it's recorded so it might help someone else. I've also documented what I tried so that if anyone from the product group ever reads this, they'll understand how I searched for it and might try to make it easier.

I wanted to add a network loopback adapter, to have a fixed IP address to work with when using an "internal" network with Hyper-V. (The fact that I even need to do this is also painful. I don't know why Hyper-V can't make it easy to work with host system folders, etc. as easily as I can with VirtualPC, VirtualBox, etc. but that's a topic for another day).

In the end, what I needed was a known IP address on the same network that my guest OS was using, via the internal network (which allows connectivity from the host OS to/from guest OS's).

I started by looking in the network adapters areas but there is no "add" functionality there. Realising that this was likely to be another unexpected challenge, I resorted to searching for info on doing this. I found KB article 2777200 entitled "Installing the Microsoft Loopback Adapter in Windows 8 and Windows Server 2012". Aha, I thought that's what I'd need. It describes the symptom as "You are trying to install the Microsoft Loopback Adapter, but are unable to find it." and that certainly sounded like me. There's a certain irony in documenting that something's hard to find instead of making it easier to find. Anyway, you'd hope that in that article, they'd then provide a step by step example of how to do it, but what they supply is this:

The Microsoft Loopback Adapter was renamed in Windows 8 and Windows Server 2012. The new name is "Microsoft KM-TEST Loopback Adapter". When using the Add Hardware Wizard to manually add a network adapter, choose Manufacturer "Microsoft" and choose network adapter "Microsoft KM-TEST Loopback Adapter".

The trick with this of course is finding the "Add Hardware Wizard". In Control Panel -> Hardware and Sound, there are options to "Add a device" and for "Device Manager". I tried the "Add a device" wizard (seemed logical to me) but after that wizard tries it's best, it just tells you that there isn't any hardware that it thinks it needs to install. It offers a link for when you can't find what you're looking for, but that leads to a generic help page that tells you how to do things like turning on your printer.

In Device Manager, I checked the options in the program menus, and nothing useful was present. I even tried right-clicking "Network adapters", hoping that would lead to an option to add one, also to no avail.

So back to the search engine I went, to try to find out where the "Add Hardware Wizard" is. Turns out I was in the right place in Device Manager, but I needed to right-click the computer's name, and choose "Add Legacy Hardware". No doubt that hasn't changed location lately but it's a while since I needed to add one so I'd forgotten. Regardless, I'm left wondering why it couldn't be in the menu as well.

Anyway, for a step by step list, you need to do the following:

1. From Control Panel, select "Device Manager" under the "Devices and Printers" section of the "Hardware and Sound" tab.

2. Right-click the name of the computer at the top of the tree, and choose "Add Legacy Hardware".

3. In the "Welcome to the Add Hardware Wizard" window, click Next.

4. In the "The wizard can help you install other hardware" window, choose "Install the hardware that I manually select from a list" option and click Next.

5. In the "The wizard did not find any new hardware on your computer" window, click Next.

6. In the "From the list below, select the type of hardware you are installing" window, select "Network Adapters" from the list, and click Next.

7. In the "Select Network Adapter" window, from the Manufacturer list, choose Microsoft, then in the Network Adapter window, choose "Microsoft KM-TEST Loopback Adapter", then click Next.

8. In the "The wizard is ready to install your hardware" window, click Next.

9. In the "Completing the Add Hardware Wizard" window, click Finish.

Then you need to continue to set the IP address, etc.

10. Back in Control Panel, select the "Network and Internet" tab, click "View Network Status and Tasks".

11. In the "View your basic network information and set up connections" window, click "Change adapter settings".

12. Right-click the new adapter that has been added (find it in the list by checking the device name of "Microsoft KM-TEST Loopback Adapter"), and click Properties.

 

 

 

I'm really pleased to see the number of courses that we're offering is increasing.

By popular demand, we've extended our Query Performance Tuning and Advanced T-SQL class to Sydney. Previously, we've only been running this course in Melbourne but demand in Sydney for it has continued to grow. For those that might like a Sydney-based offering, you'll find details here now: http://www.sqldownunder.com/Training/Courses/2 (Also, as usual, we have several ways to attend the class. The first two days on query performance tuning, and the last three days on Advanced T-SQL are also available as standalone courses).

In addition, we've had so many requests for a straightforward SQL Server administration class. In many cases, potential attendees are keen to prepare for Microsoft's 70-462 exam. We've now created a suitable course. We have focussed on practical knowledge that is useful to anyone that needs to administer a SQL Server system. Many administrators fit into the "accidental DBA" category. If you are in that category or if you are an existing developer or DBA that wants to refresh your skills, we'd love to see you attend. If you are keen to try to get certified, we think it now provides the best coverage of topics for that exam, when compared to any other 5 day course on the market. The first offering will be in Melbourne starting 15th July. Details are here: http://www.sqldownunder.com/Training/Courses/14

A strength of all our current courses is that we understand that not everyone is yet on SQL Server 2012 and that many students work in environments that have more than one version of SQL Server being used. While the courses are based on 2012, they are suitable for those working with SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2. We'll carefully point out the differences during the courses.

Yesterday I was at a site where they decided to change the service account for the SQL Server services on a set of systems. After changing the service accounts, SQL Server restarted just fine on all machines except one.

I had used the SQL Server Configuration Manager to make the changes (important to not just use the Services applet in Administrative Tools) but I got the typical error telling me that the service wouldn't start in a timely fashion. The server was running SQL Server 2008 R2 SP2.

Looking in the system event log produced the following errors:

The SQL Server (MSSQLSERVER) service terminated with service-specific error %%-2146885628.

A fatal error occurred when attempting to access the SSL server credential private key. The error code returned from the cryptographic module is 0x8009030d. The internal error state is 10001.

I spent a while looking for info on the last error and found a site where they discussed that it was generated when the service account could not read the machine keys that were stored in the  C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys folder. The article then discussed how to add read permissions for the keys one by one.

It suddenly dawned on me that it was probably a problem with the permissions on the folder instead. Checking the permissions on that folder made me realize that the local Administrators account should have the ability to read it. The service account was meant to be a member of the local Administrators account but had not been added to that group on this machine.

Adding the service account to the local Administrators group on the machine (note: not the domain administrators account) fixed the issue and the service started again, until I could get the correct account permissions set in the morning when other staff came back. (In the comments I've added a list of what's actually required).

Hope this helps someone else. (And helps me the next time I see this and have forgotten what it was J)

Fellow MVP Alessandro Cardoso sent me information today about a private cloud and virtualisation event that's running later this month.

It's being offered in Brisbane, Sydney, and Melbourne.

If that's of interest, you'll find more details here: http://virtualisationandmanagement.wordpress.com/2013/05/08/private-cloud-virtualisation-strategy-events-in-brisbane-melbourne-sydney-may-and-june-2013/

Hi Folks,

On Thursday night, I got to record a podcast with Reza Rad. I was interested to speak to Reza after reading his SQL Server 2012 Integration Services Cookbook.

In the show, we discuss SQL Server Integration Services (SSIS) extensibility and some aspects of performance tuning for SSIS.

You'll find the show here: http://www.sqldownunder.com/Podcasts

Enjoy!