Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2009
    Posts
    3

    Unanswered: database 'consolidation'

    Hi all

    I would like some help with this. I have two sybase dbs sitting on one host/one instance.

    What I am looking to do is to 'consolidate' these two databases. Currently these dbs interleave across a couple of disks (say for example 2 disks).

    I would like to 'separate' them and say have db1 running of disk 1 and db2 running of disk 2 - as opposed to having these dbs running off db1 & 2.

    I tried to hunt around for best practise and readings (as I am still a juinior) but can't seem to find anything or perhaps I don't really know the right terms to use.

    Any help/guidance would be much appreciated.

    Thanks.

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    I assume you have several devices spread across disk1 and disk2.
    - Dump the databases
    - use sp_helpdb to retrieve the layout of the database (in what order are they created on which devices)
    - drop db1 and db2
    - drop the old devices
    - create new devices of the appropriate size on the appropriate disk (devices for db1 on disk1, devices for db2 on disk2
    - create db1 on the correct order on the devices created on disk1
    - create db2 on the correct order on the devices created on disk2
    - load the previously made dumps in the new databases.

    I hope this summary helps. If not, just say so and we'll help some more.
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Sep 2009
    Posts
    3
    Thanks Martijnvs.

    Just for more info on the process. It seems to be just a dump and load process.

    I 'heard' that the master db contains info on symbolic links to where each db is pointed to. I have yet to find info on this (like reading materials just to help me understand things conceptually and covering the basics). Would anyone have any info or links to this perhaps?

    Can this process be done say by changing the symbolic links around - if say the information i heard above is true.

    thanks again.

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    The link between the device and the file path is stored in sysdevices
    Two other options if you want to move a database device:
    1) Mirror the device to a new location and then unmirror removing the primary OR
    2) unmount the database, move the files using O/S commands and then mount the database using devicename=newlocation

  5. #5
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    I agree with Martin's summary, except for one point:
    use sp_helpdb to retrieve the layout of the database (in what order are they created on which devices)
    Unfortunately sp_helpdb does not provide the info in the correct order, the sequence, that is required for the create db. If this is not done correctly, if the order of allocations is incorrect, you will end up with mixed data/log. The method of obtaining the correct db allocation sequence for the create db command is as follows; change DB_NAME and exec this on the source db:
    Code:
    USE master
    go
    SET NOCOUNT ON
    GO
    SELECT  AllocType = CASE
                WHEN segmap = 4 THEN "LOG_DEVICE"
                WHEN segmap & 4 != 4 THEN "DATA_DEVICE"
                ELSE "MIXED_DEV"  -- already mixed on the source db
                END,
            SizeMb = size / 512)
        FROM master..sysusages
        WHERE dbid = db_id("DB_NAME")
        ORDER by dbid, lstart
    GO
    Then change DATA|LOG|MIXED_DEVICE to your new target devices, and exec that.

    Actually, if you email me, I'll give you a script that produces the entire set of create/alter db commands in the correct sequence.

    Finally, there are practices that entirely eliminate this problem, in any database.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  6. #6
    Join Date
    Sep 2009
    Posts
    3
    Hi guys

    thank you very much for the advise and information. Just thought I'd give an update.

    I've managed to get the unix sa to identify the disks that the databases uses based on the device name. In doing so I've identified that one device each for both DB.A and DB.B are sharing a disk.

    My intention now is to mirror DB.A only. So I was thinking perhaps to create a master db just for DB.A.

  7. #7
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Note sure what you mean. If you intend to move the DB_A devices off the shared disks (and thus leave DB_B on those disks), yes, you can do that via mirroring/unmirroring, quite nicely ... but that does not involve "create a master db just for DB_A".
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

Posting Permissions

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