web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / SQL 2012 best practice...
Microsoft Dynamics AX (Archived)

SQL 2012 best practices for Dynamics 2012R2 CU7

(0) ShareShare
ReportReport
Posted on by imecoax 680

Hi,

We have received most of our hardware for our Dynamics AX 2012 R2 CU7 installation and we are extremely eager to get started. The hardware list is as follows:

Two HP G8 E5-2650V2's (dual processors) 32 cores (if HyperThreading turned on)

HP P2000 with 12 SFF 15k disks, not SSD unfortunately

Backend storage network to P2000 will be iSCSI running @ 10Gbit. etc.

The idea is to cluster both servers and have 2 virtual machines (SQL and AOS) stored on the P2000. I was wondering what would be the ideal setup:

1. One RAID 10 array with all 12 disks and just those two VM's

2. Two RAID 10 arrays with 6 disks each and a VM on each

3. Three RAID 10 arrays with 4 disks each, AOS on one, SQL on the other, TempDB on the third?

Access to the AOS server will be with a client installed on a terminal services server and will be accessed via RemoteApp or a similar technology.

Sorry for all the flood of information.. Just new to the field and extremely excited to get going!

Thanks!!

*This post is locked for comments

I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 237,965 Most Valuable Professional on at

    I would definitely put the transaction log to its own disk, separately from database files. The DBMS needs writing to both places in the same time and if the the disk with logs doesn't have to deal with random access, it can just keep sequentially writing to the log. Look at resources specialized to SQL Server design/tuning for additional information. (I'm not a SQL Server specialist myself.)

  • Community Member Profile Picture
    Community Member on at

    I would recommend option 2 ' Two RAID 10 arrays with 6 disks each and a VM on each' which would provide a better Disk I/O for each server. This decoupling of AOS and SQL is the best case.

    Regarding the 3rd option, you might not want the tempdb separate which would cause a lot of I/O between the arrays.

    One option what I would suggest is, if you have a demand for Datawarehouse you could go for the 3rd option with with DW database on the 3rd array.

    One more thing, if you have more users (>50). Then you might want to have multiple AOS servers. You could easily setup two lean VM's for running the AOS.

    Anyway, your configuration looks good.

    Regards,

    Saravanan L | Enterprise Architect | My Blog

  • Martin Dráb Profile Picture
    Martin Dráb 237,965 Most Valuable Professional on at

    That would still keep transaction logs together with other files, which would have negative impact on the performance of the whole solution. It would be a shame not to separate them.

    Check out Collection of SQL Server Storage Best Practices for a brief description of storage best practices and Administering Storage, I/O, and Partitioning in Microsoft SQL Server 2012 , for example, for additional suggestions and explanation.

  • imecoax Profile Picture
    imecoax 680 on at

    Thanks for all the support.

    By the way, should the Log files, tempdb, database files all be on separate VHDx's sitting on different RAID array sets and just attached as mulitple SCSI disks to the SQL VM but all as individual VHDx's?

    Is that how it is done?  

    Finally, do all those SQL tuning guides for SQL 2012 only apply to standard database setups or do they even apply to a MS Dyamics AX setup?

    Thanks!

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 237,965 Most Valuable Professional on at

    In the ideal world, you should use separate disk arrays. It guarantees that other components don't have to wait for resources if some other component is reading or writing to disk.

    I'm not sure what you mean by "standard database". If you ask whether Dynamics AX uses a normal SQL Server server or its own implementation, the former is true. And the general best practices applies regardless a SQL Server instance is used by Dynamics AX, Sharepoint, a custom application or by anything else.

  • imecoax Profile Picture
    imecoax 680 on at

    Perfect - thanks.

    And what do you think about how the VHD's should be attached to the SQL VM?

    Sepapare VHD's for the Data, Log, Tempdp, on attached to separate SCSI controllers?

    Thanks!

  • imecoax Profile Picture
    imecoax 680 on at

    Just a little bump.. Anyone?

  • Martin Dráb Profile Picture
    Martin Dráb 237,965 Most Valuable Professional on at

    I'm not sure what your question means. How would you place data and logs, for example, to distinct physical disks, if you kept them in the same virtual disk?

  • imecoax Profile Picture
    imecoax 680 on at

    Your absoultey right..! My mistake..! Wrote the post too quickly :)

    Off course they would have to be separate VHD's so that can be placed on these separate Raid Array's.

    Would you attach them to separate SCSI controllers each or all attached to one?

  • imecoax Profile Picture
    imecoax 680 on at

    Sorry was just wondering what the advice would be on the last part of the question.

    Would you attach them to separate SCSI controllers each or all attached to one?

    Sorry to bother again :)

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans