September 3, 2008 at 3:15 am
The Gurus,
I am in a cross-road as to what to do. I have 7 various applications that uses SQL server backend to store data. Presently, I put all the 7 in the same instances. A colleague is insisting that all the 7 should be on a separate virtual server not even a different instance. The issues that he raised is copied below
"I’d like also to restated my concern for reducing the number of virtual instances. Whilst I can see it is very realistic for applications to share the same instance, and still maintain database security between applications, the actual service (application) delivery is more than just about the database. I’d like assurance we are not creating more work further down the line.
Specifically, can you confirm that the separate application specific databases can be maintained at different levels / versions as required to support the individual applications. Applications can be upgraded individually in the future (even if that includes a respective database upgrade) without impact on the other applications / databases. If there is a SQL database failure it will not impact multiple applications rather than just one.
There are other concerns. Reducing the number of instances increases the risk of application incompatibility. With multiple instances also if an application fails or there is some “hardware” related interruption, the individual instance can be restarted without impact to the other instances. We will lose this benefit if installing all applications in the same instance.
I might raise other questions but I think that demonstrates the nub of my concern."
These databases are very small and from the present usage, I do not see them growing so large in the future.
Could you please, advise me on what to do?
Many thanks.
Sahoong.
September 3, 2008 at 5:21 am
Any time you hold multiple instances on one server you add some complications to your maintenance so, with that being said, your coworkers concerns are not unfounded. While I believe you could answer yes to most of those questions that he / she asks, the question about whether downtime of one would affect all is very valid. What you are potentially doing in having all the instances on the same box is forcing them into the same Service Level agreement even if they may be distinctly different.
An example would be if instance 1 is a business critical database and the application is highly used by all internal employees while instance 4 is a help desk application and can easily suffer downtime in the middle of the day without impacting much business workflow. Now any maintenance that needs to be done to the server in order to support the help desk application will directly affect this business critical application. Yes, you could perform this work during "off" hours but there is still a downtime that impacts this business critical application. If your applications are all of a similar nature and you can group them into similar service levels etc then this risk might be small as well.
On another note, why are you breaking them down into multiple instances? Are there different requirements for database version levels associated with the applications? If so, it would be nice to get them to the same version level and get them on one instance. Also, if you are using a virtual server which it seemed like you were from the post, it would seem like it might be easier to divide up the resources appropriately and split these out to different virtual servers than trying to manage the differences of having them on one "box".
Ultimately by having the multiple instances you are having to manage memory and resources associated with the instances so as not to conflict with each other and that alone can be somewhat risky especially when you have one application that the utilization spikes and now the cpu resources for the whole box are being consumed by one instance. Additionally, you still only have 2 Gig of VAS memory so there is always going to be contention for those aspects of the SQL Server engine that can only operate in that portion of memory.
I am not stating that SQL Server can't handle the multiple instances on the one box, but the complications of managing that would seem to be more than one would want to own.
Hope this helps a little.... 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 3, 2008 at 6:10 am
A very big thank to you Davids. I turely appreciate the time taken to provide a response.
My coleague is not only asking for a separate instance but a separate SQL server box for each of the application. All the apps utilises the same version of SQL server and are ok with the same SLA.
All you wrote in your response supported what I proposed except for a situation when one of the databases fail. Like I said, these apps are light wait and doesn't do anything special. For instance, one of the app just write to a database when any of the clients printed on the newtwork. For instance, if I print a word document or simply send this forum page to the printer, the application will write a record to the database that will contain my domain username and number of pages printed. These are simple databases and none of them is up to 4MB (data + log files).
Again, none of them has any web interface or web functionality. They are simply installed on the client and point to the SQL server database.
Personally, I really do not see any reason for mutiple instances or different box for each of the SQL server but his argument is that the initial arrangements was that there should be a separate SQL server box for each of the applications.
The reason why I posted this message is to enquire if different application could write to the same instance of SQL server. Please, note that each of them write to separate databases but all the databases are in the same instance of SQL server. Again, these are light database with few usage and little importance so to say.
Once gain, tx.
September 3, 2008 at 7:22 am
Hi David,
Please, find below an excerpt from your response to my post
"Also, if you are using a virtual server which it seemed like you were from the post, it would seem like it might be easier to divide up the resources appropriately and split these out to different virtual servers than trying to manage the differences of having them on one "box"."
Yes we are using virtual servers but what do you mean by dividing up resources and splitting them out to different virtual servers? Are you asking me to put each databases on different virtual servers (separate computers) or what? Please, expantiate.
Many tanks.
Sahoong.
September 3, 2008 at 7:24 am
Is you coworker asking for separate servers or separate virtual servers?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 3, 2008 at 8:01 am
From what I'm reading, I would see 7 virtual servers for 7 applications as a serious waste of disk space and hardware resources. That judgement is based on your assertion that the databases are small and low-traffic.
If they are all on one server, virtual or otherwise, SQL can manage RAM, CPU, etc., more efficiently. On seven separate virtual servers, each installation of SQL Server will grab a certain amount of RAM and never let it go, even if that that particular database (application) sees little/no traffic. That's just one example of the problems.
I could be mistaken, but if you install these as 7 virtual servers, you'll need 7 copies of Windows Server and 7 copies of SQL Server. That's all one license each, but it's a huge amount of disk space just to run 1 small database each. My installation of SQL Server takes 14.8 Gig of disk space (that's the size of C:\Program Files\Microsoft SQL Server folder). 7 copies of that is over 100 Gig of hard drive space. The Windows folder (which is only part of the OS) is 8.5 Gig. 7 copies of that is another 60 Gig. All that to support less than 30 Meg of databases (per your posts) seems excessive to me. Note: I haven't played with virtual servers much, so could be completely wrong about the disk requirements. I used VirtualPC a couple of years ago and remember having to install an OS in the virtual hard drive, and SQL also, but that could have been an atypical set-up.
The concerns about maintaining different application versions aren't critical. You can upgrade database code, tables, etc., for application versions, on a database-by-database basis. Even if you upgrade to a newer version of SQL Server, you can maintain old databases in lower compatibility modes. Microsoft's current practice is to support two versions back.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 3, 2008 at 8:23 am
If the databases are small and not of particular criticality ("Again, these are light database with few usage and little importance so to say."), one server should be fine. The key thing is, you now have a single point of failure for ALL your applications. The flip side, if each app has it's own server is the dreaded SQL Server sprawl - potential under-utilized servers for each application (been there, done that). If you can setup a failover system, whether clustering or transactional replicaton, to alleviate the single point of failure, you'll be in a fairly better situation. In addition, if you can beef-up your current server (2Gb of memory is relatively low, depending on your apps/data). And as David stated, can you upgrade all application databases at the same time without issue? If not, you're better served seperating the servers by "like upgradable" applications.
-- You can't be late until you show up.
September 3, 2008 at 1:56 pm
I would call worries about SQL upgrade paths a form of premature optimization at this point. In the event that you need to upgrade one database to SQL 2011 (or some such), but leave the rest on 2005, and can't just use compatibility levels for some reason, moving that database to a different server then is no more work than moving it now, but saves you the work of moving the other six unnecessarily.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 3, 2008 at 3:55 pm
Sorry for taking so long to reply again on this but if they are the same SLA and the same version of SQL then I would only use one instance on one virtual server. The VM solution should take care of making it highly available and the SQL Server instance should be able to handle anything that you are describing if they are light weight. Make sure the instance and the VM server is configured correctly and you should be fine.
Again, sorry for the slow reply and please reply again if you have more questions.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 3, 2008 at 4:22 pm
DavidB (9/3/2008)
The VM solution should take care of making it highly available
that would be depend on what virtualisation platform is employed and how it is licenced\configured!
7 separate VM's for homing each database is definitely overkill and overspend. Multiple instances on a single VM is not unreasonable, but it sounds like 1 VM and 1 instance may do the job for you (and get you some brownie points by saving shed loads of cash in licensing fees 😉 )
Out of interest which hyper-visor do you use?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 4, 2008 at 1:30 am
Just bear in mind that currently SQL 2005 is not completely supported on any form of virtual machine and that SQL 2008 is only currently completely supported on Hyper-V
http://support.microsoft.com/kb/956262
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 4, 2008 at 6:08 am
they will support you up to a point and that point is where they believe the hyper visor is causing the problem. Note their term "commercially reasonable support". If its something they cant diagnose they'll just ask you to go off and prove it happens in a physical environment :Whistling:
we had a problem recently with a SQL VM which we had logged with our support vendor and in the end we actually managed to fix it between ourselves and a well known sql backup software vendor 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply