?
Solved

SQL Server Best Practice

Posted on 2014-10-23
3
Medium Priority
?
76 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 38

Accepted Solution

by:
PatHartman earned 2000 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 27

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

764 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