April 17, 2012 at 10:27 am
I'm attempting to develop a scale out plan for existing SQL Servers. Current configuration is irrelevant--let's just say I was thrown a garbled mess and tasked with "fixing" it. So I have the option of either attempting to make the bad somehow less bad or designing an entirely new configuration.
What's needed:
- high-availability
- some semblance of load balance
What I'm working with:
- 7-9 physical servers
- 14+ virtual servers
- approx. 20 databases, each with a minimum of 20 tables & approximately 400000+ records per table
What I'm thinking (highly abstracted version):
-Build load balancing into the databases themselves. (literal load balancing of databases)
-Configure clustering across servers with 1 physical server & x virtual servers. Then use either Continuous Replication or AlwaysOn Availability Groups for clusters (to handle failover & synchronization).
Most of the users require only read access. There also needs to be access to live dynamic--on some databases, at least.
Any feedback regarding alternate solutions, where I'm straying, and so forth, is much appreciated.
Thanks.
April 17, 2012 at 10:32 am
Felicia Coons (4/17/2012)
I'm attempting to develop a scale out plan for existing SQL Servers. Current configuration is irrelevant--let's just say I was thrown a garbled mess and tasked with "fixing" it. So I have the option of either attempting to make the bad somehow less bad or designing an entirely new configuration.What's needed:
- high-availability
- some semblance of load balance
What I'm working with:
- 7-9 physical servers
- 14+ virtual servers
- approx. 20 databases, each with a minimum of 20 tables & approximately 400000+ records per table
What I'm thinking (highly abstracted version):
-Build load balancing into the databases themselves. (literal load balancing of databases)
-Configure clustering across servers with 1 physical server & x virtual servers. Then use either Continuous Replication or AlwaysOn Availability Groups for clusters (to handle failover & synchronization).
Most of the users require only read access. There also needs to be access to live dynamic--on some databases, at least.
Any feedback regarding alternate solutions, where I'm straying, and so forth, is much appreciated.
Thanks.
Not sure why you think load balancing is required for your datbases. If the databasses are properly designed and indexed, properly installed on well configured servers (physical or virtual) you may not need load balancing.
If you are using SQL Server 2012, definately look at using AlwaysOn. If you have read online applications, they can use the either syncronous or asyncronous replicas (or what ever the approriate term is) depending on how update to date the data has to be.
April 17, 2012 at 10:43 am
I've been asked to look at load balancing mainly because of the poor (and inconsistent) design of the databases as well as the present server configuration. I also need to make several non-relational DB's relational--but that's a separate problem.
April 20, 2012 at 7:15 am
Felicia Coons (4/17/2012)
I've been asked to look at load balancing mainly because of the poor (and inconsistent) design of the databases as well as the present server configuration. I also need to make several non-relational DB's relational--but that's a separate problem.
I understand each on of these database is about 10M rows in size, total; distributed in about 20 tables.
Those are really small databases no matter how you measure them - are their schemas clone of each other like the same database cloned for 20 different costumers?
I do not think normalizing a 20 tables database will take much time - whoever is asking to look at load balancing is desperately attempting a brute force solution that may end up costing a lot of money and adding levels of complexity.
Any chance of getting a week to redesign one database and show the boss how is it done?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply