Extreme-Advice : Analyze IO disk pressure in SQL Server

Before we move further, have a look at my following article as we are going to use the script given in that article:

Find Disk performance with sys.Dm_io_virtual_file_stats in SQL Server

I have already provided the script to display Disk IO waits in above given article, now we will further enhance that process.

By looking at IO Stall given in above article, can’t help us much. We should have data captured for few hours or may be a few days so that we can have details like which time/day we get high number of stall, we can find pattern of IO based on the statistics we capture for few hours/days. Now question comes into the picture, how can we do it with help of script given in “Find Disk performance with sys.Dm_io_virtual_file_stats in SQL Server” article?

Well we have to enhance the script give there. Let us start our journey to capture pattern of IO.

Click here to read complete article.

Extreme-Advice: Setup development environment for Windows Azure

After explaining what is cloud computing? and essential part of Windows Azure in last few days, many people asked me how they can setup development environment for Windows Azure and how they can test or deploy the application they have created. I got overwhelming response, in form of messages, from the Facebook page of “Extreme-Advice” after keeping above two cloud articles there.

Well, there are many different tools and kits provided by Microsoft to setup your development environment for Windows Azure. Some of the tools are coming in one bundle at the same time you can install it individually as per your requirement. Most of the tools given here, would comes under one bundle itself, now a days , even I have mentioned few of the important part separately just to demonstrate which exact tools comes in use for which purpose.

Here, I will be providing with few of the mandatory tools which you will need to develop Windows Azure application.

Click here to read complete article.

Understanding Microsoft Cloud and its essential part

After giving brief introduction of Cloud few days back I thought to give some light on more details of Microsoft Windows Azure.  Microsoft Windows Cloud is created by so many different services and out of which, following are the some of the important services which makes Windows Cloud more fascinating.

 

Read full article, click here.

Book Review – SQL Server Interview Questions and Answers

Well, I am not as qualified in SQL Server as the authors ofthis book (Pinal Dave & Vinod Kumar) though I dare to writemy review for his book as both of the authors are in my favourite list and I don’teven miss any article written by them then how could I miss a book written bythem? After reading the book, I really tempted to share my personal opinion with my blog reader.
“SQL Server Interview Questions and Answer” makes firstimpression that this book is written to prepare you for the interview of DBA orSQL Developer or BI professional but NO, this book is not ONLY for those whoare preparing for an interview, even seasoned developer or DBA could refer thisbook to master the basics which we may avoid or forget over the time butknowing those may create a firm ground for the project we all are working on.
Both of these authors are well known to present hard &difficult concept in very simple yet powerful manner which directly executeINSERT command in your memory without any trigger or exception, just like straitthrown dart pinch in board, this really show very hard work of both authors.
I really impressed with the book for following points.
  • “Points to Ponder” section at the end of each chapter as a Quick references to Joes 2 Pros books (I had privilege to read few of them)
  • Very inspiring quote at the begging of chapters (I enjoy it in Vinod Kumar’s twits too, in twitter)
  • Links to SQLAuthorty’s articles
When I first heard about this book, I though how much morecontent could be there in this book? As Pinal has already written the seriesarticles on this topic but with my surprise, there are LOT MORE to learn inbook so even if you have read all articles on this series in Pinal’s blog, don’thesitate to have this book, you will have so many (80%) new stuffs to look at.
This book scores 10/10 and I personally highly recommend thisbook as a good & quick reference to any professional who are dealing withSQL Server at any level.
To order the copy of the book for your own, visit SQLAuthority.com
Happy Reading!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

Last chance to win free Apple iPad this year

SQL Server MVP/Auther/Trainer Jacob Sebastian haslaunched another quiz “SQLServer DBA Quiz 2011” which is the last quiz of this year. So don’t missthis chance and answer the quiz question asked by Quiz master.
My question is already published regarding DeadLock, Live Lock and Blocking. If you have not participated yet, still, youhave chance to win as all questions are still open. 
If you are not seasoned DBA, still you can refer allquestions and answer given by expert to enhance your knowledge.
If you are experienced DBA and you know the answer, you cananswer the question, share you knowledge with community and have chance to winiPad. So what are you waiting for? Try to answer as more question as possible.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

Get your server hardware information via DM_OS_SYS_INFO in SQL Server 2005/2008/Denali

DMV(Dynamic Management View) is very powerful tool given my Microsoft in SQLServer 2005+ version; you can get much crucial information from it. Today I amgoing to show you “sys.dm_os_sys_info” which will reveal some of the hardwareinformation of the server which you are using for your SQL Server instance. 
SELECT
      cpu_count asTotal_Logical_CPU_In_Server
      ,HyperThread_Ratio
      ,cpu_count/HyperThread_Ratio asphysical_CPU_In_Server
      –converting bytes of physical memoryto MB
      ,(physical_memory_in_bytes/1024)/1024 as Total_Memory_Of_Server_In_MB
      ,(Virtual_memory_in_bytes/1024)/1024 as Current_Usage_Of_Memory_SQLServer_In_MB
      –following fields will work only inSQL Server 2008+
      –if you are using SQL Server 2005,comment following field.
      ,sqlserver_start_time
     
FROM
      sys.dm_os_sys_info
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

Take mirror backup of database in SQL Server 2005/2008/Denali

Backup is one of the most important tasks of DBA. Withouthaving proper backup, you are running in danger mode always so it is good tokeep more than backup file with you always so in any emergency if one backupgets corrupt or missing, you can use other one.
In early days of SQL Server, especially before SQL Server2005, DBA used to take full backup of database and copy it somewhere else viaDOS command XCOPY to make another copy of the same to other disk drive orsomewhere in network but SQL Server 2005+ provides the facility to take backupwith mirror copy so you don’t need to put additional efforts to make copy ofyour database backup and move it somewhere else.
Suppose my database name “SQLHub” so I would use following commandto take copy of that database in D drive and mirror copy in E drive.
–database backup of SQLHub
–full back in Ddrive SQLHubBackup folder
–same backupcopy will be copied at E drive SQLHubBackup folder
BACKUP DATABASE SQLHub
      TO DISK= ‘D:\SQLHubBackup\SQLHub.bak’
MIRROR
      TO DISK= ‘E:\SQLHubBackup\SQLHub.bak’
WITH FORMAT
GO
“With Format” option in backup is not mandatory. If we takebackup of database with backup file name SQLHub.bak on 8th July 2011,when we again run the same database backup with same backup file name “SQLHub.bak”on 9th July 2011, SQL Server keeps previous backup of database inSQLHub.bak file and copy the next so your both backup taken on 8thand 9th july 2011 will now be there in SQLHub.bak file, if you have NOT used “WITH FORMAT” option. 
If you want to take backup on 9th July 2011 and wantto remove database back taken on 8th July 2011, which is there withthe same backup file name “SQLHub.bak”, you have to use “WITH FORMAT” clause.
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

Take mirror backup of database in SQL Server 2005/2008/Denali

Backup is one of the most important tasks of DBA. Withouthaving proper backup, you are running in danger mode always so it is good tokeep more than backup file with you always so in any emergency if one backupgets corrupt or missing, you can use other one.
In early days of SQL Server, especially before SQL Server2005, DBA used to take full backup of database and copy it somewhere else viaDOS command XCOPY to make another copy of the same to other disk drive orsomewhere in network but SQL Server 2005+ provides the facility to take backupwith mirror copy so you don’t need to put additional efforts to make copy ofyour database backup and move it somewhere else.
Suppose my database name “SQLHub” so I would use following commandto take copy of that database in D drive and mirror copy in E drive.
–database backup of SQLHub
–full back in Ddrive SQLHubBackup folder
–same backupcopy will be copied at E drive SQLHubBackup folder
BACKUP DATABASE SQLHub
      TO DISK= ‘D:\SQLHubBackup\SQLHub.bak’
MIRROR
      TO DISK= ‘E:\SQLHubBackup\SQLHub.bak’
WITH FORMAT
GO
“With Format” option in backup is not mandatory. If we takebackup of database with backup file name SQLHub.bak on 8th July 2011,when we again run the same database backup with same backup file name “SQLHub.bak”on 9th July 2011, SQL Server keeps previous backup of database inSQLHub.bak file and copy the next so your both backup taken on 8thand 9th july 2011 will now be there in SQLHub.bak file, if you have NOT used “WITH FORMAT” option. 
If you want to take backup on 9th July 2011 and wantto remove database back taken on 8th July 2011, which is there withthe same backup file name “SQLHub.bak”, you have to use “WITH FORMAT” clause.
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

SQL Server Quiz 2010, one of the biggest online events of the year:

One of the SQL Server GURU and one of my favorite MVP Jacob Sebastian has teamed up with 30 well known SQL Server MVP/Author/Experts to organize one of the biggest online event of the year 2010 “SQL Server Quiz 2010”. I am so much excited to participate in this quiz competition, one can enhance their knowledge of SQL Server at the same time share their knowledge with community in these challenges.
This event will be going to start from 1st October 2010 to 31st October 2010. Each day one SQL Server quiz master will ask one question and will moderate the discussion and answer of that question. Finally Quiz Master will rate your answer between rank 1 and 10. Score of all 31 questions will be summed up to identify the winner of the competition after 31st October 2010. 
This event is not only for DBAs or SQL Developer, I have already told my .Net developers to register there to sharpen their skills in SQL Server.
You don’t have only change to share/enhance your knowledge but have chance to win some of exciting prices like Apple iPad,  Amazon Kindle, Apple iPod Nanos and free license of some cool SQL Server tools .
So what are you waiting for??? 
just grab the opportunity. If you are still not registered member of http://beyondrelational.com . Register now!!!. 
Along with Jacob, here are the names of our favorite SQL Server personality who are going to contribute in the Quiz:
Erland Sommerskarg, Itzik Ben-Gan, Andy Leonard, Louis Davidson, Arnie Rowland, Pinal Dave, Madhu Nair, Peter Larsson, Brad Schulz, Deepak Rangarajan, Greg Low, Rob Farley, Sankar Reddy, Alejandro Mesa, Glenn Berry, Jessica Moss, Madhivanan, David Barbarin, Aurelian Verla, Dinesh Asanka, Adam Haines, Jonathan Keyhayias, Vidya Sagar, Jacob Sebastian, Satya Jayanti, Michael Coles, Sugesh Kumar, Erik Jensen, Hilary Cotter, Allen White, and Paul Nielsen.

Hope to see all of you there in Quiz. Just two days remain to start event. BTW, Common Wealth Games is also about to start in India so It would be interesting for me to see which event (CWG or SQL Server Quiz 2010) is going to get more response 🙂

And last but not least, here are few important links related to SQL Server Quiz 2010:

Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Derived Column and Union All Transformation task in SSIS 2008

Many times when we are trying to get data from different sources, there is a possibility to have same key fields. To eliminate this kind of problem, “Derived Column” transformation task is very useful. For example one company has factory at one location and back office work performed from other office. Both locations has their own employee table with IDENTITY as a primary key. Obviously Identity key generally starts with 1 by default so both location would have 1,2,3 etc. in their employee table. When you try to move both the table at one single destination, you might come across with situation where employee ID 1 would have two employee and sample with many more IDs. To eliminate this situation, you can have one more column which is not exists in source table which describes where this data come from so you have unique combination of that new column and an existing EmployeeID column.
Let us see how we could perform this. Before we head towards SSIS package development, let us first create two different table in SQL Server and one blank table as a destination of these both table. You can use below script to generate those table with/without data in your SQL Server database.
create table ForDerivedColumn1
(
      EmpID INT Identity(1,1),
      EmpName varchar(10)
)
INSERT INTO ForDerivedColumn1
SELECT ‘Ritesh’ union all
SELECT ‘Rajan’
GO
create table ForDerivedColumn2
(
      EmpID INT Identity(1,1),
      EmpName varchar(10)
)
INSERT INTO ForDerivedColumn2
SELECT ‘Rushik’ union all
SELECT ‘Rajvi’ 
GO
create table ForDerivedColumnNewTable
(
      SourceID INT,
      EmpID INT,
      EmpName varchar(10)
)
GO
Now, open BIDS (Business Intelligence Studio) and create new SSIS project. Draw dataflow task in your “Control Tab” and double click on it to configure it which will redirect you to “Data Flow” tab. Since we have two different SQL Server table as an source, let us first create two “Ado Net Source” in our work place and select your database and table as a source. Below screen shot will show you how to configure “Ado NET Source”. I am showing example for FIRST “ADO NET Source” you have to do it for second “ADO NET Source” too with table name “ForDerivedColumn2”.
Now take two “Derived Column” transformation task and put them under “ADO NET Source” task, double click on it to configure.

Above screen capture showing you configuration for first “DerivedColumn” do it for second “DerivedColumn” task too with value 2 in SourceID column.

Once we have our dataset ready with Derived Column, we have to merge both dataset with “UnionALL” Transformation task. “UnionAll” work exactly same as “UNION ALL” in TSQL. So drag “UnionAll” transformation task below both “DerivedColumn” task and join extended green arrow from both “DerivedColumn” to “UnionAll” task.
Now, this is a time to convert the data in proper format with “DataConversion” transformation task as many time from different sources, same kind of data coming with different datatype, let us convert it with “DataConversion” task by putting it under “UnionAll” task and join extended green arrow from “UnionAll” task to “DataConversion”.

Now, we have to perform our last steps. Data is ready to go anywhere but we have to tell where it suppose to go. Since we want it to insert it in our SQL Server table, we are going to use “SQL Server Destination” task. Double click on it to configure it. Give your database and table reference to it as below image.

Once you give reference of your table, you have to go to “Mappings” tab in the same dialog box and set the mappings there so that proper data get inserted in proper place. Look at the below image for more detailed information.

Finally you are done with package now run it by hitting F5 key and check your database table whether you have actually received the data inside or not. After running if you get “Green Signal” in every task, J you are done.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com