I would like to replicate a MSSQL 2005 db (hosting through a shared hosting provider) directly on an integration server for development purposes. MSSQL server provides quite a few ways of performing data replication, but I am not sure that any of them is compatible with a typical shared hosting environment.
The idea would be to update the database once a day or so, much better if it can be done incrementally. Any idea how to proceed?
Try the SQL Server Publishing Wizard 1.2, which is included with VS 2008. Or download the 1.1 version here:
There are couple of ways of doing it.
Use SQL Server 2005 copy function, but it would fail if SQL Agent is not ruining or there is any permission restriction - mostly like to happen with shared hosting.
Use SQL Server backup/restore: the problem is usually you don't have access to the Database server file system with shared hosting.
Replicate database schema on development server, then do a DTS to transfer data, you can save the DTS package ( even manually edit it if needed ) and set up a scheduled task in the development machine, it would automatically synchronize the database everyday, it may not be incrementally, depends on your database design/schema.
option 3 seems to require more work but actually it is easy to set up and would save you a lot time in a long run.
any other suggestions I would like to hear too.
It really depends on how the hosting environment is setup
I used to have jobs to HOST: backup SQL DB's on SQL Server, copy over to Web/FTP Server Internal: FTP download the backup file, restore on a SQL Server
That wasn't fun, but once it's setup it works most of the time
If the remote SQL Server is easily accessible, you can setup SQL Replication, using Snapshot Replication (essentially schedule backup-and-restore) daily
I have right now a DB replicated from server DB to my computer's own SQL Server using Snapshot Replication (I did it via the wizard)