December 3, 2012 at 9:39 pm
Comments posted to this topic are about the item The Default DBA
December 4, 2012 at 7:24 am
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
December 4, 2012 at 8:01 am
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
**
December 4, 2012 at 10:52 am
Your Name Here (12/4/2012)
...** Blame...........: Ken
...
:hehe: I like that much better than Author in the comment header!
December 4, 2012 at 11:01 am
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!
December 4, 2012 at 11:34 am
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.
December 4, 2012 at 12:05 pm
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...
[font="Courier New"]
/*
** 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
[/font]
December 4, 2012 at 12:25 pm
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!
December 4, 2012 at 12:41 pm
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...
December 4, 2012 at 1:36 pm
I pointed SQL Prompt at it immediately I saw the mess the copy-paste left behind. Didn't attribute you with sloppy indentation 😀
December 5, 2012 at 8:33 am
As an accidental DBA myself I definitely think more defaults or guidance towards best practice would be a good thing. MS have made great improvements over the years in some areas, the main example coming to mind being setting individual service accounts (with 2012 seemingly creating individual logins and setting permissions for you automatically... though I've yet to find any docs explaining what's actually happening under the hood), but there's definitely room for more.
Considering the direction most other MS server products are going I'm surprised they haven't simply introduced a post-install check list yet. Eg, have a screen popup (until you tell it to go away and never come back) with a list of standard tasks that most installations need, tick boxes to check them off, and links to either get more information or better yet ask SQL to crack on and set it up for you. Experienced users can dismiss it immediately and continue as before, while newbies have a check list of things that if they didn't know about already, they now know they need to learn about and setup.
December 5, 2012 at 1:36 pm
@SAinCA
I apologize - I sort of rushed through carving out the relevant chunks of code for you yesterday and I realized just now that I inadvertently made a judgement call for you: I set the default profile to be available to all users. If you decide to allow other users to be able to send mail [and they don't provide their profile] the default "sent from" profile will be yours. Maybe this isn't an issue or maybe it is, I just thought I'd give you a heads up.
It's the section commented
-- Grant access to the profile to all users in the msdb database
Sorry for the force-fed-setting...
Cheers,
Ken
December 5, 2012 at 2:07 pm
Thanks for remembering! I'll insert a comment "Note to self: ..." in the code to revisit that!
I'll be a bit delayed as the current server is Win2003 and I can't install SQL2012 on it!
May just try out Win2012 while I'm rebuilding...
Cheers.
December 6, 2012 at 2:19 am
SAinCA (12/5/2012)
May just try out Win2012 while I'm rebuilding...
2012's definitely worth checking out, even with Metro / IFKAM it's a big improvement in terms of easy setup. Semi on-topic, the big thing we've noticed is the way that it fixes many of its own errors (after asking you of course). Eg, rather than a dumb popup telling you that component x is required before you can do an installation, so go find it, install it, and come back when you're done, it instead says "Hey, I know where that component is on the install media, or where I can download it from Microsoft, would you like to just crack on with it?" You even get an option to tell it not to prompt before reboots just get on with it, which is very useful with a new build not yet in production especially where an install requires multiple reboots. No more checking the status of a setup just to find it's be sat waiting for you to click ok for a reboot for the last 30 minutes.
April 15, 2013 at 12:36 pm
I really appreciate this script. Your script was mentioned in the discussion about dbWarden, so I came looking for it. While learning it and putting it to use I hit a couple bumps that I figured other might run into.
1. I was not familiar with SSMS templates and Ctrl+Shift+M to fill them in. A brief comment would have kept me from wondering why the script had those strange constructs while I fiddled with filling in our specific details.
2. The script initializes @operatorname twice while missing @displayname.
3. In the SQL for sending a test email, the script has a template field again instead of using @testemailto. This kept me scratching my head waiting for an email to arrive since it didn't cause an error to be reported.
4. For use with dbWarden, I added a conditional around creating the operator since dbWarden creates operators for it's own use.
I will definitely keep track of this script.
Thanks again.
Kelly
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply