SQL Database Publishing wizard is now in Visual Studio 2008.

The SQL database publishing wizard is a very popular web-downloadable add-in for Visual Studio 2005. The publishing wizard addresses the issue where a developer needs to deploy a local database from his development machine to a hosting environment on a remote machine. We received a lot of positive feedback for this wizard and we decided to integrate this with Visual Studio 2008. This is a feature that was added post Beta2 and will be available with Visual Studio 2008 RTM.

The wizard supports two key database hosting deployment scenarios:

1. It generates a single .SQL script file which can be used to recreate a database on a remote machine

Using the Database Publishing Wizard you can point to a database on your local machine, and then automatically create a .SQL script file that contains the setup logic needed to re-create a replica of the database on any remote system – for example an external hosting system. This .SQL script includes everything needed to create the database schema (tables, views, sprocs, triggers, full-text catalogs, roles, rules, etc). You also have the option of populating the new database with the same data contents as your local tables. Most hosters today support the upload and running of .SQL files to their hosted environments using their admin control panels. So, all you need to do is upload and run the .SQL script generated by the Database Publishing Wizard, and you will have a working database in your hosted environment. This should considerably reduce the effort required to deploy your databases.

2. It connects to a web service provided by your hoster and directly creates objects on a specified hosted database.

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 release version of Visual Studio 2008 will come pre-installed with the 1.2 version of SQL Publishing Wizard.

So now that you know what the Database Publishing wizard does, let us dig deeper into how it will work with Visual Studio 2008.

Step 1: Create a new website by selecting menu File ->New Web Site. Switch to Server Explorer and add a new Data connection and connect to a database. In this case we will use the Northwind database that comes with SQL Express. You should point to the database you want to publish.

Step 2: Select Northwind.dbo node in Server explorer and right click to bring up the context menu. In the Context menu you have a “Publish to provider…” option.

Step 3: Click “Publish to provider …” to launch the Database Publishing Wizard.

Step 4: Click Next to select the mode. Let us go with “Script to file” mode. We will need to specify the .SQL file name and location.

Step 5: Click Next and you will get to the Publishing Options. On this page, select the script for target database (SQL Server 2000 or SQL Server 2005) and the types of data to publish (Schema, Data or Schema+Data). You also have the option to drop existing object in script if you want to.

Step 6: Keep the default selection and hit next and generate the .SQL script.

Step 7: The .SQL file generated 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.

Step 8: The .SQL file is a plain text file. You can open it in your favorite editor and customize it as you need.

Step 9: Now that we have our .SQL files, we can go about using them to install our database at our hoster. Exactly how to install the .SQL files will vary depending on how the hoster give 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.