none
SQL 2008 Co-Locate Disk Allocation - Data sourced co-located = 1 for large SQL Databases - HOW TO OVERRIDE? RRS feed

  • Question

  • I am configuring a new protection group for SQL Server 2008 and have the following characteristics:

    180 Databases ranging in size from 500 MB to 120 GB.  The majority of databases are > 10 GB.

    When I am configuring the disk based protection with the co-locate data on disk option enabled, looking at the details of the replica and recovery point volumes, the large databases (the majority) are only allocating one database to the volume pairs (replica, recovery point).  This doesn't make sense considering it is trying to co-locate.  The volumes that are actually co-located have databases that are small (500 MB to 3-4 GB) and even then it only allocates 2-4 databases).

    How can this protection group be configured so that co-located on disk is actually being done?  Command Line / powershell?

    DPM Version: 3.0.7696.0    running on Windows Server 2008 SP2

    SQL Version:  SQL Server 2008 x64 with SP1

    • Edited by millertime78 Thursday, December 23, 2010 5:47 PM better detail
    Thursday, December 23, 2010 5:44 PM

Answers


  • We can co-locate up to 2000 small SQL databases to overcome the 300 limit data source limit (non-collocated limit).
    We allow up to 200 SQL DB’s / PG.
    We allow up to 30DB’s to be collocated on a single replica volume.

    The default SQL Co-located replica volume that DPM creates by default is 10GB meaning we will co-locate as many SQL databases that fit on that 10GB volume up

    to 30 before we create another 10GB volume.

    Depending on your average size database, you can modify that registry key so you can take better advantage of the co-location feature.

    The key is HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Data Protection Manager\Collocation\SQL
      Valuename= CollocatedReplicaSize and should be a multiple of 1GB  (1073741824 bytes)

    Now, for you to do this, you would need to perform the following steps.

    1)  Calculate the average size SQL databases under protection, and plan for some growth over time.
    2) Multiply the (average size + growth) * 30 – this will be the new collocatedreplicaSize – make sure it’s a multiple of 1GB  (1073741824 bytes)
    3) Modify the registry key to make it that new value.
    4) Unprotect the  SQL databases and chose the option to delete the replica volumes (yes that will remove all your backups, but the only way to get them on co-located replicas)
    5) Re-protect the SQL databases, but this time make sure the enable co-location checkbox is checked.
    6) DPM will make new larger replica volumes and co-locate up to 30 DB’s per replica.


    Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, December 27, 2010 6:19 PM
    Moderator

All replies


  • We can co-locate up to 2000 small SQL databases to overcome the 300 limit data source limit (non-collocated limit).
    We allow up to 200 SQL DB’s / PG.
    We allow up to 30DB’s to be collocated on a single replica volume.

    The default SQL Co-located replica volume that DPM creates by default is 10GB meaning we will co-locate as many SQL databases that fit on that 10GB volume up

    to 30 before we create another 10GB volume.

    Depending on your average size database, you can modify that registry key so you can take better advantage of the co-location feature.

    The key is HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Data Protection Manager\Collocation\SQL
      Valuename= CollocatedReplicaSize and should be a multiple of 1GB  (1073741824 bytes)

    Now, for you to do this, you would need to perform the following steps.

    1)  Calculate the average size SQL databases under protection, and plan for some growth over time.
    2) Multiply the (average size + growth) * 30 – this will be the new collocatedreplicaSize – make sure it’s a multiple of 1GB  (1073741824 bytes)
    3) Modify the registry key to make it that new value.
    4) Unprotect the  SQL databases and chose the option to delete the replica volumes (yes that will remove all your backups, but the only way to get them on co-located replicas)
    5) Re-protect the SQL databases, but this time make sure the enable co-location checkbox is checked.
    6) DPM will make new larger replica volumes and co-locate up to 30 DB’s per replica.


    Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, December 27, 2010 6:19 PM
    Moderator
  • I ended up configuring the CollocatedReplicaSize to 350GB which worked perfectly.

    I would note that when you select the protection group and select the option to stop protection, when you click Delete data, be sure that you UNCHECK the option to delete tape recover points unless you want to expire them.  In my case, I did not want tapes to expire... thus I unchecked that option.

    Monday, December 27, 2010 8:10 PM