How to deploy the SQL database to a remote host environment

How to deploy the SQL database to a remote host environment

You have developed a proud ASP. NET application, all things have been tested and everything works normally on your local machine, making full use of ASP. NET 2.0 members, roles, user information, and other new features, everything is ready, ready to publish it to a remote host environment, share this application with the world.

It is very easy to copy the. aspx file and the compiled assembly to a remote system. You only need to use FTP or directly copy the file up. However, many developers face the challenge of setting up and rebuilding database content on remote host sites, including data definition and data itself. Unfortunately, there has been no extremely simple way to achieve this in the past.

The SQL Server product group has released a candidate version of the new SQL Server Hosting Toolkit. This Toolkit will make it easy to deploy your SQL solution to the remote host environment. This toolkit allows you to operate SQL Express, SQL Server 2000, and SQL Server 2005 databases locally, and then easily migrate your data definitions and data, install the SQL Server account on a shared remote host.

The following content describes how you can start using this toolkit.

SQL Server Hosting Toolkit

The SQL Server Hosting Toolkit is available for free. A Database Publishing Wizard is also released. This Wizard supports two Database Host deployment scenarios:

1) The database Publishing Wizard allows you to point to a database that you are operating on the local machine, and then automatically generate. SQL script file, which contains the installation logic required to recreate a full copy of the current database on any remote system. This. the SQL script includes the need to create database definitions (tables, views, stored procedures, triggers, full-text index directories, roles, rules, and so on ), and all the things that fill the same data content as your local database into the new database (similar to the MySQL dump tool ). These installation logic is encapsulated in a single. the advantage of SQL files is that most host providers already support uploading. SQL file to their host environment, and the ability to run these scripts through their management control panel. Assume that you have a web host vendor that supports this function. You can use the database Publishing Wizard to deploy your website easily without the need for the host supplier to install or configure anything.

2) The database Publishing Wizard also allows you to point to a database that you are operating on the local machine, and then use web service to migrate and recreate the database to your remote host environment (instead of creating one. SQL file or run this file using the management control panel of the host supplier ). However, this release option requires the host environment to provide SQL to publish web-service. The SQL Server Hosting Toolkit contains a free web-service implementation for SQL release. We will work closely with the host supplier to deploy this web-service.

The database Publishing Wizard allows you to use SQL Express or SQL Server 2000/2005 locally, and then use SQL 2000 or SQL 2005 in a remote host environment. It does not require that the SQL Server version be the same. Therefore, you can use SQL Express 2005 locally and upload it to the SQL 2000 server in the host environment without modifying any encoding.

The database Publishing Wizard also supports the processing of built-in ASP. NET 2.0 members, role management, user information, health monitoring, and other data definitions. Many people encounter problems because ASP. NET together to build the built-in data definition. the SQL script requires the DBO permission during installation, but many host providers do not support it (Note: The DBO permission is not required for the script at runtime, the installation time is only required during installation, but this is sometimes an obstacle, unless the host vendor is willing to install it for you ). On the other hand, the database Publishing Wizard installs ASP. the DBO permission is not required for data definition and data such as NET 2.0 members, role management, and user information. You should be allowed to deploy other databases like using the database Publishing Wizard, easily deploy ASPNETDB data tables and stored procedures.

Deploy the SQL Express database to an SQL Server host account using the. SQL File

In the next few weeks, I will write a series of posts to demonstrate how to use various functions in the SQL Server Hosting Toolkit. The first tutorial in this series discusses how to use it to easily generate a local SQL Express database. SQL Installation File. Then you can copy it to a remote host account and use it to re-create an SQL Server database for your website.

Start preparation: download and install the database Publishing Wizard

The first step is to confirm that we have installed the database publishing wizard in the SQL Hosting Toolkit. Click here to download and install.

The database Publishing Wizard provides both a graphical wizard and a command line tool. The GUI wizard can be run independently or to support context-menu added to the solution manager of Visual Studio 2005 and Visual Web Developer Express. For the purpose of this tutorial, we will use the Integration Method of the solution manager below, which makes publishing extremely easy.

Step 1: Create an ASP. NET Website that uses the local SQL Express or SQL Server database

To facilitate this demonstration, we will use the built-in Personal Starter Kit template with VS 2005 (VS) and Visual Web Developer Express (VWD. To create a new web project based on this template, select File> new website in VWD or VS, and then select the Personal Starter Kit template in the new website dialog box. By default, this personal starter kit application is configured to use SQL Express (this database is free of charge and can be downloaded here. After execution, as shown in:

　　

After creating an application, you can select "website"> "ASP. to run the web management tool, create a new user, and add the user to the "admin" Role of the website. Then you can log on to the new administrator user account and try to upload new photos or customize existing photos on the website (note that, in this case, the metadata of the photo and the binary data of the original photo are stored in the database ):

　　

After the above steps are completed, we will have two SQL Express databases installed in the \ app_data folder of our project. One of the SQL Express databases is named personal. mdf contains data tables and stored procedures specific to our website, such as photo and album, as well as basic content management support. The name of another SQL Express database is aspnetdb. mdf, which contains the default ASP.. NET 2.0 members, role and user information providers' database storage, which is used by our applications for login and management.

Step 2: Create a. SQL installation script for our database

So far, we have created a new application and local database, and added custom data to the database, such as new user accounts and their role members, as well as new photos and albums, we want to deploy this application on a remote host server.

The first step is to create a. SQL script file, which will allow us to automatically recreate the same database definition and database content on the remote host account. We will use the Database Publishing Wizard installed on the local machine as part of the SQL Hosting Toolkit.