Consolidating database servers

Neither path is necessarily right or wrong as an approach, but the support and development related to each path should be analyzed from an organizational level and then a decision should be made.In some circumstances, both approaches could be used for different reasons.When hardware upgrades and software licensing are budgeted and downtime is expected and acceptable.

For example, if there are five servers being consolidated and each generates 500 read I/O operations per second (IOPS) and 200 write IOPS, the consolidated server will need to support at least 2,500 read IOPS and 1,000 write IOPS.

Will the consolidated server's storage system be able to handle that much I/O?

You either need a couple of instances on the same machine or a couple of VMs, each supporting a different version of SQL Server.

Related: Wait Before You Consolidate The second factor to consider is the permission requirements of the applications running on the SQL Server instances.

If an application requires that the login to the server be a member of the sysadmin fixed server role, you probably don't want to place that application's databases on an instance with other applications because that application could access the other data.

Many third-party vendors say that their applications require sysadmin rights, but often the applications don't.

Both alternatives have advantages and disadvantages, so what is the correct path? When considering migrating to SQL Server 2005 or 2008, should a consolidation effort be considered as well if we have a large number of SQL Servers are in scope for the project?

You are correct, the opposing paths of dedicated versus shared SQL Servers have been major trends for different reasons at many organizations.

One trend was to have a dedicated SQL Server for each application.

This trend has been countered in some organizations by a major consolidation effort.

A Microsoft Biz Talk Server installation may require the creation of up to 13 separate databases in Microsoft SQL Server for use as data stores for various Biz Talk Server features.