Overview of Hyper-V on Windows Server 2008

Last week I was at the Charlotte Alt.Net user group and gave a short presentation on Windows Server 2008 Hyper-V. My presentation was an overview on the topic and gives some information on the features, how to install it, and how to create your first Virtual Machine. Before you use Hyper-V I also show you how to tell if your hardware will support it. Here is a link to the presentation:

SQL Agent Error Log problem

Here is a quick fix to a small problem if you're running SQL Server 2000 and can't seem to get SQL Agent Error Logs to generate output to its log files. Go to SQL Agent and stop the SQL Agent service. Then go to the SQL Agent properties and on the first tab under General, uncheck the "Write OEM File" and restart the SQL Agent service. For some reason, this stops the logging to the file. I have noticed that this is turned on for MSDE 2000 and it works fine, but it doesn't work on SQL Server 2000. See the screenshot below.

SQL Mail Registry Settings

If you're using SQL Mail on SQL Server 2000, at some point you may have run into this issue. You change the mail profile or maybe you remove the mail profile, and either the profile name won't change in SQL or you can't remove it. If there is no profile, then the option is disabled but still shows up. I have ran into an issue where SQL Agent will not start because it cannot find the mail profile associated with it. There is a work around for this...and it is in the registry. Also, there is a bug in SQL Management Studio for SQL 2005 that does not allow you to see the SQL Mail settings on a SQL 2000 system and will erase SQL Mail settings if you save any changes to the configuration through it.

Normally, you want to stay away from the Registry and avoid touching it as much as possible. But sometimes you have to change settings in it. Before we get to the solution, a quick primer on how SQL Mail works. SQL Mail has two separate pieces that work with SQL Server 2000, one is based on the SQL Service Account and the other is based on the SQL Agent Service Account. The service account being used has to be a Domain account with rights to an Exchange Account/Profile. SQL Server's SQL Mail is accessed through the system extended stored procedure, "xp_sendmail". SQL Server Agent's SQL Mail sends alerts and job notification through Operators. You can configure different profiles for SQL Server and SQL Server Agent or use the same profile for both. There is more information on this from Microsoft. SQL Mail is replaced by Database Mail in SQL Server 2005.

Configuring SQL Mail
http://msdn.microsoft.com/en-us/library/ms189620(SQL.90).aspx

SQL Server Agent Mail
http://msdn.microsoft.com/en-us/library/ms188235(SQL.90).aspx


Caution: Edit the registry at your own risk and test any changes before changing your production system.

Now to resolve the issue and find the registry settings that contain the mail profiles for SQL Server and SQL Agent. These steps are for SQL Server 2000, SQL Server 2005 has similar settings but they are under different registry paths and names. Here are the steps:

1) Go to start, run and type regedt32 to open the registry editor.

2) Navigate to the settings for SQL Server under:
"HKEY_LOCAL_MACHINE > SOFTWARE > Microsoft > MSSQLServer > MSSQLServer"

3) Locate the String Value for "MailAccountName" and change the value to the new profile name or remove the value if the profile no longer exists.

4) Navigate to the settings for SQL Server Agent under:
"HKEY_LOCAL_MACHINE > SOFTWARE > Microsoft > MSSQLServer > SQLServerAgent"

5) Locate the String Value for "EmailProfile" and change the value to the new profile name or remove the value if the profile no longer exists.