none
Importing database always defaults to localdb

    Question

  • Hi all,

    we are in the process of moving some of our database projects from VS2010 to VS2012 and after a bit of experimentation we have decided to re-create the database projects by doing a 'Create New Project'  from SSOX

    All of our dev machines have their own copy of SQLServer 2008 R2 against which all db development is done.

    When I create a new project in SSDT by importing from a database that is on my dev machine SQLServer instance, the database is imported but the import process creates a new copy of the database in (localdb) and points the debug connection string at the (localdb) version.

    Two questions.

    1) Why?

    2) Is there any way to stop this behaviour and have it point at the database it was imported from by default. Also to stop the creation of the (localdb) copy that I don't want. I can't find any way of changing this behaviour.

    Thanks,

    Rob.

    Friday, September 21, 2012 11:27 AM

Answers

  • On creating a new project you're right that a database node is added to LocalDB. There is no way to change the default at present. Some points:

    - This is not a full copy of the project. It's an empty database which is only populated on debug/deploy. The cost of creating this using LocalDB is very low. LocalDBstops running if it hasn't been queried for a while, ensuring this isn't eating up resources on your dev box.

    - As of the September update of SSDT (VS2012 available here) there is one (localdb)\Projects instance under which all project databases are placed. This should help minimize the visual impact of this for you in SQL Server Object Explorer (SSOX).

    - We don't set the debug string to point to the database you created the project from since if it's a production server or shared development / debug instance we don't want your hitting F5 to test/debug changes to damage the server copy. This is a conservative "do no harm" approach and covers most common scenarios.

    - Many users do not have their own dedicated test SQL Server instances (which I can see you do). In those cases having an individual local instance for each dev to test on is a big benefit. 


    Finally I'm guessing you are aware that can upgrade from .dbproj to .sqlproj by opening your existing projects in VS2012? This would preserve your source code control history and previous file layout. It may be less time consuming than importing into new projects via SSOX?

    Kevin

    Friday, September 21, 2012 6:06 PM
    Owner

All replies

  • On creating a new project you're right that a database node is added to LocalDB. There is no way to change the default at present. Some points:

    - This is not a full copy of the project. It's an empty database which is only populated on debug/deploy. The cost of creating this using LocalDB is very low. LocalDBstops running if it hasn't been queried for a while, ensuring this isn't eating up resources on your dev box.

    - As of the September update of SSDT (VS2012 available here) there is one (localdb)\Projects instance under which all project databases are placed. This should help minimize the visual impact of this for you in SQL Server Object Explorer (SSOX).

    - We don't set the debug string to point to the database you created the project from since if it's a production server or shared development / debug instance we don't want your hitting F5 to test/debug changes to damage the server copy. This is a conservative "do no harm" approach and covers most common scenarios.

    - Many users do not have their own dedicated test SQL Server instances (which I can see you do). In those cases having an individual local instance for each dev to test on is a big benefit. 


    Finally I'm guessing you are aware that can upgrade from .dbproj to .sqlproj by opening your existing projects in VS2012? This would preserve your source code control history and previous file layout. It may be less time consuming than importing into new projects via SSOX?

    Kevin

    Friday, September 21, 2012 6:06 PM
    Owner
  • Hi Kevin, thanks for the reply it's appreciated.

    I'll go through and respond to a few of your points.

    1.) On creating a new project you're right that a database node is added to LocalDB. There is no way to change the default at present. Some points:

    Please, please change this. You'd make the dev team here very happy!

    2.) This is not a full copy of the project. It's an empty database which is only populated on debug/deploy. The cost of creating this using LocalDB is very  low. LocalDBstops running if it hasn't been queried for a while, ensuring this isn't eating up resources on your dev box.

    Yes but it is doing something I don't want it to do and creating something I didn't ask for and will never use and that is annoying.

    3.) As of the September update of SSDT (VS2012 available here) there is one (localdb)\Projects instance under which all project databases are placed. This should help minimize the visual impact of this for you in SQL Server Object Explorer (SSOX).

    I don't want the visual impact minimised I don't want it there at all. I don't like artefacts being created that I cannot control especially when I didn't want them in the first place.

    4.) We don't set the debug string to point to the database you created the project from since if it's a production server or shared development / debug instance we don't want your hitting F5 to test/debug changes to damage the server copy. This is a conservative "do no harm" approach and covers most common scenarios.

    Ok, I'll leave aside the issue of having direct access to production db's from dev machines but I would like to point out that if I'm importing a database that is on the same machine as the development environment it's a pretty safe bet that I want to use that database and not one auto created for me that doesn't support all of the features I need.

    5.) Many users do not have their own dedicated test SQL Server instances (which I can see you do). In those cases having an individual local instance for each dev to test on is a big benefit.

    Undoutedly. However it's a big pain in the neck for the rest of us.

    6.) Finally I'm guessing you are aware that can upgrade from .dbproj to .sqlproj by opening your existing projects in VS2012? This would preserve your source code control history and previous file layout. It may be less time consuming than importing into new projects via SSOX?

    Yes we had a play with that. Unfortunatly in VS2010 using the dbproj tools most of the work could be done in the Schema View. This view put files in the relevant folders tables/keys etc. Once you convert to sqlproj, the schema view just dumps all files in the root folder of the project so you then have to manually move files around. Also keys, indexes etc are now stored in the sql file that creates the table so if you import old dbproj files and convert them you get a nested folder structure that doesn't seem to be relevant any more. Also of course you get the dreaded (localdb) copy automatically created when you convert.

    That's why we decided to go with re-importing the databases and keeping a fairly flat folder structure in the projects.

    I did some looking around last week and found a post suggesting that if you un-install (localdb) then SSDT still works and does not create unwanted databases. I gave this a go and it seemed to work. Unfortunatly I then had to upgrade SSDT to the latest version and it re-installed (localdb) without asking which is a bit of a pain.

    I can see why (localdb) may be useful to some developers who do not have a SQLServer instance to work with, but for the rest of us it is an annoyance to be pushed down a path which isn't suitable.

    All we need is an option to turn this behaviour off and then you'll keep (almost) everybody happy.

    Regards,
    Rob.

    Monday, September 24, 2012 9:38 AM