The Default DBA

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQLServerCentral.com » Editorials » The Default DBA

15 posts, Page 1 of 2

1 2 »»

The Default DBA

Rate Topic

Display Mode

Topic Options

Author

Message

Steve Jones - SSC Editor

Posted Monday, December 03, 2012 9:39 PM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:20 AM
Points: 31,437, Visits: 13,752

Comments posted to this topic are about the item The Default DBA

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #1392244

jasona.work

Posted Tuesday, December 04, 2012 7:24 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:32 AM
Points: 474, Visits: 2,379

I think having the SQL installer give the option to setup Database Mail during install, as well as possibly some "best practices" alerts would be a great help, especially for the "accidental DBA" (like myself)

When I first installed SQL, I had no idea something like DBMail even existed, so for some time our server ran without it being configured. The company eventually hired someone to run the Accounting department who used SQL far more than I had, who set it up on the SQL Server. Now, it's been off to the races...

Jason

Post #1392477

Your Name Here

Posted Tuesday, December 04, 2012 8:01 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 23, 2013 1:39 PM
Points: 130, Visits: 513

I have a post-install script that sets up all of my nightly maintenance and backup stuff as well as the alerts, operators, DBMail config, etc.

Considering the number of servers I set up, the time spent on writing the script was well worth the effort. No typos, consistent job names/times/backup locations/etc.

/*
** Script name.....: SQLAgent_SetupMaintenanceSupport.sql
** Blame...........: Ken
** Purpose.........: Automate
** o Enabling Database Mail
** o Creating an email account for DBMail
** o Creating an email profile for DBMail
** o Associating an email account with a profile
** o Making the new profile the default profile
** o Create an operator based on the new profile
** o Sending a test email message
** o Adding a login for the new job owner
** o Creating a full backup job and schedule (with DR scripting)
** o Creating a differential backup job and schedule (both disabled)
** o Creating a transaction log backup job and schedule
**

Post #1392512

Chris Harshman

Posted Tuesday, December 04, 2012 10:52 AM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:20 AM
Points: 1,564, Visits: 1,719

Your Name Here (12/4/2012)

...
** Blame...........: Ken
...

I like that much better than Author in the comment header!

Post #1392620

SAinCA

Posted Tuesday, December 04, 2012 11:01 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:24 PM
Points: 116, Visits: 489

@Ken

Care to share the code for "accidental DBA consumption", please?

DBMail has proven elusive to consistently configure (had to resort to bringing the dialogs up side by side and copy-paste - how advanced is that!?) and Operators etc. look to be useful but I've no time to research them...

Even if you can post parts of the SP, that would benefit a slew of folks like me, I'd warrant...

Cheers!

Post #1392626

Your Name Here

Posted Tuesday, December 04, 2012 11:34 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 23, 2013 1:39 PM
Points: 130, Visits: 513

The script references some scripts and a few batch files that I put on the server to handle maintenance, backups and disaster recovery. I'm not sure what would be easier: giving you code that will create jobs that refer to nonexistent files or carve out the references and give you a [somewhat] untested script. Also, it's 1195 lines of code so I'm not sure how happy SQLServerCentral's forum will be with a post that big. It doesn't look like I can attach files to a post.

Post #1392642

Your Name Here

Posted Tuesday, December 04, 2012 12:05 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 23, 2013 1:39 PM
Points: 130, Visits: 513

Sorry. Sometimes I miss the point. DBMail setup.

Here's the code I use for setting up DBMail - I carved the other junk out. There are probably several ways to do this and my way is just one opinion. Others may have a better solution and I'd be excited to see their approach. I simply needed it done, couldn't find a decent example to start with so I started slingin' code for better or worse.

You may want to test it a few times on dev servers - don't trust me. <lol>
(that wasn't an <evil lol> BTW)

Also in the "you may need this" department: The SSMS tasks to [remove] DBMail are (from the wizard of course):

Configure DBMail
Manage Database Mail accounts and profiles
View, change, or delete an existing profile
[Delete] button
[Next] button
[Finish] button

Configure DBMail
Manage Database Mail accounts and profiles
View, change, or delete an existing account
[Delete] button
[Next] button
[Finish] button

Don't sweat the 'Default DBA' thing. Everybody here was a noob at one point in their career. The trick is to never panic. SQLServerCentral is here for you and Google is your friend. Except when it comes to T-SQL script examples for setting up DBMail, apparently. <grin>

I sure hope this comes across as 'Courier New' or it's gonna look ugly...

/*
** Script name.....: DBMail_Setup.sql
** Blame...........: Ken
** Date written....: 12/04/2012
** Purpose.........: Automate
** o Enabling Database Mail
** o Creating an email account for DBMail
** o Creating an email profile for DBMail
** o Associating an email account with a profile
** o Making the new profile the default profile
** o Create an operator based on the new profile
** o Sending a test email message
*/

