SQL Server Tips & Tricks: CROSS JOIN FOR UNMATACHED SEQUENCE

My tools Catalogue

Video Desktop Recordor
https://technet.microsoft.com/en-us/magazine/2009.03.utilityspotlight2.aspx?pr=blog

Sitecore TDS Issue: First Time Sync to sitecore-length of field content does not match the content-length attribute

https://medium.com/sitecore-tips-tricks/deploying-sitecore-items-with-git-and-tds-f6a47605d1b4




Step 1:
The trick is you have to add gitattribute using VSO git and then commit and sync the text given in the blog


Step 2 Delete working local copy and clone the repository again.


Step 3: Sync Sitecore again, error will disappear.

Idevice + Iphone + Ipad= IOS && Private browsing + Local storage Limitations

There are several reasons why we use localstorage in place of cookies to get away with limited memory placeholder for cookies. In ecommerce sites we often allow users to add to carts without login to certain workflow steps and hence we would require local storage to store the product details.

There is potential problem with IOS private browser where localstorage is either disable  or we can't write it back to local storage collection.

http://spin.atomicobject.com/2013/01/23/ios-private-browsing-localstorage/

Just Debugging & Performance Web Tool

Debugging Tools:

1. Firebugs
2. Firepath for selenium
https://addons.mozilla.org/eN-US/firefox/addon/firepath/
3. Selenium tool


PERFORMANCE TOOL:-

Like/Alternative Yslow.
http://gtmetrix.com

More of a network / file size analysis.
http://www.webpagetest.org

http://tools.pingdom.com/fpt/
Very similar to YSlow and Pingtom Tools



Does a side by side race of two URLs
http://www.whichloadsfaster.com/ –

results are generally public but you can download and run this program on your own server if you want. Very useful if you have lots of websites to check and want to automate that process.
http://www.showslow.com/

Yslow.

Going beyond just page load time:
HTML Validator:
HTML validation should be a pre-commit step.
I generally use the HTML Validator plugin in Firefox.
online version is here: http://validator.w3.org/

Selenium IDE 2.9.0 Kick Start Installation Guide

It was useless effort all these days to get started with selenium. I did install selenium in past using firefox add-ons and it was really smooth and fast way to do it however somehow this time things were not same as I thought it would be.

Problem:
Coming back to actual problem: Selenium IDE not appearing , then I went to menu, choose customize and then added as toolbar, still no success.

Solution : Brute force method

• Go to http://docs.seleniumhq.org/download/
• Search for Selenium IDE. 2.9 , click on firefox 2.9 and save it in desktop or file system.
• Install .xp file from Add ons .See picture.
• It will give all set of required library to get started. Click Install now. Selenium IDE appears in tool bar to get you started.

 

VISIO Stencils references

Cloud Architecture References

https://msdn.microsoft.com/en-au/library/dn589803.aspx

http://www.microsofttrends.com/2014/05/30/new-official-microsoft-azure-visio-stencil/

Production Incident 10: Default Proxy Or Proxy Or CORS When Remote Name Cannot be resolved

Issue: Remote Name cannot be resolved.

Scenario: When we call third party or cross domain api within our web api.

Resolution :

1. Enable CORs at third party api Or
2. Try to include default proxy
3. Or specific proxy at your client web APi

<system.net>
    
    
  </system.net>

 

Or

<system.net>

http://proxy.XYZ

"usesystemdefault="True" />

</system.net>

 

Or

Enable Cors

 

Error Slab:

 

Production Support Incident 6: Bad Architecture Design Database server Box having IIS webserver

Architecture Scenario:
A shared database SQL Server physical box or VM contains shared databases which is connected to different web servers hosted with different website. Lets assume within that database box we have IIS configured with WCF hosted service. The initial objective would be the wcf service which is present within database server must be using database table for storing service data. This is bad design.

What is the problem?
Now when we allocate RAM to SQL server box , sql server consumes all the RAM and keep very little RAM for OS activity. Now lets assume we have IIS configured with wcf service with database server and there is very little RAM available for IIS and OS as SQL server consumes most of the RAM memory . In such case there is going to be memory overflow or Network IO wait conditions for website request that are sending requests to this wcf service hosted in IIS that resides within this database server.

Workaround: You have to explicitly allocate RAM for sql server and for IIS to process the requests. The flipside is your sql server may started getting load and RAM consumption may shoot up to peek which may results in Suspended transactions and process requests with latency.

