locked
CRM 2011 Database and Filegroups? RRS feed

  • Question

  • Hi,

    Install of CRM 2011 RU8 and the database approx 450gig and the attachment table is approx 400gig.

    Has anyone moved tables e.g. Attachment to a separate filegroup? Is this an unsupported changed?

    The MS docos mention that modifications to the physical schema of the database, other than adding or updating indexes is not supported, however this changing the storage on the disk and not schema.

    Thanks in advance

    Brad


    • Edited by Brad Gillan Tuesday, October 23, 2012 8:26 PM
    Tuesday, October 23, 2012 8:25 PM

All replies

  • Hi Brad,

    Thats a very interesting question. I haven't come across any official MS documentation on CRM which would suggest exactly the above and the implications. This means this looks like unsupported.

    Try this out on your dev server and test whether everything is fine and actually your CRM performance has enhanced.

    I did some research on the net and found this:

    http://www.consultcrm.co.uk/sites/default/files/optimizing_and_maintaining_a_microsoft_dynamics_crm_2011_server_infrastructure_final_0.pdf

    It has something around file groups:
    ======================
    Taking Advantage of Multiple Disk Drives
    It may be tempting to map your partitions to filegroups, each accessing a different physical disk drive, to improve I/O performance. When SQL Server performs data sorting for I/O operations, it sorts the data first by partition. Under this scenario, SQL Server accesses one drive at a time, and this might reduce performance. A better solution in terms of performance is to stripe the data files of your partitions across more than one disk by setting up a RAID. In this way, although SQL Server still sorts data by partition, it can access all the drives of each partition at the same time. This configuration can be designed regardless of whether all partitions are in one filegroup or multiple filegroups.
    ======================
    Implementing Solid State Drive Technology
    Database I/O performance is a key factor for determining Microsoft Dynamics CRM 2011 application scalability. Upgrading your storage subsystem will yield better performance because of higher IOPS. Solid State Drives (SSDs) provide high IOPs and are well suited for the Microsoft Dynamics CRM application I/O profile, which tends to be random access to small pieces of data.
    SSD technology typically provides faster system performance than the magnetic media associated with traditional hard disk drives (HDDs). While more expensive than HDDs, using SSDs in a system can also enhance overall system responsiveness, and they generally consume less power.
    Customers can also choose to use SSDs only for a subset of the Microsoft Dynamics CRM database. Based on frequency of access, partition the database into filegroups so the most frequently accesses tables reside on SSDs, while the remainder continues to reside on HDDs.
    ======================

    I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful.


    Ashish Mahajan, Microsoft Dynamics CRM Solutions Architect, CSG (Melbourne)
    My Personal Website: http://www.ashishmahajan.com
    My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
    My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

    My Linkedin: View Ashish Mahajan's profile on LinkedIn
    My Twitter: https://twitter.com/#!/ashishmahajan74

    Wednesday, October 24, 2012 1:25 AM
  • Hi Brad,

    If i was the responsible to maintain the CRM there, what i could do is, create a different database, and i import over there all the attachments, so i could have the CRM database with less disk space, and as you know, the performance can be affected with that amount of data. After that, i would create a web page to show the attachments, with the same look and feel as CRM shows. The link to that page can be on the left pane of the entities, or you can import only same entities to the new database.

    I would prefer to create a generic page to show for all the entities. After that you can delete from crm all atatchements that you have migrated to the new database. 

    One more thing, every night you can run an application that imports to the new database the attachments and delete those from crm.

    With that approach you will have more performance in CRM, and you can tune the new Database to be faster if you want.

    Any doubt, say.

    Kind Regards,

    Pedro Airo


    Wednesday, October 24, 2012 7:50 AM
  • Hi Brad,

    Did the answer on this thread answer your question? did you find a different resolution?


    Visit my blog for CRM material, improving performance, kerberos, IFD, development tips, etc. :) http://quantusdynamics.blogspot.com

    Tuesday, October 30, 2012 4:00 PM
    Answerer
  • Hi nrodri

    No, I am still searching for an answer to the question 'Is it an unsupported change to the database if a table is moved to a seperate file group.'

    Regards,

    Brad

    Tuesday, October 30, 2012 9:54 PM
  • Brad

    Did you ever get an answer to this, we're looking to do the same

    http   community.dynamics.com/crm/f/117/p/103832/201928.aspx#20198

    Thanks

    C

    Thursday, April 11, 2013 1:51 PM
  • I'm not aware of anything more specific than the quote in the initial post which is from the SDK documentation.

    My interpretation is also that manipulatig filegroups does not constitute the 'physical schema of the database' as used in the SDK documentation. Therefore this is not an unsupported change. Control of filegroups is a SQL admin function, rather than the preserve of the application (in this case CRM)


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Thursday, April 11, 2013 3:38 PM
    Moderator