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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-08 : 17:56:26
|
| Landon writes "Hello - thank you for taking the time to read my question. My company is currently experiencing a problem with load on our database server and I was hoping you could provide some insight.The current system is as follows:We have two web servers - 1 - A development machine PIII 500 256 MB RAM Windows 2000 running IIS (with all service packs and hot fixes) ColdFusion 5.0 2 IDE hard drives 2 - A production machine PIII 550 512 MB RAM Windows 2000 running IIS (with all service packs and hot fixes) ColdFusion 5.0 2 SCSI hard drivesAnd one database server - Dell PowerEdge 4400 Dual Xeon 933MHz 512 MB RAM Windows NT 4 (with all service packs and hot fixes) Microsoft SQL Server 7 (with SP3) 2 SCSI hard drivesOur company serves online client statements to mutual fund clients. Every day, we receive data uploads from about thirty different companies (usually between 9am and 11am). Our problem occurs when these data uploads are imported into our SQL Server. Our database administrator has written DTS packages to extract the data from the uploads we receive, and then transforms and imports (I'm a little fuzzy on what actually happens - this is his area) the data into the appropriate tables. The DTS package usually takes about two or three minutes to complete. There are, however, two or three uploads that take about 9 minutes to complete. The problems occur when a DTS package is running, and someone tries to query our database server for data. The SQL Server will not reply with the data, and the request will time out. Even if it's just a simple user validation to log into the site, the server does not respond with the data. This problem keeps happening until the more intense parts of the DTS package are complete.To sum it up, it appears that when the DTS package deletes or inserts records, the other requests have to wait until the DTS is finished. It looks like the tables are locked somehow. The data being imported is fairly small. The zipped file usually around 2 meg, and at most 5 or 6 meg. I believe the DTS package is fairly complicated though, as it has to do a lot of data transformation in order to get the information into our tables. Still, we're talking thousands of records, not hundreds of thousands, or millions.To deal with this problem, our team has been discussing a few options:One solution from our database admin is to set up another database server identical to the first server. One server (Server A) will receive all imports and do the DTS package, while the other server (Server B) serves client data. Then, at a scheduled time, they will switch duties, and the server that handled the imports (Server A) will do the client requests, and the other server (Server B) will begin running DTS imports. Server B will run its DTS packages on the uploaded data (they would not have to transfer data to each other, as they would each have access to the uploaded raw data and would each do their own DTS on it) and Server A will now handle client requests. This process will cycle over and over.I am interested in this idea, even though it would require some changes and tweaking of the code in order to switch between different database servers, but however, I am more interested in a load balancing solution, so that if needed, we can add more servers as our load increases.I am also a little scared of having to switch database servers on the fly like that. I worry about the servers getting out of sync, and the overhead of having to code this way.What I would like to see is a load balancing implementation. Now I've scoured the net looking for information on SQL Server load balancing, and from what I can tell, it doesn't exist. Is this true? I can't imagine that *no one* has ever come up with a technique of spreading the database load over more than one server. |
|
|
JamesH
Posting Yak Master
149 Posts |
Posted - 2002-02-11 : 12:09:16
|
| I would be against switching modes for the servers in that manner. Why not use replication? Your DBA could still have the imports and DTS packages on one server (Production System) and replicate the changes in the database(s) to your other Server(s), (Reporting System). In this scenario, your Production system wouldn't lock users out as they would always be querying your reporting side which will be synched to the production system with Replication. Just one suggestion from a average joe. Good Luck,JamesH. |
 |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-02-13 : 13:45:07
|
| I second James's Idea.-Chad |
|
|
|
|
|
|
|
|
SQL Server Load Balancing