Saturday 28 July 2012

Reduce SQL Server 2012 install time on Server 2008R2 by pre-installing .Net4 / patches

If you are running SQL Server without clustering or other failover technologies you will want your SQL Server upgrade to run as quickly as possible so the users can get back the applications as quickly as possible. I always close all client connections, run an SQL back up of both client and system databases, stop SQL Services and set to manual, 'manually' copy the SQL data directories/log etc to a safe place before starting the upgrade. For me the downtime starts as soon as all the users have to come off so I can carry out a clean SQL backup.
Upgrading SQL2008R2 SP1 on Windows 2008R2 SP1 to SQL2012 a large amount of my downtime was SQL Server installing .Net4 and some subsequent .Net4 fixes before the ‘real’ SQL2012 upgrade started.
On subsequent installations I was able to reduce my downtime significantly by pre-installing .Net4 and the appropriate patches ahead of time.
Look in the redist\dotnetframeworks directory of your SQL Server installation media, it should look like the image below.
image
Run the dotNetFx40_Full_x86_x64 file first (or SC for server core install).
Once complete run NDP40-KB2468871-v2, and then NDP40-KB2544514, then switch to the appropriate x64 or x86 parent directory and run the file in there, eg NDP40-KB2533523. Now run SQL Server install and it should be much quicker. If you have many servers in this position it would be better to script it.
On Windows Server 2012 .Net4 should already be ‘pre-installed’ and this should not be necessary.
Happy upgrading!
Bizarre occurrence!
During one early upgrade I had copied the SQL installation media to the local server and then run the SQL installer from that server itself and SQL setup crashed installing .Net4. On investigation of the (many!) set up logs, the actual dotNetFx40 file was not copied correctly from the master installation media and was corrupted during the copy. Fortunately the crash had no serious consequences and I installed .Net 4 manually as per above after copying new files over, but there was some concern seeing the SQL install crash as a test install on the same server in the lab had installed perfectly! This highlighted the speed advantage of preinstalling .Net 4. When you get a crash in the middle of an install it can be a major problem as various components are at different levels, and fixing the mess can be very time consuming.

Tuesday 10 July 2012

SQL Server 2012 Standard Lock pages in memory behaviour change from SQL 2008

If you have ever seen this message in the SQL log you know you are in big trouble…
A significant part of sql server process memory has been paged out. This may result in a performance degradation.
This is well documented and can be overcome in more recent builds of SQL Server Standard using trace flag 845.
http://support.microsoft.com/kb/970070
I (and many others) had complained about the lack of this feature
http://www.sqlservercentral.com/articles/SQL+Server+2008/64421/
and MS listened. If you set the service account to lock pages in memory and set the trace flag you will be up and running. Make sure you configure Max Memory appropriately! However, having an ‘extra’ switch was something I had become used to and looking on my laptop using SQL Server 2012 RTM which does not have traceflag 845 but does have locked pages in memory configured I saw this message.image

The behaviour in SQL 2012 had changed and always locks pages in memory once the account is configured.
I looked around and indeed this is now documented in a KB article, although I don’t remember reading the behaviour change in any what’s new articles.
http://support.microsoft.com/kb/2659143/en-us?sd=rss&spid=16139

Here is an extract from the KB article with highlighting of the change:
Starting with SQL Server 2012, the memory manager simplifies the usage of "locked pages" across supported editions and processor architectures. In SQL Server 2012, you can enable "locked pages" by granting the "lock pages in memory" user right for the startup account for the instance of SQL Server in Windows. This is applicable for Standard, Business Intelligence, and Enterprise editions of SQL Server 2012 running on both 32-bit and 64-bit processor architectures.

I think this is a step forward, but I also think there should be a configuration option on the memory page to select/deselect the option, and SQL Server should carry out a check that the service account has the correct privileges and maximum memory could be checked for a ‘sensible’ value (a whole story in itself) so the memory configuration screen would be modified and would look something like this…
image
This would remove any ambiguity and make it easier for new users to use this feature.You could argue misuse of this feature could do more damage but I think users should be made more aware of this feature and know how and when to set it.