Posted by Plamen Ratchev on 01/30/07 14:15

Hi John,

There are different ways and tools to accomplish what you need based on what
you feel comfortable with and what fits your update schedule:

1) As you mention replication will work. However, since you are updating the
data once a week or less frequently it might not be worth the effort to set
up and maintain replication.
2) The analog to DTS in SQL Server 2005 is SSIS (SQL Server Integration
Services). A simple way to create SSIS package to transfer your data is to
start the SQL Server Import and Export Wizard (just right click your
database and select Tasks, Export Data...) and follow the steps. On the
Select Source Tables and Views screen you can click "Edit Mappings". There
you have options to drop and re-create the destination tables and to enable
identity insert. At the end you can execute immediately or save it as SSIS
package to execute later.
3) Use the Database Publishing Wizard. It is designed for deployment of
local databases to remote hosting environments. The tool has both graphical
and command line interfaces, and there is a way that you can update your
database via a Web page. Here are more details:
Database Publishing Wizard:
http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard
Scott Guthrie guides:
http://weblogs.asp.net/scottgu/archive/2006/12/22/recipe-deploying-a-sql-database-to-a-remote-hosting-environment-part-1.aspx
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

Regards,

Plamen Ratchev
http://www.SQLStudio.com

[Back to original message]