Analysis Service Ports

SQL 2005 AS installed on a Cluster.
We are using a named instance and only one currently exists. MS White paper
reads: The default instance listens on port 2383, while named instances are
dynamically allocated. This behavior can be changed by explicitly assigning a
port to either the default or named instance of SSAS. If the value for Port
is 0, the default behavior described above is achieved. Otherwise, the value
is treated as an absolute (static) port number to use for the specified
instance.
I have modifed the Server PORT property, on the named instance (a default
instance does not exist) to listen on port 2382 and have added a rule to our
firewall to allow this traffic. However, AS continues to listen on port 2383.
Does anyone have any experience on this and how to change it.
Is there a range of ports I can tell our network team to open?
I am not sure on SQL 2005 but in SQL 2000 you need to start the service
again to take the effect of new port numbers...
Zaki.
"FredG" <FredG@.discussions.microsoft.com> wrote in message
news:6A9E45E1-E591-4086-B8A3-A5A1556BFFDC@.microsoft.com...
> SQL 2005 AS installed on a Cluster.
> We are using a named instance and only one currently exists. MS White
paper
> reads: The default instance listens on port 2383, while named instances
are
> dynamically allocated. This behavior can be changed by explicitly
assigning a
> port to either the default or named instance of SSAS. If the value for
Port
> is 0, the default behavior described above is achieved. Otherwise, the
value
> is treated as an absolute (static) port number to use for the specified
> instance.
> I have modifed the Server PORT property, on the named instance (a default
> instance does not exist) to listen on port 2382 and have added a rule to
our
> firewall to allow this traffic. However, AS continues to listen on port
2383.
> Does anyone have any experience on this and how to change it.
> Is there a range of ports I can tell our network team to open?
|||I have restarted the service multiple times and it continues to listen on
this port.
"Zakiuddin Mohammed" wrote:

> I am not sure on SQL 2005 but in SQL 2000 you need to start the service
> again to take the effect of new port numbers...
> Zaki.
> "FredG" <FredG@.discussions.microsoft.com> wrote in message
> news:6A9E45E1-E591-4086-B8A3-A5A1556BFFDC@.microsoft.com...
> paper
> are
> assigning a
> Port
> value
> our
> 2383.
>
>

Analysis Service in cluster

Hi
I have 2-node active/active cluster. I have installed 2 SQL instances (one
per node).
Now I want to install Analysis service in cluster for datawarehouse . I have
some questions regarding this.
1. Should I have seperate cluster group to be created for this? if so, how
many disk drives would be required for that group (recommendations).
2. Should I give different instance name for analysis serviceor I can be
same instance name as SQL instance.
Thanks and Regards,
Vijay
Vijay
1. Create a separate cluster groups and add Disk to it ... I would start
with one disk or more depending on how you have laid out the DW
2. For the instance - I would recommend a different name
Thanks
"Vijay" <Vijay@.discussions.microsoft.com> wrote in message
news:16D83565-9F15-4A5D-A745-B2C47EE0BF5F@.microsoft.com...
> Hi
> I have 2-node active/active cluster. I have installed 2 SQL instances (one
> per node).
> Now I want to install Analysis service in cluster for datawarehouse . I
> have
> some questions regarding this.
> 1. Should I have seperate cluster group to be created for this? if so,
> how
> many disk drives would be required for that group (recommendations).
> 2. Should I give different instance name for analysis serviceor I can be
> same instance name as SQL instance.
> Thanks and Regards,
> Vijay
>
|||Thanks for your help
"Seme Rollansa" wrote:

> Vijay
> 1. Create a separate cluster groups and add Disk to it ... I would start
> with one disk or more depending on how you have laid out the DW
> 2. For the instance - I would recommend a different name
> Thanks
>
> "Vijay" <Vijay@.discussions.microsoft.com> wrote in message
> news:16D83565-9F15-4A5D-A745-B2C47EE0BF5F@.microsoft.com...
>
>

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 issue with ReplicationUser

I have set-up a Replication on a SQL 2005 Cluster. I used a domain account to setup the replication. This domain account is also present in the local administrator group of both nodes of cluster. However my subscriptions fail with a message "Executed as user domain\user. A required privilege is not held by the client. The step failed." I am not sure what privilege the domain user should have.

Any tips on what kind of user account I should be using? Thanks.

This is not an issue any more.

An error occurred during decryption

I have a 2 node cluster that I recently installed. During the installation,
node 2 was active. After the installation of SQL 2005 and the SP1, I added
some linked servers that use SQL authentication. When I fail over to node 1,
I get the following error:
An error occurred during decryption.
On all of my linked servers. If I fail back, everything works great. I have
these same linked servers on several other non-clustered machines and they
work fine. Is this possible a service master key issue?Could be. Can you post the error from the errorlog, including a few lines
before and after it, for context? You can remove sensitive information like
IP addresses or account names - I just need to see the error number, state,
and message.
Thanks
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave B" <DaveB@.discussions.microsoft.com> wrote in message
news:535415A4-4727-4652-82DC-401A3FD74D00@.microsoft.com...
>I have a 2 node cluster that I recently installed. During the installation,
> node 2 was active. After the installation of SQL 2005 and the SP1, I added
> some linked servers that use SQL authentication. When I fail over to node
> 1,
> I get the following error:
> An error occurred during decryption.
> On all of my linked servers. If I fail back, everything works great. I
> have
> these same linked servers on several other non-clustered machines and they
> work fine. Is this possible a service master key issue?
>|||this is a few lines of the log file.
10/20/2006 01:17:05,spid9s,Unknown,Starting up database 'tempdb'.
10/20/2006 01:17:05,spid9s,Unknown,Clearing tempdb database.
10/20/2006 01:17:05,spid9s,Unknown,Starting up database 'model'.
10/20/2006 01:17:05,spid5s,Unknown,The NETBIOS name of the local node that
is running the server is 'SRVLWDSQL1'. This is an informational message only
.
No user action is required.
10/20/2006 01:17:05,spid5s,Unknown,Server name is 'SQLSERVER'. This is an
informational message only. No user action is required.
10/20/2006 01:17:05,spid5s,Unknown,An error occurred during decryption.
10/20/2006 01:17:05,spid5s,Unknown,Error: 15466<c/> Severity: 16<c/> State:
1.
10/20/2006 01:17:05,spid5s,Unknown,Starting up database 'mssqlsystemresource
'.
10/20/2006 01:17:05,spid5s,Unknown,SQL Trace ID 1 was started by login "sa".
"Laurentiu Cristofor [MSFT]" wrote:

> Could be. Can you post the error from the errorlog, including a few lines
> before and after it, for context? You can remove sensitive information lik
e
> IP addresses or account names - I just need to see the error number, state
,
> and message.
> Thanks
> --
> Laurentiu Cristofor [MSFT]
> Software Development Engineer
> SQL Server Engine
> http://blogs.msdn.com/lcris/
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Dave B" <DaveB@.discussions.microsoft.com> wrote in message
> news:535415A4-4727-4652-82DC-401A3FD74D00@.microsoft.com...
>
>|||I read your blog on the Service Master Key (SMK). One other question I have
is what is encrypted by the SMK? If I regenerate the key and/or force the
regeneration of the key, what data am I at risk of losing? I would assume SQ
L
login passwords and other database master keys. Anything else? I am just
wondering what I may need to recreate if I need to regenerate the key.
"Laurentiu Cristofor [MSFT]" wrote:

> Could be. Can you post the error from the errorlog, including a few lines
> before and after it, for context? You can remove sensitive information lik
e
> IP addresses or account names - I just need to see the error number, state
,
> and message.
> Thanks
> --
> Laurentiu Cristofor [MSFT]
> Software Development Engineer
> SQL Server Engine
> http://blogs.msdn.com/lcris/
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Dave B" <DaveB@.discussions.microsoft.com> wrote in message
> news:535415A4-4727-4652-82DC-401A3FD74D00@.microsoft.com...
>
>|||It does look like an error with the service master key decryption. Have you
copied your master database from another instance or performed any changes
to your installation before starting to see this?
I wrote a post on this exact topic on my blog. See
http://blogs.msdn.com/lcris/archive...10/572678.aspx.
Also, I am not clear of what you mean by linked servers and how do they
relate to your clustered installation and encryption. Can you elaborate on
your setup?
Thanks
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave B" <DaveB@.discussions.microsoft.com> wrote in message
news:123435F7-6631-4C05-B5CD-CA50884DCB64@.microsoft.com...[vbcol=seagreen]
>I read your blog on the Service Master Key (SMK). One other question I have
> is what is encrypted by the SMK? If I regenerate the key and/or force the
> regeneration of the key, what data am I at risk of losing? I would assume
> SQL
> login passwords and other database master keys. Anything else? I am just
> wondering what I may need to recreate if I need to regenerate the key.
> "Laurentiu Cristofor [MSFT]" wrote:
>|||The issue that I am seeing on my cluster is any linked servers using sql
authentication created on NodeA fail when I move over to NodeB. They work
fine when I fail back to NodeA. The error I get is:
An error occurred during decryption
That is really the only link between the cluster and linked server. This
error occured both during an upgrade of the server and a new server
installation. Before I regenerate the key, I just want to know how
destructive the regenerate could be. Anything that I may need to recreate, I
want to have scripted before my maintenance window starts.
"Laurentiu Cristofor [MSFT]" wrote:

> It does look like an error with the service master key decryption. Have yo
u
> copied your master database from another instance or performed any changes
> to your installation before starting to see this?
> I wrote a post on this exact topic on my blog. See
> http://blogs.msdn.com/lcris/archive...10/572678.aspx.
> Also, I am not clear of what you mean by linked servers and how do they
> relate to your clustered installation and encryption. Can you elaborate on
> your setup?
> Thanks
> --
> Laurentiu Cristofor [MSFT]
> Software Development Engineer
> SQL Server Engine
> http://blogs.msdn.com/lcris/
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Dave B" <DaveB@.discussions.microsoft.com> wrote in message
> news:123435F7-6631-4C05-B5CD-CA50884DCB64@.microsoft.com...
>
>|||Are your nodes running under the same service account? They should be, but
I'd like to double check.
To confirm that the error happens with the service master key (SMK), run
ALTER SERVICE MASTER KEY REGENERATE. This is safe - it will fail if it
detects any problem, so you can use it as a quick SMK integrity check.
The service master key encrypts credentials (CREATE CREDENTIAL), linked
server login passwords, and database master keys (DbMK). You can loose the
first two if you FORCE regenerate the SMK, but you should still be able to
recover your DbMKs using their password encryption. This was explained in
the post I referred you to.
However, if things work on NodeA but not on NodeB, the problem is not with
the SMK. It looks like something is wrong with your cluster configuration
and the key cannot be recovered after failover. If what I told you so far
doesn't help, I suggest that you open a feedback report at
http://connect.microsoft.com/site/s...aspx?SiteID=68 and provide us
with more details about your configuration. Also request that the report be
assigned to me.
Thanks
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave B" <DaveB@.discussions.microsoft.com> wrote in message
news:CE0975B0-FD47-4193-B485-7EED618C20E7@.microsoft.com...[vbcol=seagreen]
> The issue that I am seeing on my cluster is any linked servers using sql
> authentication created on NodeA fail when I move over to NodeB. They work
> fine when I fail back to NodeA. The error I get is:
> An error occurred during decryption
> That is really the only link between the cluster and linked server. This
> error occured both during an upgrade of the server and a new server
> installation. Before I regenerate the key, I just want to know how
> destructive the regenerate could be. Anything that I may need to recreate,
> I
> want to have scripted before my maintenance window starts.
>
> "Laurentiu Cristofor [MSFT]" wrote:
>|||The ALTER SERVICE MASTER KEY REGENERATE command worked. No errors, no
warnings no issues with linked servers. However, I started experiencing othe
r
issues after the failover. After a few hours of looking, it appeared the
second node did not receive the SP1 update. I know this is a different topic
so I will start a new discussion if I can’t find any resolution.
Thanks for you help.
"Laurentiu Cristofor [MSFT]" wrote:

> Are your nodes running under the same service account? They should be, but
> I'd like to double check.
> To confirm that the error happens with the service master key (SMK), run
> ALTER SERVICE MASTER KEY REGENERATE. This is safe - it will fail if it
> detects any problem, so you can use it as a quick SMK integrity check.
> The service master key encrypts credentials (CREATE CREDENTIAL), linked
> server login passwords, and database master keys (DbMK). You can loose the
> first two if you FORCE regenerate the SMK, but you should still be able to
> recover your DbMKs using their password encryption. This was explained in
> the post I referred you to.
> However, if things work on NodeA but not on NodeB, the problem is not with
> the SMK. It looks like something is wrong with your cluster configuration
> and the key cannot be recovered after failover. If what I told you so far
> doesn't help, I suggest that you open a feedback report at
> http://connect.microsoft.com/site/s...aspx?SiteID=68 and provide us
> with more details about your configuration. Also request that the report b
e
> assigned to me.
> Thanks
> --
> Laurentiu Cristofor [MSFT]
> Software Development Engineer
> SQL Server Engine
> http://blogs.msdn.com/lcris/
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Dave B" <DaveB@.discussions.microsoft.com> wrote in message
> news:CE0975B0-FD47-4193-B485-7EED618C20E7@.microsoft.com...
>
>

AMD Opteron 64 & SQL 2000 32 bit

Are there any known issues with the AMD Opteron 200 Series running Windows
2003 Ent. Edition and SQL 2000 Ent in a cluster?
In a few threads I have seen remarks that SP 4 is needed, but in the feature
list of SP4 there is no mention about this.
Thanks,
JanThere's is only a 64 bit version of SQL Server 2000 for the Intel Itanium processor. Running 32 bit
SQL Server on WOW (Windows on Windows), where Windows emulates 32 bit on 64 bit HW using the x64
processor architecture and OS wasn't supported until sp4. This link might have some details:
http://www.microsoft.com/downloads/details.aspx?FamilyID=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&displaylang=en
As for issues, I don't know. I imagine that this isn't a very common setup. And with SQL Server 2005
out where there exists an x64 version, it might not be a common setup either?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jan" <Jan@.discussions.microsoft.com> wrote in message
news:3F4C6DF9-5D21-4F70-AC48-7BF6EE2A14A0@.microsoft.com...
> Are there any known issues with the AMD Opteron 200 Series running Windows
> 2003 Ent. Edition and SQL 2000 Ent in a cluster?
> In a few threads I have seen remarks that SP 4 is needed, but in the feature
> list of SP4 there is no mention about this.
> Thanks,
> Jan

Alternative to IPSec?

The client I'm working with has a 2-node Failover cluster. Owing to their
environment, they have several machines accessing the many databases. For
cost reasons, they went with SQL standard.
As to securing the traffic. I understand that IPSec isn't supported for
failover cluster, owing to the recovery time (6 minutes, according to the
document).
But the recovery for SQL standard doesn't allow external connections until
all databases are recovered. If the server has a lot of databases (perhaps
50), there may already be a delay.
I guess the questions I'm asking are:
1>Outside of the time delay, is there any other reasons to avoid IPSec?
2>In an environment with a lot of potential clients, what are the
recommended alternatives?
Thanks for any insight!
Bob Coppedge
me (at) RLCoppedge (put a dot here) com
IPSec can be processor intensive; so, offloading the encryption to an
outside engine would increase throughput. This could be through a support
NIC with these features, or a dedicated hardware appliance.
Short of that, you could use SSL encryption and/or HTTPS for communication.
Microsoft provides a good document on the subject through MSDN:
http://www.microsoft.com/downloads/details.aspx?displaylang=en&familyid=055ff772-97fe-41b8-a58c-bf9c6593f25e
Good luck.
Sincerely,
Anthony Thomas

"RL Coppedge" <RLCoppedge@.hotmail.com.(nospam)> wrote in message
news:28CF43D3-38DC-47A8-9C46-CC25D1A01150@.microsoft.com...
> The client I'm working with has a 2-node Failover cluster. Owing to their
> environment, they have several machines accessing the many databases. For
> cost reasons, they went with SQL standard.
> As to securing the traffic. I understand that IPSec isn't supported for
> failover cluster, owing to the recovery time (6 minutes, according to the
> document).
> But the recovery for SQL standard doesn't allow external connections until
> all databases are recovered. If the server has a lot of databases
(perhaps
> 50), there may already be a delay.
> I guess the questions I'm asking are:
> 1>Outside of the time delay, is there any other reasons to avoid IPSec?
> 2>In an environment with a lot of potential clients, what are the
> recommended alternatives?
> Thanks for any insight!
> Bob Coppedge
> me (at) RLCoppedge (put a dot here) com
>