This is design flaw at infrastructure Level hence it is always important to make assessment and revisit architecture.

 

Production Support Incident 5: Enable default proxy to access internet content or any outside DMZ API

Sometimes production webserver servers are not allowed  to access outside internet web to prevent any vulnerable attack within the server.

 

Sometime there may be requirement to call web api which is hosted in cloud and your private intranet web server requires to call those api which is hosted externally in such case we may use internal proxy within the network to make a call across firewall.

 

To enable the proxy setting I have added following piece of code in config file

http://proxy.

...." usesystemdefault="True" />

Even after allowing access through proxy the server certificates are not configured properly which may results in further execution. Ensure certificates are installed in browser to make handshake to the api.

Check certificates in server
https://technet.microsoft.com/en-au/library/cc754431.aspx

Production Support Incident 3: Disable SSlv3 Poddle Attack on Azure web role and worker Role

Poodle attack - SSLV3 Enabled

Tool to ---Test your website server is poodle free..poodle test?
https://www.ssllabs.com/ssltest/


Courtesy: http://en.wikipedia.org/wiki/POODLE

The POODLE attack (which stands for "Padding Oracle On Downgraded Legacy Encryption") is a man-in-the-middle exploit which takes advantage of Internet and security software clients'

Website and server should be poodle free, its vulnerable if we have SSLV3 policy protocol is enabled. One can disabled SSLV3 through regedit whereas in cloud we have to disable using startup script in Service definitions.

1. Add .cmd batch file in startup script of Role profile folder.
2. Give path of start up script in servicedefination configuration under task tag.
3. Keep the powershell file in root of website or webrole.
4. Ensure ps1 file is copy to content always. Right click on powershell file in solution VS and check property to enable this options.

Importantly!
Even after deployment your SSL scan test lab shows you  C grade check for webseal or WAF (Web application firewall). In such case you may have to disable ssl in server that is acting as firewall. Check with your infrastructure team.

****Check any WAF environment. Web application Firewall behind which your azure web role may resides.


http://www.ideliverable.com/blog/making-iis-configuration-changes-in-a-web-role-startup-task
http://blogs.msdn.com/b/jimoneil/archive/2011/02/07/azure-startup-tasks-and-powershell-lessons-learned.aspx
http://www.dotnettwitter.com/2011/11/executing-powershell-script-with.html
http://stackoverflow.com/questions/15350936/running-powershell-from-azure-startup-task
http://stackoverflow.com/questions/28888768/adding-startup-script-to-azure-web-role-copy-to-output-directory-missing
http://www.troyhunt.com/2014/10/disabling-ssl-3-in-azure-websites-and.html
http://blogs.msdn.com/b/kaushal/archive/2014/10/22/poodle-vulnerability-padding-oracle-on-downgraded-legacy-encryption.aspx

http://disablessl3.com/

 

Please engage the team who maintains these Linux box and follow document below to disable SSL3.

Web servers

Apache

Put the following line in your configuration file, or replace any existing line starting with SSLProtocol:

SSLProtocol All -SSLv2 -SSLv3

Then run: sudo apache2ctl configtest && sudo service apache2 restart.

Don't forget to test your website.

Nginx

Put the following line in your configuration file, or replace any existing line starting with ssl_protocols:

ssl_protocols TLSv1 TLSv1.1 TLSv1.2;

Then restart the server (in Ubuntu: sudo service nginx restart).

Don't forget to test your website.

Lighttpd

Lighttpd releases before 1.4.28 allow you to disable SSLv2 only.

If you are running at least 1.4.29, put the following lines in your configuration file:

ssl.use-sslv2 = "disable"

ssl.use-sslv3 = "disable"

Then restart the server (in Ubuntu: sudo service lighttpd restart).

Don't forget to test your website.

Production support Incident 2 : Never Rely on LINQ Object IEumerable

If you are supporting application that have nhibernate, entity framework without stored procedures setup, there will speed breaker ahead in your journey. It may work fine for given capacity and user base but sometimes may give you a surprise.

Something below in your query would ring a alarm bell.
return logEntries.ToList().Take(10);

This particular query will bring resultsets from database to webserver and then fetch 10 records for you. Just imagine for some reason and data combination you got thousands of records from database and then it manipulates something web server with these sets of resultset, surely your web server CPU will spikes for sure. There will be intermittent downtimes due to concurrent users. If there is caching profile then there could be possible race condition to create them. There are possible potential occurrence of hung and suspended transactions in sql server and so on.

You even think of brute force method to KILL SPID...and you save sometime to rescue yourself. If you're a support guy then it is good you atleast know what LINQ object query does in the background. With just little knowledge on the surface won't help. You may take short cut to fix this by mounting or increasing server configuration however this short term solution and this may blow out of proportion in another next month due to increase in users and process.

Stop gap arrangement :- to maintain P1 at bay. until you fix the main solution.

SET NOCOUNT ON

      PRINT 'Checking for long running processes'    

      DECLARE @TRANSACTION_STATUS as varchar(40)

      Declare @TimeElapsed as decimal

      Set @TimeElapsed =0.001

      set @TRANSACTION_STATUS ='SUSPENDED'

      CREATE TABLE ##temp (

      [SPID] [varchar] (13),

      [Status] [varchar] (120),

      [Login] [varchar] (120),

      [HostName] [varchar] (120),

      [BlkBy] [varchar] (13),

      [DBName] [varchar] (120),

      [Command] [varchar] (130),

      [CPUTime] [varchar] (120),

      [DiskIO] [varchar] (120),

      [LastBatch] [varchar] (130),

      [ProgramName] [varchar] (140),

      [SPID2] [varchar] (13),

      [REQUESTID] [varchar] (13)

      )

     

      --Keep Only Recipe Related Suspended Logs /Details to process further

      INSERT INTO ##temp

      (SPID,[Status],[Login],HostName,BlkBy,DBName,Command,CPUTime,DiskIO,LastBatch,ProgramName,SPID2, REQUESTID)

      EXECUTE sp_who2

      DELETE from ##temp where not [login] = 'xyz' or not dbname = 'abcDB' or HostName not in ('01-VM','02-VM','03-VM')

--The above hostname is loadbalanced webserver.

 

      UPDATE ##temp set lastbatch = Convert(DateTime, Convert(VarChar(4), Year(GetDate())) + '/' + lastbatch)

      SELECT spid, lastbatch from ##temp where lastbatch < (getdate() - @TimeElapsed)

 

      --Check for suspended transaction for last 1 hour.

      IF (select count(*) from ##temp where lastbatch < (getdate() - @TimeElapsed) and Status=@TRANSACTION_STATUS) <> 0

      BEGIN

                        -- Generate output files

                        SET NOCOUNT ON

                        DECLARE @spid varchar(5)

                        DECLARE @sql varchar(200)

                        DECLARE @sql2 varchar(200)

                        select @spid = rtrim(spid) from ##temp where lastbatch < (getdate() - @TimeElapsed) and Status=@TRANSACTION_STATUS

                        PRINT 'Start Processing'

                       

                        -- Kill the rogue process

                        PRINT 'Process to be killed is: ' + @spid

                        DECLARE @cmd varchar(10)

                        select @cmd = 'kill ' + @spid

                        Print @cmd

                        --Kill Process

                        exec (@cmd)

END

drop table ##temp

--select * from ##temp

END

 

Production Support Incident 1. SQL Server Suspended Transaction And IO Wait Issue

This is the most critical findings when there is issue with application downtime.

Application: CMS System- Content Management System

Technology: Custom Asp.net

Scenario: For any CMS system , the caching plays a very essential role. To improve overall user experience and responsive of the system , as a thumbrule and architecure design norms the CMS system should be always initialized by caching. The system content is cached one time so that there is no more chatty communication with SQL server or for that matter with database. This is important as the content in CMS website public facing internet website most of the content is global and applicable for all users. In such scenario the best practice is to cache the content and most common element one time during overall website lifecyle throughout a day.

So when we consider caching below set of design principle must be taken care:-
Life cycle of caching-Age of caching
Frequency and timeline when Business user changing content so that the changes reflects during business as usual.
Warm up caching option in IIS to reduce overall users impact on cache expiration.
Importantly the amount of data cached .Impact on w3p process in IIS, CPU utilization and heap memory fot which sql query is executed.
Mission critical application keep logic outside of application layer..keep it in database for quick fix and resolution . If logic is embedded in business logic with linq query within application layer, Consider hugh business impact and application downtime.

1. Quick Checks:

USE master;
GO
EXEC sp_who2 'active';
GO

If there is suspended transaction SPID then there is serious problem. if suspended transaction is not getting clear within 10 secs then there is potential issue with memory or execution completion of query
2. Quick Checks async_network_io wait in sql server
http://blogs.msdn.com/b/joesack/archive/2009/01/09/troubleshooting-async-network-io-networkio.aspx

3. Quick Checks Page latch above 20
http://blogs.msdn.com/b/askjay/archive/2011/07/08/troubleshooting-slow-disk-i-o-in-sql-server.aspx
http://www.jasonstrate.com/2010/09/index-black-ops-part-2-page-io-latch-page-latch/

SELECT session_id, wait_type, resource_description FROM sys.dm_os_waiting_tasksWHERE wait_type LIKE 'PAGELATCH

Resolution:
Either Optimize query
Or Increase RAM of Sql server OS box.

Production Support Security Vulnerability Attack

Production Support

The production support is always a touch job to do . The development is a lean process and it follows the timeline, process, planning and execution within the given timeline. There is liberty to give estimation and do planning whereas with support the planning is never the case. One can never know what next.
Security vulnerability sometimes taken lightly in support production and there is always a kind of disconnect among different groups like application, database and infrastructure support. When these groups work in a very disconnected mode and communication channel is not so apparent among them then there is a chance of high lapse in support paralysis.

Poddle Attack

 Unused certificates

Check for expired SSL certificates.

 

Step by step of how to disable SSL V3.

 

http://azure.microsoft.com/blog/2014/10/19/how-to-disable-ssl-3-0-in-azure-websites-roles-and-virtual-machines/

 

Use the following site to see if your site is poodle free.

 

https://www.ssllabs.com/ssltest/index.html

 

You need to get GRADE A after you have applied the fix.

DOS-DDOS- Distributed Denial of Service

Look out of requests from most common source . Someone must be screwing your system calling /loading or making requests to your website. If you check netstat, IIS logs, windows event application logs, webstats or google analytics something which gives you a indication that there is something wrong with your application. This will tell you the unusual behaviour within the systems when requests common to your server from most common sources.

There are chances your application login attempts of all users will be exhausted and thus users accounts are locked. This is a very huge business impact. Just imagine if this is your E-commerce or banking or financial sites. The day loss of business would be enormous. Hence we have something called captha introduced in early web world to tackle this.

Sr.No	Period	User Session
1	Diwali	4,00,000
2	Christmas	2,00,000
3	Normal Day	50,000

If see for given day and timeperiod the session building up in the system is going exponentially there is something serious activity going on in the system. Splunk ,HP and other tools help you find out that.
Check the size of iis log . Compare with previous days and can help you analyse the situations more clearly.
 

Ghostery Makes it easy for you

Ghostery is such a handy tool that makes our life easy when it comes to security. You can find and able to block all external sources or third party analytics that fetches source data from your website.

Ghostery is an add-ons available for chrome, safari, firefox. Check out cool stuff.

Convert Timezone UTC to Local Time e.g TimeZoneInfo.FindSystemTimeZoneById("India Standard Time")

Convert UTC to Local TimeZone

 
public class TimeZoneConvertorHelper
    {
        private static readonly TimeZoneInfo IndiaTimeZone;

        static TimeZoneHelper()
        {
            IndiaTimeZone = TimeZoneInfo.FindSystemTimeZoneById("India Standard Time");       
        }

        public static DateTime GetCurrentMelbourneDateTime()
        {
            return TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, IndiaTimeZone);
        }
    }

Big Debate: Sticky Session

Sticky Session

Cautions decision to be taken when enabling sticky session . Checks to be made:-

1. Load balanced server

2.Users session distribution across load balanced server.

3. Round Robin distribution

 

Warnings

1. When sticky session enabled , requests are not evenly distributed.
2. Sticky sessions are always good if there is not much session values/data for each users.
3. RAM utilization may be high as compared to CPU

Options:

Cloud : Elastic Load balancing

Enable sessions based on application instances or node.

Web API :Utility To read Json output From WebResponse

Introduction

The below utility fetches the output from API request through HTTP in JSON format.

