I find that I very often find myself sitting in a situation where I've been developing an ASP.NET website or other database-driven web application, making use of ASP.NET 2.0's membership and role providers etc. When I get to the point of having to publish the site to my shared hosting provider, I need some method of replicating the entire database to another server. This can be accomplished in a couple of ways if you have actual access to the remote server (i.e. you can just perform a database backup/restore).
But what about the situations where you don't have access to the server and/or can't upload a database backup for some reason. For example if you're uploading to a web hosting service, you typically only have access through a web based setup tool which doesn't allow much flexibility.
Enter the SQL Server Hosting Toolkit. This comes with a Database Publishing Wizard which allows end users to deploy databases to a remote server in two ways:
- Using the Database Publishing Wizard, simply point to a database on your local system it can generate a .SQL file containing all the logic needed to replicate an exact copy of the database on a remote system. This includes the complete schema (tables, views, triggers, roles, stored procedures etc) as well as data to populate the database with the data from your local server. I used to find this was one of MySQLs biggest strengths - it comes with the mysqldump utility which does pretty much this - but why didn't a similar tool exist for SQL Server?. Now to deploy your database, all you need to do is log on to the SQL manager interface of your hosting provider, create a database and upload the SQL file you've created. Most shared hosting providers have a management tool that allows users to upload and run .SQL scripts.
- The Sql Server Hosting Toolkit also comes with a set of web services which the hosting provider can choose to install. If these web services are available, the Database Publishing Wizard can deploy directly to the hosting environment through them.
I'll write a small tutorial later on, showing you just how easy it is to use this tool to generate SQL scripts for your database.