September 17, 2010 at 3:07 pm
Here's the background: Our division is setting up a data center to serve business units across the world. I work for one of the business units. But the data center doesn't have a SQL DBA, or anybody who knows much of SQL.
Trust me, it's not a joke. Well, anyway, the network engineer wants me to help spec out what servers to buy since we are the first major unit to use the system.
Currently the data center hosts a SQL Server 2008 cluster:1 active node, 1 passive node, 24 processors with 140 Gb. My unit just migrated most of the OLTP database onto this server.
Here's what we are trying to find out:
1. Is it advisable to have a dedicated server each for Reporting Services and Analysis Services?
At the moment, my unit uses RS only, in the distant future, AS maybe. AS is currently running on the Cluster. The only use now is somebody's Micorsoft Project Management. Some other units may use AS in the future.
2. If we go with dedicated servers for RS and AS, should we host all business units under one server or each should have their own Virtual Machine?
Note: all business units are completely independent operations. They are like separate companies.
3. If we have RS on its own server, should we keep reporting related data on this server or just ReportServer and ReportServerTempDB?
4. Our network engineer suggests to start with 16Gb memory, 1 processor with 4 or 6 cores. Is it good enough?
Thanks.
September 20, 2010 at 7:42 am
Anyone, please?
September 20, 2010 at 1:04 pm
both RS and AS are high CPU and Memory users.
they should be on separate servers.
RS should be RS only , NO DATABASE engine.
AS should be AS only, NO DATABASE engine.
a decent RS server would be at least a 4 core CPU with 16 GB of RAM.
disk space will not be to much concern here.
a decent AS server should be as least 2 CPU's with 4 cores each and 32 GB or RAM.
disk space and speed is a major factor here also as storing and spinning cubes will need fast disk with room for growth.
also, separate each business unit by instance to save on licensing costs.
September 21, 2010 at 8:13 am
That makes a lot of sense. Thanks much, Geoff.
Regarding separating business unit by instance, I guess it applies to AS since I don't see a way to isolate the cubes. But I don't have much experience with AS, either.
As for RS, can we go with all units on one instance so that we may have better performance over multi-instances? Reports can be easily isolated through report manager, and RS catalogs can be somewhat isolated through views. I guess what I'm trying to find out is the performance impact of multiple instances vs. one instance.
Thanks again.
September 21, 2010 at 9:33 am
your right about AS and isolation. running multiple instances is an absolute requirement if you are to have multiple customers or businesses using it. Each instance will get its own directory structure. you have to consider backups and restores. if you have a customer 1 and customer 2 sharing an instance, you will never be able to restore customer 1 without affecting customer 2.
however with multiple instances, they all be seperated by directory.
with RS, it is more about permissions than anything else. I would prefer multiple instances so I can define user 1 can go to instance 1 and user 2 etc.....
this gets even more complicated when you are using AD authentication between multiple domains.
September 21, 2010 at 12:29 pm
Very good point. Thanks a lot for the valuable input, Geoff.
One more thing, our regular SQL server 2008 is one instance shared among all the biz units. Is it advisable to have separate instances as well?
September 21, 2010 at 1:53 pm
that is not usually needed since each database can be controlled indvidually.
Normally, i would only spin off another instance for a business or app IF that business or app needed access to something other than its own database. Ie. app 2 needs to dbo to msdb.....
September 21, 2010 at 2:08 pm
Thanks again, Geoff. Is there a way to filter the database listing on Object Explorer in Mgmt Studio according to the access rights? I don't seem to be able to find a way to do it. It can get very confusing when there are dozens of databases with similar names to choose from. I know you can filter the table names, procs, etc.
September 21, 2010 at 2:51 pm
no. not that i am aware of.
that is another reason to spin off another instance i suppose. ease of management. i have had to do this in cases where an app uses more than 50 SQL logins. that is difficult to manage if it is on a shared instance.
September 21, 2010 at 3:25 pm
That sucks! With multiple instances on the same server, does the server automatically balance the resources across all instances or you have to carve out the memory and CPU usage beforehand for each instance?
You are such a prompt responder! Really appreciate your help. 🙂
September 21, 2010 at 4:41 pm
It will do it either way. You can let SQL handle it or you can manually adjust. If you go over 2 instances, you will absolutely need to set max memory per instance. If you don't , you run the risk of one of instances not being able to start because it does not have enough memory to do so. SQL can manage it to a point. But over 2 it needs to be told.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply