Scenario:

You finish building a great ASP.NET application, have everything tested and working right on your local system, are taking full advantage of the new ASP.NET 2.0 Membership, Role and Profile features, and are ready to publish it to a remote hosting environment and share it with the world.

Copying the .aspx files and compiled assemblies to the remote system is pretty easy (just FTP or copy them up). The challenge that confronts a lot of developers, though, is how to setup and recreate the database contents - both schema and data - on the remote hosted site. Unfortunately there hasn't historically been a super-easy way to accomplish this.

The good news is that this week the SQL Server team published the release candidate of a new SQL Server Hosting Toolkit that will make it much, much easier to deploy your SQL solutions remotely to a hosted environment. The toolkit allows you to work with SQL Express, SQL Server 2000, and SQL Server 2005 databases locally, and then easily transfer your schema and data and install them into a shared hosting remote SQL Server account.

The below post describes how you can start using this today.

SQL Server Hosting Toolkit

The SQL Server Hosting toolkit is available for free, and ships with a Database Publishing Wizard that supports two database hosting deployment scenarios:

1) The Database Publishing Wizard enables you to point at a database you are working with on your local system, and then automatically create a .SQL script file that contains the setup logic needed to re-create an exact replica of the database on any remote system. This .SQL script includes everything needed to create the database schema (tables, views, sprocs, triggers, full-text catalogs, roles, rules, etc - full details here), as well as populate the new database with the same table row contents as your local database (this is analogous to the MySQL dump utility). The benefit of having this setup logic encapsulated in a single .SQL file is that most hosters already support the ability for you to upload .SQL files to their hosted environments and run these scripts via their hosting admin control panels. Assuming you have a web hoster that supports this today, you can immediately start using the Database Publishing Wizard to easily deploy your sites without requiring anything to be installed or configured by the hoster.

2) The Database Publishing Wizard also enables you to point at a database you are working with on your local system, and then use web-services to transfer and recreate the database in your remote hoster environment (without you having to create the .SQL file or use the hoster admin control panel to run it). This publishing option does require that a SQL Publishing web-service be exposed in the hosting environment, and the SQL Server Hosting Toolkit includes a free implementation of this SQL Publishing web-service that we'll be working with hosters to aggressively deploy.

The Database Publishing Wizard enables you to use either SQL Express or SQL Server 2000/2005 locally, and then use either SQL 2000 or SQL 2005 in the remote hoster environment. It does not require that the versions of SQL match - so you can use SQL Express 2005 locally and then upload to a SQL 2000 server in the hosting environment without having to change any of your code.

The Database Publishing Wizard also supports handling the built-in ASP.NET 2.0 Membership, Role Management, Profile and Health Monitoring schemas. A lot of people have run into issues because the built-in .SQL scripts that ship by default with ASP.NET for setting up these schemas require DBO permissions at install-time for the SQL scripts -- which a lot of hosters don't support (note: the scripts do not require DBO permissions at runtime - only for install time, but this can sometimes still be a blocker in itself unless the hoster is willing to install them for you). The Database Publishing Wizard on the other-hand does not require DBO permissions when installing the ASP.NET Membership, Roles and Profile schemas/data, and should enable you to deploy the ASPNETDB tables + sprocs just as easily as any other database using the Database Publishing Wizard.

I'll be doing a series of posts over the next few weeks showing how to use the various features within the SQL Server Hosting Toolkit. This first tutorial in the series covers how to use it to easily generate a .SQL installation file of a local SQL Express database that you can then copy to a remote hosting account and use to re-create a SQL Server database for you to use with your hosted site.

Step 0: Download and Install the Database Publishing Wizard

The first step we'll need to-do is to make sure we have the Database Publishing Wizard from the SQL Hosting Toolkit installed. Click here to download it and install it.

The Database Publishing Wizard comes with support for both a GUI based wizard, as well as a command-line utility. The GUI based wizard can be run either standalone or via context-menu support that it adds to the Server Explorer in both Visual Studio 2005 and Visual Web Developer Express. For the purposes of this tutorial we'll be using this later Server Explorer integration - which makes publishing really easy.

Step 1: Create an ASP.NET web-site that uses a local SQL Express or SQL Server database

To help with this demo, we will use the built-in Personal Starter Kit template that ships with both VS 2005 and Visual Web Developer Express. To create a new web project based on it, select File->New Web Site within VWD or VS and choose the "Personal Starter Kit" template in the New Web-Site dialog. By default the personal starter kit application is configured to use SQL Express (which is free and can be downloaded here). When run the sample looks like below:

After creating the application, you can then run the web admin tool (choose the WebSite->ASP.NET Configuration menu item in VWD/VS) and create a new user and add them to the "admin" role for the site. You can then login as this new admin user and try uploading new pictures and/or customizing the existing ones on the site (note that both the picture meta-data, as well as the raw image binaries are stored in a database when you do this):

Once you are all done with the above steps we'll have two SQL Express databases installed within the \app_data directory for our project. One of the SQL Express databases is named personal.mdf and contains the tables and stored procedures specific to our web-site (photo and album tables, as well as basic content management support). The other SQL Express database is named aspnetdb.mdf and contains the database storage for the default ASP.NET 2.0 Membership, Role and Profile providers (which the application above is using for login and admin purposes).

Step 2: Creating .SQL Installation Scripts for our Database

Now that we've created a new application + local database, and added custom data to it (new users + their role membership, as well as new photos and albums), we want to deploy the application to a remote hosting server.

The first step we'll take is to create .SQL script files that will enable us to automate re-creating the exact same database schema + database content on our remote hosting account. To-do this we'll use the Database Publishing Wizard we installed as part of the SQL Hosting Toolkit.

To begin, click on the "Server Explorer" tab within Visual Studio or Visual Web Developer to see the databases that the application is using:

As you can see in the above picture, we have two SQL Express databases that we are using: ASPNETDB.MDF and Personal.MDF. To generate .SQL installation files for each one, simply select the database in the solution explorer, then right-click and select the new "Publish to Provider" context menu item (added by the Database Publishing Wizard) setup on it:

This will launch the Database Publishing Wizard and allow us to walkthrough scripting the installation of our database. As I mentioned in the intro of this blog post, the Database Publishing Wizard supports two deployment options: 1) To generate .SQL install script files that you can copy to your remote hoster and run using their existing web admin control panel tools, or 2) To upload the database directly using Database Publishing Web-Services on the hoster web-site.

For this first tutorial, we'll be using the .SQL script file approach - so keep the default radio button selected and provide a name for the .SQL install script file you want to generate:

When you click "next" you'll be given the option to customize some of preferences when creating the .SQL setup file. Note that you can control whether to drop existing objects within the target database, whether you want to target SQL 2000 or SQL 2005 with the script, and whether you want to setup both the schema and data, or just the schema, or just the data:

For this tutorial just keep the defaults selected, and hit next and generate the .SQL script:

You now have a Personal .SQL file that contains a script that you can run on any SQL server to re-create all the tables, sprocs, views, triggers, full-text catalogs, etc. for a database, as well as import and add all of the table row data that was in the database at the time the .SQL file was created.

The .SQL file itself is just plain text - so you can open it up with any text editor to see it and/or customize it with your own statements:

Notice above how the .SQL file includes both the SQL DDL needed to create the Photos table (including all of its constraints and primary-key/foreign-key relationships), as well as the SQL to insert data within the table once it is created (in the case above it is even inserting binary data for the photos - since they are stored in the database).

Once you repeat these steps for the ASPNETDB SQL Express database as well you'll have two .SQL installation scripts that you can use to automatically re-create your SQL database on any SQL Server:

Note that the .SQL files we built can be used to create two separate databases on a server, or they can both be run against the same database to create a single database that has a unified set of tables, sprocs, and data for the application. To accomplish this, simply run both scripts against the same database, and assuming no table or sproc names conflict, you'll have a single database containing everything. This later option is very useful when you have a hosting account that only provides 1 database instance for you to use!

Step 3: Using our .SQL files to create our remote databases

Now that we have our .SQL files, we can go about using them to install our database at our hoster. Exactly how we use the .SQL files to install the database will vary depending on how the hoster gives us access to our SQL account. Some hosters provide an HTML based file-upload tool that allows you to provide a .SQL file - which they will then execute against the SQL database you own.

Other hosters provide an online query tool (like below) that allows you to copy/paste SQL statements to run against your database. If you have a hoster which provides an online query tool like this, then you can open the .SQL file with a text-editor and copy/paste the contents into the query textbox and run it.

The quality of the SQL tools that different hosters provide varies quite a bit. In testing the Database Publishing Wizard we found that some custom-made SQL admin tools provided by hosters had issues where they incorrectly parsed valid SQL statements (in particular GOTO statements). This page describes one issue you might see some hosters have with GOTO statements, along with a workaround you can use. To help improve the overall quality of SQL hosting admin tools, the SQL Server team early next year is going to be shipping the source to a free SQL HTML admin tool that hosters will be able to integrate into their experiences. Hopefully this will help improve the standard experience with all Windows hosters.

If your hoster has no usable HTML web admin tool for allowing you to easily manage your SQL database, then you can also just write a simple ASP.NET page that you FTP (along with your .SQL file) to your web-site and then hit to read the .SQL file on the server in as text, and then pass it as a string to ADO.NET to execute. This will give you the same result as the query analyzer above - and fully create your database for you.

Step 4: Updating our connection-string within web.config

Once we've got our data uploaded within a database at our hoster, we'll want to upload our .aspx files, assemblies and content to the remote site (typically this is done over FTP).

The last step we'll need to take is to open up our web.config file and update the <connectionStrings> section to point at our new database location at the remote hoster. Note that you'll need to get the exact SQL server, database name, and username/password account to use from the hoster.

Using our personal starter kit example above, we'd change the <connectionStrings> section within its web.config file from the default connection-string (which uses two SQL Express database in the local \app_data directory):

We were able to use a single database (instead of the two above) because we we ran both .SQL files against the single database - which merged all schema and data into a single database instance.

Step 5: We are done

Now we can run the application remotely in a hosted environment, and it should just work.

Summary

The Database Publishing Wizard that ships as part of the SQL Hosting Toolkit should make creating .SQL files for any database (SQL Express or full SQL Server) really easy. You can use this to easily dump your local database and then use it to re-create the exact same database on a remote system.

In future tutorials I'll also show how you can actually re-create your database remotely without even having to generate a .SQL file (instead you can publish the database directly from VS to your hoster over a web-service). Stay tuned for details on how to-do this soon.

Hope this helps,

Scott

P.S. We are planning on adding the Database Publishing Wizard into Visual Studio "Orcas" (which is the next release of VS), which means you won't need to download it separately in that timeframe. But we wanted to make sure you didn't have to wait until then, which is why we are making it available today for Visual Studio 2005 and Visual Web Developer Express 2005.

P.P.S. Please visit this page to find other ASP.NET Tips, Tricks and Recipes I've written on other topics in the past.

55 Comments

Hi Scott,

This is a great thing, I've seen it a couple of days ago also.

But I have a question. Is it planned for this tool to handle versioning? Taking snapshots of current database, comparing it's sql file with the one from some previous version and re-creating the differences in the new one?

This would be VERY usefull.

Also this is a task that can be easily automated if the correct tools are presnt. Is it planned to make also an MSBuild task for this tool and maybe a command line version of it?

Hi Scott. Great work from your team as usual! Just one point... would be really useful to be able to select the objects to be scripted (you don't necessarily want to script ALL the data from all tables for example)... any chance of this in a future release?

Scott, Great tool! I can see using this to create script files for an install package that needs to populate a SQL DB. You state that the tool can only be run against a local db. Why not against a server db on my local network that I have rights to?

Ok this really sucks cuz i have been working on a similar Winform app. I have had this issue all this week with trying to get my db up to the host. Oh well, I am sure thiers is better and will go with that one. Thanks for bringing this to our attention.

By any means do you have any plans to publish more tutorials on (1) Restore and (2) Backup database to and from remote host.

Since few starterkit database names are very common,and shall not work on remote with the same name.( sql2000/2005 cannot accept the same database names ) I am wondering at what stage should i rename the database name.Please Focus more if you can spare time.

looking forward to more tutorials. This was really pleasing day for me.

Wow, this is truly great. In the past I've had to use the Mgmt Studio and search and replace to build this file.

Features I also need: Copying the remote database back down and/or refreshing the data of the local database. Also, generating a schema change sql script between the two (most common would be dev to live, but live to dev is sometimes needed too).

Sorry if I implied that this tool can only be used against local databases - that isn't the case.

It can actually be used against any SQL database (local or remote). You could open a remote database in your server explorer and then script it out. Or you could use the command-line tool or GUI wizard and provide a connection-string that way.

So you can definitely handle cases where you want to script out a database on your network.

We've been working with hosters to make it easy to integrate the reverse (hoster DB -> .SQL file) functionality into their web admin tools. That is one of the reasons why we have a command-line version of the tool, since it makes it much easier to integrate within their existing apps.

So this should provide the hoster->dev machine reverse direction support.

The diff version support is a good question that I'm going to be following up more to see if/how we could support.

Note that you can definiely use the VS Data Team Edition to do both data and schema updates today: http://msdn.microsoft.com/en-us/teamsystem/aa718807.aspx This also provides a bunch of other features that you might want to take a look at.

Have you had a chance yet to use the new VS Data Team Edition: http://msdn.microsoft.com/en-us/teamsystem/aa718807.aspx

It actually provides the schema/data comparison and migration features today, and is integrated into VS 2005 (along with many other things). You might want to take a look at it - I think you'll find it super useful.

I'm going to be following up with the Database Publishing team to see whether we can get some of the schema/data comparison features integrated into it as well.

Although you can use the above tool to backup your database, for real-time database backup and failover I'd recommend using the built-in SQL Server features for this.

With .NET 2.0 all of the SMO (SQL Managed Object) APIs are now available to script and program against using C#, VB and any other managed language. So you should be able to programmatically use the real-time Backup/Restore/Failover features this way.

From a web service security standpoint, what mechanisms are in place to prevent an unscrupulous 3rd party (ISP or ISV) from gathering entire schemas and data? Paramount are backups, but I think I did see this on the Codeplex site. Otherwise it looks great.

Oh my god, thanks SOOOOO much Scott for providing this tutorial and link to the download. &nbsp;I&#39;m a java guy moved to .NET and loved the web studio express but haven&#39;t had luck with godaddy&#39;s database server admin since they provide no info on how to use it. &nbsp;I tried to do csv imports but it didn&#39;t work. &nbsp;Go daddy provides the user security tables out of the box with no data, but should be much better.

I had taken the backup of existing database and tried to restore it on production server. The database was restored successfully. But I was not able connect to the database with the given user. Let me explain it with the below example...

Lets say the DB Name is "TESTDB" and login user id is "TESTUSER". The owner of all the tables is "TESTUSER".

Now, I took the full db backup of "TESTDB" and resoted it on another server. Then I tried to connect to that DB with the user "TESTUSER" but in vain. In order to fix this issue, I ran the below query.

use TESTDB
sp_change_users_login "update_one","TESTUSER","TESTUSER".

My query is, why should i run the above query? is it mandatory to run the above query to fix this problem? plz provide your feedback on this issue.

Unfortunately, I'm receiving the following error: I generated a Schema and Data dump of the sample AdventureWorks database, and receive the following error when trying to open the file in MS SQL Mgmt Studio: "The Operation could not be completed. Not enough storage is available to process this command." It generated a 500 mb .sql file, is that too big to open in studio?

Thanks,
Nathan

P.S. I agree that it would be nice to be able to script only certain tables, as oppossed to the whole db.

Thanks for a very useful and long waited tool for deployment. Red Gate Packager does an excellent job but is bery expensive.

I have just used the tool to copy the db from a remote production site to my localhost using the scripting option - went smooth, but I ended up with a 125 mb script. I havn't tested it yet, but tried a direct transfer indicating my localhost af a web service. Something went wrong this time, as the scipt wouldn't connect to the local server: 'Invalid URI: The format of the URI could not be determined. (System)'. What should I specify in order to deplay back to my development server ?

Depending on the size of your database and the contents within them, a 125Mb .SQL file might make sense assuming you have database data within it (if it is just schema then the file should be much smaller).

When you said you tried doing the direct transfer, did you do it via the web-service transfer agent within the database deployment tool - or via the SQL Admin Tool instead?

The sql script contains a least one table with more than 10.000 records, so the size is understandable, but I was also more afraid of the memory usage. It also gave me a 'terminated with errror'. I have no clue of where it terminated, but it seems that I got most of the data transferred. Therefore I would like to have a quick transfer like in the import/export wizard.

I tried a smaller db transfer (2,5 mb) and it wend in smoothly.

I used the the web-service transfer agent within the database deployment tool as a non script solution but failed as mentioned above.

I believe that SQL runs the script transacted - so either all statements complete or none do. If you don't have a lot of free memory or a large swap file, that might be the cause of the error above (basically there isn't enough memory to transactionally complete it - so it roles back).

Can you send me an email with more details? I'll then loop you in with someone on the SQL Database Deployment team who can comment further.

Anyone,
I've read through this blog, and have downloaded the Database Publishing Wizard. I too have the issue with my local database works (user can login) but when I copy the web site to my provider (dailyrazor.com) I cannot get the data base to work. I'M REALLY NEW AT THIS TOO. I tried creating an SQL file, and then went to run it on the dailyrazor site via my "Plesk" desktop. I created the database, and a user. But I cannot create the tables because I cannot find a way to either run the script.SQL file, or to load it. I'm sure it is my database inexperience, but this is SO frustrating.

Anyone have any idea on how to get this working on provider DailyRazor???

Amrinder: This tool will be available in a future version of SQL Server Management Studio, although we are not sure which one yet. :)

Nathan: Sorry to hear about your problems. Management Studio does have problems with large script files causing out of memory issues. Your best bet for now is to execute the T-SQL file using osql or split the SQL file manually.

Henrik: I am a little confused with your scenario. Are you choosing the "Publish" option to deploy your database using the web service? If so, that error is most likely caused by the fact that the URI you specified for the web service is invalid.

This will make it exponentially easier to deploy websites. I'm looking forward to when hosts like GoDaddy and others adopt this deployment scheme. I'm not a big fan of the webified version of SQL Server 2000. It's tough to get your data back from the host! With this model, you finally get real control of your hosted data.

I found the above procedures for creating the views and ran them once more isolated without any problem, so it might as well have been a memory problem, as the tables should have been created and populated at the time, when the procedure was executed.

I have however noticed in the script, that tablecreations are inserted in the script after view creations:

I had taken the backup of existing database and tried to restore it on production server. The database was restored successfully. But I was not able connect to the database with the given user. Let me explain it with the below example...

Lets say the DB Name is "TESTDB" and login user id is "TESTUSER". The owner of all the tables is "TESTUSER".

Now, I took the full db backup of "TESTDB" and resoted it on another server. Then I tried to connect to that DB with the user "TESTUSER" but in vain. In order to fix this issue, I ran the below query.

use TESTDB

sp_change_users_login "update_one","TESTUSER","TESTUSER".

My query is, why should i run the above query? is it mandatory to run the above query to fix this problem? plz provide your feedback on this issue.

Scott, I am trying to use this in conjunction with GoDaddy servers. This is what I've found: I think they don't have the SQL Publishing web-service exposed yet. I tried the direct connect option and failed with a long complex error I can't post here - could e-mail it if it would help. Second, GoDaddy doesn't seem to have a tool in the manager to run .SQL scripts. Maybe I am missing something. Can you please let me know if you think this Publishing Wizard is GoDaddy ready? If not, I understand that GoDaddy super cheap hosting is just going to be later than others to get this up and running. But, If you think it IS ready to be used with GoDaddy, then I'd like to know what I am doing wrong. Thanks so much for the great tutorial!!!

Scott: My script mentioned above failed due to some view creating procedures not being able to find the table object. This was in line 250.000 out of +300.000 lines. I have not found the time to check all lines, but found cases where tables were created and populated after view creating procedures were executed. I don't know if it's the case for the failure, but could be an explaination.

Alex: I used the publish option to try to 'publish' to my local db. An option like the one in the Import/export script, where you can specify the server directly would be nice to have.

This is really handy when you can't connect with Management Studio -- but that said, what happened to the "copy objects" feature we had in Enterprise Manager in DTS? Not sure if I am remembering the name of the feature correctly, but it copied schema and data and allowed you to choose the tables/view/sprocs to move easily between any attached db's. I really miss this capability, it feels like we're going backwards.

Great tool.
What would be even better is to provide a way to programmatically customize this functionality.
Either publishing an API, or enabling this functionality through cmdlet (PowerShell).
Practically I would like to be able have the possiblity to build upon this "tool" to include things like versioning, uploading different versions to different servers and updating the databases remotly... etc.

I am using WebService in my hosted application and client WinForm to synchronizing my database before. Sometime it has a server lack if I transfering more than 5.000 records of initial data (a table with 10 fields).
I will try this tools.

This simply does NOT work. I get A LOT of errors on the keyword 'GO' when trying this. How can this tool generate SQL that can NOT be read by the SQL Server 2005 on the shared host? I simply don't get it. I've spent 2 whole weeks trying to deploy a local site to a remote host with no luck. This drives me mad - seriously!

Although the ASP.NET Application Services schema has DBO names within them, the account you run the sprocs under doesn't need DBO permissions.

You should be able to use the approach I describe here with any normal account: http://weblogs.asp.net/scottgu/archive/2007/01/11/tip-trick-how-to-upload-a-sql-file-to-a-hoster-and-execute-it-to-deploy-a-sql-database.aspx

Here is a follow-on post I did that shows how to upload a .SQL file on a remote host even if your hoster doesn't allow direct Enterprise Manager access: http://weblogs.asp.net/scottgu/archive/2007/01/11/tip-trick-how-to-upload-a-sql-file-to-a-hoster-and-execute-it-to-deploy-a-sql-database.aspx