Snapshot replication with AlwaysOn

A client of mine was upgrading a 2008R2 system to 2016, they previously had database mirroring as HA solution, I suggested they go with AlwaysOn instead as mirroring is a deprecated, although simple to implement, solution. In addition to mirroring they had snapshot replication running once a day replicating a number of tables to a development environment. The old solution demanded the database was primary on one specific server, they could not change the application to use the FAILOVER PARTNER option, so at patching they always had to leave the primary running on one particular server. That wouldn’t do!

So when they decided to upgrade we went with SQL Server 2016 on Windows 2016 as that was the latest and greatest at the time. We set up Server1 and Server2 which were part of a new AlwaysOn cluster and Server3 which was a DEV server that would get some tables updated via snapshot replication each night. Simple setup, I won’t go into detail about AO, that can be found everywhere. What I was interested in was how to setup snapshot replication pushing data to a dev env. Turned out it was quite easy.

DISTRIBUTION
First we decided on where to setup the distribution database, I chose one of the AO servers, Server1 to be exact as replication was not a part of HA or DR, just copying some data and it didn’t matter if we missed a day rebuilding a server if it had crashed miserably. So on Server1 under the Replication tab we right click and choose to create a new distribution database and we also choose to have all agent jobs running on this server.

PUBLISHER
Set Server1 as PRIMARY AO server and start the New Publisher wizard, choose snapshot replication, set a schedule and choose to run a snapshot to disk right away. You will probably, depending on your setup, get an error that it can’t write to the replication catalog, easily fixed, just give the service account rights to do so and start the snapshot agent job again. Which one is the snapshot job? Well, look at the properties of each replication agent job and choose the one that says it’s the snapshot job. If it works you will get a new catalog called unc and the job will succeed, you can always check more with the Replication Monitor program.

SUBSCRIBER
Still on Server1 we right click on the published replication and choose New Subscriber, choose your security setup and create the subscription. After this first replication has been pushed out we should see the changes made on Server3.

AG CHANGES
We have to change the publisher from Server1 to the AG, so still on Server1 that is still Primary we run something similar to:

USE distribution;   
GO   
EXEC sp_redirect_publisher    
    @original_publisher = 'server1',   
    @publisher_db = 'db_to_replicate',   
    @redirected_publisher = 'YOUR-AG-NAME'
GO

This can give an error message that the secondary is not in readable mode, not sure if this is a real problem or not as I only tested on Enterprise Edition, I set the AG to Readable Secondaries=Yes and ran it again and it worked. I’ll try on Standard later. Next we run a verification, I got an error on Linked Server saying it can’t link to itself:
Warning: A linked server that refers to the originating server is not a supported scenario.  If you wish to use a four-part name to reference a local table, please use the actual server name rather than an alias.

 USE distribution;  
 GO  
 DECLARE @redirected_publisher sysname;  
 EXEC sys.sp_validate_replica_hosts_as_publishers  
     @original_publisher = 'server1',  
     @publisher_db = 'db_to_replicate',  
     @redirected_publisher = @redirected_publisher output;
GO 

Ok, so yes, this it it, I did some changes on the tables I wanted to replicate, ran the snapshot agent job again and it replicated nicely. Next I failed over to Server2 and again did some changes and ran the snapshot job on Server1 and voila! Everything still worked!!

One thing to remember, I only chose to replicate a subset of all the tables so after my first change and snapshot replication it didn’t replicate my changes, weird, but the change I did was to add another table, DUH, of course it didn’t replicate that, I had to go in under Properties of the Publication and choose that new table to have it replicated, ran the snapshot again and now it worked just fine 🙂

SQL Server unused indexes

Updating an index that is never used for searching is just a waste of cpu and diskspace, the query below can be run in a database and shows you which are not used and the size of the index.

Note, this is from the last instance reboot, if you have recently restarted than maybe you see wrong data. Or maybe you have an index that is only used for monthly reporting…or something, don’t just drop the indexes without carefully checking why it is created and its purpose.

Anywho, here’s the code…

SELECT d.name + '.' + s.name + '.' + o.name AS objectName
, i.name AS indexName
, MAX(ius.user_updates) AS userUpdates
, MAX(ius.user_seeks) AS userSeeks
, MAX(ius.user_scans) AS userScans
, MAX(ius.user_lookups) AS userLookups
, CASE
    WHEN SUM(a.used_pages) * 8 < 1024 THEN CONVERT(VARCHAR(32), SUM(a.used_pages) * 8) + ' kB'
    WHEN SUM(a.used_pages) * 8 / 1024 < 1024 THEN CONVERT(VARCHAR(32), SUM(a.used_pages) * 8 / 1024) + ' MB'
    ELSE CONVERT(VARCHAR(32), SUM(a.used_pages) * 8 / 1024 / 1024) + ' GB'
END AS indexSize
FROM sys.indexes i
INNER JOIN sys.objects o ON i.[object_id] = o.[object_id]
INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.dm_db_index_usage_stats ius ON ius.[object_id] = i.[object_id]
                             AND i.index_id = ius.index_id 
INNER JOIN sys.databases d ON d.database_id = ius.database_id
INNER JOIN sys.partitions AS p ON i.[object_id] = p.[object_id]
                             AND i.index_id = p.index_id
INNER JOIN sys.allocation_units AS a ON p.[partition_id] = a.container_id
WHERE ius.user_updates > 0
AND ius.user_seeks + ius.user_scans + ius.user_lookups <= 0
AND i.is_primary_key = 0
GROUP BY d.name, s.name, o.name, i.name
ORDER BY MAX(ius.user_updates) DESC;
GO

Should backups be run here?

Needed a quick way to see which databases should be backed up on a node, regardless of member of the AG or not. There is a function one can use and send the database name to it, see https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-hadr-backup-is-preferred-replica-transact-sql?view=sql-server-ver15 for how to use it.

It works but can be very slow, and I wanted to roll my own anyway, so a quick hack later I came up with the below code. It checks if we are on the node where AG backups should be done, if so prints out all the databases, even those not in the AG. If we are on the not preferred backup node then it only lists the non-AG databases. Works good enough for most circumstances.

DECLARE @SNAME VARCHAR(200)
SET @SNAME = @@SERVERNAME

--Primary and non-mirrored db should be backed up here
IF (SERVERPROPERTY('IsHadrEnabled') = 1) AND EXISTS(SELECT * FROM msdb.sys.dm_hadr_availability_replica_states ars WHERE ars.is_local = 1 AND ars.role_desc = 'PRIMARY')
BEGIN
	SELECT dcs.database_name
	FROM sys.dm_hadr_database_replica_states AS drs
	JOIN sys.availability_replicas AS r ON drs.replica_id = r.replica_id
	JOIN sys.availability_groups AS g ON g.group_id = drs.group_id
	JOIN sys.dm_hadr_database_replica_cluster_states AS dcs ON dcs.group_database_id = drs.group_database_id AND dcs.replica_id = drs.replica_id
	WHERE r.replica_server_name = @SNAME
	AND   g.automated_backup_preference_desc = 'primary'
	AND drs.database_state_desc = 'ONLINE'
	UNION
	select d.name
	from sys.databases d
	where d.database_id not in(select database_id from sys.dm_hadr_database_replica_states)
END

--Secondary and non-mirrored db should be backed up here
IF (SERVERPROPERTY('IsHadrEnabled') = 1) AND EXISTS(SELECT * FROM msdb.sys.dm_hadr_availability_replica_states ars WHERE ars.is_local = 1 AND ars.role_desc = 'SECONDARY')
BEGIN

	SELECT dcs.database_name
	FROM sys.dm_hadr_database_replica_states AS drs
	JOIN sys.availability_replicas AS r ON drs.replica_id = r.replica_id
	JOIN sys.availability_groups AS g ON g.group_id = drs.group_id
	JOIN sys.dm_hadr_database_replica_cluster_states AS dcs ON dcs.group_database_id = drs.group_database_id AND dcs.replica_id = drs.replica_id
	WHERE r.replica_server_name = @SNAME
	AND   g.automated_backup_preference_desc = 'secondary'
	AND drs.database_state_desc = 'ONLINE'
	UNION
	select d.name
	from sys.databases d
	where d.database_id not in(select database_id from sys.dm_hadr_database_replica_states)
END

Sort SSMS Multi Server Result Set

Hi,

A little known really good SSMS functionality is what is called Registered Servers, for more info on that specifically, look here

What I want to talked about today is the problem when using it against a bunch of servers and wanting to get the result set back ordered. Thing is the servers send back their respective result set when they are done and there is no way in SSMS you can sort it. My problem I had was that I wanted the result sets back in the order of the server name, I was coding a simple script to check the Always-On and Mirroring health against a whole bunch of servers, the output from them unsorted was very cluttered, I wanted the result sets sorted.

What I ended up doing, and you can see it in the script below, is that I converted the server name to UNICODE and took the first number as seconds to wait and the next 3 as milliseconds to wait before returning the result set. The result set I wanted was the health of the database synchronization, I know this should be monitored but sometimes you just want to do a quick check, and the wait part can be used for whatever you like.

It’s so not perfect and may not work in your environment, run it on some test servers first, you may have to adapt the code a bit but it’s quite easy.

A quick hack but it made my day 🙂

USE master
GO

DECLARE @name VARCHAR(20),@sum VARCHAR(5), @ss VARCHAR(2), @ms VARCHAR(3)
DECLARE @position int
DECLARE @delay VARCHAR(12) 
SET @name =  @@SERVERNAME
SET @position = 1

WHILE @position <= LEN(@name)
BEGIN
    SELECT @sum = CONCAT(@sum, UNICODE(SUBSTRING(@name, @position, 1)))
    SELECT @position = @position + 1;  
END
SET @ss = SUBSTRING(@sum,1,1)
SET @ms = SUBSTRING(@sum,2,3)
SET @delay = '00:00:'+@ss+':'+@ms
WAITFOR DELAY @delay

--PRIMARY AG
IF (SERVERPROPERTY('IsHadrEnabled') = 1) AND EXISTS(SELECT * FROM msdb.sys.dm_hadr_availability_replica_states ars WHERE ars.is_local = 1 AND ars.role_desc = 'PRIMARY')
SELECT 'AO Primary' as HA, role_desc, operational_state_desc, connected_state_desc, recovery_health_desc, synchronization_health_desc FROM msdb.sys.dm_hadr_availability_replica_states WHERE role_desc = 'PRIMARY'

--SECONDARY AG
IF (SERVERPROPERTY('IsHadrEnabled') = 1) AND EXISTS(SELECT * FROM msdb.sys.dm_hadr_availability_replica_states ars WHERE ars.is_local = 1 AND ars.role_desc = 'SECONDARY')
SELECT 'AO Secondary' as HA, role_desc, operational_state_desc, connected_state_desc, recovery_health_desc, synchronization_health_desc FROM msdb.sys.dm_hadr_availability_replica_states WHERE role_desc = 'SECONDARY'

--OLD SCHOOL MIRRORING
IF EXISTS(select * from sys.database_mirroring where mirroring_guid is not null)
SELECT 'Mirroring' AS HA, DB_NAME(database_id) AS 'DB name', mirroring_state_desc, mirroring_role_desc from sys.database_mirroring where mirroring_guid is not null

Stop SQL Server backups from cluttering up your SQL Server error log and the Windows event log

If you do not want all the backup database or backup log messages to show up in the Windows event viewer and SQL Server error log, the only thing to do is to add a trace flag to the SQL Server instance start up parameters. It is called trace flag 3226 and can be added by opening the SQL Server Configuration Manager, choosing Properties on the instance and under Startup Parameters add the string: -t3226

Then restart the instance, now the messages should not be logged to event viewer anymore.

See https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?redirectedfrom=MSDN&view=sql-server-2017

Why do this at all you ask? Well one example can be a client of mine that has 2000 databases in an instance, the logs are filled up with messages of successfull database and log backups, can’t see the trees!

“What is currently running in my instance?”

I have a script I turn to first, just to get a quick estimate of what is happening in my SQL Server instance.

It works in most versions 2008+ but you might find something lacking, if so just comment out that part.

The slowest part of the script is getting the graphical sql plan, so if you need real fast results then, yup, comment it out until you need it.

It’s a good script to have around, even better is  sp_blitz from Brent Ozar, you can find it and a lot of other tools here https://www.brentozar.com/blitz/

