Transcript Enterprise Presentation

New Instance…
Now What?
•
•
•
Presented by: James Donahoe
Senior Solutions Engineer – TeleTracking Technologies
MCSA: SQL Server 2012
About Me!
• PASS Virtual Chapter Leader – “In-Memory”
• SQL Environments of SQL 2000 to current
• MCSA in SQL Server 2012 , pursuing MCSE
• Retro Video Game Collector (Over 900 games!)
• Eagle Scout
• United States Marine Corps Veteran
• An owner of Retro Perler Art
About TeleTracking
24 YEARS
350K
+
As Industry
Leader in
Patient Flow
Retention
95% Rate
870
+
Clients
Acute Care Beds
62
of the
100
GREAT
HOSPITAL
st
1
By KLAS as Industry
Leader in Patient Flow
80%
Of The Best Hospitals
Agenda
• Configuration
• The Maintenance Hexagon
• What do I backup?
• DR Testing
• Best Practice Security Recommendations
• Resources
Configuration
• Are your MDF, and LDF files on the same drive?
• Are your backups residing on the same drive as your data files?
• What is MAXDOP set to?
• What is your cost threshold for parallelism?
• How many TempDB files do you have?
• What is your instance memory capped at?
Basic Maintenance
The Maintenance Hexagon
Database Backups
Recovery
Backup
Performance
Query
Performance
Faster Root
Cause Analysis
Query
Performance
Faster Root
Cause Analysis
Cycle Agent Error Log
Maintenance Examples
BACKUPS
•
Full Database Backup
•
•
•
•
Creates a full backup of all user databases.
Scheduled for every Sunday at 2:00AM
Backups retained for 14 days
located in the default backup directory
• Transaction Log Backup
• Creates a transaction log backup of all user databases in Full Recovery
• Scheduled for every Monday through Saturday at 2:00AM
• Backups are retained for 14 days
• located in the default backup directory
Maintenance Continued
MISC Maintenance
• CycleErrorLog
• Cycles the error log on SQL Server
• Is scheduled for every Sunday, Wednesday at 5:00 am.
• Integrity
•
•
Checks database integrity including indexes
Is scheduled for every Sunday at 3:00AM
• Optimization
•
•
•
•
Rebuild all indexes
Changes free space to default setting
Updates all statistics
Is scheduled for every Sunday at 4:00AM
What Do I Do With
My Backups?
What do I backup?
ALL databases – User and System!
•
•
All User Databases
System Databases
Transaction Logs
•
Watch SIZE, Backups will help keep in check
Select * from sys.configurations
•
Create a job to run that query
•
Save this into a table
•
Back it up!
•
Comes in handy if you ever have to reinstall!
TEST Your Backups!
Backing up is the first step, but they won’t do you any good if they are
corrupted. TEST your backups at LEAST once a quarter. If you haven’t
experienced a corrupted database, give it time, it will happen!
Disaster Recovery Testing
DR Testing
When was the last time you did a fire drill?
•
If it is longer than 6 months, you are overdue!
How easy is it to access the documentation?
•
\\fileshare\where\the\folder\structure\is\something\like\this
^ REALLY!? That just slows everything down.
Are the instructions clear?
• Would I be able to come in today, and be able to follow
your instructions to have your system back up
and running within your timeframe?
What is your average restore time?
(Those backup tests have come back to get yinz! – Pittsburgheze)
Security Recommendations
Best Practice Security Recommendations
• Non-Default logins provisioned to sysadmin role
• Non-default users are provisioned to db_owner role
• Who needs access?
• Don’t use SA accounts for SQL Server Agent Jobs
• Are you using the basics? “Password Complexity”, “Password Expiration”?
Resources
Resources
Local Pass Chapter
Many of us see each other monthly at a user group. Ask your question there, get the
speakers contact information and don’t be afraid to reach out to them. PASS is a
great organization that encourages learning through your peers.
BrentOzar.com
In one free, convenient zip file, you get their scripts including sp_Blitz®,
sp_AskBrent®, and sp_BlitzIndex®, plus their e-books like the SQL Server Setup
Checklist and AlwaysOn Availability Groups Setup Checklist.
Twitter
#SQLHELP, #SQLFAMILY Reaching out to any of those hashtags will get you a response
relatively quickly if you are working on something
SQLskills.com/blogs/glenn/
Free SQL Server Diagnostic Information queries—Updated MONTHLY
MinionWare
Several free pieces of wonderful software – Reindex, backup, etc. Automates those tasks for you so you don’t
have to worry!
SQLSentry
I cannot stress how great PlanExplorer is! They have a free and paid version, both get the job done, but I
HIGHLY recommend that paid version.
Virtual Pass Chapters
These chapters hold virtual meetings every month for their subject(In-Memory, Performance, Administration,
etc). Register on SQLPASS.org and look into them, lots of great, knowledgeable speakers regularly present! -Shameless Plug, In-Memory VC has a great leader, just sayin. ;)
Micrsoft Virtual Academy
Free videos on all Microsoft products
Questions?
Suggestions?
Stories?
Thank You!
To all of you for attending and to ALL of
our sponsors today!
Twitter: @SQLFlipFlopsDBA
Email: [email protected]