I wanted to try out SQL 2008 for the upcoming Madison DotNet Code Fest. I had downloaded the February CTP last week, and started out with that. I actually got everything installed and went out to find the sample databases on CodePlex, and discovered that RC0 had been released this week. Oh well, just revert the virtual machine to it’s pre sql state and start over with RC0.

Pre-Installation

Pre Requisites

The following pre requisites were installed without issue as part of the setup process. All pre requisites were included with the ISO.

• .Net Framework 3.5
• .Net Framework 3.5 SP1 Beta 1
• Hot fix for Windows Server 2003 (KB942288-v2)
• Reboot

My base Windows 2003 VM has only an 8 GB system drive, so I install everything to a second virtual hard drive which usually has a drive letter of E, as it does in this case. So all of my install paths are to E:\, but the folder structure remains the same.

SQL Server Installation Center

The SQL server setup has been completely revamped from SQL 2005, and there are some noticeable additions from the February CTP as well (the most prominent is the addition of a GUI option to use an existing configuration file).

Planning: Requirements, documentation, system configuration check, upgrade advisor

Installation: New stand alone, new fail-over cluster, add node to cluster, upgrade from SQL 2000 or 2005, search for production updates

Maintenance: Edition Upgrade (like changing from developer to Enterprise), repair, or remove from cluster

Tools: System configuration checker, installed SQL server features discovery report, upgrade integration services packages

Resources: Documentation, community, CodePlex samples

Advanced: Install based on configuration file, Advanced cluster prep and completion

Options: Platform (x86, x64, ia64)

Installing

I went ahead with and chose the stand alone installation. Five items were checked before I was allowed to proceed: Minimum OS version, setup administrator, restart computer, WMI service, validation for SQL server registry keys. All items passed and I proceeded to the product key.

The installation media seems to have all of the files for evaluation, express and express with advanced services. I didn’t have a product key, so I just went with the evaluation version, which should give me 180 days to evaluate. After selecting the edition, I had to accept the EULA and then have some setup support files installed.

Another system check was run, and everything passed except I received a warning notification because I had enabled the windows firewall. A message indicated I should check out a page at Microsoft, but the page had no mention of firewall configuration. Clicking next, I was brought to the Feature Selection page, and the main portion of SQL 2008 setup.

Feature Selection

These are the options I chose, and is not an all inclusive list.

• Instance Features
  • Database Engine
  • Reporting Services (I’m interested to see how the non-IIS reporting services works)
• Shared Features
• Business Intelligence Development Studio (to create some sample reports)
• Client Tools Connectivity
• Integration Services (Was required in Sql 2005 for maintenance plans)
• Client Tools Backwards Compatibility
• SQL Server Books Online
• Management Tools – Basic (Management Studio, SQLCMD, and SQL Power Shell provider)
• Management Tools – Complete (Support for Reporting Services, Analysis Services, and Integration Services, SQL Profiler and DB Tuning Advisor)
• Microsoft Sync Framework (interested to see how this works with mobile devices)

Instance Configuration 

• Default instance
• Instance ID: Default of MSSQLSERVER, however you can change this, and it automatically updates the install paths
• Instance root directory: E:\Program Files\Microsoft SQL Server\
• Computed SQL Server Directory: E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER
• Computer Reporting Services Directory: E:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER

I do like how you can change the instance id for the default instance, as well as choosing a named instance (which you’ve always been able to do).

Disk Space Requirements

After selection your options and installation drives, you are presented with a screen that shows drive space requirements. Unfortunately, even with changing all install options to E, half of the required disk space is still targeting my C drive.

• C:\windows: 1188 MB Required
• Shared Features (E): 724 MB Required
• Instance Directory: 267 MB Required

Server Configuration

Here you configure the user accounts for each service. Sql Server Browser defaults to Local Service. Sine I am not running in a domain, nor do I need access to any domain resources, I created a local user account and used that for the Sql Server Agent, Database Engine, Reporting Services, and Integration Services service.

You can also change the collation, but I left it as the default: SQL_Latin1_General_CP1_CI_AS

Database Engine

Account Provisioning: Windows Authentication or Mixed mode. I went with integrated, and added two users to the server admin role.

Data Directories:  Here you can choose the root data directory, as well as where user data files, log files, and the system databases are installed to, wow, that is a nice change. I’m using the default of E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data, but in our production systems, this would defiantly change. Being able to specify this stuff at install time is awesome, and I can defiantly see the need for some install scripts.

Filestream: Here you can enable FILEStream for T-SQL access. I have no idea what this means, so I will leave it off for now. You have to specify a windows share name when you enable to, so I am curious to find out more. You can also allow remote clients to have access (via the share?).

Reporting Services

You have three options, Native mode with default configuration, SharePoint with default configuration, and install but do not configure. I’m going with the native with default configuration in the essence of time.

Installation Progress

Prior to starting the install, a final system check is performed, and all checks passed. Before clicking install, you are presented a summary of what will be installed, as well as given the path to the .ini file that contains the configuration. I have included my path below, but it will very from install to install as the path includes a date stamp: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20080618_142128\ConfigurationFile.ini

The installation process took longer then I remember to install the February CTP, but other then that, the install wen smoothly and no errors were encountered.

Post Install

I went into the SQL server Configuration manager, and under Sql Server network Configuration, I enabled TCP access so I could remotely connect to the SQL server. I also opened up port 1433 to my local subnet in the Windows Firewall.

First Thoughts

I tried connecting with SQL Management Studio 2005 to my new SQL 2008 instance, and it failed, so that is a little disappointing, but not unexpected. Same goes for Visual Studio 2008 pre-SP1 (since SP1 is still beta I have not installed it on my main machine), but SP1 beta 1 fixes this issue.

Management studio looks pretty much the same, just that there are new options in the navigation tree such as auditing and resource governor. I was able to connect to a SQL 2000 database without issue. I noticed a new publish to web service option when right clicking on a database, but I’m not sure if this is part of SQL or the Sync Framework.

The report manager site came up ok, but the reports site threw a permissions error indicating my service account did not have the proper permissions.

Found some new (and updated) remote help and IT tools today. The first tool, is a new one (to me at least), which I found from an article on Tech Republic, and is called CrossLoop. Cross Loop is a remote desktop/vnc clone that allows you to view the desktop of another PC. It is designed for remote helpdesk scenarios, as it requires a user at the remote computer to initiate a connection.

The second and third tools are Team Viewer and Ultra VNC, both of which I have heard of before, but have been updated. Team Viewer’s free offering has been expanded for non-commercial use, while Ultra VNC has several plug-ins and add-ons which make it suitable for a variety of scenarios.

The add-ons for Ultra VNC that interest me the most are the Repeater and Nat2Nat, both of which allow for connections to be made across firewalls. Digging thru the Ultra VNC forums, I came across several posts talking about a solution (Ultra VNC with various add-ons) that enables remote connections to be made using SSL on port 443 without the need to configure firewalls. The Ultra VNC repeater is licensed under the BSD license, but I’m not sure on the other add-ons. The NatHelper server that is used for the Nat2Nat looks like it might be hosted by Ultra VNC, as I didn’t see any download links for it.

The final tool I came across today, was Spice Works, which was an add displayed on the Ultra VNC sight. SpiceWorks is a free (supported by ads, and not open source) tool for IT professionals looking to manager their network. Here is a review from PC Magazine (which also drops the names of a couple of open source network monitoring tools).

I was searching for a stand alone installer for windows live writer, instead of the dumbed down live services installer so that I could install live writer onto my Win2k3 workstation. Thankfully, they just released a tech preview of a new version, and it comes as a stand alone installer, which installed on Win2k3. You can download the preview here. This is not a side by side install, your previous file versions are overwritten, but all of your settings and plug-ins are preserved.

As far as new features go, the fact that I could install on Win2k3 was enough for me, but they added tabs at the bottom of the main screen to allow you to quickly switch from edit to preview to source mode very easily.

Hopefully I will be getting some time to write some more content as I’ve been too quiet lately and being able to easily blog from my main workstation at home (running win2k3) will help with this.

Update:

There is a new auto linking feature that I’m still trying to test out. This is similar functionality to content filters in dasBlog (that was a auto link), but is limited to setting up hyper links with common text, where as content filters can be setup for text or RegEx and the replacement value can be anything. The nice thing about auto-linking, is that you can see your links as you type them in live writer. However, content filters apply to your entire site and can be changed to update all text in all your posts.

Continuing from my problems with installing reporting services, I now turn to configuring reporting services running on Server 2008. I have a total of four front end web servers, two for staging and two for production. Each pair points to the staging and production databases respectively. I was following this Microsoft document on how to install and configure for server 2008, but still ran into some errors. Both errors where displayed in IE when trying to browse to http://localhost/reports (the report manager site).

Error #1: rsReportServerNotActivated

This turned out to be an error with the encryption keys. I searched for documentation on setting up a web farm, and from what I found, there really was no mention on syncing the encryption keys, but ultimately that's what I ended up doing to get both front end web servers to show as initialized.

Starting with my staging servers, I looked in the ReportServer.Keys table, and saw that I had two entries, one for each web server. However, run of them was missing data in the SymmetricKey column. I exported the key using the Reporting Services Configuration tool from one server, and imported that key (again using the Configuration tool).  This fixed the error on my staging server, but led be to Error #2 (see below).

On my production servers, I had web100 showing as activated, but web101 showing as not activated (nor would it activate). There was a row in the keys table for web100 and web101, but I couldn't export the key from web100 like I could in staging. Attempting to export the key from web101 gave me an error saying it couldn't export the key, which made sense since it didn't have one. I tried using TSQL to copy the contents of the row for web100 in the Keys table to web101, but while it executed, it didn't fix the problem. I was a little frustrated at this point and just deleted all three rows from the keys table.

Wait a minute, where did the 3rd row come from? Back in the configuration tool, neither server was shown as activated, in fact, neither server was shown at all! Turns out that 3rd row has some dummy data in it that must be used somewhere. In case anyone did what I did, you can just add the row back in. All columns are null except InstallationID = 00000000-0000-0000-0000-000000000000, and Client = -1.

I went back to the configuration tool on web101 and under encryption keys, I hit delete. Looking back in the Keys table I saw I had a row for web101 again, and I was able to export this key. I promptly imported this key in on web100 and both servers activated.

After a quick reboot to verify that everything worked still, I was able to move onto the staging servers and Error #2. Yes, I did backup both encryption keys to a safe location for DR purposes.

Error #2: The report server is not responding. Verify that the report server is running and can be accessed from this computer.

I was getting Error #2 on web200 (figure out our naming convention yet?). This server was also showing an error in the event log: Report Manager cannot create the trace log %Program Files%\Microsoft SQL Server\MSSQL.1\Reporting Services\LogFiles\ReportServerWebApp__06_06_2008_14_40_25.log. This indicates a problem with permissions somewhere I'm guessing.

For all of my web servers, I've created a new SqlRsAppPool set to classic mode, which runs as a domain account. This domain account is specified in the configuration tool as the account to use when connecting to the ReportServer database as well. The web service setting in the configuration tool is configured to use the Local Service account. Had I not had 3 other servers running correctly, I would have tried setting the web service to the domain account as well.

Reporting Services uses to local groups named SQLServer2005ReportingServicesWebServiceUser$MachineName$Instance and SQLServer2005ReportServerUser$MachineName$Instance, and I compared those groups between my two staging servers, and the users were the same (WebServiceUser had my domain account and ReportServerUser had Local Service). I next comapred the permissions on the folders in %Program Files%\Microsoft SQL Server\MSSQL.1\Reporting Services between both machines, and they were the same.

