Analysis Server 2005 Clustering

Hi

I want know for Analysis server 2005 on Clustering environment which type of Cluster (Server Custer/ NLB) is recommended by Microsoft & what is reason behind the recommendation.

Appreciated if you could provide me the URL for Analysis Server 2005 Clustering Best practice.

Thanks

Sunil

I am not aware of any specific whitepapers on clustering with AS 2005 specifically, but in nutshell the 2 different technologies have different focuses, so it depends on what you what to achieve. With most typical AS2005 installations NLB is usually recommended unless you are doing writebacks.

Failover Clustering

This gives you hardware redundancy in case of hardware failure. Note that this is a shared disk technology so you really need to have multiple disk controllers and RAID arrays setup so that you do not have a single point of failure. In the case of a hardware failure or OS crash, another node in the cluster will "pick up" the disks/IP addresses/server names that belonged to the crashed node and bring the instance of SSAS back online. So there will be a small period of downtime as the SSAS instance is started up on the new node. This type of clustering is more intended for applications like the relational database engine or exchange where the server is constantly reading and writing and you only want one copy of the data.

NLB Clustering

This sort of clustering is more for scale out scenarios as you basically have identical copies of your database on multiple servers that are all available to service queries. This means that you can "balance" the load across multiple servers, but only works with read-only databases, as if you are doing writebacks, there is no means of keeping the multiple copies in synch. This option can give you better up time than failover clustering as if an individual node fails, only the users connected to it when if failed are affected, all other users are unaffected and the when the users that were connected re-query they should connect to one of the other available nodes.|||

Hi Darren

Thanks for reply.

I want know, if i create a new cubes or any change in the existing cubes whether we need to Sync all

AS2005 Database (Respository) which is available of different node in NLB Cluster Environment?

Thanks

Sunil

|||

Yes, if you create a new cube you would need to synch all the nodes. But you would probably re-synch all the nodes on a regular basis as you process new data as this would be safer and more efficient than getting each node to re-process the same data

There is a synchronize command which you can use in AS2005 to synchronize databases between 2 servers which is pretty much aimed at doing just this. It is basically an automated backup and restore in a single command which can be scripted or scheduled.

You could also use the deployment wizard to deploy changes to mulitple servers. But I think that with an NLB scenario probably the better and more common scenario is to get one of the servers to do the processing and then synchronize with the other servers. It may even be that you have a staging server that does all the processing and is not part of the cluster.

|||

Hi Darren

The Architecture is excellent but i still i have a thing to clear about the staging server in my setup.

Could it be possible if I take 2 Server where we implement the NLB Cluster for the END user & Two Server which is cluster of staging servers in which we implement the Failover Cluster & this staging Servers are connected to the shared storage SAN Device for the Quorum & Analysis Server repository?

Appreciate if you could reply as earliest

Thanks Sunil

|||

Yes, this is probably the most robust SSAS configuration that I can think of.

I'm pretty sure that you could also have 3 (or more) servers in the NLB cluster and script to temporarily pull one node out of the cluster to do the processing, but if this node had a hardware failure during this time it would take manual intervention to recover.

You should keep in mind that logic errors and dirty data probably cause more issues with processing OLAP cubes than hardware issues, so you need to make sure your cubes and ETL processes are well designed. The advantage of having a staging server is that a processing failure usually only means that the front-end servers have slightly old data, but the users can continue working while you fix the problem.

|||

HI

I prepared a Architecture with 4 Node i.e. 2 Node Cluster for the Analysis Server using NLB Cluster & Other Two node Cluster for Staging server useing Failover Cluster & it will Syncs with NLB Cluster Node in the regular interval using Tidal Scheduler.

Appreciate, if you see the Architecture Diagram and give me your valuable suggestion.

I uploaded on

http://sunilgidwani.blogspot.com/

Blue Dash Line is for Internal Network

Black Solid Line is for External Network

Red Dot is not HeartBeat Network

Thanks

Sunil

|||

HI Darren

Appreciate, if you could reply the above mail as earliest.

Thanks

Sunil

|||It should be possible to maintian extremely high uptimes with this sort of architecture. You might need to talk to a networking expert to make sure that your network infrastructure has full redundancy so there is no other single point of failure, but from a SQL perspective it looks very solid.|||

Hi Darryn

