Recipe: deploy the SQL database to the remote host environment (Part 1)

[Original address] recipe: deploying a SQL database to a remote hosting environment (Part 1)
[Original article publication date] Friday, December 22,200 6 AM

Scenario:

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.AndData itself. Unfortunately, there has been no extremely simple way to achieve this in the past.

The good news is that this week, the SQL server product group released a new candidate version of the SQL server hosting toolkit, which will make 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 define your dataAndData can be easily migrated and installed to a shared remote host SQL Server account.

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. ItNot RequiredThe SQL Server version is the same. Therefore, you can use SQL express 2005 locally and upload it to the SQL 2000 server in the host environment without changing the 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 DBO permission during installation (install-Time), but many host providers do not support this permission (NOTE: When the script is running (runtime)NoDBO permission is required only during the install time, but this is sometimes an obstacle unless the host vendor is willing to install it for you ). On the other hand, when the database Publishing Wizard installs ASP. NET 2.0 members, role management, user information, and other data definitions and dataNoDBO permission is required. You should be allowed to deploy the aspnetdb data table and stored procedure as easily as using the database Publishing Wizard to deploy other databases.

Tutorial 1: 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 namedPersonal. MDFContains data tables and stored procedures specific to our website, such as photo and album, as well as basic content management support. Another SQL express database is namedAspnetdb. MDFContains 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.

At the beginning, click the Server Manager page in Visual Studio or visual web developer to view the database used by our program:

We can see in that we have used two SQL express databases: aspnetdb. MDF and personal. MDF. To generate. for SQL installation files, you only need to select the database in the Manager and press the right mouse to select "Publish to provider (publish to provider)" on it) "context menu items (added by the database Publishing Wizard ):

This will start the database Publishing Wizard, allowing us to generate database installation scripts step by step. As mentioned in the introduction section in this post, the database Publishing Wizard supports two deployment options: 1) generate. SQL installation script files, you can copy them to a remote host and run them using their existing management control panel tools, or 2) use the Web service provided by the host website to directly upload the database.

In this first tutorial, we will use the. SQL script file method. Therefore, retain the default single-choice button option and provide a name for the. SQL installation script file you want to generate:

After clicking "Next", the wizard provides options for you to customize some settings when generating the. SQL Installation File. Note: You can determine whether to delete existing objects in the target database, whether the script is for SQL 2000 or SQL 2005, whether to install data definition and data at the same time, or as long as the data definition, or as long as the data:

In this tutorial, retain the default options and click "Next" to generate a. SQL script:

Now you have a personal account. the SQL file contains a script that you can run on any SQL server. This script can be used to recreate data tables, stored procedures, views, full-text index directories, and import and create data in the database. all data records in the database corresponding to the SQL file.

The SQL file itself is a text file, so you can use any text editor to open it, view the content, or add your own statements for any customization:

Note that the above. the SQL file includes the ddl SQL statements required to create a photos table (including all the constraints of the table and the relationship between the primary key and the foreign key ), it also includes the SQL statement used to insert data into the table after the table is created. In the preceding example, an SQL statement is used to insert the binary data of a photo, because the data is stored in the database.

After repeated execution of the above steps on the aspnetdb SQL express database, you will have two. SQL installation scripts that you can use to automatically recreate your SQL database on any SQL Server:

 

Note that the. SQL file we created can be used to create two separate databases on one server,OrIt can also run in the same database to generate a single database that contains all the data tables, stored procedures, and data of your program. To achieve this, you only need to run these two scripts on the same database. If there is no conflict between the table name and the stored procedure name, you will have a single database that includes everything. This option is useful when your host account only provides one available database instance.

Step 3: Use Our. SQL file to create our remote database

So far, we have generated the. SQL file, and we can use it to install the database in our host environment. How we use the. SQL file to install the database depends on how the host supplier allows us to access our SQL account. Some host providers provide an HTML-based file upload tool that allows you to provide a. SQL file, and then they can execute this file in your own SQL database.

Other host providers provide an online query tool (as shown in) that allows you to copy/paste SQL statements to run in your database. If your host provider provides a similar online query tool, you can open it in a text editor. SQL file, copy/paste the content to the query text box, and then run these statements.

The quality of SQL tools provided by different host providers varies. When testing the database Publishing Wizard, we found that some custom SQL management tools provided by the host supplier could incorrectly analyze valid SQL statements, resulting in problems, especially for goto statements. This page describes a problem where you may have encountered a GOTO statement on some host vendor tools. To help improve the quality of SQL host management tools, the SQL server product group will release a free source code for SQL HTML management tools earlier next year, host vendors can integrate them into their interfaces. If successful, this will help improve the standard experience for all Windows host environments.

If your host supplier does not have an available HTML Web management tool that allows you to easily manage your SQL database, you can also compile a simple ASP. net web page, you can combine it with your. the SQL file is FTP together to your website, and then you can access the webpage. when a SQL file is read into the memory as a text file, it is converted into a string and passed to ADO.. net. This will give you the same results as the query analyzer above, and completely rebuild your database for you.

Step 4: Step 4: update the connection string in Web. config

Once data is uploaded in the database of our host environment, we need to upload our. aspx file, program set and other content (usually through FTP) to a remote website ).

The last step is to open our web. config file and update the <connectionstrings> section to point to the new database location of our remote host. Note: You need to obtain the exact SQL Server, database name, and user name/password from the host supplier.

Using the above personal starter kit as an example, we need to change the web. the default connection string in the <connectionstrings> section of the config file (originally two SQL express databases in the local/app_data folder ):

 

<Connectionstrings>
<Add name = "personal" connectionstring = "Data Source =./sqlexpress; Integrated Security = true; user instance = true; attachdbfilename = | datadirectory | personal. MDF"/>
<Remove name = "localsqlserver"/>
<Add name = "localsqlserver" connectionstring = "Data Source =./sqlexpress; Integrated Security = true; user instance = true; attachdbfilename = | datadirectory | aspnetdb. MDF"/>
</Connectionstrings>

 

To use a single SQL Server 2000 database (the scottgudb database on the server ).

<Connectionstrings>
<Add name = "personal" connectionstring = "Data Source = server123; initial catalog = scottgudb; Integrated Security = true" providername = "system. Data. sqlclient"/>
<Remove name = "localsqlserver"/>
<Add name = "localsqlserver" connectionstring = "Data Source = server123; initial catalog = scottgudb; Integrated Security = true" providername = "system. Data. sqlclient"/>
</Connectionstrings>

We can use a single database (instead of the above two databases), because we run two for a single database. in this way, all data definitions and databases are merged into a single database instance.

Step 5: complete

So far, we can remotely run our application in the host environment. It should work.

Summary

The Database Release wizard, as part of the SQL hosting toolkit, should make it easy to create a. SQL file for any database (SQL express or SQL Server. You can use it to easily dump your local database and re-build the same database on the remote system.

In future tutorials, I will demonstrate how you don't need it. the SQL file can be used to recreate the remote database (Instead, directly publish the database to your host environment through Web Service in VS). Please wait for the relevant details.

I hope this article will help you,

Scott

Note: We are planning to add the Database Release Wizard to Visual Studio "orcas" (next vs version). This means that you do not need to download the Database Release wizard separately during that period. But we have to make sure that you don't have to wait until that time. This is why we have provided it to Visual Studio 2005 and Visual Web Developer express 2005 today.

NOTE 2: visit this page to read other ASP. NET tips, tricks, and recipes I have previously written.

Tags: ASP. NET, Visual Studio,. net, Data, SQL Server, tips and tricks