Republished with Permission - Original Article
Last month I posted about the new (free) Database Publishing Wizard that is designed to make it
much, much easier to upload and deploy SQL Express and SQL Server databases in
a web hoster environment.
In my first Database Publishing post, I walked
through how you can use the Database Publishing Wizard to automatically
generate a .SQL installation file that contains the script necessary to
recreate your database schema (tables, views, sprocs, triggers, full-text
catalogs, etc) and also populate your database with the same table row contents
as your original database. This provides a super easy way to put together
a .SQL script that entirely automates replicating your database on another
server:
Figure 1
In my previous post I mentioned how hosters often provide an
online SQL management tool that you can then use to open and run your newly
generate .SQL file, or provide a SQL query tool that allows you to copy/paste
the .SQL file's contents into a query execution window to run.
Unfortunately, though, not all hosters support tools like this. And
even with hosters that do support it you might run into cases where your
generated .SQL file is so big that copying/pasting it into a text box isn't
really viable (doing a form post of 200MB of content will typically time
out!).
This blog post walks through an alternative way to
deploy your .SQL files in a remote hosting environment, and which
does not require your hoster to configure or install anything on the
backend.
Steps to Deploy a SQL Database to a Remote SQL Server
without using an Admin Tool
Below are the steps to take to deploy a local database to a
remote hoster without requiring or using any admin tool:
Step 1: Generate a .SQL File Containing your
Schema and Data
The first step to remotely deploy your database in a hosted
environment is to generate a .SQL file that dumps the schema and content of
your database. Follow the steps in my first Database Publishing blog post to learn exactly how to
generate a .SQL file for either a SQL Express or SQL Server database.
Step 2: FTP up the .SQL File to your Remote Hoster
Once you've generated your .SQL file, upload it to your
remote hoster using FTP or some other file transfer mechanism. Ideally
you should copy this into a protected location where remote users can't easily
get access to it (one suggestion: give it a random name and upload it into the
/app_data folder which is typically protected by default).
The benefit of uploading this file over FTP is that it won't
force you to limit the size of the .SQL file. It can be 100s of
megabytes in size if necessary.
Step 3: Download the RunSQL.aspx Utility Page
Visit this page and download the RunSQL.aspx file linked on
it.
The RunSQL.aspx file is an ASP.NET page that the SQL Server
team put together that supports two arguments: 1) The name of a .SQL file, and
2) The connection string of a database. When run the RunSQL.aspx page
will open the .SQL file and iterate over each of its statements and execute
them against the database (indicated via the connection string). This
will provision the database defined within the .SQL file to the remote database
target.
Step 4: Edit the RunSQL.aspx Utility Page
Open and locally edit the RunSQL.aspx file and configure the
name of your .SQL file, as well as provide the connection-string your hoster
gave you for the SQL database:
Figure 2
Replace the <<YOUR_SCRIPTFILE>>
marker and associated connection-string markers with the correct values for
your hosted configuration. Note that unless you know the fully qualified
path of the .SQL file, you'll probably want to use ASP.NET's
Server.MapPath(fileName) method to calculate the absolute path of the
relative .SQL file path in your web application. For example:
Listing 1
<span lang=EN>// Filename of the T-SQL file you want to run
string fileName = Server.MapPath("personal.SQL");
// Connection string to the server you want to execute against
string connectionString = @"Server=server123;User ID=user123;Password=password</span>
<span lang=EN>123;Initial Catalog=MyDBName123";
// Timeout of batches (in seconds)
int timeout = 600; </span>Step 5: Upload the RunSQL.aspx Utility
Page to Your Remote Hoster
Once you have finished updating the fileName
and connectionString values above, upload the RunSQL.aspx file to your remote
hoster (for example: using FTP).
For security reasons I recommend giving the file
a random file-name when you upload it so that other users can't easily find and
run it.
Step 6: Hit the RunSQL.aspx Utility Page
with a Browser
Once uploaded, hit the remote RunSQL.aspx page
via your browser. This will cause the page on your remote server to parse
the .SQL file, and execute all of its statements. Since the .SQL file
contains all of the database schema creation and population statements
necessary to recreate your database, once the page finishes running you'll have
an identical database deployed at your hoster:
Figure 3
Step 7: Delete the RunSQL.aspx and .SQL Files
Once you've finished running your .SQL script, delete
both the RunSQL.aspx page and the .SQL file from your remote hoster
server.
For security reasons you do not want anyone else to be able
to remotely hit the RunSQL.aspx page (since it might recreate your database
causing you to lose data).
Step 9: Update the Web.Config file of your application
to point to the hoster database
The only final step remaining then is to update your
web.config file's <connectionStrings> section to also point at your
remote hoster's database connectionstring value. Once you do this your
app should work fine remotely.
Hope this helps,
Scott
P.S. For more of my ASP.NET Tips, Tricks and Recipes, please
visit
this page.
