Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-11 : 10:55:08
|
| lehan writes "We will upgrade from SQL v7 to 2000. My project leader wants to utilize the SQL 2000 new feature - instances (He was the DBA for Oracle database). Several of our applications are very small and have little usage. What is the pros and cons of having an individaul instance for each application? Can we lump all the little databases in one instance and let other large databases having their own instances? What do other DBAs choose to do in this situation?Your help will be appreciated." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-11 : 11:19:14
|
The real benefit of named instances is that they allow you to run multiple VERSIONS of SQL Server on the same computer. That way, you're not completely forced to upgrade a SQL Server from version 7.0 to 2000, you can run both side-by-side. You could also use separate instances for an online transaction system and another for online anaylis systems. Also, if you need to keep two SQL servers and their data separate for security issues, multiple instances can provide that security. Books Online has lots of information on multiple instances.The only problem with multiple instances is that they use far more resources than a single instance does. The performance hit may not make using multiple instances worthwhile. If you really need your large databases to have high performance, putting them on separate instances probably won't help...they should be on a separate physical machine. You can certainly try setting up the instances the way you described, but if you see performance issues, you should look to separate them on different machines, or run them on a single instance.If your Oracle DBA chooses to thumb his nose because Oracle does better in this regard, ignore him, or better yet, tell him to spend 10 times as much money for an Oracle setup  It's really impossible to make this kind of comparison, because the operating system will affect the performance more than the software. SQL Server was designed to make use of multi-threaded processes under WindowsNT and 2000, while Unix systems favor multiple-process execution. |
 |
|
|
|
|
|
|
|
SQL 2000 instances