SQL Agent in SQL Server Express with Advanced Services Features

I was recently playing with my Express installation on the personal laptop and was trying to get SQL Agent to run on Windows XP and SQL Server 2008 Express with Advanced Services Features SP2. I set up an account added it to the administrators group, added this account to the instance with the sysadmin role. However, the agent would try to start, but then terminate unexpectedly. I then checked and enabled agent xp

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Agent XPs', 1;

GO

RECONFIGURE

GO

As stated in http://technet.microsoft.com/en-us/library/ms178127.aspx

However the agent still would not start. After searching the event view, specifically  the Windows Application log for errors and found the following

SQLServerAgent could not be started (reason: This installation of SQL Server Agent is disabled.  The edition of SQL Server that installed this service does not support SQL Server Agent.).

Seems that if we want to have automated unattended jobs running, we will have to use power shell and Windows Task Scheduler. I am going to do some browsing and see what I can find out then have an update to this post on a workaround for those of  us who use free edition.

The documentation does state that the SQL Agent is not included, I must have missed this link, check it out

http://msdn.microsoft.com/en-us/library/ms365248.aspx

TSQL thing I didn't know SET and SELECT

It always surprises me how many characteristics in a programming language I take for granted. The things like short-circuit evaluation, handling of Null values, IF then Else loops, etc. I was recently reading my complimentary book on Defensive Database Programming with SQL Server by Alex Kuznetsov, and its chalked full of things to watch out for in code.

One of the more obvious behaviors that I came across is the slight but subtle difference between Set and Select with regards to assigning a value to a variable. I was always operating under the assumption that if a select retrieved a null value that it would assign it to the variable and life is good. Wrong answer, I must have missed that in books online.

-- some code to demonstrate the behavior

DECLARE @MyCounter INT

-- Display its initial value

SELECT @MyCounter AS 'Display its initial value'

SET @Mycounter = 1

-- Display SET assigned value

SELECT @MyCounter AS 'Display SET assigned value'

SELECT @MyCounter=1 WHERE 1=2

-- Display SELECT assigned value

SELECT @MyCounter AS 'Display SELECT assigned value'

SET @MyCounter =(SELECT 1 WHERE 1=2)

SELECT @MyCounter AS 'Display assigned value using SET and SELECT'

So, this was an interesting find and while I have not come across this in the code base I maintain I am sure I have been close a few times to getting this undesired behavior. I tried this with a NULL value and it worked correctly. The take away is we all need to have a fair amount of unit testing. I find it is best when someone else does the testing because I always overlook something, same as when you write a paper, you read it several times but usually miss something. I have not tried reading code out loud, not sure it would make as much sense.

SQL Server 2008 Rowversion

The Books Online documentation indicates that timestamp data type syntax is deprecated, and "timestamp is the synonym for the rowversion data type and is subject to the behavior of data type synonyms." However, if we create a table in SQL Server 2008 SP1, using

create table T1
(
id int identity(1,1),
concurrencyID rowversion
)

Then look at the table using:

sp_help T1




This shows the column with a data type of timestamp, not rowversion.
















In fact if we use the Table designer in SSMS, rowversion is not even a choice.








So you cannot choose the rowversion type in the GUI tools in SSMS, but you can in TSQL but SQL server will switch it back to timestamp as demonstrated.

SU to another user in Oracle

Most DBAs have to execute scripts as the schema owner for them to work correctly. While the traditional method of logging in as that user works fine most of the time. There are times when one does not know the password or have access to the secure location containing the password. Being the DBA we have some options, we can change the password, but this could break other programs. We can retrieve the password hash and change the password execute the scripts and return the password back to the hash value.

If we are using Oracle 10.2 or higher we have the option to use the proxy option. we can grant our DBA account privileges to the schema account and not have to know the password.

alter user schemaowner grant connect through dbauser;

Then the DBA can connect to the schema user by

sqlplus> connect dbauser[schemaowner]@mydatabase

Performing a show user at the SQL*Plus prompt will return

show user

schemaowner

This should be the preferred option for DBA access when needing to run scripts for patches and upgrades when access to the password is not available.

In Oracle 11g the password hash is not viewable through the dba_users view, so using the has value trick is soon to be phased out and is not a great practice anyway.

Monitoring Manual vs automated, vs hybrid

What is your preferred method to monitor databases? Should DBAs rely on just Grid control, Tivoli and other automated agents to monitor their databases? What about manual checking everyday to see if you can find a problem? This is a debate that always comes up and some like scripts, and some want to check everyday. What if you have 200-300, or even more databases to check, is this it realistic just rely on one method?

I am curious to know others preferences. I prefer an automated solution such as Oracle's Grid Control. If there is some type of alert occurring out of the normal,then have someone check it out, otherwise focus your efforts on more value added work. I am sure SQL Server has a robust monitoring solution from Quest or Red Gate too, but I am not as familiar with them.

What is the best solution?