The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog



No Ads ever, except search!
Monday, May 27, 2024 Login

Blog posts for the month of July,2008.
SQL Server Skill Set for Disaster Recovery7/11/2008 8:49:26 AM

Recently I was tasked with creating a list skills needed for Disaster Recovery.  Everything !  You don't necessarily need a SQL Server guru, but you need the highest level of competency there is about the product.  Competency of the product, does not necessarily mean an expert, but it means they understand all peices of the product, how to install them, how to configure them....and then how to recover them.

Basically most of the DR Scenarios we run into our bare metal restores.  We start with a clean OS, which closely matches the production environment, but it may not be an exact match.  From there the process of installing SQL and recovering bak files (or other files from tape) begins.

The skill set I submitted was:

  • Building up an installation from new media
    • Restoring System Databases from bak files
    • Restoring User databases
    • Understand the SID, Security Identifiers and how to synchronize logins
    • Resetting linked servers, sql agent jobs and DTS / SSIS Packages
    • Named Instances of SQL
  • How to set a static port for a named instance
  • Understand how to reset / rebuild Full Text Indexes
  • Understand SAN Technology to include
    • managing and creating dynamic volumes
    • managing mount points under windows
  • Working knowledge of Virtualization
  • Extended Stored Procedures
    • Compression utilities like SQL LiteSeed
    • SQL Server 2005 .Net based stored procedures
    • Issues with the master database and extended stored procedures on restore
  • Communication Skills
    • Ability to cleary write and communicate technical issues clearly
    • Ability to prioritize appropriately
      • Restoring and getting a sql job that runs once a week is not as important as bringing the database on-line and available, etc....
  • Connectivity issues 
    • How to troubleshoot front end connectivity issues
    • Firewalls and Ports
    • Default Instances and Named Instances
    • SQL Browser issues
  • Operating System and NetBackup / Recover Skills
Polyserve SQL Installer / Upgrader7/30/2008 8:53:53 AM

When working with the Polyserve SQL Installer or Multi-Node Upgrade Wizard for SQL Server, we noticed an issue where even after applying the SQL Hotfix, the SQL Version was showing as not upgraded on some machines, this was concerning as the utility did allow us to re-virtualize with these instances in different versions, this is a big concern.  You do not want a SQL Instance on the cluster to be different versions, Different instances can be different versions, but a single instance should be the same across each machine, see picture:

Surprisingly when you went to the individual machines and checked, the SQL version was the same (so on the picture above, physically checking DEVPLYSQL01, SQLTest1 instance was at version, 9.2.3228).  So why was the utility showing 9.2.3152, even after we applied the hotfix ?  No error was reported, but something wasn't right.

Not sure if the instance was not put in maintenance mode properly, or if something else occurred, but using RegMon (registry montior) while the multi-node installer utility ran, i was able to determine that the Polyserve utility used a registry entry to populate this screen.  We were checking the physical binary sqlservr.exe and @@Version (select @@version).  The registry entry is located at:  HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.n\Setup , the key is PatchLevel.  Note that you may have to determine what Instance maps to what MSSQL.n, this can be done by checking another key, HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names, There you will see the mapping of an instance name to it's mssql.n location, see below:

Obviously something went wrong, even though no error was reported back through the service pack / hotfix installer.  The solution is to ensure to put the Instance in maintenance mode and then being the "non-trusting" type, I manually applied the hotfix / service pack to those nodes that have the issue.  Interestingly the hotfix installer indicated the instances had been upgraded, so obviously the hotfix installer doesn't check the same registry entry as the Polyserve installer.  I manually checked the instance in question and ran it again.   It reported success.  I again opened the registry and checked the patch level entry, this time it reported the correct version number.  I opened the Polyserve utility, multi-node installer and it also reported all the version numbers correctly and homogenously (is that a word ?).  We then took the instances out of maintenance mode and all was good.

We never experienced any errors or issues, so the moral of the story is, "to run your upgrade / service pack / hotfix and re-verify version numbers across each node, regardless of the messagebox reporting success", also "re-open the multi-node installer (it caches information so completely leave the utility), and verify that it "agrees" that the version numbers are the same".  Do this before going out of maintenance mode !


DestinationConnectionExcel and OfflineMode7/31/2008 8:27:01 AM

SSIS, Excel and 64 Bit SQL Server, wonderful together!

