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.

The startup process of any system is a very interesting stage. Ever since I was exposed to the world of computers, I was fascinated with the POST (Power On Self Test) sequence, about which I learnt and explored in detail when I studied Electronics & Communications engineering. SQL Server is a server system on it’s own and therefore, I recently had a yearning for learning about the SQL Server startup sequence.

Now, I know that the SQL Server startup process involves an Undo-Redo phase for rolling forward/backward open transactions in each database, but the questions that kept cropping up in my mind were:

1. When the SQL Server starts, what is the first database that is made online?
   
2. When are user databases made available in the startup sequence?
   
3. When is the tempdb available?

In order to answer these questions, I postulated a theory and then tried to collaborate it with practice. The entire experience is documented in this post.

The Theory

When I started learning about SQL Server, I thought that the database_id determines when a database comes online. This would mean that the following DMV would be the only thing tat one needs to be query:

USE master

GO

–Get the database Ids

SELECT sd.database_id, sd.name

FROM sys.databases AS sd

ORDER BY sd.database_id

GO

My test server is a SQL Server 2012 instance with reporting services installed and the AdventureWorks2012 sample database deployed. The result for this test instance is shown below:

The one database that is not listed in the results of this DMV is the resource database.

Going by original hypothesis, the master should be started up first, followed by the resource, tempdb, model, msdb and so on.

The contradiction

Contradicting my thought process mentioned above were some of my pervious observations around tempdb. It is a confirmed point thing that the model database has to be online before the tempdb is made available.

• TempDB – Is it a copy of the Model database? – http://bit.ly/T6Ikjq
  
• TempDB v/s Model database – Minimum size considerations – CREATE DATABASE – Error Msg 1803 – http://bit.ly/RMAMPq

The above contradiction led me to hypothesize that the startup sequence would be similar to master –> resource->msdb –> model –> tempdb –> ReportServer –> ReportServerTempDB –> user databases. It was now time to confirm this line of thought.

The Test

To identify the real order in which databases are brought online, I looked at the SQL Server error log where all startup activity is logged by Microsoft SQL Server. I had referenced this in one of my pervious post related to identification of the startup time of the SQL server. To read the SQL Server error log through T-SQL, we use a system stored procedure sys.sp_readerrorlog (undocumented, to the best of my knowledge).  This system stored procedure takes up to 4 parameters:

• Error log file to read (0 = Current, 1 = Archive #1, and so on)
  
• Log File Type (NULL/1 = SQL Server Error log, 2 = SQL Agent Log)
  
• String 1 (first search string)
  
• String 2 (string to further refine the search)

We need to read the current file in the SQL Server Error Log, and only need to search for one string – “Starting up”. The call would therefore be similar to the one shown below, where I have captured the results into a table variable which allows us to sort by the log time.

–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

ORDER BY dss.LogDate ASC

GO

The results of the query execution are as shown below:

The Result

The above is a very interesting result and tells me that I was not very wrong in my hypothesis. Here’s the interpretation of the log content.

• We know that recovery is a parallel process – this is confirmed by the fact that we have multiple ProcessIds in the results above
  
• The first database is recovered by process Id – 8 and is the master database – without the master database, the entire SQL Server fails to start
  
• The next database recovered is by process Id – 9 and is the resource database (“mssqlsystemresource”)
  
  
  
  • This very process goes on to recover, in sequence, the model and then the tempdb database
  
  
• The next process that SQL Server initiated was Id – 17 for recovers the msdb database
  
• Process Id – 18 then recovers the ReportServer database followed by Process Id – 19 which recovers the ReportServerTempDB
  
• Finally, process Id – 20 recovers the user database – AdventureWorks

The recovery interval (i.e. when the recovery for any database completes) depends on a number of factors and can be studied at: Understanding Recovery Performance in SQL Server.

Conclusion

In conclusion, I could now answer all my questions:

1. When the SQL Server starts, what is the first database that is made online? Answer: Master
   
2. When are user databases made available in the startup sequence? Answer: After all system databases have been recovered
   
3. When is the tempdb available? Answer: After the master, resource and model databases are recovered (in that sequence)

I learnt something new today, which is always fun. 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.