Splitting SQL Databases from Main App Server

Yahoo Archive
1
Sharon,

Here's a suggestion as a starting point. First give your server time to
stabilize as you have probably already observed that _proapsv.exe
processes grow over time but they reach a limit.

Sum the RAM used by _proapsv.exe processes.

Then sum the RAM used by other processes excluding sqlserv.exe and it to
the sum for _proapsv.exe.

Leaving 2GB free, allocate the remainder to SQL server.

So something like this ...

SQLMaxRAM = (TotalServerRAM - (Sum(_proapsv) + Sum(OtherNotSQL))) - 2GB

Brad


--- In vantage@yahoogroups.com, "lapulsifer" <sharon.pulsifer@...>
wrote:
>
> Hi Brad.
>
> How do you have SQL server memory options configured? What percentage
of total ram do you have in the Maximum Server Memory setting.
>
> We run Vantage and SQL on the same server. I'm not exactly sure how
much memory to limit SQL to for optimum performance.
>
> We run Windows 2003 Stn 64-bit. Vantage 8.03.405. SQL 2005 Standard
64-bit. Dual-core Quad processors and 12gb of ram.
2
I've got a test environment setup where we are running SQL2005 on the same server as Epicor 9. I plan on converting to a production environment where Epicor 9 and Progress will run on one server and SQL databases on another server.
Â
Other than setting up DNS and checking ODBC connections any other recommendations from the group in terms of performance and/or basic setup advice?




[Non-text portions of this message have been removed]
3
Mark, out of pure curiosity, what's your reasoning for segregating services?



Vic


-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Mark Wagner
Sent: Wednesday, July 08, 2009 9:41 AM
To: Vantage User Group
Subject: [Vantage] Splitting SQL Databases from Main App Server

I've got a test environment setup where we are running SQL2005 on the same server as Epicor 9. I plan on converting to a production environment where Epicor 9 and Progress will run on one server and SQL databases on another server.
Â
Other than setting up DNS and checking ODBC connections any other recommendations from the group in terms of performance and/or basic setup advice?




[Non-text portions of this message have been removed]



------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
4
Its a corporate IT policy thing. We are a division of a much larger company and their policy is to run SQL on separate servers (its a backup/convenience thing for them more than anything)

--- On Wed, 7/8/09, Vic Drecchio <vic.drecchio@...> wrote:


From: Vic Drecchio <vic.drecchio@...>
Subject: RE: [Vantage] Splitting SQL Databases from Main App Server
To: vantage@yahoogroups.com
Date: Wednesday, July 8, 2009, 10:18 AM








Mark, out of pure curiosity, what's your reasoning for segregating services?

Vic

-----Original Message-----
From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On Behalf Of Mark Wagner
Sent: Wednesday, July 08, 2009 9:41 AM
To: Vantage User Group
Subject: [Vantage] Splitting SQL Databases from Main App Server

I've got a test environment setup where we are running SQL2005 on the same server as Epicor 9. I plan on converting to a production environment where Epicor 9 and Progress will run on one server and SQL databases on another server.
Â
Other than setting up DNS and checking ODBC connections any other recommendations from the group in terms of performance and/or basic setup advice?

[Non-text portions of this message have been removed]

------------ --------- --------- ------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups. yahoo.com/ group/vantage/ files/.
(2) To search through old msg's goto: http://groups. yahoo.com/ group/vantage/ messages
(3) To view links to Vendors that provide Vantage services goto: http://groups. yahoo.com/ group/vantage/ linksYahoo! Groups Links



















[Non-text portions of this message have been removed]
5
Have you noticed any performance degradation?

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Mark Wagner
Sent: Wednesday, July 08, 2009 10:39 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Splitting SQL Databases from Main App Server

Its a corporate IT policy thing. We are a division of a much larger company and their policy is to run SQL on separate servers (its a backup/convenience thing for them more than anything)

--- On Wed, 7/8/09, Vic Drecchio <vic.drecchio@...> wrote:


From: Vic Drecchio <vic.drecchio@...>
Subject: RE: [Vantage] Splitting SQL Databases from Main App Server
To: vantage@yahoogroups.com
Date: Wednesday, July 8, 2009, 10:18 AM








Mark, out of pure curiosity, what's your reasoning for segregating services?

Vic

-----Original Message-----
From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On Behalf Of Mark Wagner
Sent: Wednesday, July 08, 2009 9:41 AM
To: Vantage User Group
Subject: [Vantage] Splitting SQL Databases from Main App Server

I've got a test environment setup where we are running SQL2005 on the same server as Epicor 9. I plan on converting to a production environment where Epicor 9 and Progress will run on one server and SQL databases on another server.
Â
Other than setting up DNS and checking ODBC connections any other recommendations from the group in terms of performance and/or basic setup advice?

[Non-text portions of this message have been removed]

------------ --------- --------- ------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups. yahoo.com/ group/vantage/ files/.
(2) To search through old msg's goto: http://groups. yahoo.com/ group/vantage/ messages
(3) To view links to Vendors that provide Vantage services goto: http://groups. yahoo.com/ group/vantage/ linksYahoo! Groups Links



















[Non-text portions of this message have been removed]



------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
6
Don't know havent tried it yet but my guess is that any network latency might have an impact but I expect it to be minor

--- On Wed, 7/8/09, Vic Drecchio <vic.drecchio@...> wrote:


From: Vic Drecchio <vic.drecchio@...>
Subject: RE: [Vantage] Splitting SQL Databases from Main App Server
To: vantage@yahoogroups.com
Date: Wednesday, July 8, 2009, 10:51 AM








Have you noticed any performance degradation?

-----Original Message-----
From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On Behalf Of Mark Wagner
Sent: Wednesday, July 08, 2009 10:39 AM
To: vantage@yahoogroups .com
Subject: RE: [Vantage] Splitting SQL Databases from Main App Server

Its a corporate IT policy thing. We are a division of a much larger company and their policy is to run SQL on separate servers (its a backup/convenience thing for them more than anything)

--- On Wed, 7/8/09, Vic Drecchio <vic.drecchio@ timco.aero> wrote:

From: Vic Drecchio <vic.drecchio@ timco.aero>
Subject: RE: [Vantage] Splitting SQL Databases from Main App Server
To: vantage@yahoogroups .com
Date: Wednesday, July 8, 2009, 10:18 AM

Mark, out of pure curiosity, what's your reasoning for segregating services?

Vic

-----Original Message-----
From: vantage@yahoogroups .com [mailto:vantage@ yahoogroups .com] On Behalf Of Mark Wagner
Sent: Wednesday, July 08, 2009 9:41 AM
To: Vantage User Group
Subject: [Vantage] Splitting SQL Databases from Main App Server

I've got a test environment setup where we are running SQL2005 on the same server as Epicor 9. I plan on converting to a production environment where Epicor 9 and Progress will run on one server and SQL databases on another server.
Â
Other than setting up DNS and checking ODBC connections any other recommendations from the group in terms of performance and/or basic setup advice?

[Non-text portions of this message have been removed]

------------ --------- --------- ------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups. yahoo.com/ group/vantage/ files/.
(2) To search through old msg's goto: http://groups. yahoo.com/ group/vantage/ messages
(3) To view links to Vendors that provide Vantage services goto: http://groups. yahoo.com/ group/vantage/ linksYahoo! Groups Links

[Non-text portions of this message have been removed]

------------ --------- --------- ------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups. yahoo.com/ group/vantage/ files/.
(2) To search through old msg's goto: http://groups. yahoo.com/ group/vantage/ messages
(3) To view links to Vendors that provide Vantage services goto: http://groups. yahoo.com/ group/vantage/ linksYahoo! Groups Links



















[Non-text portions of this message have been removed]
7
Mark,



Concerning SQL performance, there is no shortage of advice out there but
we have found that drive speed makes the biggest difference. Increasing
the number of drive spindles in your array is a best practice for SQL
but we did not see much gain.



In our tests, a single 15.5k RPM SAS drive significantly outran a 14
spindle RAID 10 array of 7,200 RPM SATA drives. Epicor makes a big deal
about not using RAID 5 but we found that a RAID 5 configuration with
3x15.5k RPM SAS drives was significantly faster than a 14 spindle RAID
10 array of 7,200 RPM SATA drives. We settled on a RAID 10 configuration
with 14x15.5 RPM SAS drives which produces very good performance.



Increasing network bandwidth between the app server and the SQL server
also improves certain things but in our testing, it was not a big factor
for interactive Vantage use. SQL restores is where we saw the greatest
improvement.



SQL Server 2005 makes good use of multiple CPUs but we found that clock
speed is the most important factor. For instance, we got better
performance from 4 cores running at 3GHz than 8 cores running at 2.4GHz.



Over time SQL Server will consume all the available RAM in your server
so you should set a limit.



To make the connection from the Epicor 9 app server to the SQL server,
you just need a working DSN that is referenced in your mfgsys.pf file.
Epicor recommends that you set your schema holders to read-only for
performance. I can send instructions if you need.



If you want to drill further into any of this, let me know.



Brad Feazell
Corporate IT Manager | Dril-Quip, Inc.





[Non-text portions of this message have been removed]
8
Thanks Brad, very helpful. I'm going to start this weekend and see how it goes and if anything else comes up I will let you know
Thanks again

--- On Wed, 7/8/09, FEAZELL, Brad <Brad_feazell@...> wrote:


From: FEAZELL, Brad <Brad_feazell@...>
Subject: [Vantage] Re:Splitting SQL Databases from Main App Server
To: vantage@yahoogroups.com
Date: Wednesday, July 8, 2009, 1:57 PM








Mark,

Concerning SQL performance, there is no shortage of advice out there but
we have found that drive speed makes the biggest difference. Increasing
the number of drive spindles in your array is a best practice for SQL
but we did not see much gain.

In our tests, a single 15.5k RPM SAS drive significantly outran a 14
spindle RAID 10 array of 7,200 RPM SATA drives. Epicor makes a big deal
about not using RAID 5 but we found that a RAID 5 configuration with
3x15.5k RPM SAS drives was significantly faster than a 14 spindle RAID
10 array of 7,200 RPM SATA drives. We settled on a RAID 10 configuration
with 14x15.5 RPM SAS drives which produces very good performance.

Increasing network bandwidth between the app server and the SQL server
also improves certain things but in our testing, it was not a big factor
for interactive Vantage use. SQL restores is where we saw the greatest
improvement.

SQL Server 2005 makes good use of multiple CPUs but we found that clock
speed is the most important factor. For instance, we got better
performance from 4 cores running at 3GHz than 8 cores running at 2.4GHz.

Over time SQL Server will consume all the available RAM in your server
so you should set a limit.

To make the connection from the Epicor 9 app server to the SQL server,
you just need a working DSN that is referenced in your mfgsys.pf file.
Epicor recommends that you set your schema holders to read-only for
performance. I can send instructions if you need.

If you want to drill further into any of this, let me know.

Brad Feazell
Corporate IT Manager | Dril-Quip, Inc.

[Non-text portions of this message have been removed]



















[Non-text portions of this message have been removed]
9
I'll agree with Brad here. I didn't do testing to compare, but we chose 15K SAS drives in a RAID10 config (6 spindles total), plus separate RAID10 volumes for OS and Logs (adding a new one for TempDB soon once the disks arrive). We get good performance.

Brad, I'm curious about your experience with MSSQL on 64-bit. I have 14GB RAM + 4GB swap on my MSSQL box, with 2 instances of MSSQL running (one for non-Vantage stuff). I have given 8GB over to the Vantage MSQSL instance but occasionally see problems with memory contention. I'm thinking I may just need to dial down SQL's working memory a bit but don't want to break things.

-bws

--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix / Picometrix
    bspolarich@... ~ 734-864-5618 ~ www.advancedphotonix.com


-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of FEAZELL, Brad
Sent: Wednesday, July 08, 2009 1:58 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re:Splitting SQL Databases from Main App Server


In our tests, a single 15.5k RPM SAS drive significantly outran a 14
spindle RAID 10 array of 7,200 RPM SATA drives. Epicor makes a big deal
about not using RAID 5 but we found that a RAID 5 configuration with
3x15.5k RPM SAS drives was significantly faster than a 14 spindle RAID
10 array of 7,200 RPM SATA drives. We settled on a RAID 10 configuration
with 14x15.5 RPM SAS drives which produces very good performance.



Increasing network bandwidth between the app server and the SQL server
also improves certain things but in our testing, it was not a big factor
for interactive Vantage use. SQL restores is where we saw the greatest
improvement.



SQL Server 2005 makes good use of multiple CPUs but we found that clock
speed is the most important factor. For instance, we got better
performance from 4 cores running at 3GHz than 8 cores running at 2.4GHz.



Over time SQL Server will consume all the available RAM in your server
so you should set a limit.



To make the connection from the Epicor 9 app server to the SQL server,
you just need a working DSN that is referenced in your mfgsys.pf file.
Epicor recommends that you set your schema holders to read-only for
performance. I can send instructions if you need.



If you want to drill further into any of this, let me know.



Brad Feazell
Corporate IT Manager | Dril-Quip, Inc.





[Non-text portions of this message have been removed]



------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
10
Brian,



We've had a good experience with SQL 2005 64-bit and generally good
success with 64-bit Windows servers for all apps (including 32-bit
apps).



On your swap setting, is 4GB the total size of virtual memory? If so,
you may be under-configured on a 14GB server. Without taking application
specific requirements into account, I think the best practice is to set
virtual memory at 1.5 x physical RAM as a starting point.



On TEMPDB, in addition to dedicated drives, another best practice is to
configure as many data files as you have CPU cores.



Just FYI, we ran some tests with TEMPDB on 7,200 RPM SATA drives
compared to 15.5k RPM SAS drives and saw no difference. We used the
schema change scripts from Vantage 8 to Epicor 9 as our test for this,
maybe those don't make extensive use of TEMPDB. On our system, the
schema change ran from approx 5 to 8 hours on a 60GB database depending
on the way we setup the drives. I think TEMPDB usage varies by
application so our test may not be indicative of interactive Vantage
performance.



I think you can safely reduce SQL memory by 10-20% as a trial. We did
another test on a 16GB box with SQL set at 12GB and 10GB with no
difference in performance. For sure, if you let SQL consume all
available RAM you will have performance problems, we have experienced
this. I find it a little surprising that the default setting for SQL
server is to use unlimited RAM even though this setting is likely to
cause performance problems on a heavily loaded server. SQL server is
generally good about automatically adjusting itself for maximum
performance but this is an exception.



Brad Feazell
Corporate IT Manager | Dril-Quip, Inc.





[Non-text portions of this message have been removed]
11
Hi Brad.

How do you have SQL server memory options configured? What percentage of total ram do you have in the Maximum Server Memory setting.

We run Vantage and SQL on the same server. I'm not exactly sure how much memory to limit SQL to for optimum performance.

We run Windows 2003 Stn 64-bit. Vantage 8.03.405. SQL 2005 Standard 64-bit. Dual-core Quad processors and 12gb of ram.

--- In vantage@yahoogroups.com, "FEAZELL, Brad" <Brad_feazell@...> wrote:
>
> Brian,
>
>
>
> We've had a good experience with SQL 2005 64-bit and generally good
> success with 64-bit Windows servers for all apps (including 32-bit
> apps).
>
>
>
> On your swap setting, is 4GB the total size of virtual memory? If so,
> you may be under-configured on a 14GB server. Without taking application
> specific requirements into account, I think the best practice is to set
> virtual memory at 1.5 x physical RAM as a starting point.
>
>
>
> On TEMPDB, in addition to dedicated drives, another best practice is to
> configure as many data files as you have CPU cores.
>
>
>
> Just FYI, we ran some tests with TEMPDB on 7,200 RPM SATA drives
> compared to 15.5k RPM SAS drives and saw no difference. We used the
> schema change scripts from Vantage 8 to Epicor 9 as our test for this,
> maybe those don't make extensive use of TEMPDB. On our system, the
> schema change ran from approx 5 to 8 hours on a 60GB database depending
> on the way we setup the drives. I think TEMPDB usage varies by
> application so our test may not be indicative of interactive Vantage
> performance.
>
>
>
> I think you can safely reduce SQL memory by 10-20% as a trial. We did
> another test on a 16GB box with SQL set at 12GB and 10GB with no
> difference in performance. For sure, if you let SQL consume all
> available RAM you will have performance problems, we have experienced
> this. I find it a little surprising that the default setting for SQL
> server is to use unlimited RAM even though this setting is likely to
> cause performance problems on a heavily loaded server. SQL server is
> generally good about automatically adjusting itself for maximum
> performance but this is an exception.
>
>
>
> Brad Feazell
> Corporate IT Manager | Dril-Quip, Inc.
>
>
>
>
>
> [Non-text portions of this message have been removed]
>