Solved

SQL Server Best Practice

Posted on 2014-10-23
3
73 Views
Last Modified: 2015-02-19
We are preparing to implement a Microsoft SQL Server to host Databases for different applications. I am concerned about conflict/contention/bottlenecks with multiple UserDBs and the TempDB. Does it matter if we put all Databases on the same instance or would we be better served to use separate instances therefore utilizing separate TempDBs?
0
Comment
Question by:DKHenery
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 36

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40399856
Typically, test and production instances are separate for safety.  You want production to be as insulated from testing as possible.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40399912
It depends on what you need those instances for but as Paul said, if you plan to put production and test/dev environments on teh same server then I recommend against it.

In regards to tempdb itself it is better to setup any instance to have tempdb files on separate drives than the regular databases, if possible. Also you want to create or move them on a different drive then C, which is default. Tempdb could grow to sizes that will surpass a typical C drive. Ideally you would want to separate your mdb,ndb,ndx(data) files and ldf(log) files on different drives as well, temdb and regular dbs, if possible.
0
 
LVL 1

Author Comment

by:DKHenery
ID: 40400201
Thank you for the input... I do understand the importance of moving the tempDB from the default location. My real concern is, I have 2 applications that run nightly ETLs which rely heavily on the TempDB during their processes. Would the best practice then be to run 2 Instances of SQL so they are not both hitting the same TempDB?
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

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…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

726 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