TSQ: Estimated database restore completion

Here’s a query proving you approximate percentage compeled, and estimated finish time, of any database restores happening on a SQL Server instance…

SELECT  st.[text],
		r.percent_complete, 
		DATEADD(SECOND, r.estimated_completion_time/1000, GETDATE()) AS estimated_completion_time,
		r.total_elapsed_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) st
WHERE [command] = 'RESTORE DATABASE';

The resultset will look something like below…

text				percent_complete	estimated_completion_time	total_elapsed_time
RESTRE DATABASE d...		47.57035		2014-08-08 13:49:48.373		958963

Monitoring fluentd with Nagios

Here’s just a few Nagios command strings you can use to monitor fluentd. I’ve thrown in a check for elasticsearch in case you’re monitoring an EFK system.

For checking td-agent. We should have 2 process, parent and child…

/usr/local/nagios/libexec/check_procs -w 2:2 -C ruby -a td-agent

For checking vanilla fluentd. Be aware your version name may differ…

/usr/local/nagios/libexec/check_procs -w 2:2 -C fluentd1.9

Check tcp ports. You requirements will vary…

/usr/local/nagios/libexec/check_tcp -H hostname -p 24224
/usr/local/nagios/libexec/check_tcp -H hostname -p 24230
/usr/local/nagios/libexec/check_tcp -H hostname -p 42185
/usr/local/nagios/libexec/check_tcp -H hostname -p 42186
/usr/local/nagios/libexec/check_tcp -H hostname -p 42187

For checking there is an elasticsearch process..

/usr/local/nagios/libexec/check_procs -w 1:1 -C java -a elasticsearch

TSQL: Database Mirroring with Certificates

Here’s some more TSQL for the 70-462 exam. The script shows the actions needed to configure database mirroring using certificates for authentication. Explanatory notes are included but you’re likely to need the training materials for this to make sense. TSQL is not included for the backup/restore parts needed for database mirroring.

SELECT *
FROM sys.symmetric_keys;
GO
 
-- Create a database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Secret1234';
GO
 
-- Create certificate (SQL-A)
CREATE CERTIFICATE SQL_A_Cert 
WITH SUBJECT = 'My Mirroring certificate'
GO
 
-- Create certificate (SQL-B)
CREATE CERTIFICATE SQL_B_Cert 
WITH SUBJECT = 'My Mirroring certificate'
GO
 
-- Endpoint (SQL-A) certificate authentication
CREATE ENDPOINT Endpoint_Mirroring
AS TCP (LISTENER_IP = ALL, LISTENER_PORT = 7024)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE SQL_A_Cert, ROLE = ALL);
GO
 
-- Endpoint (SQL-B) certificate authentication
CREATE ENDPOINT Endpoint_Mirroring
AS TCP (LISTENER_IP = ALL, LISTENER_PORT = 7024)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE SQL_B_Cert, ROLE = ALL);
GO
 
-- Backup certificate SQL-A
BACKUP CERTIFICATE SQL_A_Cert TO FILE = 'C:\backup\SQL_A_Cert.cer';
 
-- Backup certificate SQL-B
BACKUP CERTIFICATE SQL_B_Cert TO FILE = 'C:\backup\SQL_B_Cert.cer';
 
-- SQL-A create login for sql_b
CREATE LOGIN SQL_B_login WITH PASSWORD = 'Pa$$w0rd';
GO
 
CREATE USER SQL_B_user FROM LOGIN SQL_B_login;
GO
 
-- SQL-B create login for sql_a
CREATE LOGIN SQL_A_login WITH PASSWORD = 'Pa$$w0rd';
GO
 
CREATE USER SQL_A_user FROM LOGIN SQL_A_login;
GO
 
-- create cert on sql-a from sql_b backup
CREATE CERTIFICATE SQL_B_Cert
FROM FILE = 'c:\backup\sql_b_cert.cer';
GO
 
-- create cert on sql-b from sql_a backup
CREATE CERTIFICATE SQL_A_Cert
FROM FILE = 'c:\backup\sql_a_cert.cer';
GO
 
-- on sql-a
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO SQL_B_login;
GO
 
-- on sql-b
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO SQL_A_login;
GO
 
-- on sql-b
SELECT *
FROM sys.endpoints
ALTER ENDPOINT Endpoint_Mirroring STATE = STARTED;
ALTER DATABASE [AdventureMirror] SET PARTNER = 'TCP://sql-a:7024';
GO
 
-- on sql-a
SELECT *
FROM sys.endpoints
ALTER ENDPOINT Endpoint_Mirroring STATE = STARTED;
 
ALTER DATABASE [AdventureMirror] SET PARTNER = 'TCP://sql-b:7024';
GO
 
-- DMVs to check the setup
SELECT *
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;
 
SELECT *
FROM sys.database_mirroring_endpoints;

TSQL: Enable & Disable Logins and DENY connect

More notes for the 70-462 exam. This time we’re showing examples from ALTER LOGIN to enable and disable logins, as well a denying and granting the connect permission.

To Deny connect and disable a sql login…

USE [master]
GO
DENY CONNECT SQL TO [sql_user_c]
GO
ALTER LOGIN [sql_user_c] DISABLE
GO
Grant connect and enable a sql login
USE [master]
GO
GRANT CONNECT SQL TO [sql_user_c]
GO
ALTER LOGIN [sql_user_c] ENABLE
GO

To deny connect and disable a Windows login…

USE [master]
GO
DENY CONNECT SQL TO [SQL-A\local_account_b]
GO
ALTER LOGIN [SQL-A\local_account_b] DISABLE
GO

To grant connect and enable a Windows login…

USE [master]
GO
GRANT CONNECT SQL TO [SQLA\local_account_b]
GO
ALTER LOGIN [SQLA\local_account_b] ENABLE
GO

To deny connect to a windows domain group

USE [master]
GO
DENY CONNECT SQL TO [DOMAIN\domain_group_b]
GO

This doesn’t work for a windows group buy denying connect is essentially the same thing…

ALTER LOGIN [DOMAIN\domain_group_b] DISABLE
GO

This statement will return the following error…

Msg 33129, Level 16, State 1, Line 1
Cannot use ALTER LOGIN with the ENABLE or DISABLE argument for a Windows group. GRANT or REVOKE the CONNECT SQL permission instead.

TSQL: When were databases last restored

Here’s a couple of queries I’ve modified from When was the last time your SQL Server database was restored?

This first query shows restore times by database and restore type.

DECLARE @dbname sysname, @days int
SET @dbname = NULL --substitute for whatever database name you want
 
-- Max data / restore type
SELECT  rsh.destination_database_name AS [Database],
                                CASE
                                                WHEN rsh.restore_type = 'D'
                                                                                THEN 'Database'
                                                WHEN rsh.restore_type = 'F'
                                                                                THEN 'File'
                                                WHEN rsh.restore_type = 'G'
                                                                                THEN 'Filegroup'
                                                WHEN rsh.restore_type = 'I'
                                                                                THEN 'Differential'
                                                WHEN rsh.restore_type = 'L'
                                                                                THEN 'Log'
                                                WHEN rsh.restore_type = 'V'
                                                                                THEN 'Verifyonly'
                                                WHEN rsh.restore_type = 'R'
                                                                                THEN 'Revert'
                                                ELSE rsh.restore_type
                                END AS [Restore Type],
                                MAX(rsh.restore_date) AS [Restore Started]
                                --bmf.physical_device_name AS [Restored From],
                                --rf.destination_phys_name AS [Restored To]
FROM msdb.dbo.restorehistory rsh
 
LEFT JOIN msdb.dbo.backupset bs
	 ON rsh.backup_set_id = bs.backup_set_id
LEFT JOIN msdb.dbo.restorefile rf
	ON rsh.restore_history_id = rf.restore_history_id
LEFT JOIN msdb.dbo.backupmediafamily bmf
	ON bmf.media_set_id = bs.media_set_id
WHERE /*rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search for previous days
AND*/ destination_database_name = ISNULL(@dbname, destination_database_name) --if no dbname, then return all
GROUP BY rsh.destination_database_name,
                                CASE
                                                WHEN rsh.restore_type = 'D'
                                                                                THEN 'Database'
                                                WHEN rsh.restore_type = 'F'
                                                                                THEN 'File'
                                                WHEN rsh.restore_type = 'G'
                                                                                THEN 'Filegroup'
                                                WHEN rsh.restore_type = 'I'
                                                                                THEN 'Differential'
                                                WHEN rsh.restore_type = 'L'
                                                                                THEN 'Log'
                                                WHEN rsh.restore_type = 'V'
                                                                                THEN 'Verifyonly'
                                                WHEN rsh.restore_type = 'R'
                                                                                THEN 'Revert'
                                                ELSE rsh.restore_type
                                END
ORDER BY MAX(rsh.restore_date) DESC               
GO

This next query shows last full restore for a database. I’ve modified the query to join from sys.databases so a record is returned even for databases that do not have a restore record.

-- Max database restore
SELECT  d.[name] AS [Database],
	MAX(rsh.restore_date) AS [Restore Started]
FROM sys.databases d
LEFT JOIN msdb.dbo.restorehistory rsh
	ON rsh.destination_database_name = d.[name]
	AND rsh.restore_type = 'D'
LEFT JOIN msdb.dbo.backupset bs
	ON rsh.backup_set_id = bs.backup_set_id
LEFT JOIN msdb.dbo.restorefile rf
	ON rsh.restore_history_id = rf.restore_history_id
LEFT JOIN msdb.dbo.backupmediafamily bmf
	ON bmf.media_set_id = bs.media_set_id
GROUP BY d.[name]
ORDER BY MAX(rsh.restore_date) DESC               
GO