Adventures upgrading SQL Server

Why is SQL Server so hard to manage? It tries to be everything for everyone. And mostly succeeds. But . . .

Trying to upgrade a server from SQL Server 2000 to SQL Server 2005. Al the docs say it should be easy, but it is not. First, shut down the client application and logged all the users off. Then I skipped two steps of questionable importance. I did not stop the services explicitly because I thought the update would, and I did not detach the dozen application databases from the system. I ran the 05 install, no errors, installed SP3 (Microsoft service pack 3 for SQL Server) and the latest BOL (Books on Line), started SQL Server Management Studio, entered a query to show the installed version, and it was still, after 90 minutes of installs, the old 2000. So I looked at the "Options" on a couple of databases and "90" (internal version of SS05) was not there. I had followed all the steps – why didn’t it work? Si I then reinstalled SQL Server 2005, and the services and internal version were the SS05. Ran the SP3 and BOL again. I though I was in good shape. Nope.

I tried to upgrade the databases to 2005 by running the Copy task to the same location, because that is what I understood the BOL to say. But it copied each database to the same name with “_new” appended, which meant the CLICK applications would not see them. A big waste of time I though, so I stopped the process. This left one of the most important large databases (11GB) locked. Even rebooting the server would not let SQL Server open the file. Thank the L*** we’re running all this on the latest version of VMware, and we had taken a “snapshot” before starting. We “reverted” to the original state of the server. In a couple of minutes 2 ½ hours of work were wiped out. Back to the drawing board.

Started the install all over again. Stopped the services, but did not do the detach the dozen databases. Ran the CLICK command “stopall” which stops all the services on the system used by the CLICK applications, including SQL, IIS, others, and their own services. After an hour of installing everything but Analysis Services again the services were still at SS00. No errors on the screen, and none in the event logs. What’s going on? So I did the install again, installing only the database server this time. This took maybe another 20 minutes. When it came up it was a SS05 system. The internal version "90" was available. So I installed SP3 and BOL. It seems to be working now. The applications are running ok. I went into each database manually and changed the version from 80 to 90. More testing after a break.

Next morning - the upgrade looks good. A couple of services needed to be restarted. The batch jobs, including transaction log backups, look ok. After restarting SQLSERVERAGENT they are running hourly.

A month ago I found a bug where installation of the CLICK Extranet 5.5.3 gives bunches of error messages saying it can not find SQL Server when SQL Server 2005 (SS05) is running at the latest version, SP3.  I developed a workaround from their suggestion, and have done a number of installs successfully.

The use of SS05 with CLICK 5.5.3 caused the Windows Installer to run in an infinite loop.  It wastrying to install something unknown over and over.  Microsoft support in India did a great job defining and solving the issue. I posted an issue CLICK's support web site, and they have confirmed it. They even used our solution to help another client.

 

I often wonder if the folks writing technical documentation, particularly the detailed stuff in the "Help" files, ever treid to use it.  Are they rewriting stuff the unlettered programmer told them at the coffee machine or did they really work thru the problem?  I am trying to set up a SQL Server linked server so a .NET program can update Oracle.  You set up the connection to Oracle by editing teh TNSNAMES.ORA file, and test it by opening the database with SQLPlus.  That is easy.  The TNSNAMES has a "description" and a bunch of parameters.  None of the SQL Server writers relate the TNSNAMES to the SQL parameters for the two stored procedures: sp_addlinkedserver and sp_addlinkedsrvlogin.  And understanding the jargon depends on understanding the jargon, so someone new to some small corner of this world is caught in a loop.  Does this make sense:

[ @locallogin = ] 'locallogin'  Is a login on the local server. locallogin is sysname, with a default of NULL. NULL specifies that this entry applies to all local logins that connect to rmtsrvname. If not NULL, locallogin can be a SQL Server login or a Windows login. The Windows login must have been granted access to SQL Server either directly, or through its membership in a Windows group granted access.

Some days I could take a bunch of these snippets, out of context, and do a humerous recitation at a comedy club, like a comedian did with the IRS code.  

Planning the full set of updates to SQL05.  One server, DEV, was rebuilt last Sept so can just be upgraded by installing SQL05 on top of SQL00.  This will keep the developers happier - less down time.  The other two important servers, TEST and TRAIN, really do need full rebuilds;  the structure is a bit of a mess, and multiple releases were installed on top of each other.

Rebuild steps - all are VMware servers, so we can fall back easily: 

1. Copy a backup of the CLICK sites off to an archive - 30 to 60 minutes
2. deactivate the old VM
3. Create the new VM with the same name and IP - Tom B - 2 - 2.5 hours - this can be done aheady of time but not start it until the old one is deactivated.
4. Install SQL Server + SP3 + BOL - 1 hour +
5. Install CLICK - 1 hour - 5.5.3 plus the framework patches 01, 05, and 06
6. Copy the sites back - 30 - 60 minutes
7. Create and load the CLICK sites - 60 minutes (can overlap a bit with 6)
8. test - if fails then Tom B plus Net Ops needs to restore the old VMware site.
   
   
   On TRAIN the training site will be restored from PROD, not the old one (better keep a copy just in case).

Today's challenge is a conversion of SQL Server 2000 DTS packages to SQL Server 2005 SSIS - Integration Services.  I'm moving data from an Oracle Peoplesoft system into a local reporting system.

Oracle does not pass a code page to SQL Server.  I just wasted 2 hours looking for the propoerty to change - it is "AlwaysUseDefaultCodePage" and it is on the OLE-DB receiver - teh connection from the package task to teh Microsoft database table.  I was fooling with the Oracle OLEDB provider, because of the wording of the error message "Cannot retrieve the column code page info from the OLE DB provider."  Gotta get more flexible here.  See: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/bb08af3a-5080-40d0-b00b-e278115cb2ad/