U4BW Supported Operating System & SQL Server Versions

U4BW – Windows and SQL Server Versions

UNIT4 Business World (Agresso) is now supported on Windows Server 2016 and SQL Server 2016 which can offer enhanced performance, security and high availability features such as SQL Server Availability Groups.

SQL Server High Availability Groups  were first introduced in SQL Server 2012 as an Enterprise Feature. In SQL Server 2016 Standard Edition, Basic Availability Groups are now available.

Basic Availability Groups are a cut-down version of the Enterprise offering. They do however fit quite well with the U4BW setup and can be used to provide a very good High Availability / Disaster Recovery setup for a relatively low cost.

The setup can be:-

  • Both servers on-premise
  • Both servers cloud based
  • Primary server on-premise and secondary server cloud based (Hybrid Cloud)

In each setup there are advantages and disadvantages. But each one offers a great solution compared to a single server.

The following table shows which versions of U4BW are supported on Windows Server 2016 and SQL Server 2016. (Please note, SQL Server 2016 doesn’t need Windows Server 2016 and will run on Windows Server 2012 R2.)

U4BW Version SQL Server 2016 Windows Server 2016
Milestone 5 Yes Requires UPDATE05
Milestone 6 Yes Requires UDPATE03
Milestone 7 Yes Yes

Extended Support for SQL Server 2005 Ended in 2016

Extended support for SQL Server 2005 ended on 12th April 2016. This means that Microsoft are no longer producing security updates for this product. We have worked with Customers recently that still have some SQL Server 2005 instances.

SQL Server 2008 and 2008 R2 still have some life left in them with the extended support running until 2019, although mainstream support for them did stop in 2014. However, some software vendors have dropped support for 2008 and 2008 R2 for their latest software releases.

For further details….

https://www.microsoft.com/en-gb/server-cloud/products/sql-server-2005/

If you need to upgrade your SQL Server instance, then we can help. Our cost effective SQL Server Technical Consultancy is delivered by experienced and certified SQL Server Experts.
Contact us on 01942 366730 or drop us an email a enquiries@intersectconsulting.co.uk


IR35, the Public Sector, Contractors & maintaining critical SQL Server systems

With the ongoing pressure on public sector organisations to cut expenditure and now the additional burden of the new IR35 rules (which come in to effect on 6th April 2017). The new IR35 rules stop contractors being paid via their limited company, which effectively forces them into a PAYE scheme as opposed to paying themselves largely in dividends. This rather large tax burden on many contractors will either force them to leave the Public Sector altogether and switch to the Private Sector or it will push up the daily rate so as to offset the additional tax costs they face.

 

It’s difficult to predict how many contractors will be affected by this issue or what the effects will be on Public Sector organisations. But there is a general expectation within I.T. departments that taking on experienced contractors for interim or project based work will become more difficult.
For some positions the role may be left empty and the responsibilities spread out to other team members. This also happens for roles where the volume of work is not great enough to justify a full time member of staff. We see this at a lot of organisations which operate critical SQL Server based systems, but have no experienced database administrator to manage them.

 

This leads to an approach of only maintaining systems when they stop working. Along with increased system downtime and degraded performance, there is also an additional burden on existing staff to carry out roles which they are not trained for.
Intersect Consulting provide a low cost service that manages and maintains your SQL Server Database environments on a daily basis and also provides an on-demand service when required.

 

Visit our website for further information at www.intersectconsulting.co.uk , several NHS foundations and Local Authorities are already benefiting from our services.

 


SQL Server Merge Replication – Server Migration

We were recently working with a customer that had a SQL Server merge replication topology involving several servers. One of the servers was a fairly old physical server which was running out of resources and disk space. A new physical server was on the network waiting to replace it. The initial plan by the development was to allow for a couple of days of downtime while the 400 GB + merge replicated database was re-initialized from a snapshot. Adding to the complexity of the migration were 39 linked servers, a large number of SQL Logins, over 90 SQL Server Agent jobs and 16 other user databases. Rather than re-initialize the subscription and manually migrate everything over, we decided to use an alternative solution.

These are the steps we took.

  • Install SQL Server on new server Ensure that the installation directory and the system databases directory match what is on the old server.
  • On the new server, shutdown SQL Server and do offline backups of the system databases (MDF and LDF files) then once complete start SQL Server
  • Backup the merge subscription database and copy the backup file over to new server
  • Restore merge subscription database on new server with NORECOVERY so that further transaction logs can be applied. Don’t use the KEEP_REPLICATION option at this time as you can’t use this option with NORECOVERY.
  • Backup transaction logs for merge subscription database and restore them on new server, again using the NORECOVERY option without the KEEP_REPLICATION
  • Stop SQL Server Agent on the old server and check that there are no connections to the subscription database before running the next step. If there are connections the next step will error.
  • Carry out final transaction log backup of the merge subscription database but use the NORECOVERY option when performing the backup. This will put the database on the old server into a RESTORING state.
  • Restore the final transaction log onto the new server, and again use NORECOVERY without the KEEP_REPLICATION option. At this point both the databases will be identical and both in RESTORING state.
  • Shutdown SQL Server on both servers.
  • Set the SQL Server service to be manual on both servers
  • Copy over all of the MDF and LDF files from old server to new server (with the exception of the merge subscription database). The drive letters and directory structures that they are copied to must be identical to the ones they were in on the old server. The system database files have to be copied over as well, they will replace the ones on the new server. (They have been backed up in a previous step).
  • Shutdown the old server. Optionally rename it just in case it ever gets started and brought back onto the network.
  • Rename the new server so that the hostname matches the old servers host name
  • Restart new server
  • Start up SQL Server on new server
  • Set SQL Server services back to Automatic startup
  • Verify SQL Server is online and that the SQL Error log contains no issues
  • Bring the subscription database back online with the following command
restore database dbname with recovery, keep_replication

Because the version of SQL Server had changed slightly when SQL Server was first brought online couldn’t access it as it said it was running upgrade scripts. Once they’d been run it was necessary to run a couple of procedures manually due to the subscriber being offline

sp_vupgrade_replication

sp_vupgrade_replsecurity_metadata

Total downtime was around 20 minutes and replication picked up with no issues as soon as the database was brought back online.


Agresso Business World – Performance Tuning

An Agresso Business World customer had an issue with performance. Once a week several hundred users would all do their timesheet entry into Agresso on the same day. Users frequently complained that the system sometimes ran slow, was sluggish and hung while they were entering their timesheet details. However, this behaviour was intermittent and the issue wasn’t always evident.

So, several hundred users all doing timesheet entry into Agresso in a small time frame were experiencing performance issues.

  • “Is it the Agresso Web Server that is under too much load and is struggling with the number of connections?”
  • “Could additional indexes on the timesheet tables help?”
  • “Is there database blocking going on which is forcing users to wait?”

It was actually none of the above.

The web server was under little load. The connections for the timesheet entry were doing very few reads and there was no sign of blocking.

The issue was traced to a large number of server side processes which were using all of the database servers resources and maxing out the disk and memory.

Server side processes definition in this instance: –  scheduled jobs and services running on the Agresso Application server.

If users connected to Agresso to do their timesheets while one of these large server side processes was occurring, SQL Server had few resources available to service them. This meant that rather than taking milliseconds to process the timesheet entry, it was taking a few seconds.

An unexpected few seconds delay at the application side seems like a long time to the end users.

Basic and some more advanced techniques were used to sort out the long running server side queries. This alleviated the load on the disk I/O subsystem and memory on the database server and enabled it to deliver a fast service to timesheet entry users.

Further ongoing performance monitoring is in place to identify other badly running queries.