The goal: I have created a Microsoft MVC 5 project that uses a database. It generated lot of scaffolding for me including a localdb that is used with user authentication (this database was in the form of an MDF and LDF files). My question is what would be the correct way to move this project over to a production server and move the database to a production database?
The problem:
Should I be using scripts? Once I have made all of my changes should I have a script that copies the data and code over to their respective servers? I am not entirely sure what is the standard way of doing this. Also how would I create the connection strings for both the database and the entity's?
Any pointers would help.
Thanks
For initial deployment, the easiest option with LocalDB is to create a data-tier application. If you browse to your database in the SQL Server Object Explorer panel (in Visual Studio, VIEW > SQL Server Object Explorer), you can right-click the database and choose either "Publish Data-Tier Application..." or "Extract Data-Tier Application...". The only difference is that the former will directly apply the changes to the destination database, while the latter will just create a file (DACPAC) that you can then bring into SQL Server Management Studio. In Visual Studio this will package up the schema and data of your database and allow you deploy basically an exact copy to a destination SQL Server instance. (Be advised that SQL Server Management Studio can also generate data-tier application files, but it does not include the data in its version. To get the schema and data in SQL Server Management Studio, at least in up to and including 2014, you must create a Backup Package (BACPAC) instead.)
If you don't want to keep the data or you just prefer a straight SQL approach. You can remove all the migrations in your project's Migrations directory and blow out your local database (Browse to it in SQL Server Object Explorer and delete all the tables. Don't delete the entire database; you just want to empty it out.) Then, you can generate a new initial migration and call Update-Database -Script, which will generate all the SQL necessary to create your schemas. You can then save this SQL file for later or just copy it over into a new query in SQL Server Management Studio and run it.
For updates, SQL Server Object Explorer in Visual Studio has a nice feature that allows you to both compare schema and compare data between a local database and a remote database. Just browse your local database in that panel, right-click it and choose either "Schema Compare" or "Data Comparison", depending on which you need. A wizard will guide you through the process where you'll choose a source and destination database. It will then run its comparison and present you with the changes that need to be made to the schema or the differences in the data between the two instance, depending on which option you chose. From here you can either directly publish the changes to the destination server or generate a SQL script to apply the changes.
Alternatively, when you create migrations in your application, before updating your local database, you can call Update-Database -Script to generate the SQL to make the change first. You can then apply this SQL to your production database when you deploy the changes or hand it off to your DBA, if you have one, to make the changes for you.