SSE and Visual Studio ** Xcopy Mindshift **

Adding databases with SSE (SQL Server Express) to Visual Studio

At the announcment for Visual Studio and SSE, they talked about the Xcopy functionality.  I should explain a bit about what this means in practical terms for Visual Studio.   Most everyone is used to creating a new database by going to “Add new database“ from the Server Explorer.  This approach will still work. (Although you need to use the instance name of SQLExpress. [So, type “.\SQLExpress“ for the server name.] and there is a bug in the WebExpress SKU where this doesn't work. But, it does work in the other SKUs]) 

   In fact, if you see something like this:

   “Visual Web Developer 2005 Express Edition Beta, This feature is not supported
   by Microsoft Visual Web Developer Express“

   it's probably because you didn't specify the “.\SQLExpress” instance name.

But, this isn't the preferred way.  The preferred way is to simply copy the database into your project.  So, from the project menu, right click and choose “Add new item“ or “Add existing item“.  In either case the MDF (and LDF if present) is copied into your project.  Then a connection is automatically opened for you in Server Explorer.  So, you can directly party on the database structure.

SSE supports a connection style where you do not specify the logical database name.  You simply leave it blank in the connection string and one is generated for you automatically on the fly (based on the unique path of the MDF in the directory structure).  When your app closes down (app domain), the MDF is auto-closed which leaves the MDF in a copyable state. 

So, you can build your app and directly reference the MDF that will be in the same directory as your .exe.  When you hit F5, the project system builds your .exe/dlls and copies the MDF to the output directory.  When your app runs, it directly references the MDF that is in same directory as your exe (or relative to it).  When your app spins down and the app domain closes, both your executables and your database are Xcopyable (mailable, zippable, whatever ...) to the destination of your choice.

Note that when you debug, the same thing happens.   The exes are built in the debug directory and the MDF is copied to the debug directory as well.  So, when you are debugging your app, it's hitting the version of the MDF that's in the debug directory.  There is an option in the project system to *not* keep copying the MDF to the output dir each time that you can configure.  But, you need it there at least on the first build so that the app can reference it as it will when it's deployed.

When you deploy, you have two options.  1.) You can Xcopy.  And, you should Xcopy and pick up both the MDF and LDF files.  The VS project system does some work to manage the LDF for you automatically behind the scenes.  and 2.) you can use the explicit deploy features.  Note that you have the option of bundling up both the framework and SSE with your application.

Anyway, thought I'd better get this out on a blog so that people can see how to use SSE in the “file style“ format and so that they know about the SQLExpress instance name.



Comments (23)

  1. ScottWater says:

    Very nice post explaining the x-copy options in SQL Server Express

  2. Euan Garden says:

    Yet more Express info, the tools view

  3. Sean Malloy says:

    what happens when you xcopy an application across to a machine that doesn’t have Sql Server Express (or its big brother) installed?

    I’m guessing the app dies a horrible death with a Database/DSN not found type error?

  4. Ronny Ong says:

    Sean, the Books On-Line for SQL Express explains that XCopy deployment requires SQL Express to already be installed. It’s somewhat similar to the position Microsoft takes with the .NET Framework and MDAC: even though you can redistribute those (and even wrap their installers inside your own) Microsoft considers them to be OS/platform updates and thus recommends they be handled like any other OS update, rather than treating it as part of your app’s installation. Remember a few years ago when it was fashionable for everybody from Intuit to Symantec to include IE on their product CDs? Finally, many have come to their senses (or simply ran short of CD space) and now they just state in the System Requirements that you need to already have IE 6.0 or whatever. Did you know that there have actually been security vulnerabilities found in Acrobat Reader (the read-only reader, not even the full product!)? This means that all those old acroread.exe files you’ve got sitting on CDs everywhere are dangerous to install. Anything pervasive and shared between apps really should be installed/updated globally instead of being tied to app installs.

  5. Ronny Ong says:

    XCopy deployment for MSDE would have been interesting 3 years ago. Now it’s like the way the VB .NET team brought back Edit-n-Continue, right after VB .NET programmers got used to not having it. Those of us who truly needed to streamline MSDE-based app deployment to deal with high-volume, remote, and/or locked-down deployment scenarios already figured out how to take care of it ourselves. Outside of ISVs, I think a relatively limited percentage of corporate developers ever truly deploy MSDE-based apps to more than 1-2 machines at a time, so it’s not that burdensome for them to tinker with it until it works. I know that sounds dumb, but why do you suppose the majority of network admins in the world still install Office from CDs or flat copies of CDs instead of using vastly more efficient administrative install points, transform files, and group policy? Because it takes less thinking to grab a CD and surf the web like it spins. Off the top of your head, how many option switches for the XCopy command do you really know? Can you even explain the /z option after looking it up? Do you know which switches are supported under Win9X in protected mode (XCopy32) but not in real mode? In truth, the perception that XCopy = easy is a fallacy.

    The biggest gotcha to this XCopy approach is that you’re putting data in the same directory as your exe. This violates best practices, as well as Microsoft’s own Windows Logo guidelines established in 2000. By default, on NTFS volumes, ordinary Users do not have write permissions to anything under Program Files. This is one obstacle to regular Users installing software in a managed, secured environment. Putting data with code often encourages people to share the directory, often with the default permissions Everyone-Full Control, exposing code to tampering or virus infection. Segregating data from code makes it easier to ensure that backup and disaster recovery plans have included everything they need to, without needlessly backing up static code every day/week.

    It is true that a lot of apps were fond of keeping their data in the same directory hierarchy under Program Files but the safe way to do it is to have separate subdirectories. That’s why SQL Server has the MSSQLBACKUP, MSSQLDATA, and MSSQLLOG directories separate from MSSQLBinn. It’s also why Exchange Server keeps MDBDATA separate and exclusively for data, even though it’s under the Program FilesExchsrvr tree. It’s easy to set, verify, and audit permissions on specific subdirectories.

    Contrast this with apps like FrontRange GoldMine (and many old-fashioned xBASE/DBF based apps which kept data files right alongside the exe files). People used to commonly get confused as to why the DBF versions of GoldMine 4.x and 5.x wouldn’t work for non-Administrator users. The bad part is: they often "solved" the problem by making every user an Administrator, all the time. Thanks for blaming Windows for your security problems, folks! Might as well leave your sa password blank.

    The notion of "XCopy deployment" when it was first brought up in 2002 for ASP .NET apps is more meaningful because there’s no data involved. The closest you might come is having a web.config file which changes often, but that’s a tiny bit of text-oriented metadata which can easily be protected.

  6. Ming Chen says:

    A database product to allow Application to enable the "XCopy" secnario may be a gold mine to one but trash to the other.

    "XCopy" is just a term (probably not a good one), don’t limit your imagination on just the local machine. "XCopy" also mean that it is "downloadable", "moveable", "disconnectable"….

    As we approach to information explosion stage. Many application secnario is no longer limit to local machine only.

    I can give you the one case study: the following link is to a Fidlity Active Traider program provide a greate service to all their customer a program which can *almost* *real-time* to monitor the invester’s account stock, fund, watchlist and etc.

    To achieve this goal, consider most customer have only modem, DSL, Cable modem at from home. The issue is to constantly downloading a tons of data to the client machine become unpractical. Second, a "typical" investor will only constantly track limit amount of stock/fund/watch list they currently care. Which make the data a invester need become pretty local.

    For example, if he/she invest at MSFT stock, today he logon, he will see MSFT 4 month of stock movement information. The next day he/she login again, he still want to see 4 month accmulate stock information. What the difference between the yesterday’s MSFT 4 months stock informtion vs today MSFT 4 months stock information — it should only need 1 more day informtions not to download everything again just to jam up the bandwidth. This is not only apply to stock information, but also the customer’s investment profile, investment history,etc.

    Additionally The customer Account information, invest profile, investment history, etc.. should be store "securly" and only one person should can see and use it.

    In addition. customer like to have a crash proof database that can hold such information locally (say even if he had problem to connect to internet) he can still see his all investment information up to last download.

    Only he should have the access to this "personal" data…

    All lead to the same direction, you need a "downloadable" database (see the download button on the above link?) which will achieve all the requirement analysis above here.

    So the Sever side feed the inforamtion, client cache up on the "local" personal database with great security and only download on daily basis.

    As I mention before, "XCopy" is just a term, don’t limit your imagination on just the local machine. "XCopy" also mean that it can be "downloadable", "moveable", "disconnectable"…. As the example here for a web application that it can be a client side assistant. That’s what Lance point out — the "mind shift" needed to open lot of possibility…..

    – my 2 + 10 cents


    PS. Highly recommand Fidelity Active Trader program. I use it and love it. If you are a Fidelity customer but doesn’t have it — you miss a great service they have.

  7. Ming Chen says:

    Sorry, the correct link To Fidelity Active Traider is


  8. Lance Delano says:

    The Xcopy scenarios are very useful for the quick start scenarios. Those who are just learning and those who want something quick and dirty. The Xcopy connection style is useful well beyond these scenarios as well. In Visual Studio, it’s a "no think" deployment scenario. Just add new or existing items to your project and you’re good to go. The connection is added to your app, and the connection is ready in Server Explorer.

    In general, Xcopy will take less thinking time to use, period. The user doesn’t even need to see the connection string. (Note: SSE is not supported on Win9x)

    Also, the MDF is copied, by default, to the same dir as the .exe. However, it need only be in a dir that is specifiable relative to the .exe. (in which case you’d specify the relative path in the connection string) So, in the case of webapps for instance, they actually do write to a DATA dir that is below the .exe. But, Xcopy works the same with web as well.

  9. Wayne says:

    Thank you! Thank you! I have spent several days now trying to find this information, and you put it in a concise, easy to understand terms. It is exactly what I needed to know.

  10. Jorriss.Net says:

    The one that explains the XCopy options in SQL Server Express.

Skip to main content