Solved

SQL Server Best Practice

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach 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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 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

20 Experts available now in Live!

Get 1:1 Help Now