The new forums will be named Coin Return (based on the most recent vote)! You can check on the status and timeline of the transition to the new forums here.
The Guiding Principles and New Rules document is now in effect.
Server cluster & distributed DB
Richy
Registered User regular
Registered User regular
I want to build a server farm. I have a dozen or so old IBM servers, and I have a large SQL database in Microsoft SQL Server. I’d like to have one gateway server I could send queries to across the internet, and it would dispatch the workload to the other machines and send me back the result.
I have no idea what to do.
1: How do I hook them up?
2: How do I distribute the SQL database?
3: How do I make the whole thing accessible from the internet?
Does anyone here has expertise in this and a little time to kill to help me? Thanks!
I have no idea what to do.
1: How do I hook them up?
2: How do I distribute the SQL database?
3: How do I make the whole thing accessible from the internet?
Does anyone here has expertise in this and a little time to kill to help me? Thanks!
Richy on
0
Posts
In MSSQL-land, clustering is fault tolerance only. What you're talking about is load balancing and SQL doesn't really do that, not like oracle or whatever. You can look into network load balancing for windows server, or a dedicated hardware load balancer from F5 or cisco.
Good luck.
...this is the tricky part, and the best way to do it is going to depend on your application and your needs. Microsoft Cluster Services (MSCS) is fault tolerance only, as PirateJon said. In the Oracle world, the go-to solution for this kind of setup is usually Real Application Clusters, and that's what I've worked with before. From what I've read, in the MS SQL Server world there are a number of ways to do this. This article seems to give a good overview.
At a glance, Distributed Partition Views would seem to be the easiest to implement, assuming you can distribute your tables across servers in a way that results in roughly even load across the systems. This option would be particularly attractive if you've already got an application written, since DPV is supposed to be transparent to the application.
If the application that's going to read from the DB doesn't exist yet, then database load balancing might be possible. You could write your app from the ground up to be cluster-friendly by having perform reads from one random DB in the cluster, and perform writes to all DBs in the cluster.
This again depends on your application, and also on your network infrastructure. It's more of a network security question than a DB question. If your application is web-based, you could just host it internally and poke a hole in your firewall to allow users to load the web page. When they run queries via the web app, all of the DB traffic would happen internally between the web server and the DB server, and the results get returned to the user on standard web ports. If your application is some client executable, then you're going to have to open ports so that the app can talk directly to the DB. For MS SQL Server, I don't know what ports those would be.