dat340 most common dba tasks patrick conlan program manager sql server microsoft corporation
Post on 12-Jan-2016
218 Views
Preview:
TRANSCRIPT
DAT340DAT340
Most Common DBA TasksMost Common DBA Tasks
Patrick ConlanPatrick ConlanProgram ManagerProgram ManagerSQL ServerSQL ServerMicrosoft CorporationMicrosoft Corporation
Related TalksRelated Talks
DAT302 – SQL Server Distributed DAT302 – SQL Server Distributed Management with SQL Agent and MOMManagement with SQL Agent and MOM
DAT342 – Microsoft SQL Server DAT342 – Microsoft SQL Server Performance & TuningPerformance & Tuning
DAT380/DAT382 – Locking, Blocking DAT380/DAT382 – Locking, Blocking and Deadlock Troubleshootingand Deadlock Troubleshooting
DAT 411 – Programming and Deploying DAT 411 – Programming and Deploying SQL Server 2000 Replication:Lessons SQL Server 2000 Replication:Lessons LearnedLearned
TOP 90 PERCENT LISTTOP 90 PERCENT LISTYou know you are a DBA when:You know you are a DBA when:
1.1. You normalize your shopping list to save spaceYou normalize your shopping list to save space2.2. You apply relational logic…to your relationsYou apply relational logic…to your relations3.3. You never need to re-index your CD collection, it’s already You never need to re-index your CD collection, it’s already
clustered by artist with secondary indexes on title, label & clustered by artist with secondary indexes on title, label & genregenre
4.4. You wish you could ROLLBACK TRANSACTION on your You wish you could ROLLBACK TRANSACTION on your partners credit card billspartners credit card bills
5.5. You wake up screaming from a dream in which your boss You wake up screaming from a dream in which your boss says “Lets build a 1TB data warehouse…using SQL says “Lets build a 1TB data warehouse…using SQL Server 6.5”Server 6.5”
6.6. You spend your lunch break inventing new DBCC You spend your lunch break inventing new DBCC commands: commands:
DBCC LOSE_DATA(‘dbname’) WITH NORECOVERYDBCC LOSE_DATA(‘dbname’) WITH NORECOVERY
7.7. You understand the need to answer any question with You understand the need to answer any question with another questionanother question
8.8. You know where your data goes at night…You know where your data goes at night…9.9. ……and you know that your developers and you know that your developers mean wellmean well!!
AgendaAgenda
The BasicsThe Basics Automation: AlertsAutomation: Alerts Automation: Routine TasksAutomation: Routine Tasks Securing Your ServerSecuring Your Server Monitoring for PerformanceMonitoring for Performance Data Loading Data Loading
demodemo BACKUPBACKUP
Demo with the UIDemo with the UI Demo with ScriptDemo with Script Maintenance PlansMaintenance Plans
Where are Errors Where are Errors Logged?Logged? Demo cycling the Demo cycling the
error log & Increasing error log & Increasing the number of logsthe number of logs
SQL Agent LogSQL Agent Log
RESTORERESTORE Demo with the UIDemo with the UI Demo with ScriptDemo with Script Log ShippingLog Shipping
AgendaAgenda
The BasicsThe Basics Automation: AlertsAutomation: Alerts Automation: Routine TasksAutomation: Routine Tasks Securing Your ServerSecuring Your Server Monitoring for PerformanceMonitoring for Performance Data Loading Data Loading
Automation is Your FriendAutomation is Your Friend
You should automate all ordinary tasksYou should automate all ordinary tasks This frees up time for you to perform This frees up time for you to perform
“fun” tasks“fun” tasks Serious performance analysisSerious performance analysis Capacity PlanningCapacity Planning ProgrammingProgramming Going home early Going home early
Configure SQL Server AgentConfigure SQL Server Agent
Configure to run as a user Configure to run as a user Grant admin privs or lose functionalityGrant admin privs or lose functionality
I.e. Automatic server restart, proxy jobsI.e. Automatic server restart, proxy jobs
Configure an Operator Configure an Operator SQL Server Agent mail is different from SQL Server Agent mail is different from
SQLMail (xp_sendmail)SQLMail (xp_sendmail) Uses a MAPI profile (Outlook)Uses a MAPI profile (Outlook) Log in to the server as the service Log in to the server as the service
account and configure the Outlook account and configure the Outlook mail profilemail profile
Automate AlertsAutomate Alerts
Automatically be notified when Automatically be notified when particular conditions occurparticular conditions occur
SQL Server Agent can issue alerts SQL Server Agent can issue alerts based on either error number or based on either error number or severity of error(s)severity of error(s) Read from the Windows event logRead from the Windows event log
A number of alerts are pre-configuredA number of alerts are pre-configured But not going anywhere without you But not going anywhere without you
configuring them further…configuring them further… Customize as appropriate for your siteCustomize as appropriate for your site
Create Your Own AlertsCreate Your Own Alerts
You can create application-specific You can create application-specific alertsalerts Run sp_addmessage to add a custom Run sp_addmessage to add a custom
error messageerror message Run RAISERROR WITH LOG to raise the Run RAISERROR WITH LOG to raise the
event and write a copy to the Windows event and write a copy to the Windows application event logapplication event log Otherwise SQL Agent doesn’t see your errorOtherwise SQL Agent doesn’t see your error
Set up an alert to respond to your error Set up an alert to respond to your error numbernumber
Run jobs as appropriate…Run jobs as appropriate…
Alerting Based on Alerting Based on Perfmon CountersPerfmon Counters UI added in SQL Server 7.0/2000 to do thisUI added in SQL Server 7.0/2000 to do this Configure through same UI as Transact-SQL Configure through same UI as Transact-SQL
generated alertsgenerated alerts Monitor critical information not otherwise Monitor critical information not otherwise
easy to get ateasy to get at Memory UsageMemory Usage Database sizeDatabase size Tempdb usageTempdb usage
Note that this is an expensive operationNote that this is an expensive operation
Setting Up AlertsSetting Up Alerts
demodemo
AgendaAgenda
The BasicsThe Basics Automating AlertsAutomating Alerts Automate routine tasksAutomate routine tasks Secure Your ServerSecure Your Server Monitor for performanceMonitor for performance Data loadingData loading
Automate Routine TasksAutomate Routine Tasks
Get this “busy work” done Get this “busy work” done automagically…automagically…
If it needs to be done daily, or weekly, If it needs to be done daily, or weekly, then automate itthen automate it We’ve seen too many DBAs run We’ve seen too many DBAs run
everything ad-hoceverything ad-hoc This WON’T make you useless, we This WON’T make you useless, we
promise!promise!
Automate Your BackupsAutomate Your Backups
All backups should be run All backups should be run automaticallyautomatically
Backup type depends on database Backup type depends on database size and access patternssize and access patterns See Chapter 4 of the operations guide See Chapter 4 of the operations guide
for detailsfor details
Back up system databases as well Back up system databases as well as your own application as your own application database(s)database(s)
Don’t Forget to Back Don’t Forget to Back Up ScriptsUp Scripts Generate full scripts of all objects you Generate full scripts of all objects you
can periodicallycan periodically Use the generate scripts wizardUse the generate scripts wizard May want to automate with SQL-DMOMay want to automate with SQL-DMO Watch for encrypted textWatch for encrypted text Script out logins and custom error messages Script out logins and custom error messages
if you just script out a databaseif you just script out a database
Keep historical scripts and change Keep historical scripts and change scripts in a source control system such scripts in a source control system such as Visual SourceSafeas Visual SourceSafe
Set up a Backup JobSet up a Backup Job
demodemo
A Word About RestoreA Word About Restore
You can’t really automate restore…You can’t really automate restore… But practice, practice, practice!!!But practice, practice, practice!!! Don’t find out your backups don’t work Don’t find out your backups don’t work
when you really need themwhen you really need them
You can’t lose your job over You can’t lose your job over many things, but this is many things, but this is
one of themone of them
Other Maintenance…Other Maintenance…
DBCC CheckDBDBCC CheckDB Again, details in the operations guideAgain, details in the operations guide Check tables rather than databases Check tables rather than databases
on a large serveron a large server Do only for peace of mindDo only for peace of mind
Index ReorgsIndex Reorgs Only as neededOnly as needed Establish a pattern using DBCC Establish a pattern using DBCC
SHOWCONTIGSHOWCONTIG Depends on server usage…Depends on server usage…
Statistics UpdatesStatistics Updates
Log File MaintenanceLog File Maintenance
SQL Server Error LogsSQL Server Error Logs Configure how many you want to keepConfigure how many you want to keep Back them up with your other scripts, etc.Back them up with your other scripts, etc. Sp_cycle_errorlogSp_cycle_errorlog
SQL Server Agent Error LogSQL Server Agent Error Log Recycles after every service restartRecycles after every service restart
Job History LogsJob History Logs Agent properties, Job System tabAgent properties, Job System tab Probably too low by defaultProbably too low by default
DBMaint history logsDBMaint history logs
Database Database Maintenance PlansMaintenance Plans
demodemo
-Runs backups, integrity checks, -Runs backups, integrity checks, update index/column stats…update index/column stats…-Defrag script-Defrag script
AgendaAgenda
The BasicsThe Basics Automating AlertsAutomating Alerts Automate routine tasksAutomate routine tasks Secure Your ServerSecure Your Server Monitor for performanceMonitor for performance Data LoadingData Loading
Secure Your ServerSecure Your Server
Set up your server securelySet up your server securely Use a service account with a strong Use a service account with a strong
passwordpassword Don’t run SQL Server service as an Don’t run SQL Server service as an
administratoradministrator Run in integrated security mode - but still Run in integrated security mode - but still
apply a strong password to “sa”apply a strong password to “sa” Run on NTFS file systemRun on NTFS file system
Encrypt the data filesEncrypt the data files What to change/setupWhat to change/setup
Use Security AuditingUse Security Auditing
Set up login security auditingSet up login security auditing Not on by defaultNot on by default Configure on the security tab of Server Configure on the security tab of Server
Properties in Enterprise ManagerProperties in Enterprise Manager Enable for Failure or AllEnable for Failure or All View using the Windows Event ViewerView using the Windows Event Viewer
SQL Server 2000 AuditingSQL Server 2000 Auditing
SQL Trace – the server side of profilingSQL Trace – the server side of profiling SQL Profiler – the UI componentsSQL Profiler – the UI components Auditing is performed by SQL Trace – Auditing is performed by SQL Trace –
internal to SQLServr.exeinternal to SQLServr.exe Very robust and secureVery robust and secure
SQL Trace FeaturesSQL Trace Features
File rolloverFile rollover So you can back up the old trace files So you can back up the old trace files
while a new one is being populatedwhile a new one is being populated
Specify a max file size limit or an Specify a max file size limit or an end time for a traceend time for a trace
All audit types and data columns All audit types and data columns are selectableare selectable Get as much or as little auditing as Get as much or as little auditing as
you want/needyou want/need
SQL Server 2000 Audit EventsSQL Server 2000 Audit Events
Login/logoutLogin/logout GRD - statement permsGRD - statement perms GRD – object permsGRD – object perms Add/drop SQL loginAdd/drop SQL login GRD NT login rightsGRD NT login rights Modify login propertyModify login property Password change eventPassword change event Add/remove from fixed Add/remove from fixed
server role server role Add/remove database userAdd/remove database user Derived PermissionsDerived Permissions
Add/remove database Add/remove database role memberrole member
Add/drop a database roleAdd/drop a database role Change Approle passwordChange Approle password Statement permission usedStatement permission used Object permission usedObject permission used Backup/restore eventBackup/restore event DBCC command issuedDBCC command issued Audit modification eventAudit modification event Server shutdown/pause/startServer shutdown/pause/start
We audit 19 different kinds of events:We audit 19 different kinds of events:
SQL Server 2000 AuditingSQL Server 2000 Auditing
For each event, many subtypes For each event, many subtypes Example - GDR object permission:Example - GDR object permission:
GrantGrant DenyDeny RevokeRevoke
Each event includes (at a minimum):Each event includes (at a minimum): Success or failure Success or failure Server nameServer name Date/time of eventDate/time of event Application nameApplication name
NT usernameNT username SpidSpid Host nameHost name Statement textStatement text
How To Turn On An AuditHow To Turn On An Audit
An audit (except for C2 audit) is An audit (except for C2 audit) is just a profiler tracejust a profiler trace
So, turn on a profiler trace with the So, turn on a profiler trace with the new profiler procedures, adding new profiler procedures, adding auditing eventsauditing events
Set the trace to start with the Set the trace to start with the server if you want a comprehensive server if you want a comprehensive auditaudit Wrap the trace setup into a stored Wrap the trace setup into a stored
procedureprocedure Enable that stored procedure Enable that stored procedure
for autostartfor autostart
Using Profiler to AuditUsing Profiler to Audit
demodemo
Use Profiler to configure an audit traceUse Profiler to configure an audit trace Use the scripting feature to script out Use the scripting feature to script out
the trace definitionthe trace definition
AgendaAgenda
The BasicsThe Basics Automate : AlertsAutomate : Alerts Automate : Routine TasksAutomate : Routine Tasks Secure Your ServerSecure Your Server Monitor for PerformanceMonitor for Performance Data LoadingData Loading
Performance MonitoringPerformance Monitoring
Use System Monitor CountersUse System Monitor Counters Learn what they mean by Learn what they mean by
monitoring your systemmonitoring your system
Use SQL Server ProfilerUse SQL Server Profiler Monitor the overhead carefully Monitor the overhead carefully
on your systemon your system
Use built-in toolsUse built-in tools Replication MonitorReplication Monitor Current Activity MonitorCurrent Activity Monitor Sp_who2Sp_who2
Ongoing MonitoringOngoing Monitoring
Index fragmentationIndex fragmentation DBCC SHOWCONTIGDBCC SHOWCONTIG
May want to run well-defined queries May want to run well-defined queries periodicallyperiodically As a “ping” to verify uptimeAs a “ping” to verify uptime To monitor for performance historyTo monitor for performance history
If MSDE you may run DBCC If MSDE you may run DBCC CONCURRENCYVIOLATION to look for CONCURRENCYVIOLATION to look for performance throttling issuesperformance throttling issues
Run Index Tuning Wizard periodically Run Index Tuning Wizard periodically to see if indexes are still used/relevantto see if indexes are still used/relevant
Capacity PlanningCapacity Planning
Monitor Database sizes, index sizes, Monitor Database sizes, index sizes, filegroup usage, logfile usage, filegroup usage, logfile usage, memory usagememory usage
Watch for new objects if there’s more Watch for new objects if there’s more than one dbadmin (especially likely in than one dbadmin (especially likely in development)development)
Keep track of growth over time to Keep track of growth over time to forecast new hardware needsforecast new hardware needs Very few tools to help you here - Very few tools to help you here -
Microsoft Excel is your friend!Microsoft Excel is your friend!
AgendaAgenda
The BasicsThe Basics Automate : AlertsAutomate : Alerts Automate : Routine TasksAutomate : Routine Tasks Secure Your ServerSecure Your Server Monitor for PerformanceMonitor for Performance Data LoadingData Loading
Data Loading – Quick TipsData Loading – Quick Tips Fastest way to load data is:Fastest way to load data is:
Empty table, no indexesEmpty table, no indexes Use multiple Bulk Insert commands, one per processor, Use multiple Bulk Insert commands, one per processor,
each with a roughly equal partition of the data. If seeking each with a roughly equal partition of the data. If seeking high-end performance then balance your i/o subsystem.high-end performance then balance your i/o subsystem.
Use the TABLOCK hintUse the TABLOCK hint Set the ROWS_PER_BATCH to > rows in each fileSet the ROWS_PER_BATCH to > rows in each file Switch the database to Bulk Logged Recovery ModelSwitch the database to Bulk Logged Recovery Model
BULK INSERT command is the fastest way to load BULK INSERT command is the fastest way to load data – runs in process with SQL Serverdata – runs in process with SQL Server
BCP more flexible but a little slowerBCP more flexible but a little slower DTS can be very fast as well but you should try to DTS can be very fast as well but you should try to
take a table lock for best load performancetake a table lock for best load performance Watch out for concurrency issues with this techniqueWatch out for concurrency issues with this technique
ReferencesReferences
SQL Server Operations GuideSQL Server Operations Guide http://www.microsoft.com/technet/treeviehttp://www.microsoft.com/technet/treevie
w/default.asp?url=/technet/prodtechnol/sqw/default.asp?url=/technet/prodtechnol/sql/maintain/operate/opsguide/default.aspl/maintain/operate/opsguide/default.asp
SQL Server Resource KitSQL Server Resource Kit http://www.microsoft.com/technet/treeviehttp://www.microsoft.com/technet/treevie
w/default.asp?url=/technet/prodtechnol/sqw/default.asp?url=/technet/prodtechnol/sql/reskit/sql2000/sql2kres.aspl/reskit/sql2000/sql2kres.asp
SQL Server Security WhitepaperSQL Server Security Whitepaper http://www.microsoft.com/sql/techinfo/adhttp://www.microsoft.com/sql/techinfo/ad
ministration/2000/securityWP.aspministration/2000/securityWP.asp
Call to ActionCall to Action
Keep an eye on the SQL web site:Keep an eye on the SQL web site: http://www.microsoft.com/sqlhttp://www.microsoft.com/sql
Some components of SQL Server ship more Some components of SQL Server ship more frequently than the main engine:frequently than the main engine: SQL Server CESQL Server CE
sscebeta@microsoft.comsscebeta@microsoft.com
Notification Services Notification Services SQL XMLSQL XML
SOAP Interface, XML Bulk Load, XML OLEDB providerSOAP Interface, XML Bulk Load, XML OLEDB provider http://www.microsoft.com/sql/techinfo/xmlhttp://www.microsoft.com/sql/techinfo/xml
MDACMDAC http://www.microsoft.com/datahttp://www.microsoft.com/data
Don’t forget to complete the Don’t forget to complete the on-line Session Feedback form on-line Session Feedback form on the Attendee Web siteon the Attendee Web site
https://web.mseventseurope.com/teched/https://web.mseventseurope.com/teched/
© 2002 Microsoft Corporation. All rights reserved.© 2002 Microsoft Corporation. All rights reserved.This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
top related