what’s the issue with AttachDbFilename

by

Using 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.

This article goes into more depth about how to use this feature, but heed the very first note: the User Instance feature has been deprecated. In SQL Server 2012, the preferred alternatives 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 a single copy of the database.

  2. Use a container for local development. Here’s a great starter video by Anna Hoffman and Anthony Nocentino, and I have some other resources here, here, and here. If you’re on an M1 Mac, you won’t be able to use a full-blown SQL Server instance, but you can use Azure SQL Edge if you can get by with most SQL Server functionality (the omissions are enumerated here).

  3. Use SqlLocalDb for local development. I believe I pointed you to this article yesterday: “Getting Started with SQL Server 2012 Express LocalDB.”

  4. Use SQL Server Compact. I like this option the least because the functionality and syntax is not the same – so it’s not necessarily going to provide you with all the functionality you’re ultimately going to want to deploy. Compact Edition is also deprecated, so there’s that.

Of course if you are using a version < SQL Server 2012, SqlLocalDb is not an option – so you should be creating a real database and using that consistently. I only mention the Compact option for completeness – I think that can be almost as bad an idea as using AttachDbFileName.

EDIT: I’ve blogged about this here:

More Related Contents:

  1. How can I force entity framework to insert identity columns?
  2. A network-related or instance-specific error occurred while establishing a connection to SQL Server [closed]
  3. Why does the 2nd T-SQL query run much faster than the first when called by Reporting Services 2005 in a web-app
  4. Preventing SQL Injection in ASP.Net
  5. Streaming Databased Images Using HttpHandler
  6. Can’t find microsoft.sqlserver.batchparser.dll
  7. What’s the best way to display an image from a sql server database in asp.net?
  8. What is ASP.NET Identity’s IUserSecurityStampStore interface?
  9. What is the difference between ExecuteScalar, ExecuteReader and ExecuteNonQuery?
  10. Has anyone implement RadioButtonListFor for ASP.NET MVC?
  11. How to increase request timeout in IIS?
  12. How do I Validate the File Type of a File Upload?
  13. asp.net – session – multiple browser tabs – different sessions?
  14. Is there a way to automatically include content files into asp.net project file?
  15. Passing FormsAuthentication cookie to a WCF service
  16. Applying Styles To ListItems in CheckBoxList
  17. Can I incorporate both SignalR and a RESTful API?
  18. What are the pros and cons of running IIS as 32bit vs 64bit on a 64bit OS?
  19. How to deploy ASP.NET MVC 4 application using localDB to local IIS on Windows 7?
  20. Allow anonymous authentication for a single folder in web.config?
  21. how to access master page control from content page
  22. Page_ClientValidate is validating multiple times.
  23. Conditionally hide CommandField or ButtonField in Gridview
  24. Streaming large file uploads to ASP.NET MVC
  25. set cookie to expire at end of session? asp.net
  26. Cache VS Session VS cookies?
  27. What is an HttpHandler in ASP.NET
  28. Asp.Net MVC3, returning success JsonResult
  29. IIS AAR – URL Rewrite for reverse proxy – how to send HTTP_HOST
  30. Default redirect for Error 404

Leave a Comment