Distributed databases in a cluster

Hello gurus!
I know that SQL 2000 and very probably Yukon SQL do not support a shared
disk cluster.
So, in the shared nothing schema is possible to (in a Active/Active):
having one SQL 2000 instance running in the Active1 Server with on Database
(like pubs) and in the Active2 Server another database (like northwind)
I want that the virtual Server redirect the query automatically to one
instance or to another and in case of failure of the Active1 Server, the
Avtive2 take the Active1 works and in case of failure of the Active2 Server,
the Active1 take the work of the Active2

Thanks a lot again
AA
The concept of data partitioning across servers is attractive on teh
surface, but has a lot of complexities. SQL 2000 has a distributed
partitioned view feature that applies this technology in a limited fashion.
SQL generally scales up, not out. If you want a faster system, purchase a
bigger box. There are very few real-world applications that cannot be done
with scale-up, even with current technology.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AA" <aa@.personal.net.py> wrote in message
news:eXm5cOXIEHA.2480@.tk2msftngp13.phx.gbl...
> Hello gurus!
> I know that SQL 2000 and very probably Yukon SQL do not support a shared
> disk cluster.
> So, in the shared nothing schema is possible to (in a Active/Active):
> having one SQL 2000 instance running in the Active1 Server with on
Database
> (like pubs) and in the Active2 Server another database (like northwind)
> I want that the virtual Server redirect the query automatically to one
> instance or to another and in case of failure of the Active1 Server, the
> Avtive2 take the Active1 works and in case of failure of the Active2
Server,
> the Active1 take the work of the Active2
>
> Thanks a lot again
>
> AA
>
|||Actually, I'd disagree on the scale-up part. There are apps that would
bring even the largest commerically available servers to their knees if you
tried to run all of the transactions on a single machine.
The biggest part in here is that there is no technology inside SQL Server to
allow a transaction to automatically be continued on another node in the
event of a failover. This is possible, but it has to be coded into your
applications.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||I did qualify the statement about scale up. There are some environments
that simply cannot be scaled up to given current technology. However, I
consider those to be the exception, not the rule.
I also agree on the transaction failover issue. Smarter client tools and
faster failover on large systems will help, but scale out will need to be
addressed at some point. When and how is probably a marketing decision.
Unfortunately, none of those will help the original poster now. For his
application, he needs to start thinking about scale up if he wants to use
the current tool kit.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:OBcGddeIEHA.3276@.TK2MSFTNGP09.phx.gbl...
> Actually, I'd disagree on the scale-up part. There are apps that would
> bring even the largest commerically available servers to their knees if
you
> tried to run all of the transactions on a single machine.
> The biggest part in here is that there is no technology inside SQL Server
to
> allow a transaction to automatically be continued on another node in the
> event of a failover. This is possible, but it has to be coded into your
> applications.
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>
sql

Distinct Count Problem

I am using a standard star schema structure with a sales fact table and dimension tables joined by surrogate foreign keys. My product's dimension contains a hierarchy for SKU (the lowest level) and product class. I created a distinct count measure for "SKU Count" using the product key in the sales fact table. This works fine with no problem. Now I need to create a second distinct count measure for product class. I cannot get this to work. Product class exists as an attribute in the product dimension. I have tried several ways to get this to work but each time it results in an error. For example, I created an MDX calculation using both COUNT and DISTINCT COUNT and none of these worked. I also added the product class attribute into the fact table to see if this could work by creating a new distinct count against this column. This resulted in an error message.

Can anyone help with this? Basically, I am looking to create a distinct count measure against the product key in the fact table (which works fine) and than I want to create a second distinct count measure against the product class attribute that comes from the product dimension but is based off of the unique occurences of each product class in the fact table (referenced through the product class key).

David

Both these approaches should work, the MDX Calc would not perform as well as adding the product class attribute to the fact table. Can you tell us exact what errors you were getting.

With the fact table approach I think you will need to create a second measure group (based on the same fact table) as you can only have one distinct count measure in each measure group.

|||

Darren,

In order to create a second fact group based on the same table, would I need to create an alias table in the dsv first. This approach seems like it would be more resource intensive since I'm technically adding another 700,000 records with the "new" fact table.

David

|||

Yes, that's right you would have to scan through the fact table again, but each distinct count measure you create means another scan through the fact table. It depends how often you are processing your cube as this sort of volume should only take minutes. Technically you don't need to create an alias table, but I think it is the only way to do this through the UI, through an XMLA script you can create multiple measure groups off the one dsv object.

The alternative is to use the DistinctCount() function in a calculated measure, you would have to test it and see, but it can be quite slow depending on the size of your queries.