I decided to reboot the problem server while I went and downloaded the latest version of Process Monitor, which happens to be at v1.33. I wish I could say I found some whacky permission that didn't get set by the configuration tool, but, rebooting the server seems to have fixed the problem. I know I restarted the Reporting Services window service, but I don't think I tried restarting IIS and the app pool. Oh well, I'm sure my download of Process Monitor will not be in vain.

Other Notes

Overall the how to install and configure for server 2008 guide was pretty good. The registry keys that say that you should have don't seem to be all that important, as I was missing some of them on my working servers from the get go. I did follow the steps to enable my account to login to the report manager without using the Run As Administrator command.

Even though I have no intention of directly exposing reporting services to the Internet, I still felt that it made the most sense to install the web front end components for SQL RS on our web servers located in our presentation DMZ. However, this proved to be somewhat of a challenge.

Apparently, there is a check in the setup file that limits your installation options on Server 2008 web edition to the client tools only. It took awhile of searching on Google, but I finally found my first lead, and am embarrassed because I didn't think of it myself. All of the various SQL components have their own MSI installers on the install media, located in either Tools or Servers. For reporting services, you need to run the SqlRun_RS.msi located in Servers\Setup.

I was still running into a problem however, soon after the MSI was launched I was getting a cryptic error message, followed by a second error message stating I didn't have access to a directory. Even though I had launched windows explorer as an administrator, I still couldn't get it to run. I decided to turn off UAC, reboot and try again. This time, the install was successful. I followed up the Sql RS install by installing SQL SP2, and then turned UAC back on.

Microsoft has a good document on how to configure SQL RS on Windows Server 2008, http://support.microsoft.com/kb/938245, which is what I used for the rest of the install. The document does state that under More Information that  you can install SQL RS "On any edition of Windows Server 2008".

I think it's probably one of the nicest Saturday's of the year so far, but I thought it would be more fun to uninstall our Trial edition of TFS 2008 and install the work edition. Here are the steps I took:

• Using Windows Firewall block access to TFS and Sharepoint
• Backup all databases to another server
• Take a VMWare snapshot
• Uninstall TFS 2008
• Uninstall TFS Build 2008 ( I don't think this was necessary)
• Install TFS 2008 Workgroup **
• Install TFS 2008 Build
• Test Locally
• Remove firewall restrictions
• Test Remotely
• Remove snapsot

Things were going smoothly until I was installing TFS Workgroup, and during the database upgrade (not sure why my DBs were different between the Trial and Workgroup Edition) I got an awesome error:

Product: Microsoft Visual Studio 2008 Team Foundation Server - ENU -- Error 32000.The Commandline

'"E:\Program Files\Microsoft Visual Studio 2008 Team Foundation Server\Tools\TfsDb.exe" upgrade /server:"YourTFSServerName" /property:"TFS_SERVICE_ACCOUNT=DOMAIN\tfsserviceAccount;

TFS_REPORTING_ACCOUNT=Domain\tfsreportsAccount;

LCID=1033;VSTF_AS_INSTANCE=YourTFSDBInstance;

VSTF_AS_DATABASE=TFSWarehouse;VSTF_AS_ACCOUNT=" /showui:2622014' returned non-zero value: 100.

I decided to run the TFSdb tool from the command line, and it gives allot better information. According to the error, I needed to perform a backup log operation on my TfsWorkItemTracking database. So after doing that, that phase of the install process completed. The rest of the install process seemed to take forever, and the progress bar was about 97% to the end and just hung there for a good 3-4 minutes before it finally completed without error.

Don't ask....

• Shutdown your SQL Server service, from the services MMC
• Stop all services and applications from connecting to your SQL server. If they are connecting via the network, you could use the windows firewall to restrict access. The reason for this, is once you are in single user mode, another application may connect and consume the one connection.
• Open a command prompt window and navigate to the location of SqlServr, which should be %Program Files%\Microsoft SQL Server\MSSQL.1\MSSQL\Binn.
• Type sqlservr -m to start SQL server in single user mode
• Open Sql Server Management Studio (SSMS) and connect to your SQL server
• Fix your security
• Go back to the command prompt window and press ctrl+c, and then Y to stop SQL server
• Restart SQL server, and any dependent services (like SQL agent) in the services MMC.

If you have UAC enabled (Vista or Win2k8) you have to run the command prompt, and SSMS as an administrator.

Question...does it really matter if you deny local computer and domain administrators access to the SQL server, if any local administrator can restart SQL server in single user mode? The reason I ask, is there are a lot of "Best Practices" that say you should deny access to normal computer administrators. I've even heard that in some industries and sectors such as legal, healthcare, etc, you have to restrict who has access to the data. Maybe it's just a you need to make a good effort.

So I have been continuing on my new assignment of setting up servers for our new infrastructure, and it's somewhat boring and repetitive after the first one. I was hoping to knock out the last 3 SQL servers today, but I couldn't get our schedule maintenance plans to run. I kept getting an error stating that "The owner (Domain\User) of job <Job Name> does not have server access." Well, I am the owner, and I have SysAdmin permissions, and I am the one who created the jobs, so what could it be?

Originally I had imported the maintenance plans from a file share that I had exported the maintenance plans from a non-member server, and then imported those maintenance plans into two other servers. I tried creating a new job on one of the servers, thinking that was something wrong with the import process, but nope, I get the same error on all three servers. Another member server, configured exactly same, just setup the day before, works fine, with my account as the job owner.

Off to Google I go. I found some references to the error, but allot of them were for a KB article relating to SQL 2000, and it had a service pack fix. I did find a command ( exec xp_logininfo 'username' ), which is supposed to help you verify if your account is setup correctly, and apparently mine is not. I get an empty result set on the 3 servers I am having problems with, and one row with the correct information on the working server.

I was actually happy to see the empty row set on the three non-working servers, as now I can search on exec xp_logininfo instead of the error I was getting with the job, to hopefully find a solution to my problem. Unfortunatly, I didn't get many hits that applied to my situation. Running the xp_loginfo on both the working and non-working servers yielded some interesting information. I decided to add a domain group that my account belonged to, to both a working and non-working server. Re-running the xp_logininfo showed 1 row on the non-working server, and 2 rows on the working server. The permission path column on the working server showed null for the first row (which was present before adding the group), and the 2nd row shows the group I just added (which is the same as the non-working server after adding the group).

Somehow, the one working server has some extra permission set somewhere. I think this may be because I might have manually added my domain account on the working server, but I let a MS tool that auto-runs after SQL SP2 add my account. Sure enough, adding my domain account manually fixed the original problem, and the results of the xp_logininfo now match between all servers.

I've been working on setting up servers the past week, and will be continuing for the rest of may. Included in that are BizTalk and SQL servers. Part of our setup procedure is to document everything so that we have consistency between servers. It's boring and tedious but necessary.

While looking up some info on DB Mail in SQL 2005, I came across a good post by Brent Ozar on some post install steps.  It covered a few things I hadn't thought about, but have since added to our build documentation.

Brent's post also had links to the SQL 2005 Dashboard Performance Reports. One of the links was to an excellent write up of the installation and use of the reports by Brad McGehee.

Today I downloaded and installed the Sql Server 2005 Express Advanced edition for a test project I was working on. I needed to enable remote connections, so I when to the surface area configuration wizard and enabled remote connections thru tcp, and I opened port 1433 on the computer's firewall. However, I could not connect.

Running a netstat -an showed nothing listening on port 1433. Doing some searching I found an outdated post on the sql express blog that helped me fix the problem.

1. Open Sql Server Configuration Manager
2. Expand Protocols for SQLEXpress under SqlServer 2005 Network Configuration and click on Tcp/Ip
3. On the IP Addresses tab, scroll to find the IpAny entry.
4. Clear out the field for dynamic ports and put in 1433 for the TCP Port
5. Restart SQL Server.

The reason you have to do this, is by default the SqlBrowser service (port 1434) is not running by default. When clients try to connect, they must know the port to connect to. The default sql port is 1433, so once we have that setup, even if the SqlBrowser service is not running, or is blocked by the firewall, we can still connect.