I appreciate your help & repose, now I am planning to implement your suggested architecture for Analysis Server using NLB Cluster.

Thanks one again

Sunil

an unkown insert type by me(!)

Hi Dear Coder Friends;

i just discovered below insert type in MSSQL 2005 Automatic creating insert sentence but i couldn't use because there is a syntax error.

INSERT INTO [KimlikBilgileri]

([CvId]

,[KimlikNo]

,[Ad]

,[Soyad]

,[Cinsiyet]

,[DogumTarihi]

,[UlkeId]

,[DogumYeri]

,[MedeniDurumu])

VALUES

(<CvId, int,>

,<KimlikNo, char(11),>

,<Ad, varchar(50),>

,<Soyad, varchar(50),>

,<Cinsiyet, char(5),>

,<DogumTarihi, smalldatetime,>

,<UlkeId, int,>

,<DogumYeri, varchar(50),>

,<MedeniDurumu, varchar(8),>)

Question for above;

-- What's this type called?

-- does it make any security bug like injections?

i want to use this one as a stored proc to add "create proc KimlikBilgilerInsert as ". So i think i don't have to declare one by one

am i right?

Thank you for your valuable knowledge

The syntax error is due to the datatypes. Remove all the datatype indicators.

This is called an INSERT statement.

Yes, it is susceptible to SQL Injection -there are very long varchar(50) fields, and there is no data validation. I would consider where the values are gathered, and if from textboxes on a form, then I would put this statement in a stored procedure and add some data validation code.

alternative to Outlook/ Exchange?

Is there anything other than Outlook/ Exchange that can be used in order to
set up SQL Mail/ Alerts? My Network Admin is hoping for some type of SMTP
Relay type of deal.
Check the following extended stored procedure for SMTP mail:
http://www.sqldev.net/xp/xpsmtp.htm
-Sue
On Tue, 5 Apr 2005 16:30:42 -0700, "ChrisR"
<noemail@.bla.com> wrote:

>Is there anything other than Outlook/ Exchange that can be used in order to
>set up SQL Mail/ Alerts? My Network Admin is hoping for some type of SMTP
>Relay type of deal.
>
|||Thanks Sue. Do you know if this is supported by MS?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:b1d6519p7d7ooer6vtumjjab67g5sijker@.4ax.com...
> Check the following extended stored procedure for SMTP mail:
> http://www.sqldev.net/xp/xpsmtp.htm
> -Sue
> On Tue, 5 Apr 2005 16:30:42 -0700, "ChrisR"
> <noemail@.bla.com> wrote:
>
|||Depends on what you mean - Microsoft doesn't support third
party utilities but adding the extended stored procedure and
using it doesn't leave you with an unsupported installation
of SQL Server.
It's stable to use...many people use it. I've been using it
for a few years. I think it's a improvement over using SQL
Mail, Agent Mail, MAPI, etc.
-Sue
On Wed, 6 Apr 2005 07:54:08 -0700, "ChrisR"
<noemail@.bla.com> wrote:

>Thanks Sue. Do you know if this is supported by MS?
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:b1d6519p7d7ooer6vtumjjab67g5sijker@.4ax.com.. .
>

alternative to Outlook/ Exchange?

Is there anything other than Outlook/ Exchange that can be used in order to
set up SQL Mail/ Alerts? My Network Admin is hoping for some type of SMTP
Relay type of deal.Check the following extended stored procedure for SMTP mail:
http://www.sqldev.net/xp/xpsmtp.htm
-Sue
On Tue, 5 Apr 2005 16:30:42 -0700, "ChrisR"
<noemail@.bla.com> wrote:
>Is there anything other than Outlook/ Exchange that can be used in order to
>set up SQL Mail/ Alerts? My Network Admin is hoping for some type of SMTP
>Relay type of deal.
>|||Thanks Sue. Do you know if this is supported by MS?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:b1d6519p7d7ooer6vtumjjab67g5sijker@.4ax.com...
> Check the following extended stored procedure for SMTP mail:
> http://www.sqldev.net/xp/xpsmtp.htm
> -Sue
> On Tue, 5 Apr 2005 16:30:42 -0700, "ChrisR"
> <noemail@.bla.com> wrote:
>>Is there anything other than Outlook/ Exchange that can be used in order
>>to
>>set up SQL Mail/ Alerts? My Network Admin is hoping for some type of SMTP
>>Relay type of deal.
>|||Depends on what you mean - Microsoft doesn't support third
party utilities but adding the extended stored procedure and
using it doesn't leave you with an unsupported installation
of SQL Server.
It's stable to use...many people use it. I've been using it
for a few years. I think it's a improvement over using SQL
Mail, Agent Mail, MAPI, etc.
-Sue
On Wed, 6 Apr 2005 07:54:08 -0700, "ChrisR"
<noemail@.bla.com> wrote:
>Thanks Sue. Do you know if this is supported by MS?
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:b1d6519p7d7ooer6vtumjjab67g5sijker@.4ax.com...
>> Check the following extended stored procedure for SMTP mail:
>> http://www.sqldev.net/xp/xpsmtp.htm
>> -Sue
>> On Tue, 5 Apr 2005 16:30:42 -0700, "ChrisR"
>> <noemail@.bla.com> wrote:
>>Is there anything other than Outlook/ Exchange that can be used in order
>>to
>>set up SQL Mail/ Alerts? My Network Admin is hoping for some type of SMTP
>>Relay type of deal.
>>
>

alternative to Outlook/ Exchange?

Is there anything other than Outlook/ Exchange that can be used in order to
set up SQL Mail/ Alerts? My Network Admin is hoping for some type of SMTP
Relay type of deal.Check the following extended stored procedure for SMTP mail:
http://www.sqldev.net/xp/xpsmtp.htm
-Sue
On Tue, 5 Apr 2005 16:30:42 -0700, "ChrisR"
<noemail@.bla.com> wrote:

>Is there anything other than Outlook/ Exchange that can be used in order to
>set up SQL Mail/ Alerts? My Network Admin is hoping for some type of SMTP
>Relay type of deal.
>|||Thanks Sue. Do you know if this is supported by MS?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:b1d6519p7d7ooer6vtumjjab67g5sijker@.
4ax.com...
> Check the following extended stored procedure for SMTP mail:
> http://www.sqldev.net/xp/xpsmtp.htm
> -Sue
> On Tue, 5 Apr 2005 16:30:42 -0700, "ChrisR"
> <noemail@.bla.com> wrote:
>
>|||Depends on what you mean - Microsoft doesn't support third
party utilities but adding the extended stored procedure and
using it doesn't leave you with an unsupported installation
of SQL Server.
It's stable to use...many people use it. I've been using it
for a few years. I think it's a improvement over using SQL
Mail, Agent Mail, MAPI, etc.
-Sue
On Wed, 6 Apr 2005 07:54:08 -0700, "ChrisR"
<noemail@.bla.com> wrote:

>Thanks Sue. Do you know if this is supported by MS?
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:b1d6519p7d7ooer6vtumjjab67g5sijker@.
4ax.com...
>

Alternative for LEFT() and CHARINDEX() that works with text Data Type

I have a stored procedure that receives a list of ids, to get the emails of each of those ids. The problem that I'm having is that I'm using a char data type that is max length is 8000, but the contact lists are getting bigger and soon they might reach the variable max length. The contact list that I receive will look something like this "1234,67523,67875,789687,", I'm using "," as a separator. Right now what I do is this

@.array_value=LEFT(@.ContactList, @.separator_position- 1)

The LEFT function doesn't work with data types text and ntext. But I'm in need of a string data type with a max length bigger than 8000. So I will apreciate if anyone knows of another function that does the same or similar and works with text data type, or any other ideas will be welcome.

Thanks,

Erick

what database & version are you using?

|||

SQL Server 2005

|||

Use varchar(max) instead of varchar(8000) then.

|||

Can you change the datatype? Try using varchar(MAX) or nvarchar(MAX). MSDN says built-in string functions have been built to work with the new large object types (MAX).

http://msdn2.microsoft.com/en-us/library/ms178158.aspx

|||

I just changed, I didn't know that worked. I just read thats new in sql 2005, that didn't work on 2000.

Thanks

|||

That worked but, Can I give max value to varchar in asp.net vb?

|||

http://msdn2.microsoft.com/en-us/library/a1904w6t(VS.80).aspx

|||

It looks like you just set the type to the normal DBType, i.e. use varchar for varchar(MAX), then set the length to -1 (this tells the parameter to use whatever length the data is, rather than specifying how much of the data to use).