Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
akashenk
Posting Yak Master
111 Posts |
 Posted - 2007-07-17 : 09:28:21
|
| I am developing a web app that uses a SQL Server backend and will be served by a Web Farm at some point in the future. Can anyone give me some guidance at to what the common architectual considerations are when using SQL Server with a Web Farm? Initially, would it be allright to continue to have a single SQL Server on the network, with several webservers connecting to it instead of just one, or would this be a big performance nightmare? Eventually, since the goal of using the Web Farm would be to reduce individual server load, I would think having multiple SQL server instances would be preferred. Is this even done in the real world? I'm not a dba, so I'm not quite sure of the how multiple instances would be synchronized so that they held the same data and how applications on varying web servers would know to connect to one or another (by varying the connections string, perhaps). Can anyone give me any guidance? Thanks! |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-07-17 : 11:05:11
|
| You should get someone in to advise.The performance of a single instance depends on what it has to do and how it is implemented.Make all access via SPs so that you can optimise without affecting the app.You would probably want to have some sort of load balancing - for which there are a number of solutions.As to keeping things synchronised - that needs to be designed in. Can you link a session to the client and update something central when the transaction is complete?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
akashenk
Posting Yak Master
111 Posts |
Posted - 2007-07-17 : 11:31:04
|
| Thanks for the reply, nr. All access is through SPs, though a few of them (not many) rely on dynamic SQL, so the benefit is reduced a little. The web farm web servers will be load balanced, but I'm not sure about the architecture of the Database. If there is a single instance, then everything will continue working normally, but wouldn't there be many more requests made to the DB from multiple webservers than if there was only one hosting the app and wouldn't this bog things down quite a bit?In similar scenarios, do people really use multiple instances of SQL Server? I can see if you wanted to house separate (but structured the same) data for something like East Coast, West Coast, Region, or something along those lines... but in cases where the source of the data is homogeneous, is is possible (and is there anything to be gained) by having separate instances? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-07-17 : 11:50:34
|
| >> In similar scenarios, do people really use multiple instances of SQL Server?If needed yes.If you can't partition the data then it depends on the business.Maybe the servers can request data from a central source and then process locally.Maybe just compare a latest update date and only transfer the data if it's changed.Maybe have latency for the various servers and deal with conflicts after the update.Whether multiple instances of the database are needed depends on the app. Usually there is a lot less processing done on the database server than on the app servers so a single database server can service many clients.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
akashenk
Posting Yak Master
111 Posts |
Posted - 2007-07-17 : 11:58:45
|
| Thanks! |
|
|
|
|
|
|
|
|
SQL Server used with Web Farm