Introducing LocalDB, an improved SQL Express


Updated 2011-11-28: Added reference to the walkthrough of using LocalDB in Visual Studio 2010 and to the new LocalDB Installer.

Updated 2011-11-02: Added reference to .NET Framework 4 support for LocalDB in the Q&A section.

Introduction

It gives me great pleasure to introduce a new version of SQL Express called SQL Express LocalDB.

LocalDB is created specifically for developers. It is very easy to install and requires no management, yet it offers the same T-SQL language, programming surface and client-side providers as the regular SQL Server Express. In effect the developers that target SQL Server no longer have to install and manage a full instance of SQL Server Express on their laptops and other development machines. Moreover, if the simplicity (and limitations) of LocalDB fit the needs of the target application environment, developers can continue using it in production, as LocalDB makes a pretty good embedded database too.

Background

Before focusing on technical description of LocalDB, I’d like to provide some background on the direction we took building it.

Today SQL Server Express serves two distinct needs. On one hand it is a free edition of SQL Server. The installation, management and programming of SQL Express in this role is expected to be 100% compatible with other editions. It can be used for learning, training and to run relatively small production database (with less than 10GB of data). Upgrade from SQL Express to paid SQL Server editions is a matter of typing in a license key and no installation is required.

But SQL Express is also SQL Server edition for developers writing applications targeting SQL Server. In this role the programming of SQL Express is still expected to be 100% compatible with other SQL Server editions, but SQL Express is supposed to be small, simple, low-footprint, require no configuration or administration, run as non-admin user, etc.

Our approach so far was to try to make SQL Express perform well in both roles. But as SQL Server product matured, and in effect added more complexity, it became harder and harder for SQL Express to be both compatible with other SQL Server editions and small/simple. The challenge is most visible in installation and configuration of SQL Express. In SQL Server “Denali” we decided to change the approach it and introduce a dedicated version of SQL Express for developers – LocalDB that delivers the simplicity and yet is compatible with other editions of SQL Server at the API level.

Also, by making LocalDB a better SQL Express for developers, we hope to be able to improve the regular SQL Express to be a better free SQL Server. We’d be very happy to hear your feedback in this area, especially if you’re using SQL Express as a database server and find any issues caused by the new features that were introduced to fit the needs of developers and desktop environment.

High-Level Overview

After the lengthy introduction it’s time to take a look at LocalDB from the technical side. At a very high level, LocalDB has the following key properties:

  1. LocalDB uses the same sqlservr.exe as the regular SQL Express and other editions of SQL Server. The application is using the same client-side providers (ADO.NET, ODBC, PDO and others) to connect to it and operates on data using the same T-SQL language as provided by SQL Express.
  2. LocalDB is installed once on a machine (per major SQL Server version). Multiple applications can start multiple LocalDB processes, but they are all started from the same sqlservr.exe executable file from the same disk location.
  3. LocalDB doesn’t create any database services; LocalDB processes are started and stopped automatically when needed. The application is just connecting to “Data Source=(localdb)\v11.0” and LocalDB process is started as a child process of the application. A few minutes after the last connection to this process is closed the process shuts down.
  4. LocalDB connections support AttachDbFileName property, which allows developers to specify a database file location. LocalDB will attach the specified database file and the connection will be made to it.

LocalDB Instances

As we said before, at the heart of LocalDB is the same sqlservr.exe as in the regular SQL Express and other editions of SQL Server. In case of LocalDB, it is installed into one central location together with all necessary DLLs. By default it is located at “C:\Program Files\Microsoft SQL Server\110\LocalDB\Binn”.

When an application uses any of the client-side providers (like ADO.NET, ODBC or PDO) to connect to “Data Source=(localdb)\v11.0”, the provider will first check if LocalDB instance for the current user is started. If it’s already started the application will connect to it. Otherwise the LocalDB instance for the current user will be started and then the provider proceeds to connect to it. Note that each user (Windows login) may have their own LocalDB instance that is isolated from instances of other users.

