Skip to content

Tiered Distribution

As I was looking for information on MS Clustering Services ( MSCS ) (Failover Clustering ( WSFC ) in Windows Server 2008) and how it related to the overall picture, I kept digging deeper and deeper into the ‘why’ you would do it this way (cluster servers). I found out the answer lies in Tiered Distribution. Before I could dive into failover clustering, I had to first remember back to my college days about tiered architectures. I did well on the exams, but spitting out answers to a question does not prove an understanding of the subject, the ‘why’ if you will. So in order to tackle my MSCS / WSFC questions as they relate to SQL Server 2005, I had to first make sure I knew ‘why’ best practice infrastructures involve tiers. Here’s what I found out:

Single Tier – Mainframe

Single Tier - MainframeBack in the day when IBM ruled supreme on anything computer, there was basically only one tier: the mainframe. The mainframe handled everything: applications, data, etc. The two main problems with a mainframe is scalability and expense. Mainframes have a physical limit on memory, processing power and disk space. Once you’ve reached the limit, the next expansion will involve buying another mainframe, which brings up the expense issue.

Two-Tiered – Clients and Databases

Two-Tiered - Clients and DatabasesThe logical progression from the mainframe is to increase scalability by pushing application processing to the client. In this way you can add new machines at small expense and also tweak each user’s machine to their specific needs. Users no longer have to share processing power with everyone else. Drawbacks include the management of these clients: you better hire a ton of desktop guys. Also, you can’t let anyone outside of the company use your system.

Three-Tiered – Clients, Applications, and Databases

Three-Tiered - Clients Applications and DatabasesThe next evolution in tiered distribution is a compromise between the mainframe ease of having few machines and the scalability of a two-tier distribution. As you can see here, the application work has been brought to a middle tier called the application tier. Users usually make requests of these applications through a thin client, and the web and application servers do the work. This is what stumped me in school, because the difference here was not explained well. You wouldn’t move applications like Word or Excel to the app server because they are single user applications, but you would move applications like PeopleSoft, Remedy or Documentum because these are collaborative effort applications. One of the biggest problems with the three-tier distribution is the application tier is now exposed to external customers, so they can connect to the web servers. Not a great idea.

Four-Tiered – Clients, Web Servers, Applications and Databases

Four-Tiered - Clients Web Servers Applications and DatabasesAh this looks much better. With the addition of the web server tier, we can now put our web server in the DMZ or perimeter network without exposing our application servers to the outside world. Additionally we can give the two tiers much different set ups. We would give the web server tier more network sockets and I/O while we give the application servers more processing power, which the web servers don’t really need.


Hopefully this makes it very clear as to why tiered distribution is the current norm in a typical infrastructure. It really made it easier for me to understand why you would cluster SQL Server as well as how it actually works. For more in depth information on this subject, check out this Microsoft best practices page on Tiered Distribution.


Troubleshooting a Botched Install of SQL Reporting Services 2005

New Toy!

Cool kids have cool toysRecently I set up a new Dell PowerEdge 840 server at my house to have a place to learn new technologies and basically mess around. A friend of mine and I installed Windows Server 2008 and SQL Server 2005 including SQL Server Analysis Services ( SSAS ) and SQL Server Reporting Services ( SSRS ). The installations were pretty simple, which was surprising. For now I didn’t want to complicate the SQL installation, so I installed it with only one service account to handle all the services.

Well I made a very newbie mistake by forgetting all the passwords we had set for the windows accounts we set up during installation. Since my friend has a limited number of installations of Server 2008, I had to find a way to either get ahold of the passwords or find a utility to change the passwords. I couldn’t just rebuild from scratch. I found this utility (Offline NT Password & Registry editor), which allowed me to change the account passwords via a boot disk.

Wow! That was easy

Everything is gravy right? Wrong! It turns out to change the service account information for SSRS you must first backup the key used to create the encryption SSRS uses because you need to restore the key to change the service account info. Well I had changed the service account’s password hash on the disk and so never had a chance to create the backup key. All of the account information was now locked into the encryption.

Some uninstallations were in order. I uninstalled and reinstalled SSRS, but it didn’t solve my problem.  The uninstaller didn’t remove the SSRS instance files from the drive.  When I reinstalled SSRS, all of the connection information was still intact.  I also uninstalled and reinstalled IIS, but the SSRS application information and virtual folders were still intact.  I had to get a lot more medival.  To make a long story short, here’s the list of what I did to clean up the server:

Google powers, activate!

All of this work created a nice, clean server once more.  I then reinstalled SQL 2005, including all components.  During the first installation, my friend and I installed some additional IIS 7.0 role services In order for SSRS to function correctly.  I couldn’t remember what they were, but my google powers found this gem of a list.  Additional IIS role services installed, I then ran SQL Server 2005 SP3 (most importantly for this post, it corrects some bugs with SSRS configuration manager).  I then promptly opened the SSRS configuration manager.  Low and behold, everything was fixed!

Inital install SSRS all green

Onward with discovery

I hope this article can shed some light on how to fix a bad install of SS Reporting Services 2005.  I know I had a hell of a time finding help with troubleshooting.  It seems every work on how to install and configure SSRS assumes each step is automatically successful.  It would be nice if they also added some notes on what to do if the step does NOT install or configure as expected.

Now it’s time to dig in deeper to this thing called Reporting Services….