Finance | Project Operations, Human Resources, ...

Windows 2019 and SQL 2014 drive configuration best practices

(0) Share

Report

Posted on by mnola

Hello,

Looking for general Best Practice notes when it comes to running Windows 2019 and SQL 2014 in a VM environment and how the underlying drives (O/S, Data, TempDB, etc.) should be configured. Are the drive configurations in the Dynamics GP System Requirements that we always follow for a Physical Hardware setup still applicable in a VM environment, even though the underlying storage where the VM drives (e.g. C: (O/S), D: (Data), L: (Logs Files), T: (TempDB), etc.) may all end up using the same underlying physical storage hardware?

I understand the importance of separating the O/S, Data, Logs, TempDB into separate drives, regardless if they end up on the same underlying shared storage. Just looking for confirmation that this should result in improved performance. We have a test VM server has plenty of memory and processors assigned to it, but when running a large RM Aged Trial Balance detail report (about 108,000 pages), it takes 3 times as long to complete, compared to the Production Server. One of the main differences between the two environments is that the test machine only has two VM drives (C: (O/S) and D: (Data)). In both environments, the reports both start caching to the screen in about the same amount of time, but as the pages scroll to the screen, the test machine generates an estimated 1000pages/min where the prod machine generates at least 5000pages/minute. The data and resulting reports are the same.

Thanks,

Michael.

Categories:

Microsoft Dynamics GP

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

Justin Sutton on at

Like (0)

Report

Hello Mnola,

Overall, there are no special requirements for using a VM environment.

That said, it is important to note that a lot of times we see performance issues like this, the problem is with the drives being overwhelmed.

I recommend starting with this article, and the WhitePaper it mentions:

community.dynamics.com/.../performance-with-dynamics-gp-where-do-i-start

That said, the community has quite a few VM installs, so they may have other recommendations.

NOTE: Your post is tagged as GP 2018, but you note Windows 2019. If by this you mean Server 2019, please note that only GP 18.2 is supported on Server 2019.

Was this reply helpful? Yes No
mnola 72 on at

Like (0)

Report

Hi Justin,

Thank you for the response. Please note the following:

1. Yes - we are using latest GP 18.3 release in testing.

2. Have seen White Paper before, but there is no mention of VM in it.

3. Looking to see if there is a clear performance improvement when putting SQL files onto separate VM drives, even though they reside on the same physical storage device.

Networking and AV aren't a factor, as we are running the reporting tests directly on the SQL Server itself and in the slow server case, temporarily turned of AV, with little to no improvement (AV has been configured with proper Exclusions too).

Do know that when the report is being published to the Screen, it is doing a lot of file processing in the %TEMP% folder and the issue most likely is Disk - I/O related. Where we are the only ones logged onto the Test Server and this is the only process being run, the CPU usage is very low and the amount of available memory is very high (SQL Server is no where near the max memory setting of 28GB (only consuming about 4GB, leaving free 28GB for the O/S and GP).

We aren't in control of the VM environment (have 3rd party that handles this). My gut feel is that we should have them create a separate drive for the TEMPDB databases and Log files, so they aren't competing with the %TEMP% folder I/Os (even though they end up residing on the same physical storage hardware). However the 3rd party I'm dealing with is adamant that having separate drives will have no factor because it is the same underlying physical storage.

So, looking for something a bit more concrete that possibly recommends we follow the GP System Requirements as it relates to separating SQL storage drives, even though it is on a VM and the underlying storage is the same.

Thank you!

Michael.

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

You now have to configure your host VM server in combination with the virtual images you create. When you create and configure your image you can create multiple drives within that image. I normally pull whatever drives I will assign to have the log files to use the fastest drives inside the drive pool within the host server. So you may designate within your image that the 'C" will hold the OS and the SQL data files are on the 'D' drive and the log files will be on the 'E' drive. As you configure this image point the 'E' drive to pull space from the fastest drive array pool. You can certainly point the tempdb log file to go onto the 'E' drive within your image.Perhaps you can talk to your 3rd party IT firm and have them adjust the configuration.

Was this reply helpful? Yes No
mnola 72 on at

Like (0)

Report

Hi Richard,

Thank you for your response. Believe the underlying storage array is one large array, so might not be a separate/faster array to designate the Log files to. Regardless, in a VM environment, is it still Best Practice to put the O/S on C:, Data on D:, Logs on E: and TEMPDB database on F:? Or does it just make sense to separate the Database Files from the Log Files (C:, D: and E:)? Obviously, if there is an option to put the Log Files on a faster array, will definitely look to see that happen.

However, right now, their are only two drives (C: and D:) on the TEST machine. Which is why I'm questioning if additional drives should be introduced to separate out the TEMPDB database and for good measure, the Log Files. Personally, I would think we will want a C: (O/S), D: (Data), E: (Logs), F: (TEMPDB data) and G: (TEMPDB Log), even if the underlying storage array is the same.

Thanks,

Michael.

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

Michael, in your case I do not think it will make any difference. If all your drives are coming off the same drive array just having them in different folders should have no effect on speed. However, I do prefer having separate folders just from a housekeeping standpoint. I think the bigger issue here is after you have everything setup and people start using GP and run reports, see how fast all your log files grow. Try to identify anything that seems to make them grow. I would think the RM Historical Aged Trial Balance detail report would have a bigger issue. There is a lot more work going on with that report. Also try checking the properties of your tempdb database. What is its starting size and how is it allowed to grow?

Was this reply helpful? Yes No
mnola 72 on at

Like (0)

Report

Hi Richard,

Can't see moving forward with users until we see a significant improvement on the RM Trial Balance Report (really shouldn't be slower, should be same, if not faster).

As for the TEMPDB, it is configured as 8 x 4GB with 64MB autogrowth increments. Running one last test now and will look to see if they grow at all, but not expecting them too, as we are only running the RM TB report.

Thanks again,

Michael.

Was this reply helpful? Yes No

Join the conversation

Community site session details

Windows 2019 and SQL 2014 drive configuration best practices

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

Windows 2019 and SQL 2014 drive configuration best practices

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates