SQL Server Performance

Switching to another web host

Discussion in 'Performance Tuning for SQL Server Replication' started by zizo, Sep 29, 2003.

  1. zizo New Member

    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?
  2. satya Moderator

    First of all make sure there is a connection between these webhosts, if not you have to opt for downtime while moving databases.

    If you're using SQL 2K then take help of COPY DATABASE WIZARD or take full backups for all databases including system, then restore them on new webhost to port all information with regard to the web application.

    Just a link about moving logins between the serverhttp://www.databasejournal.com/features/mssql/article.php/2228611

    KBA to move databaseshttp://support.microsoft.com/support/kb/articles/q224/0/71.asp (for ver.7 but still applies to 2K too)


    Satya SKJ
  3. gaurav_bindlish New Member

  4. Twan New Member

    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

  5. zizo New Member

    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,
  6. Twan New Member

    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...


Share This Page