The Default DBA

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

  • 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

  • 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

    **

  • Your Name Here (12/4/2012)


    ...

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

    ...

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

  • @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!

  • 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.

  • 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]

  • 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!

  • 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...

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

  • 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.

  • @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

  • 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.

  • 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.

  • 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