Post reply

Splitting TB database

  • January 25, 2006 at 3:13 pm

    #113175

    We have 4 TB database (Sql Server 2000) that comprises 50 tables. We would like to split it to 2 different Sql Servers 2005 (25 tables on each server). Out plan is: 1) copy 4 TB mdf file to 2 new servers; 2) attach 2 identical databases to new servers; 3) drop tables #26-50 from 1-st server and #1-25 from 2-nd server; 4) shrink data files (hope, we will get 2 mdf by 2 TB). What do you think about this plan (or what is most efficient way to do this)?   Thanks

  • January 25, 2006 at 11:14 pm

    #617079

    IMHO. Depending on timelines etc. Doing it that way will mean that you have to attach the mdf. file twice and at 4 TB will take very long. Would it not be faster to restore the mdf. on one server and then export the other tables? (depending on your network speed)

    Does the old server not have any business logic? (Table1 is related or dependant on table30)


    Andy.

  • January 26, 2006 at 6:13 am

    #617121

    1) From business point of view splitting is OK. 2) I am afraid that restoring 4 TB database or export 2 TB tables over network will take longer. But I can not be sure as do not have this type of experience. Attaching 4TB database is not an issue, future shrinking- this is what scares me. Thanks

  • January 26, 2006 at 6:22 am

    #617129

    I do not have any databases that are larger than 1TB. I suppose it is just trial and error. Good luck!


    Andy.

  • January 26, 2006 at 8:47 am

    #617201

    If you have space on disk make a bcp of tables.

  • January 26, 2006 at 8:55 am

    #617206

    I guess bcp and table export using DTS will give actually same result, i.e. time. Thanks

  • January 26, 2006 at 9:28 am

    #617221

    Instead of copying 4TB twice, I would build a 2-TB database on each new server and then DTS different tables to each.

    IMHO, the time involved in shrinking databases and slowing down user access to the data will be much more than the DTS process.



    Michelle

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply