Speaking at SQL Saturday Exeter #269

At the end of last year, I had my first SQL Saturday experience. I presented my first international session on SQL Saturday in Slovenia. It was really great, so I decided to submit other sessions for other SQL Saturdays. I really like the British people so I submitted a few sessions for SQL Saturday Exeter. And guess what, one of my sessions got selected! I’m really looking forward to meet the British SQL people and the other international speakers that I have met before like Jen Stirrup, Stacia Misner, William Durkin, André Kamman, Mladen Prajdic and Matija Lah. You can find my session abstract below.

 

1, 2, 3, … wait… 1, 2, 3, … many… SQL Servers

Quote: “A good DBA is a lazy DBA”. When you have to manage a few SQL Servers, it’s not that hard to be a lazy DBA and to automate your day to day activities. But when you have to manage over 100 instances, it can become a tough job. Just imagine how you will deploy a new maintenance job on all your instances? Are you going to connect to each server one by one? Not really… Microsoft provides an enhanced set of multi-server management tools. The purpose of this sessions is to point out which SQL Server tools you can use for multi-server management. The session is bulk loaded with demos and it will give you a good idea what features can be helpful in your environment.

More information about the event can be found on http://www.sqlsaturday.com/269/eventhome.aspx

Hope to see you all there!

Visualize your backup schedules

Recently I was asked by a customer to provide the database backup timings for every SQL Server (only the full backups).
Originally, all the backups were taken at 19:00 on a SAN disk that was attach to each server directly. Once the backup was taken, it is then pushed to tape every night. But recently the customer changed strategy and all backups had to be put on a CIFS share of a NetApp. The backup location was changed but not the schedule. As a result, some servers were having problem with the backups. All servers were taking their backups at the same time and were transferred over the network to the same network share. The network load increased a lot J

So, I had to come up with a solution to get the start time and the duration of each backup job of each server. With that information, I could reschedule the jobs on each server so that the network load was spread during the night.

This blog post of Brad Schulz (Blog) helped me to get the information out of the sysjobs tables. I’ve changed the script a bit so that I got the average backup duration of each server. I’m using a Central Management Server so it was easy to get my information in one time.

SET NOCOUNT ON; 
declare @HistoryStartDate datetime,
		@HistoryEndDate datetime

set @HistoryStartDate=’19000101’
set	@HistoryEndDate = getdate();

with JobHistData as(  
	select j.name,
			date_executed=convert(datetime,convert(varchar(8),h.run_date)) +’ ‘+stuff(stuff(right(1000000+h.run_time,6),3,0,’:’),6,0,’:’),
			jobsecsduration=h.run_duration/10000*3600+h.run_duration%10000/100*60+h.run_duration%100,
			jobtimeduration=stuff(stuff(right(1000000+h.run_duration,6),3,0,’:’),6,0,’:’),
			jobstarttime=stuff(stuff(right(1000000+s.active_start_time,6),3,0,’:’),6,0,’:’)
	from msdb.dbo.sysjobhistory  h
	inner join msdb..sysjobs j on h.job_id = j.job_id
	inner join msdb..sysjobschedules js on js.job_id=j.job_id
	inner join msdb..sysschedules s on s.schedule_id=js.schedule_id
	where j.name=’DatabaseBackup – USER_DATABASES – FULL’
	and step_id=0     --Job Outcome
	and run_status=1  --Succeeded
	)

select Name,AvgDuration=convert(int,avg(jobsecsduration*1)),CONVERT(VARCHAR, DATEADD(second,convert(int,avg(jobsecsduration*1)),0),108) as AvgTime,JobStarttime
from JobHistData 
where date_executed>=dateadd(day,datediff(day,’19000101’,@HistoryStartDate),’19000101’)  and date_executed<dateadd(day,1+datediff(day,’19000101’,@HistoryEndDate),’19000101’)
group by Name,jobstarttime

The query gives me the following result (server names are hidden)

Nice overview but not really useful to reschedule the backup jobs. I needed something visual, with timelines, so that I could easily see which backups were running at the same time. I tried several programs but I came up with a solution in… (ow yes) Excel! To be honest, I’m not an expert in Excel 😉

