When moving copies of our production store to our test systems I disable all scheduled jobs using an old Extranet script.  Frequently the jobs are left enabled in Windows Server 2008R2 while being disabled in Extranet.


When opening the Windows Server 2008R2 in Server Manager (Configuration >> Task Scheduler >> Task Scheduler Library) I got an error message “The selected task "{0}" no longer exists.".  After digging into his, the solution is to delete a file which is out of synch with the registry –
C:\Windows\system32\Tasks\Microsoft\Windows\Defrag\ScheduledDefrag  (no extension)

This file is a scheduler item for regular defragmentation of the server.  After it is deleted the Scheduler is pretty reasonable to work with.  The job can be recreated if needed.

It may be a job other than “defrag”, from what I read, but has been defrag on all of my VMs.  The process is to click once on all of the objects under
Configuration >> Task Scheduler >> Task Scheduler Library >> Microsoft >> Windows 
and see which item give the error on clicking it once.

Creating an account to access SQL Server

We've created a set of custom views on our CLICK 5.6 database which are used for reporting.  To access them we  needed simple production account:


Creating production accounts -
- make sure it doesn't exist
- create account
- assign it a user name
- give it limited security rights
- give it the right to connect

IF NOT EXISTS ( SELECT  *
                FROM    master.dbo.syslogins
                WHERE   loginname = N'ZZBSTN\dsq-apps' ) 
    CREATE LOGIN [CHBSTN\dsq-apps] FROM WINDOWS
CREATE USER [dsq-Apps] FOR LOGIN [ZZBSTN\dsq-apps]
GO


GRANT REFERENCES ON  [dbo].[v_Person] TO [dsq-Apps]
GRANT SELECT ON  [dbo].[v_Person] TO [dsq-Apps]
GRANT REFERENCES ON  [dbo].[v_PersonTraining] TO [dsq-Apps]
GRANT SELECT ON  [dbo].[v_PersonTraining] TO [dsq-Apps]
GRANT CONNECT TO [dsq-Apps]
GO

CLICK 5.6 patch matching

It took us five tries to create an application patch on our development CLICK 5.6 system that would apply successfully to our staging server, with a newly restored copy of our production site running.  Then it took two more smaller patches, with a number of iterations on each, to completely move the new features.

Under version 5.5.3 and earlier the "Version Information" screen included the exact name of the patch file, and I could compare the file name to the names of patch files we've accumulated on our development and staging servers.  In 5.6 we must use the "Patch ID" in the PatchHistory.entity.xml in the patch itself, inside the PATCH.ZIP.  I compare this ID to the PatchID in the left column of Version Information on the staging (patch test site).  This number is a 32 character alpha-numeric that seems to be like a guid.  Each patch has a unique one, so we will not confuse patch files again (not that I've ever made a mistake on a patch).

Version Information:

PatchID:
779442BED2A1684DBCCC8680DDDF5AA9  
Command Line:
PatchStore -store CHBTEST -patchrunid 2010.07.28.09.56.14.236 -userName Administrator -noversioncheck -unzipDirectory C:\Program Files\Click Commerce\Extranet\Temp\kiad4mv2.gyy


PatchHistory.entity.xml from the Patch.ZIP



-
-
   
 

I'm running a number of batch jobs using SQL Server Agent as batch job manager.  A few new jobs were failing with the error"

The job failed.  Unable to determine if the owner (domain\user) of job NAME has server access (reason: Could not obtain information about Windows NT group/user  error code 0x54b. [SQLSTATE 42000] (Error 15404)).,00:00:39,0,0,,,,0

The owner of the SSA job and the step "Run as" accounts were different.  Both were admins on the server and had DBO rights to the database.  The job consisted of three steps - an OS step, and two SQL queries.  It failed to run when the two accounts differed; when they were both the same it worked.

Concise guide to SQL Server 2005 memory use over 4 GB on a 32-bit server

All of this information is from SQL Server Books on Line and MSDN; much is copyrighted by Microsoft.  The best single reference is http://msdn.microsoft.com/en-us/library/ms190731(v=SQL.90).aspx  There are many caveats – read and research before implementing.  This was done, and tested, for SQL Server 2005 running on Windows Server 2003.  The Click Commerce “Extranet” was running as well.  Some users do need to work directly on the server using Click programs.


