The Challenge of Version Diversity

Even when an environment is limited to one vendor product line (e.g. Microsoft SQL Server, in my case), you can still be challenged by the differences among versions, service packs, and even hotfix levels. Today, I had an interesting case that prompted me to add this blog to my suite of blogs.

We have an old Great Plains install - from the days before it became Microsoft Dynamics. It is running on top of SQL Server 2000 RTM (version 8.0.194)! With no support agreement and very little IT staff still around that even remember the system, we have to keep it chugging along while PeopleSoft ramps up. So I notice that the database backups are failing even though the files seem to be generated. Looking deeper, I notice that it is the verification that is failing and only on 1 of the databases.

That database backup succeeds, but I don't see the backup file in the specified directory. Turns out that the failure message:

BackupDiskFile::OpenMedia: Backup device 'X:\xxxxx\xxxxx_xxxxx.bak' failed to open. Operating system error = 32(The process cannot access the file because it is being used by another process.)

is misleading. I assumed a permissions issue, full drive, or some other OS problem. Google searches did little to dissuade my initial assumptions, so I continued troubleshooting at the directory/permissions level for about 1 hour. I created new directories, verified permissions with the directories that were succeeding, pointed the backups to new directories.

Finally, I simply opened Query Analyzer and tried to run the backup command manually. The process failed with a new message with keywords that I could Google:

[Shared Memory]...ConnectionRead...Connection Broken

That lead me to The MSsForum and a link to this Microsoft Knowledge Base article.

Because of the lapsed support agreement and lack of product expertise, I couldn't risk implementing a hotfix or service pack at this time. So, I checked the Workaround section. It stated:

To work around the problem, use the TCP/IP Net-Library instead of the Named Pipes Net-Library to connect to the SQL Server database, and then back up or restore the database.

But, their suggested method of prefixing the server name with tcp: in the connection properties wasn't going to work for a SQL Agent job. So I went to the Client Network Utility on the SQL Server itself and setup an Alias (under the Alias tab) with the same name as the SQL Server instance using TCP/IP as the Network Library. Since this particular SQL Server was a default instance using the standard port, no other changes were necessary. I'll assume that this alias was used the SQL Agent, because the backup job is now successful!

I'll check on the job over the next couple of days and update this blog when I can confirm or bust my workaround theory.