如何以编程方式使用现有数据库/模式作为模板的Asp.Net应用程序部署数据库？ [英] How to deploy a database programmatically from an Asp.Net application using an existing database/schema as a template?

问题描述

问：

我创建一个Asp.Net应用程序，它具有为每个租户独立的数据库多租户数据架构。我需要能够创建和编程方式从应用程序部署数据库时，一个新租户签约。什么是保持模板数据库/模式/脚本，并用它来部署服务器上的新的数据库的最佳方式。

注意：

我使用的.Net 4.0应用程序和SQL Server 2008 R2服务器。

当前解决方法：

目前默认模板数据库将保留在服务器上。是对数据库的修改通过Visual Studio做是为了保持通过源代码控制版本。当一个新的数据库需要部署：

1. 创建使用服务器上的一个新的数据库 Database.Create

2. 然后使用<一个href="http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.transfer.transferdata.aspx"相对=nofollow> Transfer.TransferData 超过转让只是模式到新的数据库。或者，使用<一个href="http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.transfer.scripttransfer.aspx"相对=nofollow> Transfer.ScriptTransfer 并存储在内存中的脚本后续部署。

解决方案

您的部署应根据上从头开始创建数据库脚本。任何架构修改你做，使它通过升级数据库架构的脚本。请参见版本控制，并且数据库以更加延长讨论的话题。

做基础的部署让你在对如何处理差异的工具决定摆布比较工具的选择（大桌，这会造成非常危险的，如果该工具决定以副本为基础修改），也让你摆布任何的支持比较工具具有自动化，这使得失败/重试处理，我应该怎么把它......好玩。

Question:

I am creating an Asp.Net application that has a multi-tenant data architecture with separate databases for each tenant. I need to be able to create and deploy databases programmatically from the application when a new tenant signs up. What is the best way to maintain a template database/schema/script and use it to deploy a new database on the server.

Note:

I am using .Net 4.0 for the application and SQL Server 2008 R2 for the server.

Current Workaround:

Currently a default template database is maintained on the server. Changes to the database are done through Visual Studio to maintain versioning through source control. When a new database needs to be deployed:

1. Create a new database on the server using Database.Create.

2. Then use Transfer.TransferData to transfer just the schema over to the new database. Or, use Transfer.ScriptTransfer and store the script in memory for subsequent deployments.

解决方案

Your deployment should be based on scripts that create the database from scratch. Any schema modification you make, make it through a script that upgrades the database schema. See Version Control and Your Database for a more extended discussion of the topic.

The alternative of doing diff tools based deployment put you at the mercy of the tools decision on how to handle the diff (with large tables, this can get very dangerous if the tool decided to to a copy-based modification) and also puts you at the mercy of whatever support the diff tool has for automation, which makes the failure/retry handling, how should I put it,... 'fun'.

这篇关于如何以编程方式使用现有数据库/模式作为模板的Asp.Net应用程序部署数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！