First I paste the result of my query in Excel. Column C and D should have type Time.
I added some extra columns:

  • Current Endtime = Column C + D
  • Number Starttime = Column D but the type is Number
  • Number Endtime = Column E but the type is Number
  • Number Elapsed Time = Column G – F. Column should also have the Number type

On the second sheet add an empty Stacked Bar Chart

Right click on the chart and select “Select Data…”

The Chart data range should be the first column Server Name
Edit the Series:

  • Change the Series name in Starttime
  • Series values will be column D Current Starttime

Add a second Serie

  • Change the Series name in Duration
  • Series values will be column H Number Elapsed Time

Edit the Category

  • Values will be column A Server Name

You should now have a similar configuration

Now that we have all the data in our chart, let’s fix the layout

Right click on the Horizontal Axis and select Format Axis…

On your right, select the AXIS Options and change the following values

  • Bound Minimum    0,791666666666667    This is 19:00, the start time of my backups
  • Bound Maximum    1            This is 00:00
  • Units Major        0,010416666666666    This is every 15 minutes

As a result, the axis should now be a time value from 19:00:00 till 00:00:00 for every 15 minutes.

Right click on the Vertical Axis, go to AXIS Options and select Categories in reverse order. This will change the order of my server list and will put the horizontal axis on top.

The last thing that you need to do is to hide the Starttime series. We are only interested in duration of the backup starting from the start time. In the chart, right click the starttime series (this is normally the blue line) and select Format Data Series Go to the Fill option and select No fill

Now you should get a similar result like this

If you want, you can add some extra design to the chart. I finally came up with the following layout

This really gives me a nice overview how my backups are scheduled.
Setting up my new backup timings will be really easy now. I just have to change the start time on the first sheet.

You can get my Excel example here!

Have fun!

Create an empty AlwaysOn Availability Group

Last week I had to install a 6 node failover cluster at a customer. 4 nodes were used for high availability solution in site A and 2 nodes were in site B for disaster recovery. Each site was hosting 9 SQL Server instances.

On my last day, I had to configure the AlwaysOn Availability Groups (AVG) as disaster recovery solution.  I have chosen the following settings for my AVG’s

  • Asynchronous commit mode
  • Backups on primary replica
  • Readable secondary

There was just one small “problem”. There were no databases to include in the AVG ‘s because this was a complete new platform. I couldn’t use the AlwaysOn Availability Group Wizard because that expects a database. But I wanted to prepare the AVG’s in advance so that the customer only needed to add the databases to the groups at a later stage.
Since I had to install 9 AVG’s I created a script that I could reuse every time I want to create an empty availability group.
The script will create an Availability Group on the primary replica, will add a group listener and will join one secondary replica all in one execution.

You can find the script below but I must mention a few remarks and restrictions

  • You have to execute the script in the SQLCMD mode in the SQL Server Management Studio. To do this go in the main menu to Query –> SQLCMD Mode
  • Only one secondary replica can be used
  • The settings for the replica’s are the one mentioned above
  • Only one IP address can be linked to the group listener
  • Named instances are supported

Before you can execute the script you only need to specify a few parameters:

Parameter Description
PrimaryReplica The primary replica. Default and Named instances are supported eg. ServerInst01
SecondaryReplica The secondary replica. Default and Named instances are supported eg. ServerInst01
EndPointPort Specify the port for the endpoints
AvailabilityGroup The name of the availability Group
Listener The name of the group listener
IPListener The IP address of the listener
ListenerPort Specify the listener port
ListenerSubnet Specify the subnet of the listerner IP Address

You can find my script below. If you should have questions, remarks or suggestions, please do leave a comment so that I can improve it.

Have fun!

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:setvar PrimaryReplica Node1 --Specify the primary replica
:setvar SecondaryReplica Node2 --Specify the secondary replica
:setvar EndPointPort 5022 --Specify the port for the endpoints
:setvar AvailabilityGroup AVG_Group --Specify the name of the availability group
:setvar Listener AGL --Specify the name of the group listener
:setvar IPListener 192.168.0.20 --Specify the IP Address of the group listener
:setvar ListenerPort 1433 -- Specify the listener port
:setvar ListenerSubnet 255.255.240.0 --Specify the subnet of the listeren IP Address

:Connect $(PrimaryReplica)

USE [master]
GO
declare @AVGLogin as sysname
declare @CMD nvarchar(max)

select @AVGLogin=service_account from sys.dm_server_services
where servicename like 'SQL Server (%'

if not exists(select name from sys.syslogins where name=@AVGLogin)
begin
 set @CMD=N'CREATE LOGIN [' + @AVGLogin + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]'
 print 'Create service account as server login...'
 exec sp_executesql @CMD
end

if not exists (select name from sys.endpoints where name='Hadr_endpoint')
begin
print 'Create HA/DR endpoint...'
CREATE ENDPOINT [Hadr_endpoint] 
 AS TCP (LISTENER_PORT = $(EndPointPort))
 FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
end

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
 ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END


set @CMD=N'GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [' + @AVGLogin + ']'
print 'GRANT CONNECT ON ENDPOINT...'
exec sp_executesql @CMD

GO

:Connect $(SecondaryReplica)

USE [master]
GO

declare @AVGLogin as sysname
declare @CMD nvarchar(max)

select @AVGLogin=service_account from sys.dm_server_services
where servicename like 'SQL Server (%'

if not exists(select name from syslogins where name=@AVGLogin)
begin
 set @CMD=N'CREATE LOGIN [' + @AVGLogin + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]'
 print 'Create service account as server login...'
 exec sp_executesql @CMD
end

if not exists (select name from sys.endpoints where name='Hadr_endpoint')
begin
print 'Create HA/DR endpoint...'
CREATE ENDPOINT [Hadr_endpoint] 
 AS TCP (LISTENER_PORT = $(EndPointPort))
 FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
end

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
 ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END

set @CMD=N'GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [' + @AVGLogin + ']'
print 'GRANT CONNECT ON ENDPOINT...'
exec sp_executesql @CMD

GO

:Connect $(PrimaryReplica)

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
 ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
 ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END

GO

:Connect $(SecondaryReplica)

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
 ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
 ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END

GO

:Connect $(PrimaryReplica)

USE [master]
GO

declare @SystemAddressPrimary nvarchar(256)
declare @SystemAddressSecondary nvarchar(256)

if charindex('','$(PrimaryReplica)')=0
 set @SystemAddressPrimary='$(PrimaryReplica)'
else
 select @SystemAddressPrimary=substring('$(PrimaryReplica)',0,charindex('','$(PrimaryReplica)'))

if charindex('','$(SecondaryReplica)')=0
 set @SystemAddressSecondary='$(SecondaryReplica)'
else
 select @SystemAddressSecondary=substring('$(SecondaryReplica)',0,charindex('','$(SecondaryReplica)'))

declare @CMD nvarchar(max)
set @CMD='
CREATE AVAILABILITY GROUP [$(AvailabilityGroup)]
WITH (AUTOMATED_BACKUP_PREFERENCE = PRIMARY)
FOR 
REPLICA ON N''$(PrimaryReplica)'' WITH (ENDPOINT_URL = N''TCP://' + @SystemAddressPrimary + ':$(EndPointPort)'', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY)),
 N''$(SecondaryReplica)'' WITH (ENDPOINT_URL = N''TCP://' + @SystemAddressSecondary + ':$(EndPointPort)'', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY));'

print 'Create the availability group...'
exec sp_executesql @CMD

print 'Create the availability group listener...'
ALTER AVAILABILITY GROUP [$(AvailabilityGroup)]
ADD LISTENER N'$(Listener)' (
WITH IP
((N'$(IPListener)', N'$(ListenerSubnet)')
)
, PORT=$(ListenerPort));

GO

:Connect $(SecondaryReplica)

ALTER AVAILABILITY GROUP [$(AvailabilityGroup)] JOIN;

GO