I hope that all of you had a chance to participate in my session on the “Underappreciated Features of Microsoft SQL Server” during the Exclusive SQL Server Webcast series on Security & Scalability. If not, I trust the recorded sessions would be up soon for you to see. I will share the location of the same once available.

During the session, I demonstrated the T-SQL Debugger (http://beyondrelational.com/blogs/nakul/archive/2011/02/02/t-sql-debugger-underappreciated-features-of-microsoft-sql-server.aspx). Looking at the number of questions after the session during the Q&A and from my colleagues at the office, it looks like I underappreciated the uniqueness of the feature, which always gives a sense of satisfaction to a presenter. I felt that I had brought something new to the audience, and that the audience would be using the features demonstrated. That’s great!

I received a number of questions related to the debugger. Earlier this week, I had mentioned about which SQL Server versions/editions support T-SQL Debugging, and what are the basic permissions that one needs to use the T-SQL debugger (Enabling T-SQL Debugger in SQL Server Management Studio (SSMS)).

Even after you make sure that the user permissions are configured correctly, you may find that you are still not able to use T-SQL Debugging if attempting to debug when the Database engine is running on a computer other than where the Query editor is running. This is because debugging involves both server & client side components and your Windows Firewall is not configured correctly.

WARNING: If you are not comfortable with modification of the Windows Firewall rules, please allow your IT administrator to make these configuration changes. The author is not responsible for any security or other system stability issues that may arise due to improper Firewall configuration.

Here are the configuration changes that need to be done (for SQL 2008):

On the Server

1. Add TCP port 135 to the exceptions list on the Windows Firewall
2. Add the program sqlservr.exe to the exceptions list. By default, sqlservr.exe is installed in C:Program FilesMicrosoft SQL ServerMSSQL10.InstanceNameMSSQLBinn, where InstanceName is MSSQLSERVER for the default instance, and the instance name for any named instance
3. If using IPSec, you will also need to open UDP port 4500 and 500

On the Workstation (where the Query editor is running)

1. Add TCP port 135 to the exceptions list on the Windows Firewall
2. Add program ssms.exe (SQL Server Management Studio) to the exceptions list. By default, ssms.exe is installed in C:Program FilesMicrosoft SQL Server100ToolsBinnVSShellCommon7IDE

That should get your debugger working as expected.

Until we meet next time,

Be courteous. Drive responsibly.

I hope that all of you had a chance to participate in my session on the “Underappreciated Features of Microsoft SQL Server” during the Exclusive SQL Server Webcast series on Security & Scalability. If not, I trust the recorded sessions would be up soon for you to see. I will share the location of the same once available.

The session went very smooth, and I was fortunate enough to have a strong following throughout the session. I presented the following underappreciated features during the session:

1. For Administration: Object Explorer Details and Object Explorer
2. For Development: Template Explorer and T-SQL Debugger

Looking at the number of questions after the session during the Q&A and from my colleagues at the office, it looks like I underappreciated the uniqueness of the feature – the T-SQL debugger. I received a number of questions related to the debugger, and I will attempt to address a few of these in the upcoming sessions.

For those who would like a quick, hands-on experience of the T-SQL Debugger, you may want to visit my article at: http://beyondrelational.com/blogs/nakul/archive/2011/02/02/t-sql-debugger-underappreciated-features-of-microsoft-sql-server.aspx

Some of the questions I received were:

1. Is the T-SQL Debugger version or edition specific?
2. I use SQL Server 2005 and I am not able to see the debugger
3. I use SQL Server 2008 R2, and yet the debugger is not available

The answer to each of these questions is as follows:

History

The T-SQL Debugger has been around at least since the days of Microsoft SQL Server 2000 (that’s when I started using it) – it’s just that it’s not being used often which makes it an underappreciated feature, and one of the key demos that I wanted to demonstrate in the session.

Enabling the T-SQL Debugger in SQL Server Management Studio

To the best of my knowledge, the debugger is available in all editions of the Microsoft SQL Server, except the Express edition. Hence, if you are not able to see the T-SQL Debugger in your SSMS, most probably,  you are using the SSMS that comes with the Express edition.

If you can see the debugger, but are not able to debug a T-SQL query, it is important to note that not everyone is allowed to debug T-SQL code. This is because, T-SQL Debugging is a highly-privileged operation. To enable the T-SQL debugger in SSMS, the following must be true:

1. The user context under which the SSMS is running must be a member of the sysadmin fixed server role
2. The user must be connected to the SQL Server using a user who is member of the sysadmin fixed server role

Until we meet next time,

Be courteous. Drive responsibly.

Today’s post is part of the series that I am running under the title "Under Appreciated Features of SQL Server". The series has been inspired by Andy Warren’s editorial on SQLServerCentral.com.

The pre-requisite for today’s session is a basic knowledge of connecting to a SQL Server using the command line.

SQLCMD Utility

Batch files and command line utilities are perhaps some of the most underappreciated features of any IT establishment. When connecting to a database, we had the “OSQL” utility in the days of SQL Server 2000.

When SQL Server 2005 came about, it introduced the SQLCMD utility. This utility is a replacement of OSQL, and has a large number of advantages over the same. To list a few:

1. Parameterized variable support
2. Use SQLCMD to execute both T-SQL and DOS-commands (Refer Pinal’s post here)
3. Improved Performance & security
4. Support for DAC
5. Multi-server connection

There may be more features of SQLCMD, which you are more than welcome to mention in the discussion associated with this post.

SQLCMD rules

There are some basic rules for any SQLCMD query. They can be summarized as under:

1. SQLCMD commands must be the first statement on a line
2. Only one SQLCMD command permitted on each line
3. SQLCMD commands can be preceded by white spaces or comments
4. Before the start of each SQLCMD command, use a colon (“:”). Only exception to this rule are “exit” and “!!” commands
5. Single-line comment characters are two hyphens (–) and must appear at the beginning of a line

SQLCMD and SSMS

What many people don’t know is that the SQLCMD mode is also accessible from the SQL Server Management Studio. This gives you as a developer, the comfort of writing and executing SQLCMD files using the familiar environment of the SQL Server Management Studio.

Enabling SQLCMD mode in the SSMS

1. Launch the SQL server Management Studio
2. Click on “New Query” to open a new instance of the T-SQL Query editor
3. Go to Query –> “SQLCMD”

Writing a simple query in the query editor

Taking an example from the Books On Line (http://msdn.microsoft.com/en-us/library/aa833281.aspx), write the following code in the T-SQL editor:

!!mkdir c:TempSqlcmdOutput
:out c:TempSqlCmdOutputtestoutput.txt
select @@VERSION as 'Server Version'
!!dir
GO
select @@SERVERNAME as 'Server Name'
GO 4

Now, click “Execute” or hit “F5” to execute the query. Navigate out to the directory – “C:TempSqlCmdOutput”, and open the “testoutput.txt” file. You can see that the combined output of the DOS-commands and the T-SQL Query is available there.

Execution technology

When run from the command-line SQLCMD uses the OLE DB provider, whereas when run from SSMS, it uses the Microsoft .NET framework sqlclient.

Some limitations of the SQLCMD mode in SSMS

Because the SSMS cannot display interactive prompts, such queries cannot be executed from within the SSMS, and must be used from the command line.

Conclusion

We can now see how the most modern SSMS interface can help us write efficient command line SQLCMD queries. I personally use the SSMS to edit production level SQLCMD queries for our product and hence I urge you all to experiment with the SQLCMD mode and experience the raw power that it has to offer.

This post concludes my series on the "Under Appreciated Features of SQL Server". It has been great learning new stuff about SQL Server throughout writing of this series. I will be sharing a few of the nuggets that I have accumulated in the time to come. I hope that you, my kind readers will enjoy my future posts as well.

Until we meet next time,

Be courteous. Drive responsibly.

Today’s post is part of the series that I am running under the title "Under Appreciated Features of SQL Server". The series has been inspired by Andy Warren’s editorial on SQLServerCentral.com.

Microsoft SQL Server 2005 was a completely different SQL Server. The internals had changed and so had the mechanisms to monitor and manage the server. What this meant was that administration tasks had to be re-developed – all procedures had to be re-written and all script repositories had to be redone. Why? SQL Server 2005 made it easier for administrators to query the SQL Server meta-data by the introduction of Catalog views and Dynamic Management Views.

Catalog Views

In the days of SQL Server 2000, administrators had to query the system tables directly. This had a number of drawbacks due to the tight coupling with the underlying table structure. However, looking at it from a developer perspective, these tables are internal to the SQL Server and we as administrators were invading it’s privacy.

Hence, starting SQL Server 2005, any metadata information that was used by the SQL Server database engine is now exposed to the administrators via Catalog Views. They are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information.

You can read all about catalog views on Books On Line at: http://msdn.microsoft.com/en-us/library/ms174365.aspx.

If you are still stuck with SQL 2000 system table based queries

You can mover over to Catalog Views by using Compatibility Views (http://msdn.microsoft.com/en-us/library/ms187376.aspx). But, if you are now thinking of moving away from system tables, my suggestion would be to take the big jump and move over to Catalog Views directly.

A mapping of the system tables to the catalog views is available at http://msdn.microsoft.com/en-us/library/ms187997.aspx

Dynamic Management Views & Functions

Fondly abbreviated “DMV”, Dynamic Management Views are perhaps the most powerful feature of the SQL 2005+ line-up. No SQL Server release previously had the ability to allow the user to dynamically query the internals of the SQL Server while it was running. DMVs allow administrators to monitor the health of the SQL Server instance, diagnose problems and tune performance. If SQL Server were a human, DMVs would turn the skin transparent and allow you to see the pumping heart and the entire blood flow.

Contrary to older releases of SQL Server, they are not views based off the system tables, which would require that system tables are regularly updated and then queried, which would mean the introduction of performance issues. DMVs are such that they report off the underlying metadata, which make them faster and more accurate or “real-time”.

Dynamic Management Functions (DMF) are also available, which represent the internal state of the SQL Server as a function of the input provided. DMVs and DMFs are together known as Dynamic Management Objects.

Two types of Dynamic Management Objects are available to the users:

1. Server scoped dynamic management views, which require VIEW SERVER STATE permission on the server
2. Database scoped dynamic management views, which require VIEW DATABASE STATE permission on the database

Naming Conventions

Dynamic Management objects are always prefixed with dm_* and reside in the resource database (i.e. the sys schema).

DMVs can be used in T-SQL statements using two, three or four-part naming, just as is the case with a user table. DMFs, on the other hand can be referenced using two or three-part names. Dynamic Management objects cannot be referenced using a one-part name. (which means that you need to reference the DMV dm_exec_requests as sys.dm_exec_requests and not dm_exec_requests).

Securing Dynamic Management Objects

Dynamic Management Objects expose the internal state of any SQL Server. Therefore, they need to be controlled by user security and everyone cannot be provided access to them.

By default, in any Microsoft product, the paradigm is “secure by default”. Keeping this in mind, for SQL Server, the DENY would always take precedence. As discussed earlier, dynamic management objects need VIEW STATE permissions on the server and the database. Hence, all the administrators need to do is DENY the VIEW STATE permissions to wherever required (if server-level access is not required, DENY on the server, but allow on the database or, do the reverse if only server-level access is to be provided.

To know more about dynamic management objects, please visit:http://msdn.microsoft.com/en-us/library/ms188754.aspx

Where to find these in the Object Explorer

Dynamic Management Objects are, as mentioned, prefixed with the sys schema, indicating that they are members of the resource database. But, the resource database is hidden. Does this mean that the dynamic management objects are also hidden? No.

SSMS is a very powerful tool, and we use it so much that we almost take it for granted. The Object Explorer is exactly what it’s name suggests – allows you to explore all SQL Server objects – system or user defined.

Hence, to find dynamic management objects, all we need to do is to expand the master database in the Object Explorer and navigate out to the Views->System Views node.

Next, right click on the Views and filter the views listing for views belonging to the sys schema and objects containing dm_ in their name. To learn more about filtering objects in the Object Explorer, read my previous post in the Underappreciated Features series here.

Expand the filtered list and you can see all the DMVs listed for your convenience.

You can follow the same process to filter and look for the programmability components (dynamic management functions) in the master database.

Some key differences

It is important to understand that Catalog views are views based on the underlying system tables. Because data in tables is permanently persisted, the values in the catalog views are not reset upon server instance restart.

Dynamic objects, on the other hand report directly off the meta-data in memory, which means that they report data since the last SQL Server restart. This is a very important difference to keep in mind when working with catalog views and dynamic objects.

In Conclusion

Microsoft SQL Server, with each passing release is making increasing the richness and use of Dynamic Management objects and catalog views. I would request all readers to explore these powerful objects and make working with SQL Server a pleasurable experience.

Until we meet next time,

Be courteous. Drive responsibly.

Today’s post is part of the series that I am running under the title "Under Appreciated Features of SQL Server". The series has been inspired by Andy Warren’s editorial on SQLServerCentral.com.

How often have you faced a situation wherein while the host operating system is perfectly operational, but the SQL Server simply fails to respond to your connection requests? Starting SQL Server 2005, Microsoft provides a “reserve” dedicated administrator connection (DAC) – which is a special diagnostic connection for administrators when standard connections to the server are not possible.

Today, I will share with you some of the important points that I had captured when I first learnt about it in SQL Server 2005.

Connecting to Microsoft SQL Server using DAC

There can be only one connection to the SQL Server under dedicated administration – this is primarily to conserve the minimal resources which have been kept aside for these troubleshooting exercises. This means that it is always recommended to use DAC using the sqlcmd mode rather than the SQL Server Management Studio (where one may open up other connections accidentally – refer my post The multiple connections of SSMS to learn how).

Connecting via SQL Server Management Studio

In order to connect to a Microsoft SQL Server under the dedicated admin connection using SQL Server Management Studio, all that needs to be done is:

1. In the SQL Server Management Studio, click on “Database Engine Query”
2. Add the “ADMIN:” before the server name
3. Use the credentials of a user who is a member of the sysadmin fixed server role, and then click “Connect”

Connecting via SQLCMD

As I mentioned earlier, connecting using DAC is slightly tricky when using SQL Server Management Studio because the management studio can open multiple connections to the SQL Server if not used with care, and that would mean that our one DAC connection might be used up somewhere else accidentally.

A better alternate is to connect using DAC via the SQLCMD. To do so, the SQLCMD statement would look like:

sqlcmd  -S VPCW2K8DENALI -E -A -d master

The –A parameter indicates our intention to use the dedicated administrator connection.

What port does DAC use?

The reason an administrator would want to use DAC is because the SQL Server doesn’t respond to regular queries. Which also means that SQL Server is somehow not responding to the regular port that is used for communicating to SQL Server (typically, 1433). Therefore, DAC should have been implemented to use a different port. And that is exactly what has been done – with the additional security feature of the port changing every time SQL Server restarts.

Whenever Microsoft SQL Server restarts, an entry is added to the SQL Server error log indicating the port# in use by DAC until the next restart of Microsoft SQL Server.

Can I connect using DAC from anywhere?

Remote administration is a great thing, and mandatory in the days of virtualization and data centers. However, this also makes us all inclined to reject a feature if it cannot be used remotely. Similar is the case with DAC.

By default, DAC can only be used on the machine running the SQL Server instance. To run DAC from a remote machine, run the following advanced configuration option against your server:

/****************************************************
             !!!WARNING!!!
This script will modify the security configuration
of your SQL Server. Please inform your administrator
before use.

THIS SCRIPT IS SUPPLIED "AS-IS" AND WITHOUT
WARRANTY FOR DEMONSTRATION PURPOSES ONLY.
****************************************************/
--Enable display of advanced options
sp_configure 'show advanced options',1
RECONFIGURE
GO
--Turn ON Remote Admin Connections (DAC)
sp_configure 'remote admin connections',1
RECONFIGURE
GO
--Disable display of advanced options
sp_configure 'show advanced options',0
RECONFIGURE
GO

DAC and SQL Server Express Editions

To the best of my knowledge, DAC is not available by default on SQL Server Express Editions. Per Books On-Line (http://msdn.microsoft.com/en-us/library/ms188396.aspx) one can start SQL Server using trace flag 7806 (add –T7806 to the startup parameters) to enable DAC on express editions, but I have not given it a try myself.

Words of caution:

Dedicated Admin Connection, is a diagnostic and troubleshooting tool. It is not a security feature or replacement for administrative privileges, and hence must not be accessible to all. At the end of the day, it is a backdoor into the SQL Server instance, and therefore, must be used with extreme caution.

Also, it is to be kept in mind that while DAC allows you to recover from most scenarios, it may not work every time, especially, when the SQL Server is heavily constrained on the resources that can be spared for the DAC.

A good reason to have master as the default database

By the way, we may have a practice of assigning a user database as the default database for a login. If for some reason, this database is offline or not available, we will land up in an error – 4060. Because master is guaranteed to be available if the SQL Server instance is running, it is always recommended to have master as the default database for users belonging to the sysadmin role.

Until we meet next time,

Be courteous. Drive responsibly.