Deploying a Local Database to a Remote Web HostBy Scott Mitchell
Microsoft Visual Web Developer and SQL Server 2005 Express Edition make it easy to design, develop, and test ASP.NET web applications locally. In my books, tutorials, and classes, the explanations, examples, and assignments often use these tools to develop web applications locally. After creating a usable, interesting web application, readers and students invariably ask, "How do I get this website on the Internet?" Web applications designed for personal use, as a hobby, or for small- to medium-sized businesses are typically hosted by a web host provider. A web host provider is a company that has a plethora of computers that are accessible from the Internet. For a monthly fee, you can upload your web application to a web hosting company's servers to make your site available.
Deploying a web application from your local development machine to the web host company's servers requires uploading the files and information needed to serve your site. Many of these files can simply be uploaded to the web hosting company's servers using FTP or FrontPage Server Extensions (FPSE). What is more challenging is uploading a Microsoft SQL Server database from the local development machine to the remote web host. This challenge stems in part from the fact that most web hosting companies disallow use of Microsoft SQL Server 2005 Express Edition and instead require that you use a database on their database server (which might be SQL Server 2000 or 2005). Therefore, to deploy your site you will need to somehow duplicate your database's schema and data on the web hosting company's database server.
In short, duplicating a database's schema and data often means obtaining the T-SQL scripts to create the schema and add the data and executing that script on the remote database server. Fortunately, this process is greatly simplified by Microsoft's SQL Server Database Publishing Wizard. This wizard allows you to create the script needed to publish a database directly from within Visual Studio. Read on to learn more!
Downloading and Installing the SQL Server Database Publishing Wizard
Microsoft's SQL Server Database Publishing Wizard provides a step-by-step wizard for deploying a local database to a remote environment. The wizard can generate the T-SQL script to create just the schema, or the schema and table data. In this article we will look at a real-world application and examine how to push up the functionality to a remote database server using the Database Publishing Wizard. The code and database that we will deploy in this article is available for download at the end of this article. To follow along, however, you will need to first download the Microsoft SQL Server Database Publishing Wizard. This article's directions and screenshots refer to version 1.1 of the Database Publishing Wizard.
|A Separate Download for Now, But Part of Visual Studio Later...|
|While the Database Publishing Wizard must be downloaded from Microsoft for the time being, it will eventually become part of Visual Studio and therefore will be installed when installing Visual Studio. At the time of this writing (May 9th, 2007), the word is that this tool we be part of Visual Studio code name "Orcas," which is slated to be officially released in late 2007 or early 2008.|
Examining the Sample Application
In my book Sams Teach Yourself ASP.NET 2.0 in 24 Hours, I walk readers through creating a picture album website. This website uses ASP.NET 2.0's Membership system's
SqlMembershipProivderto manage user information. As such, the database contains the tables, views, and stored procedures needed by the
SqlMembershipProvider. In addition to these database objects, there are also three additional tables used by the application:
Categories- each user can create an arbitrary number of categories and optionally associate a picture with a category.
Pictures- this table contains a record for each uploaded picture. It stores what user uploaded the picture, what category (if any) the picture belongs to, when the picture was uploaded, and its title and description.
Comments- authenticated users can add their comments to any picture. These comments are stored in this table, and include information on who made the comment, the picture the comment is about, the date and time the comment was made, and the comment's subject and body.
|Getting Started with a Web Hosting Company|
If you have not yet decided upon what web hosting company to use, you will need to do so before you can deploy your
site. There are a many web hosting companies, ranging from small one-man shops to publicly-traded ones. The prices
can differ greatly, from as low as $5 per month to several hundred dollars per month, depending on features and service.
When deciding upon a web hosting company make sure that they support ASP.NET applications. If you are creating data-driven
applications, also ensure that the plan you sign up for includes a Microsoft SQL Server database.
Some web hosting companies I have used in the past include: WebHost4Life, ServerIntellect, and ORCSWeb. There are also sites like HostIndex that let you search a database of web hosting companies and filter results by criteria like price, whether they support ASP.NET applications, and so on.
Deploying the Website to a Remote Web Host
Deploying the web application from your local machine to a web hosting company's servers involves three steps:
- Copying the ASP.NET-related files to the web hosting company's servers.
- Duplicating the database's schema and data.
- Configuring the website as needed. This includes updating the
Web.configon the live server so that it references the web hosting company's database server (rather than the database information used in local development). It might also include other assorted configuration steps to properly setup to indicate that this is an ASP.NET 2.0 application. Consult your web hosting company for further information and help.
App_Datafolder. The application available for download at the end of this article includes a single database file in
ASPNETDB.MDF, which contains the schema for the
Picturestables. This database also contains a bit of data: there are three users registered on the site and a total of five uploaded pictures.
Let's use the Database Publishing Wizard to duplicate this database schema and data on a remote server. In the Server Explorer, right-click on the database name and choose the "Publish to provider..." menu option.
This launches the Database Publishing Wizard. The first step asks us to select which database to publish and whether we want to script all objects in the selected database. Select the appropriate database, leave the "Script all objects in the selected database" checkbox checked, and click Next. (If you uncheck the checkbox the subsequent screens will ask you what types of database objects you want to publish - database roles, schema, stored procedures, tables, and/or views - and then which specific roles, schemas, stored procedures, tables, and/or views to publish.)
With our current selections, the Database Publishing Wizard will generate the T-SQL script necessary to create the database roles, schemas, tables, stored procedures, and views. The wizard can save this script to a file. The script can then be executed against your web hosting company's database server either through SQL Server Management Studio or a web page (more on this later). As the screenshot below shows, in addition to saving the script as a file, you can also have it applied directly to the remote web host. In order for this second option to be used, your web hosting company must provide a web service that is specifically designed for the Database Publishing Wizard. Consult your web hosting company to see if this is an option and, if so, the information needed to connect to this web service.
If you are following along, choose the first option and save the T-SQL script on your computer's file system and click Next.
The next screen allows you to specify publishing options, such as whether the script should include
whether the remote database is SQL Server 2000 or SQL Server 2005, and whether you want to publish just the schema or the
schema and the data. Go ahead and leave the default options specified and click Finish to complete the wizard.
After clicking Finish, the wizard will display a status screen, indicating its progress as it scripts the database. Assuming everything works as expected, once the wizard completes you will have a T-SQL script file that contains the T-SQL statements for dropping the objects (if they exist), creating the tables, views, and stored procedures, and adding the data (if this option was selected). All that remains now is to execute this SQL script on the remote database!
Executing the T-SQL Script Created by the Wizard on the Remote Database
In order to propagate the local database to the remote database server we need to execute the T-SQL script on the remote database server. This can be accomplished in a number of ways. If you can connect to the database through SQL Server Management Studio, then you can do so and execute the script from there. If you have installed Microsoft SQL Server 2005 on your development machine then you likely already have SQL Server Management Studio on your machine. If you have just the Express Edition of Microsoft SQL Server 2005 installed, you can download and use the free SQL Server Management Studio Express Edition.
Another option is to FTP your script to your remote website and then create and run an ASP.NET page that opens the script file and executes the T-SQL commands against your database at the web hosting company. Microsoft has created such an ASP.NET web page. See Upload T-SQL and execute at your hosting provider using an ASP.NET page for the code and instructions. Additionally, see the resources in the Further Readings section at the end of this article for more advice and examples.
Updating Your Remote Website's Connection String Information
After uploading your website to your web hosting company's servers and duplicating the database, don't forget to update your web application's connection string information so that it uses the remote database server rather than your local one. For example, the demo code available at the end of this article uses a Microsoft SQL Server 2005 Express Edition database in the
App_Datafolder for local development. Consequently, the
Web.configfile, which holds the connection string information, has the following entry:
Note that the connection string references the
SQLEXPRESS data source and indicates the database file via the
AttachDbFilename setting. Once you've duplicated the database on the remote server, you'll need to update
the remote website's
Web.config file to reference the remote database server. This might result in changing
<connectionStrings> section to something like:
The exact settings for
Pwd would depend on
your database account information. Consult your web hosting company for details specific to your website and for example
connection string syntax.
|IMPORTANT: The Database Publishing Wizard Script Recreates the Existing Database Objects|
Keep in mind that if the "Drop existing objects in script" option is set to True (the default) then the script
generated by the Database Publishing Wizard will drop the tables, stored procedures,
and views before recreating them. Consequently, any data in the tables on the remote database server will be lost, as
the tables will be dropped and then recreated.
The Database Publishing Wizard is a great tool for moving a local database to a remote database server for the first time or whenever you are willing to obliterate the existing data and "start from scratch" on the remote server. For more complicated scenarios - like when you've added new columns to a table on the local database server and you want to update the schema on the remote database server to include these new columns - your best bet is to turn to third-party tools such as red-gate's SQL Compare and SQL Data Compare, which compare and synchronize changes between two databases' schema and data, respectively.
Microsoft's Database Publishing Wizard simplifies the process of duplicating a local database's schema and/or data to a remote database. It integrates within the Visual Studio IDE, adding a "Publish to provider..." menu item to the context menu in the Server Explorer window. The wizard makes it easy to specify what objects to create and options such as whether to include
DROPcommands and whether to script just the schema, data, or both. The result of the wizard is a script file with the T-SQL commands needed to duplicate the local database. This script can then be executed on a remote server, either through SQL Server Management Studio or by uploading the script file and then visiting an ASP.NET page that executes the script file against the remote database.