1. Enable the Lock Pages in Memory Option - The Windows policy Lock Pages in Memory option is disabled by default. This privilege must be enabled to configure Address Windowing Extensions (AWE). This policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. On 32-bit operating systems, setting this privilege when not using AWE can significantly impair system performance. Locking pages in memory is not required on 64-bit operating systems. Use the Windows Group Policy tool (gpedit.msc) to enable this policy for the account used by SQL Server 2005 Database Engine. You must be a system administrator to change this policy.
a. On the Start menu, click Run. In the Open box, type gpedit.msc.  The Group Policy dialog box opens.
b. On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.  
c. Expand Security Settings, and then expand Local Policies.  
d. Select the User Rights Assignment folder.  The policies will be displayed in the details pane.  
e. In the pane, double-click Lock pages in memory.
f. In the Local Security Policy Setting dialog box, click Add.
g. In the Select Users or Groups dialog box, add an account
h. with privileges to run sqlservr.exe.


2. In SQL Server right-click a server and select Properties.  Click the Memory node.  Under Server Memory Options select Use AWE to allocate memory.   To use 6 GB of memory set Minimum server memory to 3072 (MB), and Maximum server memory to 5120 (MB).  Alternatively run these commands:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'min server memory', 3072
RECONFIGURE
GO
sp_configure 'max server memory', 5120
RECONFIGURE
GO
Restart SQL Server for these to take effect.


3. Edit the BOOT.INI file, and remove the /3GB switch, if present, and add the /PAE switch.  This requires a reboot to take effect. (Note: the 3GB switch only hurts if memory exceeds 16 GB)


(Comments appreciated)

This week I found a new way to mess up a CLICK site upgrade.  I have a server that has been running the latest release, 5.6.0.5, and needed to update a copy of our production 5.5.3 site to the new version for testing.  Just after I started the restore, using the CLICK program "restore553storeto56", a developer started the CLICK "Entity Manager".  Well, after running for 5 1/2 days at 100% CPU usage the migration process timed out.  The  EntEntityEditor process had used 71 hours of CPU on the dual-core server while doing a total of 8 IO - 4 read, 4 write.  I started the process again from the beginning, with the developers logged out, and the restore and migration took 6 hours.

CLICK has a bunch of timeouts, which will kill certain transactions when exceeded.  When a task has failed or an error was spotted in the WOMlog and the issue was traced to a time-out then I've raised the value, often with the consent of CLICK support.  But without really understanding the trade-off implicit in the timeout I have not set it back after the issue was resolved.  This migration would have failed far sooner, and the issue resolved sooner, if the timeouts had been lower.  But even with "Command" and "Connection" timeouts too high I saw timeout errors.  The best solution is a good description of the trade-off and impacts of the parameter.  This is often tough to put together, though.

Learning: lock out the developers when starting a migration job.

Life is much easier as a developer when we have tool that make fast work of drudgery.  Every quarter I need to produce a reliability report, comparing our actual experience on out production server to our SLA (Service Level Agreement).  This once involved checking every CLICK WOMlog, looking for restarts and other glitches.  Now I use AstroGrep;  2 minutes to search 3 months of logs.

As developers we're often testing scripts on our development sites.  During debugging we write many statements to the WOMlogs, with values at intermediate steps of the process.  Reading these once meant doing a "LOG ROLL" to create a new Click application WOMlog, running the code, opening the log in Notepad, and going to the bottom to see new messages.  Now I create a share on the WOMlog directory, and from my desktop run "Tail for Win32".  It displays the tail (bottom) of the log continuously, so checking out my messages is just a matter of looking at the Tail window.

Source Forge http://sourceforge.net/ has an immense list of shareware utilities, is easily searched, and most work as advertised.  Few are perfect; can’t expect every bell and whistle from someone’s individual effort in their spare time.  But perfection is the enemy of progress, someone said.

AstroGrep is at http://sourceforge.net/projects/astrogrep/ - there are many other GREP utilities, but this one was very simple to use and worked well.  Copy it to a directory, and run it.  Select the folder to search, and entire a file name or wildcard, and let it run.  The list shows files with one or more instances of the search string.  One click and the lower window will list the occurrences; double click and the file opens on Notepad.

Tail is at http://sourceforge.net/projects/tailforwin32/.  Create a windows share to the directory, run Tail.  Create a workspace (window), and use File >> Open to put a file (WOMlog) in the workspace.  When anything is added you will see it.  TAILforWIN is a bit rough around the edges, but does the job I need. 

Both do not require an install; just download the file and run it.

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/