Bad habits: Using AttachDBFileName

[See an index of all bad habits / best practices posts]

Just about every time I look through SQL Server-related questions on Stack Overflow, I see someone describing a problem that they are blaming on their code, but is likely caused by their connection string. The pattern is usually something like:

Management Studio is not showing me updated data from my application. Could this be some kind of caching issue, or an error that isn't being raised? Here's my code:
 
 
 
-- code that looks like a typical, working insert/update
-- *separate* code that looks like a typical, working select

While sometimes it can be because they are connected to the wrong database, and sometimes it can be because they are looking at the wrong copy of the table (maybe they forgot to specify the schema), and sometimes it can be a simple typo or an unnoticed filter, it is often due to a combination of connection settings. So I always ask to see the connection string before trying to debug code which may seem and actually be perfectly fine; when this is the reason, the connection string will look something like this (emphasis mine):

Data Source=.\SQLEXPRESS; AttachDBFilename=|DataDirectory|\db.mdf; User Instance=true; <...auth...>;

Using AttachDBFileName and User Instance means that SQL Server is creating a special copy of that database file for use by your program. If you have two different programs using that same connection string, they get two entirely different copies of the database. This leads to a *lot* of confusion, as people will test updating data with their program, then connect to a different copy of their database in Management Studio, and complain that their update isn’t working. This sends them through a flawed series of wild goose chase steps trying to troubleshoot the wrong problem. And there are many variations on the problem – a partial list:

• I deleted data in my application and it’s still there in SSMS
• I added a column in the application and it won’t show up in SSMS
• I added a user in SSMS but my application is still getting access denied
• I created a new table in Visual Studio and SSMS still can’t see it
• …and so on…

This article goes into more depth about how to use the user instances feature, but please heed the very first note (which goes all the way back to the 2008 version of the document):

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Another thing to be aware of: The logical database name if you just specify AttachDBFileName in Management Studio is the full path to the file. If you connect to a local instance (any edition), you can add additional connection parameters, including this option:

Connecting to an unattached MDF file

Observe how it looks in Management Studio’s Object Explorer and sys.databases (and notice how upper case has been forced):

AttachDBFileName in action: Object Explorer

AttachDBFileName in action: sys.databases

This means that in order to reference the database, you need to say things like:

ALTER DATABASE

  [C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL12.SQL2014\MSSQL\DATA\OLD\DBFILESAMPLE.MDF]

  SET RECOVERY SIMPLE;

You can fix this, of course, it’s just an additional barrier to getting started and/or using existing scripts that reference a proper, logical database name:

ALTER DATABASE 
  [C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL12.SQL2014\MSSQL\DATA\OLD\DBFILESAMPLE.MDF] 
  MODIFY NAME = SimplerName;

In SQL Server 2012 and above, the preferred alternatives for local development are (in this order, IMHO):

1. Create or attach your database to a real instance of SQL Server. Your connection string will then just need to specify the instance name, the database name, and credentials. There will be no mixup, as Management Studio, Visual Studio and your program(s) will all be connecting to the same, single copy of the database.
2. Use the SqlLocalDb feature for local development. I wrote an article on this: “Getting Started with SQL Server 2012 Express LocalDB.” (It should be just as relevant for the 2014 version.) There are also some interesting tidbits in this blog post by Jerry Nixon and this blog post by Krzysztof Kozielczyk.

In the past I might have recommended using SQL Server Compact Edition, but there are several reasons I don’t like this, including the fact that the functionality and syntax is not the same as “proper” SQL Server. There’s also the fact that Compact Edition is deprecated, too.

Of course if you are using a version < SQL Server 2012, or you need to peer develop on the same database with multiple users, SqlLocalDb is not an option – so you should be creating a real database on an accessible instance and using that consistently. The connection string above would change to reference the logical name of the database instead of its physical location:

Data Source=.\SQLEXPRESS; Initial Catalog=mydb; <…auth…>;

(Your server/instance name may be different, of course; I just find this issue most common with the standard, local \SQLEXPRESS named instance.)

[See an index of all bad habits / best practices posts]

Aaron Bertrand

Aaron (@AaronBertrand) is a Data Platform MVP with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com.

Aaron’s blog focuses on T-SQL bad habits and best practices, as well as coverage of updates and new features in Plan Explorer, SentryOne, and SQL Server.