SELECT 	db_name(r.database_id), r.session_id, r.blocking_session_id, r.cpu_time,
		SUBSTRING(qt.text,(r.statement_start_offset/2) +1,
		(CASE WHEN r.statement_end_offset = -1
				THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
		ELSE 
				r.statement_end_offset
		END -r.statement_start_offset)/2) AS stmt_executing,
	s.login_name, r.percent_complete, r.start_time, 
	CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time 
	,CASE s.transaction_isolation_level
		WHEN 0 THEN 'Unspecified'
		WHEN 1 THEN 'ReadUncomitted'
		WHEN 2 THEN 'ReadComitted'
		WHEN 3 THEN 'Repeatable'
		WHEN 4 THEN 'Serializable'
		WHEN 5 THEN 'Snapshot'
	END as 'Isolation level'
	,	s.HOST_NAME, 	s.PROGRAM_NAME,	s.host_process_id, 
	r.status, 	r.wait_time,	wait_type, 	r.wait_resource,
	r.total_elapsed_time,r.reads,r.writes, r.logical_reads, r.plan_handle
	--Comment out below if you don't need the plan
	, (select query_plan from sys.dm_exec_query_plan(r.plan_handle)) as xml_plan
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt, 
			sys.dm_exec_sessions s
WHERE r.session_id > 50 
and r.session_id=s.session_id
and r.session_id <> @@SPID
ORDER BY r.cpu_time desc

Open firewall port with Powershell

If you have a server facing the Internet and you want to have RDP open on it, the least you can do is to change RDP port, just makes simple sense.

So, first change the port RDP will listen to via running regedit and changing HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Terminal Server\WinStations\RDP-Tcp\PortNumber in decimal node to something other than 3389, lets say port 3355 for the sake of this exercise.

Now, before you reboot change the incoming open firewall port on the server to that same port or you won’t be able to RDP in. Simple Powershell line: netsh advfirewall firewall add rule name=”Open Port 3355″ dir=in action=allow protocol=TCP localport=3355

Now I run some stuff in Azure, so I change my RDP port there too. When that is done, reboot 🙂

Unlock sa account

Always use AD accounts now, skip SQL accounts and do not use sa for anything other than whatever you just can’t solve with an AD sysadmin account.

Ok, sa is locked out, you have sysadmin permissions, so you can unlock it with the following.

ALTER LOGIN [sa] WITH CHECK_POLICY = OFF;

ALTER LOGIN [sa] WITH CHECK_POLICY = ON;

Nice huh!

Get a mail whenever a process is taking a long time to finish

Simple monitoring of long running processes, it will mail you data about the currently long running processes if it finds any. Prereqs are that you define a database mail profile.

(See further below how to setup a database mail profile.)

This script will mail whenever it finds anything that’s been running more than 10 seconds, change the line

AND DATEDIFF(ss, r.start_time, GETDATE()) > 10

to whatever you want, but lower than 10 seconds wont help much as this script should be run in SQL Agent and it has a lower limit of 10 seconds as wait between runs.  Run it as a simple TSQL script every 10 seconds or what is good for you.

IF EXISTS ( 
	  SELECT CAST(r.session_id AS NVARCHAR(10)), db_name(r.database_id)
	FROM sys.dm_exec_requests r
	CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt, 
			sys.dm_exec_sessions s
	WHERE r.session_id > 50 
	and r.session_id=s.session_id
	and s.program_name not like '%DatabaseMail%'
	and r.session_id <> @@SPID 
	AND DATEDIFF(ss, r.start_time, GETDATE()) > 10	  
	  ) 
BEGIN	
	EXEC msdb.dbo.sp_send_dbmail
    	@profile_name = 'DatabaseMail',
    	@recipients = 'receiver@yourdomain.com',
    	@query = 'SELECT db_name(r.database_id), r.session_id, r.blocking_session_id, r.cpu_time,
		SUBSTRING(qt.text,(r.statement_start_offset/2) +1,
		(CASE WHEN r.statement_end_offset = -1
				THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
		ELSE 
				r.statement_end_offset
		END -r.statement_start_offset)/2) AS stmt_executing,
		s.login_name, 	s.HOST_NAME, 	s.PROGRAM_NAME,	s.host_process_id, 
		r.status, 	r.wait_time,	wait_type, 	r.wait_resource,
		r.total_elapsed_time,r.reads,r.writes, r.logical_reads
      		FROM sys.dm_exec_requests r
      		CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as qt, 
			sys.dm_exec_sessions s
      		WHERE r.session_id > 50  --ensure that we omit SQL server processes
	 	AND r.session_id=s.session_id
      		AND DATEDIFF(ss, r.start_time, GETDATE()) > 10',
	@subject = 'Long running processes',
    	@attach_query_result_as_file = 1
