how to build scalable & secure database applications noel jerke & erin welker scalability...
TRANSCRIPT
![Page 1: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/1.jpg)
![Page 2: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/2.jpg)
How to Build Scalable & How to Build Scalable & Secure Database Secure Database
ApplicationsApplications
Noel Jerke Noel Jerke &&
Erin WelkerErin Welker
Scalability ExpertsScalability Experts
![Page 3: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/3.jpg)
Part 1 – AgendaPart 1 – Agenda
Scaling SQL Server Application Scaling SQL Server Application DatabasesDatabases End-To-End ScalabilityEnd-To-End Scalability Application Data ManagementApplication Data Management .Net Tips and Tricks.Net Tips and Tricks
![Page 4: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/4.jpg)
End-To-End ScalabilityEnd-To-End Scalability
Business Requirements
![Page 5: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/5.jpg)
Scalability starts with the analysis of the Scalability starts with the analysis of the business requirementsbusiness requirements Fast hardware, software, and tuning are Fast hardware, software, and tuning are
only a small part of the scalability only a small part of the scalability equationequation
The base of the pyramid is design, which The base of the pyramid is design, which has the greatest influence on scalabilityhas the greatest influence on scalability
Technical design is based on meeting Technical design is based on meeting business requirementsbusiness requirements
Business Requirements
![Page 6: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/6.jpg)
A critical juncture in developing scalable A critical juncture in developing scalable
systems is aligning business requirements systems is aligning business requirements and the architectural directionand the architectural direction BudgetsBudgets Critical nature of the system to the core Critical nature of the system to the core
businessbusiness Functional requirementsFunctional requirements Time frameTime frame RisksRisks
Acceptable Acceptable UnacceptableUnacceptable
![Page 7: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/7.jpg)
Other Issues to ConsiderOther Issues to Consider
ComplexityComplexity Is the application well understood?Is the application well understood? Can it be reasonably controlled to scale Can it be reasonably controlled to scale
properly?properly?
ManageabilityManageability Is the application manageable?Is the application manageable? Do you have the right resources that are Do you have the right resources that are
capable of scaling the system?capable of scaling the system?
![Page 8: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/8.jpg)
Application Data Application Data ManagementManagement
Key techniques to reduce the impact of Key techniques to reduce the impact of the application on the databasethe application on the database Data cachingData caching Pre-processing dataPre-processing data Load-balanced asynchronous processingLoad-balanced asynchronous processing Mirroring dataMirroring data
![Page 9: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/9.jpg)
Data CachingData Caching
Commonly utilized dataCommonly utilized data Data that is infrequently updatedData that is infrequently updated Reasonable in size to cache in the Reasonable in size to cache in the
middle or client tiermiddle or client tier
![Page 10: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/10.jpg)
Data
Application Server
Cache
![Page 11: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/11.jpg)
Pre-processing DataPre-processing Data
Real-Accurate versus Real-TimeReal-Accurate versus Real-Time Focus on having data calculations Focus on having data calculations
done ahead of time to meet accuracy done ahead of time to meet accuracy requirements requirements
Reduce the real time calculation Reduce the real time calculation requirements to a sub-set of data that requirements to a sub-set of data that has not been pre-processedhas not been pre-processed
![Page 12: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/12.jpg)
Data
Application Server
Cache
- Processing Engine
![Page 13: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/13.jpg)
Load BalancedLoad BalancedAsynchronous ProcessingAsynchronous Processing
When real time analysis of large When real time analysis of large volumes of data is required, move the volumes of data is required, move the calculations into a middle-tiercalculations into a middle-tier
Allow several servers to run the middle Allow several servers to run the middle tier objects and federate the data to be tier objects and federate the data to be processedprocessed
![Page 14: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/14.jpg)
Data
Application ServerApplication Server
Cache Cache
- Processing Engine- Calculations
- Processing Engine- Calculations
![Page 15: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/15.jpg)
Mirrored DataMirrored Data
Use techniques for mirroring data Use techniques for mirroring data between two SQL Server to separate between two SQL Server to separate analysis transactions from OLTP analysis transactions from OLTP transactionstransactions
Techniques can include using Techniques can include using replication and double commit of replication and double commit of transactionstransactions
![Page 16: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/16.jpg)
Application ServerApplication Server
Cache Cache
- Processing Engine- Calculation
- Processing Engine- Calculations
ReportingData
CoreData
![Page 17: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/17.jpg)
.Net Tips and Tricks.Net Tips and Tricks
Server ControlsServer Controls Server controls add overhead to the Server controls add overhead to the
serverserver Only use when neededOnly use when needed Consider using alternative direct coding Consider using alternative direct coding
and not using a control – this may take and not using a control – this may take more programming time but might reduce more programming time but might reduce overhead significantlyoverhead significantly
![Page 18: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/18.jpg)
.Net Tips and Tricks.Net Tips and Tricks
Cache Data and PagesCache Data and Pages Plan caching into your application Plan caching into your application
architecturesarchitectures Caching will significantly improve Caching will significantly improve
performanceperformance
Minimize Number of AssembliesMinimize Number of Assemblies Assembly loading can be costlyAssembly loading can be costly Try to place logic of small assemblies in Try to place logic of small assemblies in
another assemblyanother assembly
![Page 19: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/19.jpg)
.Net Tips and Tricks.Net Tips and Tricks
Use Stored ProceduresUse Stored Procedures Preferred data access methodPreferred data access method Compiled in the databaseCompiled in the database Optimize for ADO.netOptimize for ADO.net
Use SQL Data Reader for ReadsUse SQL Data Reader for Reads Always use for forward read-only data Always use for forward read-only data
accessaccess Note the connection remains open for the Note the connection remains open for the
data reader, close as soon as possibledata reader, close as soon as possible Use type accessors (GetInt32, GetString, Use type accessors (GetInt32, GetString,
etc.) to reduce type conversionetc.) to reduce type conversion
![Page 20: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/20.jpg)
.Net Tips and Tricks.Net Tips and Tricks
When to Utilize DataSetsWhen to Utilize DataSets Powerful ability to relationally work with data Powerful ability to relationally work with data
outside of the databaseoutside of the database Can add significant overhead to the serverCan add significant overhead to the server Beware of synchronization issues with data Beware of synchronization issues with data
updated on the server having been also changed updated on the server having been also changed in the databasein the database
Very useful for complex data manipulation that Very useful for complex data manipulation that can be handled on the servercan be handled on the server
Useful for working with non-database relational Useful for working with non-database relational and XML dataand XML data
![Page 21: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/21.jpg)
Part 2 - AgendaPart 2 - Agenda
Indexing StrategiesIndexing Strategies Minimizing Table SizesMinimizing Table Sizes Efficient and Reusable Query Plans Efficient and Reusable Query Plans Effective LockingEffective Locking Appropriate Database MaintenanceAppropriate Database Maintenance Minimal Use of CursorsMinimal Use of Cursors Knowledge of Available ToolsKnowledge of Available Tools Securing SQL Server Application DataSecuring SQL Server Application Data
![Page 22: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/22.jpg)
Indexing Strategies – Indexing Strategies – Heap TableHeap Table
![Page 23: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/23.jpg)
Indexing Strategies – Indexing Strategies – Clustered IndexClustered Index
![Page 24: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/24.jpg)
Indexing Strategies – Indexing Strategies – Non-Clustered IndexNon-Clustered Index
![Page 25: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/25.jpg)
Effective Table IndexingEffective Table Indexing
Create an effective clustered index, such as:Create an effective clustered index, such as: Most frequently used search criteriaMost frequently used search criteria Primary KeyPrimary Key Frequently used rangeFrequently used range
Keep index keys smallKeep index keys small Only index selective columnsOnly index selective columns
Make sure the left-most column is selectiveMake sure the left-most column is selective
Use Indexed Views, as appropriateUse Indexed Views, as appropriate Verify results and monitor over timeVerify results and monitor over time
![Page 26: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/26.jpg)
Minimizing Table SizesMinimizing Table Sizes
Archive data, when ableArchive data, when able Consider partitioned views and create Consider partitioned views and create
partitionspartitions
Current DataHistoryHistoryHistory
Indexed View with Check Constraint
![Page 27: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/27.jpg)
Efficient and Reusable Efficient and Reusable Query PlansQuery Plans
Dynamic queriesDynamic queries Auto-parameterization:Auto-parameterization:
SELECT * FROM Orders WHERE Order_ID = 1SELECT * FROM Orders WHERE Order_ID = 1
sp_prepare/sp_executesp_prepare/sp_execute sp_executesqlsp_executesql Stored ProceduresStored Procedures
![Page 28: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/28.jpg)
Effective LockingEffective Locking
Keep transactions shortKeep transactions short Apply proper indexesApply proper indexes Retain SQL Server’s default behaviorRetain SQL Server’s default behavior Monitor for locking issues:Monitor for locking issues:
sp_who / sp_locksp_who / sp_lock AlertsAlerts System Performance MonitorSystem Performance Monitor SQL ProfilerSQL Profiler SQLDIAG.exeSQLDIAG.exe
![Page 29: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/29.jpg)
Appropriate Database Appropriate Database MaintenanceMaintenance
Mix Full, Differential and Transaction Mix Full, Differential and Transaction log backups effectivelylog backups effectively
Only defragment fragmented indexesOnly defragment fragmented indexes Use DBCC INDEXDEFRAG if batch Use DBCC INDEXDEFRAG if batch
maintenance window is an issuemaintenance window is an issue Starter script in SQL Server Books OnlineStarter script in SQL Server Books Online
Consider manual Shrink DB and Consider manual Shrink DB and Update StatisticsUpdate Statistics
![Page 30: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/30.jpg)
Minimal Use of CursorsMinimal Use of Cursors
Use set-based instead of row-based Use set-based instead of row-based operationsoperations
Row-based can be unknowingly Row-based can be unknowingly implemented by:implemented by: CursorsCursors DTS LookupDTS Lookup Functions to perform lookupsFunctions to perform lookups
![Page 31: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/31.jpg)
Knowledge of Available Knowledge of Available ToolsTools
SQL Profiler!!SQL Profiler!! Many events and data columns Many events and data columns Can be saved to SQL table for query Can be saved to SQL table for query
analysisanalysis
System Performance MonitorSystem Performance Monitor SQL Query AnalyzerSQL Query Analyzer
Graphical or textual showplanGraphical or textual showplan SET STATISTICS IO ONSET STATISTICS IO ON SET STATISTICS TIME ONSET STATISTICS TIME ON
![Page 32: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/32.jpg)
Securing SQL Server Securing SQL Server Application DataApplication Data
Ensure up-to-date service packs and Ensure up-to-date service packs and security patches are installedsecurity patches are installed
Implement a layered security approachImplement a layered security approach Use Windows AuthenticationUse Windows Authentication
If this cannot be used, be If this cannot be used, be veryvery careful careful about how password information is storedabout how password information is stored
Use Application or User rolesUse Application or User roles
![Page 33: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/33.jpg)
Securing SQL Server Securing SQL Server Application DataApplication Data
Implement security at the object levelImplement security at the object level Use views and stored procedures to Use views and stored procedures to
provide a layer of abstraction from provide a layer of abstraction from tablestables
Encrypt sensitive data such as credit Encrypt sensitive data such as credit card numberscard numbers
Beware of opportunities for SQL Beware of opportunities for SQL InjectionInjection
![Page 34: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/34.jpg)
Call To ActionCall To Action
Sign up to meet with the Sign up to meet with the Scalability Experts/Microsoft Scalability Experts/Microsoft Technical team.Technical team.
For more information, please email For more information, please email [email protected]
You can download all presentations atYou can download all presentations atwww.microsoft.com/usa/southcentral/
![Page 35: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/35.jpg)
Scalability Experts (972) 478-4333Scalability Experts (972) 478-4333www. scalabilityexperts.comwww. scalabilityexperts.com
Michael Handshy (sales)Michael Handshy (sales)[email protected]@scalabilityexperts.com
(972) 478-4333 Office (972) 478-4333 Office (214) 629-6729 Mobile(214) 629-6729 Mobile
Noel JerkeNoel Jerke [email protected]@scalabilityexperts.com(972) 745-3735 Office(972) 745-3735 Office (214) 215-0787 Mobile(214) 215-0787 Mobile
Erin Welker Erin Welker [email protected]@scalabilityexperts.com(972) 478-4333 Office(972) 478-4333 Office (817) 239-4425 Mobile(817) 239-4425 Mobile
Contact InformationContact Information
![Page 36: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/36.jpg)
Questions ?Questions ?
![Page 37: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/37.jpg)
SQL Server Summit SQL Server Summit Brought To You By:Brought To You By:
![Page 38: How to Build Scalable & Secure Database Applications Noel Jerke & Erin Welker Scalability Experts](https://reader035.vdocuments.us/reader035/viewer/2022062722/56649f335503460f94c4f745/html5/thumbnails/38.jpg)
© 2004 Microsoft Corporation. All rights reserved.© 2004 Microsoft Corporation. All rights reserved.This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.