SQL Server | Intersect

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 back online

Welcome to our new Customer Manchester NHS Mental Health & Social Care Trust

Manchester NHS Mental Health & Social Care Trust are now benefiting from our Remote DBA Services. Our Remote DBA Service is a cost effective solution for Organisations who need an experienced production DBA to manage, monitor and administer their systems, but do not have the volume of work to support employing a full time DBA.

Our Remote DBA service benefits include;

systems managed and monitored daily by qualified SQL Server DBA’s
ensuring your data is secure
environment is problem free
performance is at its best
access to an experience production DBA
reduction in overheads
excellent customer service

SQL Server 2005 end of Extended Support

Extended support for SQL Server 2005 ends on 12^th April 2016. This means that Microsoft will no longer be producing security updates for this product. Not sure how many Agresso customers are still running SQL Server 2005 but imagine there are still a few. We have dealt with other customers recently that still have some SQL Server 2005 instances.
For further details….
https://www.microsoft.com/en-gb/server-cloud/products/sql-server-2005/

SQL Server 2008 and 2008 R2 still have plenty of life left in them with the extended support running until 2019, although mainstream support for them did stop in 2014. However, some software vendors (including UNIT4) have dropped support for 2008 and 2008 R2 for their latest software releases.
For further details….
https://www.intersect.co.uk/agresso-business-world/agresso-milestone-5-supported-platforms/

Agresso Milestone 5 – Supported Platforms

A lot of organisations running Agresso Business World are probably aware that Milestone 5 is available. And a certain number will be thinking about upgrading to M5 for either the benefit of new functionality or bug fixes.

It’s useful to be aware in the early stages of planning that Milestone 5 does not support Windows 2008 and 2008 R2 Server, nor does it support SQL Server 2008 and 2008 R2.

For new installations this isn’t really an issue as by default most organisations are rolling out new systems are running Windows 2012 R2 Server and SQL Server 2012 / 2014. But for existing customers who may be planning to use their existing test and live environments that are 2008 based a bit more consideration will be required

Agresso Web Client – Turning on HTTPS

The Agresso Web client can either be run under HTTP or HTTPS. The HTTPS mode provides greater security as the data that is transmitted between the Agresso Web Client and the Agresso Web Server is encrypted.

Report Engine (Excelerator) and Web Services (ipad connections etc) also use the same method to connect to the server.

HTTPS is the recommended method and is strongly recommended for any environments where data will be transmitted over the internet.

HTTPS uses an SSL Certificate which can either be self generated or purchased from a third party certificate authority such as Thawte, RapidSSL, Comodo, VeriSign (now Symantec) etc. Self generated certificates are free, but we would recommend purchasing one from a certificate authority.

Once you have the certificate it requires loading onto the server via the certificate root console. How to obtain and load the certificate is documented by the certificate authority when you purchase the certificate so won’t cover that here. But once it’s loaded onto the server it needs adding into IIS.

Start IIS and go to the Default Web Site and then click on the Bindings menu in the right hand pane.

From the Bindings menu add the HTTPS protocol and select the SSL certificate that you have just loaded onto the server.

On the Agresso web server launch the Agresso management console and navigate to the following menu

Expand the menu node and go to the Protocol option. If the HTTPS is not already there then add it in. Remove the HTTP entry so it looks something like this. Do this step for the Web Services and the Report Engine (and if applicable Web Classic – Milestone 4 only)

Then go to the Publish option and in here you will see that it is currently published using HTTP.

Withdraw this and then Publish it using the HTTPS option. Do this for the Report Engine and if applicable Web Classic.

Please note that when you first withdraw the web service that it may likely just be the hostname of the server. In this instance the server is called agressoweb and Fully Qualified Domain Name (FQDN) of the company name is intersect.co.uk. SSL Certificates would have been normally purchased specifically for this server agressoweb.intersect.co.uk or they would be wildcard certificates which you can install on any server which has a FQDN ending in intersect.co.uk.

Because the certificates are only valid for the exact hostname, after withdrawing the HTTP://agressoweb you would publish the https to HTTPS://agressoweb.intersect.co.uk.

If the FQDN is omitted then the hostname won’t match the name on the SSL certificate and this will give a warning message and a red cross in the menu bar in your browser.

So the URL to the web client that would previously have been http://agressoweb/agresso would now be https://agressoweb.intersect.co.uk/agresso

1 2 345