Because of SQL licensing changes, the execs are hot on reducing the number of SQL Servers we have. I have approximately 60 databases (all 12GB or less) on one SQL server (production). This server is virtualized and on a SAN. I also have a DB Server for our developers to use that is on the SAN (test). About ½ the databases on the production server are for in house written applications. We have one vendor that remotes in on the production server. This vendor’s db’s are in a separate instance.
In addition we have a SQL server for our Trucking Division that mostly runs vendor applications. (This vendor remotes into the server for issues.) The last server hosts the db’s for a quoting application and SharePoint 2010 (about 40 databases total, 10 or so which are 15-20GB in size). The quoting application vendor also remotes in to support the application. The plan is to virtualize the application servers and get everything on the SAN. Exec wants us to move the databases to the SQL server already on the SAN instead of creating new virtual SQL servers. (Over 110 databases on one server.)
My gut tells me this is a bad idea, but if I don’t want to do it I have to justify why it should not be done. The vendors don’t care for it either but none are saying we can’t do it or they won’t support the app if we do.
All of the SQL Servers are running SQL Server 2008 R2 on 64 bit servers. There are no plans to upgrade the SQL version at this time. We are considering upgrading SharePoint to SharePoint 2013. We run only the standard features of SharePoint. We do run Reporting Services and Integration Services.
So my questions are, how do I determine if this is feasible? What are the pro's/Con's? Where can I find information for building a SQL server like this that hosts the databases for so many varied applications? Should I install multiple instances? I could easily have 5-6 instances just separating out the major vendor applications. Getting enough memory is not an issue.
If your current applications and SQL Server have no performance issue, personally, there is no need to create more instances to separate out the applications. If you persist doing it, then the connection string between the SQL Server and application needs to be modified, if there are many different mapping between application and corresponding database, it will be a big work.Additionally, if there are new databases needs to add into current database in future, then you can consider to create new instances and add new databases into them to reduce the workloads.