The connection manager "DestinationConnectionExcel" will not acquire a connection because the package OfflineMode property is TRUE. When the OfflineMode is TRUE, connections cannot be acquired.

We've seen similiar errors to this before and I blogged something about, blah blah blah, with a solution like re-installing client tools, before trying that, try this, it's an easier fix and may work.  This was found by Tom Reeves, an excellent SQL Server DBA !

Register 32bit dtexec

I ran into this issue yesterday and once in the past and I finally figured out how to fix it quickly. The issue occurs on a 64bit server trying to use the 32bit dtexec command. Typically the package will have an excel connection in it or some another type of object that doesn’t have a 64 bit driver. When you install the 64bit integration services it registers the 64 bit version of DTEXEC, which in turn unregisters the 32 bit version. To fix this you have to register the 32 bit version again. You can do this by running the below code from the server with the issue.

%windir%\syswow64\regsvr32 "%ProgramFiles(x86)%\Microsoft SQL Server\90\dts\binn\dts.dll"

Buick Century Replace Freeze / Expansion Plugs7/16/2008 3:33:41 PM

Newest car in the fleet, a 1989 Buick Century.  1 week on the road, and water is everywhere, surprisingly know steam, so I'm pretty sure it's not a hose.  Thinking the worst, a cracked block or something.  After working hard on the problem with my neighbor Bill, read as enjoying a budweiser on a swing....he related a story about a plymouth he owned where the freeze plug was always leaking, right over the starter.  This got me to thinking and sure enough, you can see the anit-freeze, leaking right out of a freeze plug, some will call these expansion plugs.

Removal and installation is difficult, as there are components in the way and it requires striking them with a mallet to get them in.  The local garages all wanted from 150 to 200 for this repair.  For that amount, i'll try it myself first and push it to them if I can't do it.

First glance at this one made me think I'd have to remove the starter and the check oil tube that guides the dipstick into the oil pan.  Fortunately I found a product, a neoprene rubber expansion plug that doesn't need to be "hammered in".  For $2.46, it's worth a shot.

I was able to use a long screw driver and hammer, with the car up on ramps and tap the old plug loose, be advised, even if you think there is no anti-freeze in the car, your block holds quite a bit, and I was nicely bathed in anti-freeze and muck.  Definetly this was some nasty crap floating around in that engine.  Guess that's why you supposed to "flush and fill" your car occasionally, this thing looks like it's never been changed.  Loosening it was easy, but it took a bit of work with vice grips to pull it out.

Installing the neoprene rubber plug was a bit of work, because the oil dipstick tube, is right over this freeze plug, but a little bit of work and it's in.  Very nice because no hammerring, now all i you have to do is get a wrench on the damn thing and tighten her down.

So far it is holding, I'm not sure I'd trust it for many many years, but I'm only trying to get 1 year out of this car.

Pictures below.


Size and Move TempDB Log file 7/10/2008 3:14:07 PM

Today I was preparing TempDB for some best practices and had to move and size the log file.

I was not able to size the log file of tempDB until I had moved it....and of course I mean physically move it first, which requires stopping and starting the instance.

I was in the process of creating 16 tempdb data files of an even size on a specific mount point and moving the tempdb log file to another mount point.  The current location only had 5gb free, the new location for tempdb's log file would be 33gb in size, i tried to move and size it at the same time and the command failed.  Then I moved the file.  Stopped and started the instance.  Than sized the log file.  PIA.

USE [master]


alter database tempdb

modify file (name='templog', filename= 'D:\EAM\EAMQATempLog\TempDB.LDF')

alter database tempdb

modify file (name='templog', size=10240MB, filename= 'D:\EAM\EAMQATempLog\TempDB.LDF')


MODIFY FILE ( NAME = N'tempdev', SIZE = 2048MB,

filename = 'D:\EAM\EAMQATempDB\tempdb.mdf' )


add FILE ( NAME = N'tempdev2', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb2.ndf' ) to filegroup [PRIMARY]


add FILE ( NAME = N'tempdev3', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb3.ndf' ) to filegroup [PRIMARY]


add FILE ( NAME = N'tempdev4', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb4.ndf' ) to filegroup [PRIMARY]


add FILE ( NAME = N'tempdev5', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb5.ndf' ) to filegroup [PRIMARY]


add FILE ( NAME = N'tempdev6', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb6.ndf' ) to filegroup [PRIMARY]


add FILE ( NAME = N'tempdev7', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb7.ndf' ) to filegroup [PRIMARY]


add FILE ( NAME = N'tempdev8', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb8.ndf' ) to filegroup [PRIMARY]


add FILE ( NAME = N'tempdev9', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb9.ndf' ) to filegroup [PRIMARY]


add FILE ( NAME = N'tempdev10', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb10.ndf' ) to filegroup [PRIMARY]


add FILE ( NAME = N'tempdev11', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb11.ndf' ) to filegroup [PRIMARY]


add FILE ( NAME = N'tempdev12', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb12.ndf' ) to filegroup [PRIMARY]


add FILE ( NAME = N'tempdev13', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb13.ndf' ) to filegroup [PRIMARY]


add FILE ( NAME = N'tempdev14', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb14.ndf' ) to filegroup [PRIMARY]


add FILE ( NAME = N'tempdev15', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb15.ndf' ) to filegroup [PRIMARY]


add FILE ( NAME = N'tempdev16', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb16.ndf' ) to filegroup [PRIMARY]


alter database tempdb

modify file (name='templog', filename= 'D:\EAM\EAMQATempLog\TempDB.LDF')

alter database tempdb

modify file (name='templog', size=10240MB, filename= 'D:\EAM\EAMQATempLog\TempDB.LDF')

Bravo base to Ghost rider tango7/2/2008 10:57:16 AM

I recently mailed some homebrew to someone in California.

His reply upon receipt, "Bravo base to Ghost rider tango. The package is here!"

What a country !

"Relax, and have a homebrew."


I/O Error reading and writing to membership partitions7/2/2008 10:47:50 AM

Our Polyserve cluster took a deep dive and crashed, all nodes.  Root cause is still under research, but basically we zoned some new storage to the cluster and after a reboot of the nodes the Polyserve software was unable to read or write to the membership partitions.  Of course the error didn't state that, as that would have made troubleshooting the problem easier, instead we received this error:

Event Type: Error
Event Source: sanpulse
Event Category: SAN Storage
Event ID: 17005
Date:  7/2/2008
Time:  9:35:40 AM
User:  N/A
Computer: BCPLYSQL03
This matrix is unable to take control of SAN because the servers are unable to perform fencing operations, possibly due to a networking or fencing hardware failure or misconfiguration. As a result, some or all filesystem operations may be paused throughout the matrix. In addition, filesystem mounts and unmounts and disk imports and deports cannot be performed.

We have zoned storage to Polyserve many many times, and never had a stability issue, we've had isolated issues with LUNS not showing up, mini / storport issues, emulex issues, but nothing that caused the cluster to become unstable.

So we eventually de-zoned the new storage, rebooted the entire cluster and everything worked fine.  We're not sure if we zoned the storage incorrectly (we have a new SAN Administrator, so maybe it wasn't done correctly), though I don't suspect this.  Our SAN Administrator while new has succesfully zoned storage to our clusters in the past with no issues, and understands how / what Polyserve is. 

More so, I suspect some internal issue to windows / emulex / Powerpath or something that upon the zoning of the new storage, caused the LUN Id's change to map incorrectly.


Rear Wheel Implosion6/5/2008 10:29:45 AM

Metal on Metal + Heat = Serious Problem.

After 40 years on the road I guess something has to give, and today it was the right rear wheel of my 1969 sunroof, somehow the axle nut backed off a bit allowing the drum to become mis-shapen.  I don't really know how this happens, but I can tell you that when you are cruising at 65 mph and hear a grenade going off!

Fortunately I was able to take 'er off the road and limp her to Vee Village in downtown kansas city.

The looseness is the axle nut caused the drum to become misshapen causing the issue.

Part not available locally, of course, $92.00 + labor = Ouch !

The actual word from Vee Village was "RR drum is loos and wallowed out."

Vee Village fixed 'her up good, but there attention to detail is lacking, I drover her out of the shop and with-in two blocks I could here a slight grinding / grating sound, not like before, but definetly NOT right.  Return to vee village.

They take her back in and say that all is fine, hah !

I drive it with them, "oh yeah, Bob, that doesn't sound right", no shit man !

Finally they end up having to work the drum and backing plate on lathe to get them to work properly, quietly and without a lot of heat build up.



Blog Home