用DPW(Database Publishing Wizard)進行SQL Server數據庫發(fā)布

很多數據庫都提供提供數據庫定義和數據導入功能，但不幸的是SQL Server卻沒有，導出成SQL語句只會到處數據庫對象的定義，并不包含數據，為了達到導出數據的功能，我們通常只能通過數據庫附加或者備份的功能實現，而無法像MySQL那樣利用一個SQL語句就能完成數據庫的定義和數據導入。

好了，SQL Server現在也提供了這種功能了，它用到了一個軟件數據庫發(fā)布向導(Database Publishing Wizard)，它可以免費獲取，該向導支持2種數據庫主機部署場景：

1) 數據庫發(fā)布向導允許你指向一個你正在本機上操作的數據庫，然后自動生成一個.SQL腳本文件，該文件包含了在任何遠程系統(tǒng)上重建一個當前數據庫的完整拷貝所需的安裝邏輯。這個.SQL腳本包括了需要創(chuàng)建數據庫定義(表，視圖，存儲過程，觸發(fā)器，全文索引目錄，角色，規(guī)則等等，可在此處參考所有細節(jié))，以及把與你本地數據庫同樣的數據內容填充到新數據庫里去的所有的東西(這類似于MySQL 的dump工具)。把這些安裝邏輯封裝在單個.SQL 文件的好處是，大多數主機供應商已經支持上傳 .SQL 文件到他們的主機環(huán)境，并且通過他們的管理控制面板運行這些腳本的能力。假定你現有一個支持這個功能的web主機供應商，你可以馬上開始使用數據庫發(fā)布向導來輕松地部署你的網站，而不需要主機供應商安裝或配置什么東西。

2) 數據庫發(fā)布向導也允許你指向一個你正在本機上操作的數據庫，然后使用 web service 向你的遠程主機環(huán)境遷移和重建數據庫(而不用創(chuàng)建一個.SQL 文件或者使用主機供應商的管理控制面板運行這個文件)。不過，這個發(fā)布選項需要主機環(huán)境提供SQL發(fā)布web-service。 SQL Server Hosting Toolkit包含了一個免費的SQL發(fā)布web-service實現，我們將與主機供應商密切合作來部署這個web-service。

數據庫發(fā)布向導允許你在本地使用 SQL Express 或 SQL Server 2000/2005 ，然后在遠程主機環(huán)境中使用SQL 2000 或 SQL 2005。它不要求SQL服務器的版本是一樣的，所以，你可以在本地使用SQL Express 2005，然后上傳到主機環(huán)境中的SQL 2000服務器，而不用改動任何編碼。

數據庫發(fā)布向導也支持對內置的ASP.NET 2.0成員，角色管理，用戶信息和健康監(jiān)測等數據定義的處理。許多人遇上問題，因為隨 ASP.NET一起發(fā)布的來建立這些數據定義的內置.SQL 腳本在安裝時(install-time)需要DBO權限，但很多主機供應商對此并不支持(注：腳本在運行時(runtime)不需要DBO權限，只是在安裝時(install time)才需要，但這有時會是個障礙，除非主機供應商愿意為你安裝)。而在另一方面，數據庫發(fā)布向導在安裝ASP.NET 2.0成員，角色管理，用戶信息等數據定義和數據時，并不需要DBO權限，應該允許你，和使用數據庫發(fā)布向導部署其他數據庫一樣，輕松地部署 ASPNETDB數據表和存儲過程。

最后需要說明的，如果你不是使用VS2005、沒有安裝SQL Server2005你可能還需要安裝其它組件：

Microsoft SQL Server Native Client  
下載地址：http://download.microsoft.com/download/a/c/3/ac37a75a-1e0b-49ba-aea1-0f9b258e4965/sqlncli.msi

Microsoft SQL Server 2005 Management Objects Collection
下載地址：http://download.microsoft.com/download/f/7/4/f74cbdb1-87e2-4794-9186-e3ad6bd54b41/SQLServer2005_XMO.msi

最主要的東東，Database Publishing Wizard
下載地址：http://download.microsoft.com/download/2/1/b/21b68714-3d9a-4610-9eba-88509ffc27fb/DatabasePublishingWizard.msi

具體的安裝順序了全部軟件如下：

1、Microsoft .NET Framework Version 2.0

2、Microsoft Core XML Services (MSXML) 6.0

3、Microsoft SQL Server Native Client

4、Microsoft SQL Server 2005 Management Objects Collection

5、Microsoft SQL Server Database Publishing Wizard 1.1

以上5個下載后全部安裝，注意按照順序。

以下是具體的操作方法，是英文的，就不翻譯了。原文在Publishing a Database Using the Database Publishing Wizard。

This tutorial shows you how to publish a database using the Database Publishing Wizard (DPW).

To Publish Your Database Using the DPW

1. Launch the Database Publishing Wizard Client. If the welcome screen displays, check the Do not show this starting page again box.
2. Click Next.

   

3. On the Select Source Server screen, enter the connection information for your local SQL server.

   Note: This is NOT the same information as that used for your hosted server. If you are publishing from an instance of SQL server running on your desktop or local network, you use the Specify server information section. If you are publishing from a remote database, connect via a connection string. For specific connection information, contact the database server owner.

   

4. Once you have entered the connection details, click Next. This step brings up the Select Database screen, displaying a list of all databases available on your local server.

   Note: If you do not see the database you want to publish, press Back to return to the Select Source Server page and then return to this page without changing connection details. If this step does not work, contact the server owner for support.

   

5. Select the database you want to publish. If your database contains prohibited objects, such as full text catalogs and assemblies, or you do not want to publish all objects, uncheck the Script all objects in the selected database.
6. Press Next.
7. If you are not publishing all objects, the Choose Objects Type appears.

   If You Are Not Publishing All Objects

   1. Select the Object types to be published. You will be able to pick specific objects of each type on later screens.

      

   2. Click Next. This step brings up the Choose screen for each object type selected (for example, Tables and Views). If you wish to publish all objects of a given type, click Select All.

      

   3. Once you have selected all objects to be published, press Next to advance. Repeat this step until you reach the Select an Output Location screen.
8. The Select an Output Location screen appears.

   

9. Select the Publish to Shared hosting provider option. Select the appropriate Provider and Target Database from the drop down lists.
10. If you have not yet configured a connection or need to make a new connection, press More. The Hosting Providers dialog displays.

    

    If You Have Not Configured a Connection

    1. Press New. The Provider Configuration dialog displays.
    2. Enter the following values:

       Name:

       Enter any name to represent the connection to the hosted service. You will enter names for specific databases later.

       Web Service Address:

       Enter the URL for the publishing service. The URL can be found in the database information section of your hosting control panel.

       Note: The service requires a secure (HTTPS) connection to protect data. If you attempt to connect using a non-secure connection, the connection will fail and you will be prompted to use a secure connection.

       User name:

       Enter your FTP account user name.

       Password:

       Enter your FTP account password.

       Note: Checking “Save Password” causes the application to store your password locally. If this is a public or shared machine, this may not be a secure choice.

       

    3. To enter a database, press New. This displays the Database dialog.
    4. Enter the appropriate configuration values. The configuration values can be found in the database information section of your hosting control panel.

       Server Name:

       Enter the full path to the database server.

       Database Name:

       Enter the name of your hosted database.

       Note: The database must be owned by the FTP account entered in the provider configuration. If you have multiple hosting accounts, be sure to use the correct credentials when configuring the Publishing Wizard.

       User Name:

       Enter your database user name. This is the same user name used to access the SQL Web Manager.

       Password:

       Enter your database password. This is the same password used to access your SQL Web Manager.

       Note: Checking Save Password causes the application to store your password locally. If this is a public or shared machine, this may not be a secure choice.

       

    5. Click OK to complete the database set-up. Repeat this step for each database you want to configure. If you have multiple databases owned by a single hosting account, they can all be entered under a single hosting provider entry.
    6. When you have completed entering databases, click OK on the Provider Configuration dialog to return to the Hosting Providers list. You can test your connection settings by pressing Test. This feature validates the connection to the web service but does not test connectivity to the database itself.
    7. Click OK to return to the Select an Output Location dialog. If the appropriate provider and database are not selected, select them in the drop down lists.
    8. Click Next to advance to the Select Publishing Options screen. Before this page is displayed, the service validates the configuration settings by connecting to the publishing service and the target database. If a connection cannot be established, an error message displays and the application remains on the Select an Output Location screen.
11. The Select Publishing Options page is pre-populated with the appropriate default values. In most cases, these do not need to be modified.

    Drop existing objects in script:

    If True, the wizard checks if a published object exists in the database and, if found, drops the existing version before publishing.

    Note: Any data contained within dropped objects will be lost.

    If False, the wizard does not drop existing objects before inserting or modifying their structure. If an object of the type and name specified already exists, the wizard proceeds directly to data insertion. If the object does not exist, it will be created.

    Note: If the target database contains an object of the same name and type but with a different structure (for example, a table with a different number of columns), a publishing error may occur. Certain conditions may not report an error (for example, the database being published contains a table with fewer columns than the existing table). In such cases, the published database will not exactly match the local database.

    Publish using transaction:

    If True, the database is published as a single transaction. If an error occurs, all changes are rolled back.

    If False, published changes are committed as they are published. If an error occurs, the database may be left in a partially published state.

    Schema qualify:

    If True, objects are fully schema qualified in the script.

    If False, only the object name is used. It is recommended this option be True.

    Script for target database:

    Specifies the version of the target database. This must match the server version or publish will fail.  The wizard sets this value to the correct version.

    Types of data to publish:

    This section specifies what type of information to publish.

    Schema only publishes the structure of objects.

    Data only populates tables.

    Schema and data publishes the structure of objects and populates tables.

    

12. Click Next to advance. This step brings up the Review Summary page, which displays the selected options and configuration. This is the last chance to review before publishing begins.

    

13. Click Finish to begin publishing.

The Database Publishing Progress screen tracks the progress of the publish.  Based on the options selected, the wizard displays some combination of the following steps:

Gathering List of Objects to script:

The wizard is communicating with your local SQL Server and determining what objects must be scripted, including any objects you selected and their dependencies.

Connecting to web service:

The Wizard is connecting to the web service and establishing a publishing session.

Scripting objects:

The Wizard is performing preliminary scripting of the objects to be published.

Processing:

The wizard is completing local scripting of an object and transmitting it to the Web service. The Web service is executing the script against your hosted database server. The status message updates based on the specific object being published at a given instance.

The amount of time to publish a database varies based on complexity of the database, number of objects, and the amount of data in tables. Most databases complete publishing in under 30 minutes.

It is possible to abort processing at any point by pressing Stop. If an error occurs, a red link to more details is provided in the Message column. If publishing is aborted or an error occurs, you can use Back to move back through the screens and modify settings.

If publishing succeeds, press Close to close the application. Your database is now transferred to the hosting server and ready for use.