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 12th 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

Windows-2008-R2

 

 

 

 

 

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

Agresso-SSLThe 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.

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

 
 
 
 
 
 
 
 

 

 

On the Agresso web server launch the Agresso management console and navigate to the following menu
agresso-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)

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

agresso-uri

 

 

 

 

Withdraw this and then Publish it using the HTTPS option. Do this for the Report Engine and if applicable Web Classic.
agresso-publish
 
 
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