How to deploy a SQL database to a remote host environment

you've developed a proud asp.net application, everything has been tested and everything is working on your machine, taking full advantage of the new features of members, roles and user information in ASP.net 2.0, ready to publish it to a remote host environment, Share this application with the whole world.

Copying an. aspx file and a compiled assembly to a remote system is easy, as long as you are using FTP or copying files directly up. But many developers face the challenge of erecting and rebuilding database content on remote host sites, including data definitions and data itself. Unfortunately, there has been no very simple way to achieve this goal in the past.

The SQL Server product group has released a release candidate for the new SQL Server Hosting Toolkit, which makes it easy to deploy your SQL solution to a 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 to a shared remote host SQL Server account.

The following sections describe how you can start using this toolkit.

SQL Server Hosting Toolkit

SQL Server Hosting Toolkit is available for free, along with a Database Publishing Wizard (db Publishing Wizard), which supports 2 database host deployment scenarios:

1 The Database Publishing Wizard allows you to point to a database that you are operating on the computer and then automatically generate one. SQL script file that contains the installation logic required to reconstruct a full copy of the current database on any remote system. This one. SQL scripts include the need to create database definitions (tables, views, stored procedures, triggers, full-text indexed catalogs, roles, rules, and so on, can refer to all the details here, as well as all the things that populate the new database with the same data content as your local database (this is similar to the MySQL dump tool). Encapsulate these installation logic in a single. The advantage of SQL files is that most host vendors have already supported uploads. SQL files to their host environment, and the ability to run these scripts through their admin control Panel. Assuming you have a web hosting provider that supports this functionality, you can immediately start using the Database Publishing Wizard to easily deploy your site without requiring a host vendor to install or configure anything.

2 The Database Publishing Wizard also allows you to point to a database that you are operating on your computer and then use the Web service to migrate and rebuild the database to your remote host environment (without creating one.) SQL file or run this file using the host vendor's admin Control Panel. However, this release option requires the host environment to provide SQL publishing Web-service. SQL Server Hosting Toolkit contains a free web-service implementation of SQL Publishing, and we will work closely with our host vendor 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 the SQL Server version to be the same, so you can use SQL Express 2005 locally and upload it to the SQL 2000 Server in the host environment without altering any encoding.

The Database Publishing Wizard also supports processing of data definitions for built-in ASP.net 2.0 members, role management, user information, and health monitoring. Many people encounter problems, because with the ASP. NET to establish the built-in of these data definitions. SQL scripts require dbo permissions at installation time (Install-time), but are not supported by many host vendors (note: Scripts do not require dbo permissions at run time (runtime), only at installation time (install Time) is required, but this can sometimes be an obstacle unless the host vendor is willing to install it for you. On the other hand, the Database Publishing Wizard does not require dbo permissions when installing data definitions and data such as ASP.net 2.0 members, role management, user information, and should allow you to easily deploy ASPNETDB datasheets and stored procedures just as you would deploy other databases using the Database Publishing Wizard.

Use. SQL file to deploy SQL Express database to a SQL Server Host Account

Over the next few weeks, I'll be writing a series of posts demonstrating how to use the functionality of 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, and then you can copy it to a remote host account and use it to re-establish a SQL Server database for your site.

Starting preparation: Downloading and installing the Database Publishing Wizard

The first step we need to make is to confirm that we have installed the Database Publishing Wizard in SQL Hosting Toolkit. Click here to download the installation.

The Database Publishing Wizard has both a graphical Interface wizard and a command line tool. The graphical Interface wizard can run either individually or through Context-menu support added to Visual Studio 2005 and the visual Web Developer Express Solution Manager. For the purposes of this tutorial, we will use the integration approach of the following solution manager, which makes publishing extremely easy.

First step: Create a asp.net web site that uses local SQL Express or SQL Server databases

To facilitate this demonstration, we will use the built-in Personal Starter kit template with VS (VS) and visual Web Developer Express (VWD). To create a new Web project based on this template, select the file-> new site in VWD or VS, and then select the Personal Starter Kit template in the New Web Site dialog box. By default, this personal Starter Kit application is configured to use SQL Express (this database is free and can be downloaded here). After execution, the following illustration shows:

　　

After you create the application, you can select "website"-> ASP in Vwd/vs. NET configuration menu item to run the Web Administration tool, create a new user, and add the user to the "admin" role of the site. Then you can log in to the new Admin user account, try uploading new photos or customizing the existing photos on the site (note that, in so doing, the photo's metadata and the original photo's binary data are stored in the database):

　　

After the above steps are completed, we will have 2 SQL Express databases installed in our project's App_Data folder. One of the SQL Express databases, named Personal.mdf, contains data tables and stored procedures that are specific to our web site, such as photo (photo) and album (album) tables, and basic content management support. Another SQL Express database named Aspnetdb.mdf, contains the default ASP.net 2.0 member, role and user information provider database storage, used by our application for login and management purposes.

Step Two: Create for our database. SQL Setup Scripts

So we've created new applications and local databases, added custom data to the database, like new user accounts and their role members, new photos and albums, and we want to deploy this application to a remote host server.

The first step we have to make is to create. SQL script files that will allow us to automatically rebuild identical database definitions and database content on a remote host account. We will do this by using the Database Publishing Wizard (DB Publishing Wizard) that is installed on this computer as part of the SQL Hosting Toolkit.