?
Solved

Is there a best practice for having separated an application and a SQL Database?

Posted on 2015-01-21
13
Medium Priority
?
76 Views
Last Modified: 2015-02-06
I am having a big problem, we have a COFIDI (A software for emitting invoices) application and the SQL database in the same server. The problem is that frequently,, the server stops due to memory saturation, and we have increased the amount of memory 3 times,, and every time the server takes all the memory. I know there is a best practice in the market for having separated these two components (application and SQL database). Can someone help me?
0
Comment
Question by:Apolo Victores
  • 7
  • 4
  • 2
13 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40562536
Are you setting 'max memory' configuration in SQL Server to limit it so that it doesn't take all the RAM?
0
 

Author Comment

by:Apolo Victores
ID: 40562779
Yes!, but It continues "eating"  all the memory,, and I need to separate the application and the SQL DB,, but I need to support it by this Best practice..
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40563095
So what help, specifically, do you need?
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 

Author Comment

by:Apolo Victores
ID: 40564193
I am so sorry Scott!, I do know I need to plan a better plan for the design and implementation, but currently I am taking the responsibility of this server/app , and as I think this current scheme is wrong ,I mean having the application and the Database in the same server, so in order to justify my asseveration I need to look into the best practices in the market.
Thanks!!
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40566055
How much memory the server has?
Also, a part from application and SQL Server, what else is installed in the server?
0
 

Author Comment

by:Apolo Victores
ID: 40575143
It has 16 GB, and there is just installed  the Application and the SQL  software (From Microsoft).
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40575170
Which versions and editions of WIndows Server and SQL Server are installed?
Are both 64bits?
0
 

Author Comment

by:Apolo Victores
ID: 40575189
yes, both are 64 Bits
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40575200
16GB isn't bad for a standard installation and use of SQL Server. How much it's configured as Max Server Memory?
Can you see any locks in the database? Do you know how much memory the application is consuming?
0
 

Author Comment

by:Apolo Victores
ID: 40582687
I have seen that the application just consume 4 MB of Memory, but the SQLSRVR consumes the total memory,, and  I don´t know why,, also I would be so happy to restrict to the SQLSRVR  just to 12 GB of memory, in order to use the rest to trhe application.

Thanks!
0
 
LVL 53

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 40583452
You change the maximum memory using the GUI. In SSMS right-click on the instance name and chose properties, then click the Memory node and under Server Memory Options enter the amount that you want for maximum server memory.

You can also do that with T-SQL. Just open a new query window in SSMS and paste the following code:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'max server memory', 12288
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'show advanced options', 0;
GO
RECONFIGURE WITH OVERRIDE

Open in new window

0
 

Author Comment

by:Apolo Victores
ID: 40586527
Thank you !, I will try and inform you!
0
 

Author Closing Comment

by:Apolo Victores
ID: 40594061
Thank you so much !  these instructions helped me a lot!
Now , I can see that the sqlsvr  process only takes these 12 GB of memory,, and also see that the application also consumes the 7 rest of the ramian memory.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question