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.

Advertisements

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.

A Computer Memory Primer

Here is a quick list of the different types of memory found within a typical computer workstation / server.

SRAM (Static RAM)	DRAM (Dynamic RAM)	DRAM	DRAM	DRAM	DRAM	DRAM	DRAM	DRAM
–	SIMM	SIMM	DIMM	DIMM	DIMM	DIMM	DIMM	RIMM
–	–	–	SDR SDRAM	DDR(1) SDRAM	DDR2 SDRAM	DDR3 SDRAM	DDR4 SDRAM	–
–	–	–	–	2000	2003	2007	2012	–
–	32 Bit	32 Bit	64 Bit	64 Bit	64 Bit	64 Bit	64 Bit	–
		1 –> 128 MB
–	30 Pin	72 Pin	168 Pin	184 Pin	240 Pin	240 Pin (different notch to DDR2)	284 Pin	–
–	–	–	–	SO-DIMM = 200 Pin	SO-DIMM = 200 Pin (different notch to DDR1)	SO-DIMM = 204 Pin	–	–
						DDR3L is a Low Voltage Version

• SRAM (Static RAM) is mainly used within the CPU Cache. It is faster, but more expensive to produce than DRAM.
• DRAM (Dynamic RAM) is essentially Computer Main Memory
• SIMM = Single Inline Memory Module
• DIMM = Dual Inline Memory Module. These can be ECC and / or Registered (Buffered) in Servers and High End Workstations. So, “standard” DIMM modules are also known as UDIMM (Unregistered), whilst registered memory is RDIMM
• ECC = Error Correction Code. An extra “parity” chip on the memory board is used to detect and correct errors. Due to the additional chip (and processing required), ECC RAM is both more expensive and slower than Non-ECC Memory.
• Registered / Buffered Memory.
• SDRAM = Synchronous Dynamic Random Access Memory
• SDR = Single Data Rate
• DDR = Double Data Rate
• SO-DIMM = Small Outline Dual Inline Memory Module (Laptop Memory)

Active Directory: Global Groups; Universal Groups and User Logon

If you are struggling to understand Global Catalog Servers (GCS); Universal Security Groups; and Group Replication, it may help to think about what is actually happening during the user logon process and why.

When a User logs onto a Microsoft Domain Network, two things need to happen. The Logon Service has to:

1. Verify the User Credentials (Username and Password)
2. Determine which “Security Groups” the user is a member of (this establishes the permissions they have on the network)

For the purposes of this exercise, it is the second part of the process that we are interested in.

Obtaining a list of Security Groups is an essential part of the user’s “Access Token”. The access token is generated on the fly each time the user logs onto the network. It acts as a security pass for the duration of the session. So, when the user attempts to access a resource (anywhere within the forest), the operating system compares the permissions on the user access token with the security permissions on the resource (such as a file share) in order to determine if access can be granted.

In accordance with best practices, User Accounts should only be added to Global Security Groups within their Own (Local) Domain. Global Security Groups can then be added to Universal Security Groups in order to grant permissions to resources in other Domains throughout the Forest.

The membership list of Global Groups for the user’s “Local” Domain are replicated to every Domain Controller within that Domain ONLY. So, it is a relatively easy task for the DC that is processing the logon to query its own copy of the Active Directory Database and obtain a list of all Global Groups to which that user is a member. Users cannot be members of Global Groups within other Domains, so finding those is not an issue. So far, so good.

Note: The “Access Token” includes a FULL list of all the security groups that particular user is a member of, plus all of the security groups that those groups are themselves a member of. For instance, if the user is a direct member of “G_Sales”, and “G_Sales” is in turn a member of “G_Contracts”, then BOTH groups will be individually listed on the Access Token. This is very important to remember!

That just leaves us with the task of determining which Universal Groups the user is a member of. Remember, Universal Groups grant permissions to resources within another domain in the forest.

Universal Groups membership is not stored within the database of the Local Domain, so the DC cannot simply query its own Active Directory Database – remember a DC only knows about objects within its own Domain.

Universal Groups “live” in the Forest, and their membership lists are stored on a special type of Domain Controller called a Global Catalog Server. There must be at least one Global Catalog Server (GCS) within each Forest – the very first Domain Controller within the Forest is automatically designated the role of GCS. So, the DC processing the user logon must be able to contact a GCS within the Forest to find out which Universal Groups the User is also a member of.

Note: if a GCS cannot be found, the logon process will Fail!

When all of the Group Membership information has been gathered, the “Access Token” will be generated and issued to the user account.

Access Token for “DomainA.User A”

Group Membership List:

G_Sales

G_Contracts

U_Sales

Hopefully, you can now see why it is important that ALL security groups are listed within the Access Token (even if membership is indirectly a result of “G_Sales” being a member of “G_Contracts”). If ALL Security Groups were not listed within the Access Token, the User would be denied access to “G_Contracts” and “U_Sales” resources, and would only be able to access “G_Sales” resources.

XAMPP 1.7.3 for Windows: Folder Structure

The following is a summary of the contents of each folder within a Windows XAMPP installation.

Folder	Description	Version	Notes
Anonymous
Apache	Web Server Service	2.2.14	Configuration file is: “apache\conf\httpd.conf”
cgi-bin
FileZillaFTP	FTP Service	0.9.33	Configure using the XAMPP Console.
htdocs	Web Site Pages
install
licenses
MercuryMail	SMTP Mail Server Service		A simpler alternative is to configure an ISP Mail Server within “php\php.ini”
MySQL	MySQL Database Files	5.1.41	Configure using PHPMyAdmin
Perl	Perl Scripting Language Folder	5.10.1
PHP	PHP Scripting Language Folder	5.3.1	PHP is enabled by Default. Configure within “php\php.ini”.
PHP\ext	“DLL” Files		There are several “dll” files to be found with “php\ext” to support additional functionality (such as SQL Server). These are enabled / disabled within “php.ini”).
PHPMyAdmin	MySQL Administration Tool	3.2.4
Security
Sendmail	“Fake” Sendmail Application		Configure within “sendmail\sendmail.ini”. A simpler alternative is to configure an ISP Mail Server within “php\php.ini”.
Tmp
WebAlizer	Web Server Usage / Analysis Tool
WebDAV

 

Note: XAMPP 1.7.7 is the latest version that can be installed on Windows XP / 2003. All newer versions require Windows Vista / 2008 or newer.

Sage 50 HR Firewall Ports

Sage 50 HR requires the following Firewall Ports to be open on the Server:

TCP 4197
TCP 5150

Configuring Alerts within the Avast ADNM Console

In order to receive an Administrative Email Notification each time a Virus is detected by the Avast Workstation Client, it is necessary to configure the following settings within the “Avast Distributed Network Manager” (ADNM) Console:

[1] Basic SMTP Settings
 
[2] SMTP Settings within the “Computer Catalog” Properties

[3] Add an SMTP Alert
 
The “Net Alert Address” is the Address that will receive the Alert.
 
At this point, use the “Test” Button to make sure the Alert is properly generated. A “Virus Warning” Test message should arrive at the nominated Mailbox.

[4] Configure the “On Access Scanning Tasks”

 
This will ensure the Alert "fires” when a Virus is detected.

[5] Configure the “On Demand Scanning Tasks”

These should be configured in exactly the same way as the “On Demand Scanning Tasks”