Lock Pages In Memory Incompatible with Virtualization

In a previous posting I provided links to references for Enabling the Lock Pages in Memory Option on 64 bit Windows Servers.  I recently learned this is not compatible with Servers running in a virtualized environment.  After enabling this on two servers that use physical hardware, without problems, our server admin agreed that we should probably implement it on all of our 64 bit SQL Servers following the Microsoft recommended practice, so we made the change at the end of the week last week.

Everything was ok, until Sunday just before noon, when I received a series of alerts on my Treo that connection tests to one of our primary SQL Servers were failing.  Though not recommended or supported, we run SQL Server on VMWare 3.5 for Disaster Recovery, and we have never had trouble with it.  This server happened to be one of the virtual servers, so I logged into work, and found that the server was completely dead, no ping response, no RDP Access, so I jumped onto the VM Client Console and it was blue screened.  Not good, but at least I can restart it and start looking for what might have caused this.  However there are no logs, not dumps, nothing.

Unfortunately I have learned that sometimes in IT the unexplainable happens and then doesn't reoccur.  A phone call to our server admin and some digging by him didn't turn anything up either, so we are stuck with no answer, just wait and see if it happens again.  The performance charts for the server didn't hold any clue to what could have occured on the server.  It was moving along like normal then dead. 

Monday morning the server is online and running just fine.  Somewhere around 10:30am one of the other Server Administrators did a VMotion of the server to move it from one host to another.  This has been a standard practice by the server team for a few years, to do load balancing or maintenance on the Virtual Hosts, and there have never been problems with this before.  Generally the memory cache flushes out, and has to re-grow when they do this, so it is not often that they move a SQL Server from one host to another.  However, this time, the server immediately lost all but 1/4 of its normal memory, and it would not recover from this.  Instead it began paging everything to the Virtual Swap Memory on disk, until it finally crashed completely.  Since I monitor for high memory pressure, I never received an alert of the problem, something I have since corrected, and the server limped along for just over 2 hrs before it died completely.  Not one complaint for poor performance was received during this time span, interestingly enough.

The first thing we did was disable the last change made which was locking pages in memory and restart the server.  Now it is a waiting game to see if the problem repeats before we can get a scheduled outage at night to take the server offline and really look it over.  Later that night we couldn't repeat the problem by moving the server, so I guess problem solved?  To find out for sure we decided to move a developer server with the option set and see what happened.  Since there was a delay between the move and crash previously on a high load server and the developer servers have little load on them, I wrote a process to generate load against the server and enabled it.  It took just over 16 hrs, but the server crashed just like the production server.

If you run SQL Server on VMWare 3.5 and you use VMotion to move servers, do not Enable Locking Pages in Memory.

Now using Windows Live Writer

I have had issues since I started blogging with the online site interface for publishing data.  I even went so far as to write my own C# blogging application, though it only performed about 1/2 of what I wanted it to.  Today I found the Windows Live Writer on my brothers blog,  which looks like it will do what I want.  I'll be trying it out over the next few weeks, to see how much easier it turns out to be.

I definitely dig the Insert code plugins that allow for easy formating of code like:

Label err_Message = new Label();
TextBox InputBox = new TextBox();
private bool ValidateInputs()
{
    try
    {
        Convert.ToInt32(InputBox.Text);
    }
    catch
    {
        err_Message = "Invalid datatype for Input. Must be convertible to an Integer.";
        err_Message.Visible = true;
        return false;
    }
    return true;

}

I recommend that anyone doing blogging take a look at this tool.  I will be using it to update all of my previous posts so they look better.

Installing IIS After ASP.NET

For those of us who decide after the fact that we will need to install Reporting Services, and we didn't install IIS, there is a rough path that sometimes has to be traversed due to the fact that ASP.NET was installed first which means that it will not be registered appropriately with IIS. This is recognized by the SQL Installer, and it will not allow you install Reporting Services until you have resolved it. I found the solution to this in four easy steps:

1. Click Start -> run -> cmd - ENTER
2. At the command prompt, type the following, and then press ENTER:"%windir%\Microsoft.NET\Framework\version\aspnet_regiis.exe" -i
3. Register the Aspnet_isapi.dll by clicking start -> run
4. In the Open text box, type "regsvr32 %windir%\Microsoft.NET\Framework\version\aspnet_isapi.dll" and then press ENTER

In both of the above paths, version is the directory that corresponds to the version of .NET that you installed.

The original Source of this information is Mike Viehweg's site.

Scripting Attach/Detach of all Databases

Recently I had to upgrade a larger database server with 167 databases on it from SQL 2000 Enterprise Edition to SQL 2005 Standard. I had to maintain a fast method of attaching the databases to 2005 or 2000 in the event of an issue, rapidly, so I needed generate a script to attach/detach every database on the server.

SET NOCOUNT ON
CREATE TABLE#Temp
(AttachScript varchar(8000))

DECLARE@sql varchar(4000)
DECLARE@dbname varchar(128)

SELECT@dbname =min(name)
FROMmaster..sysdatabases

WHILE@dbname IS NOT NULL
BEGIN
    SET@sql =' use ['+@dbname +']
    declare @res varchar(8000)
    set nocount on
    set @res = ''exec sp_attach_db N'''''+@dbname+''''' ''
    select @res = @res + '','''''' + rtrim(cast(filename as varchar(100))) + ''''''''
    from ['+@dbname+']..sysfiles
    order by fileid asc
    select @res
    '
   INSERT INTO#Temp
    EXEC(@sql)

   SELECT@dbname =min(name)
   FROMmaster..sysdatabases
    WHERE Name>@dbname
END
SELECT* FROM #Temp
DROP TABLE #Temp

Cumulative Update 7 Released for SQL Server 2005

Microsoft released CU7 for SQL 2005. Like most of the CU's for SQL this should be applied with caution, and only if it resolves a specific problem listed in the fix list. You will have to request this from Microsoft as with the other Updates in the past.

http://support.microsoft.com/kb/949095/en-us

There will be a SP3 for SQL 2005.

Jens Suessmeyer posted on the MSDN Forums today the link to her blog with information about SQL 2005 SP3. There will in fact be a SP3 coming after the release of 2008 to the public later this year. This is good news for the SQL community where there have often been questions about which Cumulative Update or hotfix should be applied, and what the support level of those would be.

http://blogs.technet.com/dataplatforminsider/archive/2008/04/15/sql-server-2005-sp3-coming-soon.aspx

Solutions for Common T-SQL Problems

I have been working recently in coordination with SQL MVP Arnie Rowland, and the other Moderators/Answerers on the MSDN SQL Server Forums to build a online Wiki that contains Solutions for Common T-SQL Problems we see on the forums. There is a limited topic base published currently but it is growing rapidly. So far this has been a great success, and a wonderful resource when answering similar questions on the forums.

Locking Pages In Memory on 64 bit SQL Servers

One of the benefits of being on 64bit SQL Server is that it allows you to utilize memory beyond the 4GB limit without additional changes like enabling AWE, the /PAE extensions, and the /3GB switching like were done in 32 bit SQL 2000. However, a lot of questions on the forums recently pulled some information to light about a recommended best practice, though not required, configuration of Enabling Lock Pages in Memory for x64 Servers.

Some important Links on this topic:

PSS SQL Server Engineers Blog: Do I have to assign the Lock Pages in ...
Ask the Performance Team : Lock Pages in Memory...do you really need it?
How to: Enable the Lock Pages in Memory Option (Windows)
How to reduce paging of buffer pool memory in the 64-bit version ...

If you haven't done this on your 64 bit servers already, you should review the above and change your configuration.

Temporary Break from Blogging

Since I am working nights on building the SQLCLR.net site, and adding articles to it, I will be taking a momentary break from regular blogging. Of course, if there is some non-CLR related topic that I have to write about, it will be posted here, but my current focus is getting the site up and running. Feel free to visit the site to find new information as I publish it there, or to contribute to the efforts.

SQLCLR.net Site Online

I finally got the issues with http://www.sqlclr.net resolved with the hosting company, and the site is online and ready to accept submissions from registered users. I am spending alot of time learning the DotNetNuke platform, and configuring the site for access, but I have been able to post a few articles, and examples on the site:

Enabling CLR in SQL Server

Generic Regular Expression Function

I am also seeking content from other users on the internet that I find, by email. Hopefully I can bolster up support for this site and get it off the ground.

SQLCLR.net Site Creation

I find that as time progresses, more and more people are starting to use CLR in SQL Server, and that is good, but the online resources for CLR in SQL are sparse and sometimes impossible to find without hours of searching depending on the problem at hand. I have registered SQLCLR.net and created a site, but am currently having some issues with my hosting and ASP.NET that are delaying the launch. Interestingly enough, someone has registered SQLCLR.com already, but there is just a link to their blog site. Hopefully this isn't a duplicated effort.