Application architecture

Hi all,
I am developing a production optimization application. The application is a
distributed application with several physical machines doing things like
measurements, calculations and control and they all communicate through
TCP/IP using .Net remoting.
A requirement for the application is that it should be able to process 100
items / minute.
I am interested in using SQL server to store data about my produced items
and also about the system itself. For instance storing performance counters
from the different machines.
The logging to the database is not a critical feature of the system and it's
important that the writing to the database does not harm the performance of
other more critical functions in the system.
To handle the communication with the database I have a few altenative
architectures that I would like you to comment on (or please feel free to
suggest any other solutions)
1. Use a single SQL server that all machines on the network communicate
directly to. Should I use asynchrounous write to limit the impact on the
writing system?
2. Use separate instances on each machine (SQL Express) and then copy or
move the data when the system is idle.
3. Use a middle layer that resides on a separate machine that handles all
the communication to the SQL server. Essentially dump all messages on this
resource and write them from here when there is an opportunity. All problems
only affect this machine.
I would Like to use alternative 1 but I'm concerned about the availiabilty.
I have had problems before with another application that writes to an SQL
server over an office network where the SQL server does not respond (or
responds slowly) and cause performance degrading in my application.
Please give me some advice on this matter
Best regards
Henrik
SQL Server will cache any new data or data changes and handle the disk
writing on its own time.
If you are inserting new data, then the operation will be very fast.
(Sometimes, when updating existing data, slowness can be encountered because
other users may be locking and/or blocking the data. I have production
systems that are capable of accepting new data inserts at a rate of
thousands per second -so your requirement of hundreds per minute will be no
issue for SQL Server.
The main issue affecting the speed you desire is that the location of the
Transaction Log file (*.ldf) should be on a dedicated drive or array. The
log file writes all changes, serialized, and any other activity on the drive
will move the drive heads, and then they have to return to the last location
to write the next piece of data. If there are no other activities on the
drive, then the drive heads are 'always' in the right location, and the
speed is optimal.
Choice #1, and do not bother trying async writes.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Henrik" <henrik_@.community.nospam> wrote in message
news:7188099B-03F9-4A01-9C9D-5BD41CD1234E@.microsoft.com...
> Hi all,
> I am developing a production optimization application. The application is
> a
> distributed application with several physical machines doing things like
> measurements, calculations and control and they all communicate through
> TCP/IP using .Net remoting.
> A requirement for the application is that it should be able to process 100
> items / minute.
> I am interested in using SQL server to store data about my produced items
> and also about the system itself. For instance storing performance
> counters
> from the different machines.
> The logging to the database is not a critical feature of the system and
> it's
> important that the writing to the database does not harm the performance
> of
> other more critical functions in the system.
> To handle the communication with the database I have a few altenative
> architectures that I would like you to comment on (or please feel free to
> suggest any other solutions)
> 1. Use a single SQL server that all machines on the network communicate
> directly to. Should I use asynchrounous write to limit the impact on the
> writing system?
> 2. Use separate instances on each machine (SQL Express) and then copy or
> move the data when the system is idle.
> 3. Use a middle layer that resides on a separate machine that handles all
> the communication to the SQL server. Essentially dump all messages on this
> resource and write them from here when there is an opportunity. All
> problems
> only affect this machine.
> I would Like to use alternative 1 but I'm concerned about the
> availiabilty.
> I have had problems before with another application that writes to an SQL
> server over an office network where the SQL server does not respond (or
> responds slowly) and cause performance degrading in my application.
> Please give me some advice on this matter
> Best regards
> Henrik
>
>
|||Hello Henrik,
I'd also vote for alternative 1 since it is most simple and efficient. As
Arnie said, SQL itself cache data writing. If there is no blocking issues
on the server, its preformance shall be very good. You may want to adjust
your application/Stored procedures/queries carefully to avoid/alleviate
blocking issues so that SQL Server can keep quick response time, especailly
when multiple clients are inserting/updating same tables at same time to
SQL Server.
Also, performance tuning of SQL Server itself is important for better
response time.
224453 INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking
Problems
<http://support.microsoft.com/?id=224453>
271509 INF: How to Monitor SQL Server 2000 Blocking
<http://support.microsoft.com/?id=271509>
Q224587 INF: Troubleshooting Application Performance with SQL Server
<http://support.microsoft.com/support/kb/articles/q224/5/87.asp>
TechNet Support WebCast:Performance troubleshooting and analysis in
Microsoft SQL Server 2000
http://support.microsoft.com/?id=838622
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#EYBAG
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.