public static class JsonRequestStreamUtility
    {
        public static WebRequest CreateRequest(string url, string method = WebRequestMethods.Http.Get, string body = null)
        {
            var webRequest = (HttpWebRequest)WebRequest.Create(url);
            webRequest.Method = method;
            webRequest.Accept = "application/json";
            webRequest.Headers.Add("Accept-Charset", "utf-8");
            webRequest.ContentType = "application/json";

            webRequest.Proxy = new System.Net.WebProxy()
                {
                    Address = new Uri("xyz"),
                    UseDefaultCredentials = true,
                };
            
            if (body == null)
                return webRequest;

            using (var streamWriter = new StreamWriter(webRequest.GetRequestStream()))
            {
                streamWriter.Write(body);
            }
            return webRequest;
        }

        public static string ExecuteJsonResponseStream(WebRequest webRequest)
        {
            
            if (webRequest == null) throw new ArgumentNullException("webRequest");

              try
            {

                // Get the stream associated with the response.
                Stream receiveStream = webRequest.GetResponse().GetResponseStream();

                // Pipes the stream to a higher level stream reader with the required encoding format. 
                StreamReader readStream = new StreamReader(receiveStream, Encoding.UTF8);
                return  readStream.ReadToEnd();
                
            }
            catch (WebException we)
            {
                              
                return string.empty;
            }
        }
    }

Cookieless Session Vs Cookie Session

Reconstructed MSDN Understanding:-

Simple E.g. You go in pub and you handover your jacket in a counter .To get your jacket back you get some token as a reference. Similarly we have session id which is generated on request made to server and it is placed cookies.

Quick Take:-

Cookie Session

1. Cookie store session id and it identify session data from server for each request and response. User agent browser to server.

2. If Session object is not created or used apart from session_start , for each new requests it creates new session IDs. Hence it is require to create session object either in session_start or any part of application.


Cookie Less Session

1. In this case session id is static and remain same for entire session period
2. Session id is stored in URL for each request and response.

There can be possibilty when there is device where cookies are disabled and in such scenario we can make use of Cookie less session. There is feature itself in asp.net by using Autodetect for UseDeviceProfile.

http://msdn.microsoft.com/en-us/library/system.web.sessionstate.httpsessionstate.sessionid(v=vs.110).aspx

http://www.iis.net/learn/application-frameworks/scenario-build-an-aspnet-website-on-iis/planning-step-2-plan-asp-net-settings

http://msdn.microsoft.com/en-us/library/vstudio/ms178581(v=vs.100).aspx

http://blogs.msdn.com/b/jaskis/archive/2009/12/23/securing-session-id-asp-asp-net.aspx

FactFile SessionID in Asp.net and SqlServer SessionState

Facts to be remember. While dealing with session always keep below factfile into consideration.

I created a variable in the Session_Start in the global.asax file:

var sessionID = Session.SessionID;

 

I found the following data while debugging the application:

ASP.NET generated : lehxv4so4ioi2gqqaxtjzhyo
SQL Server saved  : lehxv4so4ioi2gqqaxtjzhyo84497b6f

 

 

lehxv4so4ioi2gqqaxtjzhyo84497b6f
in bold is your sessionid and in italics is the application id
the extra 8 bytes are the applicationid
 

SQL Server Session Tables

ASPStateTempSessions
ASPStateTempApplications
 

SQL Server Session Database

tempdb database in SQL Server by default

1. Very Very Imp never use Tempdb for outproc session.
2. In out proc mode session_end never calls, no use if you apply any debug or diagnostics pointers.

 

If we using webfarm and out proc session, ensure machine key across server instance is unique.

 

http://www.codeproject.com/Articles/104082/Configuring-ASP-session-state-on-SQL-server

http://stackoverflow.com/questions/22228972/strange-timeout-in-sitecore-7


http://blogs.msdn.com/b/akshayns/archive/2008/09/29/common-reasons-for-the-session-loss-issue-in-asp-net-applications.aspx

Cookie Container


http://www.developer.com/net/asp/article.php/10917_3595766_3/Storing-Session-State-in-a-SQL-Server-Database.htm


http://weblogs.asp.net/stevewellens/using-session-state-in-a-web-service


http://weblogs.asp.net/jongalloway/10-things-asp-net-developers-should-know-about-web-config-inheritance-and-overrides


http://www.c-sharpcorner.com/UploadFile/1d42da/using-session-state-in-a-web-service/




http://blogs.msdn.com/b/tess/archive/2008/11/06/troubleshooting-appdomain-restarts-and-other-issues-with-etw-tracing.aspx


http://www.codeproject.com/Articles/35119/Using-Session-State-in-a-Web-Service


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


http://seejoelprogram.wordpress.com/2008/11/10/maintaining-aspnet-session-state-in-an-ajax-application/



http://stackoverflow.com/questions/24707339/how-to-make-a-session-enabled-asp-net-web-service-work-with-an-ajax-client-in-a



http://devpinoy.org/blogs/willydavidjr/archive/2008/06/13/using-asp-net-session-state-in-a-web-service.aspx