placement of Analysis service? on cluster or lone server?

Hi, we have an active/passive cluster which hosts our production database.
Transactional replication is being made from the production to a 'reporting'
database residing on a lone server. We will need to install Analysis service
for the construction of cubes.
I was wondering where Analysis service should be installed?
Is is ok to install it on the lone server and have it use the data from the
reporting database (which is filled via transactional replication)? Will
Analysis service have a problem with this based on the fact that it is
reading a read only database which doesnt have primary keys in the tables?
OR, should analysis service be installed on the cluster? Is it cluster
aware?
At this stage we are thinking of only running the cubes at night to generate
the data for export to reporting service.
Would it then be a good idea to install reporting service on this same lone
server?
Any insight would be most appreciated!
Cheers, john
If that is the case, you can't possibly be running transactional
replication.
The "reporting" database is LOGICALLY read-only, not physically read-only.
This database also MUST have all of the primary keys in place on the tables,
since it is impossible to replicate a table with transactional replication
if the table does not have a primary key. This primary key exists at both
the publisher and the subscriber.
Analysis Services is not going to write to your tables when building cubes,
so it does not care if the tables are physically read-only, logically
read-only, or read-write.
Where you place AS is entirely up to you. You can install it on the
stand-alone server and you can install it in the cluster. The question you
need to ask is what level of availability do you need for AS. If it needs
to be available as much as possible, even through a hardware failure, then
it should be installed in the cluster. If not, it should be installed on
the stand-alone machine.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"john clarke" <jclarke@.nospam.com> wrote in message
news:%23QyI71JDGHA.688@.TK2MSFTNGP11.phx.gbl...
> Hi, we have an active/passive cluster which hosts our production database.
> Transactional replication is being made from the production to a
> 'reporting'
> database residing on a lone server. We will need to install Analysis
> service
> for the construction of cubes.
> I was wondering where Analysis service should be installed?
> Is is ok to install it on the lone server and have it use the data from
> the
> reporting database (which is filled via transactional replication)? Will
> Analysis service have a problem with this based on the fact that it is
> reading a read only database which doesnt have primary keys in the tables?
> OR, should analysis service be installed on the cluster? Is it cluster
> aware?
> At this stage we are thinking of only running the cubes at night to
> generate
> the data for export to reporting service.
> Would it then be a good idea to install reporting service on this same
> lone
> server?
> Any insight would be most appreciated!
> Cheers, john
>
>

PK design in clustered environment

We are designing a large transactional database that will end up being deployed in a clustered SQL Server 2000 environment. We have initially designed all of our primary key fields as GUID type to eliminate any potential future replication issues. Will
this design impact the performance of the db negatively versus using an auto-increment PK design using big int type fields and following a ranged identities design approach?
Primary keys selection and performance is the same regardless of whether you
are in a clustered environment or not.
GUIDs as PKs are not a good idea. First, they are effectively random, so
there will be lots of page splits and table/index fragementation if you
allow the default of clustered order on the PK. Second, they are totally
artificial, which I think is a very bad idea from a data integrity
standpoint. Finally, they are wide. Remember, every non-clustered index
uses the clustered index to look up date rows. GUIDs are very wide and will
slow down index intersection as a possible query resolution path.
Here is the (very condensed) version of how I prefer to layout index
structures.
First, I create a RowID field Int (or BigInt) Identity(1,1) on each table.
I then create a unique, clustered index on this column. Note it is NOT the
Primary Key. Primary keys are data-centric, not artificial. If you cannot
identify a data-centric key, then you need to rethink your design since it
is by definition not third normal form. With data-centric keys, I don't
worry about new RowIDs if I need to replicate the data.
My PK index is non-clustered. Any additional indexes are also
non-clustered.
Since the data is now in insert order, the cache manager now has a bit of
help. Most databases query newly inserted data more often than older data,
especially data in large tables. Inserts are intentionally hot-spotted,
also helping out the lazy writer and checkpoint processes. Index
intersection is highly optimized.
I have been using this technique for a few years now and it makes a HUGE
difference in performance.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"toml" <anonymous@.discussions.microsoft.com> wrote in message
news:A2293202-5C5F-4240-9413-628E5913FF58@.microsoft.com...
> We are designing a large transactional database that will end up being
deployed in a clustered SQL Server 2000 environment. We have initially
designed all of our primary key fields as GUID type to eliminate any
potential future replication issues. Will this design impact the
performance of the db negatively versus using an auto-increment PK design
using big int type fields and following a ranged identities design approach?