Solved

SQL Server Best Practice

Posted on 2014-10-23
3
69 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
3 Comments
 
LVL 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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 shrink a transaction log file down to a reasonable size.

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now