Category Archives: Windows

Use protection to stay available

One of the biggest worries for us grumpy DBAs are the risks which are posed to us due to our reliance on other teams, specialists and technologies in order to maintain a working and efficient SQL Server infrastructure. This was one of the key concepts when I put together my Thinking outside the Box talk for SQLBits 7, and insist that we need to understand, get involved and help communicate best practices to our colleagues and fellow IT professionals.

An often overlooked area of our SQL Server infrastructure is the lack of protection afforded to the machine and service accounts that our instances and Clusters are using, and the dangers posed to these environments because of this is enormous.

Particularly in the case of SQL Clusters, should a well meaning Windows Administrator accidentally reset or remove the Cluster Group network names, you have a potentially very big problem to deal with. On deletion of the account you still have the possibility of recovering this account from tombstone using a third party tool (more on this another time) but if the account is removed, the first you will know of it is when your SQL Failover Cluster fails and refuses to start on any other nodes in your cluster.

New to Windows 2008 is the ability to protect your accounts and machine names from accidental deletion and it is very simple to do. Firstly as the DBA you should list all the objects that you wish to protect from your Cluster names, SQL Failover Cluster names (aka virtual servers), Windows network names (you should do this as well for all of those servers running standalone SQL servers) and all Service Accounts used within your Clusters and SQL instances. In short, for any object you DONT WANT DELETED!

Windows 2003 users can refer to this excellent article “Protect Objects in Windows Server 2003 Active Directory from Accidental Deletion” instead.

Once you have compiled your list all you need to do is get your friendly Windows Admin to load up the Active Directory Users and Computers snap in and for each object look at it’s properties.

On the Object tab, ensure that the “Protect object from accidental deletion” checkbox is ticked.

A little bit of forward planning in this area could save you and your fellow professionals a world of pain. Be sure that you think ahead, use protection and maintain your availability.

CHK your DSK, ironing out those abnormalities

Ironically not more than a week or so after I emphasised the importance for DBAs to check file system integrity in my recent post Cluster aint misbehavin’, another incident raises it’s ugly head. The post highlighted that file system corruption can cause unusual behaviour with your SQL instance, so when the other day the following errors in the event log were passed to me, I knew exactly what to look at.

In this first error Event ID 823: “The operating system returned error 21 (error not found) to SQL Server during a read at offset 0x000000000a0000 in file ‘R:\Microsoft SQL Server\MSSQL.1\DATA\tempdb.mdf'” it is fairly obvious that this error relates in some way to the file system. The clue is in the words “operating system returned”.

The next error Event ID 9645: “An error occurred in the service broker manager, Error: 823, State: 2” is less obviously related to the first, however there does indeed appear to be a pattern in the Application error log where by one error follows the other. The other thing that you will note is that the error code listed in the description matches the Event ID of the previous error.

Anyway, irrespective of whether we believe there is a connection, let us troubleshoot the first problem. So the absolute first thing you need to ensure is that the file system has no errors. Let us run a read only chkdsk against it…

Unsurprisingly we find that there are issues. Since this disk is actually a Clustered disk resource we need to ensure that we OFFLINE any resource that is dependant on it. When we run the chkdsk /f we will have to close all open file handles. Should any dependant resource be left ONLINE then we will find our Clustered Group failing over and we want to avoid that happening. We can leave the SQL Network Name and SQL IP Address both ONLINE but don’t forget not to touch your disks. If you OFFLINE them you will not be able to fix them!

So once we have taken everything OFFLINE we can then fix the file system.

It is usually prudent to run another read only chkdsk post fix to ensure that this time it comes back clear, but once you are happy then all your OFFLINE resources can be turned back on.

Another final point to note is that when you get these file system corruption issues, they usually happen for a reason. They are generally suggestive that you may have a failing drive on you RAID array, and certainly in this particular instance this was the root cause of the problems. Our hardware management software gave us a delayed signal of impending drive failure a few days later, so this was definitely an early warning signal, and is perhaps is time to check you have replacement drives available to swap out. Most importantly at this stage you should ENSURE your Full and Transaction Log backups are succeeding in the event something goes horribly wrong and if you have a DR solution then start planning for failover.

Generate your cluster logs

Windows and SQL Clustering can be a tricky business, but fortunately most of the administration and troubleshooting can be performed through the GUI. Obviously the same argument can apply that the simplicity of any GUI can be it’s downfall in the sense that it opens up something complicated to the inexperienced. This can be a recipe for disaster on Clusters but as mentioned in previous posts is really down to your IT structures to restrict access to them.

Beginner to moderate level Clustering administrators may not be aware of this, but there is much you can (and sometimes must) perform from the command line. In day to day running activities there is usually no need to do so but knowing that various possibilities are available to you from the command line can be an absolute life saver. I also want to add that there have been numerous posts recently from various professional’s blogs about managing Clusters from Powershell, but this is not what I am discussing. The commands I refer to are the native executables that are installed through the setup of Clustering.

I recently came across a great post about one such command, thanks to being pointed in the direction of Joe Sack from a tweet by Gavin Payne (blog | twitter). I immediately noticed the post from Joe (seeing the word Clustering always catches my attention) and decided to take a look and encourage you to check it out. He effectively discusses generating the Cluster log files for Windows 2008 Clusters so I decided to try it out myself quickly. Trying is the only sure fire way to remembering in my humble opinion!

Running the command will generate all the logs for each Clustered node in your specified location. Should any node be offline then the command will simply generate an RPC error for that node.

So the first thing you need to do is run the command to generate those logs.

Once all those lovely logs have been generated (it appears to be one per node) you can review them for your enjoyment.

As you can see when a problem hits, just knowing how to do this could aid you in your quest to solve the issue much faster than otherwise. Hopefully in future posts I can have a look into what other things you might want to use the native command line for when configuring your Clusters.

What a real Server should be

I really like this. I had heard about this but hadn’t seen any evidence…and still must look into it, but I really like it.

Anyone who remembers the days of Novell NetWare being King of the LANs may agree that you don’t need a fancy GUI to have a server. Something which is still relevant to UX’s today and thankfully Microsoft are starting to take notice. One thing I really like about Microsoft is that they do listen and are not too arrogant to think that only their opinion is the only one that matters.

im going old-school

I shan’t go into why I believe a GUI is not something for a server right now, but all I can say is that I shall be looking into the restrictions and possibilities of the Server Core Installation in due course. I am confident that this could be the beginning of the end for GUI based servers for Windows, but then it couldn’t really be called Windows any more could it? How about … “Microsoft Server – Solid, fast and dependable!”

My Big Fat Greek Desktop

Disclaimer

Although this post may look at first to be about Linux and whilst many of you visiting this post will be either Windows or SQL centric you might think this is not for you. Actually you are exactly the right people to be reading it because I want you to understand that you have options out there open to you. Your desktop can be a blank piece of paper and you can draw what you want. You are not constrained by software boundaries if you do not want to be. My desktop is all about flexibility, speed and usability. I can do anything I want whether it be Linux, Windows, SQL, Oracle whatever on my beloved desktop.

This post also shows how you can use Linux and make it more usable, way beyond what you might expect from this humble open source OS. Go on, live a little!

In the Beginning

I’ve recently been suffering from unexplained machine freeze for several weeks and decided enough was enough. Resolve or upgrade, but no more cold boots first thing in the morning. I’m getting ahead of myself though, let’s go back to the start of the problem…

Over quite a considerable period of time I had come to grow and love my GUI customizations of my Linux host (Mint 7) and it had everything. It looked slick, had little effects which looked cool such as the magic carpet effect when minimizing Gnome windows and it was fast. My Windows XP guest ran beautifully fast within its virtual environment (arguably as fast as native) and since I was addressing the whole 5GB of RAM (1.5GB more than my original 32 bit XP build could) I wasn’t even losing out on memory).

I even migrated to a new Windows guest, a brand spanking new Windows 7 build and I was quite proud of my flexibility on my lovely desktop. I mean what could go wrong, if I didn’t like it I could just use my old guest. Hell I could even run them together until I was satisfied the day had come to decommission XP. Things continued for a while in a state of Nirvana. Linux host, Windows 7 guest and a whole multitude of lovely things in the background. At this point I shall not discuss how I’d “merged” my Linux home and desktop with my Windows documents and desktop, but just be assured they got along just dandy. Two OS’s working as one.

Over time something happened and I didn’t know what. I would arrive in the morning to find the keyboard lights all on but unresponsive to touch (or hammer) and a blackened monitor. Initially I would just cold boot the machine in frustration and lose everything that was un-saved. Over time I learnt that the Linux host was still active and therefore installed the SSH Daemon so now first thing I could ssh in and save the state of my VirtualBox guest and then put the machine through a soft reboot. This continued for some weeks until the fateful day I told you about earlier.

After a little bit of investigation I noticed some strange errors in the messages log (and perhaps in a few of the other system logs) that indicated there was a problem somewhere in the region of the GUI. Evidence suggested that this was with the graphics card or graphics driver but I really couldn’t be sure. Even the Linux guru I consulted believed that this might be the case, but we were both unsure.

Finally one day I decided to install Mint 9 (2 iterations up). Upon completion I put back in place various bits and pieces, such as Documents from my old home and some dot configuration files. Upon arrival the very next day, to my frustration a similar problem as before had occurred. Although this time the problem was a slightly more of a stable result. I simply had a message stating that my machine was running in low graphics mode and an option menu where I could choose to close X if I wanted to. I have seen this very same message on another of my machines and it always seemed to occur after around 5 hours of usage. I had guessed that this was because the Graphics card was starting to overheat, but I couldn’t be sure. Anyway I now had two very similar problems on two different machines, one of which had been upgraded – which as I have said had an issue before. I was convinced that this issue could not (or would be unlikely to be) a hardware or driver related issue but perhaps something to do with configuration.

get out of the way baby ‘cos there’s no turnin’ back

So I asked myself -on the presumption that my clean build resulted in a good working machine, what was changed to make it unstable. The answer was that I had replaced my .gconf folder in my new home with my previous one. It seemed a little too obvious to me but I thought I would assume this was actually the problem.

http://projects.gnome.org/gconf/

The .gconf folder contains various Gnome (Windows environment) based customizations and settings and is located in each Linux user’s home folder. Now one thing I was absolutely positive about was that from time to time I had added the extra GUI tweak here or there to make my interface even prettier. That’s one thing I love about Linux, it’s customizability. With respect to the GUI, it is also its greatest downfall.

Anyway to cut a very long story short, the next morning my machine had not frozen – Yay! The day after that, it still hadn’t failed. Better still my machine and all guests absolutely fly. I’ve been very careful about what I’ve altered, but with a few bits of tinkering here and there my desktop is back to looking damn sexy again.

So why are you telling me this?

Well the first thing I want to say is that when you make customizations to any environment, you should make sure you remember that you have made them and always remember to have a “Last Known Good” backup available. Easier said than done I know, but it could have saved me a little pain had I remembered what I had changed all those weeks back and could easily revert what I had done.

The second point I want to make is related to the GUI itself on Linux. I am pretty sure that the settings I had changed which caused me the issue occurred after playing with the compiz manager setting lots of fancy effects. Be very careful of the compiz changes you make and always remember what you did.

The final thing I wish to discuss is how you can make customizations to your Gnome GUI in the first place. There are lots of changes to the settings you can make which will help your GUI do lots of the things you love about MS Windows, and lots more extra goodies. All these settings though are not in the same place, and I am hoping at very least this article will serve as a good reminder to myself.

The first GUI changes you should make are accessed on the Gnome panel, simply right-click the vertical line (the separator) near your Menu button and show desktop and select the Preferences menu item.

This will give you access to the Window List Preferences dialog, and I like to set the option to Always group windows.

windows style grouping

The next change you may want to make are the Menu Preferences. To access this, right-click the Menu button and select Preferences. The changes I like to make here are the icon sizes for the applications. Another change you can make is for the actual Panel itself. To access the Panel Properties, simply right-click empty space of the panel in question and select Properties. to get the dialog. I like to change the height of the panel (making it smaller) and making it slightly transparent. I also like to set the lower panel to autohide which ironically I don’t like to do in Windows.

Some of other GUI settings that I use are accessed via the Menus. I use an Ubuntu Custom Menu (added as an item to a new top panel) to access these. First select System/ Preferences/ Desktop to access the Desktop Settings menu. Here you will find all the Window-like stuff, such as Desktop icons to add (which I do) and a few other customizations to set.

add your desktop icons

Moving swiftly on to the desktop appearance, you will probably at some stage want to change the theme you are running on Gnome. To access this, simply right-click a blank part of your desktop and select Change Desktop Background. This will take you to the Appearance Preferences menu where you can alter lots of the lovely things you can see below. Generally I choose the theme you can see selected in the screen shot below and also change all of the Fonts to a smaller size, because I think the defaults are far too large.

change your theme

Last for this post, but certainly not least you have the Compiz Settings Manager. This is a bit of a beast and you can do some weird and wonderful things in here such as the lovely magic carpet (and lots of other effects) that I have mentioned previously. You can also significantly slow down you machine or introduce an error to your config just as I had done, so please be careful and remember what you changed and take backups or copies of you system settings in your home directory. Access this again through System/ Preferences menu

be careful with your compiz

…and finally

So now our work is done, my desktop is complete (for today at least) and its looking just like I want it. So I think it is only fair I present it to you.

welcome to my world

In the screen shot, to the left you will notice my lovely Windows 7 desktop. This is the machine that pays my bills because it’s got lots of Microsoft goodies such as SQL 2008 R2 client tools, Visual Studio 2010 and Office 2010 to name but a few. With the simple click onto this machine and a press of my right CTRL key whilst also pressing the F key, this guest is fully maximized to fill the entire screen as if it was installed natively. The same sort of thing we are used to in VMWare Workstation or even Remote Desktop connection (if you can ever remember that key combination). Unlike VMWare Workstation, there is absolutely no sluggishness at all.

Next I would like to draw your attention to the machine underneath it. You would be wrong to think that this is another guest, it is infact a remote connection to another Linux machine over the WAN using an SSH tunnel using some lovely software that does VNC like (but so much better) desktop. Again I can maximize this entire machine so it looks and feels like I am there. This time I have to use the left CTRL and ALT keys whilst also pressing down (yes you guessed it) the F key. It’s really nice.

Moving swiftly on, and looking across to the top right of the desktop you will see a machine called Neon. This is my little pet play thing which I have several different versions of SQL installed going right up to R2 which I use to help me take screen shots and also to trouble shoot tweets and test out any scenarios of my own that I may have. This machine is a guest, so I can pause it, save it’s state in seconds and then close it if I don’t want it around or don’t need it. This usually happens when my pet play thing is misbehaving itself or needs a rest.

To the bottom right of the screen is a remote desktop connection (using the linux rdesktop command) to a random live work server. I tend to have a very nasty habit of having a lot of these, but I am currently in treatment and hope to keep them at a minimum sometime soon. Within my Windows 7 guest I might also have a couple of remote desktops going on, depending upon where the focus of my attention has been.

People I would like to welcome you to my environment. I hope you like it.

SSMS query pane bug in Windows 7 its bugging me!

Are you familiar with this menu? Are you sure? Do you use it a lot? Yep me too.
Most of you will already know how to get this menu within SSMS, and one such way (the way I always use) is to right click the white space within a Query window. One of the reasons you might wish to do this could be because you are copying text. Well in Windows 7 this does not seem to work any more. I initially noticed the problem when using my spanking new SQL 2008 R2 client installation. After several installations on a couple of platforms it appears that the issue is not specific to SQL 2008 R2 but is actually related to running on Windows 7. The following all display this behaviour :-

SQL 2005
SQL 2008 with or without service packs
SQL 2008 R2
(SQL 2000 not tested)

To demonstrate what I mean, lets launch a query window within SSMS and type some text and execute the query. OK so now lets say we want to copy a selection of text. After selecting, and right clicking the mouse….nothing.

Strangely next I’m going to do something slightly different. With the selected text I’m going to right click and hold the mouse over it and drag across the screen.

Upon release this mouse we still get a context menu coming up which allows up to move or copy here. Hmm, right.

Let’s now move into the Messages (Results pane will do also) pane of the query window and right click.

Great it works!

My final tests included using the keyboard short-cuts to copy and paste texts within the upper query window and there is not a problem there either.

In conclusion the only issue being affected appears to be purely the static right click within the upper query pane window. It is not a show stopper since this menu can be accessed through the main menu bar, but it is darned annoying and makes me wonder what other little quirks I might uncover within SQL applications running on Windows 7.

Windows 7 install sit back and watch

Well I’m not really going to go into any detail right now about my experiences so far with Windows 7, but I just wanted to show just what the installer looks like. I’ve heard all sorts of things about it being quicker and less painful in Windows 7 and I guess if I am being honest that might be largely true.

One thing for sure is that the interface does look nice and polished. However first booting up from your media you are presented with this unappealing boot sequence. Shame they didn’t give a nice custard yellow colour to the bar, it would have looked very much like the NT 4 loader.

And then it happens, wow we go from a dull, boring and we’ve seen it all before screen to a lovely colourful little animation. Very nice, very pointless, but graphically appealing.

Well things continue along the same vein from screen to screen, yes indeed the installation screens do look nicely done.

Well that is until this strange one. Bizarrely we have a old style window with list boxes inside to select language and region settings. Note to UI designer, a panel might have stood out less and kept a little more in keeping with the feel of things… still what do I know?

Things really continue as they mean to go on, and I suppose I don’t really recall having to spend much time answering prompts. I guess one of the most obvious things that seems to be automatically configured/ improved is the Networking configuration. I’ll be honest, I don’t recall answering any prompts for this. This is how it should be I think because you can always change this later if the installation defaults are not suitable for your install.

Possibly my biggest scratch of the head moments came when I saw the “Install Now” button?!

Yeah of course I want to and then the “Installing Windows…” window. Get this, first it goes and “Copying windows files” and strangely then needs to “Expanding Windows files”. Hello! Anyone thought about that.

Any modern installation I would expect to copy and expand via memory, and if this is what it is doing then scrap one of the steps because it is just plain daft.

I particularly liked the screen to set the time-zone. This is very aesthetically pleasing and doesn’t have any of those slight irritations such as pointless action buttons on the windows (such as close or minimize)

Well the installation has gone without hitch, and is overall an improvement over previous versions. Time will tell as to my feelings towards the OS.