/*
** "Challenger, go at throttle up..."
*/
SET NOCOUNT ON

-- Declare variables
DECLARE @accountname NVARCHAR(128), -- DBMail EMail account
@profilename NVARCHAR(128), -- DBMail EMail profile
@displayname NVARCHAR(128), -- DBMail EMail display name
@emailaddress NVARCHAR(128), -- DBMail EMail address used
@mailservername NVARCHAR(128), -- DBMail EMail server
@operatorname NVARCHAR(128), -- SQLAgent operator name
@testemailto NVARCHAR(128), -- DBMail send test email to address
@errorflag TINYINT -- Flag for script errors

-- Initialize variables
/*
*****************************************
** **
** MODIFY THESE VARIABLES IF NECESSARY **
** **
*****************************************
*/
SELECT @accountname = '<OperatorAlias, sysname, DBA>',
@profilename = '<OperatorAlias, sysname, DBA>',
@operatorname = '<OperatorAlias, sysname, DBA>',
@emailaddress = '<OperatorAlias, sysname, DBA>@coopertire.com',
@mailservername = '<SMTPServer, sysname, SMTP.nowhere.com>',
@operatorname = '<OperatorAlias, sysname, DBA>',
@testemailto = '<TestEMailTo, sysname, ken@nowhere.com>'

/*
** Script variables
*/
SELECT @errorflag = 0

-- Output script settings line 1
SELECT @accountname AS accountname,
@profilename AS profilename,
@emailaddress AS emailaddress,
@mailservername AS mailservername,
@operatorname AS operatorname,
@testemailto AS testemailto

-- Enable DBMail
IF (SELECT [value_in_use]
FROM sys.configurations
WHERE [name] = 'Database Mail XPs') = 1
BEGIN
PRINT 'CHECK: Database Mail already enabled.'
END
ELSE
BEGIN
PRINT 'Enabling DBMail...'
USE [msdb];
EXEC sp_configure 'show advanced', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'show advanced', 0;
RECONFIGURE WITH OVERRIDE;
IF @@ERROR != 0 SELECT @errorflag = @errorflag + 1
END

-- Create a Database Mail account
IF EXISTS (
SELECT *
FROM msdb.dbo.sysmail_account a
WHERE a.name = @AccountName)
BEGIN
PRINT 'CHECK: DBMail account ' + @accountname + ' already exists.'
END
ELSE
BEGIN
PRINT 'Creating Account ' + @accountname + '...'
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @accountname,
@description = 'Mail account for Findlay Database Administration team.',
@email_address = @emailaddress,
@display_name = @displayname,
@mailserver_name = @mailservername;
IF @@ERROR != 0 SELECT @errorflag = @errorflag + 1
END

-- Create a Database Mail profile
IF EXISTS (
SELECT *
FROM msdb.dbo.sysmail_profile p
WHERE p.name = @ProfileName)
BEGIN
PRINT 'CHECK: DBMail profile ' + @profilename + ' already exists.'
END
ELSE
BEGIN
PRINT 'Creating Profile ' + @profilename + '...'
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @profilename,
@description = 'Profile used for Findlay Database Administration team.' ;
IF @@ERROR != 0 SELECT @errorflag = @errorflag + 1
END

-- Add the account to the profile
IF EXISTS(
SELECT *
FROM msdb.dbo.sysmail_profileaccount pa
JOIN msdb.dbo.sysmail_profile p
ON pa.profile_id = p.profile_id
JOIN msdb.dbo.sysmail_account a
ON pa.account_id = a.account_id
WHERE p.name = @ProfileName
AND a.name = @AccountName)
BEGIN
PRINT 'CHECK: DBMail profile/account ' + @profilename + '/' + @accountname + ' already exists.'
END
ELSE
BEGIN
PRINT 'Creating Profile/Account ' + @profilename + '/' + @accountname + '...'
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @profilename,
@account_name = @accountname,
@sequence_number =1 ;
IF @@ERROR != 0 SELECT @errorflag = @errorflag + 1
END

-- Grant access to the profile to all users in the msdb database
IF (SELECT prinprof.is_default
FROM sys.database_principals dbprin,
msdb.dbo.sysmail_principalprofile prinprof,
msdb.dbo.sysmail_profile prof
WHERE dbprin.principal_id = msdb.dbo.get_principal_id(prinprof.principal_sid)
AND prof.profile_id = prinprof.profile_id
AND prof.name = @profilename
) = 1
BEGIN
PRINT 'CHECK: DBMail profile ' + @profilename + ' is already the default email profile.'
END
ELSE
BEGIN
PRINT 'Making ' + @profilename + ' the default email profile...'
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = @profilename,
@principal_name = 'public',
@is_default = 1 ;
IF @@ERROR != 0 SELECT @errorflag = @errorflag + 1
END

-- Add an operator
IF EXISTS (SELECT [name] FROM msdb.dbo.sysoperators WHERE [name] LIKE @operatorname)
BEGIN
PRINT 'CHECK: Operator ' + @operatorname + ' already exists.'
END
ELSE
BEGIN
PRINT 'Creating ' + @operatorname + ' operator...'
EXEC msdb.dbo.sp_add_operator
@name = @operatorname,
@enabled = 1,
@weekday_pager_start_time = 90000,
@weekday_pager_end_time = 180000,
@saturday_pager_start_time = 90000,
@saturday_pager_end_time = 180000,
@sunday_pager_start_time = 90000,
@sunday_pager_end_time = 180000,
@pager_days = 0,
@email_address = @emailaddress,
@category_name = N'[Uncategorized]'
IF @@ERROR != 0 SELECT @errorflag = @errorflag + 1
END

-- Send test message
PRINT 'Sending test email message...'
EXEC msdb.dbo.sp_send_dbmail @profile_name = @profilename,
@recipients = '<TestEMailTo, sysname, ken@nowhere.com>',
@subject = 'Test alert message',
@body = 'Please ignore this message - it is only a test.'
IF @@ERROR != 0 SELECT @errorflag = @errorflag + 1

-- End of script
IF @errorflag != 0
PRINT 'Script completed with ' + CONVERT(VARCHAR(5), @errorflag) + ' warning(s).'
ELSE
PRINT 'Script completed successfully.'
SET NOCOUNT OFF
GO

Post #1392649

SAinCA

Posted Tuesday, December 04, 2012 12:25 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:24 PM
Points: 116, Visits: 489

Your Name Here (12/4/2012)

Except when it comes to T-SQL script examples for setting up DBMail, apparently. <grin>

Thanks, script posted just fine... Totally agree with your comment, above - found nothing to help until your post.

I have a new QA server that needs DBMail, so I'll definitely be a guinea pig and post back if I find any oops! moments.

Cheers!

Post #1392657

Your Name Here

Posted Tuesday, December 04, 2012 12:41 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 23, 2013 1:39 PM
Points: 130, Visits: 513

Cool. I gave it a test ride before I sent it but I'd appreciate your input. Sadly, the font came over in the post but the spacing didn't. I took such great care to indent my code for readability too...

Post #1392664

SAinCA

Posted Tuesday, December 04, 2012 1:36 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:24 PM
Points: 116, Visits: 489

I pointed SQL Prompt at it immediately I saw the mess the copy-paste left behind. Didn't attribute you with sloppy indentation BigGrin

Post #1392679

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions