Click Commerce Extranet development involves configuration and creating scripts, largely JavaScript, on objects. To test these debug statements are added which put messages in the WOMlog. Viewing these usually means opening the latest WOMlog file in the directory C:\Extranet\Logs and skimming to the tail. After a few complaints from a favorite developer I found the really neat utility TAIL4WIN http://tailforwin32.sourceforge.net Start it and open a read share into the Extranet\Log directory and a window will continuously show you the tail - bottom - of the log file. This saves a lot of time. Highly recommended.

DBCC prevents transaction log backups

Every Sunday at 3 AM I run a batch scheduled job in SQL Server 2005 SP3 to verify the integrity of my database:

alter database chbdev1 set single_user with rollback immediate

alter database chbdev1 set recovery simple

dbcc checkdb ('chbdev1', repair_rebuild)

dbcc shrinkdatabase (chbdev1, 10, truncateonly)

dbcc shrinkfile (pe, 10, truncateonly)

dbcc shrinkfile (pe_log, 10, truncateonly)

waitfor delay '00:00:05'

alter database chbdev1 set recovery full

waitfor delay '00:00:05'

alter database chbdev1 set multi_user

One of these steps is causing SQL Server to forget that it had a backup last night, so when transaction log backups begin later during the work day they fail. The easy solution is to do a backup at the end of the script. But, does this DBCC change the database enough to justify not allowing tran log backups without a full? I wonder if one of the Microsoft developers made this a conscious decision.

Another problem with this is that about once every 6 months a database is left in single-user mode. I don't know why, but I don't let this job run if I can not check the database on Sunday morning. It's never run before vacation.

Lots more to learn about this. Let me know if you see a better way to do this.

Fixing a transaction log backup on an upgraded database

I created a new database and restored it from a SQL-Server 2000 system to SQL-Server 2005 with no problem. Then I set up a maintenance plan with two sub-plans: nightly full backups and hourly transaction log backups during the work day.

After setting up the backups I changed the database "Compatibility level" from 80 to 90. The nightly backup ran fine that night, but the transaction log backups failed consistently. Turns out that changing the database compatibility level wipes out the database's knowledge of when it was last backed up, and a transaction log backup will only run after a full backup. So I ran another full backup, and then transaction log backups started to run just fine.

The only useful error message, that pointed me to the real problem, was in the SQL-Server Integration Services Output file. The History log, the SQL-Server logs, and the server Event logs were pretty useless. So, always set up job step output files for every step, even if it seems unnecessary.

Full backup of a shrunk database - learning by monitoring

I grew up programming back in antiquity when all production was done by batch jobs. The concept can be foreign to some current system managers, but it sure beats working nights and weekends.

This job is designed to run in an hour or so every Sunday at 3 AM, well outside our SLA (service level agreement with users). It could be run monthly or as needed, but I find that jobs run less then weekly tend to be forgotten, and errors can easily be missed.

select * from sysfiles

ALTER DATABASE IRBDEV SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE IRBDEV SET RECOVERY SIMPLE

DBCC CHECKDB ('IRBDEV', repair_rebuild)

dbcc shrinkdatabase (IRBDEV , 10, truncateonly)

dbcc shrinkfile (pe, 10, truncateonly)

dbcc shrinkfile (pe_Log, 10, truncateonly)

WAITFOR DELAY '00:00:05' --

ALTER DATABASE IRBDEV SET RECOVERY FULL

WAITFOR DELAY '00:00:05'

ALTER DATABASE IRBDEV SET multi_USER

PRINT N'IRBDEV file space '

select * from sysfiles

This works well in both SQL Server 2000 and 2005, I think.

I did find out that TEMPDB can not be shrunk in a simple batch job, as it can be on SQL Server 2000 - see How to shrink the tempdb database in SQL Server

Life with EMAIL

I might get 30 to 50 emails per day, requiring some simple (or complex) response. But I've found that Alexander Graham Bell's newfangled invention can short-circuit a dozen rounds of email.

We went back and forth on setting up a SQL Server linked server to an old Oracle database to support an old reporting system from our new, under development, work flow process system. Linked servers work, but coding for production operation, including robust error recovery, can be a trick. We were able to select, update, and insert OK, but merging that code with the CLICK system and designing error recovery led to a three week stream of email, all focused on one item rather then the problem.

One phone call led to a meeting of only 30 minutes which completely redesigned the approach, and will save considerable development time. We will use Integration Services and the batch processes that have been working reliably for 2+ years.

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/