August 28, 2009 at 8:00 am
Hey all,
To be brief, I'm in the midst of setting up a 6 node 2005/2008 multi instance cluster farm which will hopefully help us consolidate a very large environment as well as provide a central environment to bring up new databases within.
Basically, the layout is as such:
- 6 nodes
- Windows 2008
- 1x 2005 community instance
- 1x 2008 community instance
- 1x 2008 application specific instance (intranet)
- 1x 2008 reporting instance
There are plans of adding several instances with in the next year, as well as possibly additional nodes.
Anyways, here's my question. What to do with SSIS/SSAS/SSRS? My initial idea was to keep them off the cluster. As shown above I had one instance slated as the reporting instance in by where we would ETL and/or restore/mirror/replicate data to for reporting purposes. The actual reporting application would be hosted on a separate server. I think this is a decent plan, but I've never split the front from the back of SSRS.
What my struggles are right now are what to do with SSIS/SSAS. My idea was to install SSIS on a utility server and/or a separate application server per environment/department/application and have all packages saved on the file system and run from there. I have had bad experiences with SSIS getting out of control on a production SQL box in regards to memory utilization and I want to avoid that. Thoughts on that?
As for SSAS ... I'm at a loss. I've never really done anything with SSAS. Can I split it off appropriately like the SSRS concept? In this type of configuration, a large clustered environment for most of the environment to live in, does it make sense to split these different services onto their own servers vs. simply installing them on each node and clustering?
This is outside of my general administration of what I've done in the past and at this point just looking for some feedback/ideas on how to handle the additional services involved with SQL. Looking for pro's/con's/headaches/benefits to different types of setups and what others experienced.
Keep in mind, I work for a Hospital system which up till this point has not had much overall administration/architecture. This is the first crack at truly revamping the environment at large. I have over 200 instances, almost 3,000 databases, over 6TB of data, all of which do their own thing, have their own rules/security/services. This is my attempt to conform and consolidate this environment and I want to make sure I do it right the first time through without little change as we progress through this. The difficult piece of this is that more or less, the future is unknown. I need to make the best initial architecture that will provide us with the most scalability with the least amount of headache in the future.
Any advice is most appreciated.
Thanks
September 18, 2009 at 7:23 am
Hey all,
Well, I've successfully brought up my first 4 nodes with 4 instances of SQL running. I have people looking for answers on what to do with SSRS/SSAS, and honestly, I'm still not sure what to do.
I'm thinking at this point to the put the front end of SSRS on a virtual server. Not entirely sure how to do this yet. I've briefly read the MS documentation, but anyone have any other documentation that would help me with this?
As for SSAS ... completely at a loss. Again, the goal of these boxes were to be purely SQL, but I really don't know what I can/can't do with AS and if it makes sense to split it off to a different server.
Again, any advice on this would be most helpful.
September 18, 2009 at 8:02 am
SSRS required own security model and special attention. SSRS are also required IIS running, which is not good for database server.
I would create application server with IIS and SSRS installed and load balancing.
SSRS repository databases should not be installed there because of high security risk, it is better to keep them on separate and very secure instance of database server.
September 18, 2009 at 8:04 am
SSAS are always very resource consuming services, so it is better to keep them away of production OLTP cluster.
September 18, 2009 at 8:15 am
Sergey Vavinskiy (9/18/2009)
SSRS required own security model and special attention. SSRS are also required IIS running, which is not good for database server.I would create application server with IIS and SSRS installed and load balancing.
SSRS repository databases should not be installed there because of high security risk, it is better to keep them on separate and very secure instance of database server.
If you are running SQL 2008, the SSRS that comes with that doesn't require IIS at all..
I would go on to say that splitting SSRS (regardless of 2005/2008) onto its own server with the reportserver database hosted elsewhere is the first step in a potential scale-out scheme.
I don't think I agree with the statement "high security risk", it is elevated when IIS is installed, but unless you have a novice install and manage it, the risk isn't huge.
CEWII
September 18, 2009 at 8:18 am
If you are running SQL 2008,
We are in SS2005 thread, so we are not talking about SS2008 at all.
September 18, 2009 at 8:18 am
Agreed, I definitely want to take that route with SSRS. Next step is actually "how-to". Obviously setting up SSRS as is on a stand alone, especially with 2k8, is rather straight forward. Any links/tips on how to set SSRS 2008 on separate servers? I already have a base 2008 instance setup and ready to go, but was not installed with SSRS. Not sure If I need to redo this or what has to be done from here.
I'll come back to SSAS once I know what I'm doing SSRS.
Thanks
September 18, 2009 at 8:27 am
I noticed that one thing so far not discussed was licensing..
If you break a part off you are required to have a license for it. When you buy a SQL license you are buying all the components on ONE machine, if you break SSRS or SSAS off to a seperate box they each need an additional license.
I believe the only time this isn't true is an active/passive cluster, where the passive cluster node doesn't require a seperate license.
This can get quite expensive, with SQL Std at $6,000 MSRP/processor and SQL Enterprise at $25,000 MSRP/processor.. I know that there are discounts but my experience with management has been that they balk when the numbers get big..
CEWII
September 18, 2009 at 8:29 am
Sergey Vavinskiy (9/18/2009)
If you are running SQL 2008,
We are in SS2005 thread, so we are not talking about SS2008 at all.
I disagree, that we aren't talking about that at all..
Basically, the layout is as such:
- 6 nodes
- Windows 2008
- 1x 2005 community instance
- 1x 2008 community instance
- 1x 2008 application specific instance (intranet)
- 1x 2008 reporting instance
CEWII
September 18, 2009 at 8:30 am
Planning a Reporting Services Deployment including Scale-out deployment for SS2005/SS2008:
http://technet.microsoft.com/en-us/library/ms157293(SQL.90).aspx
Configuring a Report Server Scale-Out Deployment for SS2005:
http://technet.microsoft.com/en-us/library/ms156453(SQL.90).aspx
Configuring a Report Server Scale-Out Deployment for SS2008:
September 18, 2009 at 8:36 am
Sounds like you have a big task to deal with, I would split everything out, definitionally want SSRS and SSAS on separate machine.
In previous posts, there seems to be some issue about your configuration. If i am reading it right for your multi node cluster
you have 1 sql 2008 reporting service
you have 2 sql 2008 server instances
you have 1 sql 2005 server instances
all these are enterprise and this is a windows 2008 cluster.
A lot to do with consolidation has to do with workload, depending on how much SSIS, SSAS and SSRS are utilised, can dictate if they can share or have dedicated resources.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 18, 2009 at 8:45 am
If you break a part off you are required to have a license for it. When you buy a SQL license you are buying all the components on ONE machine, if you break SSRS or SSAS off to a seperate box they each need an additional license.
That is correct.
But it is actually not so bad, because SSAS may use SS Developer edition, and SSRS could be shared environment, which is common for the big companies.
September 18, 2009 at 8:52 am
Thanks guys, I'll check out those links soon. Basically at this point, we have no idea what will happen with SSAS/SSRS. I am new to this environment and basically, there has been little overall administration. There are so many unknowns and uncertainties. As of today, I have probably about 10 users/vendors wanting SSRS which from there I have no idea how many reports that will be, nor users. For SSAS, as of today, I only have 1 vendor needing this.
In my prior two environments, it was much easier to make these decisions as we knew who/how/where/when/why. Here, this is a VERY large environment that up until my arrival was 95% vendor controlled. Well, I am trying to change everything and this is my first big step.
So basically I'm trying to build a robust and scalable environment that allows me flexibility when/where need be. So I think it's a pretty wise choice at this point to at least get started with RS/AS on different boxes. If they get big, we may bring up their own clusters for it, but that's for another day. Regardless, it seems like the smart move is to keep them off the cluster.
The licensing, is a whole other ordeal and I'm not overly concerned about that right now. So I guess my first move is to give the SSRS a shot. Not looking for the exact details (yet), but can SSAS be handled in a similar fashion? I honestly have zero experience with SSAS and quite honestly unsure to as what options I have at this time.
Thanks again all
September 18, 2009 at 9:35 am
Sergey Vavinskiy (9/18/2009)
But it is actually not so bad, because SSAS may use SS Developer edition, and SSRS could be shared environment, which is common for the big companies.
I agree with the SSRS piece, but can't on SSAS piece, if you use thDev Edition in a non-dev environment you have violated the license, which is a pure per seat license.
CEWII
September 18, 2009 at 11:06 am
So with the level of unknown and uncertainty, is it a fair assumption to say that it's not feasible to scope an "enterprise" solution for SSAS? At least not at this time? Does it make more sense to go back to the user/vendor and state that if they want SSAS, that it will have to be brought up on it's own environment? That's kind of what I'm leaning towards as of today ...
Thoughts?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply