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.
Also, with proper gauzing application capacity with perfmon data and other monitoring tools data, we can definitely go for database consolidation and look for creating severity\criticality based environment so that support and cost would be handled easily and your client will be happy too rather than following always thumb rule.
Today's world look for customized and cost effective solution and it has become norm of life that we should give them suitable solution as per their requirement and that is where DBA's challenge comes into place. In my view and as per experience, I do have in this cloud solution oriented world, we should go for consolidation wherever possible with proper monitoring to alert client in proactive manner rather than saying it will be good or bad one for them.
You can always consolidate based on the criticality and availability of the database(low, med, high). you also need to understand and measure the metrics required such CPU., Memory. Disk and Performance before consolidation.
Please read the below link
What is your current server configuration and load ? is there any performance issue or any other application issues are you facing?
If there is no berformance issue and application host support, then there is no harm in putting all the database in one single instance and making necessary changes in the connection setting to point the ritght application and host.
No matters if its 100 databases or 200 databases, only consideration is how big it is and how often its used and queried or how often the reports or what is the volume of the transaction, number of user connections, CPU and memory usage, tempdb usage etc..
If there is no issues with above then 99% there wont be any issues with database level putting under single instance, its just a matter of changing your connection string and redirecting the application to specific sql instance..
SQL 2008 R2 is almost having all the nice sql features and its awesome and sql 2008 R2 is running many organizations machines now a days, no worries about 2012 or 2014.
For your load and DB size you can run under one instances, also please keep a note of your database growth every 3 months, so that you can plan accordingly if there is any with more amount of data growth, and transaction volume and number users ( connections ).
Raju Rasagounder MSSQL DBA
This is just an update on what I did. Because this particular vendors logs in for support issues (They have VPN), we set their DB's in a separate instance on our main SQL server and then installed the management console on the application server so they never have to log into the SQL server.
Because my test DB's all have the same name as my production db's, we set them up in a separate instance. We write a lot of stored procedures and Agent Jobs so I prefer testing those on a non-production instance first.
We did beef up the memory and added another processor to the server (virtual). Currently we are running 4 instances of SQL on the server. Two of those instances were set up because of Vendor Access issues and one was set up for a test instance.
Everthing was working fine up until about 10 days ago, when our VMWare host was changed. We were trouble shooting issues with our Oracle server on the SAN and they decided to move most of the servers off the host that the Oracle server was on. The problem seems to lie in the snaps that are done. Users have been complaining that things are slower now. The VMWare tech is still troubleshooting that.