如何迁移Visual Studio 2012 ASP.Net MVC 4项目以使用SQL Server实例而不是数据文件? [英] How to migrate a Visual Studio 2012 ASP.Net MVC 4 project to use an SQL Server instance instead of a data file?

问题描述

当我使用Visual Studio 2012创建一个新的ASP.Net MVC 4项目时，它将数据放在项目目录中的mdf文件中.

这时我想做的是将数据库缓慢迁移到SQL Server实例，同时保留ASP.Net MVC 4项目模板提供的所有支架材料(我是指用户帐户管理等)

什么是正确的逐步方法?

我究竟应该如何更改连接字符串?

我应该如何在生产中的SQL Server中对我的应用程序进行身份验证?

很抱歉，您有一个愚蠢的问题，但我以前没有处理过ASP.Net应用程序，根据我以前的经验(使用WinForms)，所有实际用户都拥有一个单独的SQL Server帐户，并且非常简单.

解决方案

整个用户实例和AttachDbFileName = 方法都有缺陷-充其量！ Visual Studio将围绕.mdf文件进行复制，并且最有可能，您的INSERT工作正常-但是您只是在查看错误的.mdf文件结束！

我认为真正的解决方案是

1. 安装SQL Server-Express(无论如何您已经完成了安装)或任何其他版本

2. 安装SQL Server Management Studio(Express)

3. 在 SSMS Express 中创建数据库，并为其指定逻辑名称(例如YourDatabase)

4. 使用其逻辑数据库名称(在服务器上创建时提供)连接至它-请勿随意使用物理数据库文件和用户实例.在这种情况下，您的连接字符串将类似于:

   Data Source=.\\SQLEXPRESS;Database=YourDatabase;User ID=AppUser;Pwd=Top$ecret
   

   其他所有内容与以前完全相同...

要部署到生产环境，基本上有很多选择:

1. 自己创建部署SQL脚本，并使用sqlcmd或任何其他有用的SQL脚本运行器执行它们

2. 使用SQL差异工具，例如 Red-Gate SQL比较，甚至使用内置的Visual Studio差异工具来确定客户端站点上安装的数据库版本与新版本之间的差异，并根据该差异创建单个升级SQL脚本.

3. 使用Visual Studio数据库项目，并让VS处理升级脚本和部署. VS数据库项目在数据库之上构建模型-基本上，您只能创建CREATE TABLE ....脚本，而VS工具可以确定需要更改，删除或创建的内容

4. 如果您使用的是Entity Framework代码优先-请考虑使用EF代码优先的迁移从C#代码更新数据库

When I create a new ASP.Net MVC 4 project with Visual Studio 2012 it puts the data in an mdf file in the project directory.

What I would like to do at this point is to migrate the database to an SQL Server instance gently, keeping all the scaffold stuff provided by the ASP.Net MVC 4 project template (I mean user accounts management etc.)

What would be the right step-by-step way to do this?

How exactly should I change the connection string?

How should I authentify my application in SQL Server in production?

Sorry for a dumb question but I haven't dealt with ASP.Net applications before, in my previous experience (which was with WinForms) all the actual users had a separate SQL Server account and it was pretty straightforward.

解决方案

The whole User Instance and AttachDbFileName= approach is flawed - at best! Visual Studio will be copying around the .mdf file and most likely, your INSERT works just fine - but you're just looking at the wrong .mdf file in the end!

The real solution in my opinion would be to

1. install SQL Server - Express (and you've already done that anyway) or any other edition

2. install SQL Server Management Studio (Express)

3. create your database in SSMS Express, give it a logical name (e.g. YourDatabase)

4. connect to it using its logical database name (given when you create it on the server) - and don't mess around with physical database files and user instances. In that case, your connection string would be something like:

   Data Source=.\\SQLEXPRESS;Database=YourDatabase;User ID=AppUser;Pwd=Top$ecret
   

   and everything else is exactly the same as before...

For deployment to production, you basically have a number of options:

1. create deployment SQL scripts yourself and have them executed using sqlcmd or any other useful SQL script runner

2. use a SQL diff tool like Red-Gate SQL Compare or even the built-in Visual Studio diff tool to determine difference between the database version installed at your client's site, and the new version, and create a single upgrade SQL script from that diff

3. use the Visual Studio Database Projects and let VS handle the upgrade scripts and deployments. VS database projects craft a model on top of your database - you basically only ever create the CREATE TABLE .... script and the VS tools figure out what needs to be altered, dropped, created fresh

4. if you're using Entity Framework code-first - look into using the EF code-first migrations to update your database from C# code

这篇关于如何迁移Visual Studio 2012 ASP.Net MVC 4项目以使用SQL Server实例而不是数据文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！