Need recomendations on architecture.

We will be creating a moderately high-volume OLTP database application that needs 24/7 availability. We are planning to offload OLAP processing to a second copy of the system. We will be using SQL Server 2005.

I originally planned to set the second server up with SQL Server 2005 mirroring to cover the 24/7 availability requirement, with the idea that we could also do OLAP reporting off of the mirrored copy of the database. But I've gotten some indications that a mirror database is offline and not available for querying. So I figured I would use transactional replication to keep the OLAP database current. Now I am wondering if I need to use mirroring at all, or if I should just use transactional replication on the entire database and swap to the replicated database if the production server crashes.

What is everyone's opinion?

Replication only, for both OLAP reporting and failover?
Mirroring to one database for failover, with replication to a another database for OLAP reporting?My recommendation is to mirror for redundancy, much like you'd use clustering. Mirroring allows you to set up N servers that appear to the user as a single server. It gives you a quick, easy, and supported way to do something a lot like what log shipping did in previous versions, with automatic failover.

I'd also suggest that you use replication for OLAP processing, but that it go to a completely different server than your mirror does. You really want the replicated copy of your OLTP data to serve as the pre-staging area in your DW architecture, so I'd really want to avoid using a mirrored database for this use. By creating this "pre-staging" image, you can then roll the data into the staging form in whatever is the most convenient fashion, allowing it to flow quickly and easily through your staging area to the atomic level of your DW. At that point you can decide if you want to go to the extra effort to create relational rollups (a good idea for many reasons), or if you want to go directly to cubes (faster, but you lose a lot of options if you take the shortcut).

-PatP|||I'm afraid the company would balk at footing the bill for a third server to house the OLAP copy. What I may suggest is housing the mirrored database and the OLAP database on the same server.|||Understood... While we can figure out ways to provide exactly what users want, we are all constrained by what the users can afford. I think that you need to at least present the users the information that they need in order to make an informed decision... They need to know what you'd recommend as well as what you can live with...

-PatP

Need Information about Processing and Aggregations (Update / Referenced Dimensions / Many to Man

HI!

I am looking for some information about processing of complex big models with partitioning.

facts:

we need a lot of aggreations have several dimension with 2 mio. members have partitioned the cube and want to process only a few and not all partitions want to process dimension by update (does not unporcess old partitions) or increment update have some dimensions with references relationship to the partitions may have linked measuregroups

questions:

update dimensions processing does this unprocess a cube or partition? we have the problem that our partitions get unprocessed? maybe if its referenced by ohter dimension or it is an parent child dimensions are there any circumstances where an update dimension unprocess the cube? how well are aggregations if we process update dimension how well are aggregations if we process update referenced dimension howto aggregate with a many to many measuregroup and update dimension

Links to any good documentation would be really interesting.

THANKS

HANNES

Some good material about AS2005 processing model: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_asprocarch.asp

First. In AS2005 you have 2 types of processing that update dimension.

One is ProcessUpdate and another one is ProcessAdd.

update dimensions processing|||

Hi Hannes,

Did you find what was the problem that your partitions become unprocessed?

I have the same problem, i'm doing processing to new partitions and the old ones become 'Unprocessed'

Thanks in advance.

|||

It was because this was a test environment and we changed metadata. Added some attributes. This always need to be a full process.

LG, HANNES

|||

thanks for your answer.

I'm doing Process Full to the partitions, and I have the problem above.

the source database was changed but my query that reads the data hasn't changed, means that i have new columns in the fact table and i haven't add it to my query, does it matter? , i didn't changed my measure group at all.

Do you have any suggestion?

Do I need to do ProcessFull to dimensions as well?

thanks.

|||

If you do a full process of a partition the processing state of a cube does not change

(if the cube was prcessed before if is processed after

if the cube was not processed before it is not processed after - als log as you process by the xmla commands!)

Hannes

Need Information about Processing and Aggregations (Update / Referenced Dimensions / Many to Man

HI!

I am looking for some information about processing of complex big models with partitioning.

facts:

we need a lot of aggreations

have several dimension with 2 mio. members

have partitioned the cube and want to process only a few and not all partitions

want to process dimension by update (does not unporcess old partitions) or increment update

have some dimensions with references relationship to the partitions

may have linked measuregroups

questions:

update dimensions processing

does this unprocess a cube or partition?

we have the problem that our partitions get unprocessed?

maybe if its referenced by ohter dimension

or it is an parent child dimensions

are there any circumstances where an update dimension unprocess the cube?

how well are aggregations if we process update dimension

how well are aggregations if we process update referenced dimension

howto aggregate with a many to many measuregroup and update dimension

Links to any good documentation would be really interesting.

THANKS

HANNES

Some good material about AS2005 processing model: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_asprocarch.asp

First. In AS2005 you have 2 types of processing that update dimension.

One is ProcessUpdate and another one is ProcessAdd.

update dimensions processing|||

Hi Hannes,

Did you find what was the problem that your partitions become unprocessed?

I have the same problem, i'm doing processing to new partitions and the old ones become 'Unprocessed'

Thanks in advance.

|||

It was because this was a test environment and we changed metadata. Added some attributes. This always need to be a full process.

LG, HANNES

|||

thanks for your answer.

I'm doing Process Full to the partitions, and I have the problem above.

the source database was changed but my query that reads the data hasn't changed, means that i have new columns in the fact table and i haven't add it to my query, does it matter? , i didn't changed my measure group at all.

Do you have any suggestion?

Do I need to do ProcessFull to dimensions as well?

thanks.

|||

If you do a full process of a partition the processing state of a cube does not change

(if the cube was prcessed before if is processed after

if the cube was not processed before it is not processed after - als log as you process by the xmla commands!)

Hannes

Need Information about Processing and Aggregations (Update / Referenced Dimensions / Many to

HI!

I am looking for some information about processing of complex big models with partitioning.

facts:

we need a lot of aggreations

have several dimension with 2 mio. members

have partitioned the cube and want to process only a few and not all partitions

want to process dimension by update (does not unporcess old partitions) or increment update

have some dimensions with references relationship to the partitions

may have linked measuregroups

questions:

update dimensions processing

does this unprocess a cube or partition?

we have the problem that our partitions get unprocessed?

maybe if its referenced by ohter dimension

or it is an parent child dimensions

are there any circumstances where an update dimension unprocess the cube?

how well are aggregations if we process update dimension

how well are aggregations if we process update referenced dimension

howto aggregate with a many to many measuregroup and update dimension

Links to any good documentation would be really interesting.

THANKS

HANNES

Some good material about AS2005 processing model: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_asprocarch.asp

First. In AS2005 you have 2 types of processing that update dimension.

One is ProcessUpdate and another one is ProcessAdd.

update dimensions processing|||

Hi Hannes,

Did you find what was the problem that your partitions become unprocessed?

I have the same problem, i'm doing processing to new partitions and the old ones become 'Unprocessed'

Thanks in advance.

|||

It was because this was a test environment and we changed metadata. Added some attributes. This always need to be a full process.

LG, HANNES

|||

thanks for your answer.

I'm doing Process Full to the partitions, and I have the problem above.

the source database was changed but my query that reads the data hasn't changed, means that i have new columns in the fact table and i haven't add it to my query, does it matter? , i didn't changed my measure group at all.

Do you have any suggestion?

Do I need to do ProcessFull to dimensions as well?

thanks.

|||

If you do a full process of a partition the processing state of a cube does not change

(if the cube was prcessed before if is processed after

if the cube was not processed before it is not processed after - als log as you process by the xmla commands!)

Hannes