To try LocalDB just use this connection string: “Data Source=(localdb)\v11.0;Integrated Security=true”. Note that because the activation logic lives in the client-side provider, you need to install the latest one – SQL Server Native Client “Denali” (for ODBC and OLE DB) or the next .NET Framework (for ADO.NET). We are also shipping a QFE for .NET Framework 4 that adds the support for LocalDB to it (the updated .NET Framework 4 is already available, see this post for details).

Database as a File

LocalDB connection strings support AttachDbFileName property that allows attaching a database file during the connection process. This lets developers work directly with databases instead of the database server. Assuming a database file (*.MDF file with the corresponding *.LDF file) is stored at “C:\MyData\Database1.mdf” the developer can start working with it by simply using the following connection string: “Data Source=(localdb)\v11.0;Integrated Security=true;AttachDbFileName=C:\MyData\Database1.mdf”.

New Installer

In this CTP LocalDB is a shared feature of SQL Server Express available to try as an option during installation of any other version of SQL Express. Our ultimate goal is to offer LocalDB in a separate, simplified and smaller installer. We will keep you posted on our progress here.

LocalDB and User Instances of SQL Express

Users familiar with today’s User Instances of SQL Server Express should feel right at home with LocalDB. In essence LocalDB offers the ability to create and start a “user instance” without the hassle of installing and maintaining the parent SQL Express instance. In that respect LocalDB could be seen as an upgrade of the User Instances feature of SQL Server Express.

Let me also take this opportunity to remind everyone that User Instances are on a deprecation path since SQL Server 2008.

LocalDB and SQL Express

LocalDB is not a replacement for SQL Server Express — it is an addition to SQL Server Express lineup. While LocalDB is meant for developers, the regular SQL Server Express will continue to exist as a free SQL Server edition, fully compatible with and easily upgradeable to higher SQL Server editions.

LocalDB and SQL Server Compact?

Small and simple database, lightweight installation, connecting to a database file — this will sound familiar to any developer using SQL Server Compact. The similarities are not accidental, as our goal for LocalDB was to be as easy to use as SQL Server Compact (while being as powerful and compatible with full SQL Server as SQL Express).

There are significant differences between LocalDB and SQL Server Compact:

  1. Execution mode: SQL Server Compact is an in-proc DLL, while LocalDB runs as a separate process.
  2. Disk usage: all SQL Server Compact binaries amount to some 4MBs, while LocalDB installation takes 140MBs.
  3. Features: SQL Server Compact offers core RDBMS functionality like querying, while LocalDB provides a much richer set of features, including Stored Procedures, Geometry and Geography data types, etc.

Send us Your Feedback

Please share your feedback with us! Feel free to start a thread on SQL Express Forum, hit the “Email Author” button on this post or file a Connect item!

 

 

Q&A

Thank you all for your feedback. Please read below for answers to your questions!

 

Q: You say that LocalDB runs as a separate process. However, the main SQL Server site states “LocalDB has all of the same programmability features as SQL Express, but runs in-process with applications”. So which is it?

A: LocalDB runs as a separate process – it does not run in-process. It does however run in the same security context as the calling application.

 

Q: Does SQL Express Denali still have the same memory limitations as that of SQL Server 2005 Express.

A: The memory limitation has not changed for Denali. At Denali RTM, LocalDB will have the same memory limitations as Express.

 

Q: What is the difference between LocalDB and SQL Server Compact Edition?

A: LocalDB is the SQL Server database engine. As such it offers functionality such as online backup, transactions and data integrity.  LocalDB is designed for PCs but unlike Compact does not support small mobile devices like smart phones because of its hardware and disk requirements. Compact Edition is a shared file system database.  It is not fully API compatible with SQL Server and does not support the same level of data integrity. SQL Compact is also not supported by the PHP Driver and PDO.

 

Q: Where can I find the option to install LocalDB?

A: You must install the SQL Server Express Edition and select LocalDB as a feature. You can unselect the Database Engine if you do not require SQL Server Express as a service.

A: Starting from SQL Server 2012 RC0 LocalDB can be downloaded and installed separately, as described in this post.

 

Q: How do you actually create a LocalDB database?

A: Connect to LocalDB and then use the usual T-SQL “CREATE DATABASE …” command.

 

Q: Can LocalDB be a subscriber for merge replication?

A: Merge replication is not supported by LocalDB.

 

