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?

  • Why would you not simply put both the management and reporting databases on a single instance? It would be more straightforward and there would not be the overhead required by running two separate copies of SQL Server. If you really need more than one instance, you want to look into the "named instances" feature of SQL Server. – darin strait Jan 13 '11 at 14:31
  • Hi,both instances belongs to different applications and First Instance would use the DB more then the second. If, One instance can have the dedicated server resource it would be equivalent to having two seperate physical severs. is this possible with SQL 2008? – Rajeev Jan 13 '11 at 14:49

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.

  • Hi Jay, There are two different applications, one DB is for agents management data another one is Reporting DB which will get feeds from multiple applications? The usage of First DB is high, as there would be daily many agents working on it. The second one is reporting DB which will use SSRS to provide different reports. Now, ideally there should be two different physical servers handling each DB independently, but I would like to save the hardware cost hence want to explore the option of having two DB on one physical servers? – Rajeev Jan 13 '11 at 14:55
  • @Rajeev Well, you can create one or two virtual servers on a single machine, but honestly I think you'll get the best performance on a single machine by putting both databases in a single SQL instance. Anything else would mean a lot more overhead. – Jay Jan 13 '11 at 14:58
  • Jay, In case of Production Environment, the virtualization will not be considered. In case of having Single server and Single Instance and having Two databases can Physical resources be dedicated on database level with the help of Resource Governer or with something else? – Rajeev Jan 13 '11 at 15:10

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.

  • Sorry for my limited understanding, but my aim is to understand that if there are two different databases, which will get feed from different application, is it benefitial to have these two on seperate instances or may be on seperate hardware or there is no such need and both databases can be put under single instance; what will happen if one fails and other do not want to fail and would there be any problem with replication to secondary site? – Rajeev Jan 14 '11 at 9:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.