“Notes from the Field”

1. “Notes from the Field” Lessons learned developing a distributed .NET application Presenter Darryl Pollock Squirrel Consulting Pty Limited Sydney SQL SIG 26 October 2004

2. Darryl PollockSquirrel Consulting Pty Limited • Developer for 18 years • DBA for 7 years • Consultant for 5 years including : • Development/ SQL and .NET Architecture • Clustering • Performance Tuning • Large DB implementations (up to 1 TB) darryl@squirrelconsulting.com.au

3. Agenda • Background of the application • Architecture • Data Modelling • Concurrency • Reporting Services Integration • Pearls of Wisdom • Demo • (there is a QA session – but would prefer Questions throughout!)

4. SME’s seem to have core applications based around 1 or 2 servers • These applications are business-critical and often need monitoring • BUT • SME’s cannot afford nor wish to manage large infrastructure monitoring systems Background

5. clients began asking for a monitoring system for their business-critical systems • The industry was researched, products tested, evaluated for a 3 month period • No single product was found that met the following criteria: Background

6. Lightweight – would not create performance issues • Platform independent from NT upwards • Secure, yet firewall-independent • Was not overly complex to use • Provided hierarchical security (portal) • Provided long term (trend) reporting • Could work across multiple organisations • Low Cost! Criteria

7. Agent (RSM) Configuration (XML File) SOAP/XML(Secure) (Simple/Forms Based Configuration) Lightweight/Platform Independent Performance/Events Licensing SOAP/XML SOAP/XML Agent Monitoring SOAP/XML Reporting Pre-configured Portal Technology (Simple)

8. The Challenge! • 4 Languages • Multiple Technologies/Multiple Platforms • Multiple applications competing for the same resource: • One SQL Server!

9. Building the Data Model Historical Data Initially just _Total

10. Building the Data Model - Testing Initially this table would grow by 100-300 rows every interval (1-3 minutes) Performance of course was spectacular even with 15-20 agents running Aggregation of the data was performed at insertion by way of a trigger (avg,max,min)

11. Building the Data Model - Tuning Reaching 1 million rows occurred with one agent running in a week Once the table started exceeding 1 million rows, we were in trouble! The database was straining to view the data even with reasonable indexing Performance of course was abysmal even with 2 agents running 300 Rows became 1000 rows by allowing ALL instances! Aggregation of the data was changed to occur hourly – and there was no archiving strategy in place – we’d encountered our first major issue. Why? Because our archiving strategy was not part of the design!! (Big mistake)

12. Building the Data Model – Re-factoring Trigger This became the “snapshot” of the current state Deletion and Aggregation became hourly – only 1 hour of data was maintained Deletion and Aggregation nightly We not only adjusted the model, but we built the first foundation of the report architecture In the final product, we removed aggregation from the DB completely and moved to the agent – it took 7 weeks to get the agent to run efficiently – but worth it!

13. The Pyramid of Error

14. Concurrency – the cowards way out? • We weren’t building a large enterprise system, but a system that an SME would be running – one server to run probably everything…Which meant : • Most likely scenario was portal retrieving transactional data from a highly transactional system. (Does this sound like a disaster in the making?) • Transactional data that was being sent to the presentation layer at the portal experienced MAJOR concurrency issues. The only solution that would work for us was : • SHOCK! HORROR!!! Select with (nolock) • Our alerts were real-time, so there was no perceived delay – it was a choice that worked for this application. • However this did not solve all our concurrency issues so….

15. Concurrency – letting SQL do its thing… • Connection re-use – as soon as we adjusted the Web Service to have each WebMethod use a single connection, our performance increased by about 20% - this can be achieved either through code or connection pooling. • This resulted in locks being held for shorter periods of time • We replaced DataAdaptors with SQLCommand.ExecuteScalar wherever possible, thereby reducing the amount of data we were retrieving • We adjusted all highly transactional, tables to use Row-Level locking, but left the others alone. • We stopped specifying any type of lock to hold in our procs. • We then sat back and tested • SQL held locks, but released them, fast! – This was the result we wanted.

16. Large Data Sets and what to do with them • Retrieving a large number of events from the database became a performance bottleneck – instead of just relying on tuning – we not only tuned the indexes on the event table but used .NET’s built in DataView and DataFilter objects. • The proc retrieved the entire dataset from a well-indexed table, and instead of going back to the database and retrieving a filtered new rowset – we passed the original DataSet into a DataView and applied a filter – with excellent results • Eeek!!!!!!! Dot net Code!!! (This little snippet increased performance by 100%!) • If Me.DataFilter <> "" Then Dim oDV As DataView = Me.RSMCounters.DefaultView oDV.RowFilter = Me.DataFilter Me.datagridRSMCounters.DataSource = oDV Me.datagridRSMCounters.DataBind() Else Me.datagridRSMCounters.DataBind() End If

17. Reporting Services Integration Select Report Retrieve List of Reports Get Server Name Build URL Based on Parameters of where we were in the portal Redirect Browser to URL

18. or (How not to be bullied by developers!) • In most cases, you want your stored procedure to represent your logical model …however • Sometimes it is best to let the application do the logic for retrieving the data structure vs. retrieving it all in one “hit” in a complicated proc • why? because it is quicker! • For example: Collaboration

19. Our database – the philosophy • Golden Rules don’t put app logic in db don’t put db logic in app • Use your db as a data storage device, nothing more • we only use a few cursors just for initialisation – and they are run once. If you think you need a cursor to run more than once – forget Golden Rule #1! • use .NET to loop through a data set and evaluate each row..its quick and far more efficient

20. DRI • If you choose to have your application manage the referential integrity of your application, then the integrity is as strong as your worst developer. • The database is never wrong! • We let SQL generate the error and then handle it at the application layer. • The application should be ignorant of the physical database, and only be concerned with how to read and write objects. • Use the Cascading deletes and updates – it can save hours of development time.

21. The Future • 2 Way SOAP communication – creating SQL ‘queues’ of commands at the agent • Wireless job summary and control agents • Yukon – execute all functionality via CLR • Extending the architecture beyond monitoring • Retail sales summaries • GPS and SOAP

22. Contact • More Info - www.squirrelconsulting.com.au/LearnMore.htm • Demo – www.remotesquirrel.com • Company www.squirrelconsulting.com.au • darryl@squirrelconsulting.com.au