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.