Archive

Archive for the ‘T-SQL’ Category

SQL Server Availability Groups and Automatic Seeding

June 20, 2018 Leave a comment

Microsoft released Availability Groups (AG) as a feature in SQL Server 2012. Prior to SQL Server 2016, there were two methods of adding a database to a new AG replica.

  1. You could provide the Add Database to Availability Group wizard a file share accessible by the primary and secondary replicas.  SQL Server would run FULL and LOG backups of each database to the share and use them to restore the database(s) to each replica.
  2. You could manually run a FULL and LOG backup of each database, copy the backup files to each replica, and restore the databases WITH NORECOVERY.

With SQL Server 2016. Microsoft has provided a third option, Automatic Seeding.  With Automatic Seeding, you specify the databases and the replicas and SQL Server will begin transferring data to each replica.  The duration of the seeding process depends on the size of the database and the network bandwidth available between primary and secondary replica.

There are a few considerations to keep in mind when considering Automatic Seeding.  Virtual log files in the primary database cannot be freed while the automatic seeding process is running.  For a large, active database this can lead to excessive log growth.  AU\utomatic seeding is a single-threaded process and is limited to 5 databases.  Finally, compression is disabled for Automatic Seeding by default, but can be enabled by turning on trace flag 9567.  This can improve performance of the seeding process but will increase CPU overhead.

The status of the Automatic Seeding process can be monitored using the following query: (download)


USE master;

SELECT local_database_name,
internal_state_desc,
remote_machine_name,
database_size_bytes / 1045876 AS [Database Size (mb)],
transferred_size_bytes / 1045876 AS [Transferred Size (mb)],
transfer_rate_bytes_per_second / 1045876 AS [Transfer Rate/Sec (mb)],
(database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second AS [Time Remaining (sec)],
CASE
WHEN ((database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second) < 360000
THEN '0'
ELSE ''
END
+ RTRIM(((database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second) / 3600)
+ ':' + RIGHT('0' + RTRIM(((database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second) % 3600 / 60), 2)
+ ':' + RIGHT('0' + RTRIM(((database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second) % 60), 2) AS [Time Remaining]
FROM sys.dm_hadr_physical_seeding_stats
WHERE internal_state_desc = 'ReadingAndSendingData'
ORDER BY remote_machine_name;

The query returns the following result set:


Note that one row will be returned per replica being seeded.

Automatic Seeding makes initializing AG replicas simple. I hope you can put the status query to good use.

Validate SQL Server Database Mail Settings

November 13, 2017 1 comment

In my last post, I shared a script to automate the migration of SQL Server Database Mail settings. In this post, I show how to send test e-mails from all Database Mail profiles on an instance. The migration I was working on contained 21 Database Mail profiles. The following script will send a test e-mail from each profile to confirm successful configuration. I hope you can put this code to use in your migrations.

USE msdb;

SET NOCOUNT ON;

/* Drop and create temp table
   and declare local variables */
DROP TABLE IF EXISTS #profiles;

CREATE TABLE #profiles
(RowId INT IDENTITY(1,1)
,ProfileName SYSNAME);

/* Update @recipient 
   Set @noexec = 1 to print the EXEC sp_send_dbmail statements
   Set @noexec = 0 to execute the statements immediately */
DECLARE @profilename SYSNAME,
@sqlstr NVARCHAR(2000),
@loopcount INT = 1,
@looplimit INT,
@noexec INT = 1,
@recipient NVARCHAR(255) = N'fgill@concurrency.com';

/* Insert Database Mail profile names into temp table
   and initialize the loop limit variable */
INSERT INTO #profiles
(ProfileName)
SELECT name
FROM sysmail_profile;

SELECT @looplimit = MAX(RowId) FROM #profiles;

/* Loop through the list of Database Mail profiles and 
   execute sp_send_dbmail for each one */
WHILE @loopcount <= @looplimit
BEGIN

	SELECT @profilename = ProfileName FROM #profiles WHERE RowId = @loopcount;


	SELECT @sqlstr = CONCAT('EXEC msdb.dbo.sp_send_dbmail  
		@profile_name = ''',
	@profilename,''',  
		@recipients = ''', @recipient, ''',  
		@body = ''Database mail succeeded for ', @profilename, ' on SQLWEBDB-01.'',  
		@subject = ''Database mail test for ', @profilename, ';''');

	SELECT @loopcount += 1;

	IF @noexec = 1
	BEGIN

		PRINT @sqlstr;

	END
	ELSE
	BEGIN

		EXEC sp_executesql @sqlstr;

	END
		
END

Migrating SQL Server Database Mail Settings

November 6, 2017 3 comments

This week, I was working on a migration for a client.  The migration was moving databases from a stand-alone instance to a two-node Availability Group.  When it came to moving the Database Mail settings, I discovered they had 21 sets of profiles and accounts.  Not wanting to manually create 42 Database Mail profiles, I set out to automate the process.  A web search yielded this blog post by Iain Elder. This script does what I was looking for, but would only generate settings for a single Database Mail profile.  Using Iain’s code as a starting point, I modified it to create Database Mail settings for all profiles on an instance.  The script is listed below. I hope this simplifies your SQL Server migrations.

USE msdb;
GO

/*
	Author -		Frank Gill, Concurrency
	Date -			2017-08-18
	Description -	Script queries sys tables to generate dynamic SQL statements
					to recreate database mail accounts and profiles
					Code modified to generate multiple mail profiles from the script found here:
					https://basetable.wordpress.com/2012/04/03/script-out-database-mail-settings/
	Note -			Execute query in SQL Server Management Studio and output Results to Text (Ctrl + T)
					Copy the results to a separate query window 
					For mail acounts with passwords, the password will appear as 'NotTheRealPassword'
					You will need to change this to the real password
					Each result set will contain a header row of Text
					Do a group replace (Ctrl + H) of Text to --Text to comment the headers out
					
*/

SET NOCOUNT ON;

/* Drop and create temp table 
   Declare local variables */
IF OBJECT_ID('tempdb.dbo.#sysmail_info') IS NOT NULL
BEGIN

	DROP TABLE #sysmail_info;

END

CREATE TABLE #sysmail_info
(RowId INT IDENTITY(1,1)
,account_name SYSNAME
,email_address NVARCHAR(120)
,display_name NVARCHAR(128)
,replyto_address NVARCHAR(128)
,[description] NVARCHAR(256)
,servername SYSNAME
,servertype SYSNAME
,[port] INT
,credential_identity NVARCHAR(4000)
,use_default_credentials BIT
,enable_ssl BIT
,profile_name SYSNAME
,sequence_number INT
,database_principal_name SYSNAME NULL
,is_default BIT);

DECLARE @loopcount INT = 1,
@looplimit INT,
@SQLText VARCHAR(MAX),
@ProfileText VARCHAR(MAX),
@CrLf CHAR(2) = CHAR(13) + CHAR(10);

/* Insert the required database mail info into the temp table */
INSERT INTO #sysmail_info
(account_name
,email_address
,display_name
,replyto_address
,[description]
,servername
,servertype
,[port]
,credential_identity
,use_default_credentials
,enable_ssl
,profile_name
,sequence_number
,database_principal_name
,is_default)
SELECT a.name AS account_name,
a.email_address,
a.display_name,
a.replyto_address,
a.[description],
servername,
servertype,
[port],
c.credential_identity,
s.use_default_credentials,
s.enable_ssl,
p.name AS profile_name,
pa.sequence_number,
dp.name AS database_principal_name,
pp.is_default
FROM msdb.dbo.sysmail_profile AS p
INNER JOIN msdb.dbo.sysmail_profileaccount AS pa ON
  p.profile_id = pa.profile_id
INNER JOIN msdb.dbo.sysmail_account AS a ON
  pa.account_id = a.account_id
LEFT OUTER JOIN msdb.dbo.sysmail_principalprofile AS pp ON
  p.profile_id = pp.profile_id
LEFT OUTER JOIN msdb.sys.database_principals AS dp ON
  pp.principal_sid = dp.sid
LEFT OUTER JOIN msdb.dbo.sysmail_server AS s ON
  a.account_id = s.account_id
LEFT OUTER JOIN sys.credentials AS c ON
  s.credential_id = c.credential_id;

/* Set loop limit to max RowId value */
SELECT @looplimit = MAX(RowId) FROM #sysmail_info;

/* Generate commands to enable database mail */
SELECT @SQLText = '
EXEC msdb.dbo.sp_configure
	@configname = ''show advanced options'',
	@configvalue = 1;
RECONFIGURE;

EXEC msdb.dbo.sp_configure
	@configname = ''Database Mail XPs'',
	@configvalue = 1;
RECONFIGURE;';

SELECT @SQLText AS [Text];

/* Loop through each row in the temp table 
   build commands to recreate database mail accounts and profiles */
WHILE @loopcount <= @looplimit
BEGIN

	SELECT @SQLText = '

	EXECUTE msdb.dbo.sysmail_add_profile_sp
	  @profile_name = ''' + profile_name + ''',
	  @description  = ''' + ISNULL([description],'') + ''';

	EXEC msdb.dbo.sysmail_add_account_sp
	  @account_name = ' + CASE WHEN account_name IS NULL THEN 'NULL' ELSE + '''' + account_name + '''' END + ',
	  @email_address = ' + CASE WHEN email_address IS NULL THEN 'NULL' ELSE + '''' + email_address + '''' END + ',
	  @display_name = ' + CASE WHEN display_name IS NULL THEN 'NULL' ELSE + '''' + display_name + '''' END + ',
	  @replyto_address = ' + CASE WHEN replyto_address IS NULL THEN 'NULL' ELSE + '''' + replyto_address + '''' END + ',
	  @description = ' + CASE WHEN [description] IS NULL THEN 'NULL' ELSE + '''' + [description] + '''' END + ',
	  @mailserver_name = ' + CASE WHEN servername IS NULL THEN 'NULL' ELSE + '''' + servername + '''' END + ',
	  @mailserver_type = ' + CASE WHEN servertype IS NULL THEN 'NULL' ELSE + '''' + servertype + '''' END + ',
	  @port = ' + CASE WHEN [port] IS NULL THEN 'NULL' ELSE + '''' + CONVERT(VARCHAR,[port]) + '''' END + ',
	  @username = ' + CASE WHEN credential_identity IS NULL THEN 'NULL' ELSE + '''' + credential_identity   + '''' END + ',
	  @password = ' + CASE WHEN credential_identity IS NULL THEN 'NULL' ELSE + '''NotTheRealPassword''' END + ',
	  @use_default_credentials = ' + CASE WHEN use_default_credentials = 1 THEN '1' ELSE '0' END + ',
	  @enable_ssl = ' + CASE WHEN enable_ssl = 1 THEN '1' ELSE '0' END + ';

	EXEC msdb.dbo.sysmail_add_profileaccount_sp
	  @profile_name = ''' + profile_name + ''',
	  @account_name = ''' + account_name + ''',
	  @sequence_number = ' + CAST(sequence_number AS NVARCHAR(3)) + ';
	' +
	  COALESCE('
	EXEC msdb.dbo.sysmail_add_principalprofile_sp
	  @profile_name = ''' + profile_name + ''',
	  @principal_name = ''' + database_principal_name + ''',
	  @is_default = ' + CAST(is_default AS NVARCHAR(1)) + ';
	', '')
	FROM #sysmail_info
	WHERE RowId = @loopcount;

	WITH R2(N) AS (SELECT 1 UNION ALL SELECT 1),
	R4(N) AS (SELECT 1 FROM R2 AS a CROSS JOIN R2 AS b),
	R8(N) AS (SELECT 1 FROM R4 AS a CROSS JOIN R4 AS b),
	R16(N) AS (SELECT 1 FROM R8 AS a CROSS JOIN R8 AS b),
	R32(N) AS (SELECT 1 FROM R16 AS a CROSS JOIN R16 AS b),
	R64(N) AS (SELECT 1 FROM R32 AS a CROSS JOIN R32 AS b),
	R128(N) AS (SELECT 1 FROM R64 AS a CROSS JOIN R64 AS b),
	Tally(N) AS (
	  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
	  FROM R128
	),
	SplitText(SplitIndex, [Text]) AS (
	  SELECT
		N,
		SUBSTRING(
		  @CrLf + @SQLText + @CrLf,
		  N + DATALENGTH(@CrLf),
		  CHARINDEX(
			@CrLf,
			@CrLf + @SQLText + @CrLf,
			N + DATALENGTH(@CrLf)
		  ) - N - DATALENGTH(@CrLf)
		)
	  FROM Tally
	  WHERE
		N < DATALENGTH(@CrLf + @SQLText) AND
		SUBSTRING(@CrLf + @SQLText + @CrLf, N, DATALENGTH(@CrLf)) = @CrLf
	)
	SELECT [Text]
	FROM SplitText
	ORDER BY SplitIndex;

	SELECT @loopcount += 1;

END