Solved

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

Posted on 2015-01-21
13
68 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 48

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 48

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 48

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 48

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL replication over high latency link 10 52
SQL Backup Question 2 29
Download ms sql express. 2 23
What is the best way to use power bi and ssrs 3 18
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

789 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