SQL Server - Publish your database along with data in a few simple steps?
It comes very handy for every SQL Server user to publish the database that will help the needs of developer/user to deploy a local database from the Development machine to a hosting environment (remote machine) with few simple steps.You may be aware that after the release of SQL Server 2005 & Visual Studio 2005, the SQL database publishing wizard was available as web-downloadable add-in for Visual Studio 2005. In this place Microsoft has received a lot of positive feedback for this wizard and tables have turned out to integrate the same tool with Visual Studio 2008! As promised the Database Publishing wizard has been integrated with Visual Studio 2008 RTM release.
Precisely SQL Server Database Publishing Wizard 1.1 does the job (as per the release notes):
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.
The 2 options I find it very useful for the Developers and for the users within my work environment in the key deployment scenarios and you can take advantage of this tool:
- It generates a single .SQL script file which can be used to recreate a database on a remote machine
With few steps you can achieve this with the Database Publishing Wizard , 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. It will create a .SQL (file format) script that includes everything needed to create the database schema (tables, views, sprocs, triggers, full-text catalogs, roles, rules, etc), easy isn’t it. Not only this the user will also have the option of populating the new database with the same data contents as your local tables. Lets assume within our BT environment you need to host your database on a shared SQL production platform and to avoid any sort of mismatch of data/schema you could supply this script to the DBAs who supports the platform. 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.
- It connects to a web service provided by your hoster and directly creates objects on a specified hosted database.
Ok what we are looking here is a web service? Database Publishing wizard enables the process to point to a database (that you wanted to publish) and then use web-services to transfer and recreate the database in your remote hoster environment. In the outside world (out of BT) this will help you having to create the .SQL file or use the hoster admin control panel to run it. Just a catch about this option is, it 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
Not only that I like the fact that even you can then FTP the generated script up to your remote server environment and execute it, see Scott Guthrie's Tip/Trick: How to Upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database reference on how-to-do-it. It is a fact that many ISPs out there will not let the users to have Admin access on the platform and alternative to that is you can use the built-in web-service support provided by the database publishing wizard to directly export and copy the database over the Internet to your remote hosting provider.
Finally I would like to refer the excellent reference from Scott Gu's blog on Recipe: Deploying a SQL Database to a Remote Hosting Environment that will help you go in-detail on this tool. Make sure you test the tool before deploy and mould according to your database publishing needs within your environment.