Adding a Node to a SQL Server 2005 Failover Cluster

Setting up multi-node clustered SQL instances from scratch is common practice for companies that want to implement high availability, but adding a new node to an existing clustered SQL instance that has been running for a while may have a few sticking points, especially for a SQL 2005 instance. In this series, I will walk through how to add new node for existing clustered SQL Server instance for SQL 2005 environments, and applying a post-configuration service pack.

Step by step procedure for adding a node

Below, I detail step by step instructions for how to add a node and then apply a service pack or hot fix afterward.
Step 1: Log onto the primary node for the existing SQL 2005 Clustered instance. The primary node is the clustered node where your SQL 2005 clustered instance is currently running.

Step 2: It's always advisable to run the setup procedure by a service account with administrative privilege on all the clustered nodes. To launch Add or Remove Programs in Control Panel with a service account, follow these steps:

1.       Navigate down to C:\WINDOWS\system32
2.       Look for appwiz.cpl

3.       Right click on appwiz.cpl, select click RunAs, then specify service account "User Name" and "Password" and click OK

Step 3: Once you have seen the Add or Remove Program window below, it means you have successfully launch it with the designated domain account.

Step 4:  If your OS is 64 bit, look for "Microsoft SQL Server 2004 (64-bit)," and then click Change. The wizard for "Microsoft SQL Server 2005 Maintenance" will start as below (I have masked the instance name for Reporting Services and Database Engine for confidentiality of the company) In your environment, you should expect to see the instance name for the masked portion. You can also see common components that were already installed on the server.

Step 5: If you have multiple SQL 2005 instances then you will need to perform one instance at a time. Here I only have one SQL 2005 clustered instance, so I selected the instance by clicking the radio button and then clicking Next.

Step 6: In the Feature Maintenance page, select a component to change; Database Engine (Clustered) is selected by default. Click Next, then the SQL Server 2005 System Configuration Checker starts.

After few seconds, the Welcome page for "Microsoft SQL Server installation Wizard" will pop up, as below.

Step 7: Simply click Next to proceed

Step 8: Installation wizard will perform system check and make sure it can proceed without Error or critical warning.  Review all the warning and error message provided by System Configuration Checker. Address any threatening warning and error if needed, then re-launch from Step 2.
Click Next once system configuration check is completed.

Step 9:  in the Change or Remove Instance page, select Maintain the Virtual Server.

Step  10: On the Cluster Node Configuration page. Here is where you will see the ActiveNode, PassiveNode and the new node you'd like to add. Since the installation wizard needs to be launched from the active node for the clustered instance, you will see the machine you logged onto appears on "Required node." For the existing passive node for the clustered instance, the server name will appear on "Selected nodes," and the new node you are working on to add into the clustered instance will appear on "Available nodes."
Simply click Add to move the available node to selected nodes. You can now see both Passive node and the new node being listed under "Selected nodes," like the below screenshot.

Click Next to proceed.

Step  11: Remote Account Information page shows up. Here you just need to provide the password for the domain account you used to launch the Setup wizard from step 2. Make sure the login you used to launch the wizard has administrative permission on all the three clustered nodes for the instance you are operating. Then click Next to proceed.

Step 12: Here you will be asked to provide the password for the existing service account used to run SQL Server service, SQL Agent service and Browser service.  Once you fill in the correct password information, click Next.

Step 13: On the Error and User Report Settings page, depending on your company's policy, select either or not to send error reports and usage data to Microsoft, then click Next.

Step 14: Ready to Update page shows up, click Install.

Step 15: Configuring Microsoft SQL Server page will show up, do not click anything, let it run through progress bar.

Step 16: Then a windows explorer navigation window will pop up; here you have to browse through your directory to help the installation wizard to locate SqlRun_SQL.msi. You can find this msi under SQL 2005 installation binary folder ...\disk1\Setup, depending on where you place the SQL 2005 install binary. You can also do a search on SqlRun_SQL.msi under the install binary to find its exact location.

Step 17: Once you successfully locate the SqlRun_SQL.msi, click OK to continue.

Step 18: Don't click on anything, Let the setup wizard do its duty.

Step 19: After a few minutes, you will receive the above warning message that indicates that existing nodes for the SQL 2005 clustered instances are already patched. So you will also need to patch the new node to match rest of the clustered nodes. Click OK to acknowledge.


Step 20: You will see this "Setup Progress" page once Setup Wizard is about to install components on the new node. Do not click anything and wait for its setup progress page.

Step 21: Finally, Setup Wizard has come down to the business of installing all necessary components on the new node. Please wait as it indicates on the page.

*If you are adding two new nodes instead of one new node, the setup wizard will provide a drop down so you can scroll the node and see the install progress on each node, like the below diagram.

The installation procedure to add two new clustered nodes and to add one new clustered node are exactly the same, except that you can scroll the drop-down list of server names to review the installation progress. Below, I will continue to use the screenshot I took for adding one new node to an existing clustered SQL 2005 instance.

Step 22: Keep your fingers crossed and hopefully you can see the Green Check Mark that indicates successful setup of the respective components; below three screenshots display successful progress of the installation.

Step 22: Once you see that all components have the status "setup finished," you can relax and click Next because Setup wizard has successfully install the selected SQL components on the new node.

Step 23: You can review the setup log by clicking the summary log, or clicking Finish to complete the procedure of adding a new node.

Step 24: It's a good practice to recycle your affected servers. Make sure the SQL 2005 clustered instance is back online and healthy after adding the new node. Do not failover the SQL instance to the newly added node just yet.

Author: Claire Hsu

Creating Secure SQL Server Service Accounts

Problem

We are looking to install SQL Server and we know it will require a service account. In this tip we cover what should be done to configure the SQL Server service accounts securely.

Solution

Service Accounts for a Server Installation

If you're on a domain, it's generally recommended that you use a domain level account. This should be a regular domain user account and definitely not a member of the Domain Admins group. The reason for the domain user account recommendation and not a local account is that it allows Active Directory to be the single source for your security system. It makes things easier to manage and audit. If you don't have a domain, using a local account is fine, just don't make it a member of the Administrators group (if you go the domain user account route, you should also avoid this group membership).
If you're installing multiple components, the recommendation is to use separate accounts if you can. If you're just installing the database server, you have two services to be concerned with: the main SQL Server service and the SQL Server Agent service. Even in this case it's recommended to use separate accounts. While this is a best practice, it is not unusual to see a single account per server for all of the SQL Server services. However, the issue here is those additional services could potentially have permissions in other SQL Server components you might not want because they aren't needed. For instance, the SSIS service doesn't need to be able to control the database engine. However, if you use the same account, there's a potential for that to happen.

As far as assigning specific permissions, don't. Let the installer set the permissions correctly. It will attempt to set the minimum permissions necessary. If at some later point you need to change the service account, use the SQL Server Configuration Manager because it will handle the permissions as well as a few other details that are not done by changing the service account using any other interface.

One definite no-no is to re-use a service account across servers unless you're dealing with a situation like a scaled-out SQL Server Reporting Services installation. For the database engine, SQL Agent, and SSIS, you should ensure that no service account is used on multiple servers. If you have the need for a service account to access resources on a second server, you can always assign the permissions explicitly.

Considering SQL Server 2008 and Windows Server 2008 and above

If you have the combination of SQL Server 2008 or above and Windows Server 2008 or above, then SQL Server will be installed using an operating system feature called Service Isolation. In terms of user accounts, all user accounts have a unique identifier we call a SID (Security Identifier). With Windows Server 2008, services also have SIDs. This was implement for the situations were you have multiple services running under the same account (for instance, Network Service or System) but these services shouldn't have access to resources belonging to other services that happen to share the same service account. As a result, each service now has a SID.

SQL Server 2008 and above can use this to ensure that the service has permission to database resources, not the account. For instance, in the figure below, note that NT Service\MSSQL$SQL2008R2 (a SQL Server 2008R2 named instance called SQL2008R2) and its SQL Agent service, NT

Service\SQLAgent$SQL2008R2, are listed as logins. This is because of service isolation. In this case, the only way you get sysadmin level access is by coming through those services. Even if the service account was to be compromised, unless it was one of those services connecting, the account would have no privileges within SQL Server.

Handling Developer Workstations

When considering developer workstations, there is no change in the recommended best practices, but their probably ought to be. If your organization has a lot of developers, then keeping up with services accounts for ever developer installation can be a management nightmare. In most cases developer workstations shouldn't have processes that need to access anywhere outside of the workstation SQL Server is installed on. In this case it makes more sense to create local user accounts for the various SQL Server services. The same rules apply: separate accounts for different services and starting each user account as a user level account and allowing SQL Server to update the permissions accordingly.

One thing you don't want is for the developers to use their own accounts. This can cause a problem at password reset time. A classic scenario is the developer gets the message to change the password when he or she logs on to the domain and follows suit. However, the SQL Server service would still be configured to run with the old password. As a result, next time SQL Server is started, you'll have an audit failure. This is true for all accounts with that same user account but now the problem is every single one of those services will attempt to log on to the domain as if nothing has happened. The problem then is each of those services will generate audit failure events and could potentially cause the user account to be locked out, depending on the lockout policy. Obviously this is a situation to be avoided.

Also, if you still have developers on XP, it's time to migrate. The Service Isolation spoken of earlier is first available in Windows Vista. So a combination of Windows Vista or higher and SQL Server 2008 or higher will ensure you get Service Isolation and minimize your exposure.

In Summary

• For servers use domain accounts
• For developer workstations use local user accounts
• Never configure the accounts to be anything more than regular user accounts. Let SQL Server make the permission changes.
• Use separate accounts for each service.
• Never re-use an account on multiple servers, with the exception of scale out scenarios like is possible with SQL Server Reporting Services.
• Try to use Windows 2008 or higher for the operating system

SQL #

SQL# is your QUICKEST and EASIEST way to extending the power of T-SQL in Microsoft SQL Server 2005 and newer!

You can be more productive in just three easy steps:
1) Download the SQL# script
2) Run the SQL# script
3) Use SQL# functions and procedures

The following are the free functions available with SQL# now:

Current FREE functions:

• String: Contains, Count, Cut, EndsWith, Equals, IndexOf, InitCap, IsNumeric, Join, LastIndexOf, Newline, NthIndexOf, PadLeft, PadRight, Replace, Split, SplitIntoFields, StartsWith, Trim, WordWrap
  
  
• RegEx: CaptureGroup, Escape, Index, IsMatch, Matches, Match, MatchLength, MatchSimple, Replace, Split, Unescape
  
  
• Math: CompoundAmortizationSchedule, Constant (30 physics constants), Convert (22 measurement conversions), Cosh, CubeRoot, Factorial, IsPrime, RandomRange, Sinh, Tanh, Truncate
  
  
• Date: Age, BusinessDays, DaysInMonth, DaysLeftInYear, Extract, FirstDayOfMonth, FormatTimeSpan, FromUNIXTime, FullDateString, FullTimeString, GetDateTimeFromIntVals, GetIntDate, GetIntTime, IsBusinessDay, IsLeapYear, LastDayOfMonth, NewDateTime, NthOccurrenceOfWeekday, ToUNIXTime, Truncate
  
  
• InterNet: AddressToNumber, HTMLDecode, HTMLEncode, IsValidIPAddress, NumberToAddress, URIDecode, URIEncode, URIEncodeData, URIGetInfo, URIGetLeftPart
  
  
• Miscellaneous: CRC32, Deflate, GenerateDateTimeRange, GenerateDateTimes, GenerateFloatRange, GenerateFloats, GenerateIntRange, GenerateInts, GetDirectoryName, GetFileName, GetRootDirectory, GUnzip, GZip, Hash, HashBinary, Inflate, IsValidCC, IsValidCheckRoutingNumber, IsValidConvert, IsValidPostalCode, IsValidSSN, ToWords (i.e. translate a number into word representation: 150 = One Hundred and Fifty)
  
  
• Database: BulkCopy, BulkExport, ForEach (combined ForEachDB and ForEachTable), HTMLExport, XOR
  
  
• Convert: BinaryToHexString, DateTimeToMSIntDate, FromBase64, HexStringToBinary, HtmlToXml, MSIntDateToDateTime, ROT13, ToBase64, UUDecode, UUEncode
  
  
• LookUps: GetCountryInfo (i.e. ISO info), GetStateInfo (i.e. ISO info)
  
  
• Internal: Version, Help, Setup, Uninstall, GrantPermissions, IsUpdateAvailable, SetSecurity, WebSite
  
  
• Operating System: EventLogRead, EventLogWrite, GenerateTone, MachineName, Uptime
  
  
• Twitter: BlockUser, CreateFavorite, DestroyDirectMessage, DestroyFavorite, DestroyStatus, FollowUser, GetBlocks, GetFavorites, GetFollowers, GetFriends, GetFriendsTimeline, GetHomeTimeline, GetMentions, GetMessages, GetPublicTimeline, GetRetweetedBy, GetRetweetedByMe, GetRetweetedToMe, GetRetweets, GetRetweetsOfMe, GetSentMessages, GetStatus, GetUser, GetUserTimeline, Retweet, SendDirectMessage, UnBlockUser, UnFollowUser, Update, xAuth
  
  
• User-Defined Aggregates: GeometricAvg, Join, Median, Random, RootMeanSqr
  
  
• User-Defined Types: FloatArray, HashTable, NVarcharArray

Download  the PDF manual

SEQUENCE in SQL Server 2011

SQL Server 2011 (or Denali) has now arrived CTP. In this article we will look at a core new feature of SQL Server 2011 which is SEQUENCE. If you are familiar with Oracle, you will already know all about this feature since it has been standard on Oracle more than 10 years I believe.

What is Sequence in SQL Server ?

In simple terms, it is a new database object and a substitute for the Identity of columns.
Using the identity attribute for a column, you can easily generate auto-incrementing numbers (which as often used as a primary key). With Sequence, it will be a different object which you can attach to a table column while inserting. Unlike identity, the next number for the column value will be retrieved from memory rather than from the disk – this makes Sequence significantly faster than Identity. We will see this in coming examples.

Creating a Sequence in SQL Server

To use Sequence first SQL Server Management Studio (SSMS) and expand the Object explorer, under programmability you will see the sequence node.



If you right click the sequence and select new, you will be taken to the below screen which has all the attributes for the sequence.



Since Sequence is a database object, it needs to be assigned to a schema. It has a data type which can be int, bigint, tinyint, smallint,numeric or decimal. The start value and increment as similar as to the values you will be familiar with using Identity.
The Minimum and maximum are boundaries for the sequence. When  the cycle option is set you have the ability to re-use sequence numbers.
Similarly, Sequences can be created using T-SQL as follows.

IF EXISTS (SELECT * FROM sys.sequences WHERE name = N’EmployeeSeq’)
                DROP SEQUENCE EmployeeSeq;
GO
 CREATE SEQUENCE EmployeeSeq AS tinyint
                START WITH 0
                INCREMENT BY 5;
GO

Now let us see how we can integrate this with an Insert statement.
First we will create a table to incorporate the sequence we created.

CREATE TABLE Employee
(ID tinyint,  Name varchar(150) )

Then we will insert:

INSERT INTO Employee
(ID,Name)
VALUES
(NEXT VALUE FOR EmployeeSeq, ‘Dinesh’)
INSERT INTO Employee
(ID,Name)
VALUES
(NEXT VALUE FOR EmployeeSeq, ‘Asanka’)

Note that you are now using the EmployeeSeq sequence object for the insert.

Restarting a Sequence

Can we restart Sequence in SQL Server 2011? Of course you can. In identity you previously used Reseed for this.

ALTER SEQUENCE dbo.EmployeeSeq
RESTART WITH 2;

Above statement will allow you to restart the sequence from 2.

Sequence Transactions

Another issue is, what will happen to the next number of a sequence when the transaction is rolled backed.
Let us check this. First we will see what is the current value of  the sequence.

SELECT current_value FROM sys.sequences
WHERE Name = ‘EmployeeSeq’

Since, we have restarted the sequence this will return a value of 2 and now let us execute this in transaction and later we will rollback the transaction.

BEGIN TRAN
INSERT INTO Employee
(ID,Name)
VALUES
(NEXT VALUE FOR EmployeeSeq, ‘Asanka’)
ROLLBACK TRAN

Again, we will check the next value for the sequence:

SELECT current_value FROM sys.sequences
WHERE Name = ‘EmployeeSeq’

Above query will return 7 which means the rollback statement does not rollback the next value for the sequence. This behaviour is the same as Identity.

CYCLE

 If you have set the cycle option on, your Sequence object will re-use numbers. Let us see this in an example. By stating CYCLE your sequence cycle option will be set to true.

CREATE SEQUENCE [dbo].[EmployeeSeq]
 AS [tinyint]
 START WITH 2
 INCREMENT BY 5
 CYCLE

 To see this option to work let us execute following statement.

SELECT
NEXT VALUE FOR EmployeeSeq
GO 100

Following image is a part of results you would get and you can see that after 255 it has restarted to 0 again.

OVER

Let us create a new sequence.

CREATE SEQUENCE SeqOrder AS tinyint
                START WITH 1
                INCREMENT BY 1
                MINVALUE 1
                NO MAXVALUE
                CYCLE;
GO

Now, let us run following query:

SELECT ID,Name,
NEXT VALUE FOR SeqOrder OVER (ORDER BY Name DESC) As [Order]
FROM Employee;

The results should be as shown below:



This means you can use Sequence as a running number in a query.
 

Performance of Sequence vs Identity

For me this is the best aspect of using Sequence. Performance wise it has advantage over the identity.
Let’s measure this.
I will create three tables; timing to measure the time, idt to insert data with identity and seq for insert data with sequence.

CREATE TABLE timing(
Instance varchar(50),
occ_time datetime default getdate())
CREATE table idt
(ID int identity(1,1), Des Varchar(100) )
CREATE table seq
(ID int  , Des Varchar(100) )

Then I will create two procs, insert_idt to insert data with identity and insert_seq to insert data with sequence.
Creating insert_idt procedure:

CREATE PROC insert_idt
AS
BEGIN
INSERT INTO idt
(Des)
VALUES (‘insert idt’)
INSERT INTO timing
(Instance)
Values (‘Insert idt’)
END

Creating procedure insert_seq:

CREATE PROC insert_seq
AS
BEGIN
INSERT INTO seq
(ID,Des)
VALUES (NEXT VALUE FOR SEQ5,’insert seq’)
INSERT INTO timing
(Instance)
Values (‘Insert seq’)
END

 Then I executed each proc 10000 times:

exec insert_idt
 GO 10000
 exec insert_seq
 GO 10000

Then we measure the timing for each batch:

SELECT CAST(MAX(occ_time) – MIN(occ_time) AS TIME) FROM timing
 WHERE Instance =’Insert idt’
 SELECT CAST(MAX(occ_time) – MIN(occ_time) AS TIME) FROM timing
 WHERE Instance =’Insert seq’ 

In this test, executing of the first batch of procs (using Identity) took16.557 seconds and second (using Sequence) took 14.33 seconds, thus demonstrating the performance advantage of Sequence. This advantage will be much greater you are testing this in a table where there is a large number of records.

Limitations of Sequence

·         You are allowed to use NEXT VALUE FOR in a UNION ALL, but not in a UNION, EXCEPT, INTERSECT, or with DISTINCT. In which case you will get the following error message.
Msg 11721, Level 15, State 1, Line 3
NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION (except UNION ALL), EXCEPT or INTERSECT operator.

·         In addition, I attempted to create a view with sequence and failed with following error message.
Msg 11719, Level 15, State 1, Procedure vw_1, Line 4
NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views, user-defined functions, user-defined aggregates, sub-queries, common table expressions, or derived tables.
·         An error will be thrown when the NEXT VALUE FOR function is used in a TOP, OVER, OUTPUT, ON, WHERE, GROUP BY, HAVING, ORDER BY, COMPUTE, or COMPUTE BY clause.
·         Finally an error is thrown if the NEXT VALUE FOR function is used in the WHEN MATCHED clause, the WHEN NOT MATCHED clause, or the WHEN NOT MATCHED BY SOURCE clause of a merge statement.

Final words

Note that these samples are from CTP1 and there may be some syntax and feature changes in the final release but this core feature will exist.

Author: Dinesh Asanka

Adding Expires Header to static images in IIS 7

One of the things YSlow checks for is the expires HTTP header for static content such as images, script files and style sheets. Some sites always use custom HTTP handlers for serving scripts and stylesheets, only the static images have been a problem.

The problem

The problem is that with images on hosted environments on IIS 6, it’s impossible to control the serving of them without redirecting them through an HTTP handler. That’s not a good idea for several reasons:

• It adds unnecessary overhead by going through the ASP.NET ISAPI
• You need to add custom code to handle the requests
• You need to change the URL from .gif to .gif.axd or similar

Here is what YSlow finds on Mads website that needs the expires header set to a far future date:



As you can see, it is all my static images that lacks the expires header.

The solution

If you run IIS 6 there is no good way of adding an expires header to images unless you have control over the IIS. If your site is hosted then you probably have no control at all. If you are using IIS 7 however, you can very easily add the header in your web.config’s system.webServer section like so:
<staticContent>
 <clientCache httpExpires="Sun, 29 Mar 2020 00:00:00 GMT" cacheControlMode="UseExpires" />
</staticContent>

What happens is that all static content will now have an expires HTTP header set to the year 2020. Static content means anything that isn’t served through the ASP.NET engine such as images, script files and styles sheets. This is one of the very easy tricks that will increase the performance of your site as well as your YSlow score.

Reference: .NET Slave

Make your ASP.NET Page Methods Secure

One of the most persistent misconceptions about ASP.NET’s page methods is the notion that they have some intrinsic protection against requests that don’t originate from the page where they’re defined. Since a page method’s code resides within a page’s code-behind file, it’s intuitive to assume that those methods benefit from some form of inherent security.

Unfortunately, that is not the case

Exploiting your page’s insecurities

In case it’s hard to believe these code-behind methods truly are so easily accessible, let’s take a look at a quick example. Let’s say you have a page method that returns sensitive business information, like so:

[WebMethod]
public static int SecretFormula() {
  return 42;
}

Assuming that method is defined in a Default.aspx file’s code-behind, located in a folder named TopSecret, here’s a bit of jQuery you could use to request the secret formula from any page on the site:

$.ajax({
  url: '/TopSecret/Default.aspx/SecretFormula',
  type: 'POST',
  contentType: 'application/json',
  data: '{}',
  success: function(result) {
    alert(result.d);
  }
});

In fact, you can even make that request from a plain HTML file. In this example, I’m making the request right from a simple HTML file named index.htm:



Not only is the request not originating from the ASPX file that contains the page method, that request wasn’t originating from an ASPX file at all!

Is this really a problem?

You might be asking yourself if this is really a problem to begin with. After all, you aren’t very likely to write client-side code that requests sensitive data unless you actually need that data, regardless of whether you write that code on an ASPX page, HTML page, or anywhere else.
The trouble is, a villain could land on any of your site’s pages, open up Firebug, and start probing for weaknesses like this one. In this case, the obscurity of the exact location of the page method might lead you to a false sense of security, but if an attacker has any knowledge of your system’s architecture then the door is wide open.
Even more troubling, as long as an attacker uses POST requests with an application/json Content-Type, they could use a tool as simple as Fiddler to interrogate your site’s services remotely. A few lines of server-side code, and your competition could set up a site driven by the page method APIs on your site that you thought were private!

A fixable problem

Page methods aren’t indefensible against the shenanigans of external interlopers though. You can secure them with ASP.NET’s built-in authorization mechanism, just as you would any other ASPX page (or ASMX service). In fact, if you secure an ASPX page with ASP.NET authorization, page methods defined in its code-behind are automatically equally secure.

To deny unauthenticated access to methods in our TopSecret folder, adding an authorization entry to the web.config in that folder is all that’s necessary:

<?xml version="1.0"?>
<configuration>
<system.web>
<authorization>
<deny users="?" />

</authorization>
</system.web>
</configuration>

Now, an unauthenticated user trying to access the SecretFormula method from index.htm (or anywhere else, without authenticating first) is denied access:




Of course, there are a variety of ways to control access to these methods. You might test to see which role a user’s in and respond accordingly, or you might only need to verify some token stored in the Session.
Regardless of the particular mechanism, the key is to remember that none of this is automatic and that you must be mindful of regulating access to your page methods.

Reference: Encosia.

"The resource cannot be found" error in ASP.NET MVC 3

This week I downloaded the latest version of NerdDinner source code from codeplex for ASP.MVC 3 architecture. Opening the solution and executing, I got  "The resource cannot be found" exception. I was clueless as I just downloaded the latest source from the SVN and executed. Googling on it I learned that we can run into a problem when our requests are automatically redirected to a (possibly non-existent) login page.

To fix this, include the following lines in your web.config file:

For MVC 3 beta and MVC 3 RC1:
<appsettings> <add key="autoFormsAuthentication" value="false">
</add></appsettings>

For MVC 3 RC2:
<appsettings> <add
key="enableSimpleMembership" value="false"> </add></appsettings>

Detecting Mobile Device and Redirecting

Project Description

This project, called "Foundation", is just one of 51Degrees.mobi components for mobile web development. Its provided as a .NET open source class library that detects mobile devices and browsers, enhancing the information available to .NET programmers. Accurate screen sizes, input methods, plus manufacturer and model information is all available. Mobile handsets can optionally be redirected to content designed for mobile devices. Smart phones, tablets and feature phones are all supported.

---

Mobile Optimized Web Sites

Foundation detects the presence of a mobile device, and enables the web request to be directed to web pages designed for mobile. 51Degrees.mobi other products help ASP.NET developers rapidly create fast mobile web pages supporting tablet devices, high end handsets, and basic feature phones. Learn More & Free Trial.

---

How does it work?

