XAMPP: Using MySQLDump to Backup MySQL on Windows Server 2003 and Windows 7

I couldn’t find anything specific for Backing up the MySQL Databases within XAMPP. There certainly isn’t a pre-configured routine available. In addition, MySQL Workbench (unlike its predecessor MySQL Administrator) does not provide any automated backup facility.

So, I decided to explore the use of “MySQLDump”, a command line routine that can be inserted into a batch file and executed as a “Scheduled Task”. “MySQLDump” is included as part of XAMPP and is found within “<path to XAMPP>\MySQL\bin\”.

Here is the basic syntax I used (from a command prompt):

<path to XAMPP>\MySQL\bin\MySQLDump –u root –p myrootpassword –result-file=”<path to Backup Folder>\MySQLDump.sql” –all-databases

I then copied this into a batch file (MySQLDump.bat) and used “System Tools => Scheduled Tasks” (Task Scheduler in Windows 7) to run the batch file each evening.

Of course, you must ensure that the “MySQLDump.sql” file itself is stored in a safe location and can be recovered in case of server failure / building loss.

Advertisements

Fitting USB3 into a DELL PowerEdge R510 Server

USB3 is not supported natively within Windows Server 2008 (or Windows 7), and therefore Dell has always seemed reluctant to fit USB3 hardware into its Servers.

To make matters worse, most USB3 cards require some sort of SATA Power from the main board, and Dell does not provide these connectors inside the chassis either. So, fitting a PCI Express USB3 card is a tricky proposition.

Fortunately, StarTech offer several USB3 Cards that do not require any additional power from the board (save that provided by the PCI Express slot itself). However, be aware that if you are intending to connect an external Backup Drive to the card, you will need to ensure that the device itself is powered.

I chose the StarTech PEXUSB3S42 Card. It provides three external USB 3.0 Ports (another port sits inside), and was a breeze to install.

The first image (below) shows my R510 Server prior to fitting the Card, and the second shot shows the new card in place.

For reasons explained above, it was neccesary to run the driver installation disk (provided in the box), and this resulted in a further reboot of the server.

My HP RDX Backup System is now connected to a much faster port and the backup times have reduced accordingly.

All in all, a successful upgrade.

PS, don’t forget to use a USB3 lead when connecting your External Drive, or you will still be running at USB2 speeds. 😉

 

 

 

SQL Server: Clustered Indexes

When creating a new table within SQL Server Management Studio Express (SSMSE), it will not have an index or a primary key by default.

A primary key is not mandatory, but from a performance point of view, it nearly always makes sense to create one. The Primary Key is a UNIQUE “Index” Value in a Database Table, such as the “Customer Code” in a Customer List (or the “Order Number” in an Order Register).  A table can have only one Primary Key.

To create a Primary Key within SSMSE, right click the desired field within the table and click “Set Primary Key”. If there are any issues saving the table design changes, follow the instructions here.

Once a Primary Key has been created, that particular database field (column) cannot contain duplicate values.

You will now notice that a new “Key” has been created for the table, along with a “Clustered Index”.

Clustered Index
A Clustered Index ensures that the data within the table is stored in exactly the same physical order as the index itself. A clustered index does not have to be unique, but it will be if it is associated with a primary key (as above).

By definition, there can only be ONE clustered index per table.

A table without a clustered index is called a “Heap”.

Non-Clustered Index
Since there can be only one clustered index per table, all other indexes must be Non-Clustered. These do not affect the way in which the data is physically stored.

Sage Payroll: No Updates were installed

Occasionally, you may find that Sage Payroll “Software Update” will continue to prompt for an update that has been installed previously.

After hitting “Install Now”, you get a further notification that “No Updates Were Installed”.

The next time you start payroll, it will repeat the whole process again!

Fixing this is easy enough. Just delete the “softwareupdates.dat” from the “C:\ProgramData\Sage\SBD Software Updates” folder on the workstation.

You should now receive the correct notification that your software is up to date.

SAGE 200: Purchase Ledger Contacts Schema

Here is another one in a series of articles documenting the Sage 200 Database Schema.

This time, I was struggling to generate a list of “Email Contact Addresses” for Remittances Advice within the Purchase Ledger. Click here to download a PDF copy of notes I made whilst decoding the Sage PL Contacts Schema.

Hopefully, you will find them useful in similar circumstances.

Microsoft IIS: Configuring a Domain SSL Certificate

In a previous article, I explained how to host a second web site within IIS and how to configure the server bindings so that both sites (the new site and the default site) were able to listen on “Port 80”.

I would like to expand upon this by adding a Domain SSL Certificate to the server, so that traffic between our newly created web site and the client is encrypted.

Let’s begin by reminding ourselves that the address of the web site we created in the previous article is “http://8g7yf4j-www2” and the default web site is “http://8g7yf4j-win7” (yours may be different of course).

By the end of this exercise, we will have a secure site with an address of “https://8g7yf4j-www2” (note that the “http” prefix is now “https”).

The process is basically two stages:

1. Create a new Domain SSL Certificate within IIS Manager
2. Create a new “SSL” binding within IIS Manager

So, let’s get on with creating a new Domain Certificate that will be used to encrypt the traffic.

From within IIS Manager, select the server hosting the web site, and then click “Server Certificates”, and then “Create Domain Certificate”. It is important to select “Domain Certificate”, as it will automatically be trusted by all of the computers within the Domain Network.

Type the host name of the Web Site (8G7YF4J-WWW2) in the Common Name box, and pad out the remaining fields as necessary before hitting “Next”. Remember, this is the host name you entered in the the binding in the previous exercise (not the actual host name of your server).

On the “Certification Authority” screen, The Certificate Authority (in most cases) will be your Domain Controller, and the friendly name will again be your Host Name (8G7YF4J-WWW2). Click “Finish” to create the certificate. It is important that the Host Name stored within the Certificate matches the Web Address, otherwise you will receive a Certificate Error when you visit the Web Page.

The final step is to add a new Binding on Port 443 for the Web Site. You should be familiar with this process from the previous exercise.

The Details you need to enter into the Binding are shown in the table below.

Type	https
Host Name	8G7YF4J-WWW2
SSL Certificate	8G7YF4J-WWW2

Make sure you select “https” as the type (and not “http”). Click “OK” when you are done, and note that you now have two bindings for your Web Site:

1. Port 80 (Unencryped)
2. Port 443 (Encrypted)

At this point, you can restart your web server (for good measure), and test the new certificate from a workstation by typing “https://8G7YF4J-WWW2” into a Web Browser.

After the page has loaded, you can click on the padlock icon and view the details of the certificate.

Microsoft IIS Bindings and Hosting Multiple Web Sites

This article covers the configuration of multiple web sites within a single installation of Microsoft Internet Information Server (IIS). In order to do this, we need to establish an understanding of how “Bindings” work.

An “out of the box” installation of IIS will create a “Default Web Site” that listens on Port 80. This is the standard “listening” port on which web sites are hosted. In technical terms, we “Bind” the Default Site to Port 80.

If IIS is running on your network server, you can test the site from any workstation on the network by typing the “IP Address” or the “Host Name” of the Server into Internet Explorer. In my case, this is “http://192.168.200.62” or “http://8g7yf4j-2012r2”.

This is all pretty straight forward so far. The situation becomes a little more complicated if you decide to host a second web site within IIS. Since the default Web Site is already “listening” on Port 80, it isn’t entirely obvious how we can run two web sites on the same server without moving one of the sites to a different port (such as 8080). This is where we need a little more knowledge of how to properly configure the bindings when we are running multiple sites.

To demonstrate this, we will go through the process of adding a second Web Site within IIS.

The first step is to create a new folder within “C:\inetpub\” to hold our Web Site assets. For convenience, I have named mine “www2root” (the default site is named “wwwroot”).

Now that we have created the (empty) folder, we can go ahead and choose “Add Website” from within IIS Manager. I used the following settings:

Site Name	www2root
Physical Path	c:\inetpub\www2root
Host Name	Leave this blank!

Click “OK”, and you will receive a warning to the effect that you are trying to bind two web sites to the same port.

For the time being, we will accept this warning.

You can immediately see that our new web site is not running, and it cannot be started without first stopping the default web site. So, we need some mechanism by which IIS can differentiate between the two sites, and allow them to exist in harmony. The way that we do this is by utilising the “Host Name” field within the binding settings. You will hopefully recall that we left this entry blank in the previous step.

Choose “Edit Bindings” for the “www2root” web site and set the host name field to something other than the host name of your server. For example, the hostname of my server is “8g7yf4j-2012r2”, so I have set the value of the field within the bindings to “8g7yf4j-www2”. Click “OK” and “Close” when you are done.

Site Name	www2root
Physical Path	c:\inetpub\www2root
Host Name	8g7yf4j-www2

If you got everything correct, you should now be able to start both Web Sites.

There is now just one more task to perform. If we are going to use the “http://8g7yf4j-www2” host name to connect to the new site, we need to add this information to our LOCAL DNS Server as an Alias “CNAME” record. I’m not going to describe how to do this in detail (as it is beyond the scope of the article), but the information I entered in the DNS settings is shown below.

Alias Name	www2root
FQDM	8g7yf4j-www2.mydomain.co.uk
FQDN (Target)	8g7yf4j-2012r2.mydomain.co.uk

We should now be able to access both Web Sites from a Workstation Web Browser as follows:

• Default Web Site (wwwroot): https://8g7yf4j-2012r2
• New Web Site (www2root): https://8g7yf4j-www2

You will most likely receive a “403: Access Forbidden” page when you try to load the new web site. This is purely because the web directory we created is empty, and so there is no content to display. If you wish, you can create a basic “index.html” file and place it within “c:\inetpub\www2root” to satisfy yourself that everything is working properly.

To summarise, if you wish to have multiple sites running within IIS, each site must have a unique binding (and appropriate CNAME entry within DNS).