(This follows on from my previous script about emailing SQL Job ouput.)
There are times when a SQL server scheduled task needs to look at the output from the previous step, and make decisions based on that – particularly when parsing output from noisy/chatty scripts.
I have, therefore, put together a quick and dirty script to check the output from a SQL Server job step, and pull that into a string, so that it can be examined and a script can then make decisions about what to do next.
CREATE FUNCTION [dbo].[JobStepOutput]
(
@StartDate INT,
@StartTime INT,
@job_name sysname,
@step_id INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
/*
FN: JobStepOutput
Parameters:
@StartDate int - msdb-formatted date at which the job starts
@StartTime int - msdb-formatted time at which the job starts
@job_name sysname
@step_id int - step to look at. If zero, then all.
Usage:
To be used in a SQL Agent job step:
declare @StartDate int = CONVERT(int, $(ESCAPE_NONE(STRTDT)));
declare @StartTime int = CONVERT(int, $(ESCAPE_NONE(STRTTM)));
declare @job_name sysname = '$(ESCAPE_NONE(JOBNAME))';
declare @StepID int = CONVERT(int, $(ESCAPE_NONE(STEPID))) - 1;
select JobStepOutput(@StartDate, @StartTime, @job_name, @StepID);
*/
DECLARE @StartDateTime DATETIME = msdb.dbo.agent_datetime(@StartDate, @StartTime);
DECLARE @job_id UNIQUEIDENTIFIER =
(
SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @job_name
);
DECLARE @EndDateTime DATETIME =
(
SELECT ISNULL(MIN(msdb.dbo.agent_datetime(run_date, run_time)), GETDATE())
FROM msdb.dbo.sysjobhistory
WHERE msdb.dbo.agent_datetime(run_date, run_time) > @StartDateTime
AND job_id = @job_id
AND step_id = 0
);
DECLARE @results NVARCHAR(MAX) = N'';
SELECT @results = STUFF(
(
SELECT message
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory jh
ON j.job_id = jh.job_id
WHERE 1 = 1
AND msdb.dbo.agent_datetime(run_date, run_time) >= @StartDateTime
AND msdb.dbo.agent_datetime(run_date, run_time) < @EndDateTime
AND jh.job_id = @job_id
AND
(
(
jh.step_id = @step_id
AND @step_id >= 0
)
OR (@step_id = -1)
)
ORDER BY msdb.dbo.agent_datetime(run_date, run_time),
step_id
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'),
1 ,
0 ,
''
);
RETURN (@results);
END;
GO
Some notes:
I’ve shown STEPID - 1 in the example; this gives the previous job step.
If you pass in -1, it’ll return all the job step output for this job
I’ve tweaked the core sysjobhistory query from the previous post; this now looks for the end date of the job so you can more easily refer to previous job runs as well. Probably not needed, but came in handy while testing.
In case WordPress does something weird, it’s over on my github repo as well.
Postscript
While putting this script together, I noticed (in SQL2016) a function called “fn_sqlagent_jobs”, and hoped it might be useful. It didn’t appear to do anything, so wasn’t. And so I ended up writing the above.
I came across a need for SQL Server Agent job output to be emailed out to the developer who created the code that the job was running. For various reasons, they don’t necessarily have access to the SQL Server itself – hurrah – but need to see what’s being generated by their scripts in case something goes wrong.
So, I ended up writing this extremely rough and ready SP:
USE master;
SET QUOTED_IDENTIFIER ON;
IF OBJECT_ID('SendJobOutput', 'P') IS NULL
EXEC ('CREATE PROCEDURE SendJobOutput AS BEGIN SELECT 1 END');
GO
ALTER PROCEDURE SendJobOutput
@StartDate INT,
@StartTime INT,
@job_name sysname,
@recipients VARCHAR(MAX)
AS
BEGIN
/*
SP: SendJobOutput
Parameters:
@StartDate int - msdb-formatted date at which the job starts
@StartTime int - msdb-formatted time at which the job starts
@job_name sysname
@recipients varchar(max) - string full of semi-colon-separated email addresses to get the output message
Usage:
To be used in a SQL Agent job step:
declare @StartDate int = CONVERT(int, $(ESCAPE_NONE(STRTDT)));
declare @StartTime int = CONVERT(int, $(ESCAPE_NONE(STRTTM)));
declare @job_name sysname = '$(ESCAPE_NONE(JOBNAME))';
declare @recipients varchar(max) = 'user@address.bar; someoneelse@foo.com';
exec SendJobOutput @StartDate, @StartTime, @job_name, @recipients
*/
DECLARE @StartDateTime DATETIME = msdb.dbo.agent_datetime(@StartDate, @StartTime);
DECLARE @Subject sysname = N'Job output - ' + @job_name;
DECLARE @job_id UNIQUEIDENTIFIER =
(
SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @job_name
);
DECLARE @message NVARCHAR(MAX)
= N'<html><body><table><tr><th>JobName</th><th>DateTime</th><th>Step Name</th><th>Message</th></tr>';
DECLARE @results NVARCHAR(MAX) = N'';
SELECT @results = @results + STUFF(
(
SELECT j.name AS TD,
'',
msdb.dbo.agent_datetime(run_date, run_time) AS TD,
'',
jh.step_name AS TD,
'',
message AS TD
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory jh
ON j.job_id = jh.job_id
WHERE 1 = 1
AND msdb.dbo.agent_datetime(run_date, run_time) >= @StartDateTime
AND jh.job_id = @job_id
ORDER BY msdb.dbo.agent_datetime(run_date, run_time),
step_id
FOR XML PATH('TR'), ELEMENTS
),
1,
0,
''
);
SELECT @message = @message + @results + N'</table></body></html>';
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DoNotReply - SQLMail', -- name of email profile already defined in msdb.dbo.sysmail_profile
@recipients = @recipients,
@subject = @Subject,
@body_format = 'HTML',
@body = @message;
END;
At some point, I’ll revisit it to smarten up the email message being generated, but this at least is usable.
Instructions for use are in the header block, but, spoiler alert, it involves adding an extra step at the end of a job, and pasting in a few lines of script which makes use of the SQLAgent job step macros/tokens.
Note the hack to get multiple “TD” elements – if those extra ''s aren’t selected, the whole things gets blended into a single
which is not right.
You’ll need to configure the email profile being used.
And, in case WordPress corrupts the script above (as if…), it’s over on my github page too as “SendJobOutputByEmail.sql”
You know how it is. You need to wait for a job to complete before moving onto the next one… And, just sometimes, you need to do the same with SQL Server.
MSDB
The msdb system database contains various tables for dealing with the SQL Agent and the job scheduler. The ones of particular interest for this problem are:
syssessions
The syssessions table contains a list of the SQLAgent sessions. Unfortunately, there’s no absolute guarantee that the session_id is in ascending order, so we need to pull the top value when sorted by agent_start_date in descending order.
sysjobs
The sysjobs table contains information about each scheduled (and unscheduled) job. We’re only interested in the job_id and job_name fields at the moment.
sysjobactivity
The sysjobactivity table contains data about what jobs are running in the current session and, indeed, can be used as a snapshot of what jobs were running when a previous SQLAgent instance was ended.
Final code
Here follows a quick & dirty query to watch for one job to finish, and then to start the next job:
USE msdb;
DECLARE @JobToWaitFor sysname = 'First Job';
DECLARE @JobToLaunchNext sysname = 'Second Job';
WHILE EXISTS
(
SELECT j.name,
ja.*
FROM sysjobs j
INNER JOIN sysjobactivity ja
ON ja.job_id = j.job_id
WHERE ja.session_id =
(
-- can't use max session_id, as not guaranteed to be the most recent agent start date
SELECT TOP 1 session_id FROM syssessions ORDER BY agent_start_date DESC
)
AND ja.start_execution_date IS NOT NULL
AND ja.stop_execution_date IS NULL
AND j.name = @JobToWaitFor
)
BEGIN
-- delay loop here
WAITFOR DELAY '00:00:10'; -- tweak this as appropriate to your job length, or desired delay between job finish/start
END;
EXEC sp_start_job @job_name = @JobToLaunchNext;
Yes, there are more elegant / succinct ways to do this – you could achieve similar results by using sp_help_job for example – but using script snippets like this can allow a more complex job workflow than merely “start this job, run this script” – in the past, I’ve written similar code to launch several jobs to run in parallel and then wait for them all to finish before moving onto the next step.
You need to change your password, so you use Ctrl+ALT+Delete. That’s fine, as long as you’re physically at the machine.
But if you’re connected to a remote desktop session somewhere, that key combo doesn’t get through. But you can send Ctrl+ALT+Insert to do the same thing.
What if you’re on a remote server and connected through that to another remote server (with a different set of credentials) and you need to send Ctrl+ALT+Del to that? Well, Ctrl+Alt+Del will do your local machine, Ctrl+ALT+Insert will do the top level Virtual machine… Today I learned about Ctrl+ALT+End – that’ll do the one that you’re actually looking at. Hurrah!
Ctrl+Alt+End. I know all this VDI-inside-a-VDI is all a bit Inception-y, but it does the job. Now, if only I could figure out the password complexity rules…
I needed a script to create logins on a second server to match the primary. There are reasons why using, say, the powershell dba tool set isn’t an option (eg the server isn’t built, or the two can’t communicate, or your security team doesn’t allow for the use of this sort of thing), so let’s have a look at what we can do just in T-SQL.
There are three things to look at:
Creating Logins from Windows accounts
Creating logins that are authenticated by SQL Server
Assigning membership of the appropriate server roles
Note that we’re just creating a snapshot of what’s there – we’re not aiming to keep these things in true synchronisation between servers. Note also that this script will only create accounts that don’t exist. If there’s an account already there with the same name, the script generated will not recreate it with new options.
There are a couple of ways of getting at login information:
sys.syslogins
This is the old way. Try not to use it any more. The sys.syslogins documentation says it will be removed in a future version of Microsoft SQL Server.
sys.server_principals and sys.sql_logins
These are the new(ish) DMVs to use – sys.server_principals contains the bulk of the information needed, and sys.sql_logins one contains a few extra bits that are specific only to SQL Server authentication (passwords, policy flags, etc), but the bulk is inherited from sys.server_principals
Scripting Windows Login Accounts
This is probably the easiest script to write, as it just references sys.server_principals. I’ve written a simple “IF NOT EXISTS” wrapper around the whole thing, to make sure we don’t try to create an account that’s already there.
SELECT N'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(name, '''')
+ N') BEGIN CREATE LOGIN ' + QUOTENAME(name) + N' FROM WINDOWS WITH DEFAULT_LANGUAGE = '
+ default_language_name + N', DEFAULT_DATABASE = ' + QUOTENAME(default_database_name) + N' END;' AS SQLQuery
FROM sys.server_principals
WHERE
type IN ('U', 'G')
AND name NOT LIKE 'NT %';
Note that we’re excluding the NT service accounts / default accounts, as there’s no point in generating those, as they’ll already be there on the target anyway.
Scripting SQL Authenticated Accounts
Really? We still have these? Unfortunately, yes – and I can’t see them going away any time soon, unfortunately. So, here we are.
As above, I’m generating the “IF NOT EXISTS” wrapper. This time, we’re querying sys.sql_logins, as it contains only those server principals, and the extra (password / policy) information required by these accounts.
SELECT N'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(sl.name, '''')
+ N') BEGIN CREATE LOGIN ' + QUOTENAME(sl.name) + N' WITH PASSWORD = '
+ CAST(CONVERT(VARCHAR(256), CAST(LOGINPROPERTY(sl.name, 'PasswordHash') AS VARBINARY(256)), 1) AS NVARCHAR(MAX))
+ ' HASHED' + N', SID=' + CONVERT(VARCHAR(256), sl.sid, 1) + N', DEFAULT_LANGUAGE = '
+ sl.default_language_name + N', DEFAULT_DATABASE = ' + QUOTENAME(sl.default_database_name)
+ N', CHECK_EXPIRATION = ' + CASE sl.is_expiration_checked
WHEN 1 THEN
N'ON'
ELSE
N'OFF'
END
+ N', CHECK_POLICY = ' + CASE sl.is_policy_checked
WHEN 1 THEN
N'ON'
ELSE
N'OFF'
END
+ N'; END;' AS SQLQuery
FROM sys.sql_logins sl
WHERE
sl.name NOT LIKE '##%'
AND sl.name NOT LIKE 'sa';
Server Role Memberships
This is the final step – assigning role memberships. For this, we need to use the sys.server_role_members DMV as well as sys.server_principals (twice, once for the role name, once for the member name). That’s the big CTE before the main SELECT statement.
WITH
RoleMembers AS
(
SELECT srm.role_principal_id,
srm.member_principal_id,
r.name AS RoleName,
m.name AS MemberName
FROM
sys.server_role_members srm
INNER JOIN sys.server_principals r
ON srm.role_principal_id = r.principal_id
INNER JOIN sys.server_principals m
ON srm.member_principal_id = m.principal_id
)
SELECT N'IF EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(MemberName, '''')
+ N') BEGIN ALTER SERVER ROLE ' + RoleName + N' ADD MEMBER ' + +QUOTENAME(MemberName) + N'; END;' AS SQLQuery
FROM RoleMembers
WHERE
RoleMembers.member_principal_id IN
(
SELECT principal_id
FROM sys.server_principals
WHERE
type IN ('U', 'S')
AND name NOT LIKE 'NT %'
AND name NOT LIKE 'sa'
AND name NOT LIKE '##%'
);
Wrapping this up
I’ve put all this functionality into a single view that can be queried to return the statements in the order above – logins before roles – so that it can be easily used to generate everything you need. The View definition script is below. Note the extra field (“Ord”) which we won’t query later, but will use to sort the results.
USE [master];
GO
/****** Object: View [dbo].[ExportLoginQueries] Script Date: 24/03/2020 16:00:58 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
IF OBJECT_ID('dbo.ExportLoginQueries', 'V') IS NULL
BEGIN
EXEC ('CREATE VIEW dbo.ExportLoginQueries AS SELECT 1 AS [one]');
END;
GO
ALTER VIEW [dbo].[ExportLoginQueries]
AS
WITH
RoleMembers AS
(
SELECT srm.*,
r.name AS RoleName,
m.name AS MemberName
FROM
sys.server_role_members srm
INNER JOIN sys.server_principals r
ON srm.role_principal_id = r.principal_id
INNER JOIN sys.server_principals m
ON srm.member_principal_id = m.principal_id
)
SELECT 3 AS Ord,
N'IF EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(MemberName, '''')
+ N') BEGIN ALTER SERVER ROLE ' + RoleName + N' ADD MEMBER ' + +QUOTENAME(MemberName) + N'; END;' AS SQLQuery
FROM RoleMembers
WHERE
RoleMembers.member_principal_id IN
(
SELECT principal_id
FROM sys.server_principals
WHERE
type IN ('U', 'S')
AND name NOT LIKE 'NT %'
AND name NOT LIKE 'sa'
AND name NOT LIKE '##%'
)
UNION ALL
SELECT 1 AS Ord,
N'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(name, '''')
+ N') BEGIN CREATE LOGIN ' + QUOTENAME(name) + N' FROM WINDOWS WITH DEFAULT_LANGUAGE = '
+ default_language_name + N', DEFAULT_DATABASE = ' + QUOTENAME(default_database_name) + N' END;' AS SQLQuery
FROM sys.server_principals
WHERE
type = 'U'
AND name NOT LIKE 'NT %'
UNION ALL
SELECT 2 AS Ord,
N'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + QUOTENAME(sl.name, '''')
+ N') BEGIN CREATE LOGIN ' + QUOTENAME(sl.name) + N' WITH PASSWORD = '
+ CAST(CONVERT(VARCHAR(256), CAST(LOGINPROPERTY(sl.name, 'PasswordHash') AS VARBINARY(256)), 1) AS NVARCHAR(MAX))
+ ' HASHED' + N', SID=' + CONVERT(VARCHAR(256), sl.sid, 1) + N', DEFAULT_LANGUAGE = '
+ sl.default_language_name + N', DEFAULT_DATABASE = ' + QUOTENAME(sl.default_database_name)
+ N', CHECK_EXPIRATION = ' + CASE sl.is_expiration_checked
WHEN 1 THEN
N'ON'
ELSE
N'OFF'
END
+ N', CHECK_POLICY = ' + CASE sl.is_policy_checked
WHEN 1 THEN
N'ON'
ELSE
N'OFF'
END
+ N'; END;' AS SQLQuery
FROM
sys.sql_logins sl
WHERE
sl.name NOT LIKE '##%'
AND sl.name <> 'sa';
GO
All that needs to be done now is to write something to extract this as & when required. For example, using bcp:
bcp "SELECT SQLQuery FROM dbo.ExportLoginQueries ORDER BY Ord, SQLQuery" queryout "<<PathToBackupDirectory>>\ExportedSQLLogins.sql" -t -c -S <<servername>> -T
A SQL Server administrator has left. But what does that actually mean? What do we need to check to make sure that things will continue to work after their Active Directory account is disabled?
Some things to check are:
SQL Database items
Database ownership
SQL Scheduled Task ownership
Server role membership
Database role membership
object ownership
Windows items
Windows Scheduled Tasks
Ownership / login ID for Windows Services
Local Group Membership
File Share Ownership
A few things to note:
This collection of script merely identifies issues
Don’t blindly implement fixes without proper testing and without a rollback – you may run into trouble
This collection is not complete
SQL Server
Database ownership
Ideally, databases would be owned by some low-level non-sa account which is disabled, and solely exists to avoid these problems. This is not an ideal world, so we need to check for databases owned by our departing administrator:
DECLARE @LeaverPrincipalName sysname = 'YourCorp\Hugh.Splitter';
SELECT name,
state_desc
FROM sys.databases
WHERE owner_sid =
(
SELECT TOP (1) sid FROM sys.server_principals WHERE name = @LeaverPrincipalName ORDER BY name
);
Transferring database ownership should be relatively straight-forward and risk-free. Except there are certain applications that depend on the database being owned by a sysadmin, or by a particular account (sa), or by their own service account, rather than checking for membership of the db_owner role… So make sure you test your changes. You’ll need to use the ALTER AUTHORIZATION statement, as follows:
ALTER AUTHORIZATION ON DATABASE::<<DBName>> TO <<NewUserName>>;
SQL Scheduled Task Ownership
When a SQL Server agent job runs a T-SQL job step, that step is executed as the owner, rather than as the SQL Agent account or the sa account. From the documentation linked just above:
Transact-SQL job steps use the EXECUTE AS command to set the security context to the owner of the Job.
So, we need to make sure that our leaver’s account isn’t the owner of any SQL Agent jobs:
SELECT name
FROM msdb.dbo.sysjobs
WHERE owner_sid =
(
SELECT TOP (1) sid FROM sys.server_principals WHERE name = @LeaverPrincipalName ORDER BY name
);
Which is all well and good, except there is the possibility for it to be set to use a proxy account, so we’ll need a more complex query:
SELECT JobName = j.name,
js.step_id,
js.step_name,
JobOwner = SUSER_SNAME(owner_sid),
ProxyName = p.name
FROM
msdb.dbo.sysjobsteps js
INNER JOIN msdb.dbo.sysjobs j
ON js.job_id = j.job_id
LEFT OUTER JOIN msdb.dbo.sysproxies AS p
ON js.proxy_id = p.proxy_id
WHERE
SUSER_SNAME(owner_sid) = @LeaverPrincipalName
OR p.name = @LeaverPrincipalName
ORDER BY
j.name,
step_id;
What server roles did the user have? This might be helpful when replicating the user’s permissions when transferring job roles to another user – except this should all be done through active directory group membership, amirite?
Assuming, though, that you’re working somewhere where granting permissions to individuals is the done thing, then you’ll want to check the results of the following:
SELECT sprole.name AS [SQL Server Role],
spmember.name AS [Member Name]
FROM
sys.server_role_members srm
INNER JOIN sys.server_principals sprole
ON sprole.principal_id = srm.role_principal_id
INNER JOIN sys.server_principals spmember
ON spmember.principal_id = srm.member_principal_id
WHERE spmember.name = @LeaverPrincipalName;
Database Role Membership
Similarly, you may need/want to check for membership of database roles across all databases on your server:
IF OBJECT_ID('tempdb.dbo.#DBRoleMembers', 'U') IS NOT NULL
BEGIN
DROP TABLE #DBRoleMembers;
END;
CREATE TABLE #DBRoleMembers (DB sysname, DBRoleName sysname, MemberName sysname);
SELECT @sql
= N'
USE [?];
INSERT INTO #DBRoleMembers
SELECT DB_NAME(),
dprole.name,
dpmember.name
FROM sys.database_role_members drm
inner join sys.database_principals dprole
ON dprole.principal_id = drm.role_principal_id
INNER JOIN sys.database_principals dpmember
ON dpmember.principal_id = drm.member_principal_id
WHERE dpmember.name = ' + QUOTENAME(@LeaverPrincipalName, '''');
DECLARE @sqlouter NVARCHAR(MAX) = N'master.sys.sp_msforeachdb @command1';
DECLARE @sqlparams NVARCHAR(MAX) = N'@command1 nvarchar(max)';
EXEC sp_executesql @sqlouter, @sqlparams, @sql;
SELECT * FROM #DBRoleMembers ORDER BY DB, DBRoleName, MemberName;
Object Ownership
It might be worth checking for ownership of individual objects within a database, as you won’t be able to delete the user from the database until ownership of objects has been transferred to another user.
SELECT @sql
= N'
CREATE TABLE #ownedobjects (DBName sysname, ObjName sysname, ObjType VARCHAR(60));
exec sp_msforeachdb ''INSERT INTO #ownedobjects SELECT ''''?'''', name, type_desc FROM [?].[sys].[objects] WHERE principal_id = (SELECT sid FROM sys.server_principals WHERE name = '''
+ QUOTENAME(@LeaverPrincipalName, '''') + N''')''
select * from #ownedobjects';
--SELECT @sql;
EXEC (@sql);
As with databases, use the ALTER AUTHORIZATION statement to fix this.
Windows
For the Windows-based things, we’re going to drop into PowerShell
Windows Scheduled Tasks
Windows Scheduled tasks will fail when the owner account is disabled / deleted. Or, indeed, the password is changed or expired – but you don’t run jobs as a user account, do you? Oh, you do?
$username = 'YourCorp\Hugh.Splitter'
Write-Host "Checking ownership of Windows Scheduled Tasks"
get-scheduledtask | where-object {$_.Author -eq $username}
Oops. You might want to check those.
Windows Service Login
And, of course, you don’t use user accounts to run windows services, do you? Let’s check.
Write-Host "Checking Login for Windows Services"
get-wmiobject win32_service | where-object {$_.StartName -eq $username }
Local Group Membership
It might also be worth checking to see which groups the user is in on a particular server (we’ll leave the active directory stuff to the AD team)
Write-Host "Checking Local Group Membership"
get-localgroup | foreach-object -process {
if ( $(get-localgroupmember $_ | where-object {$_.name -eq $username} ) -ne $null )
{
write-host "User $username is a member of $_.name group"
}
}
File Share Ownership
What about file shares? Does your leaver own any of those? I’m not sure if it makes a difference, but it’s worth identifying these in case anyone needs to change the permissions later…
Just to unpick this one a little – the first filter is to remove items that don’t have a path, such as the IPC$ share; and the Convert-Path is there to transform the PSPath value of, say, Microsoft.PowerShell.Core\FileSystem::D:\Backups to a more normal-looking D:\Backups
Still to do
I should probably think about file ownership as well – in case anything depends on that.
And maybe running this all into a single script that can run against all servers / a list of servers
The big question, though – what else needs to be checked?
Finally
Do you have the passwords / know where the passwords are stored and have access to that system?
This is a checklist put together and followed for an upgrade of a pair of physical SQL Server 2012 servers which hosted a single availability group of several terabytes of data with minimal downtime.
The availability group was configured with synchronous commit and automatic failover.
List of Steps
If possible, stop any ETL processes that put data into the affected databases – less traffic here means less traffic to catch up with when you resume the AG HADR.
Make sure all AGs hosted by the servers are on the same server
Put servers, clusters, listeners, everything into Maintenance Mode in SCOM / SQLMonitor / Solarwinds / whatever other monitoring system you use.
Disable Transaction Log backup jobs on all servers hosting the Availability Group(s)
Take Log backups
Configure the Availability Group(s) to manual failover
Suspend data movement for each database on the secondary server
Backup the system databases on the secondary server
Upgrade the secondary server and reboot if required (it might not be required, but it is recommended to do this anyway)
Resume data movement on the secondary, and allow the servers to synchronise.
Failover the availability group(s) to the upgraded server with no data loss
Check that data movement is suspended to the new secondary / unupgraded server due to version incompatibility
Upgrade other server and then reboot
Resume data movement on secondary, and allow to synchronise
(optional) failover AG with no data loss to test all is OK (this isn’t really required, but can be useful for extra peace of mind)
Reconfigure availability group(s) back to automatic failover (if that’s how it was)
Re-enable transaction log backup jobs
Take servers out of maintenance mode in SCOM/SQLMonitor/Solarwinds/Whatever
A couple of useful SQL queries
Suspend / Resume HADR for all databases on the server
This is the suspend version – change suspend to resume and rerun later
DECLARE @sql VARCHAR(MAX) = '';
SELECT @sql = @sql + 'ALTER DATABASE ' + QUOTENAME(DB_NAME(hars.database_id)) + ' SET HADR SUSPEND;' + CHAR(10) + CHAR(13)
FROM sys.dm_hadr_database_replica_states AS hars
INNER JOIN sys.dm_hadr_availability_group_states AS hags
ON hags.group_id = hars.group_id
WHERE hars.is_local = 1
ORDER BY DB_NAME(hars.database_id);
PRINT @sql; -- optional
EXEC (@sql);
Check health of replicas in the AG (run on primary)
SELECT ag.name,
ar.replica_server_name,
hars.is_local,
hars.role_desc,
hars.operational_state_desc,
hars.connected_state_desc,
hars.recovery_health_desc,
hars.synchronization_health_desc,
hars.last_connect_error_number,
hars.last_connect_error_description,
hars.last_connect_error_timestamp,
ag.failure_condition_level,
ag.health_check_timeout,
ar.replica_server_name,
ar.owner_sid,
ar.endpoint_url,
ar.availability_mode_desc,
ar.failover_mode_desc,
ar.session_timeout,
ar.primary_role_allow_connections_desc,
ar.secondary_role_allow_connections_desc,
ar.backup_priority,
ag.automated_backup_preference_desc,
ar.read_only_routing_url
FROM sys.dm_hadr_availability_replica_states AS hars
INNER JOIN sys.availability_groups AS ag
ON ag.group_id = hars.group_id
INNER JOIN sys.availability_replicas AS ar
ON ar.group_id = ag.group_id
AND ar.replica_id = hars.replica_id;
Monitor progress of catchup/synchronisation (run on primary)
SELECT ag.name,
adc.database_name,
ar.replica_server_name,
hdrs.is_local,
hdrs.synchronization_state,
hdrs.synchronization_state_desc,
hdrs.is_commit_participant,
hdrs.synchronization_health_desc,
hdrs.database_state_desc,
hdrs.is_suspended,
hdrs.suspend_reason,
hdrs.suspend_reason_desc,
hdrs.last_sent_time,
hdrs.last_received_time,
hdrs.last_hardened_time,
hdrs.last_redone_time,
hdrs.log_send_queue_size,
hdrs.log_send_rate,
hdrs.redo_queue_size,
hdrs.redo_rate,
hdrs.filestream_send_rate,
CEILING(hdrs.redo_queue_size / hdrs.redo_rate) / 60.0 AS est_redo_completion_minutes, -- approximately
hdrs.last_commit_time,
hdrs.low_water_mark_for_ghosts,
hdrs.secondary_lag_seconds -- for SQL 2016 and above
FROM sys.dm_hadr_database_replica_states AS hdrs
INNER JOIN sys.availability_groups AS ag
ON ag.group_id = hdrs.group_id
INNER JOIN sys.availability_databases_cluster AS adc
ON adc.group_database_id = hdrs.group_database_id
INNER JOIN sys.availability_replicas AS ar
ON ar.replica_id = hdrs.replica_id
WHERE hdrs.log_send_queue_size <> 0
OR hdrs.redo_queue_size <> 0;
You can’t run SQL Server Configuration Manager on a Windows Core server – no UI.
You can’t run SQL Server Configuration Manager on another server and direct it to point to your server running Windows Core.
But what you can do is run Computer Management from your full-fat server, and connect that to your Core server to do stuff there. And one of the things you can do is run the SQL configuration tools…
How has it taken me so long to find that out? Oh yes – I hardly ever *see* a Windows server core edition installation. Probably in part because of this sort of thing. Heigh ho.
I was thinking about postcode data, as it might come in handy for some reporting / planning / analysis work later.
There’s a set of scripts to create tables and populate with postcode data in SQL Server, which was my starting point.
A note for those from outside the UK – the UK postcode system breaks down into a few letters and numbers, generally of the form AAXX XAA (where A is a letter, and X is a digit). The first part is known as the outbound code – the letters are (generally) related to a major city in the area – NE for Newcastle, LS for Leeds, OX for Oxford, for examples); the one or two digits that follow represent an area in that town or, in more rural areas, this could be a “post town” – for example the IP postcode area. Here, the IP represents Ipswich, in Suffolk, but the whole postcode area covers most of Suffolk as well as some parts of Norfolk…
The code for that is relatively straightforward:
DECLARE @Line VARCHAR(MAX);
SELECT @Line = STUFF(
(
SELECT ', ' + CAST(lng AS VARCHAR(20)) + ' ' + CAST(lat AS VARCHAR(20))
FROM dbo.outcodepostcodes
WHERE outcode LIKE 'IP%' and lat <> 0
ORDER BY CAST(STUFF(outcode, 1, 2, '') AS INT)
FOR XML PATH('')
),
1,
2,
''
);
SELECT geography::Parse('LINESTRING(' + @line + ')');
Something to note – the lat <> 0 bit – The UK has “non-geographic” postcodes as well as the normal ones. These, obviously, have no positional information, and, in this data source, that’s represented by 0 values in the lat and lng fields.
Also, I’ve switched to the Mercator projection. If you use the default (equirectangular) projection option, it’s going to look wrong unless the data you’re mapping is very close to the equator.
Now, knowing most of the above, I wondered what it would look like if you drew a line from the center of each postcode outbound code to the next one for each post town. In numerical order. It turns out that the IP code looks like this:
A bit of a drunkard’s walk… and the other areas are similar, and don’t really look much like anything. Until you come to put them all on one result set:
Looks familiar?
UK Postcode Map Query Code
You want the code for this, because of course you do…
DECLARE @outcodemap TABLE (outcode VARCHAR(9), map VARCHAR(MAX));
DECLARE @oc AS VARCHAR(9);
DECLARE @map VARCHAR(MAX);
DECLARE o CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT DISTINCT
LEFT(outcode, CASE WHEN outcode LIKE '[A-Z][0-9]%' THEN 1 ELSE 2 end)
FROM dbo.outcodepostcodes
WHERE
lat <> 0
AND
(
outcode LIKE '[A-Z][A-Z][0-9]'
OR outcode LIKE '[A-Z][A-Z][0-9][0-9]'
OR outcode LIKE '[A-Z][0-9]'
OR outcode LIKE '[A-Z][0-9][0-9]'
);
OPEN o;
FETCH NEXT FROM o
INTO @oc;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @map = STUFF(
(
SELECT ', ' + CAST(lng AS VARCHAR(20)) + ' ' + CAST(lat AS VARCHAR(20))
FROM dbo.outcodepostcodes
WHERE
lat <> 0
AND (outcode LIKE @oc + '[0-9]' OR outcode LIKE @oc + '[0-9][0-9]')
ORDER BY CASE
WHEN outcode LIKE '[A-Z][0-9]%' THEN
CAST(STUFF(outcode, 1, 1, '') AS INT)
ELSE
CAST(STUFF(outcode, 1, 2, '') AS INT)
END
FOR XML PATH('')
),
1,
2,
''
);
INSERT INTO @outcodemap (outcode, map) VALUES ( @oc, @map );
FETCH NEXT FROM o
INTO @oc;
END;
CLOSE o;
DEALLOCATE o;
SELECT outcode,
CASE
WHEN CHARINDEX(',', map) > 0 THEN
geography::Parse('LINESTRING(' + map + ')')
WHEN DATALENGTH(TRIM(map)) > 1 THEN
geography::Parse('POINT(' + map + ')') -- just in case...
END
FROM @outcodemap;
Again, there are a couple of things to note – the code could probably do with being tidied up, and I’m ignoring those odd postcodes which have AAXA or AAXXA as the outbound. Yeah, the UK postcode system is kinda messed up in places!
After the fun of the last few days, I thought it might be a better idea if I wrapped this all up into a single stored procedure. So I did that.
Parameters
I have implemented the following parameters:
@ShowCPUAsLines (BIT, default 1) – show line graphs rather than polygons. Set to 0 if you want polygons
@ShowDeadlock (BIT, default 1) – show Deadlock information. Set to 0 to suppress
@DeadlockBlobRadius (TINYINT, default 1) – radius of deadlock circles on the graph. If zero, then will be displayed as a point rather than as a circle, but that’s pretty much unreadable…
@OutputDeadlockTable (BIT, default 1) – outputs the deadlock information table used internally. Note that this will return information about all the deadlocks that have been identified from the logs, not just the ones shown
@ShowEndBar (BIT, default 1) – if you don’t like that “Here’s where we are now” bar, set to zero
@EndBarWidth (TINYINT, default 1) – width of that now / end bar. If zero, then a line is drawn rather than a bar
The code
IF OBJECT_ID('dbo.PerformanceGraph', 'P') IS NULL
BEGIN
EXEC ('CREATE PROCEDURE dbo.PerformanceGraph AS BEGIN SELECT 1 END');
END;
GO
ALTER PROCEDURE PerformanceGraph
@ShowCPUAsLines BIT = 1,
@ShowDeadlock BIT = 1,
@DeadlockBlobRadius TINYINT = 1,
@OutputDeadlockTable BIT = 1,
@ShowEndBar BIT = 1,
@EndBarWidth TINYINT = 1
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ts_now BIGINT =
(
SELECT TOP (1)
cpu_ticks / (cpu_ticks / ms_ticks)
FROM sys.dm_os_sys_info WITH (NOLOCK)
ORDER BY cpu_ticks DESC
);
DECLARE @CPUSQLOut AS VARCHAR(8000),
@CPUIdleOut AS VARCHAR(8000),
@CPUOtherOut AS VARCHAR(8000);
DECLARE @CPURecords AS INT;
DECLARE @DeadlockPoints AS VARCHAR(8000),
@DeadlockBlobs AS VARCHAR(8000),
@DeadlockRecords AS INT;
DECLARE @OutputGeometry TABLE (ShapeInfo GEOMETRY, Caption VARCHAR(20));
WITH
CPULoad AS
(
SELECT TOP (256)
y.SQLProcessUtilization AS [SQL Server Process CPU Utilization],
y.SystemIdle AS [System Idle Process],
100 - (y.SystemIdle + y.SQLProcessUtilization) AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - y.timestamp), GETDATE()) AS [Event Time]
FROM
(
SELECT x.record.value('(./Record/@id)[1]', 'int') AS record_id,
x.record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,
x.record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,
x.timestamp
FROM
(
SELECT timestamp,
CONVERT(XML, record) AS record
FROM sys.dm_os_ring_buffers WITH (NOLOCK)
WHERE
ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE N'%<SystemHealth>%'
) AS x
) AS y
ORDER BY y.timestamp DESC
)
SELECT @CPUSQLOut
= STUFF(
(
SELECT ','
+ CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time]) - COUNT(*) OVER (PARTITION BY 1) AS VARCHAR(10))
+ ' ' + CAST(CPULoad.[SQL Server Process CPU Utilization] AS VARCHAR(20))
FROM CPULoad
ORDER BY CPULoad.[Event Time]
FOR XML PATH('')
),
1,
1,
''
),
@CPUIdleOut
= STUFF(
(
SELECT ','
+ CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time]) - COUNT(*) OVER (PARTITION BY 1) AS VARCHAR(10))
+ ' ' + CAST(CPULoad.[System Idle Process] AS VARCHAR(20))
FROM CPULoad
ORDER BY CPULoad.[Event Time]
FOR XML PATH('')
),
1,
1,
''
),
@CPUOtherOut
= STUFF(
(
SELECT ','
+ CAST(ROW_NUMBER() OVER (ORDER BY CPULoad.[Event Time])
- COUNT(*) OVER (PARTITION BY 1) AS VARCHAR(10)) + ' '
+ CAST(CPULoad.[Other Process CPU Utilization] AS VARCHAR(20))
FROM CPULoad
ORDER BY CPULoad.[Event Time]
FOR XML PATH('')
),
1,
1,
''
),
@CPURecords = (SELECT COUNT(*) FROM CPULoad);
IF @ShowCPUAsLines = 1
BEGIN
INSERT INTO @OutputGeometry (ShapeInfo, Caption)
VALUES
(geometry::Parse('LINESTRING(' + @CPUSQLOut + ')'), 'CPU-SQL'),
(geometry::Parse('LINESTRING(' + @CPUOtherOut + ')'), 'CPU-Other'),
(geometry::Parse('LINESTRING(' + @CPUIdleOut + ')'), 'CPU-Idle');
END;
ELSE
BEGIN
INSERT INTO @OutputGeometry (ShapeInfo, Caption)
VALUES
(geometry::STGeomFromText(
'POLYGON((' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0, ' + @CPUSQLOut + ', '
+ '0 0, ' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0 ))',
0
), 'CPU-SQL'),
(geometry::STGeomFromText(
'POLYGON((' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0, ' + @CPUOtherOut + ', '
+ '0 0, ' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0 ))',
0
), 'CPU-Other'),
(geometry::STGeomFromText(
'POLYGON((' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0, ' + @CPUIdleOut + ', '
+ '0 0, ' + CAST(0 - @CPURecords AS CHAR(5)) + ' 0 ))',
0
), 'CPU-Idle');
END;
DECLARE @XMLDeadlocks TABLE ([XML] XML, UTCTime DATETIME, LocalTime DATETIME, MinutesAgo INT);
IF @ShowDeadlock = 1
BEGIN;
WITH
xmlsource AS
(
SELECT CONVERT(XML, event_data) AS deadlock_xml
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
),
xmldates AS
(
SELECT xmlsource.deadlock_xml,
xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime') AS deadlock_UTCtime, -- this is UTC.
DATEADD(
mi,
DATEDIFF(mi, GETUTCDATE(), GETDATE()),
xmlsource.deadlock_xml.value('(/event/@timestamp)[1]', 'datetime')
) AS deadlock_LocalTime
FROM xmlsource
WHERE xmlsource.deadlock_xml.value('(/event/@name)[1]', 'VARCHAR(256)') = 'xml_deadlock_report'
)
INSERT INTO @XMLDeadlocks ([XML], UTCTime, LocalTime, MinutesAgo)
SELECT xmldates.deadlock_xml,
xmldates.deadlock_UTCtime,
xmldates.deadlock_LocalTime,
DATEDIFF(MINUTE, xmldates.deadlock_LocalTime, GETDATE()) AS MinutesAgo
FROM xmldates;
SELECT @DeadlockPoints = STUFF(
(
SELECT DISTINCT
', (' + CAST((0 - xml.MinutesAgo) AS CHAR(5)) + ' 0)'
FROM @XMLDeadlocks xml
WHERE xml.MinutesAgo <= @CPURecords
FOR XML PATH('')
),
1,
2,
''
),
@DeadlockBlobs
= STUFF(
(
-- SQL Prompt formatting off
SELECT DISTINCT ', CURVEPOLYGON(CIRCULARSTRING('
+ CAST((0-xml.MinutesAgo) + @DeadlockBlobRadius AS CHAR(5)) + ' 0, '
+ CAST((0-xml.MinutesAgo) AS CHAR(5)) + ' ' + CAST(@DeadlockBlobRadius AS CHAR(5)) + ', '
+ CAST((0-xml.MinutesAgo) - @DeadlockBlobRadius AS CHAR(5)) + ' 0, '
+ CAST((0-xml.MinutesAgo) AS CHAR(5)) + ' ' + CAST((0 - @DeadlockBlobRadius) AS CHAR(5)) + ', '
+ CAST((0-xml.MinutesAgo) + @DeadlockBlobRadius AS CHAR(5)) + ' 0'
+ '))'
-- SQL Prompt formatting on
FROM @XMLDeadlocks xml
WHERE xml.MinutesAgo <= @CPURecords
FOR XML PATH('')
),
1,
2,
''
),
@DeadlockRecords =
(
SELECT COUNT(*) FROM @XMLDeadlocks xml WHERE xml.MinutesAgo <= @CPURecords
);
IF @DeadlockBlobRadius = 0
BEGIN -- show points
INSERT INTO @OutputGeometry (ShapeInfo, Caption)
VALUES
( geometry::Parse('MULTIPOINT(' + @DeadlockPoints + ')'), -- ShapeInfo - geometry
'Deadlocks' -- Caption - varchar(20)
);
END;
ELSE
BEGIN -- show blobs
INSERT INTO @OutputGeometry (ShapeInfo, Caption)
VALUES
( geometry::Parse('GEOMETRYCOLLECTION(' + @DeadlockBlobs + ')'), -- ShapeInfo - geometry
'Deadlocks' -- Caption - varchar(20)
);
END;
IF @OutputDeadlockTable = 1
BEGIN
SELECT UTCTime, LocalTime, MinutesAgo, XML FROM @XMLDeadlocks xd ORDER BY xd.MinutesAgo;
END;
END;
--End Bar
IF @ShowEndBar = 1
BEGIN
IF @EndBarWidth = 0
BEGIN
INSERT INTO @OutputGeometry (ShapeInfo, Caption)
VALUES
( geometry::Parse('LINESTRING(0 0,0 100)'), -- ShapeInfo - geometry
'Now' -- Caption - varchar(100)
);
END;
ELSE
BEGIN
INSERT INTO @OutputGeometry (ShapeInfo, Caption)
VALUES
( geometry::Parse('POLYGON((0 0,0 100, ' + CAST(@EndBarWidth AS CHAR(3)) + ' 100, '
+ CAST(@EndBarWidth AS CHAR(3)) + ' 0, 0 0))'
), -- ShapeInfo - geometry
'Now' -- Caption - varchar(100)
);
END;
END;
-- Final Output
SELECT ShapeInfo, Caption FROM @OutputGeometry;
END;
GO
What now?
There may be other functionality that can be added to this – but I can’t think of anything right now.
But I probably won’t be doing any more on this in the near future anyway, as my Redgate SQL Monitor licence has arrived, so I can get back to doing things properly.
