Earlier, I wrote a post about how adding the Application Name and the Workstation ID in a connection string are recommended best practices because these would help system administrators troubleshoot problems faster by being able to identify which application and which workstation actually initiated a SQL Server connection. In most teams, the connection string is constructed and provided to the development team by the administrators/DBAs and therefore, as soon as the post was up, I received a couple of questions from these DBAs, one of which included:

Is there a way to test connection strings before an application is built to consume the connection string?

This is a very interesting question. A connection string has many different parameters which can influence the overall behaviour of the application and therefore, testing the connection strings before actually using them makes complete sense. In today’s post, I explore the answer to this question.

Using the “Connect To…” window of SSMS to test connection strings

If you work frequently with the SQL Server Management Studio (Tutorial on “Getting Started with SSMS”), you would notice that one of the first windows that the user encounters is the “Connect To” window. By default, the window looks similar to the screenshot shown below and allows the user to choose the SQL Server instance to connect to, the mode of the connection (windows authentication/SQL Server authentication) and the user credentials.

This window collects what is the “bare-minimum” information to establish a connection. Clicking on the “Options” button exposes a window that enables the user to tweak the connection parameters in greater detail. As you can see, the window consists of three tabs:

• Login
  
• Connection Properties
  
• Additional Connection Parameters

Login Tab: This is essentially the same as the compact version of the window.

Connection Properties Tab: Allows a user to: Define an initial catalog/database Modify the network protocol to be used and choose the network packet size Modify the timeout parameters

Additional Connection Parameters: This allows a user to free-form type connection string options that are not available on the UI (for example, the Application Name and the Workstation Id) NOTE: Please note that any information entered here would override the information specified in the UI on the other tabs of the “Connect To…” window.

When preparing the demonstrations shown in my earlier post on connection strings, I used these options to test out the changes and tweaks to the connection strings. In a similar way, administrators/DBAs can now use the SSMS to test out their connection strings before they hand them over to the application development teams.

I trust you found this feature of the SSMS useful – I use it on a regular basis, and would like to know if you have ever used it. Do drop in a line as you go.

Until we meet next time,

Be courteous. Drive responsibly.

The glue that holds the connection between an application and a SQL Server instance is just a simple string, called the “connection string”. The connection string serves a purpose much greater than simply connecting the two together. Connecting Strings have the power to influence:

• Application Security
  
• Application behaviour
  
• Help in auditing and troubleshooting

To most developers, a simple ADO connecting string would be something like:

Provider=SQLNCLI10;Data Source=WINDOWS8RPSQL2K12;Initial Catalog=AdventureWorks2012;User ID=someuser;Password=userpassword

If we try to highlight the different parts of the connection string, we see that we know the following:

• the library to use for the connection
  
• the data source (i.e. the instance name) to connect to
  
• the database to use by default once the connection has been established and,
  
• the credentials for a successful SQL authentication

All of the above constitute required information, and includes all the essential information about the SQL Server instance. What it does not have is the information about the calling application. To the SQL Server, the originator of the request is essentially unknown. The request could have come from almost anywhere and it would have honoured the request provided the details were correct.

Not only is this bad from a security standpoint, it is also not recommended from an auditing and troubleshooting perspective also. Let’s just see an example of this situation.

A small demo

For this demo, I launched multiple query editors within the SQL Server Management Studio, connected to the same instance of SQL Server. I then executed the same T-SQL query from these query editor windows, and attempt to identify each connection using the SQL Server Profiler (Refer tutorial here).

You can also develop a test application and run multiple instances of the application to observe a similar behaviour.

Now, we know that each query editor will open it’s own connection to the SQL Server. Therefore if the associated SPID is known, auditing is not a difficult task. In production systems, this is not the case and therefore, an alternate approach becomes necessary.

The solution – Modify your connection string!

From an auditing and troubleshooting standpoint, it is therefore, always a good practice to include the application name of the calling application and the workstation Id of the workstation. For the ADO connection string shown above, a simple modification like the following would work wonders:

Provider=SQLNCLI10;Data Source=WINDOWS8RPSQL2K12;Initial Catalog=AdventureWorks2012;User ID=someuser;Password=userpassword;Application Name=”MyTestApp”;Workstation Id=”SSMS01”

Running the same test again shows us that the Profiler can now distinguish between the calls coming in via “MyTestApp” through it’s the various sessions (represented as the HostName) v/s the calls coming in from the SQL Server Management Studio itself.

Now, the SQL Server can be programmed to only log connections not originating from certain applications, or can be programmed to respond differently when the same database/stored procedure is being executed over different connections – the possibilities are endless!

It is therefore a best practice to also set the Application Name and Workstation Id as part of the connection string of your application.

To know more about connection strings and their anatomy

A couple of years ago, I wrote a piece on SQLServerCentral.com (Connection Strings 101). That article address, at length the various components of a connection string and the areas they influence. I also provide a consolidated list of connection string components for ADO, ODBC and OLE DB libraries to download!

May the power of the connection string be with you!

Until we meet next time,

Be courteous. Drive responsibly.

The SQL Server Management Studio is, in reality, a Visual Studio shell and therefore exposes to the SQL Server users, all the related usability and productivity features of what is perhaps the most powerful IDE of all times from the Microsoft stable. The SSMS for SQL Server 2008 used the VS2008 shell and therefore came with a lot of goodies, including Intellisense. The SSMS for SQL Server 2012 is powered by VS2010 shell and brings a lot of newer features and improvements to existing ones.

Completion Mode in Intellisense

All of us use slightly different programming practices – some of us use “TAB” to complete an object name shown by Intellisense, whereas others use “SPACE”. Both worked in the same way in the SSMS for SQL Server 2008.

However, when SQL Server 2012 was released, the SSMS did something different – SPACE no longer selected an object name! By default, one had to use the “TAB” key to complete the word. Allow me to demonstrate.

	SQL 2008 / SQL 2008 R2
	Before	After
Space
TAB
	SQL 2012
	Before	After
Space
TAB

As you can see, there is a marked difference in behaviour for the “completion mode” of the Intellisense in SSMS 2012. So, what’s going on? And for those who liked it the old way, how can we “fix” it?

Toggling the Completion Mode

The answer comes as a facility in the SSMS IDE to toggle the Completion mode. Go to the “Intellisense” option in the Edit menu and click on “Toggle Completion Mode”:

After toggling the completion mode, the Intellisense selection starts behaving in the same way as SSMS for SQL Server 2008. The functionality can also be achieved by using the key combination Ctrl + Alt + Space.

Known issues

The only known issue I find with “Toggle Completion Mode” is that it is session specific. Restarting SSMS resets the completion mode setting to default.

The related MS Connect Case is: http://connect.microsoft.com/SQLServer/feedback/details/686087/intellisense-completion-mode-does-not-persist. Unfortunately, it is marked as “Can’t Reproduce”. If you can reproduce it and would like to get Microsoft to fix it, please cast your vote accordingly on the Connect site.

References

• To know more about the features of SSMS, please refer the tutorial series, “Getting Started with SSMS”
• Madhivanan has written a nice post on the "Intelligent use of Intellisense" showing how Intellisense can be used to determine which objects are available based on the T-SQL statement context that one is in

Until we meet next time,

Be courteous. Drive responsibly.

Most of us (especially those working in close contact with development teams) are generally tasked with multiple roles and responsibilities – the most common combination being that of a database developer and a database administrator. In order to fulfill both responsibilities, I generally have both the query editor and the object explorer open for any server that I am connected to.

In some cases though, I connect to a server to execute a query or two and then find my self in need to access the object explorer.  In such cases, I use a neat functionality hidden in the pop-up menu of the query editor, which I thought would benefit all this week. So, here are the steps.

Assume that you are working in the SSMS query editor window. 1. Right-click anywhere in the white space 2. Click on “Open Server in Object Explorer”

You can see that the Object Explorer is now up.

There is no need to explicitly go to the Object Explorer and use the “Connect” buttons. Alternatively, you can also use the keyboard shortcut Alt + F8.

Why would you want to use the Object Explorer only when required?

There’s a reason why I don’t prefer to launch object explorer by default for servers I don’t work with regularly – for every feature of the SSMS that one uses, an additional connection is opened with the SQL Server (Read more about this here: http://bit.ly/XE6fZh).

Until we meet next time,

Be courteous. Drive responsibly.

In my earlier post (Restart Recovery-Database Startup Sequence), I studied the overall database startup sequence as part of the restart recovery process for databases on a SQL Server instance. The conclusion that I came to was that the restart recovery is a multi-threaded activity and multiple spids are utilized to bring the SQL Server instance online in the shortest possible time frame. The first database to come online is (obviously) the master database followed by the rest of the system databases in sequence (resource, model, tempdb, msdb and Reporting Server related DBs). Finally the user databases are brought online.

The question that now came to my mind was that it is possible for an instance to have multiple user databases. What would be the startup sequence for these user databases under normal conditions?

On my test instance, I have 6 user databases according to this script to identify system v/s user databases:

--Provides a list of user databases
select sdb.database_id,
       sdb.name
from sys.databases sdb 
WHERE CAST((CASE WHEN SUBSTRING(sdb.name,0,14) IN ('master','msdb','model','tempdb','ReportServer$') 
                      THEN 1 
                 ELSE sdb.is_distributor 
            END) AS BIT) = 0

The user databases on my test instance are listed below:

To know the sequence in which these databases were brought online as part of the restart recovery sequence, I used the query from my pervious post with a minor modification in that I am filtering the log text:

--Check the sequence logged into the SQL Server Error Log
DECLARE @DatabaseStartupSequence TABLE (LogDate DATETIME, ProcessInfo VARCHAR(10), LogText VARCHAR(50))

INSERT INTO @DatabaseStartupSequence (LogDate, ProcessInfo, LogText)
EXEC sys.sp_readerrorlog 0, 1, 'Starting up database'

SELECT CAST(dss.LogDate AS TIME) AS LogTime, dss.ProcessInfo, dss.LogText
FROM @DatabaseStartupSequence AS dss
WHERE dss.LogText LIKE '%AdventureWorks%'
ORDER BY dss.LogDate ASC
GO

The results are as shown below:

Result

Multiple spids are assigned to recover the user databases in order of their database_id. SQL Server started 5 separate processes to recover 5 out of the 6 user database. The 6th database was handled by the first spid that completed it’s work based on whether the scheduler had capacity available.

In conclusion, restart recovery of system databases has a specific sequence that is followed by SQL Server, but when it comes to user databases, the order is governed by the database_id of the database.

This topic is by no means complete. There is a lot more to explore, so, if you have any related observations/questions, please do share them via the blog comments, or send them on my Twitter handle (@nakulv_sql).

Until we meet next time,

Be courteous. Drive responsibly.