END

 

Mail profile setup via script, change the mailhost and some other stuff to what you like before trying to send database mail.

--Enable Database Mail features on server

sp_configure 'show advanced',1
go
reconfigure
go
sp_configure 'Database Mail XPs',1
go
reconfigure
go

-- Create a Database Mail Account and Profile
-- !!! Remember to change display_name to your server\instance!!!

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MailAccount',
@description = '',
@email_address = 'noreply@domain.com',
@display_name = 'SERVERNAME\INSTANCENAME',
@mailserver_name = 'mailhost.domain.com' ;


-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DatabaseMail',
@description = '' ;

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DatabaseMail',
@account_name = 'MailAccount',
@sequence_number =1 ;

-- Grant access to the profile to public

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'DatabaseMail',
@principal_name = 'public',
@is_default = 1 ;

 

 

TDE Transparent database encryption

Start encrypting your data folks, better be safe than sorry and TDE is a simple way of keeping at least the mdf files and backups safe from snooping eyes.

In more technical terms TDE encrypts data at rest, so data that is on the disk is encrypted, it is decrypted when read into memory, so if you have access to the database through a SQL Server login you can see the decrypted data just as usual. Also the backups are encrypted so an attacker can’t just copy a .bak file and run with it, they’ll need the certificate to be able to decrypt it.

Better than nothing and you can set it up in AlwaysOn too, I have documented a simple way to do it below.

IMPORTANT!!!

Keep your passwords and certificates safe, preferably NOT on the DB servers. Ok? Good 🙂

Run this on a test AO a couple times until you are sure of all the steps.

--Primary
USE Master;
GO

-- Create a master key
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='reallystrongpassword';
GO

-- Create certificate
CREATE CERTIFICATE TEST_Cert
WITH 
SUBJECT='Test Cert';
GO

-- Backup to somewhere on disk
BACKUP CERTIFICATE TEST_Cert
TO FILE = 'Z:\dbbackup\TEST_Cert'
WITH PRIVATE KEY (file='Z:\dbbackup\TEST_Cert_Private',
ENCRYPTION BY PASSWORD='alsoreallystrongpassword')

--Secondary
USE Master;
GO

CREATE MASTER KEY ENCRYPTION
BY PASSWORD='reallystrongpassword'; --does not need to match primarys master key password
GO

CREATE CERTIFICATE TEST_Cert
FROM FILE='Z:\dbbackup\TEST_Cert'
WITH PRIVATE KEY (
FILE = 'Z:\dbbackup\TEST_Cert_Private',
DECRYPTION BY PASSWORD='alsoreallystrongpassword')


--Now check on both servers that we have the certificate installed
SELECT name, certificate_id, principal_id, pvt_key_encryption_type_desc, start_date, expiry_date
FROM sys.certificates;

--Also check with AlwaysOn dashboard that the databases are in sync

--Primary
USE DB_To_Be_Encrypted  -- I am sure your databases have better names...
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TEST_Cert;
GO

ALTER DATABASE DB_To_Be_Encrypted --This will take a while even if the command returns immediately
SET ENCRYPTION ON;
GO

--On primary we can run some commands to check logspace used, encryption progress and such
USE MASTER;
GO

dbcc sqlperf(logspace)
go

sp_helpdb DB_To_Be_Encrypted
go

-- Check encryotion process
SELECT db.name,db.is_encrypted,dm.encryption_state,dm.percent_complete,
dm.key_algorithm,dm.key_length
FROM sys.databases db
LEFT OUTER JOIN 
sys.dm_database_encryption_keys dm
ON db.database_id = dm.database_id;
GO

 

How do we remove TDE? Easy, below will clean up what we added above.

--Primary
alter database DB_To_Be_Decrypted set encryption off

-- When it has finished decrypting
use DB_To_Be_Decrypted
drop database encryption key


--On both primary and secondary
use master 
drop certificate TEST_Cert
drop master key