Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2002
    Posts
    6

    Unanswered: Move entire DB, best practices?

    Hello, we're in the process of moving our website to a new datacenter and was wondering if anyone had any do's/don'ts about moving an entire database?

    Background, both boxes are running sql2000, the current db is 16GB and since it's used for our website, I'd prefer to minimize downtime as much as possible.

    Last time we made a move like this, we used DTS and it literally took 8 hours. We also had referential integrity issues. This time, I'm thinking about detaching the database on the current box, copying both the mdf and ldf over to the new box and then reattaching. I'm unclear as to how that would affect users/logins and if there is anything else I'm not seeing.

    BTW, I freely disclaim I'm not a SQL admin (I guess that statement didn't surprise anyone).

    Appreciate any feedback!

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    to be honest a sql newbie may not want to detatch and attatch a db. It is a bit proccess oreinted and you could kill your production box. The easiest thing that I have found is to take the most current full backup, move it local to the new machine and restore from it. Now if you dont have the same drive lettering it will be a bit tricky but it can be done. The trick is on the options tab, where you need to force the backup and it also has a window where you can change drive lettering. Just change the letter under the Restore As column and you are set. also u need to leave the database operational.
    Hope this helps just let us know if not
    Regards
    Jim

  3. #3
    Join Date
    Jul 2002
    Posts
    229
    Yes, you might get in trouble with the SIDs. Provided you only use SQL Server logins, it can be fixed using sp_change_users_login - study this SP in BOL.

    I guess you could script out names, SIDs and passwords.
    After the copying, drop all database users and re-create them using the script. (I haven't done this myself.)

    I think server collations need to be the same on both servers.

  4. #4
    Join Date
    Jul 2002
    Posts
    229
    Don't you still need to deal with the SID problem?

  5. #5
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Honetsly you may not have to do security seeings how it is part of the db and thus in the backup. try it without first and see
    Regards Jim
    Originally posted by Coolberg
    Don't you still need to deal with the SID problem?

  6. #6
    Join Date
    Jul 2002
    Posts
    229
    Originally posted by JDionne
    Honetsly you may not have to do security seeings how it is part of the db and thus in the backup. try it without first and see
    Regards Jim
    Yes, but will the mapping between logins and users be retained, since it's stored in the master (I believe) database?

  7. #7
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Im prety shure that it will, if im not mistaken when you do a recovery operation the master db is updated. The great thing of having two databases is that if it doesnt you can always script the security from the other . Id give it a try and see what happens. Just test before you go into production.
    Regards
    Jim

    Originally posted by Coolberg
    Yes, but will the mapping between logins and users be retained, since it's stored in the master (I believe) database?

  8. #8
    Join Date
    Jul 2002
    Posts
    229
    Interesting. I'll play with this on Monday!

  9. #9
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Let me know how it goes

  10. #10
    Join Date
    Apr 2002
    Posts
    6
    JDionne, I'm sure there are many things I'm in the dark about regarding detach/attach, but it does seem pretty straightforward. Am I missing something besides right-clicking on the DB, selecting detach (update stats), copying mdf/ldf to the new box and selecting attach?

  11. #11
    Join Date
    Mar 2002
    Posts
    112

    re

    I don't recommend you to do detach/attach because it can be more downtime .
    I think if you want to avoid downtime you have to running both of them(old production, new production) paralell ,You can backup/restore old to new and set replication (transact) both of them when you check , test and everything is OK you will switch production to new production it can make a little downtime but I thing It's safer way and less downtime than detach/attach to new box but it's take loger time

  12. #12
    Join Date
    Oct 2002
    Posts
    369

    Re: Move entire DB, best practices?

    RE: Hello, we're in the process of moving our website to a new datacenter and was wondering if anyone had any do's/don'ts about moving an entire database?
    Background, both boxes are running sql2000, the current db is 16GB and since it's used for our website, I'd prefer to minimize downtime as much as possible.
    Last time we made a move like this, we used DTS and it literally took 8 hours. We also had referential integrity issues. This time, I'm thinking about detaching the database on the current box, copying both the mdf and ldf over to the new box and then reattaching. I'm unclear as to how that would affect users/logins and if there is anything else I'm not seeing.
    BTW, I freely disclaim I'm not a SQL admin (I guess that statement didn't surprise anyone). Appreciate any feedback!

    Q1 [How may downtime be minnimized in moving a database?]

    A1 What is 'best' really depends on the circumstances of your specific situation and what resources you have available. You should be dry run testing various techniques to see what will work given whatever your maximum downtime criteria and resource availability is. Since your DB is relativly small, copying your DB files and reattaching on the target server may meet whatever your downtime limit is (much depends on how much bandwidth you have available between the source and target). However, from your post it appears your system will be down / unavailable durring the entire copy and reattachment process. One technique I've used to minnimize downtime to well under one minute with much larger DBs (and a very small budget) involves the following:
    1 Do a DB restore from a full backup dump, (delivering a several 100 GB full DB backup dump on tape can save lots of bandwidth expense); then sucessivly apply multiple small transaction log dumps (small TL backup dumps are cheap to transmit compared to huge Full DB backup dumps) to the target server (with norecovery option).
    2 When the folks responsible for the application e.g., (your webserver folks) are ready to redirect their application to the target:
    i) Dump and restore a final source server transaction log dump, (time it to make it as small as possible), to the target server and take the original DB off-line (immediatly following the final source server transaction log dump).
    ii) Recover the restored DB on the target.
    iii) Have the application staff redirect the application to the target. This could actually occur at step i or after step ii (much depends on how the application handles failed connections).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •