July 31, 2007 at 8:11 pm
As a Data architect I have been asked to plan for consolidation of 40 instances of SQL Servers running in the organization on SQL2000 for several business applications, GIS systems, Data Warehouse and infrastructure databases like blackberry, document repository etc. The idea is to save on licencing cost and easy maintainability.
I'm wondering what would be a good approach. Merging everything into one might make it too difficult to maintain 100s of databses in one instance and hardware requirement would be huge. Would it be a better idea of merge all business systems databases and DW in one instance and infrasture into another instance?
Where can I find the best practices and recommendations on enterprise data architecture?
Thanks for your suggestions.
August 1, 2007 at 2:13 am
My first criteria would be load.
Identify the busiest DBs and decide how many machines you have to spread them over.
Then, take the medium DBs and spread them. One or two machines? 3?
Then the very small ones, can they live with the busy ones? Does they load ever spike to a point when it will hurt the main DB on the machine?
Theoretically, having 100s of DB's on a server is possible, piratically, I would not.
I remember reading an article on this. It outlined the fact that each DB needs n amount of resources and you'll get to a point where the machine will almost run out of resources when idle. I'll see if I can dig it up.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 1, 2007 at 2:45 am
having been through a number of studies on consolidation I find that the people/teams producing the plans have no idea on how or what consolidating sql server involves. If you replace 40 servers with 40 instances the DBA still has as many servers to manage - so no gain there.
Biggest issue is disk and memory. w2k3 has an upper limit of 64gb ram and 8 processor sockets., giving each instance 4gb of ram will only allow you to get 14 instances to a server ( allowing for o/s overhead ).
You have a similar issue using virtual server, you have untold licensing issues if you use vmware, plus I personally wouldn't recommend it for a prod environment. Dedicated disks and arrays are another problems and isolation - there are only so many drive letters available, if you've ever had to endure shared luns on physical spindles on a san then you'll understand io contention.
where you can gain is where you have 10 small apps each on their own server, putting all the databases on one instance will certainly give you big gains - but are they all reliable -will the vendor allow you to have a shared instance - most won't support you if you don't have a dedicated server - how many connect as sysadmin this giving one apps user acess to another - if you patch one will that bring all the other down .. not an easy task. I'm not too sure you gain that much going to data centre - 2005 and 64bit will give you more memory and better potential.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 1, 2007 at 5:28 am
I thought with Windows 2003 running out of driver letters was no longer an issue since you can use mount points for drives instead of drive letters. I'm no specialist on Windows though so correct me if I am wrong.
August 1, 2007 at 11:35 pm
Dear all, its totaly going out of mind as I am not able to collect thougths to imagine hudreds of databaes, as our prod server is hardly running 15 databases.
But would like to actually suggest SAN as I have heard that it has got good capabilities to handle database and can accumulate large memory resources.
Thanks,
Jwalant Natvarlal Soneji
August 2, 2007 at 9:10 am
It's not the number of databases you put on a server, but the resources it takes to keep them online without any performance degradation.
For our consolidation, we collected a few weeks of data for total of user connections, total transactions and total CPU per database. We were able to spread the load out over several servers in a cluster environment. We also use the data file sizes and some other information to allocate database storage on a SANS device. This worked best for us.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply