丢失的迁移和Azure数据库现在不同步 [英] Lost migrations and Azure database is now out of sync

问题描述

快速问题是我的Azure数据库如何恢复与本地数据库同步？

详细信息：
我一直在使用实体框架在我的项目，这真的没有与ASP.NET MVC 5示例项目，其中内置了Identity 2。

有两个数据库上下文，一个称为ApplicationDbContext（aspnet_somethingUserDB）来自Identity，另一个名为StoreInitialTestContext（store_db），来自我的更改。

有趣的是，我将这些上下文都放在单独的SQL数据库中，而且它们只在Azure服务上的一个名为store_db的单一数据库中。

事情正在游泳，两个本地数据库具有相同的表互补作为Azure上的单个数据库，同步。该网站在本地适用于调试和Azure。

通过添加模型对模式进行最后一次更改后，我做了update-database命令和本地数据库已更新。不幸的是，当我将网站发布给Azure时，通过运行代码优先迁移...选择上下文和复选标记后，Azure数据库没有更新。

查看我的代码，我没有看到任何正在等待的迁移，所以我不知道如何让Azure数据库恢复同步。

As最后的努力，我将我的Azure数据库从基本设置为标准，并使用我的本地store_db和Azure store_db之间的比较模式命令。我确保只有通过取消选中与aspnet_UsersomethingDb有关的表来更新本地store_db中的表。

该操作不成功，所以我尝试撤消对本地的更改是问题，哪些不适用于Azure。

这也不成功，所以我调整了我的网站，以避免EntityFramework 模型vs数据库差异崩溃，所以至少会有所不同。

在这一点上，我的本地数据库很好，但关闭代码第一次迁移复选框，Azure数据库报告：

在EntityFramework.dll中发生了类型System.InvalidOperationException的异常，但
不在用户代码中处理

其他信息：自
创建数据库以来，支持StoreInitialTestContext上下文的模型已更改。考虑使用代码优先迁移来更新数据库
（ http://go.microsoft.com/software/）。 com / fwlink /？LinkId = 238269 ）。

打开复选框，报表是：

在EntityFramework.dll中发生了类型为System.Data.SqlClient.SqlException的异常，但
未在用户代码中处理

附加信息：
数据库中已经有一个名为ExtendedUserInformations的对象。

这似乎表明代码优先迁移希望添加对象以使事情保持同步，但对象已经存在。

是否只有快速获取这些数据库同步的方法？在Azure中没有太多的数据，所以如果我必须，我可以导出一些记录，并在重新创建db后重新加载。

你建议使用update-database并指定Azure连接字符串？

解决方案

我最后在这里做的可能不是最简单的方法，但是可能会帮助某人。

---

首先，虽然他提出的是有趣的，但在我的情况下没有工作， http：// robertgreiner .com / 2012/05 / using-entity-framework-database-migrations-to-update-a-remote-database / 。我应该已经意识到了，但是可以通过使用Azure连接字符串在Azure数据库上运行 update-database 命令。

确保将数据库缩放到标准级别或更高级别，以避免命令超时和斑点操作。基本和Web不适用于任何有用的，请注意，您可以更改

可以通过复制和粘贴获取Azure连接字符串从发布设置（右键单击您的项目，选择发布，然后选择设置），需要的-ConnectionProviderName是 System.Data.Sqlclient ，所以命令如下所示：

update-database -ConnectionString
Data Source = tcp：'your-sql-server' .database.windows.net，1433; Initial Catalog ='your-db-name';
用户ID = user @'your-sql-server'; Password ='sql-server-pwd'

---

由具有两个数据库上下文ApplicationDbContext和我的StoreInitialTestContext的示例项目增加了复杂性。事实上，每个上下文都有一个本地的数据库，但是在Azure上只有一个数据库，所以很难使用 DropCreateDatabaseAlways 或 DropCreateDatabaseIfModelChanges 初始化器。

我最终编写了最大数据库表的内容，以便稍后恢复。

接下来，我删除了整个Migrations目录，这个目录有效地禁止迁移（正如我们在这里所指出的那样，没有 disable-migrations 命令）。

接下来，我为 StoreInitialTestContext 设置了一个数据库初始值设置，如下所示：

  public class StoreInitialTestContextInitializerCreate：DropCreateDatabaseAlways< StoreInitialTestContext> 
 {
 protected override void Seed（StoreInitialTestContext context）
 {
 //现在没有
} 
} 
  

另外，将其注册为适当上下文的数据库初始值设置：

  public class MvcApplication：System.Web.HttpApplication 
 {
 protected void Application_Start（）
 {
 
 //删除StoreInitialContext数据库和如果模型更改，则重新创建
 Database.SetInitializer< StoreInitialTestContext>（new StoreInitialTestContextInitializerCreate（））; 
  

我曾经重新创建了包含 StoreInitialTestContext

一旦本地数据库被完全清理，我转移到Azure。

我去了门户并删除了数据库，以便下次我的网站在Azure上运行时可以重新创建。

我确保设置在发布配置文件中设置为更新数据库，但注意到代码优先迁移选项不可用。

下一次网站在Azure上运行时，实体框架没有看到数据库，所以它是创建的。

我确实通过列出项目来运行我自己的上下文，并通过注册为用户来运行aspnet身份部分。检查Visual Studio中的SQL Server对象资源管理器确认所有表都已创建。

接下来，是时候从表中还原数据，但首先要避免Azure错误，我将我新创建的数据库设置为已停止Web数据库层到标准层。

然后我加载将脚本逐个导入Visual Studio，将连接字符串用户和密码输入到对话框中，并运行脚本。

只有几个表要更新，顺利进行，然后我准备好迎接我的下一个挑战。

The quick question is "How do I get my Azure database back in sync with my local database?"

Details: I have been using entity framework in my project, which really not that different from the ASP.NET MVC 5 sample project, which has Identity 2 built in.

There are two database contexts, one called ApplicationDbContext (aspnet_somethingUserDB) from Identity and the other one called StoreInitialTestContext (store_db) that is from my changes.

Interestingly, I have each of these contexts in a separate SQL database locally, and they are in just a single database, called store_db on the Azure service.

Things were working swimmingly, and the two local databases, which had the same table complement together as the single database on Azure, were in sync. The website worked well locally for debugging and on Azure.

After making a last change to the schema by adding a model, I did the update-database command and the local database was updated. Unfortunately, when I published the website to Azure, after selecting the context and the checkmark by "run code-first migrations...", the Azure database didn't update.

Looking at my code, I don't see any migrations that are pending, so I'm not sure how I get the Azure database back in sync.

As a last-ditch effort, I set my Azure database from Basic to Standard, and used the compare schema command between my local store_db and the Azure store_db. I made sure that only tables in the local store_db were updated by unchecking the tables having to do with aspnet_UsersomethingDb.

That operation wasn't successful, so I attempted to undo the changes to the local which were the problem, and which didn't get applied on Azure.

That too wasn't successful, so I adjusted my website to avoid the EntityFramework "model vs database differences" crash so that it would at least limp along.

At this point, my local database is fine, but turning off the "code first migrations" checkbox, the Azure database is reporting:

An exception of type 'System.InvalidOperationException' occurred in EntityFramework.dll but was not handled in user code

Additional information: The model backing the 'StoreInitialTestContext' context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269).

Turning on the checkbox, the report is:

An exception of type 'System.Data.SqlClient.SqlException' occurred in EntityFramework.dll but was not handled in user code

Additional information: There is already an object named 'ExtendedUserInformations' in the database.

This seems to indicate that code-first migrations want to add the object to get things in sync, but that the object already exists.

Is there just a quick way to get these databases in sync? There is not that much data in Azure, so if I have to, I can export some records and reload them after recreating the db.

Do you recommend using update-database and specifying the Azure connection string?

解决方案

What I ended up doing here was probably not the easiest way to do this, but it may help someone.

---

First, although what he proposed was interesting but didn't work in my case, a big shout to http://robertgreiner.com/2012/05/using-entity-framework-database-migrations-to-update-a-remote-database/ . I should have realized it, but the update-database command can be run on the Azure database as well by using the Azure connection string.

Make sure to have your database scaled to "standard' tier or above to avoid command timeouts and spotty operation. 'basic' and 'web' are not meant for anything "useful". Note that you can change the tier under the "Scaling" Azure settings for the database as often as you like.

One can get the Azure connection string by copy and pasting from Publish settings (right click on your project, select "Publish" and then select "Settings"), and the -ConnectionProviderName which is needed is System.Data.Sqlclient, so the command looks like this:

update-database -ConnectionString "Data Source=tcp:'your-sql-server'.database.windows.net,1433;Initial Catalog='your-db-name'; User ID=user@'your-sql-server';Password='sql-server-pwd'

---

The complexity is very much increased by the sample project having two database contexts, ApplicationDbContext and mine, StoreInitialTestContext. The fact that there is a database on the local side for each context, but only a single database on Azure, makes it difficult to use the DropCreateDatabaseAlways or DropCreateDatabaseIfModelChanges initializers.

I ended up scripting the contents of the largest database tables so that I could restore them later.

Next, I deleted the entire Migrations directory, which effectively disables migrations (As one had noted here, there is no disable-migrations Package Manager command).

Next, I set up a database initializer for the StoreInitialTestContext, like this:

public class StoreInitialTestContextInitializerCreate: DropCreateDatabaseAlways < StoreInitialTestContext > 
{
      protected override void Seed(StoreInitialTestContext context)
      {
        // nothing for now
      }
}

Also, register this as the database initializer for the proper context:

public class MvcApplication : System.Web.HttpApplication
{
    protected void Application_Start()
    {

        // Drop the StoreInitialContext database and recreate if the model changes
        Database.SetInitializer<StoreInitialTestContext>( new StoreInitialTestContextInitializerCreate());

This I used to recreate the database containing the StoreInitialTestContext upon the next run of the website.

Once the local database was completely cleaned up, I moved to Azure.

I went to the portal and deleted the database, so that it could be recreated next time my website was run on Azure.

I made sure that the settings in the Publish profile were set to update the database, but noted that the code-first migrations option was not available.

The next time the website was run on Azure, the entity framework saw no database, so it was Created.

I made sure to exercise both my own context by listing items, and the aspnet Identity portions by registering as a user. Checking the SQL Server Object explorer in Visual Studio confirmed that all tables had been created.

Next, it was time to restore the data from the tables, but first to avoid Azure errors, I set my newly created database, which had been created as a "defunct" "Web" database tier, to the "Standard" tier.

I then loaded the scripts into Visual Studio one by one, entered the connection string user and password into the dialog, and ran the scripts.

There were only a few tables to update, and that went smoothly, and I was then ready for my next challenge.

这篇关于丢失的迁移和Azure数据库现在不同步的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！