1

We have a project for school in which we have to create a web application. We have access to an online database, but the problem is that we have to connect through VPN before we can connect to it.

For this reason, we're looking for a possibility in which we have a local database (which would be in the project, I suppose?) that we can all use (the project is on a subversion server). But when we deploy the project on our deployment server, we want it to use the real database connection.

I think I've seen it before, but after searching for hours I couldn't find anything relevant.

Is this possible?

EDIT:

We use MVC5 with Entity Framework.

Olivier Van Bulck
  • 751
  • 1
  • 8
  • 22

3 Answers3

7

People typically do this in one of several ways depending on what you want to achieve.

  • Pull down the database as an mdf file and store it in your repository. You can then have a manual step during setup where you ask people to import it into their database (I recommend localdb for local development but sure, you can use sql server or something).
    • Advantages: Very simple to set up for the person arranging this.
    • Disadvantages: Manual step is difficult for beginners. If the database is large it will swell your repository. If one developer changes the database (for example by adding a column) then you have to let everyone know to blow away their copy and restore from backup. Also, there is no real explicit history of how your database changes and your test database is not integrated with whatever you have to do for deployment.
  • Pull down the database as an mdf file. Include this file in your project and set it's properties as Content/Copy if Newer. Then use it directly using a connection string to attachDb such as Server=(localdb)\v11.0;AttachDbFilename=.\MyDataFile.mdf;Database=dbname;.
    • Advantages: No manual step, everything just works
    • Disadvantages: Obviously you'd want to use relative paths for AttachDbFilename and I'm not 100% that this is supported. Also, same as above but instead of having to let everyone know when their db needs restoration it just restores behind the scenes. This can mean users suddenly see their data disappear with no notice. It can also fail sometimes due to things like a locked database file and everyone just has to get good at keeping an eye out for that.
  • Maintain a sql script that can recreate your database in localdb. Provide people with a powershell or batch script (also in source control) to run it easily. Optionally use a post-build script that determines if you need to recreate the database and runs it.
    • Advantages: Everything is very explicit. Reasonably small size in the repository (which should be able to store text efficiently). You can use the same script as part of deployment.
    • Disadvantages: More work to set up. Still no real way to deploy changes to existing databases.
  • Use Entity Framework Database first. I can't speak to what the process looks like exactly when doing this but I know that it is possible.
    • Advantages: I guess.
    • Disadvantages: Ewwww EF database first
  • Use Entity Framework Code First with Migrations. Use explicit migrations (not the silly auto-generate-my-entire-db cruft) and write a proper Seed method to populate your data.
    • Advantages: This is what professional developers do and is based on tested patterns used frequently by Rails, Django, and many other frameworks. It is very flexible and explicit and supports changing existing databases.
    • Disadvantages: Can be quite difficult to set up if you don't have experience and especially if you're unaware of the migrations pattern. There's some naming difficulties that make it kind of hard to google (database first EF vs code-first EF, explicit migrations vs auto-generate-the-db, several different Seed methods that depend on your initializer).
George Mauer
  • 117,483
  • 131
  • 382
  • 612
  • 1
    Storing databases as MDF/LDF in source control is a really bad idea - don't choose those options (you get no history, cannot merge easily and deploy is hard) – Ed Elliott Apr 07 '15 at 15:32
  • All of which I mentioned in the answer @EdElliott at that point you're basically just using source control as a distribution channel for a base db – George Mauer Apr 07 '15 at 15:36
1

You can create a local database using Entity Framework, which saves the database file on the local filesystem, which you can push to your version control server to share with your colleagues. If you decide to deploy the database, you can generate an sql script that you can run on the production database. You can do so by connecting to the local database using SQL Management Studio. You will just need to modify the connection string of the published application after deployment.

Community
  • 1
  • 1
ilian
  • 129
  • 1
  • 1
  • 7
  • If you're going with this approach the things to google/read up on are Code-first Entity Framework, Entity Framework migrations, and the concept of seeding in EF Migrations. – George Mauer Apr 01 '15 at 23:00
0

You used to be able to use Sql Server Express but it has changed to LocalDB and can be installed and run locally.

http://blogs.msdn.com/b/sqlexpress/archive/2011/07/12/introducing-localdb-a-better-sql-express.aspx

https://msdn.microsoft.com/en-us/library/ms233763.aspx

TheRk
  • 341
  • 2
  • 8
  • Yes, but is it possible to tell the application to reach the SQL server when deployed? Because that's the main problem at the moment... – Olivier Van Bulck Apr 01 '15 at 22:58
  • no need to install localdb, it comes with visual studio – George Mauer Apr 01 '15 at 22:59
  • Using local db is just that A local Copy, It will be share amongst the team via the subversion but deployment will require each running computer to be able to utilize the database. If you want to use a central server then that is what you would use but to do so requires connectivity to it and thus will need to be connected to vpn in your case. unfortunately you can't do both. – TheRk Apr 01 '15 at 23:04