Installing SQLExpress 2008 R2

Notice:

Microsoft recently released latest version of SQL Server, SQL Server 2008 R2, available in the Express edition as well you can download from Microsoft web site;
Supported Operating Systems are, as indicated in the release notes the following operating system: Windows 7; Windows Server 2003; Windows Server 2008; Windows Server 2008 R2;
this seems to exclude Windows XP from the available installation scenarios, but in the following experience we were able to install SQLExpress 2008 R2 on Windows XP service pack 2;
please be warned that Microsoft dropped Windows XP from the available OS and eventual use of such will result in an unsupported scenario.

Current goal: installing SQLExpress 2008 R2 with Advanced Services and related tools.

Running the SQL Server Installation Center, the choice is for "a new installation.."

After the traditional rules check

we are prompted with the Und User License Agreement acceptance..

The next step is defining the feature we like to install

Having that done, we have to configure the Instance to be installed, choosing to install a named instance named, as default setting, SQLExpress

following Microsoft Best Practices, we elected to run the SQL Server service in an appropriate limited context, so we created a local machine limited account via the traditional account management dialog: the account will be in this case named SQLServer to help understand the meaning and the reasons of it's nature..

so that limited account is set to run the SQL Server service

as finally resulting in the Server Configuration dialog

we elected to later use a static assigned TCP port for the TCP/IP network protocol communications, so the SQL Server Browser can be directly disabled at this setup stage.

As regards the Collation setting, the wizard proposed us a SQL collation but we did an override, setting a traditional Windows collation based on our Latin codepage, with accent sensitivenes constraint

and finally we have to set the Engine configuration settings, where mixed mode authentication can be authorized, providing an appropriate (please use a strong one) password for the traditiona sa standard SQL Server system administrator's login

the dialog reports the Windows account(s) that are currently defined to be added to the SQL Server administrators (sysadmin) server role, and we currently want to override that setting including all local machine (at operating system level) administrators members

We do not want to change default settings about the NTFS file system folders that will host the instance installed files, so we just checked them for our curiosity

but we like to modify the settings about the FILESTREAM related features in order to enable them

There we are, quiet ready to go, with a final kindnes to help the Microsoft SQL Server team to know about our installation process and SQL Server running status

and finally the installation process can start..

It took about 30 minutes to complete the installation on this virtual machine but we finally are rewarded with it's succesful completation

We can start checking the file system for further evidence of this success, and we can find a Program Files\Microsoft SQL Server folder where the instance has been installed, and the naming convention is MSSQL10_50.InstanceName as indicated in the dialog

and the very same installation success is in evidence accessing our Programs where both a Microsoft SQL Server 2008 R2 group and a Microsoft SQL Server 2008 one are available.. the latter only include access to SQL Server Configuration Manager tool

Going futher, the Services management applet indicates the status of the brand new installed services

It's finally time to start finishing the configuration settings of our new installed instance: as already indicated, we like to set our instance to listen on a static assigned TCP port rather than using a dynamically assigned one, thus allowing us to disable the SQL Server Browser service but requiring us to always provide a full qualified connection string inluding the port SQL Server will listen to, or defining an appropriate alias for each remote client

NB:
loosely speaking, the SQLBrowser is a companion service provided to resolve connectivity for remote connections.. Microsoft only had the chance to register 1 well known port for SQL Server on IANA, tcp/ip 1433 port, the traditional one used, default setting for all default instances.. This does not mean each and every SQL Server default instance is listening on that port, as lot of sysadmins change it to a different one (again, this one is a well known one) to limit attacks to SQL Server instances [I'm one of them :)], but this is another story :)
Having SQL Server (since v.2000) provided the fetaure to install more than a single instance per machine, as it was till SQL Server 7.0, the team had to address remote connections settings for those named instances.. Their choice was to install them without specifying a particular port but letting the service (each instance) the freedom to chose a free one at startup, resolving to a dynamically assigned port. So you can specify a static assigned port or a dynamic assigned one, that, at every and each startup, will be chosen among the free ones; usually this one does not change, but potentially can. In order to mitigate this problem, the SQL Server Resolution Service (SQLBrowser ancestor) has been implemented, a service listening on another well known port, UDP 1434, that intercepts all incoming connections, resolving the address with a local registry lookup, and finally redirecting the connection to the port the desired SQL Server instance is listening on.. to support this big change, MDAC stack had been modified in order to allow redirection, feature available on the MDAC 2.6 version.. so this service is required if a SQL Server instance has been set to listen on a dynamically assigned port... it is not required if all of them have been set to a static assigned one and your connection strings specify that port at connection time (or an Alias is specified for the server, where the listening port is indicated).. if this is the case you can, and I usually do, stop SQLBrowser. It has been reported in the past that the additional lookup to resolve port address references was slowing down connections. In our personal case, we do stop it to limit the attack surface area as well. These are the "essential" features exposed by the SQLBrowser service..

Modifying the port assignement settings requires us to restart the service

And finally we can access the new SQL Server Management Studio (basic) management tool

to dynamically interact with our SQL Server instance

Our policies require us little more settings changes, as we like to disable User Instance feature but enable the SQLClr integration and xp_cmdshell availibility:
both of these changes are to be done if and only if required as they do enlarge your surface attack area, so please verify your requirements before enabling them.
These changes can be performed accessing the instance's facets

and in the Server Configuration facet we can address our requirements

As we are in SQL Server Management Studio, we end up refining our working IDE following our habits, accessing it's properties (Tools ->Options) specifying we do prefer to output results to text and not to the traditional defaul grid

We are finally done with the limited settings changes we wanted to perform, so we like to start testing this brand new instance;
using our home built companion tool, we generated a brand new user database using a set of DDL and DML sql scripts

 
we provided extended authentication info, as the TCP/IP port the instance is listening to only is required from remote client connections, but we did override the default local connection protocol from shared memory to TCP/IP as well

 
and the result was succesful indeed

Even if we already tested the local connection was available to the instance, our goal was to be succesful with a user client application

resulting succesfully

As local connections are available, the final test is about remote connections;
so we have to verify our network settings

and eventually enabling the File and Printer sharing for Microsoft Networks

Additional steps are required, as we have to instruct the local Firewall about our interest to process external accesses, so we have to define appropriate exceptions about the changes we have done;
File and Printer sharing for Microsoft Networks usualy is already in order, but we still miss and have to define an appropriate excpetion on the static assigned port we assigned to the instance service via SQL Server Configuration Manager;
this exception will be on the port and on My network (subnet) only only, thus disabling access from the internet [the external evil :)], in order to limit the surface attack area; you can and obviously should limit that reference as more as you can, so you are encouraged to eventually only provide a limited custom list of allowed external machines

Finally ready, we can try a [in this case hopefully successful :) ] external remote access to the installed instance via another user client application