HowTo Install SQL Server 2008 R2

Here is the first of what I hope to be a regular part of The Code Attic experience,
a how-to article on something nice and geeky. As I continue to work on building
out my new machine I will document each of the steps I take in creating demos and
development environments.

Before you can develop or start showing demoes data a place to store and keep data
is needed. So after getting the OS up and running (Windows Server 2008 R2 of course)
you need a database. The following are the most straightforward 45 steps I used
to installing SQL Server 2008 R2 Developer addition with Reporting Services and
Analysis Services.

I won’t fully guarantee the steps will work for your setup but it does in mine,
at the very least is should provide some direction for you in your installation.

As always leave thoughts, questions, and tips in the comments.

Fun and luck I send your way.

Machine:

  • Windows Server 2008 R2 Standard x64 Windows
    Server 2008 R2 x64

  • Roles:
    • Active Directory Domain Services
    • IIS
    • Application Server
  • Running as virtual machine within VMWarePlayer 3.1.0 build-261024

Steps:

  1. Run the System Configuration Checker, resolve any issues that it detects
  2. Click Installation
  3. Click New Installation or add feature to an existing installation
  4. Enter Product key
  5. Accept license agreement
  6. Setup Support Rule
    • Windows Firewall Warning to resolve
      • The link which you are redirected to is : Configuring the Windows Firewall to All
        SQL Server Access (
        http://msdn.microsoft.com/en-us/library/cc646023(SQL.100).aspx
        )
        1. Assuming that if you are installing SQL Server you have an understanding of the
          purpose of a firewall I will not summarize the opening paragraphs.
        2. In case you were not aware, a default installation of Windows Server 2008 has the
          firewall on by default.
        3. You finally make it to useable information regarding the above error window about
          a third of the way down the article. The section is called “Ports Use By SQL Server.”
          They are kind enough to provide a table that explains each of the ports and there
          use.
        4. Default instances of SQL Server need TCP Port 1433 opened up. If you will have multiple
          named instances running on the machine additional port planning will need to take
          place, and is beyond the scope of this tutorial.
          • TCP port 1434
            • will need to be allowed if you plan to use Dedicated Admin Connections.
            • This feature is not enabled by default and so will not be covered in this tutorial
              as we are looking to build a basic machine with SQL Server 2008.
          • UDP port 1434
            • listens for any SQL Server Browser requests and provides the needed redirect to
              the appropriate TCP port for the named instance.
          • TCP port 443
            • is the default HTTPS port for SQL Server.
        5. Since we will also be installing Analysis Services the are additional ports to consider
          opening in the firewall will be
          • TCP port 2383
            • Standard port for a default Analysis Services installation
          • TCP Port 2382
            • Used only if a named instances of Analysis Services is being used.
          • TCP Port 80
            • If going to use Analysis Services via IIS (i.e. a connection is allowed to be established
              via a URL)
            • PivotTable requires HTTP or HTTPS
          • TCP Port 443
            • If planning to connect via HTTPS
        6. The final SQL piece our base image will have is Reporting Services
          • TCP Port 80
            • Allows for a HTTP (url) connection to be established
            • It is not recommended to use the default World Wide Web Services rule.
          • TCP Port 443
            • Allows for a HTTPS (secure url) connection to be established.
            • Again it is recommended not to use the default Secure World Wide Web Services rule.
        7. Open up the Windows Firewall and enable the abovementioned ports that relate to
          what you plan to install.
  7. You will be asked to enter your product key code, once entered click Next
  8. The Next step is to accept the Microsoft EULA. Feel free to take time to read it,
    I am sure there is many important things listed. Once you have read to your hearts
    content check the ‘I Accept..’ box, and if you wish to help out with the next version
    check the ‘Send feature usage…’ box.
  9. Click Next
  10. For this base image install SQL Server Feature Installation
  11. Click Next
  12. Feature Selection
    • Select the following
    • This feature set will require approximately 5752 MB
  13. Click Next
  14. The Installation Rules will run. There is a total of 24 rules it checks for. My
    system passed 6 of the rules while the remaining 18 were skipped (given a status
    of ‘Not Applicable’)
    • You will need to resolve any Fails before continuing. It is recommended that you
      correct warnings also.
  15. Click Next
  16. Instance Configuration screen
    • You will now be asked to either accept the default name instance or define a specific
      name for the SQL instance.
    • Since we are building out a fairly default machine for development purposes, select
      ‘Default Instance’
  17. Upon clicking Next you will be asked to review the disk space requirements
    for all the features you selected. If the listed drive does not provide enough space,
    then click Back and change the root directory under Instance Configuration.
  18. You will now be on the Server Configuration screen, where you will be required to
    enter the account which will run each service.
  19. Click Next
  20. The next step is the Database Engine Configuration. This step involves setting up
    the authentication mode and the SA account of the instance of SQL Server.
    • Authentication mode select Windows
    • Click Add Current User if you are currently logged in as an account you want to
      have SA privileges
  21. Click Next
  22. Analysis Services Configuration is the next step. This step is used to setup the
    administrators for Analysis Services.
    • Add all the users you wish to define with such a role.
    • Click Add Current User if the currently logged in user should be granted such permissions.
  23. Click Next
  24. Report Services Configuration is the last of the service configuration steps.
    • Select the top option “Install the native mode default configuration”
  25. Click Next
  26. If you would like to supply error reports to Microsoft check the box.
  27. Click Next
  28. Installation Configuration Rules will run. There is a total of 8 rules which will
    be checked.
    • My system resulted in 6 passed rules and 2 listed as Not Applicable
      • The NA rules are: Instance Name and SQL Server 2000 Analysis Services (64-bit) install
        action
  29. Upon clicking Next you will be given a summary of all the options
    and configurations settings chosen in the earlier steps.
  30. Click Install
  31. Everything has installed now it is time to perform some testing.
  32. The quick an dirty is to open up SQL Management Studio and create a database, if
    it creates you are in luck, it works.
  33. For Reporting Services, there are some additional steps.
  34. Try going to the report services link: http://<your
    server name>/Reports.
    • It will normally ask for credentials, at which time enter some credentials.
    • Most likely you will receive the following error message:
      • “SQL Server Reporting Services Error User <domain>\<name> does not have
        required permissions. Verify that sufficient permissions have been granted and Windows
        User Account Control (UAC) restrictions have been addressed.”
    • As you can guess, the user account must be given some permissions to get in.
  35. So you may be wondering how do I get around this issue and what caused it. My understanding
    is how Reporting Services handles the user accounts that are added during installation,
    see step 22.
  36. Even though you may already be logged in as the administrator for the system and
    SQL, you must run Internet Explorer as Administrator
  37. Re-enter the url for Report Server Manager (http://localhost/Reports)
    and you will be see the following
  38. Click Site Settings in the upper right hand corner. You are now
    in the site settings section of Report Server (if you are familiar with Windows
    SharePoint Server 3.0 you will notice some similarities in the setup of the screen.
    (Always nice of them to reuse good ideas.)
  39. On the left hand side click Security
  40. Click New Role Assignment this will take you to the page were new
    users can be added with different level of security (admin or user)
    • Here is a summary of the roles available, paraphrased from ‘User Predefined Role’
      at http://msdn.microsoft.com/en-us/library/ms157363.aspx.
    • Role Description
      System Administrator A user has the ability to enable features and set defaults throughout the system.
      In addition, has the ability to set site-wide security, and define role definitions,
      along with manage all jobs.
      System User Only basic server information is viewable to this role group.
    • You need to add at least one user as a System Administrator so that you no longer
      have to run as administrator. For my set-up I will be adding both a system user
      and a system administrator user. This will allow me to have separation of control
      during demoes. I would recommend creating two new user groups on the machine which
      are assigned the appropriate role levels, and then adding the accounts to the groups.
      This will allow you to quickly make changes as new test accounts are created and
      used, along with getting you in the practice of thinking about security in terms
      of groups and not individuals.
    • In addition to setting the system roles you must setup roles for the actual folder/viewing
      level also. To do this return to Home, and click Folder Settings.
      • You will see a similar security setting page as before. Click New Role Assignment
    • As with before either groups or users can be assigned the different roles. Depending
      on you planned strategy it may make since to assign individual users at this level,
      but remember the more individuals you begin assigning, the more difficult administration
      tasks can become.
    • Here is a summary of the roles available, paraphrased from ‘User Predefined Role’
      at http://msdn.microsoft.com/en-us/library/ms157363.aspx.
      • Role Description
        Content Manager All item level roles are wrapped into this single role. This means users or groups
        assigned the role are able to grant permissions, define folder structure, and all
        other management abilities of report server content.
        Publisher Grants the ability to add new items to the report server, including new reports
        and folders
        Browser Can run, subscribe, and browse reports. Can be considered a read only permission
        level.
        Report Builder Has the ability to author and edit reports which exist in the Report Builder
        My Reports Allows user to create a personal report workspace (think a SharePoint MySite like
        experience) were they can store and manager reports for personal use.
      • My recommendation is the user assigned the System Administration role at the site
        level should also be granted Content Manager role at this level. While your system
        user level group/user should be given at minimum Browser role, if not more. This
        is your call as to how you plan on using the system.
Advertisements

My Shiny Black Laptop

I have recently become the proud parent of a Lenovo ThinkPad T510. I find it to be quite cute and very slick looking, were as my wife has a different opinion, and I quote, “It is soo ugly! And what is with the tacky red dot?” (I feel it must be noted this was within minutes of removing it from the packaging.) I then had to explain it wasn’t bought for its fashionably good looks but rather for the secret power within it, yet I continue to get a small eye roll every time I get it out.

My greatest complaint so far is with the keyboard. The keyboard feels a little on the squished side, but has very nice feeling keys which type soft with just enough click noise and feed back to make you realize there is structure to the whole chassis. What I find most aggravating is that the left Control key is not placed on the outside edge of the keyboard (directly under the Shift) but slid in wards with the emphasis laptop Function key sitting in the normal place.

As a developer I do a lot of copy and paste (write one copy often) which leads to a lot of stop think and readjust the hand to properly copy and past. If you know of a way to change key functions leave a comment, I would love to turn the function key into a control key to save myself a lot of grief.

On a positive note, the non-glare screen is very nice, you still have a little reflection when in bright sunlight but is very ghostly/shadow like and hasn’t hurt my ability to work. Even wearing sunglasses allows the screen to be easily seen in all light conditions.

I have not had a lot heat issues as of yet, but I still haven’t gotten into a lot of heavy processing, I’m still in that love stage of setup…I try to return to this issue in a few weeks after running SASS cubes and SharePoint BI. The 9-cell battery upgrade has added some bulk but as I’ve done setup and install procedures I have gotten in the 7 hour realm of battery life which I think is fantastic.

So now to unveil the specs of this machine:

Processor: Intel Core i5 M540 2.53Ghz
RAM: 4 GB
Screen: 15.6 anti-glare (1600×900)
2 mega-pixel camera
500 GB Hard drive 7200 rpm
Bluetooth Antenna
NVIDIA 3100M 512MB discrete graphics

The Windows Experience Index rated the system at 4.9 with the following scores:

Calculations per second – 6.8
Memory Operations per second – 5.9
Graphics – 4.9
Gaming Graphics – 5.9
Primary Hard Drive – 5.9

I am currently running Windows 7 Professional ( the 64-bit version of course) with all of the 2010 Office Client stack and Visual Studio 2010 Ultimate. For virtualization of developer servers I am running the latest version of VMPlayer. So far I have not run into any gotchas involving this stack of software. For virus and malware protection I am relying on Microsoft Security Essentials, as I feel it has a smaller footprint then some of the other free virus protection. The Windows 7 Homegroup feature allowed this new machine to immediately start sharing files and printers with the other machines on my home network, and has made it very easy to move over old files I had on those other machines, someone really thought that feature set out.

As I continue to use the machine I’ll post updates, but so far I would highly recommend the system to anyone looking to purchase a lot of power on a tight budget. (Especially if looks aren’t a priority.)

Dig Through the Attic

I was inspired to create the Code Attic as I remember as a kid the excitement my siblings and I had when we were given a chance to explore Grandma and Grandpa’s attic. You never knew what type of treasures and some time strange garbage they might have placed up their.

It may have been stuffy, hot, and had a funny smell, but we always seemed to enjoy the exploration that could be done. And usually came across at least one interesting thing to play with during that visit.

So now that I am older, I feel there should still be an ‘attic’ of sorts to store away my thoughts for later looking and to provide others with that childhood excitement of an attic search, without the smell and heat.

The Code Attic is going to become the attic of my adventures in technology, as a Microsoft SharePoint consultant. The articles and how-to entries will revolve around SharePoint and custom .NET development but may bleed into what ever other technologies I might be learning/working with at the time. My hope is to start giving some back to the crazy realm of the blog-o-sphere that has helped me along the way.

My end goal is to store away something new to The Code Attic every Tuesday and if something real cool or for that matter strange comes along, and extra post might happen.

You can contact me via the comments, try me on twitter #thecodeattic, or drop an email to thecodeattic at gmail dot com.