Tip of the Week 1 – IIS Application Pool Recycling

This guide was designed for use with Epicor ERP10 products, however applies to any and all IIS Application pools which you may wish to set to auto recycle.

To Set Automatic Application Pool Recycling:

  • Open IIS on the App Server
  • Under Application Pools, select the one you wish to configure:
  • On the right pane click Recycling (not Recycle!)
  • The Edit Application Pool Recycling Settings window appears, one recommendation would be to set a Specific Time out of business hours, and outside of any other ERP processes e.g. MRP.
  • Note that this allows recycling to be set at individual application pool level, you should check which applications this affects by reviewing the applications set to use this pool. To do this when the pool is selected, click the View Applications button the right pane (or by right clicking)
  • An alternative to individual app pool recycles is to do a full IISRESET which will restart all IIS related services and applications. To do this it’s best to setup a Windows Scheduled Tasks to run the command IISRESET will full administrator privileges.

Happy New Year

Wow, 2018 has come around already and its time to yet again share some techy tips and posts that will hopefully not bore everyone too much.

I hope everyone who celebrates the festive season enjoyed it and are feeling as refreshed as I am, ready to hit the ground running for this year?!

 

I will start next week properly, although content is already in the works, I am going share Windows Server and SQL tips going forwards to widen the reach a little, so please share the posts to anyone you feel may benefit.

 

 

SQL Server Tip of the Week – Winter Break

Unlike the Premier League, I will take a winter break from the SQL Tips,

Most likely I will use the break to build up the quality and quantity of content for an awesome 2018.

On that note I’ll leave you with this link to get your systems ready for the weather… 

https://wateroxconsulting.com/archives/prepare-for-winter/ 

 

Thanks for visiting!

SQL Server Tip of the Week – Avoiding restore errors

Quick tip for this week as I have not been home much to contemplate writing something epic, although I have a couple lined up for future…

This week, my quick tip is for avoiding errors when restoring over an existing database.

It is common practice for many enterprise applications to have the concept of a Live/Production environment and a Test/Development environment. Some products will see you have around five or six different ones, especially during implementations of new systems or upgrades. Typically, I work in a world where you have three environments with your data and one with sample/demo data.

I will be writing a more in depth backup/restore guide, but I wanted to cover some generics to start with.

One common task is to take a copy of live data and restore it into a test or development area so that you can try things out without risking critical data. Some companies may even have policies where they do this weekly or monthly to have a “pristine” copy of their data. When this is, a repetitive task it’s sometimes best to script it out, especially where the process is identical each time, the problem with this though is ensuring the destination database is in a state where the restore can happen. And whilst we can script in the Single-User mode and various other options, we cannot 100% safely validate and kill existing sessions, what if a session to that database is from a business critical system you forgot to switch off before the process began?

Therefore, my recommendation is to use the SQL Server Activity Monitor. This tool is also a starting point for identifying performance issues such as long running queries or table locks.

  1. To access the Activity Monitor load up SQL Server Management Studio, log in, and then right click the Server node (top) in the Object Explorer, under the menu you will see Activity Monitor:

  2. To find whether your destination Database has any connections to it you need to expand the processes section, in there you will see a list of every database connection.

  3. A quick way to see if your database has any connections is whether or not it appears in the database column, you can click on the header to filter on DB name, if yours is not there then it doesn’t have any connections, if it is then filter on it so you can see what connections you have:

  4. If there is only 1 or 2 lingering connections you could right click on them row by row and kill the connection, however if, like above, there are many, I would suggest looking at the Hostname column and seeing what is running on that machine (as the user listed in Login column) to try to identify what you forgot to switch off before attempting the db overwrite.

This tip has saved me many times, it give you a quick view of access to a database, and is the first port of call should you get any “can’t overwrite, file in use” style errors.

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!