Click here to monitor SSC

Jonathan has been working with SQL Server since 1999. He enjoys performance tuning, development and using SQL Server to provide appropriate business solutions. He is the founder and leader of the PASS SQL South West user group http://www.sqlsouthwest.co.uk , is a moderator at SQL Q + A forum ask.sqlservercentral.com and is on twitter at @fatherjack. He has spoken at SQLBits and SQL in the City, SQL Saturdays and local user groups across the UK and Europe.

Coping with infrastructure upgrades

Published 19 November 2012 9:00 am

A common topic for questions on SQL Server forums is how to plan and implement upgrades to SQL Server. Moving from old to new hardware or moving from one version of SQL Server to another. There are other circumstances where upgrades of other systems affect SQL Server DBAs.

For example, where I work at the moment there is an Microsoft Exchange (email) server upgrade in progress. It it being handled by a different team so I’m not wholly sure on the details but we are in a situation where there are currently 2 Exchange email servers – the old one and the new one. Users mail boxes are being transferred in a planned process but as we approach the old server being turned off we have to also make sure that our SQL Servers get updated to use the new SMTP server for all of the SQL Agent notifications, SSIS packages etc.

My servers have a number of profiles so that various jobs can send emails on behalf of various departments and different systems. This means there are lots of places that the old server name needs to be replaced by the new one.

Anyone who has set up DBMail and enjoyed the click-tastic odyssey of screens to create Profiles and Accounts and so on and so forth ought to seek some professional help in my opinion. It’s a nightmare of back and forth settings changes and it stinks. I wasn’t looking forward to heading into this mess of a UI and changing the old Exchange server name for the new one on all my SQL Instances for all of the accounts I have set up.

So I did what any Englishmen with a shed would do, I decided to take it apart and see if I can fix it another way. I took a guess that we are going to be working in MSDB and Books OnLine was remarkably helpful and amongst a lot of information told me about a couple of procedures that can be used to interrogate DBMail settings.

USE [msdb] -- It's where all the good stuff is kept
GO

EXEC dbo.sysmail_help_profile_sp; 

EXEC dbo.sysmail_help_account_sp;

Both of these procedures take optional parameters with the same name – ID and Name. If you provide an ID or a name then the results you get back are for that specific Profile or Account. Otherwise you get details of all Profiles and Accounts on the server you are connected to.

DBMailProfileAccount

As you can see (click for a bigger image), the Account has the SMTP server information in the servername column. We want to change that value to NewSMTP.Contoso.com.

Now it appears that the procedure we are looking at gets it’s data from the sysmail_account and sysmail_server tables, you can get the results the stored procedure provides if you run the code below.

SELECT  [account_id] ,
        [name] ,
        [description] ,
        [email_address] ,
        [display_name] ,
        [replyto_address] ,
        [last_mod_datetime] ,
        [last_mod_user]
FROM    dbo.sysmail_account AS sa;

SELECT  [account_id] ,
        [servertype] ,
        [servername] ,
        [port] ,
        [username] ,
        [credential_id] ,
        [use_default_credentials] ,
        [enable_ssl] ,
        [flags] ,
        [last_mod_datetime] ,
        [last_mod_user] ,
        [timeout]
FROM  dbo.sysmail_server AS sms

Now, we have no real idea how these tables are linked and whether making an update direct to one or other of them is going to do what we want or whether it will entirely cripple our ability to send email from SQL Server so we wont touch those tables with any UPDATE TSQL. So, back to Books OnLine then and we find sysmail_update_account_sp. It’s exactly what we need. The examples in BOL take the form (as below) of having every parameter explicitly defined.2012-11-18_1004

Not wanting to totally obliterate the existing values by not passing values in all of the parameters I set to writing some code to gather the existing data from the tables and re-write the SMTP server name and then execute the resulting TSQL.

IF OBJECT_ID('tempdb..#sysmailprofiles') IS NOT NULL 
    DROP TABLE #sysmailprofiles
 GO 

CREATE TABLE #sysmailprofiles
    (
      account_id INT ,
      [name] VARCHAR(50) ,
      [description] VARCHAR(500) ,
      email_address VARCHAR(500) ,
      display_name VARCHAR(500) ,
      replyto_address VARCHAR(500) ,
      servertype VARCHAR(10) ,
      servername VARCHAR(100) ,
      port INT ,
      username VARCHAR(100) ,
      use_default_credentials VARCHAR(1) ,
      ENABLE_ssl VARCHAR(1)
    )

INSERT  [#sysmailprofiles]
        ( [account_id] ,
          [name] ,
          [description] ,
          [email_address] ,
          [display_name] ,
          [replyto_address] ,
          [servertype] ,
          [servername] ,
          [port] ,
          [username] ,
          [use_default_credentials] ,
          [ENABLE_ssl]
        )
        EXEC [dbo].[sysmail_help_account_sp]

DECLARE @TSQL NVARCHAR(1000)

SELECT TOP 1
        @TSQL = 'EXEC [dbo].[sysmail_update_account_sp] @account_id = '
        + CAST([s].[account_id] AS VARCHAR(20)) + ', @account_name = '''
        + [s].[name] + '''' + ', @email_address = N''' + [s].[email_address]
        + '''' + ', @display_name = N''' + [s].[display_name] + ''''
        + ', @replyto_address = N''' + s.replyto_address + ''''
        + ', @description = N''' + [s].[description] + ''''
        + ', @mailserver_name = ''NEWSMTP.contoso.com'''
        + +', @mailserver_type = ' + [s].[servertype] + ', @port = '
        + CAST([s].[port] AS VARCHAR(20)) + ', @username = '
        + COALESCE([s].[username], '''''') + ', @use_default_credentials ='
        + CAST(s.[use_default_credentials] AS VARCHAR(1)) + ', @enable_ssl ='
        + [s].[ENABLE_ssl] 
FROM    [#sysmailprofiles] AS s
WHERE   [s].[servername] = 'SMTP.Contoso.com'

SELECT  @tsql

EXEC [sys].[sp_executesql] @tsql

This worked well for me and testing the email function EXEC dbo.sp_send_dbmail afterwards showed that the settings were indeed using our new Exchange server.

It was only later in writing this blog that I tried running the sysmail_update_account_sp procedure with only the SMTP server name parameter value specified. Despite what Books OnLine might intimate, you can do this and only the values for parameters specified get changed. If a parameter is not specified in the execution of the procedure then the values remain unchanged. This renders most of the above script unnecessary as I could have simply specified the account_id that I want to amend and the new value for the parameter I want to update.

EXEC sysmail_update_account_sp @account_id = 1, @mailserver_name = 'NEWSMTP.Contoso.com'

This wasn’t going to be the main reason for this post, it was meant to describe how to capture values from a stored procedure and use them in dynamic TSQL but instead we are here and (re)learning the fact that Books Online is a little flawed in places. It is a fantastic resource for anyone working with SQL Server but the reader must adopt an enquiring frame of mind and use a little curiosity to try simple variations on examples to fully understand the code you are working with. I think the author(s) of this part of Books OnLine missed an opportunity to include a third example that had fewer than all parameters specified to give a lead to this method existing.

2 Responses to “Coping with infrastructure upgrades”

  1. HildaJ says:

    I find this article very interesting. I’m a DBA and I consider myself a beginner ever though I have been around SQL Servers for several years, there’s so much to learn. Quick question. Could have you accomplish the same goal with an UPDATE statement or is that bad practice?

  2. darkhelmutis says:

    In theory, yes you could use an UPDATE statement. However as FJ indicated: you have no idea how this table is related to other tables. It is assumed since this is a stored procedure provided by MS that it takes this into account and is the safer method to use.

Leave a Reply