Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Default DBA


The Default DBA

Author
Message
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36111 Visits: 18742
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
My Blog: www.voiceofthedba.com
jasona.work
jasona.work
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1893 Visits: 10927
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
Your Name Here
Your Name Here
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 790
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
**
Chris Harshman
Chris Harshman
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2681 Visits: 3278
Your Name Here (12/4/2012)
...
** Blame...........: Ken
...


Hehe I like that much better than Author in the comment header!
SAinCA
SAinCA
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 683
@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!
Your Name Here
Your Name Here
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 790
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.
Your Name Here
Your Name Here
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 790
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

SAinCA
SAinCA
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 683
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!
Your Name Here
Your Name Here
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 790
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...
SAinCA
SAinCA
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 683
I pointed SQL Prompt at it immediately I saw the mess the copy-paste left behind. Didn't attribute you with sloppy indentation :-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search