Solved

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

Posted on 2015-01-21
13
67 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 69

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 69

Expert Comment

by:Scott Pletcher
ID: 40563095
So what help, specifically, do you need?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 47

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 47

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 47

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 47

Accepted Solution

by:
Vitor Montalvão earned 500 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

770 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