ASK Expert in BeyondRelational.com

SQL & XML GURU Mr. Jacob Sebastian has real passion to reach more and more IT person to help them in Microsoft technology. I always appreciate him for his endeavor efforts to help community.  Jacob has started new forum “ASK” under BeyondRelational.com  where you can ask question directly to the industry experts.
There are many experts available there for various subjects like TSQL, XML, ETL, SSIS, SQL Server, .NET, DBA, performance tuning and many more.
If you want to ask me any SQL Server related question, feel free to ask at following link.
You may find many forums regarding all topics I have mentioned above, the main advantage here is, you can directly ask to experts rather than raising question in other forums and wait for somebody to look at your question and answer it.
So, what are you waiting for? Register in beyondrelational.com and ask question which is giving you sleepless night.
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Life savior Dedicated Administrator Connection (DAC) in SQL Server 2008

DAC could become a real life savior for SQL Server DBA in some extreme critical situation when you are not able to connect to your SQL Server instance by any other way or you found you instance totally unresponsive, you can use DAC connection.
You shouldn’t practice to login via DAC connection in normal situation as it is special connection used to use as a rescue commando only. It intends to diagnose the issue, troubleshoot it and hopefully resolve it when you are not able to make connection to instance vial any other way.
BTW, you couldn’t make more than one connection with DAC at the same time and doing so will be resulted in an error. 
Before we see how to login with DAC, I would like to tell you few points which you should keep in mind before you try out DAC.
–) you must have sysadmin server role with your login to make DAC connection
–) there are few restrictions while you make connection with DAC, you can’t Backup/Restore database. Anyhow it is advisable not to use any heavy task while you are connected via DAC as it is designed to diagnose the problem and fix it only so you might want to look around to some catalog view or DMVs.
–) Generally DAC is available locally only by default, if you try to login via DAC from remote computer, you might see following error (however, you can enable DAC from remote computer too).

There are two ways to get connected with DAC. 1.) SQLCMD 2.) SSMS. All you need to do is, specify ADMIN: as a prefix of your SQL Server instance. Suppose my instance name is “SQLHub”, I should use “Admin:SQLHub”. Suppose my instance name is “SQLHub\SQL2K8R2”, I should use “Admin:SQLHub\SQL2K8R2”.
From SQLCMD, you can connect to DAC by specifying -A flag. Now, let us see, how we can do it via SSMS.
While opening SSMS, you will see login prompt “Connect to Server”, if you will give you credential of sysadmin server role along with admin:servername(instance), you will be greeted with an error given below.

TITLE: Connect to Server
——————————

Cannot connect to admin:WIN-7XRT6YL02S0.

——————————
ADDITIONAL INFORMATION:
Dedicated administrator connections are not supported. (ObjectExplorer)

——————————
BUTTONS:

OK
——————————

 

The reason is, only one DAC connection is possible as I have already written above, if you want to access DAC connection via SSMS, don’t try to login via object explorer, rather cancel that window and follow the given steps below.
File Menu->New->Database Engine Query
Give you credential of sysadmin account, keep admin: before your server name and you will be able to connect to DAC.

WIN-7XRT6YL02S0 is my server name of SQL Server 2008, you can see “Admin:” before my server name. I am going to use my windows administrator account as it has sysadmin server role, however you can use any of your login which has sysadmin server role permission.
Keep this information ready as you never know when you need it?
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Understanding basic tools of SQL Server 2008/2005

One of my friends has just updated his live database from SQL Server 2000 to SQL Server 2008. You may know that we had direct access of system table in SQL Server 2000 which is not directly possible in SQL Server 2008/2005. He has developed many scripts in last 8 years which was directly accessing many of the system tables to generate certain kind of report, out of such scripts, few was not working. 
In recent SQL Server (2005/2008) version, we do have those “System Base tables” but it is not directly visible as well as accessible unless you are making dedicated administrator connection (DAC).  You have to access those information stored in system base table via “Compatibility View” or “Catalog View”.
“Compatibility View” is there to support your old scripts (though few very specific won’t work) which was using direct system tables in SQL Server 2000. For example we had sysobjects, sysindexes, sysusers etc. tables in older version but now we have views with the same name so your scripts of older version would works fine. Though it is “Compatibility View” intend to provide backward compatibility only, it doesn’t consist many columns which provides information about specific features of newer SQL Server version so it is recommended to use “Catalog View”.
Right from SQL Server 2005+ we are having “Catalog View” which was reading may useful information from system base tables and provide us. “Catalog View” along with “Dynamic Management View (DMV)” is under sys schema. I would prefer to go with “Catalog View” rather than “Compatibility View”. Because “Catalog View” is much richer than “Compatibility View”, you can run following query and see result by your own.
–Compatability View
select * from sysdatabases
–Catalog View
select * from sys.databases
Apart from these Views, there is one more powerful tool to read metadata of SQL Server 2008/2005 and internal information about SQL Server is Dynamic Management View (DMV) and Dynamic Management Functions (DMF). DMV and DMF generally starts with “dm_” and it resides in “sys” database so it would always starts with prefixes “sys.dm_” for example:
–return information about authenticated session in SQL Server
select * from sys.dm_exec_sessions
–returns info about currently locked object.
select * from sys.dm_tran_locks
–this is DMF and retun I/O stat for MDF and LDF file.
select * from sys.dm_io_virtual_file_stats(DB_ID(N’master’), 2);
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Sharpen your .NET Skills with .NET Quiz 2011 at BeyondRelational.com

SQL Server & XML Guru Mr. Jacob Sebastian (SQL Server MVP) has recently completed one grand online event for SQL Server along with other 30 SQL Server MVP/Blogger, winners are already declared for that, find more details here.
After completing this grand international online event, he has now started .NET Quiz 2011 on BeyondRelational.Com with help of  Hima Bindu Vejella who is Quiz Manager for “.NET Quiz 2011”.
This event has already started from 1st January 2011 for 31 days. Each day one .NET quiz master will ask one question and s/he 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 January 2011.
You don’t have only chance to share/enhance your knowledge but have chance to win some of exciting prices like Apple iPad and free license of some cool .NET tools.
So what are you waiting for??? 
Just grab the opportunity. If you are still not registered member of http://beyondrelational.com . Register now!!!. 
Here is the official home page for .NET Quiz 2011.
BTW, I am too one of the quiz master and my question is going to be enabled by today 2nd January 2011. Here is the link for the same.
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah