1 / 24

virtual techdays

INDIA │ 28-30 September 2011. virtual techdays. Tips for Successful SQL Server Deployment in Enterprise Environment. Balmukund Lakhani │ Technical Lead – SQL Support, Microsoft blakhani@microsoft.com | http://SQLServer-Help.com |. INDIA │ 28-30 September 2011. virtual techdays.

jonesmichael
Download Presentation

virtual techdays

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. INDIA │ 28-30 September2011 virtual techdays Tips for Successful SQL Server Deployment in Enterprise Environment Balmukund Lakhani │ Technical Lead – SQL Support, Microsoft blakhani@microsoft.com| http://SQLServer-Help.com |

  2. INDIA │ 28-30 September2011 virtual techdays • Working with SQL Technology since 2001 (almost 10 years) • Currently working as Technical Lead with Microsoft SQL Support Team • Premier Field Engineer @ Microsoft • ERP Onsite Support @ RamcoSystems • DBA @ RamcoSystems • Developer @ RamcoSystems • Team Blog – http://blogs.msdn.com/SQLServerFAQ • Personal Blog – http://SQLServer-Help.com • Active Participant in MSDN SQL Forums. A B O U T M E…

  3. INDIA │ 28-30 September2011 virtual techdays • Best Practices or Good Practices or Better Practices? • Installation • Memory • Security • Database Files • TempDB Files Placement • Backup • Monitoring SQL Server S E S S I O N A G E N D A

  4. INDIA │ 28-30 September2011 virtual techdays Good Practices around Installation/Patching of SQL Server

  5. INDIA │ 28-30 September2011 virtual techdays • Use domain account (not domain admin) as Service Account. • Have separate account for each server. • If you install SQL 2005, please remember to install non-cluster aware component on both nodes. • SQL 2008 Onwards, use Slip Streamed media • Avoid known issue of setup itself. • Overall reduced time to install SQL Server. • SQL 2008 onwards, first apply patch on passive node in cluster (rolling upgrade), failoverand then apply on active node. • Provide “Perform Volume Maintenance Tasks” permission (using SecPol.msc) to service account. • Use Configuration File / Command line install for faster deployment. • Configure antivirus software to skip LDF/MDF/NDF/Backup files (KB 309422) • Make sure ReadMe of release is followed to understand the known issues in advance. • Evaluation Edition Expired? SQL Server Installation/Patching Tricks

  6. INDIA │ 28-30 September2011 virtual techdays Good Practices around Memory Settings of SQL Server

  7. INDIA │ 28-30 September2011 virtual techdays • If you are using 32 bit SQL Server and you have more than 2 GB of RAM, please configure AWEif you want more memory to be used. • To use AWE, SQL Service account needs “Lock Pages in Memory” privilege. • Limit Max Server Memory of SQL Server. • Keep an eye on DBCC MemoryStatusoutput. • Don’t use Task Manager to check how much memory SQL is using. Use PerfMon. SQL Server Memory Configuration Tricks

  8. INDIA │ 28-30 September2011 virtual techdays Good Practices around Security in SQL Server

  9. INDIA │ 28-30 September2011 virtual techdays • Change Production Server Port to something other than 1433. • Don’t add SQL Server Service Account as SysAdmin. • Make sure permission is given to those who need it. • Rename sa to some other name. • Keep strong passwords for SQL Logins. • Don’t add unwanted users to SQL Server. • Remove Builtin\Administrators • Disable xp_cmdshell unless it is absolutely needed. • Use Windows Authentication where ever possible. SQL Server Security Tips

  10. INDIA │ 28-30 September2011 virtual techdays Recover from a locked out situation Demo

  11. INDIA │ 28-30 September2011 virtual techdays Good Practices around Database Files

  12. INDIA │ 28-30 September2011 virtual techdays • Multiple Transaction Logs file won’t help in performance. • Don’t Shrink the data files to save space unless it’s the last thing in the world! • Don’t let the file grow automatically. Plan in advance. • Don’t have AutoShrink of database turned ON – worst than performing manual shrink. • Don’t have AutoClose ON for the database. • Avoid having percentage growth. Fix size is better. • Frequent Autogrowth is not healthy. • Don’t Scan the files by Antivirus. SQL Server Database Files Tips

  13. INDIA │ 28-30 September2011 virtual techdays Problem caused due to Many VLFs & Shrink is an E V I L Demo

  14. INDIA │ 28-30 September2011 virtual techdays Good Practices around TempDB Files

  15. INDIA │ 28-30 September2011 virtual techdays • How may tempdb database we have?  • There are multiple suggestions around how many tempdb data files. • If you see any contention on PAGELATCH then increase the tempdb files. • Track tempdb usage and make sure that drive has enough space to allow growth. • If there are multiple temdpb data files, make sure the size is same. SQL Server TempDBManagament

  16. INDIA │ 28-30 September2011 virtual techdays Good Practices around Monitoring

  17. INDIA │ 28-30 September2011 virtual techdays • Automate as much as you can. • Alert for long Blocking • Alert for Data File Free Space. • Alert for High CPU Usage. • Be proactive • Check ErrorLog regularly. • Check for Dumps. • Use DDL Triggers on production to avoid any mishap of dropping objects. • Install Performance DashBoard on Servers to look in graphical DMVs output. • Use SSMS colour feature to make sure you are working on proper server. SQL Server Monitoring

  18. INDIA │ 28-30 September2011 virtual techdays SQL Server Management Studio Status Bar Color & DDL Trigger to avoid accidents Demo

  19. INDIA │ 28-30 September2011 virtual techdays And … have a solid maintenance plans.

  20. INDIA │ 28-30 September2011 virtual techdays • Don’t blindly update stats for all table and rebuild all indexes. • Check with Business Unit about tables and their data change frequency. • Check the RTO and RPO from business to decide backup frequency. • If recovery model is full, make sure log backups are taken.. Please! Please!! Please!!! • Don’t ever have shrink in the Maintenance Plan. • Myth – If backup is successful, backup file is restorable. • Check database integrity (DBCC CheckDB) on regular basis. • Have a sound disaster recovery plan! • And make sure you test the plan before you actually do it. SQL Server Maintenance Plan tips

  21. INDIA │ 28-30 September2011 virtual techdays Puzzle Time!

  22. INDIA │ 28-30 September2011 virtual techdays Puzzle 03:22 PM

  23. INDIA │ 28-30 September2011 virtual techdays • Ancient Trade of Performance Tuning - Index, Beyond Index and No Index • Pinal Dave • 11:45am - 12:45pm • High Availability - A Story from Past to Future • Balmukund Lakhani • 1:45pm - 2:45pm • Monitoring Performance in Cloud for SQL Azure Applications • Vinod Kumar • 3:00pm - 4:00pm • Managing SQL Server Made Easy - Features and Concepts • Amit Banerjee • 4:15pm - 5:15pm RELATED CONTENT in VTD Sep 29th 2011 – DBA Track

  24. THANKS│28-30 September2011 virtual techdays Please do give your Feedback, Complete evaluation at the end of this session. You could also write to be at : blakhani@microsoft.com│ http://twitter.com/blakhani

More Related