Switching to another web host

Our website has a large database of around 100,000 registered users.
The problem now is we are changing our web host, so how can I ensure that the
database is exactly replicated @ the new host without loosing any data.
How can the switching between the 2 hosts happen transparent to our web users with no data loss?

See if this is useful...

http://vyaskn.tripod.com/moving_sql_server.htm

HTH.

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Hi Zizo,

To be honest I don't think that there is an easy solution to this... You are talking about moving your system from one ISP to another. You want to switch the system and have it use the new database without users noticing anything...

Assuming that you have the connectivity requred to get a database connection from one ISP to connect to the database server in the other ISP.

You'd have to set up something like merge replication which will allow both side to be updated (but this is not necessarily easy depending on your application)

Then once this is set up you'd want to have both systems fully functional, then cut over the DNS to point from the old WEB server to the new one... After a day or two the old server could be decommissioned

Quite a dangerous task though, so consider if you can live with an outage long enough to backup the database, copy it to the new site and restore it, then reconnect the users to logins (sp_change_user_login)

To overcome the DNS problem you'd have to get the old ISP to NAT to the new address or set the TTL on the DNS record to a time less than the expected outage

Cheers
Twan

Hi Twan,
What you said is really great.Actually it's not an easy job. Here's what I have in mind:

1. Using detach & attach, I'll copy the DB to the new host
2. I will get the 2 systems fully functional, which means that whatever transactions the users on the DB @ the old host will be automatically done on the new DB @ the new host.
3. Make the DNS opint to the new host
4. When the old host is out, I just make my website point to the new DB's ip address

So, what do u think ?

I have 2 problems here:
1. Since I new to replication, how can I do step 2 (Having both systems running & identical) ?
can u send me any guides/articles ?!
2. If I use detach & attach, will I need to restore the users logins? and how ?

Thanx a lot for your help,
Fady

Hi Fady,

There is quite a bit of information about replication in books online. Merge replication sounds like the easiest way to keep your two systems in synch, although transactional replication with updates might also be possible.

THis is definitely something that you need to test thoroughly beforehand. It is not a trivial task to set up.

Yes you will need to restore logins. You won't be able to transfer passwords very easily, but the rest can be scripted out using Enterprise Manager. Right click on a database, choose All Tasks, Generate SQL Script. In the dialog don't choose any objects, but do choose to script out logins

I can't stress this enough, test, test, test... Depending on the size of your database, and the length of time to backup/copy/restore a much more economical way is to have some downtime...

Cheers
Twan