Home / server / Questions / 222012
In Process
Rajeev
Asked: 2011-01-14 05:41:06 +0800 CST

SQL 2008 managing 2 Instances on single Physical server

  • 772

I need some calrification about managing SQL server 2008. The scenario is as follows: I have One Windows Physical server at Primary site, I want to have Two different applications database on it, so shall I create two Instances on same server or shall have diffenrent server for another database. First Database is for management purpose while second would be used for Reporting purpose. There is a second database at the secondary site, which will be in Passive mode and I intend to connect them through MSCS. Now, can I have both Instances on Single server and both will work fine? The management database will be used more. Please reply soon. Can both Instances have dedicated reources allocated to them?

sql-server-2008 sql

2 Answers

  1. From the description, it sounds like these could easily go in a single instance. Any given SQL Server can house many separate databases.

    This is certainly preferable to trying to maintain two separate instances, whether on one or two physical servers, especially if these management and reporting functions are part of the same application/system.

    This is the easiest way to configure things, so it is worth trying out. Only start thinking about a second physical server if you start to have performance problems with the reporting function.

    • 1

  2. Nothing in the original posting points to a need for two instances on the same server. SQL Server will balance resource usage, by demand, across all databases in the same instance. If there is a concern regarding the load generated by the SSRS usage, then this can be managed by utilizing SQL Server tools and well-defined queries.

    There is little benefit to creating two SQL Server instances. When you create two instances, you have to split hardware resources between each instance. Two instances of SQL Server are not able to balance processor, memory, and I/O across all requests as each instances is not aware of the demand-load of the other instance. Two instances requires two different listening ports for client communications and, overall, will require more management. These are strong negative points.

    The information you have provided does not indicate the need for two instances and you continue to push back with needing two instance. It would be helpful to understand the underlying pre-determined need for two instances.

    Help us to help you.

    • 1