| Author |
Topic |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2004-05-27 : 22:07:28
|
I was recently discussing sql-server scaling with an it-manager from a potential buyer of the company I work for and he said some things that made me confused, so now I got the following three questions:1. Is there any significant overhead when using two linked servers in sql-queries in a production environment? Both servers are in the same local network and supporting a website with quite heavy traffic.2. Can filegroups be placed on different physical servers within the same local network?3. Does a sql-server cluster work in the same manner as several webservers with NLB (network load balancing), in the sense that either of the servers are likely to process a request? Or does it just work as a failover?I thought the answers to these questions were1) No 2) Yes 3a) Yes 3b) Nobut this guy said the opposite of all these and didn't really want to leave a chance that any of them were actually possible. Heh, in the case that I am right on all of them, the guy could have saved 4 guys 4-5 months of work (hehe, let's hope I'm right then  )EDIT: Hmm, should maybe have posted this under Administration...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-05-27 : 22:28:22
|
| I would say the other guy is pretty much right.3. Clustering he is definitely correct. A SQL Server cluster is just a failover.1. "Significant" really depends. There is a bit of latency plus the network overhead. This may or may not be significant in your case. Maybe do some testing ?2. Data files on Network shares ? No I'm pretty sure you can't do that.Damian |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-05-28 : 07:44:20
|
| Ok, but what does one do when you need the performance of an extra database-server and cannot upgrade your existing one any more? When you have reached the limit of processors/RAM/++ ? |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-28 : 08:58:58
|
| 1. Like Damian said, there can be significant overhead in linked servers depending on the network overhead. Another problem with linked servers is the efficiency of the plan compilations. Anytime you can avoid them, it's better to do so.2. No. You can divide your traffic up in many web sites (especially hosting companies) by having active/active clusters. This gives you disk redundancy while spreading the traffic over multiple servers. The "load balancing" is a manual process though. You divide the databases evenly over the servers to balance the load.--Theoretically, it's also semi-possible with replication. Your appliction would need to know on state level what database it connected to and maintain that through the session. Haven't ever tried this, but it would be fun to do. :)3. Clustering is failover only of course. It's just server redundancy. If one server fails, the other server picks up.When you get to that point Lumbago, you spend some serious time tuning the environment. When that has been optimized, they need to choke up money for more hardware. They need to decide if they want to continue doing business. Business cost money.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-05-28 : 13:46:29
|
| Ok, so my assumtions were wrong here and thats just fine of course, but I must say I was surprised about the clustering. As you understand I have never worked with it and don't know too much about it.But say you got a seriously big wallet and a seriously heavy load on your website with transactions happening all over the place (i.e. 10k users online at the same time doing 10 transactions each every minute). How would one go about designing a system that would be capable of such loads? |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-28 : 13:54:12
|
| Well, you design a really good database, you test it really well and do a lot of performance tuning.You buy a big system (SAN for fiber disk I/O and a big server like an 8-way with hyper-threading), then you install it and monitor. You continue to tune based on the monitoring. We have thousands of users and run 300-400 transactions/sec. We have peaks well over 1000 t/sec. So, it's not really TOO big of a system. Depending on what you are trying to do, you might want to look at things like:indexed viewsmultiple filegroupsmultiple SANs (that would be kewl)Data Center with very large servers64-bit SQL Serveretc, etc, etc.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-05-28 : 16:36:24
|
| Ok, thanx for the info man... My database is totally lame right now and none of this will really be realised in the near future, but when a potential buyer of the entire company I work for asks "what if all the users of my site were merged over to your site" I need to know what to say. Maybe I'll just give him your number |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-05-28 : 17:56:31
|
Is is a way to load balance across multiple servers, but it's involved. I suggest buying a bigger faster box to get the job done.Do some reasearch on Federated Databases in the BOL or on Google.Here's a cut and paste from BOL:quote:
Designing Federated Database ServersTo achieve the high levels of performance required by the largest Web sites, a multitier system typically balances the processing load for each tier across multiple servers. Microsoft® SQL Server™ 2000 shares the database processing load across a group of servers by horizontally partitioning the SQL Server data. These servers are managed independently, but cooperate to process the database requests from the applications; such a cooperative group of servers is called a federation.A federated database tier can achieve extremely high levels of performance only if the application sends each SQL statement to the member server that has most of the data required by the statement. This is called collocating the SQL statement with the data required by the statement. Collocating SQL statements with the required data is not a requirement unique to federated servers.I It is also required in clustered systems.Although a federation of servers presents the same image to the applications as a single database server, there are internal differences in how the database services tier is implemented.
<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-05-29 : 06:54:10
|
Thanx guys...  |
|
|
|
|
Filegroups and Linked servers