none
How to use SQL Server Express effectively in development environment?

    Question

  • Hi all,

    I must be missing something obvious. I'm not clear on how SQL Express can provide a decent development experience. Consider:

    1. I have a class library that includes an express database file, set to always copy to output folder.
    2. I have code that attaches to the database in the output folder at runtime.

    As a result of the above, the first execution after restarting SQL Server will work. Subsequent builds will fail because SQL Server has a lock on the database file in the output folder. Or one of the two files will be copied (DB and log) and therefore they will be out of sync and detected as corrupt when the code attempts to connect. Either way, I have to restart SQL Server and rebuild before the DB connectivity will work again.

    I figured I might need to detach from the DB in my shutdown code, but I cannot execute sp_detach_db from my code because it cannot be done through a connection to the database (database is in use). Moreover, I'm not sure I want to because the user might execute multiple instances of the application.

    What am I missing here? Do I need to put steps in my build script to stop and start the local SQL Server instance?

    Thanks for any help,
    Kent
    Thursday, January 31, 2008 11:19 AM

Answers

  • Hi Kent,

     

    The problems you are having with needing to detach are caused by the fact that you're naming the database as part of the connection string. (Database=Cache). You should not be doing this. For user Instances you should allow them to be Autonamed as part of the attach process specified by using the AttachDbFilename keyword. By specifying AttachDbFilename you are telling SQL Express to use the following logic on connection:

    1. Check if the database specified by the file path is already attached. If it is, just use the attached database.
    2. If it is not attached, attached it and give it a name.

    By naming the database yourself you are overriding the logic that we designed to allow deployment of embedded database to work correctly. I know for a fact your connection string will break in ClickOnce deployment, and there are likely other places where it will as well. Trust me, I've spent a fair amount of time troubleshooting an application where I did this same thing and it causes all kinds of problems.

     

    Take the database name out and you shouldn't have to mess with detach anymore.

     

    As far as updates to the database in your project after initial deployment, those are difficult as VS is just not design to handle them. The best way is to work out a way to apply update scripts to your database as part of your program. When you get to that point there are some samples that can help. I describe an update scripting soluton in my blog here. Since then the sample code has been refined by one of my co-workers, so I recomend that you use the sample project that is here instead of the one I've posted. The updated project uses the same general principles, but has a cleaner implementation.

     

    Regards,

    Mike

    Friday, February 01, 2008 4:24 PM
    Moderator

All replies

  • Hello,

     

    Except if i'm making an error, i think you are knowing the pleasures of the User Instance. That's pretty but i discover that can't be used easy in real world.

    It may be useful when you want to learn SQL Server and VC# or VB. As everything about databases is automaticaly created by Visual Studio, you don't spend time to understand what's happening and you are hurting with a wall

    See these links :

    http://msdn2.microsoft.com/en-us/library/ms143684.aspx

    http://msdn2.microsoft.com/en-us/library/bb264564.aspx

    http://msdn2.microsoft.com/en-us/library/ms254504.aspx

     

    Don't forget that with user instance , the SQL Server Express uses the autoclose option ( the connection is automatically closed after a certain time so you are loosing the connection )

     

    I'm using SQL Express as a "classical" SQL Server :

    - no user instance

    - no autoclose option

    - when i'm developping a program, i check that it works with local and remote connections ( especially in a Workgroup network ). If it's working, i know it will have the same behaviour through a domain network ( controlled par a Windows Server )

    - i know there are limitations but for the applications i'm developping, they are unpointless

     

    I hope that with these links , you will be able to correct very quickly your problem

    For me i never use attaching databases.

    I prefer to create database by program ( with SqlCommand and execution of a script ).

     

    I'm programing from 28 years and i know that the use of auto-generated code like with VC#/VB Express forbids you to think what you are doing really.

     

    Have a nice day

    Thursday, January 31, 2008 6:07 PM
    Moderator
  • hi Kent,

    I'm not a User Instance fan nor do I use it.. but I think you probably should modify the "always copy to output folder" property as required... just "copy back" the modified database if you think the test data you are using in your dev expirience is vital, else keep the "original" db... as you know how it works, you can live with it understanding it's behaviour..

    just my $0.02 ...

    regards

    Thursday, January 31, 2008 6:19 PM
    Moderator
  • Hi Kent,

     

    The behavior you describe is actually contrary to how User Instances work in the developer envinronment, which suggests that there is another layer deeper in your code logic that is working against you. For my money, the culprit lies in your statement "I have code that attaches to the databse in the output folder at runtime" which depending on how you're doing that is likely the issue. Could you provide a sample of to code you use to do this and connection string that is being employed?

     

    In general, User Instances actually work quite well in the development environment with the exception of a "missing data" problem caused by the fact that VS treats the database just like any other project file and puts a new copy into the output directory every time you Debug. I've blogged about this here. Otherwise, user instances automatically handle attach of the database and shutdown of the instance, so you're code would be redundent in that case.

     

    Let's take a look at the code and start from there.

     

    Regards,

    Mike

     

    Friday, February 01, 2008 12:00 AM
    Moderator
  • Thanks for the comments all - they've been very helpful.

    I wasn't using User Instance but now I am after reading more about it. My use case might explain why: I'm using a local SQL Express DB to store cached data for a rich client. Nothing more is stored in there, and there is no "default" data that is being overwritten. I don't want to require full trust, which was the main motivation for changing to User Instance.

    FWIW, my connection string is now:

    Code Snippet

    Data Source=.\SQLExpress;AttachDbFilename=|DataDirectory|\Caching\Cache.mdf;Database=Cache;Trusted_Connection=Yes;User Instance=True


    The problem with file access remains, but I have a better understanding of it now. Setting the copy behaviour to Copy if newer mitigates the issue, but any changes to the "master" DB file will require the instance be detached before building.

    One way around this would be to implement a custom MSBuild task that:
     1. Checks if the DB has changed and needs to be copied
     2. If so, detaches the instance
     3. Copies the files

    However, this may be overkill because once the schema settles down, this will rarely be an issue. After switching to a User Instance setup, I used the SQL Express Utility to detach before builds, and that was workable for me.

    If anyone has further insight, I'd love to hear it.

    Thanks again,
    Kent
    Friday, February 01, 2008 11:20 AM
  • Hi Kent,

     

    The problems you are having with needing to detach are caused by the fact that you're naming the database as part of the connection string. (Database=Cache). You should not be doing this. For user Instances you should allow them to be Autonamed as part of the attach process specified by using the AttachDbFilename keyword. By specifying AttachDbFilename you are telling SQL Express to use the following logic on connection:

    1. Check if the database specified by the file path is already attached. If it is, just use the attached database.
    2. If it is not attached, attached it and give it a name.

    By naming the database yourself you are overriding the logic that we designed to allow deployment of embedded database to work correctly. I know for a fact your connection string will break in ClickOnce deployment, and there are likely other places where it will as well. Trust me, I've spent a fair amount of time troubleshooting an application where I did this same thing and it causes all kinds of problems.

     

    Take the database name out and you shouldn't have to mess with detach anymore.

     

    As far as updates to the database in your project after initial deployment, those are difficult as VS is just not design to handle them. The best way is to work out a way to apply update scripts to your database as part of your program. When you get to that point there are some samples that can help. I describe an update scripting soluton in my blog here. Since then the sample code has been refined by one of my co-workers, so I recomend that you use the sample project that is here instead of the one I've posted. The updated project uses the same general principles, but has a cleaner implementation.

     

    Regards,

    Mike

    Friday, February 01, 2008 4:24 PM
    Moderator
  • Ah, I see. Thanks Mike, that works - manual detaching is no longer required.

    Sunday, February 03, 2008 12:29 PM