Solved

SQL Server Best Practice

Posted on 2014-10-23
3
71 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 35

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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
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…

786 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