INDIA
This presentation is the property of its rightful owner.
Sponsored Links
1 / 24

virtual techdays PowerPoint PPT Presentation


  • 66 Views
  • Uploaded on
  • Presentation posted in: General

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

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Virtual techdays

INDIA │ 28-30 September2011

virtual techdays

Tips for Successful SQL Server Deployment in Enterprise Environment

Balmukund Lakhani │ Technical Lead – SQL Support, Microsoft

[email protected]| http://SQLServer-Help.com |


Virtual techdays

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…


Virtual techdays

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


Virtual techdays

INDIA │ 28-30 September2011

virtual techdays

Good Practices around Installation/Patching of SQL Server


Virtual techdays

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


    Virtual techdays

    INDIA │ 28-30 September2011

    virtual techdays

    Good Practices around Memory Settings of SQL Server


    Virtual techdays

    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


    Virtual techdays

    INDIA │ 28-30 September2011

    virtual techdays

    Good Practices around Security in SQL Server


    Virtual techdays

    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


    Virtual techdays

    INDIA │ 28-30 September2011

    virtual techdays

    Recover from a locked out situation

    Demo


    Virtual techdays

    INDIA │ 28-30 September2011

    virtual techdays

    Good Practices around Database Files


    Virtual techdays

    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


    Virtual techdays

    INDIA │ 28-30 September2011

    virtual techdays

    Problem caused due to Many VLFs

    &

    Shrink is an E V I L

    Demo


    Virtual techdays

    INDIA │ 28-30 September2011

    virtual techdays

    Good Practices around TempDB Files


    Virtual techdays

    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


    Virtual techdays

    INDIA │ 28-30 September2011

    virtual techdays

    Good Practices around Monitoring


    Virtual techdays

    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


    Virtual techdays

    INDIA │ 28-30 September2011

    virtual techdays

    SQL Server Management Studio Status Bar Color

    &

    DDL Trigger to avoid accidents

    Demo


    Virtual techdays

    INDIA │ 28-30 September2011

    virtual techdays

    And … have a solid maintenance plans.


    Virtual techdays

    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


    Virtual techdays

    INDIA │ 28-30 September2011

    virtual techdays

    Puzzle Time!


    Virtual techdays

    INDIA │ 28-30 September2011

    virtual techdays

    Puzzle

    03:22 PM


    Virtual techdays

    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


    Virtual techdays

    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 :

    [email protected] http://twitter.com/blakhani


  • Login