Q: Can LocalDB be launched from a service?

A: LocalDB can be launched from a service, as long as the profile is loaded for the service account.

 

Q: Installed OK, but how about a nice little VS2010 C# (working) sample for creating a LocalDB database?

A: We are currently working on a sample application to illustrate the usage of LocalDB in C#.

A: Please see Using LocalDB in Visual Studio 2010 for a sample walkthrough.

 

Q: When will System.Data.SqlClient support LocalDB?

A: LocalDB is planned to be supported with .NET 4.0 with a QFE (not yet shipped) and future versions of .NET

A: LocalDB is supported by System.Data.SqlClient in .NET Framework 4 Update 4.0.2, as announced in this post.

Comments (79)

  1. Karlo Bartels says:

    You say that LocalDB runs as a separate proces. However, the main SQL Server site (http://www.microsoft.com/…/express.aspx) states "LocalDB has all of the same programmability features as SQL Express, but runs in-process with applications". So which is it? I'm confused…

  2. Adam says:

    Does SQL Express Denali still have the same memory limitations as that of SQL Server 2005 Express which is now 6 years old.

    Back then it was 1GB of memory allowed per Instance. Has this been bumped to 2GB or more yet seeing as the DB size limit was bumped from 4GB to 10GB for the last release.

  3. Naveen says:

    Installing sql server sometime is really tuff task……..WMI, Encryption, perf count, DCOM, MOF compilation, previous incorrect un-installations, unknown/undocumented system privileges and other countless issues that EVERYONE faces while sql installation.

  4. CodingBeaver says:

    What is the difference between LocalDB and SQL Server Compact Edition?

  5. ErikEJ says:

    See this comparision between Express 2008 R2 and SQL Server Compact

  6. Can't find the engine.. says:

    Hi,

    I have installed Denali CTP3 but I am unable to find the folder mentioned above. Is there some special options I need to set during install ?

  7. cosmicone says:

    There seems to be no documentation at all on how to use localdb, as mentioned, the localdb folder is missing.  Too bad, I want to get testing today…

  8. cosmicone says:

    I wasted several hours today unable to find a localdb option in the installation

  9. eralper says:

    Hello cosmicone, you can check the following article http://www.kodyaz.com/…/sql-server-2011-localdb-setup.aspx

    The installation ends with success and the SQL discovery report lists the LocalDB successfully

  10. Adam says:

    How do you actually create a LocalDB database?

  11. RussP says:

    Can LocalDB be a subscriber for merge replication?

  12. Olivier says:

    Can LocalDB be launched from a service ? Many ISP apps use a Windows service.

    A silent installation mode would be great, with optional parameters by the command line, environment variables or msi properties.

  13. Mark says:

    Installed OK, but how about a nice little VS2010 C# (working) sample for creating a LocalDB database?

  14. Mark says:

    Now working nicely with System.Data.Oledb using Provider=SQLNCLI11.1 and Data Source=(localdb)v11.0.  However, no luck so far using System.Data.SqlClient.  Insight or referrals appreciated.

  15. mxj says:

    How about a nice little VS2010 VB and C# (working) sample for creating a LocalDB database?

  16. thorsten_doerfler@hotmail.com says:

    Sounds interesting. What about Multi-User scenarios? Will it be possible to use a single read-only MDF across multiple user instances. e.g. Terminal Server environments?

  17. kamil.zmeskal@cuzk.cz says:

    Does bcp.exe from SQL Server 2008 R2 Command Line Utilities works with LocalDB?

  18. dr.gazza says:

    support the fulltext index and search ?

  19. giorgio says:

    how to use sql locadl db via ado classic?

  20. Andrew says:

    Is it possible to ship the required DLLs for LocalDB with an application in order to bypass the installation requirement?

  21. kengs1951@gmail.com says:

    Is visual studio express 10 compatible with this version of sql

  22. YongCheng says:

    Do I need install both Server Express and LocalDB in the same PC? in case I need some feature from the server version.

  23. Awesome! says:

    This is great, this solves a lot of our problems. Here are my questions/thoughts:

    1) You mentioned that the install might be smaller when localdb is split off from the Denali CTP. Any idea how much smaller? Could it be gotten down to, say, 35MB? That would help us tremendously.

    2) I wish I had a DB that was identical to SQL Server in TSQL, data types, query expressiveness, etc. but did not support some of the heavier, richer features in order to save space and install size. I would even be willing to give up stored procedures to get something like that. It sounds like localdb is a great step in that direction, but still kind of heavy.

    3) It would be great to have a slim version of Management Studio in a small install to go along with localdb. Currently you have to download a >100MB install to get Management Studio. A smaller, lighter version would be very helpful.

    4) Will there be a VisualStudio Setup project bootsrapper for localdb? I hope so! I'm still waiting for the bootsrapper for 2008 R2. 🙂

  24. click-it.in@hotmail.com says:

    What are the files to deploy along with LocalDB ?

  25. zeavan says:

    Hi, for first impression look good.

    And came at good time for disconected application, there is any compatibility with sync framework, and improvement with Entity framework.

  26. nskerl@gmail.com says:

    Will LocalDB support Service Broker?

  27. Ian says:

    the statement:

    "Upgrade from SQL Express to paid SQL Server editions is a matter of typing in a license key and no

    installation is required."

    What versions of SQL express does this apply please? 7, 8, 9, 10, 10.5, 11?

  28. Krzysztof Kozielczyk - MSFT says:

    @nskerl@gmail.com

    LocalDB supports Service Broker today, but only for local queues. I cannot comment on the future, but remote Service Broker queues are not among frequently asked-for LocalDB features so far.

    The best way to request a SQL Server feature is filing the connect item – see the footer of all my posts for a link.

    -Krzysztof

  29. Carson says:

    Will there be any way to utilize the LocalDB without ever requiring administration rights, including for the install?

  30. F.OGUER says:

    "Merge replication is not supported by LocalDB." : Does-it planned ?

  31. Krzysztof Kozielczyk - MSFT says:

    @Carson:

    I don't know if we ever implement non-admin installation of LocalDB. It will not be in the RTM of SQL Server 2012.

    If you feel strong about the need for this feature, and you are willing to share your usage scenario and more info, please file a connect item. It is the best way of telling us what you need and why. See the footer of all my posts for a link.

    Thanks,

    -Krzysztof

  32. Krzysztof Kozielczyk - MSFT says:

    @F.OGUER

    Same for the replication. It will not make RTM of SQL Server 2012 and I cannot comment on future releases.

    As I said, we are paying close attention to our connect feedback…

    Thanks,

    -Krzysztof

  33. Charles Stanton says:

    Can LocalDB be used by those of us on the Java side of the fence?  How would we connect a JDBC driver to an on-demand database?

  34. Krzysztof Kozielczyk - MSFT says:

    @Charles Stanton

    Unfortunately JDBC driver doesn't support LocalDB at this moment and there is no easy workaround. The team is aware of this missing feature, but filing a connect item is always helpful for DCR tracking and prioritization.

    Thanks,

    -Krzysztof

  35. 52.bill.bell@gmail.com says:

    I am trying to test it out using ADO.  I am really not sure I can even connect to it using ADO, not ADO.Net.  To connect to SQL Server I use:

       Dim MyConnect As New ADODB.Connection

           MyConnect.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _

           "Persist Security Info=False;" & _

           "Initial Catalog=MyDB;" & _

           "Data Source=MyComputerMyInstance"

    I have a reference to ADO 6.0

    Does anyone know if you can connect using ADO and what the connection string is?

  36. Krzysztof Kozielczyk - MSFT says:

    @mogulman52:

    Only ADO.NET, ODBC and OLE DB drivers have been updated to understand LocalDB connections. One possible approach of using LocalDB from ADO is to make sure ADO is using SQL Server 2012 Native Client.

  37. Wallace Kelly says:

    Will SQL Server 2012 Profiler be able to record LocalDb events? Specifically, I want to trace the SQL to / from LocalDb.

  38. Krzysztof Kozielczyk - MSFT says:

    @Wallace Kelly

    Since LocalDB is part of SQL Server Express, SQL Profiler will not work with it, as SQL Profiler doesn't support SQL Server Express.

  39. Brad Pepers says:

    Can multiple users be using the same database file at the same time?  For instance if the database is stored on a network shared drive and two users are running an app on separate computers that use the same database.

  40. Krzysztof Kozielczyk - MSFT says:

    @Brad Pepers

    No, only one LocalDB instance can have the database open at any given time. This is also true for any other SQL Server editions and instances.

  41. Michael Ramirez says:

    Can I create a report using Report Builder 3 that connects to a LocalDB data source?

  42. Alex Apostolou says:

    Will LocalDB support jdbc connectivity?

  43. @Charles Stanton & Alex Apostolou,

    Do you use SQL Server Express today for local development?  Are you working on Windows or another platform?  It would be great to hear more about how you would like to use LocalDB with your Java app.

    Shamitha Reddy, Program Manager Microsoft JDBC Driver for SQL Server

  44. Imar Spaanjaars says:

    What's the story on development tools within Dev 11 for LocalDB? Right now it seems this is quite limited: no query designers, no diagrams etc. This means you either need to use SQL Server Management Studio separately outside of Dev 11, or target a SqlExpress instance instead. This seems like a step back. My guess is that developers are likely to choose the second option and skip LocalDB which would be a shame.

    Will there be full design-time support for LocalDB in Visual Studio?

    Imar

  45. srini.acharya@live.com says:

    Imar,

       SQL server Data tools( msdn.microsoft.com/…/hh500335(v=VS.103).aspx)  use LocalDB as the default development database and provide full design time support including query designer, object exploer etc.

    — srini

  46. A.Kadir Bener says:

    Thanks for all descriptions. But there is an error i cannot fix…

    I've already used the localdb on a small project. I tested and saw no problem.

    But when i deployed the application some users complained about connection problems which says there is a network related error to connect (localdb)v11.0 instance is not reachable.

    I finally reproduced the error message. The setup is ok, the .Net 4.0.2 update is ok and all other Windows updates are ok. There is no error when the application is started with Administrator account.

    But when a user account is used the exception throws and says that the sql server is not reachable. Do you have any idea for the customers who wants to use a username which have no administrator privileges on their computer.

    Thank you.

  47. Kadir,

    From your description, I cannot immediately say what might be the problem.

    Please look at the Windows application event log to see if there are any error messages. You can also look at the SQL Server logs in "%localappdata%MicrosoftMicrosoft SQL Server Local DBInstancesv11.0".

    One question: do your users see this error all the time? If they try your application again, does it work for them the second time?

    Ward.

  48. Steve says:

    I haven't used it yet, but this is so extremely exciting. It is this kind of thing that will keep developers at MS; not Metro. I've never installed SSExp before. But, with this wise move, I d/l'd & installed the entire install, esp LocalDB. I also put my MySql projects on hold, great work you guys did here!

  49. We appreciate your comments, Steve. Please don't hesitate to post here again if you have any questions.

    We are also looking for input on how to make it better for developers, so pleaase post your suggestions as well.

    Ward.

  50. Doodling DBA says:

    Hi there – thanks for the article!

    Question – how does patching work for LocalDB through SSDT? Is it patched via Visual Studio or does it require separate SQL patching?

    Thanks!

  51. ONLY dumb developers are saving apps and dbs in local app data!!! says:

    "%localappdata%MicrosoftMicrosoft SQL Server Local DBInstancesv11.0" is a folder that is EXCLUDED from roaming via a standard MS GPO named "Exclude directories in roaming profile" with a default value of "AppDataLocal;AppDataLocalLow;". Therefore if a user logs of from his PC this folder get's deleted. All data inside this folder get's wiped from disk (data loss). Turn your brains on guys! What Google does is WRONG.

    Suxxx Microsoft ADCENTER is such a broken software. It also installs the app into local app data. Therefore the application is also lost, not only the database.

  52. LocalDb - Can I still use dot InstanceName format somehow says:

    Summary:

    Thank you MS for your free tools.

    Can I still use .InstanceName somehow especially as its the only insance on the machine

    Details:

    Again thanks for your hard work on these free tools.

    LocalDb looked promising.  Unfortunately after investigation there is one major problem.  We have a suite of utilities all with a hard coded .InstanceName if we move from Express to LocalDb we have to re-write lots of our software suite. (yes yes maybe it should all be in the registry) but what of Customers who also have . paths in their .exe.config files. Surely it would have been easier [to use] to have LocalDb interfaces automatically sense that a .InstanceName is of type LocalDb and start it. Why the (LocalDb)InstanceName? I find this and other stuff from MS is not very well thought out as a real world solution (if academically correct) I mean its purist but not backwards compatible, forging ahead without thought for us poor ISVs who have existing code,running systems that need upgrading and customers.

    Its frustrating that compact does not have stored procedures; one reason we can't use it. I understand why (kind of) though its bad design to encode SQL.

    Thanks for reading

  53. Re: LocalDb – Can I still use dot InstanceName format somehow

    I appreciate your frustration. Unfortunately, there is no way to use the ".instanceName" notation to reference a LocalDB instance. Our experience is that most ISVs provide a mechanism to specify the server name parameter of the connection string externally to their applications. It strikes me that this would not be a difficult change for you, and might provide benefits for the future. That said, we will keep your suggestions in mind for a future release — perhaps we could extend the existing instance alias mechanism.to meet this requirement.

    Thanks,

    Ward.

  54. Stephan says:

    Unfortunately, I must not that LocalDb is NOT a "better" SQL express. In fact, I have had nothing but problems with it. For some unknown reason, it keeps thinking that the database is read-only. In some cases, I resolved this but granting all users on the machine full control over the MDF/LDF files. In other cases, the database (somehow?) actually did transition to a readonly state and even using Sql Management studio I have been unable to make the database not read only ! What a PITA.

  55. Stephan, I'm sorry you have had difficulties with LocalDB. In designing LocalDB, we made a conscious decision to always run the DB engine "unelevated", even if the user that started it was running elevated. This is much safer for your computer, but it does mean that your database files must have their permissions set to allow your own account to access them — not just administrators. One way to ensure this is to locate database files under your profile directory (e.g. users<username>). If you locate them elsewhere, you should use the security tab in Windows Explorer to give yourself full access to the directory.

  56. Doug Kimzey says:

    Where would I find documentation that would help me build an InstallShield or WiX installed for an application using LocalDb?

  57. Javier Siancas says:

    How can I use LocalDB with Visual Studio Express 2012 for Windows Phone?

  58. KSnyder says:

    Q. How do I install both SQL Express 2012 & LocalDB 2012 on the same development PC?

    I tried running the LocalDB 2012 Wizard installer and it failed because I have a 'Higher Version' of SQL Express installed.

    I currently cannot run a web app using LocalDB unless I 'Attach' the database file in SQL Express 2012.

  59. William Vaughn says:

    When will LocalDB attached-file databases support the Database Diagram functionality that other SQL Server databases accessed from the Server Explorer can leverage? This was supported in earlier versions of Visual Studio that used SQL Express. Why not in LocalDB?

  60. Lance Wheeler says:

    I was just trying to download SQL Server Express LocalDB 2012 and it gave me a message saying "Unable to start SQLWriter.  Verify that you have sufficient priviledges to start services."  Is there a certain restriction going on?  I am a beginner, so I have no idea what it could mean, maybe that I need administrative rights but not sure.  Please help!

  61. Anphy Jose says:

    Hi,

    Is there any way i can change the owner of Localdb instance? In our application we are creating and instance and sharing. But as soon as the owner who has created the localdb instance log offs the instance is stopped. No one other than the owner can start the localdb instance.

    Any suggestions on how this issue can be solved?

  62. Novaterata says:

    What do I need to do if I switch the user my process or App Pool is running as to access a particular LocalDb Named Instance (or v11.0 for that matter).

    I already have an installed software running an MVC4 application and a Topshelf service. They currently are running as LocalSystem, but I need to be able to switch, Post-Install, to any other user. Whenever I try to do this I get Error 50 cannot create Instance even if I explicitly create the instance for that user.

    Also two instances with the same name will appear under "sqllocaldb info" and one of those instances cannot be interacted with by sqllocaldb.

    I'm already aware of how to interact with LocalSystem localdb using PsExec -s sqllocaldb

  63. Gigaplex says:

    [To try LocalDB just use this connection string: "Data Source=(localdb)v11.0;Integrated Security=true"]

    Didn't work for me. I had to use 'SSPI' instead of 'true' for Integrated Security, otherwise I got DB_E_ERRORSOCCURRED. I thought these settings were supposed to be equivalent?

  64. compress LocalDb V 11,0 says:

    in LocalDb V 11,0 is there any way to Compress the size of MDF file and Clear all the Empty Space in Database file either with tool or programmatically

  65. Sean says:

    Can you, How do you install LocalDB with click-once?

  66. Jim says:

    Oh of course; (localdb)v11.0 to connect.  What WAS I THINKING!!?

    Funny.

    The whole thing, from download, to connecting to the DB could use a LOT of streamlining.  You want to compete with MySQL in the low end market??  

  67. Chris Wolf says:

    I installed SQL Server 12, localdb and I find it's totally impossible to connect to it.  I tried MSSMS, sqlcmd, etc. – no luck.  You would think that MS would document how to do this most basic thing.  And yes, it's started:

    $ sqllocaldb i MSSQLLocalDB

    Name:               MSSQLLocalDB

    Version:            12.0.2000.8

    Shared name:

    Owner:              ICHIJIMAcwolf

    Auto-create:        Yes

    State:              Running

    Last start time:    6/12/2014 8:34:11 AM

    Instance pipe name: np:\.pipeLOCALDB#C86052DDtsqlquery

  68. GG says:

    Just installed SQLLocalDB.msi

    what is the next step to get it working ? The (localdb)v11.0 does not show up in the list of servers as it does on my development pc.

  69. GG says:

    Finally got it working, turns out that you do not only need SqlLocalDB.msi but also sqlincli.msi to get it working.

    The latter installs the native sql server client that you need to make your localdb visible to ADO.

    Why is this not mentioned anywhere ? Every install documentation I have looked at (and there have been many) do not mention this at all, leaving many many many frustrated people out there that will eventually give up on localdb because they cannot get it working…

  70. Nameless says:

    In SqlServerCompact Edition we can create a database like this:

    SqlCeEngine engine = new SqlCeEngine(connectionString);

    if (!System.IO.File.Exists(filePath))

    {

      engine.CreateDatabase();

    }

    In LocalDB v.11, how we can create the database if not exists? Where is the SqlServerEngine?

  71. wangx2 says:

    Is it possible to use attachdbfilename to attach 2 mdf databases?

  72. Subbah says:

    I do not want to install LocalDB using SqlLocalDB.msi on customer computers. Is there a way to include LocalDB-related binaries into my application installation program? Will this work?

  73. Arvind says:

    I installed LocalDB.  From the command line, I am able to create a new local database using the command "sqllocaldb create testlocaldb".  So, I know the installation is fine.  However, when I try to connect to it from SQL Management Studio with the server name being "(localdb)V11.0", it does not work.  I get the following error (I tried V12.0 instead of V11.0 and I get the same error) – what am I doing wrong?

    TITLE: Connect to Server

    ——————————

    Cannot connect to (localdb)V11.0.

    ——————————

    ADDITIONAL INFORMATION:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 50 – Local Database Runtime error occurred. Cannot create an automatic instance. See the Windows Application event log for error details.

    ) (Microsoft SQL Server, Error: -1983577832)

    For help, click: go.microsoft.com/fwlink

    ——————————

    BUTTONS:

    OK

    ——————————

  74. Prot says:

    Damn! Why everyone is saying that we are able to connect to localdb using this well known connection strings if it doesn't work. Have you seen how many post you can find in the Internet that are saying "how to connect to this bladdy localdb?" It means that something is wrong with localdb or configuration or installed clients. Only named pipes works and I spent four days looking how to connect so when I'm reading that localdb was created to make developers lifes easier I'm little pissed off.

  75. Pradeep says:

    sqllocaldb info pradmlocaldb

    Name:               pradmlocaldb

    Version:            11.0.5532.0

    Shared name:        pradmdbshare

    Owner:              NT AUTHORITYSYSTEM

    Auto-create:        No

    State:              Running

    Last start time:    24-12-2014 21:25:23

    Instance pipe name: np:\.pipeLOCALDB#SH817125tsqlquery

    From Management Studio:-

    you can copy the pipe name in the server name section : np:\.pipeLOCALDB#SH817125tsqlquery

    you can copy the actual name in the server name section (if management studio is launched under the owner's login context): (localdb).pradmlocaldb

    you can copy the shared name in the server name section (if management studio is launced by a login who is not the owner of the instance):(localdb).pradmdbshare

    From Application:-

    blogs.msdn.com/…/net-framework-4-now-supports-localdb.aspx

    You need to create a ODBC System DSN :-

    DSN Name : Any name you like pradmdbshare1

    Server   : The shared name like (localdb).pradmdbshare

    Connection String: "DSN=pradmdbshare1;Database=master;Uid=mstest;Pwd=passw0rd!!@52";

    Only the windows login who is the owner of the instance can connect to the instance using the instance name other should use the shared name. The instance can only be stopped and started by the owner of the instance.

    If the instance is not started and if the application tries to connect to the instance a new SQLServr.exe process will start to host the instance but this will only happen when the application who is trying to connect is under the context of the windows login who is the owner of the instance. If some other windows login tries

    to launch the same application the instance will not get started by itself ( you an use SQLLOCALDB exe to start the instance under the owners login context) and then make the connection request.

    If you look at the above example you will notice that the connection is made to SQLLocalDB with a SQL Login; The windows login context that I have mentioned above is for the application which is getting launched.

  76. chuck says:

    When using LocalDB in my ASP.NET MVC application, I have the connection string and can successfully connect to the database the first time it is created, but subsequent attempts to connect after changing my Model props in some way make it necessary to change the name of the .mdb file and also the logical name in the connection string. I have tried shutting down, deleting .mdb and .ldb files, clearing cache from AppDataTempTemporary ASP.NET Files, etc., everything I can think of, and still, on subsequent connection attempt to the database with a code-first approach, the db does not want to be re-created after a change to the Model unless I change the actual name of it (both filename and logical name) in the connection string to something I haven't used previously. I've been simply appending incremental integer values, i.e., File1.mdb, File2,mdb, File3, mdb, etc.Is this a known issue and are there any reasonable workarounds besides what I am doing? This entire ability to do code-first approach using MVC is a major selling point for me, and it just seems kind of messy to have to keep re-naming the db file until I'm done changing the Models.

  77. novaterata says:

    @chuck Have you tried stopping and deleting the instance, i.e. sqllocaldb stop [InstanceName] followed by sqllocaldb delete [InstanceName] at the command line? With [InstanceName] being v11.0 or another named instance. If you don't specify the InitialCatalog then it will pick a GUID which will be associated with the MDF file. I would recommend you use a static InitialCatalog, so that there won't be a conflict between the database path and the "name" of the database in the instance. Think of the instance as a whole installation and master db of SQL Express that is trivialy created, started, stopped, and deleted

  78. Beto says:

    Hola buenas tardes una pregunta el LocalDB soporta BulkCopy? ya que estoy desarrollando una aplicación en la cual tengo que exportar aproximadamente 20,000.00 registros de una DB de SQL Server a mi LocalDB, al momento de correr la aplicación no me genera ningún error pero no me inserta en la LocalDB, la conexión a la base es exitosa asi que no entiendo que pueda ser? este es mi bulk que estoy ocupando:

    Using bulkCliente As New SqlBulkCopy(conn, SqlBulkCopyOptions.Default, objTransaccion)

                       bulkCliente.DestinationTableName = "Tbl_Clientes"

                       bulkCliente.BulkCopyTimeout = 380

                       bulkCliente.ColumnMappings.Add("NoSerie", "NoSerie")

                       bulkCliente.ColumnMappings.Add("Modalidad", "Modalidad")

                       bulkCliente.ColumnMappings.Add("DiaFacturacion", "DiaFacturacion")

                       bulkCliente.WriteToServer(ds.Tables(2))

                       bulkCliente.Close()

    End Using

    Espero me puedan apoyar, se los agradecería demasiado.

    Saludos.

Skip to main content