Single instance vs. multiple instances of SQL Server on a server

Let`s consider a situation when you need to build a server with SQL Server used by multiple client applications. Here are two options to consider.

We`ve created a table with comparison of these two options.


 Comparison of single instance to multiple instances SQL Server installation

 

Single instance

Multiple instances

Separate Security between databases and applications

 

Better

Memory usage

Better

 

TempDB disk usage

Better

 

SLA differences

 

Better

CPU usage

better

 

IO

Better

 

Ease of administration

Better

 

Overall performance

Better

 

Multiple test and dev environments

 

Better

Cost of licensing

Cheaper

 

Different SQL Server versions for backward compatibility

 

Better

Demo purposes

 

Better

Monitoring

Better

 

Conclusion

From the table above we can see that having multiple instances of SQL Server on a single server could be beneficial only in a specific cases when you need a great degree of isolation for the environment, otherwise consider using single instance installation.

 

acyclovir cheap onlinebuy generic acyclovir cream onlineaciclovir cream buy online ukwhere can i buy acyclovir cream onlineaciclovir tablets buy online australiaaciclovir tablets 800 mg buy onlinebuy aciclovir tablets online ukbuy acyclovir cream for genital herpeswhere can i buy acyclovir creambuy acyclovir cream 5
  • Troy says:

    Good Post. I think you should qualify some of your statements as some of the choices you’ve stated are better from a single-instance are only true in certain conditions. For my background, I’ve been involved with development, administration, and managing DBAs for 10 years in an enterprise setting with nearly 1000 instances. My thoughts on your post are below.

    1. For most “small shops” this would be a good guide.
    2. All Disk and IO concerns are not really valid. Writing data to the data and log files will happen in a single or multi-instance environment regardless if those data and log files are placed on the same disk / array. So whether those databases are in a single versus multi instance setup is irrelevant. This includes TempDB – as opposed to a single large tempdb, you’ll have two smaller ones.
    3. You’re gain in CPU / memory usage is only in the actual “SQL Server Overhead”. SQL Server’s internal overhead for CPU and memory is minimal and not really a concern (a few hundred MBs of memory and virtually idle CPU usage).
    4. Your monitoring and troubleshooting is actually worse on a single instance machine because the difficulty and complexity of trying to separate out which application is causing the issue can become a problem. You have no clear delineation between which application is consuming how much of the servers resources (memory is a prime example). Using separate instances, you can provide this type of delineation and have a clear picture on which instance is consuming which resources. Advanced DBAs would be able to use the Resource Governor features to limit resource availability, but again, that adds a layer of complexity that unless well documented can become extremely difficult to troubleshoot if you don’t know it exists.
    5. Licensing is actually a wash. Based on *my understanding* of Microsoft’s licensing, once you license a certain version and edition of SQL Server for a server, you can use as many instances on that server as you wish so long as those instances remain on the same version and edition. This is the same for virtualization – as long as your host is licensed for Windows / SQL, you can have as many VMs as the OS allows (Datacenter allows unlimited, Standard allows 4) running Windows / SQL because you have purchased the licenses at the host level.

    Thanks for Sharing!
    Troy

  • JeremyH says:

    Why would the cost of licensing be cheaper if you have a single instance? You are licensing the cores, not the instances.

  • Buy acyclovir ointment, Can i buy aciclovir over the counter uk