Http requests are intercepted by an additional HttpModule before the page handler starts to process the page. The first task of the module is to detect the device making the request and enhance the default properties provided by Microsoft. For example; the Request.Browser.ScreenPixelsWidth property will return the precise value for the mobile device. Finally the module determines if the request should be sent to a mobile home page and performs the redirection. The web.config file can be used to control how detection and redirection operate for your specific web site. For example; some sites may wish to redirect only the very first request to a mobile home page enabling the user to navigate to the traditional home page.
To find out more try downloading our detection example web site or reading the operational summary.

---

Guides and Documentation User Guide Operational Summary Installation Instructions FAQs Latest Release Note Tutorial for Enhance (3:43 min video) WURFL mapping table Example Projects Add 51degrees.mobi Foundation to an Existing Web Site Mobile Device Detection and Redirection for ASP/MVC Get in Touch 51degrees Forum Contact Us Find out about 51Degrees.mobi

Find out more: http://51degrees.codeplex.com/

Finding out the culprit behind “# of Exceps Thrown / Sec” using ProcDump and WinDbg

I’ll show you how to find out the culprit (exceptions) who is causing a high number of .Net “# of Exceps Thrown / Sec”

We’ll do this with WinDbg and ProcDump tools.

Let us start with reviewing the documentation for this performance counter.

  "Exception Performance Counters"

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

# of Exceps Thrown / Sec

  Displays the number of exceptions thrown per second. This includes both .NET exceptions and unmanaged exceptions that are converted into .NET exceptions.

  For example, an HRESULT returned from unmanaged code is converted to an exception in managed code.

  This counter includes both handled and unhandled exceptions. It is not an average over time; it displays the difference between the values observed in the

  last two samples divided by the duration of the sample interval. This counter is an indicator of potential performance problems if a large (>100s) number of exceptions are thrown.

So we know from here that a large number of exceptions could cause performance issues.

The ‘problem’ here is that this counter includes handled exceptions which means that there will be no crash because, well, it is handled.

In other words it will not be anything in the event logs or any type of stack trace to look at.

And it will not be easy to set a dump trigger on a particular exception since we do not know what the exact exception is.

It could be anything, and this post is about finding out what exception is being thrown this frequently (and possibly cause the performance issue).

All we know is that the application is performing slowly, there are no crashes and the Net “# of Exceps Thrown / Sec” hits a high number (> 100).

So how do we go on from here? I’ll stick to my way of demonstrating from scratch. So the first thing is to create an application that will show this behavior.

Create a new .Net C# console application with the following code:

    class Program

    {

        static void Main(string[] args)

        {

            Console.WriteLine("Press any key to start!");

            Console.ReadLine();

            for (int i = 1; i < 4; i++)

            {

                ThrowHighAmountOfExceptions();

                Console.WriteLine("Iteration {0} done.", i);

            }

           

        }

        private static void ThrowHighAmountOfExceptions()

        {

            int waitTime = 5000;

            bool run = true;

            Stopwatch sw = new Stopwatch();

            Thread.Sleep(waitTime);

            sw.Start();

            while (run)

            {

                int x = 0;

                try

                {

                    int y = 1 / x;

                }

                catch (Exception ex)

                {

                    //Exception is handled.

                }

                if (sw.ElapsedMilliseconds >= waitTime)

                {

                    run = false;

                }

            }

            sw.Stop();

        }

    }

This application will wait 5 seconds, then throw a lot of exceptions for 5 seconds then wait 5 seconds. It will do this over 3 iterations.

Run the application but do not press any key yet.

Start performance monitor (Start – Run – PerfMon ).

Add the “.NET CLR Exceptions\# of Exceps Thrown / sec“ counter.

 

Then run the application and you should have an output like this, notice that the % Processor Time follows (in green).

 

So we can see that we have a lot of .Net exceptions thrown. So now the question is; what exceptions?

( In this case we know (DivideByZeroException), but in real life you probably don’t. If you did, you would have fixed it and not read this J )

First download and install Proc Dump from here:

  “ProcDump”

  http://technet.microsoft.com/en-us/sysinternals/dd996900

Start the application again (do not hit any key yet) and figure out what the PID is for the application (use Task Manger for example) or use the process name.

Then navigate to the directory where you have extracted Proc Dump and run the following.

C:\ProcDump>procdump 6140 -ma -s 3 -p "\.NET CLR Exceptions(_Global_)\# of Exceps Thrown / sec" 100

Here we are saying that when the process with PID 6140 hits more than 100 exceptions thrown per second and keeps doing this for at least 3 seconds, then do a full dump (-ma) on the process.

So run this and start the application. This should give an output like this:

C:\ProcDump>procdump 6140 -ma -s 3 -p "\.NET CLR Exceptions(_Global_)\# of Exceps Thrown / sec" 100

ProcDump v3.04 - Writes process dump files

Copyright (C) 2009-2011 Mark Russinovich

Sysinternals - www.sysinternals.com

Process:               HighExcepPerSec.exe (6140)

CPU threshold:         n/a

Performance counter:   \.NET CLR Exceptions(_Global_)\# of Exceps Thrown / sec

Performance threshold: 100

Commit threshold:      n/a

Threshold seconds:     3

Number of dumps:       1

Hung window check:     Disabled

Exception monitor:     Disabled

Terminate monitor:     Disabled

Dump file:             C:\ProcDump\HighExcepPerSec.dmp

[13:56.09] Counter:    17580  1s

[13:56.10] Counter:    33398  2s

[13:56.11] Counter:    33835  3s

Process has hit performance counter spike threshold.

Writing dump file C:\ProcDump\HighExcepPerSec_110623_135611.dmp ...

Dump written.

Dump count reached.

So now you have a dump of the process.

Next step is to download and install the Debugging Tools for Windows. Found here:

  "Download and Install Debugging Tools for Windows"

  http://msdn.microsoft.com/en-us/windows/hardware/gg463009.aspx

Start WinDbg and select Open Crash Dump (CTRL+D) and navigate to the dump just created and open that one.

Then load the sos.dll found in your .Net installation directory:

0:000> .load C:\Windows\Microsoft.NET\Framework\v2.0.50727\sos.dll

and then dump out all objects of type exception:

!dumpheap -stat -type Exception

in my case this gives the following output:

0:000> !dumpheap -stat -type Exception

total 10093 objects

Statistics:

      MT    Count    TotalSize Class Name

6c2f3fbc        1           12 System.Text.DecoderExceptionFallback

6c2f3f78        1           12 System.Text.EncoderExceptionFallback

6c2f0e2c        1           72 System.ExecutionEngineException

6c2f0d9c        1           72 System.StackOverflowException

6c2f0d0c        1           72 System.OutOfMemoryException

6c2f0ebc        2          144 System.Threading.ThreadAbortException

6c90b7a0    10086       726192 System.DivideByZeroException

Total 10093 objects

So this clearly shows that 10086 exceptions have been thrown and this is most likely the cause of the high number of “# of Exceps Thrown / Sec” and possible performance issues.

Now all we have to do is to find where in our code that particular exception(s) could be thrown. Thats it!
Author: Michael Aspengren

Tuning IIS 6.0 to Improve ASP.NET Performance

In the Patterns and Practices Group's "Improving .NET Application Performance and Scalability", which is available in full text online and as a PDF download from the above link, as well as in softcover through MSPress and major booksellers, there are over 1000 pages and appendixes of detailed information about how to improve .NET application performance and scalability, written by the top experts in the business. One area that is both little understood and potentially confusing is the tuning of Internet Information Services 6.0.

Formula for Reducing Contention

The formula for reducing contention can give you a good empirical start for tuning the ASP.NET thread pool. Consider using the Microsoft product group-recommended settings that are shown in Table 6.1 if the following conditions are true:

• You have available CPU.
• Your application performs I/O bound operations such as calling a Web method or accessing the file system.
• The ASP.NET Applications/Requests In Application Queue performance counter indicates that you have queued requests.

Table 6.1: Recommended Threading Settings for Reducing Contention

Configuration setting Default value (.NET Framework 1.1) Recommended value

maxconnection	2	12 * #CPUs
maxIoThreads	20	100
maxWorkerThreads	20	100
minFreeThreads	8	88 * #CPUs
minLocalRequestFreeThreads	4	76 * #CPUs

To address this issue, you need to configure the following items in the Machine.config file. Apply the recommended changes that are described in the following section, across the settings and not in isolation. For a detailed description of each of these settings, see "Thread Pool Attributes" in Chapter 17, "Tuning .NET Application Performance."

• Set maxconnection to 12 * # of CPUs . This setting controls the maximum number of outgoing HTTP connections that you can initiate from a client. In this case, ASP.NET is the client. Set maxconnection to 12 * # of CPUs.
• Set maxIoThreads to 100 . This setting controls the maximum number of I/O threads in the .NET thread pool. This number is automatically multiplied by the number of available CPUs. Set maxloThreads to 100.
• Set maxWorkerThreads to 100 . This setting controls the maximum number of worker threads in the thread pool. This number is then automatically multiplied by the number of available CPUs. Set maxWorkerThreads to 100.
• Set minFreeThreads to 88 * # of CPUs . This setting is used by the worker process to queue all the incoming requests if the number of available threads in the thread pool falls below the value for this setting. This setting effectively limits the number of requests that can run concurrently to maxWorkerThreads – minFreeThreads . Set minFreeThreads to 88 * # of CPUs. This limits the number of concurrent requests to 12 (assuming maxWorkerThreads is 100).
• Set minLocalRequestFreeThreads to 76 * # of CPUs . This setting is used by the worker process to queue requests from localhost (where a Web application sends requests to a local Web service) if the number of available threads in the thread pool falls below this number. This setting is similar to minFreeThreads but it only applies to localhost requests from the local computer. Set minLocalRequestFreeThreads to 76 * # of CPUs.

Discussion: The proviso above indicates that these settings should be used when your application has I/O bound operations and the Applications/Requests In Application Queue perfcounter indicates you have queued requests. However, I have found that settings approaching those indicated can improve performance on ASP.NET apps that do not exhibit these conditions. I recommend using the "Homer" web stress tool from at least one remote machine (and preferably more than one machine, with the supplied ASP controller page), or the .NET ACT Application Center Test application, to throw a good solid load at your app and carefully measure the performance statistics with each set of both the default and the above settings. In particular, pay close attention to the Requests per second and the time to last byte readings. This baseline testing scenario should provide the basis for further tuning if it is necessary, and it doesn't take long at all. You can only improve something if you have metrics, and the way you get the metrics is to take the time to get them! You can easily script all kinds of "user paths" through your ASP.NET application with testing software such as is mentioned here, and get the important baseline metrics you need. One more thing-- rule number 1 of software testing and debugging:

"When you are going to change something, ONLY CHANGE ONE THING AT A TIME!" Test it, get the metrics, and only then, proceed.

Kernel Mode Caching

If you deploy your application on Windows Server 2003, ASP.NET pages automatically benefit from the IIS 6.0 kernel cache. The kernel cache is managed by the HTTP.sys kernel-mode device driver. This driver handles all HTTP requests. Kernel mode caching may produce significant performance gains because requests for cached responses are served without switching to user mode.
The following default setting in the Machine.config file ensures that dynamically generated ASP.NET pages can use kernel mode caching, subject to the requirements listed below.
Dynamically generated ASP.NET pages are automatically cached subject to the following restrictions:

• Pages must be retrieved by using HTTP GET requests. Responses to HTTP POST requests are not cached in the kernel.
• Query strings are ignored when responses are cached. If you want a request for http://contoso.com/myapp.aspx?id=1234 to be cached in the kernel, all requests for http://contoso.com/myapp.aspx are served from the cache, regardless of the query string.
• Pages must have an expiration policy. In other words, the pages must have an Expires header.
• Pages must not have VaryByParams .
• Pages must not have VaryByHeaders .
• The page must not have security restrictions. In other words, the request must be anonymous and not require authentication. The HTTP.sys driver only caches anonymous responses.
• There must be no filters configured for the W3wp.exe file instance that are unaware of the kernel cache.

Discussion: The "enableKernelOutputCache = "true" setting IS NOT present in the default machine.config "httpRunTime" element. Since it is not present, we should be able to expect that the default setting of "true" is automatic. Personally, I feel better explicitly putting the attribute in there, and setting it to "true". As an aside, I have found that it is ALWAYS a good idea to KEEP A BACKUP COPY of your machine.config stored somewhere safe.

Tuning the Thread Pool for Burst Load Scenarios

If your application experiences unusually high loads of users in small bursts (for example, 1000 clients all logging in at 9 A.M. in the morning), your system may be unable to handle the burst load. Consider setting minWorkerThreads and minIOThreads as specified in Knowledge Base article 810259, "FIX: SetMinThreads and GetMinThreads API Added to Common Language Runtime ThreadPool Class," at http://support.microsoft.com/default.aspx?scid=kb;en-us;810259 .
Discussion: The .NET Threadpool is somewhat limited in its flexibility and is specifically limited in terms of how many instances you may have per process, since it is static. If you have ASP.NET applications that specifically need to run background thread processing, you may wish to investigate using a custom threadpool class. I have used Ami Bar's SmartThreadPool with great success, and have even modified it to provide a ThreadPriority overload. You can have more than one instance of this pool, and each can be custom configured. This type of approach provides maximum flexibility while simultaneously permitting individual threadpool tuning of critical resources.

Tuning the Thread Pool When Calling COM Objects

ASP.NET Web pages that call single-threaded apartment (STA) COM objects should use the ASPCOMPAT attribute. The use of this attribute ensures that the call is executed using a thread from the STA thread pool. However, all calls to an individual COM object must be executed on the same thread. As a result, the thread count for the process can increases during periods of high load. You can monitor the number of active threads used in the ASP.NET worker process by viewing the Process:Thread Count (aspnet_wp instance) performance counter.
The thread count value is higher for an application when you are using ASPCOMPAT attribute compared to when you are not using it. When tuning the thread pool for scenarios where your application extensively uses STA COM components and the ASPCOMPAT attribute, you should ensure that the total thread count for the worker process does not exceed the following value.
75 + ((maxWorkerThread + maxIoThreads) * #CPUs * 2)

Evaluating the Change

To determine whether the formula for reducing contention has worked, look for improved throughput. Specifically, look for the following improvements:

• CPU utilization increases.
• Throughput increases according to the ASP.NET Applications\Requests/Sec performance counter.
• Requests in the application queue decrease according to the ASP.NET Applications\Requests In Application Queue performance counter.

If this change does not improve your scenario, you may have a CPU-bound scenario. In a CPU-bound scenario, adding more threads may increase thread context switching, further degrading performance.
When tuning the thread pool, monitor the Process\Thread Count (aspnet_wp) performance counter. This value should not be more than the following.
75 + ((maxWorkerThread + maxIoThreads) * #CPUs) If you are using AspCompat, then this value should not be more than the following.
75 + ((maxWorkerThread + maxIoThreads) * #CPUs * 2) Values beyond this maximum tend to increase processor context switching.
Discussion: There is a long list of attention items that revolve around and are tightly woven into the IIS tuning issue for ASP.NET application tuning and scalability. These include, but are not limted to the following:

• Improving page response times.
• Designing scalable Web applications.
• Using server controls efficiently.
• Using efficient caching strategies.
• Analyzing and applying appropriate state management techniques.
• Minimizing view state impact.
• Improving performance without impacting security.
• Minimizing COM interop scalability issues.
• Optimizing threading.
• Optimizing resource management.
• Avoiding common data binding mistakes.
• Using security settings to reduce server load.
• Avoiding common deployment mistakes.

Author: Peter A. Bromberg