Saturday, December 23, 2006

SQL Server Database Publishing Wizard RC

Microsoft posted the SQL Server Database Publishing Wizard (DBPW) 1.0 Release Candidate bits on December 19, 2006. The Wizard generates a T-SQL script for the specified database that you can upload to a hosting service provider. DBPW was known on the CodePlex site as the SQL Server Hosting Toolkit during its development. According to the documentation, you can use DBPW with SQL Server 2000 or 2005 in the following modes:

  1. Generate a single SQL script file which can be used to recreate the contents of a database by manually executing the script on a target server.
  2. Connect to a web service provided by your hoster and directly publish the contents of a source database into a specified hosted target database.
Here's the description from the download site:

SQL Server Database Publishing Wizard enables the deployment of SQL Server databases into a hosted environment on either a SQL Server 2000 or 2005 server.

It generates a single SQL script file which can be used to recreate a database (both schema and data) in a shared hosting environment where the only connectivity to a server is through a web-based control panel with a script execution window. If supported by the hosting service provider, the Database Publishing Wizard can also directly upload databases to servers located at the shared hosting provider. Optionally, SQL Server Database Publishing Wizard can integrate directly into Visual Studio 2005 and/or Visual Web Developer 2005 allowing easy publishing of databases from within the development environment.

I downloaded and ran DBPW's installer, which adds a Microsoft SQL Server Database Publishing Wizard Start menu folder and Database Publishing Wizard command. I then gave the graphic version of the Wizard (sqlpubwiz.exe) a test drive with a copy of the Northwind sample database running on an SQL Server 2005 Express SP2 Beta test instance under Windows Vista Ultimate. The following screen captures illustrate the initial test's steps: 1. Specify the source server. Don't use 'localhost' because the Wizard runs on a named pipe connection. 2. Select the source database. 3. Specify the output folder and filename.

4. Set the publishing options (the blue blotch is a Vista screen capture glitch.)

5. Confirm your publishing spec.

6. Follow the publishing process, which required less than 15 seconds for a small database on a fast machine.

The result was a nicely-commented and formatted 4,605-line, 2.7-MB Northwind.sql T-SQL script. I tested the script by executing it from a local SQL Server Management Studio Express (SSMSX) instance, which took five seconds to complete. A 207,000-line, 67.7-MB script from my Oakmont sample database took about a minute to generate and 0:03:23 to execute locally.

If you just want to generate a script named Northwind.sql in the local folder with Windows integrated security and the default options, here's the syntax:

sqlpubwiz script –d Northwind

DBPW's installer also adds a Publish to Provider context menu choice to Visual Studio 2005 Server Explorer's SQL Server data connections that opens the Wizard's Select an Output Location dialog, skipping the first two steps.

Full documentation for the DBPW is available from the CodePlex sqlhost wiki.

It's too bad that DBPW doesn't include an option to generate T-SQL for SQLServer Compact Edition (SQLce).

Technorati tags: , , , , , , ,

Posted by Roger Jennings (--rj) at 7:09 AM

Labels: , ,

Subscribe to: Post Comments (Atom)