roman rehak: 24/7 database administration + database mail unleashed
DESCRIPTION
7 octobre 2014 Groupe SQL Sujets: 24/7 Database Administration & Database Mail Unleashed Conférencier: Roman Rehak from Burlington 24/7 Database Administration Keeping your SQL Server databases available 24/7 is a very challenging task. This presentation will discuss the issues and challenges involved, possible strategies, and provide many recommendations about how to make sure your environment remains available around the clock. The presenter manages a team of DBAs responsible for several ecommerce websites with high required uptime so this presentation will be very much "from the trenches" material. Database Mail Unleashed SQL Server provides a mailing framework called Database Mail. You will learn how Database Mail works, how to set it up, how to use it effectively for notifications, and other every daily database administration tasks.TRANSCRIPT
![Page 1: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/1.jpg)
Two for Tuesday1) Database Mail Unleashed
2) 24/7 Database Administration
Roman RehakPrincipal Database Architect
MyWebGrocerBurlington, VT
![Page 2: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/2.jpg)
• Principal Database Architect/DBA Team Manager at MyWebGrocer• Independent SQL Server consultant• Specialize in
SQL Server administration, High Availability Data Access, ADO.NET, SSIS, data exchange
• Writer for Visual Studio Magazine, SQL Server Magazine, SearchSQLServer.com
• Involved in Professional Association for SQL Server (www.sqlpass.org) Speaker at 2002, 2003, 2006 PASS Community Summit Member of Program Committee the last 7 years
Track Leader for “Database and Application Development” track• DevTeach /SQLTeach Chair for SQL Server • President of Vermont SQL Server User Group• Blog: http://sqlblog.com/blogs/roman_rehak
Professional Background
![Page 3: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/3.jpg)
Part 1Database Mail Unleashed
![Page 4: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/4.jpg)
SQL Mail and why it sucks
• Used in SQL Server 2000 and prior versions• Based on and relying on MAPI• Not supported on cluster instances• Many, many issues
Required Outlook installation Messages stuck in Outbox
Unless Outlook opened all the time Works with SMTP, but can hang SQL Server
If SMTP server stopped or not accessible
![Page 5: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/5.jpg)
If you are stuck with 2000 for now
• Use xp_smtp_sendmail instead• Written by Gert Drapers• Uses SMTP only• Easy to set up• Powerful interface
HTML output Attachments
• http://www.sqldev.net/xp/xpsmtp.htm• http://mreschke.com/topic/167/SMTP+from+MSSQL+Proce
dure
![Page 6: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/6.jpg)
Database Mail
• No More MAPI!!!• SMTP based• Asynchronous processing
Uses Service Broker
• Robust• Scalable• Fault tolerant
![Page 7: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/7.jpg)
Database Mail architecture
• Profile This is what you specify when calling mail sp
• Accounts Contains details about SMTP servers
• System views For viewing sent emails, errors
• Security – role in msdb, public/private• SQL Agent Mail
![Page 8: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/8.jpg)
Profile
• Holds a collection of SMTP accounts• Holds security details
Private and Public settings
• Priority for selecting order of accounts Starts with lowest, goes up Once email is sent, it keeps using the first successful account until it fails Once last successful account fails, starts at 1 again
![Page 9: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/9.jpg)
SMTP Account• Contains settings for a single SMTP Server
Server name Port Reply-To email address
• Three authentication methods Windows Authentication Basic Authentication
Works with ISP SMTP servers Anonymous Authentication
• Can be used by multiple profiles
![Page 10: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/10.jpg)
What happens when you send email
• Mail is queued up in mail queue in msdb• Sp_send_dbmail returns immediately
Further mail processing is asynchronous
• New row appears in sysmail_allitems Status is unsent
• DB Mail executable is notified• DB Mail attempts delivery (possibly retrying)• On success, status is updated to sent• On failure, status is updated to failed
![Page 11: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/11.jpg)
Security
• Mail is off by default Enable with wizard or sp_configure
• Msdb has a new role – DatabaseMailUserRole Add DB users to grant send mail privileges
• Profile security Public for all users in the database Private for specific users
• Control over attachments Max size, prohibited file extensions
![Page 12: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/12.jpg)
System views in msdb
• sysmail_allitems Shows all mail messages
• sysmail_faileditems Might be useful to monitor
• sysmail_sentitems• sysmail_unsentitems• sysmail_event_log
Shows status and error messages
• sysmail_mailattachments
![Page 13: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/13.jpg)
Demo – Database Mail
![Page 14: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/14.jpg)
Utilizing DB Mail in notifications
• Can be easily used with SQL Agent• Create a SQL Operator
Use valid email
• Configure SQL Agent to use DB MAIL Select Database Mail profile to be used SQL Agent NEEDS TO BE RESTARTED
• Tips for SQL Agent notifications Notify on failures ONLY Include server name in profile Set up folders and rules (labels in Gmail)
![Page 15: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/15.jpg)
Demo – SQL Agent Mail
![Page 16: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/16.jpg)
DB Mail Best Practices
• Configure alternate account for failover• If busy, spread over different SMTP servers
Use profiles to load balance
• Purge old emails/logs when not needed sysmail_delete_mailitems_sp (purges messages) sysmail_delete_log_sp (purges mail logs) Determine retention period Use scheduled job to purge
• Keep off if not needed
![Page 17: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/17.jpg)
DB Mail Best Practices
• Create a default profile• Utilize with SQL Agent• Keep security tight• Do not use multiple emails in TO, CC or BCC
Possible bug in DB Mail If one emails fails, processing stops, then retries again Successful emails up to the point of failure might get multiple copies until
processing is stopped
• Distinguish between QA/Dev and Production
![Page 18: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/18.jpg)
DB Mail Best Practices
• Use when it makes sense Don’t turn this your mass/spam mailer Although scalable, SQL Server has other things to do
• Good use SQL Agent alerts and notifications Emailing errors from jobs, SSIS, backend processing Data driven subscriptions Small to medium scale email in applications
Keep scalability in mind
![Page 19: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/19.jpg)
Part 224/7 Database Administration
![Page 20: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/20.jpg)
• High Availability Overview• SQL Server High Availability options• Case Study - HA with mirroring • Maintaining Standby Servers • Preventing downtime • Minimizing downtime• Questions
Agenda
![Page 21: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/21.jpg)
24/7 Database Administration
• Administering databases that need to run 24/7 Extremely difficult Certain downtime required to do Windows updates, etc… Small periodic maintenance window can make a big difference
• High Availability is extremely important Different types, level of complexity, pros and cons with each Don’t believe Microsoft marketing
• Requires planning - drills are crucial “Si vis pacem, para bellum” Things unexpected do happen, but we can learn from them Need to have monitoring and alerts in place
• Having a talented and dedicated team is crucial
![Page 22: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/22.jpg)
Successful High Availability components
• High availability options at the SQL Server level• High availability at the hardware level• Monitoring• Alerts and notifications• Trained and responsive team• Emergency response procedures• Emergency drills• Sufficient documentation
![Page 23: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/23.jpg)
• High Availability Overview• SQL Server High Availability options• Case Study - HA with mirroring • Maintaining Standby Servers • Preventing downtime • Minimizing downtime• Questions
Agenda
![Page 24: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/24.jpg)
SQL Server High Availability
• The true HA does not exist yet• SQL Server fundamentally remains a single point of failure• Different options exist
Each has prons and cons Failing database(s) over is easy, but SQL Agent jobs need attention too SQL Server Clustering got it close, but there were too many issues to make
it hard to run reliably
• What we need is a true, failure transparent cluster• In the meantime
Learn what’s available Pick the right technology/tools
![Page 25: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/25.jpg)
Native Replication
• When to use Expertise in the house Publishing will not affect data availability
Deadlocks while publishing Published tables get locked up
• Suitable Data redundancy Less suitable as an application backend
• If you use it Implement alerts when failures occur Respond quickly Protect subscribers with triggers, read-only privileges
![Page 26: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/26.jpg)
Restored Database Copies
• Simple and effective way of spreading data load• Suitable when data latency is not super high• Added benefit – testing your backups• Good uses
Reporting server Source for Data Warehouse extracts Application – use separate connection string for data older that X number of
hours
• You may need to kick off custom SQL after a restore Logins, permissions, additional objects, etc…
• It can provide partial High Availability (order history, …)
![Page 27: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/27.jpg)
SQL Server Clustering
• Multiple physical nodes create a virtual node• Some learning curve but it has become easier• One huge advantage – exposed by a virtual name,
regardless of what node it’s running on• Failover is transparent• You lose existing connections• No changes in connection strings needed• Scheduled jobs continue to work• Good hardware recommended
![Page 28: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/28.jpg)
AlwaysOn
• New in SQL Server 2012• Powerful but complex• Requires Enterprise license for both SQL Server and
Windows to get all features• Improvements
Database availability groups Exposed as a single endpoint for connection strings More than one replica Data on replicas can be used for read-only access
• This may not work for small companies
![Page 29: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/29.jpg)
Database Mirroring
• Database is mirrored to another server• In case of an issue, you can failover to mirror server
Manual failover Automatic failover
• Transactions applied to mirror High Performance – asynchronous High Safety - synchronous
• Not server level Need to maintain logins, jobs, etc
• Database not available on mirror But, you can create a snapshot Possibly HA/load balancing option
![Page 30: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/30.jpg)
• High Availability Overview• SQL Server High Availability options• Case Study - HA with mirroring • Maintaining Standby Servers • Preventing downtime • Minimizing downtime• Questions
Agenda
![Page 31: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/31.jpg)
Database Mirroring Demo
![Page 32: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/32.jpg)
Database Mirroring Tips• Script it out
The wizard is OK, but tedious when doing 10+ databases
• Increase the default timeout 10 seconds not enough ALTER DATABASE Foo SET PARTNER TIMEOUT 120
• Do not mirror to an SSD drive if possible Replaying of transaction log different than on the primary 6 times more I/O
• Set up alerts Several events, you probably want to know if a failover occurred
• Set up backups on both sides Use “Ignore databases not online” in the backup task (Maintenance Plans)
![Page 33: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/33.jpg)
Intermission
![Page 34: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/34.jpg)
• High Availability Overview• SQL Server High Availability options• Case Study - HA with mirroring • Maintaining Standby Servers • Preventing downtime • Minimizing downtime• Questions
Agenda
![Page 35: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/35.jpg)
Dual-server deployment
• Needed anywhere you use a standby machine (AlwaysOn, Database Mirroring, SQL Server Clustering, Log Shipping)
• Deploy to each SQL Server instance SQL Logins Scheduled jobs Linked Servers Note – N/A for clustering
• Deploy to each physical computer Powershell scripts SQL scripts Executables/DLL components SSIS packages
![Page 36: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/36.jpg)
Dual-server deployment tools• Scripting logins
Script out logins on primary, apply to standby servers Use my script to preserve SIDs for SQL Server logins Make sure you have fast access to passwords if needed
• Synchronizing scheduled jobs Many different options out there You can use my SSIS package Generates scripts multiple servers, saves to a folder Typically you can ignore replication and maintenance plans jobs
Utilize categories to manage/script only what you need
• Use automation Sqlcmd Powershell
![Page 37: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/37.jpg)
Scripting SQL Agent Jobs Demo
![Page 38: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/38.jpg)
• High Availability Overview• SQL Server High Availability options• Case Study - HA with mirroring • Maintaining Standby Servers • Preventing downtime • Minimizing downtime• Questions
Agenda
![Page 39: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/39.jpg)
Preventing downtime• Extensive monitoring
Performance (3rd party tools) Low disk space Transaction logs growing too large
Failed replication Paused/Suspended mirroring Not setting up log backups Disabling log backups
• Set up alerts Failed jobs, mirroring disconnects, replication hiccups Page only when needed
• Distribute email notification handling within the team • Do not notify of success unless you really need it
![Page 40: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/40.jpg)
Preventing downtime
• Utilize Policy Management Many possible types of alerts can be easily created Examples
Email us when no full backup was done within last 25 hours Email us when no log backup was done within last 2 hours Many other possibilities
• Invest in good hardware EBay is not your friend
• Run DBCC periodically• Use tools for database access, not direct SQL
If you need direct updates, train people to COMMIT
![Page 41: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/41.jpg)
Preventing downtime
• DBA instinct• Learn from your incidents• Build checklists for common procedures
Example – Enable what you disabled, etc..
• Build a KB/Wiki/Whatever for your company• Cross-train the team members
DBA Mirroring At least two people to be SME, ideally more than that
![Page 42: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/42.jpg)
Warning - SSD/RAMSAN drives
• Great for speed increase• Mainly for READS, much less for WRITES• Not suitable for transaction logs• Potential issues
Single point of failure The number of good sectors does decrease Database mirroring causes fast wear&tear
• If you use them Consider implementing an array of SSDs
![Page 43: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/43.jpg)
• High Availability Overview• SQL Server High Availability options• Case Study - HA with mirroring • Maintaining Standby Servers • Preventing downtime • Minimizing downtime• Questions
Agenda
![Page 44: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/44.jpg)
Minimizing downtime
• Pager is very important for quick response Extra compensation makes a difference Per week or per incident
• Sufficient documentation Who uses what, when, how, emergency contacts, downtime implications,
etc… Development team input required
• Demand regular maintenance nights Upgrades, re-indexing, table locking ops, etc
• Have “Maintenance pages” ready to go Takes pressure off somewhat
• Tell execs and managers to back off
![Page 45: Roman Rehak: 24/7 Database Administration + Database Mail Unleashed](https://reader036.vdocuments.us/reader036/viewer/2022062709/558dfc9e1a28abba0d8b4578/html5/thumbnails/45.jpg)
Minimizing downtime• Utilize automation to rebuild things
Wizards are great but slow Scripting is your friend here Examples – rebuild database mirroring, replication
• Data Center tips Make sure multiple employees are familiar with the data center Utilize “Remote Hands” service Line up “Remote Hands” for SQL Server restarts and reboots If you lose power – BRING DOMAIN CONTROLLERS ONLINE FIRST!
• Develop procedures for possible SQL Server issue SUSPECT database Losing TEMPDB data or log drive Losing MASTER database