SQL Server File Locations for Default Instances

Each Instance of SQL Server has its own set of Executables (sqlservr.exe) and Data Files. The file locations for the default instance are different depending upon the version installed. Essentially, this is to allow multiple versions of SQL Server to happily co-existing on the same server.

The table below lists the default file locations (as they appear on my systems).

Version	Location of Database Engine	Additional Instances	Service Names
SQL Server 2000  & MSDE(v8)	C:\Program Files\Microsoft SQL Server\MSSQL	MSSQL$MyInstance	MSSQLSERVER; MSSQL$MyInstance
SQL Server 2005 (v9)	C:\Program Files\Microsoft SQL Server\MSSQL.1\	MSSQL.2; MSSQL.3…
SQL Server 2005 Express (v9)	C:\Program Files\Microsoft SQL Server\MSSQL.1\	MSSQL.2; MSSQL.3…
SQL Server 2008 (v10)	C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER	MSSQL10.MyInstance	SQL Server (MSSQLSERVER); SQL Server (MyInstance)
SQL Server 2008 Express (v10)	C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS	MSSQL10.MyInstance
SQL Server 2008 R2 (v10.25; v10.5)	C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER	MSSQL10_50.MyInstance	SQL Server (MyInstance)
SQL Server 2008 R2 Reporting Services (v10.25; v10.5)	C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER	MSSQL10_50.MyInstance	SQL Server (MyInstance)
SQL Server 2008 R2 Express (v10.25; v10.5)	C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS	MSSQL10_50.MyInstance
SQL Server 2012 Express (v11)	C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS	MSSQL11.MyInstance	SQL Server (MyInstance)
SQL Server 2014 Express (v12)	C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS	MSSQL12.MyInstance	SQL Server (MyInstance)

 

Common Files
Additionally, multiple instances (within each version) share some common files.

Version	Location of Common Files	Notes
Common Files (2000)	C:\Program Files\Microsoft SQL Server\80\
Common Files (2005)	C:\Program Files\Microsoft SQL Server\90\	The 2005 installation also creates an  “80” folder.
Common Files (2008)	C:\Program Files\Microsoft SQL Server\100\
Common Files (2012)	C:\Program Files\Microsoft SQL Server\110\
Common Files (2014)	C:\Program Files\Microsoft SQL Server\120\

 

The Browser Service
There is only ever one copy of the SQL Browser Service running (and it always lives in the same folder). Normally, it will be automatically upgraded to whichever is the latest installed version of SQL Server.

The Browser Service listens for requests on UDP Port 1434 on behalf of all the installed SQL Server Instances that are installed on the Server (irrespective of version).

Version	Location of Browser Service	Notes
SQL Server 2000 Listener Service		Requires SQL Server 2000 SP3a
SQL Server 2005 Browser Service	C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe	Version 2005.90.5000.0
SQL Server 2008 Browser Service	C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe	Version 2007.100.5500.0
SQL Server 2008 R2 Browser Service	C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe	Version 2009.100.1600.1
SQL Server 2012 Browser Service	C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe	Version 2011.110.2100.60
SQL Server 2014 Browser Service	C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe	Version 2014.120.2000.8

 

64 Bit Windows
Within 64 Bit Windows, some files may be installed within “Program Files (86)” rather than “Program Files”.

SQL Server Compact Edition
These editions have very little in common with the SQL family as discussed in this document. SQL Server Compact Edition does not run as a service, nor is there any concept of database instances. The default installation directory is “C:\Program Files\Microsoft SQL Server Compact Edition\” and a file type of “sdf” is used for each data store.

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

 

 

 

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

Group Policy: Disabling Internet Explorer Add-Ons

If you want to enable / disable an Internet Explorer Add-On via group policy, you will firstly need the “Class ID” of the Add-On.

Within IE, select “Manage Add-Ons” from the Tools Menu. From there, select your Add-On and click “More Information”. For the purposes of  this example, I have chosen the “Java™ Plug-In SSV Helper”.

You can use the “Copy” button to place all the details into the clipboard (as I have done in the table below).

Below shows some sample Add-On information (we only need the “Class ID” in order to disable the Add-On).

Name: Java(tm) Plug-In SSV Helper Publisher: Oracle America, Inc. Type: Browser Helper Object Architecture: 32-bit and 64-bit Version: 8.0.450.14 File date: ‎17 ‎April ‎2015, ‏‎12:17 Date last accessed: Not available Class ID: {761497BB-D6F0-462C-B6EB-D4DAF1D92D43} Use count: 0 Block count: 0 File: ssv.dll Folder: C:\Program Files\Java\jre1.8.0_45\bin

Name: Java(tm) Plug-In 2 SSV Helper Publisher: Oracle America, Inc. Type: Browser Helper Object Architecture: 32-bit and 64-bit Version: 8.0.450.14 File date: ‎17 ‎April ‎2015, ‏‎12:17 Date last accessed: ‎10 ‎July ‎2015, ‏‎11:52 Class ID: {DBC80044-A445-435B-BC74-9C25C1C588A9} Use count: 0 Block count: 3125 File: jp2ssv.dll Folder: C:\Program Files\Java\jre1.8.0_45\bin

Finally, we need to configure the Group Policy itself.

Within a suitable Group Policy Object (GPO)..

• Navigate to: User Configuration => Policies => Administrative Templates => Windows Components => Internet Explorer => Security Features => Add-On Management
• Enable the Add-On List” setting
• Click the “Show” button
• Click the “Add” button
• Copy the “Class ID” value you obtained earlier in the exercise, and copy it into the “Name” field
• Set the “Value” field = 0 to disable to add-in (make sure you include the brackets): 0 = Disable; 1 = Enable
• Click “OK” to save the data

Add any other items to the list as required.

Click “OK”, and you’re done.

Give the policy time to refresh on your network, and you should find that all of the Add-On(s) have been disabled.

WSUS: Recovering Disk Space

Over time, my WSUS (Windows Server Update Services) Server began to consume alarming amounts of disk space, mainly due to neglect on my part. So, it was time for a bit of a tidy up.

Declining Superseded Updates

The most important thing to realise is that (even though there were over 30,000 items listed within the database) the only updates that are consuming disk space are the ones that have been approved. WSUS does not download updates that have a status of “Unapproved” or “Declined”.

So, lets have a look inside the console.

Go to “All Updates” and filter the results to “Approval: Approved” and “Status: Any”

From the screen capture (above) it can be seen that I have almost 5000 approved updates filling up my server drive. The download folder was in excess of 50GB.

All of these updates would have been required at some point, otherwise I would not have approved them. However, a significant proportion of these will have been “Superseded”. In other words, Microsoft has issued an update that replaces a previous one, and therefore only the newer one is required.

The first step will be to identify and “Superseded” updates and “Decline” them.

The easiest way to identify “superseded” updates is to show the “Supersedence” column within the WSUS Console.

The supersedence icon next to each update will enable us to identify updates that are no longer required:

	These updates (and any updates without an icon) have not been superseded. These updates are “current” and we need to keep them.
	These updates have been superseded, and (in most cases) are surplus to requirements.

As stated above, we are only interested in superseded items. Anything that has not been superseded may be needed.

So, let’s go ahead and select a few superseded updates and “Decline” them.

You should only select updates that have been superseded, and where the “Needed” count is zero and the “Installed / Not Applicable” value is 100%.

Just select a dozen or so items to begin with. Then, click on “Decline” (and “Yes” to confirm).

WSUS Server Cleanup Wizard

After “Declining” the updates, Run the WSUS Server Cleanup Wizard. You should see some “Unused” updates have been removed and some disk space has been recovered.

When you are satisfied with the results, go ahead and decline a larger batch of updates. Remember to make sure that they are:

• Superseded
• Needed Count = 0
• Installed / Not Applicable = 100%

A Warning about “WSUS Updates”

These are updates to the WSUS Service itself. If you have inadvertently declined any of these updates, you will need to re-approve them. Otherwise, the WSUS system will not operate properly.

Within “Updates”, go to the “WSUS Updates” container and approve all updates that are listed as “Not Approved”.

“Un-approving” (rather than “Declining) Updates

It is just as valid to “un-approve” updates as it is to “decline” them. However, I found that when an update is “un-approved”, the WSUS Cleanup Wizard waits for 30 days before deleting the download files from the WSUS folder.

Creating a Shared Resource Calendar in Exchange 2007

It’s often handy to create a shared calendar within your organisation for booking rooms and other shared resources such as cars and laptops.

Outlook / Exchange 2007 makes this easy to achieve.

Firstly, create a shared Calendar (within Public Folders) using Outlook. For the purposes of this example, let’s call this “Pool Cars”.

  

The next stage is to create the shared  resource(s). This must be done within “Exchange Management Console” on the Exchange 2007 Server (Start => All Programs => Microsoft Exchange Server 2007 => Exchange Management Console). Again, it is a simple process using the “New Mailbox” Wizard. You should choose either a “Room Mailbox” or an “Equipment Mailbox”.

Use the table below for completing the details within the wizard.

Mailbox Type	Room Mailbox or Equipment Mailbox
User Type	New User
User Information	Fill in the User (Resource) Information as required.
User Logon Name	PoolCar01
Password	Anything valid will do (the account will be disabled)
Alias (Email Address)	PoolCar01
Mailbox Database	Set as per your organisation
Managed Folder Mailbox Property	Set as per your organisation

Now that the Resource Mailbox has been created, it needs to be configured to “Auto Accept” Meeting Requests. This must be done within the “Exchange Management Shell” on the Exchange Server (Start => All Programs => Microsoft Exchange Server 2007 => Exchange Management Shell). Type the following command into the shell:

Set-MailboxCalendarSettings "PoolCar01" -AutomateProcessing AutoAccept

To check that the value has been set correctly, type:

Get-MailboxCalendarSettings “PoolCar01" | fl

That essentially completes the process. You can create as many shared resources as your organisation requires (you do not need to purchase additional CALS for these mailboxes).

Additional Notes

By default, a resource cannot be booked for more than 24 hours (1440 minutes). This setting can be adjusted using the Exchange Management Shell. The example below extends the time to 5 days (7200 minutes):

Set-MailboxCalendarSettings "PoolCar01" –MaximumDurationInMinutes 7200