SQL Server Tip of the Week – AlwaysOn Introduction

Here’s some notes I recently wrote to help explain the basics of AlwaysOn Clustering in SQl – originally written for fairly technical people, i/e/ they know how to install SQL and use SSMS!

 
What is Always On?
SQL’s new (from 2012) failover clustering solution, built in to SQL that allows automatic failover of SQL servers without interruption to the applications using a database.

What is required?
2 fully licensed SQL servers at the same version with replication module installed
Windows Failover Clustering role installed
IP addresses for cluster (1 for cluster, min. 1 per node)
Windows Domain – cluster is a domain object

How to install/configure
Microsoft documentation/Brent Ozar is best source.
e.g. https://www.brentozar.com/archive/2015/06/how-to-set-up-standard-edition-alwayson-availability-groups-in-sql-server-2016/ for 2016
For installing Failover clustering you could add the role via PowerShell e.g.

Install-WindowsFeature FailoverClustering,RSAT-Clustering-Mgmt,RSAT-Clustering-PowerShell


What to know in advance

For an installer there are a number of things you need to know in advance:
• What type of synchronization is to be used?
• Synchronous or Asynchronous – https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups
• Cluster Name – this is the name above all servers in a cluster and therefore where Epicor applications point
• Cluster IP address – ideally decided in advance so can be reserved/static , it’s the IP for the cluster not the server
• Node IP address – each server in the cluster will have a secondary IP used purely for clustering, this is not the IP address used for management of that server
• Secondary Node IP address – only if the servers exist in different subnets they need a node IP address in each range
• Witness Server file location – this is another server available to all SQL servers where a witness file is stored to maintain quorum, example \\FILESERVER\SQLWitness

Things to Note

• Asynchronous synchronization can mean a data loss, review the link above for more info, it is also not an automated failover. – designed for offsite replication over a large distance.
• Synchronous is a more accurate (live replica) sync, but “could” be a source of lag SQL side. i.e. increased overhead
• In 2016 Standard SQL you can only have 1 DB in each availability group, therefore from a config side it’s worth considering which DBs need to be available should the system failover.
• When restoring a database in an availability group (overwriting) you must take it out of the availability group first, and example of doing this with existing restore scripts is (with additions highlighted):

--Backup Source DB Code Goes Here
--Safety Backup of Destination DB Code Goes Here
USE [master]
ALTER AVAILABILITY GROUP MyDatabase_AG REMOVE DATABASE MyDatabase; 
--Restore Script GOes here, i.e. grab back of source and overwrite destination...
ALTER AVAILABILITY GROUP MyDatabase_AG ADD DATABASE MyDatabase
GO

Virtual Home Server

As one of those people who loves to be running the latest tech in both my home and professional lives it’s critical that I build the correct infrastructure in order to achieve that.

At home I recently obtained a 2014 spec Dell server, which had a fair bit of memory and storage, certainly for what was to become the hub of my home operations!

In the last 2 months I have been building the server up, utilising all the latest platforms I can get my hands on.. VMWare ESX 6.5, Windows 10/2016, Ubuntu 16.04 etc.

I now have 8 VMs, across 2 datastores having upgraded all firmware possible and playing around with various settings to balance performance and noise (it’s in the spare room)

Here’s the outcome of that work:

This first image shows my ESX 6.5 HTML5 based landing page (one of easiest to use web admin tools I’ve seen), you’ll note the 128GB RAM, Dual 2.9GHz CPUs and 8.5TB storage – perfect for running media servers as well as testing platforms for my crazy ideas!

Drilling down into the VMs I have built you’ll see a mixture of OSes and things I’m testing:

I was clever enough (somehow) to make my FTP server web facing, it’s where I store all the freebie utility style programs that I use across many systems, It allows me to use it instead of having to carry a USB around all the time!

Plex is the big one, over 3TB assigned to it for all the media we have at home, we can play it across all our devices, such as the SmartTV, Amazon Fire Stick, XboxOne etc.

What I’ve not yet got to grips with is the VMNetwork side of things, eventually I’d like to VLAN off some of the VMs to do some sandbox style testing with various OSes, maybe get back into Linux and re-learn hardening techniques etc, just need the time!

SQL Server Tip of the Week – Domain Migration

I thought I would try a new feature, and hopefully get a schedule of tips I want to share with the world.

In the last week or so the subject of Domain Migration has come up for the millionth time in my career so far (slight exaggeration)

Switching domains is easy, log in on one domain, go to the system settings in Windows and change to the other domain, with a reboot committing the change fully. This is great, but what about the applications?

SQL is possibly the most popular DB platform out there, and up until the last couple of months has been restricted to the MS Windows platform. As a Microsoft product it ties in heavily to the operating system and Active Directory. Typically most DBAs will configure there SQL installation with dual authentication, Windows for binding Windows Accounts and SQL Authentication for local based security. The majority of applications these days will utilise Windows Authentication as this make the management of security policies such as password expiration and removal of users a little simpler, and generally management is from one place, i.e Active Directory. The only issue here is if you’re doing a manual Domain Migration (i.e no ADMT style tools) then as soon as you change the domain the SQL server sits on, the applications will no longer work and the users will not be able to connect. This is where the SA user is god, as it is local to the SQL installation not the domain/Windows element.

My advice for a manual SQL Server domain migration is simple:

  1. Ensure you have dual auth enabled
  2. Ensure you know your SA password (if not, create a new SA style user and note the password you use)
  3. Change SQL to use builtin service accounts (not a domain service account) before the move – can be changed afterwards to domain users under new domain.
  4. Change ownership of all DBs to SA
  5. Change ownership of all Task Agent jobs to SA

These should be done before switching domain!

For steps 4 and 5 I have the following scripts which I have used a few times now, and they do exactly as described, changing the ownership:

Change DBs ownership to sa:

EXEC sp_MSforeachdb 'EXEC [?]..sp_changedbowner ''sa'''

Change Job ownership to sa:

DECLARE @name_holder VARCHAR(1000)

DECLARE My_Cursor CURSOR

FOR

SELECT [name]  FROM msdb..sysjobs

OPEN My_Cursor

FETCH NEXT FROM My_Cursor INTO @name_holder

WHILE (@@FETCH_STATUS <> -1)

BEGIN

exec msdb..sp_update_job

        @job_name = @name_holder,

        @owner_login_name = 'sa'

FETCH NEXT FROM My_Cursor INTO @name_holder

END

CLOSE My_Cursor

DEALLOCATE My_Cursor

 

One additional step once the migration is done and the server is back up on the new domain, would be to login as sa, and add in the security groups/users from the new domain that need access to the server. I typically add the Domain Admins as SQL sysadmin users. To achieve this for the current domain you can run:

USE [master]
GO
declare @DomainName nvarchar(32)
set @DomainName = (SELECT DEFAULT_DOMAIN()[DomainName])
declare @DomainAdmin nvarchar(64)
set @DomainAdmin = @DomainName + '\Domain Admins'
print @DomainAdmin
EXEC master..sp_addsrvrolemember @loginame = N'NT AUTHORITY\SYSTEM', @rolename = N'sysadmin'
EXEC master..sp_addsrvrolemember @loginame = N'NT AUTHORITY\NETWORK SERVICE', @rolename = N'sysadmin'
EXEC master..sp_addsrvrolemember @loginame = @DomainAdmin, @rolename = N'sysadmin'
print @DomainAdmin + ' Has been Added as sysadmin'
GO

Incidentally this was one of my early attempts at writing SQL scripts without googling around! – not this also adds SYSTEM and NETWORK SERVICE (local builtin users) as sysadmins.

Epicor Users Group EMEA 2017 Conference

Epicor as a company would be nothing without its customers, and customers wouldn’t get very far without Epicor.
Throw into that mix an array of partners, after all I joined the Epicor world through a partner, and you have a fantastically balanced world of skills, expertise, knowledge, and ideas. More importantly it’s the people, and in the last week I finally got to really understand that community of people, and become a part of it.

On Wednesday, 15th November, 2017 at 08:30 I arrived at the Priest House Hotel, Castle Donnington, UK with an unusual, previously unknown kind of nervous excitement. I had arrived, suited and booted, laptop in tow and ready to showcase myself, represent my company and embrace this wonderful community.
Only 2 weeks prior had I received the invitation to present on Server and SQL Optimisation, a topic I know a fair amount about but the concept of presenting at this level was new to me. That said I think I may have mentioned previously that it was something that interested me, so it was potentially my own doing.
2 weeks to prepare a presentation isn’t a huge amount of time when you have a day job, and a home life to work around, but I relished the challenge and got stuck in, only a few days after the initial request and approval did I discover that I in fact had 2x 1 hour presentation slots…ah… a slightly bigger challenge, but again one to relish.
With 4 days to spare I had 2 presentations written, and a full mirror test complete, with only a slight worry on timings.
Fast forward back to 09:30 on the day and the first presentation begins, 30+ people in the room staring at me wondering how this will go down. 1 hour goes by, maybe a bit more, and it seems to be going ok. The questions are flooding in, I’m somehow managing to respond to all of them, answering everything thrown at me. The feedback continues into the break, a number of people saying “great presentation”, “thanks for the information”, “I’m going to look at X when I get back”. Wow, I actually resonated with some of these people. Bearing in mind that many have been in the Epicor and technology worlds far longer than I have, they seemed to all pick something up from the first one. Great, confidence restored, let’s nail the second presentation.
Before I knew it lunch was served, I was still conversing with various people; customers, partners, ex-colleagues and current ones. It wasn’t until I realised only a sausage roll was left in the room that I had gone through 2 hours of presenting and almost an full hour extra of chatting on only 1 coffee, a mini cinnamon swirl and this sausage roll!
Never before had I talked so much tech on so little caffeine, it was at that point I realised that knowledge, preparation and adrenaline we key to this day.

The EUG EMEA 2017 conference seemed to be a huge success across the 3 days from everything I have seen, the middle day on which I presented was focussed on non-product specific IT related topics. I think the fact I came up against the hot topic of GDPR in my session slots and still had a fairly full room was testament to the fact the nobody knows everything, and everyone wants to know a little more on the subjects of Server and SQL optimisation. The feedback at the time certainly reflects that.

I want to once again thank everyone in the EUG EMEA team for having me, the customers who attended the sessions for their perseverance, great questions and feedback and Epicor for letting me attend and present at fairly short notice. Hopefully this will be the start of a new chapter for me as I look to push on into 2018.

For more info on the Epicor Users Group (EUG) please check out the following:
Web: http://www.epicorusers.org
Twitter: https://twitter.com/eug_emea
Conference Twitter: #EUGEMEA2017

2017 Stats

In my last post I mentioned that I thought my install stats were somewhere in the 3-4 per month range, turns out I wasn’t too far off on that.

Last week I spent a little time updating my personal documentation and pulling together numbers etc. more so for my own confidence boost but also so I have some collateral to back myself up.

The stats read as follows:

please note I only started making my own notes outside of time entry/calendars in March 2017:

In 7 Months:

  • 24 Technical Engagements (categorised as over half a day solid consulting, not including “support” style assistance) – >3 per month
  • 22 different customers
  • 6 Go-lives supported (3 new/3 upgrades)
  • 8 international projects supported with training/documentation reviews etc.
  • 5/24 were on site engagements
  • 2 formal sysadmin training cources delivered
  • 4 cloud-based upgrades

So yes, I am in the 3-4 per month category and it’s nice to realise I am in the area I thought!