如何对SQL server express进行实时更改

I've been developing an asp.net web app using VS studio. I'm using SQL Server Express. During development I have been testing my web app on my server.

我一直在开发一个使用VS studio的asp.net web应用程序。我使用的是SQL Server Express。在开发过程中，我一直在我的服务器上测试我的web应用程序。

Every time that I need to update my database I would simply delete my old database (located on my server) and upload my new DB. Since I'm only testing, and my app has no users, it hasn't been a problem.

每当我需要更新我的数据库时，我只需删除我的旧数据库(位于我的服务器上)并上传我的新数据库。因为我只是在测试，我的应用程序没有用户，所以这不是问题。

Problem:

Once my site goes live I don't know how to make changes to my DB. Obviously I wont be able to simply delete it as it will contain user data. So how do people typically update a live DB. That is, lets say my site is live and now I need to add more tables and stored procedures to my DB. How would i do this on a live site?

一旦我的站点上线，我就不知道如何修改我的DB了。显然，我不能简单地删除它，因为它将包含用户数据。人们如何更新动态数据库。也就是说，假设我的站点是活动的，现在我需要向我的DB添加更多的表和存储过程。我该如何在直播网站上做到这一点呢?

4 个解决方案

#1

To make changes to a production database, you'd:

要对生产数据库进行更改，您需要:

Try to schedule an outage when the least number of users will be affected, posting information so users are aware prior to
尝试在用户数量最少的时候安排一次中断，发布信息，以便用户在此之前知道
Use data definition language (DDL) scripts to make the changes to the database tables, and potentially data manipulation language (DML) scripts to massage existing data into what the changes need.
使用数据定义语言(data definition language, DDL)脚本对数据库表进行更改，并使用可能的数据操作语言(data manipulation language, DML)脚本将现有的数据调整为需要的数据。

The scripts necessary for step 2 should have been tested in Development and Test/QA environments to ensure as few issues as possible are experienced in the Production system. Backups that allow you to restore the database to previous versions of the application are required for both the Development and Test/QA environments.

第2步所需的脚本应该在开发和测试/QA环境中进行测试，以确保在生产系统中尽可能少地遇到问题。开发和测试/QA环境都需要备份，这些备份允许您将数据库恢复到应用程序的以前版本。

#2

You either need to:

你需要:

Update your database at a time when no-one will be using the site. OR
更新你的数据库时，没有人会使用这个网站。或
Ensure that your updates do not affect the operation of the site.
确保您的更新不会影响站点的操作。

The second option involves giving any new non-NULLable columns sensible defaults, ensuring that all INSERT statements use column lists (e.g. INSERT INTO dbo.MyTable (col1, col2, col3) VALUES (...)) and ensuring that new stored procedure parameters have defaults. This is not an exhaustive list, but a good start.

第二个选项涉及给任何新的非空列的合理默认值，确保所有插入语句都使用列列表(例如，插入到dbo中)。MyTable (col1、col2、col3)值(…)，并确保新的存储过程参数具有默认值。这不是一个详尽的列表，而是一个好的开始。

#3

Mostly we create release scripts whereby the changes are scripted out and repeatable. So you will see things like

大多数情况下，我们会创建发布脚本，在这个脚本中，更改被脚本化并可重复。你会看到类似的东西

IF NOT EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_NAME = 'myTableName' 
           AND  COLUMN_NAME = 'myColumnName')
BEGIN
     ALTER TABLE myTableName
     ADD myColumnName varchar(50)
END

You can tell which objects have changed with stuff like:

你可以通过以下内容来判断哪些对象发生了变化:

SELECT
[name],
create_date,
modify_date
FROM sys.tables
ORDER BY
modify_date DESC

Some people use RedGate software to compare the 2 different database, but there is a cost to that.

有些人使用RedGate软件比较两个不同的数据库，但这是有代价的。

#4

It depends on that kinds of changes you want to make to your live database.

这取决于您想要对您的实时数据库进行的更改。

In your question you only talk about adding new tables and stored procedures.
As long as you only add new stuff (tables, sprocs, or even new columns to existing tables) you don't have to do anything special, because SQL Server can do this while the database is in use and the changes don't affect your users because their version of your app doesn't even know about your newly added stuff.

在您的问题中，您只讨论添加新表和存储过程。只要你只添加新的东西(表、存储过程、甚至新列到现有表)你不需要做任何特别的,因为SQL Server可以在数据库使用和更改不会影响您的用户,因为他们的版本的应用程序甚至不知道你新添加的东西。

On the other hand, it gets way more complicated if you change or even delete existing stuff.
There is a great chance that this will be a breaking change for your app, as it will probably stop working when tables look different than it expects or if it tries to access tables/sprocs that don't exist anymore.
(Even if you only add new stuff like I said in the beginning - you will probably want to update your app anyway, so it can actually USE the new stuff in the database)

另一方面，如果你改变甚至删除现有的东西，事情就会变得复杂得多。这对你的应用来说是一个巨大的改变，因为它很可能在表看起来不像它期望的那样或者当它试图访问不再存在的表/sprocs时停止工作。(即使你只添加了我刚开始说的新东西——你可能还是想要更新你的应用程序，所以它实际上可以使用数据库中的新东西)

So you probably will need to make your database changes AND deploy a new version of your app as well, both at the same time.

因此，您可能需要对数据库进行更改，并同时部署应用程序的新版本。

I'm no ASP.NET expert, but as far as I know it's not possible to update an ASP.NET app without all active users getting kicked out.
So in this case you would have to do what OMG Ponies already said: choose a time when the least possible number of users is affected, and/or inform your users early enough about the scheduled outage.

我没有ASP。NET专家，但是据我所知，更新ASP是不可能的。没有所有活跃用户的网络应用被踢出。因此，在这种情况下，您必须做OMG小马已经说过的事情:选择一个可能影响最少用户的时间，并/或尽早通知您的用户计划停机。

#1