slide1
Download
Skip this Video
Download Presentation
virtual techdays

Loading in 2 Seconds...

play fullscreen
1 / 24

virtual techdays - PowerPoint PPT Presentation


  • 98 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' virtual techdays' - joelle


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
slide1

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 |

slide2

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…

slide3

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

slide4

INDIA │ 28-30 September2011

virtual techdays

Good Practices around Installation/Patching of SQL Server

slide5

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

slide6

INDIA │ 28-30 September2011

virtual techdays

Good Practices around Memory Settings of SQL Server

slide7

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

slide8

INDIA │ 28-30 September2011

virtual techdays

Good Practices around Security in SQL Server

slide9

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

slide10

INDIA │ 28-30 September2011

virtual techdays

Recover from a locked out situation

Demo

slide11

INDIA │ 28-30 September2011

virtual techdays

Good Practices around Database Files

slide12

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

slide13

INDIA │ 28-30 September2011

virtual techdays

Problem caused due to Many VLFs

&

Shrink is an E V I L

Demo

slide14

INDIA │ 28-30 September2011

virtual techdays

Good Practices around TempDB Files

slide15

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

slide16

INDIA │ 28-30 September2011

virtual techdays

Good Practices around Monitoring

slide17

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

slide18

INDIA │ 28-30 September2011

virtual techdays

SQL Server Management Studio Status Bar Color

&

DDL Trigger to avoid accidents

Demo

slide19

INDIA │ 28-30 September2011

virtual techdays

And … have a solid maintenance plans.

slide20

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

slide21

INDIA │ 28-30 September2011

virtual techdays

Puzzle Time!

slide22

INDIA │ 28-30 September2011

virtual techdays

Puzzle

03:22 PM

slide23

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

slide24

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

ad