BDOTNET UG MEET: 26th May 2012 (WindowsAzure & SQLServer)

bdotnet  SQL12_Logo windows-azure

Today we had a UG meet at Microsoft Building,Bangalore. UG Meet means guaranteed technical learning, a great networking opportunity, feedback from masters, unlimited fun, lots of tweets and the count goes on.

The audience experienced two very informative session by two masters 1)Windows Azure by Niraj Bhat(FB|T) & 2)SQL Server Execution Plan by Pinal Dave(B|T) from SQLAuthority. This time also we had a houseful audience like every time and each one went back with some new thoughts , some new ideas and some new concepts. One thing I must say that from today onwards every one who attended the session will “Ask SQL Server” for their doubts , rather then assuming anything:)

Vinod Kumar(B|T) introduced the speakers and said a few words on Windows Azure. He asked multiple questions to audience and gave them a feel of what’s coming up. Also if you know the answers , you can tweet him or put the same on #BDOTNET FB page.

Here goes some points covered in the sessions.

Windows Azure: Big Talk

Windows Azure is an open and flexible cloud platform and used to build , deploy and manage applications across global Microsoft managed Data Centers. It provides you a great application hosting environment.

Niraj told us about the Open Source Apacheâ„¢ Hadoop which is used to develop open-source software for reliable, scalable, distributed computing.

Microsoft says ~ A Hadoop based service on Azure will offer broader access to Hadoop through easy setup and configuration, simplified programming with JavaScript integration, and integration with familiar tools such as Excel.

Apache Hadoop is a powerful, scalable, distributed computing framework designed to handle the extreme volumes of these unstructured and structured data. The Developer Preview for the Apache Hadoop- based Services for Windows Azure is available at https://www.hadooponazure.com/. Its a software framework to process vast amounts of data scheduling, monitoring, re-execution of processing tasks.

You can follow the steps below for Hadoop On Azure.

  1. workflow
  2. create gather data
  3. copy data to hdfs/asv
  4. mapreduce functions
  5. output to hdfs/asv

Niraj also spoke about Google’s initiative of Parallel Programming MapReduce. MapReduce is a programming model used to process large amount of raw data.

 

SQL Server Execution Plan

How to use petrol efficiently?  Should I sale my car? Should I go for a Bi-Cycle?

Pinal Dave started his session and triggered the current petrol issue which gave a big question mark to everybody. In what way this problem is related to SQL Server execution? Actually he was trying to clear the idea that “to be efficient , you need to use your resource properly”.

Why is my query running so slow?

Is there a problem with the way I use my select statement?

Is my index not proper?

Does the where clause make sense in the performance?

Less resources better performance. Is this true?

Pinal Dave took a session on one of the most discussed topic of SQL server i.e. Execution Plan. He explained the best practices and gave some nice suggestion with a couple of Q&A on execution plan. The demos added flavor and clear the confusion with the execution plan described through the SSMS. When to use indexes and whether the select and where statement make sense in performance tuning or not was the main discussion on the day.

Some of the points need to be considered are

  • What are the indexes we have?
  • Adding columns doesn’t change the execution plan its the where clause that change the plan, (But you need to consider the columns)
  • Nonclustered index already have clustered index in the selection.
  • Order of the index also matters.
  • When you want to tune always compare the execution plans.
  • Check the actual vrs estimated plans
  • Conversion of datatype matters
  • Put N before varchar to make it nvarchar (plain english 🙂 )

In SSMS Shortcut for Display Estimated Execution Plan : CTRL + L
Shortcut for Include Actual Execution Plan : CTRL + M

Pinal also touched the SQL Server 2012 keyword SEQUENCE and Vinod pitch in to clear some doubt on Pinal’s request. Vinod has dedicated a complete article for SEQUENCE.

  • You will not be able to change the datatype of a sequence with alter sequence statements.
  • IDENTITY is different because even the IDENTITY can get reset using the TRUNCATE command, but SEQUENCE has to be reinitialized using the ALTER for the same.
  • Sequence can be used inside a  Insert , Select , Update statement, with Over and Order By and With Default.
  • If you need a unique sequence number to be generated across a single database then sequence can be of great help. IDENTITY can be used to generate a unique number for a given table and not across tables.

QUIZ Time

  1. What is bits  in Windows 7?
  2. What are the different Optimization level? (trivial, full)
  3. What are the difference between WCF and windows service?

Useful Links

https://www.facebook.com/groups/BDotNet/

http://blogs.extremeexperts.com/

http://blog.sqlauthority.com/2010/01/22/sql-server-execution-plan-estimated-io-cost-estimated-cpu-cost-no-unit/

http://ranjandotnet.blogspot.in/2012/05/wcf-servicesvs-windows-service-this.html

Check http://beyondrelational.com/ for my articles, Interview Questions and Just Learned sections.

Till next time Keep Sharing Smile , its free 🙂

Customized success or failure log of the SQL job in the Windows Event Viewer.

This #JustLearned was published in Beyondrelational.com and for other #JustLearned by me check the link provided at the end of this article.

 

You can check the status of a  SQL job and log the customized success or failure message of the job for the day in the Windows Event Viewer.

IF NOT EXISTS (SELECT TOP 1 RUN_DATE FROM MSDB.DBO.SYSJOBHISTORY WHERE JOB_ID=
(SELECT JOB_ID FROM MSDB.DBO.SYSJOBS WHERE NAME=’S_000W’) AND
STEP_ID = 3 AND RUN_STATUS=1 AND
RUN_DATE=CONVERT(VARCHAR , GETDATE(), 112) ORDER BY RUN_DATE DESC)
BEGIN
EXEC XP_LOGEVENT 60000, ‘S_000W: DAILY JOB NOT COMPLETE’, ERROR
END
ELSE
BEGIN EXEC XP_LOGEVENT 60000, ‘S_000W: DAILY JOB COMPLETED’, INFORMATIONAL
END

Usage

–xp_logevent { error_number , ‘message’ } [ , ‘severity’ ]

For more information

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

Also check the uses RAISERROR

For other #JustLearned check the below links

SQL Server Tips – Identify running jobs on remote server

Checking the Job Schedule status of a SQL job

Windows Service Installation with a Service User Account

Dotnet – Its possible to generate the assembly of specific platform

Platform specific code in dotnet

Disabling/Enabling SQL Jobs or Job schedule from remote computer

Windows Service with Windows Timer control doesn’t tick

Interview Questions & Answers (.NET & SQLServer)

I have written couple of questions and answer in my blog at BeyondRelational. Here I plan to put all of them together and also in future when ever I will add any questions answers to that blog, I will add a link here. Go through the questions and answers , but don’t think these are complete, you have to read a lot using the reference material and other books. You have to practice a lot and then you will get the answers fundamentally and practically correct for your understanding. So happy reading and please provide feedback for this series. If you find anything wrong, please guide me and tell me the correct answer so that I can verify the same and will update the most appropriate answer.

Till next time Keep smiling 🙂

Windows Service Installation with a Service User Account

While installing a windows service I wrote this command in my installation script (not using the installutil.exe) and used a service user account.

 

SC.EXE create MyService binPath= "%CD%MyService.exe" start= auto obj= "domainMyserviceuser" Password= "AbcD12345" DisplayName= "My Service"

SC.EXE DESCRIPTION MyService  "My Alert Service"

NET START MyService

 

During installation I got the below error and the service failed to start though it got installed.

System error 1069 has occurred.

The  service  did not start due to a logon failure.

logon failed due to missing ntrights

When I added the username and password manually to the service and started it manually it worked fine.

Then I modified the installation script and added the following command before creating the service which worked fine in installing and starting the service from the package itself.

ntrights.exe -u Myserviceuser +r SeServiceLogonRight

 

The command ntrights grants the Logon as a service rights to the user.

 

You can find this article in the link BeyondRelational

#JustLearned 6: Windows Service with Windows Timer control doesn’t tick

While designing a windows service if you are planning to use a timer control, make sure not use the System.Windows.Forms.Timer. The timer event will not tick. You have to use the System.Timers.Timer , which will work properly. I came to know about this after developing a windows service using the timer control. Also System.Threading might help, though I have not tried this.


private static System.Timers.Timer tmr;

public AlertService()
{
InitializeComponent();
tmr = new System.Timers.Timer(30* 60000);
}


protected override void OnStart(string[] args)
{
try
{
//Attach the elaspsed event of the timer
//Set the interval of timer to 30 minute and enable the timer.

tmr.Elapsed += new System.Timers.ElapsedEventHandler(tmr_Elapsed);
tmr.Interval = 30* 60000;
tmr.Enabled = true;
}
catch (Exception eg)
{
//your log
}
}

My List of Useful Websites

As a professional software developer, I have come across many websites which I use in,

1) Getting the code snippet

2) Getting help while debugging my code

3) Fixing some really critical issues

4) How to perform testing

5) How to increase performance even

6) How to write mails and

7) How to communicate and present and a lot more.

Over the years I have found that these websites are quite helpful in increasing my productivity and gaining a better understanding of the subject I am currently working upon. The list of such websites has grown over time and I find them quite indispensable. Even though they are my personal choice only, I feel that the list will be very usable for other developers too.

We have www.google.com for almost every situation (at least until something better comes up and. http://msdn.microsoft.com/en-in, http://technet.microsoft.com/en-us/default and http://www.codeproject.com/ for Microsoft Technology. I have categorised the websites into various groups to help you locate a specific website as per requirement.

Process/Standards

OOPS

Link: http://www.codeproject.com/KB/architecture/OOP_Concepts_and_manymore.aspx

This article elaborates the concept of oops and believe me this is one of the best article I have ever read in the topic of OOPs. This one is neatly described by Nirosh in codeproject, with clear pictorial representation and some real life examples.

Project Management

http://www.projectsmart.com/

Coding Standards/Best Practice

C#

http://msdn.microsoft.com/en-us/library/xzf533w0(v=VS.71).aspx

http://msdn.microsoft.com/en-us/library/czefa0ke(v=VS.71).aspx

http://weblogs.asp.net/lhunt/pages/CSharp-Coding-Standards-document.aspx

Sql Server

http://technet.microsoft.com/en-us/library/cc966413.aspx

http://www.extremeexperts.com/sql/articles/BestPractices.aspx

http://www.pinaldave.com/sql-download/SQLServerGuideLines.pdf

Language/Tools

C#/.Net

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

http://msdn.microsoft.com/en-us/vcsharp/

http://www.software-pointers.com/en-articles-csharp.html

http://www.csharphelp.com/

http://www.c-sharpcorner.com/

http://www.csharp-examples.net/

http://www.blackwasp.co.uk/Default.aspx

http://www.dotnetcurry.com/BrowseArticles.aspx

http://www.csharptricks.com/

http://beyondrelational.com/

C# /.Net Blogs

http://msdn.microsoft.com/en-us/netframework/aa569283.aspx

http://www.dijksterhuis.org/

http://www.networkedblogs.com/topic/C%23/

http://www.csharp411.com/

http://www.techrepublic.com/

http://debugmode.net/

http://kashyapas.com/

http://blogs.microsoft.co.il/blogs/gilf/

http://www.devmarch.com/.NET/exclusives/writing-high-performance-net-code.html

http://beyondrelational.com/

Windows Programming

http://www.blackwasp.co.uk/Default.aspx

Database

SQL Server

http://blog.sqlauthority.com/

http://beyondrelational.com/

http://www.extremeexperts.com/General/Home/Homepage.aspx

http://www.databasejournal.com/

http://www.sql-server-performance.com/

Visual Studio

http://visualstudiomagazine.com/Home.aspx

http://msdn.microsoft.com/en-us/vstudio/aa718325

http://www.microsoft.com/visualstudio/en-us

http://www.learnvisualstudio.net/

Testing

http://www.quicktestingtips.com/tips/

http://www.softwaretestinghelp.com/practical-software-testing-tips-to-test-any-application/

http://www.readwriteweb.com/archives/12_unit_testing_tips_for_software_engineers.php

Testing Blogs

http://www.sujitnayak.com/?category_name=testing-tips

Interview Questions

http://www.questpond.com/

http://blog.sqlauthority.com/

http://www.geekinterview.com/Interview-Questions

http://csharp-interview-questions.net-tutorials.org/category/c-interview-questions/basic-interview-questions/

Quiz

http://www.software-architects.com/TechnicalArticles/CNETSecrets/tabid/81/Default.aspx

http://www.ex-designz.net/test_page.asp?tid=48

http://beyondrelational.com/quiz/default.aspx

Tips & Tricks

#justlearned

Follow @jacobsebastian on twitter for everyday tips and tricks saying #JustLearned

http://beyondrelational.com/

http://www.dailycoding.com/

http://dailydotnettips.com/tag/tips-tricks/

http://abhijitjana.net/2011/02/28/15-tips-from-daily-net-tips/

http://dotnetkicks.com/default.aspx

Tutorials

http://www.w3schools.com/

http://www.html-5.com/tutorials/index.html

http://www.csharp-station.com/Tutorial.aspx

http://csharp.net-tutorials.com/

http://www.functionx.com/csharp/

http://www.pluralsight-training.net/microsoft/

Presentation Skill/ Public Speaking

Public Speaking

http://blogs.extremeexperts.com/2010/10/11/being-a-public-speaker/

This article shows you the way and put emphasis on the different points that is required by a public speaker. Article like this can flow from the pen of a genius public speaker, and yes Vinod Kumar is the one. I have personally attended many of his presentations and you have to trust me when I say “He is the guide you are searching for”. Also visit his blog and you will get plenty of articles on SQL SERVER and presentation skills.

Presentation Skills

http://blogs.extremeexperts.com/2010/06/21/presentation-experiences-and-tips/

Blogs Writing

http://www.wikihow.com/Write-a-Famous-Blog

http://www.problogger.net/31-days-to-building-a-better-blog/

http://www.lifehack.org/articles/technology/101-steps-to-becoming-a-better-blogger.html

http://weblogbetter.com/

Cheat Sheet

http://www.cheat-sheets.org/

http://refcardz.dzone.com/

Community

http://bdotnet.in/

http://bitpro.in/

http://www.ineta.org/

http://www.sqlpass.org/

Please add your choice and let it grow. I will be waiting for your suggestion and feedback on this. Till next time “Challenge you limits”.

Manas Ranjan Dash

http://manasdash.wordpress.com/

#Just Learned 4 Customised success or failure log of the SQL job in the Windows Event Viewer.

You can check the status of a SQL job and log the customised success or failure message of the job for the day in the Windows Event Viewer.

01.IF NOT EXISTS (SELECT TOP 1 run_date FROM msdb.dbo.sysjobhistory 
02.WHERE job_id=(SELECT job_id FROM msdb.dbo.sysjobs where name='S_000w') AND step_id = 3
03.AND run_status=1 AND run_date=CONVERT(VARCHAR , GETDATE(), 112) ORDER BY run_date DESC)
04.BEGIN 
05.EXEC xp_logevent 60000, 'S_000w: Daily Job not complete', ERROR 
06.END
07.ELSE 
08.BEGIN 
09.EXEC xp_logevent 60000, 'S_000w: Daily Job completed successfully', INFORMATIONAL 
10.END

Usage –xp_logevent { error_number , ‘message’ } [ , ‘severity’ ] For more information –http://msdn.microsoft.com/en-us/library/ms186244.aspx Also check the uses RAISERROR

BDOT NET Session: SQL TIPS and MSI

Today I attended the BDOTNET UG meeting at MICROSOFT Microsoft Signature Building – Atrium, EGL, Koramangala ring road, Near Domlur, old airport road, Bangalore.Since long time am a member of the group and love to attain each session. You will come to know about new technology, lovely tips , amazing tricks, you will get a chance to meet the PERFECTIONIST of MS Technologies. I want to share some point what we learned from @Vinodk_sql and @msigeek Vijay raj in today’s session.

Myth Buster in SQL
1) Check constraint is a perfromance booster.
2) When you are using nested transaction, Once you rollback the main transaction everything will be rolled back(even if you have committed the inner one).
3) The sequence how triggers fires is : i) Action ii)Trigger iii)Rollback
4) Though we are using clustered index, still physical and logical order of placing data is different.
5) Truncate statment also keeps log, not delete only.(Common Interview question)
6) Got to know that SQL Server also believes in GHOST. The records marked for deletion are cleared up by Ghost Cleanup.

For more details you have to visit Vinod’s blog http://www.extremeexperts.com/ or http://bdotnet.in

@msigeek Vijay raj displayed the way Microsoft Installer can be used and how it helps in packaging and easy install. He showed us the microsoft client profile configuration designer and gave us nice tips and tricks.
It was really a informative session and believe me once you join us in these session only you will come to know that you can think more on what you are doing and can find out easiest way to do it.

So friends join BDOTNET and be a member today , register yourself for the SMS facility and get invitation on your mobile http://bdotnet.in/

 

Exceptions…Lets Handle it.

It is always advisable to handle EXCEPTIONs properly in your code(provided that the programming language has a support for it). Its a standard practice and the best way to handle surprised or “MY God!!!” type errors .

So you must be aware that its better to be specific while handling errors. Instead of going for a big basket (Exception class in general) choose a specilised exception(for example IOException). Because this will help you not only tracking where the bug is but also reduce debugging time.
Now thats not the point of this topic. When you are very much specific that a particular type of error is going to occur, why don’t you validate instead of going for exception way!!! That may be a more accurate way of not letting the exception to occur. If you know that divide by zero exception is going to occur in your code, then validate the number before hand and pass the non zero arguments so that you will save execution time.

So its completely depends on the requirement, study it properly and go for some validation (as applicable) rather putting more efforts on exception for the same.

Happy Coding…Keep smiling