Hello. Doing a College project and i hope i could get some advice. We are building a hybrid cloud. The private cloud will be two sql servers on respective windows 2012 servers spun up on openstack. There will be a third sql server on a windows 2012 vm hosted on amazon ec2. We want to illustrate a hybrid cloud with by accessing these databases to run queries. So naturally we think the best way is to use powershell to load balance or too divert sql queries between the servers. All the windows 2012 severs will be in the same domain. The clients of this network are envision to be sql management gui on our laptops. Any ideas? Thanks in advance. Please be patient as i am new to this.
So… I guess, is there a specific question you’d like help with? I’m sure you can do all that, yes, but I’m not sure what kind of advise you’re looking for.
Load balancing databases is a fairly big, complex topic, and not likely something you want to tackle yourself with PowerShell. Here’s an article you should read, to start: https://devcentral.f5.com/articles/the-challenges-of-sql-load-balancing
If you’re only load balancing SELECT queries, and not modifying the databases, then it’s a simpler problem, but I’d still try to avoid writing my own load balancing code. There are lots of solutions out there for load balancing things like web servers, and those might work with simple read-only database queries as well.
Fair enough. My ignorance is probably showing through in the way i formatted my question. I am new new also to windows servers. I am basically not sure what i am doing. Is there a powershell script which allows one server in my domain to become active with others in a passive mode and at a certain condition; say time of day; to make the other server become the active with the previous active server becoming passive?
My partner knows more about servers. I know SQL server. I will get him to re edit my question if needs be.
Well, your clients need to talk to a SQL server, and you have three of them (which are not clustered, if I’m reading this correctly.) So what you probably need is a load balancer, which would give clients a single point of access to get to the SQL servers.
It’s possible that you could use PowerShell to control the load balancer and change which server is active, but that would depend in the device / OS that’s being used, and what management APIs are available. However, that’s probably not necessary. The load balancer would just distribute traffic between the three SQL servers automatically, based on whatever critera it’s set up to use.
Maybe haproxy on a linux vm ?