Advanced SQL Schema Customization & Reporting

1. As day to day business needs become more complex so does the need for specifically tailored and timely access to your SQL data. MSGL provides a wide array of data access tools and methods but sometimes you might require customized access to integrate into your existing reporting and management tools or wish to develop new ones. This session will focus in on the SQL schema (structure and naming conventions) along with the do’s and don’ts of how you can accomplish your customized reporting needs by using specific examples. • Highlights of this session include: • Schema (structure and naming conventions) • Custom views • Data replication • Performance tuning • Feel free to ask specific questions to focus in on areas that concern your business most. Advanced SQL Schema Customization & Reporting Presented By: John Dyke

2. Overview • Planning for Élan database customization • Extending the Élan database • Using new Élan database customizations • Creating custom web services • Integrating with other applications • Implementing custom applications • Keeping everything synchronized • Data replication • Performance tuning your Élan database customizations User Group 2012 ▪ Denver

3. Planning for MSGL database customization • Identify your requirements • What must it do? • What tools will be used? • Create an implementation plan • Proper planning here will help minimize the impact of Élan schema changes • It is HIGHLY recommended that you do this in consultation with MSGL User Group 2012 ▪ Denver

4. Extending the Élan Database • All interaction with Élan databases MUST BE READ ONLY • DO NOT modify or directly link to Élan database objects or you WILL NOT be able to cleanly update your database • Views and stored procedures may be added into a separate schema • Database objects WILL CHANGE • Wrap Élan tables and views with custom views • Care should be taken to prevent performance issues User Group 2012 ▪ Denver

5. Extending the Élan Database • Establish a naming convention • All Élan views and stored procedures follow the convention • [Module Code]V_[View Name] • [Module Code]SP_[Stored Procedure Name] • The following naming convention for custom views and stored procedures is • [Company Name]V_[View Name] • [Company Name]SP_[View Name] User Group 2012 ▪ Denver

6. Using new Élan database customizations:Creating Custom Web Services • Use existing Élan tables and views • Use custom views or stored procedures • Create custom return types when returning data from Élan tables or views • Tie together information from multiple sources or transform information using other applications to get the desired result User Group 2012 ▪ Denver

7. Using new Élan database customizations:Integrating with Other Applications • Transform data from web services or SQL with BizTalk • Pull data from web services into Excel for further analysis • Update SharePoint with data from Élan and custom web services, SQL Reporting, BizTalk and Excel User Group 2012 ▪ Denver

8. Using new Élan database customizations:Implementing Custom Applications • Create in-house applications • Create applications that run on mobile devices for employees on the road • Integrate Élan and custom web services along with other applications User Group 2012 ▪ Denver

9. Keeping everything synchronized • Backup database and version changes • Create and implement a test plan • If it is automated impact of changes are known • DO NOT modify or directly link to Élan database objects User Group 2012 ▪ Denver

10. Keeping everything synchronized: Data replication • If you are hosting in the ASP environment and DO NOT have a dedicated SQL Server or have demanding reporting needs • You should have an onsite SQL Server and special arrangements should be made to replicate a copy of your data to you • In this case ALL custom changes must be added to a separate database User Group 2012 ▪ Denver

11. Performance tuning your Élan database customizations • Activity Monitor – New SQL Server 2008 Dashboard • Overview – Provides a dashboard of intuitive graphs and charts that illustrate SQL’s Processor Time, Waiting Task, Database I/O and Batch Requests/Sec User Group 2012 ▪ Denver

12. Performance tuning your Élan database customizations • Processes – Open this up and you can see all the processes that are running on the SQL Server at that moment. User Group 2012 ▪ Denver

13. Performance tuning your Élan database customizations • Resource Waits User Group 2012 ▪ Denver

14. Performance tuning your Élan database customizations • Data File I/O – I/O activity for relational database files. Shows info for both system and user databases. User Group 2012 ▪ Denver

15. Performance tuning your Élan database customizations • Recent Expensive Queries – gives you the opportunity to capture queries that are performing the worst and is negatively impacting the server. Gives you around 10 -15 worst queries. User Group 2012 ▪ Denver

16. Performance tuning your Élan database customizations • Use the SQL Server Tuning Advisor – This will give you advice on adding indexes, views etc… Use Profiler to get queries to test your performance. User Group 2012 ▪ Denver

17. Performance tuning your Élan database customizations • Database Tuning Advisor – After you get the trace file open up the Tuning Advisor. User Group 2012 ▪ Denver

18. Performance tuning your Élan database customizations • Click on the Tuning Options tab and you can choose what you want the advisor to look for indexes, views etc… User Group 2012 ▪ Denver

19. Performance tuning your Élan database customizations • Click on the Start Analysis User Group 2012 ▪ Denver

20. Performance tuning your Élan database customizations • Once the Analysis is complete there will be recommendations for you. User Group 2012 ▪ Denver

21. Performance tuning your Élan database customizations • If there are recommendations, or you know you need to add performance enhancements, to add changes directly to Élan database objects • Consider creating “static” snapshots of data which can be refreshed on demand or a scheduled basis • This will trade disk-space for performance • This is much cheaper than buying a more powerful SQL Server but must be weighed against how up-to-date the data must be User Group 2012 ▪ Denver

22. END • Advanced SQL Schema Customization & Reporting User Group 2012 ▪ Denver