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).

SQL Server Browser Service and “Network Discovery”

Client Applications normally connect to the Default SQL Server Instance on TCP Port 1433. Additional Instances are normally allocated Dynamic TCP Port Numbers by the Server Operating System.

For the benefit of Clients, Microsoft SQL Server 2005 provides the “Browser Service” (UDP Port 1434) to advertise the Names and Port Numbers of each Instance of SQL currently installed.

When a Client requests access to a particular Instance, the Browser Service responds with the appropriate Port Number and the client uses this to establish a connection.

If the Client is a Remote Client, Windows Firewall (on the SQL Server) needs to be configured to allow inbound traffic to the ports used by:
[1] The SQL Browser Service (UDP 1434).
[2] The Default SQL Instance (TCP 1433).
[3] Additional Named Instances (Dynamic TCP Ports).

It is easy to see that in this scenario, it is impossible to pre-configure the Firewall for a Dynamic Port (since we do not know what the port number is going to be). Therefore, additional Instances should be allocated a “Fixed TCP Port Number” using the SQL Server Configuration Manager. In this way, the correct firewall Exceptions can be configured.

Choosing a Fixed Port
To view the current Dynamic Port of a particular Instance, use the “SQL Server Configuration Manager” (see below).

To turn this into a “Fixed Port”, remove the current value from the “TCP Dynamic Ports” field and place it into the “TCP Port” Field. Restart the SQL Service. It will now be a case of Configuring the SQL Server Firewall to pass traffic to the Fixed TCP Port (in this example Port “1266”).

SAGE 2010: Adding a Custom Menu Item

Each Sage 2010 User is associated with one or more “Roles”. Users with multiple roles can use “Switch Configuration” from the Sage 200 File Menu to choose the most appropriate one.

It is the “Role” (set within Sage System Administration) that determines the Menu Items they are presented with.

In Order to add a New Menu Item to a particular Menu, we need to complete the following Steps:

1. Add a New Feature / Target (set within Sage System Administration)
2. Authorise the New Feature for the Role (set within Sage System Administration)
3. Add a New Menu Item and Link to Target (using Sage Menu Designer)

In this example, we are adding a “Sales Orders (Live)” report to “Construct” Role within Sales Order Processing.

Step 1: Add a New Feature
Features apply to the system as a whole. Start up Sage System Administrator: Sage Tools => Sage 200 => System Administrator.

Find a suitable place within the features structure and choose “Add Menu Item” from the Context Menu. In this case, we are adding a new “Feature” to “Sales Order Processing”.

For consistency, populate the “Name” field with the name of chosen Menu Item. Leave the default GUID.

Click OK.

Still within “Features”, locate the newly added Item and add a new “Target” as shown below.

Note:

• A “Target” is simply a shortcut to a Report / Program that can appear as a Menu Item (within any Menu).
• A Feature can contain multiple “Targets”.
  

Fill in the details using the screen shot below as a guide. Leave the “Target GUID” field alone.

Step 2: Authorise the New Feature within the Menu
Start up Sage System Administrator (if you have not already done so): Sage Tools => Sage 200 => System Administrator.

From within “Roles”, select the appropriate “Role Name” (that matches the Menu) and then select “Features”.

Locate the new Menu Item within the Tree and ensure that “Sales Orders (Live)” is checked.

Click OK.

Step 3: Add a New Menu Item
In this final Step, we will actually create the Menu Item and link it to the “Target” created above.

Start up Sage 200 / MMS using a valid Username and Password (must be an Administrator) and select “Menu Design Mode” from the Tools Menu.

Select the Appropriate Menu (Desktop) using the Desktop Design Toolbox. Since we are adding a new item to the “Construct” menu, we need to choose “Construct” from the list.

Find a suitable place in the menu structure, then “Right Click” with the mouse and choose “Add Menu Item”

Use the example below as a guide to completing the fields.

Choose “Existing Target” and then “Select Target”.

Locate the newly added Feature and then Click “Select.

Leave the default Icon alone. Click “Save”.

To move the new Menu Item further up the Menu Tree, select “Move Up” on the context Menu.

Save the Menu Changes when complete.

Close the Menu Designer and return to Sage 2010.

That’s basically it. All users using the “Construct” menu should now have a new report to play with.

Stephen Holder
28 August 2015