Category: SQL Server

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

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.

2016 Update

If you have seen my other (more personal) blog you will have come across the post I did recently regarding how 2016 has flown by and so many things have changed career wise. If you’ve not read that feel free to jump on over here: http://johnnyward.me.uk/newblog/a-year-of-two-halves/

As this is my technology blog I though I’d share a quick update on skills and technologies that have advanced through this year, the most noticeable are:

  • SQL Skills – yes I know more than just installing now, I can troubleshoot performance issues and tweak setups to ensure a nice smooth running system.. Maintenance of SQL is also one of my most common tasks across many customers.
  • ERP – a broad subject, but I will break down a little. Firstly I am more than capable of a “vanilla” install of Epicor ERP 10/10.1, I’m also capable of updating/upgrading those platforms. On top of that I can also talk tech with IT/ERP managers across all industries to help spec, and install/deploy the Epicor ERP platforms. This also includes a number of additional installs (such as web, Enterprise Search, education tools etc.) and enhancements, such as those for document management and CRM.
  • Hyper-V – not something I’d had the opportunity to play with much before, however I am now capable of installation, deployment, management and maintenance of Hyper-V 2012+ platforms, and I have to say, what a great platform it is.
  • VMware ESX – my knowledge in VMware has also jumped this year, having had the opportunity to manage 2x ESX 6 environments in recent months and upgrade a couple of 5s to 5.5 I am once again familiar with the VMWare suite and as always find it an exciting challenge.
  • Remote Desktop Services – sure we all know a little Terminal Services from back in the day, but having now deployed around 5 of these 2012R2 beauties this year, i am exceptionally impressed. Easy to install and configure, and just as easy to troubleshoot and fix, provided you have the time and mental space in order to do so!

Coming up in 2017…

So it seems there may be a few changes ahead, ones that will potentially make my career, and about time too!

The biggest 2017 challenges seem to be:

  • Cloud – funnily enough the UK hasn’t quite taken to it fully yet…
  • Solutions – how can a product be further enhanced to meet a customer’s longer term goals…
  • Tools – might need a course in C# and a few late nights

Watch this space…

Tech Update

It has been a while since I posted an update of all the technologies I’ve been working with, projects I’ve worked on and general IT related things that have affected me in recent times, so here’s a brief update of my world of tech!

 

Latest Technologies used

  • VMWare vSphere, vMotion, ESX 5.5 etc. – underlying technology of the infrastructure in my current role, love the live server migrations between hosts, and the performance monitoring is very good.
  • Windows Server 2012 R2 – finally have the opportunity to work with Microsoft’s latest stable enterprise OS, and I have gradually started to like/ understand the new interface. Some of the updates to certain roles are also very impressive including WSUS (Updates), WFCS (clustering), ADDS (Activce Directory), some very nice improvements over Windows 2008 R2 which is the last main release I am familiar with.
  • WhatsUp Gold – systems monitoring software from Ipswitch, what a fantastic product this is. I can monitor all servers, virtual machines, network components, even WLANs (which is one of the most used features). I have heavily customised the environment to have dashboards for all sorts, most notably the SQL performance (most important system) I can now see real-time memory usage/ system statuses, even in-depth transactional performance!
  • SQL (2008 R2) – Something I’ve had to get to grips with very quickly as it runs the 2 most important systems for my current role. I am fairly confident with performance monitoring, understanding heavy queries, bottlenecks, even a grasp of lazy writes and paging! SQL is usually administered by a professional, qualified DBA, but we cope just fine!
  • SQL (2014) – only within a test scenario but I am very proud of my virtualised SQL 2014 Always-On testing cluster, it has given me an opportunity to better understand SQL installs, Windows Failover Clusters, SQL High Availability and basic database maintenance.
  • Cisco Wireless Networking – heavily used within large organisations across the globe, I now have a fantastic understanding and hands on experience with a network capable of almost 100% wireless coverage within an huge site! I also have great experience with security on these networks and have implemented whitelists (MAC filtering) etc. I’ve also been able to ensure the wireless network is 100% monitored across all nodes and devices, with historical data available to help resolve any issues.
  • Windows 10 – I’m a pure techie, so when the latest version of something is out, I have to try it! I was sceptical of the whole Windows 10 thing, even after trialling the pre-release versions around 12 months ago. So when the opportunity came to try at work I though yes that’s safer than at home! – so 2 months in; every device I have is now Windows 10 where possible. Being an IT admin naturally I’ve butchered it as much as possible to better understand its workings, and to be fair so far I am impressed!

Project Work

Whilst the current role hasn’t really thrown any specific projects my way yet, I am actively involved with a domain migration project initiated by the buying out of our division by another company. That is something that will be happening very soon, and will probably consume a lot of my working hours up to Christmas.

I’ve also been spearheading work to implement a standard way of displaying data across site, this has been done using Raspberry Pi with Screenly OSE which allows scheduling of web content, images and videos for display on HD screens. This again is a recent thing that so far is working very well (no reboots/crashed in 3 weeks)

At home I have also been playing with a Raspberry Pi, and have plans to implement a media server to stream content such as from the home CCTV system and family photos etc. Hopefully this will be done before the year is out once I have a screen in the kitchen!

 

 

 

Upgrading SQL Express 2005 to SQL Standard 2008

We have been using the Helm Control Panel for some time now, but thought it would be better to upgrade to the full version of SQL Server.

This was possibly a mistake.

First of all you cant seem to just upgrade to SQL Standard 2008 from SQL Express 2005. So here is my way round.

Backup your HELM4DB using Express management studio and copy this backup to somewhere safe!!!!

Next uninstall all SQL Express products from Add remove programs and reboot.

Once rebooted you need to install SQL Server 2008 Standard or enterprise. I will be using Standard.

***Please note if you are using a 64 bit machine with windows 64 bit os you need to run setup as follows or the install will not work!!***

Launch a command prompt and cd to the setup directory and run the following:>

setup.exe /Action=Install /INSTALLSHAREDWOWDIR=xxxxxxxxxxxx

where xxxxx is the folder where you want it to be installed.

The above should now let you install. If you still get an error change the folders to Microsft SQL Server1 which worked for me.

Once the install is complete if you didn’t install SP! you will now need to or the Management Studio will not work!

Once you can load the Management Studio restore the Helm4DB Backup you took and check the configuration in the Helm Config Tool.

All Done!