Active/Active configuration

Few questions on Active/Active configuration (SQL 2000):
-Any articles on setting up a two node Active/Active configuration (ideally
step by step)? Almost everything out there is on Active/Passive.
-Since there are at minimum 2 instances running at the same time on a two
node active/active configuration, is there still 1 virtual IP address? If so,
how does sql server knows which instance an application/user is refering to?
-On an active/passive configuation, microsoft recommends two networks
(public/private) but it can be done with only 1 (not recommended but
possible). Does this also apply on an Active/Active configurtion? We are
using blade servers and only 1 network can be configured so I want to make
sure that Active/Active can work with only 1 network.
-We have 2 main databases and we are planning to have one database on each
node. Would'nt this have a better performance instead of having both database
on one server (active/passive). I am aware that clustering is for
availability only but it seems like we could get a better performance in
active/active. Is this a valid assumption?
Thank you all
--
System Eng.
MSc in CS, MCSE4, IBM Certified MQ 5.3 Administrator
"I saw it work in a cartoon once so I am pretty sure I can do it."
> -Any articles on setting up a two node Active/Active configuration (ideally
> step by step)? Almost everything out there is on Active/Passive.
Setting up another instance in a cluster is no different than setting up the
first instance. Books Online has detailed steps.

> -Since there are at minimum 2 instances running at the same time on a two
> node active/active configuration, is there still 1 virtual IP address? If so,
> how does sql server knows which instance an application/user is refering to?
You need a separate IP addresse for each clustered instance.

> -On an active/passive configuation, microsoft recommends two networks
> (public/private) but it can be done with only 1 (not recommended but
> possible). Does this also apply on an Active/Active configurtion? We are
> using blade servers and only 1 network can be configured so I want to make
> sure that Active/Active can work with only 1 network.
Still applies. But since the purpose of a failover cluster is to provide
high availability, you really shouldn't share a single network. That kind of
defeats the HA purpose.

> -We have 2 main databases and we are planning to have one database on each
> node. Would'nt this have a better performance instead of having both database
> on one server (active/passive). I am aware that clustering is for
> availability only but it seems like we could get a better performance in
> active/active. Is this a valid assumption?
You may get better performance by virute of running each database on its own
host, thus using separate processors, memory, network cards, HBA's, etc. That
is, if your database can take advantage of these separate hardware
components. So, if one of your databases is cpu bound, for instance, having
it on its own server would certainly help.
Linchi
"Sas" wrote:

> Few questions on Active/Active configuration (SQL 2000):
> -Any articles on setting up a two node Active/Active configuration (ideally
> step by step)? Almost everything out there is on Active/Passive.
> -Since there are at minimum 2 instances running at the same time on a two
> node active/active configuration, is there still 1 virtual IP address? If so,
> how does sql server knows which instance an application/user is refering to?
> -On an active/passive configuation, microsoft recommends two networks
> (public/private) but it can be done with only 1 (not recommended but
> possible). Does this also apply on an Active/Active configurtion? We are
> using blade servers and only 1 network can be configured so I want to make
> sure that Active/Active can work with only 1 network.
> -We have 2 main databases and we are planning to have one database on each
> node. Would'nt this have a better performance instead of having both database
> on one server (active/passive). I am aware that clustering is for
> availability only but it seems like we could get a better performance in
> active/active. Is this a valid assumption?
> Thank you all
> --
> --
> System Eng.
> MSc in CS, MCSE4, IBM Certified MQ 5.3 Administrator
> "I saw it work in a cartoon once so I am pretty sure I can do it."
|||Linchi, just make sure your application is active/active ready. Will
both nodes assign duplicate IDs such as customer or order numbers? Is
common application state stored in shared memory? Will a response get
returned to a wrong node?...and so on. www.availabilitydigest.com
On Jan 19, 3:38 pm, Linchi Shea <LinchiS...@.discussions.microsoft.com>
wrote:[vbcol=seagreen]
> first instance. Books Online has detailed steps.
>
> high availability, you really shouldn't share a single network. That kind of
> defeats the HA purpose.
> host, thus using separate processors, memory, network cards, HBA's, etc. That
> is, if your database can take advantage of these separate hardware
> components. So, if one of your databases is cpu bound, for instance, having
> it on its own server would certainly help.
> Linchi
>
> "Sas" wrote:
>
|||> Linchi, just make sure your application is active/active ready.
I don't know what you meant by 'active/active ready'. There is nothing you
need to ready your application for.

> Will both nodes assign duplicate IDs such as customer or order numbers?
The SQL instance is running on one node at a time. At no time, it would run
on both nodes, and no database can be accessed by more than one node at a
time.

> Is common application state stored in shared memory?
There is no shared memory.

> Will a response get returned to a wrong node?...and so on.
It doesn't matter what node is running the SQL instance. The instance is
accessed via a virtual IP address that is not the same as the node IP address.
Linchi
"jreederhi@.nac.net" wrote:

> Linchi, just make sure your application is active/active ready. Will
> both nodes assign duplicate IDs such as customer or order numbers? Is
> common application state stored in shared memory? Will a response get
> returned to a wrong node?...and so on. www.availabilitydigest.com
> On Jan 19, 3:38 pm, Linchi Shea <LinchiS...@.discussions.microsoft.com>
> wrote:
>
|||Linchi. It looks as if we are using different definitions of active/
active. To me, an active/active configuration is one in which two or
more nodes are executing the same application using synchronized
database copies. If one node goes down, its users can be switched to
a surviving node in seconds, thus virtually eliminating downtime due
to node failures. If you are using a single common database, there
should be no application issues. www.availabilitydigest.com
On Jan 27, 11:58 pm, Linchi Shea
<LinchiS...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> need to ready your application for.
> on both nodes, and no database can be accessed by more than one node at a
> time.
>
> accessed via a virtual IP address that is not the same as the node IP address.
> Linchi
>
> "jreede...@.nac.net" wrote:
>
>
>
>
|||That type of clustering is not supported by SQL Server 2005. Each instance
of SQL can run on a single node at a time. Other than a read-only shared
database model for reporting, there is no provision in SQL Server for
multiple SQL instances runing on different nodes to access the same data
store.
This is the major reason I dislike the Active/Passive and Active/Active
nomenclature. It is a holdover from SQL 7.0 clustering and does not
accurately reflect the capabilities of the current product. And 7.0 didn't
do this either, it just had a functional distinction between the primary
node and its failover partner that led to this unfortunate choice of names.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<jreederhi@.nac.net> wrote in message
news:1170095155.783463.3200@.h3g2000cwc.googlegroup s.com...
> Linchi. It looks as if we are using different definitions of active/
> active. To me, an active/active configuration is one in which two or
> more nodes are executing the same application using synchronized
> database copies. If one node goes down, its users can be switched to
> a surviving node in seconds, thus virtually eliminating downtime due
> to node failures. If you are using a single common database, there
> should be no application issues. www.availabilitydigest.com
> On Jan 27, 11:58 pm, Linchi Shea
> <LinchiS...@.discussions.microsoft.com> wrote:
>
|||<jreederhi@.nac.net> wrote in message
news:1170095155.783463.3200@.h3g2000cwc.googlegroup s.com...
> Linchi. It looks as if we are using different definitions of active/
> active. To me, an active/active configuration is one in which two or
> more nodes are executing the same application using synchronized
> database copies.
Your definition is wrong. High Availability clustering is not about
horizontal scaling.

> If one node goes down, its users can be switched to
> a surviving node in seconds, thus virtually eliminating downtime due
> to node failures.
This is what Server Clustering, or MSCS, is - it is a high availability
platform that helps reduce downtime. It does not provide horizontal scaling.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
The next ClusterHelp classes are:
Denver starting Feb 12th
NYC starting Feb 19th
London starting March 6th
|||Geoff: I suspect that the inability for two instances of a database
manager to access the same physical database is common. When we speak
of active/active, we mean that two nodes, each with its own database,
are cooperating in the same application. The two database copies are
kept in synchronism by some means, typically data replication. By
keeping replication latency small (hundreds of milliseconds with
today's products), the problem of data loss following a node failure
or of data collisions is minimized. Furthermore, the two nodes can be
geographically separated for disaster tolerance. (See http://
availabilitydigest.com/public_articles/what_is_active-active.pdf).
There are several products that support SQL Server that can provide
this capability. www.availabilitydigest.com
On Jan 29, 1:33 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:[vbcol=seagreen]
> That type of clustering is not supported by SQL Server 2005. Each instance
> of SQL can run on a single node at a time. Other than a read-only shared
> database model for reporting, there is no provision in SQL Server for
> multiple SQL instances runing on different nodes to access the same data
> store.
> This is the major reason I dislike the Active/Passive and Active/Active
> nomenclature. It is a holdover from SQL 7.0 clustering and does not
> accurately reflect the capabilities of the current product. And 7.0 didn't
> do this either, it just had a functional distinction between the primary
> node and its failover partner that led to this unfortunate choice of names.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> <jreede...@.nac.net> wrote in messagenews:1170095155.783463.3200@.h3g2000cwc.goog legroups.com...
>
>
>
>
>
>
>
>
|||<jreederhi@.nac.net> wrote in message
news:1170181441.248151.311950@.j27g2000cwj.googlegr oups.com...
> Geoff: I suspect that the inability for two instances of a database
> manager to access the same physical database is common.
It is part of the shared nothing model for MSCS.

> When we speak
> of active/active, we mean that two nodes, each with its own database,
> are cooperating in the same application.
No, when you speak of active/active, you mean two nodes, each with its own
separate database in its own virtual server. Two instances of SQL, one on
each node.

> The two database copies are
> kept in synchronism by some means, typically data replication.
You are not talking about Microsoft clustering here.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
The next ClusterHelp classes are:
Denver starting Feb 12th
NYC starting Feb 19th
London starting March 6th
|||<jreederhi@.nac.net> wrote in message
news:1170181441.248151.311950@.j27g2000cwj.googlegr oups.com...
> Geoff: I suspect that the inability for two instances of a database
> manager to access the same physical database is common. When we speak
> of active/active, we mean that two nodes, each with its own database,
> are cooperating in the same application. The two database copies are
> kept in synchronism by some means, typically data replication. By
> keeping replication latency small (hundreds of milliseconds with
> today's products), the problem of data loss following a node failure
> or of data collisions is minimized. Furthermore, the two nodes can be
> geographically separated for disaster tolerance. (See http://
> availabilitydigest.com/public_articles/what_is_active-active.pdf).
> There are several products that support SQL Server that can provide
> this capability. www.availabilitydigest.com
>
I disagree with this, in relation to Microsoft Cluster Service.
The PDF file referenced here is NOT describing Active/Active for Microsoft
Clustering
Please do NOT refer to this PDF in relation to MSCS
The technologies represented on your website, are not in relation to
Microsoft, and terminology you use is conflicting with standard terminology
within the Microsoft scope. I understand that you have some patented
technology to sell. Please do not use newsgroups to advertise, as this is
considered spam.
Please see Microsofts own definition on Active/Active :
http://technet2.microsoft.com/WindowsServer/en/library/8846a72b-0882-4a24-8eee-a768e52925281033.mspx?mfr=true
(scroll down to the "Q. What is active/active verses active/passive?")
Rgds,
Edwin.

Active/Active Clustering

Does SQL Server 2005 support Active/Active clustering? If so, where can I find a how-to on setting up a active/active cluster?

Yes. Fully supported.

Books on line is the best place to start. Beyond that, there are plenty of articles around such as this one ;

http://www.databasejournal.com/features/mssql/article.php/3444181

|||

I'm not sure where you got your information but Active/Active Clustering IS NOT supported on SQL Server 2005.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=603251&SiteID=1

|||

That's definitely a news flash, because I have dozens of clustering implementations of SQL Server 2005 that have multiple instances installed on them. It is VERY definitely supported and has been possible since SQL Server 2000. This is NOT something that will ever go away.

However, there really is no such thing as an Active/Active SQL Server cluster. I can install a single instance of SQL Server into a cluster and I can install multiple instances of SQL Server into a cluster. Whether I decide to run all of the instances on a single node or run instances on each of the nodes is entirely up to my whim as well as being a completely transitory decision.

The post you are quoting has nothing to do with what you are asking. SQL Server has never allowed multiple instances to serve the same physical database. SQL Server 2005 Enterprise Edition enables a capability when combined with a SAN that does allow multiple instances to connect to the same database for read ONLY operations. Never have you been able to connect multiple instance to the same database and make changes. This capability has nothing at all to do with clustering.

|||The question was: Does SQL Server 2005 support Active/Active clustering?|||

Clearly this is a issue of what you mean by Active/Active. Microsoft used that term a while ago to mean SQL instances running on all cluster nodes, as opposed to only 1 node with the other node(s) existing simply to take over in case something went wrong.

Obviously other RDBMS's have a different meaning for active/active.

Active Directory access in SSIS

Does anyone have any examples of setting up a connection manager in SSIS to read information from Microsoft Active Directory? I would like to retrieve a list of users and populate them into a SQL Server table.

Thanks!
Harry

BOL has an example for a script task but it should be fairly straight forward to convert it to a script component. There is also an OLEDB driver for active directory that should be able to be used directly from the OLEDB source (although I don't know whether anyone has actually tried using it yet).

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/a88fefbb-9ea2-4a86-b836-e71315bac68e.htm

HTH,

Matt

Acitvation Failure

Setting up database mail of SQL Server 2005 and get an Activation Failure from the database log. Any ideas?

Date 7/11/2007 1:26:03 PM
Log Database Mail (Database Mail Log)

Log ID 2
Last Modified 7/11/2007 1:26:03 PM
Last Modified By sa

Message
Activation failure.

There should be additional information in the event log or SQL Server log.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Acessing SQL Server over the web with shared IP

I am new to setting up SQL Server and would greatly appreciate if
someone can help me out with this issue:

I have successfully set up SQL Server but want to be able for clients
of mine to be able to connect to it via the web through enterprise
manager. I have tried everything I could think of to get it to work. I
made sure the correct ports where open in the firewall (1433,1434).
Everytime I try to connecting using the IP of the box that the SQL
Server resides I get SQL Server does not exist or access denied. I
verified that the login credentials being used for the database I am
trying to correct to are correct. Still the same error message.

How I would like to set it up would be to create a subdomain on the
box and point it to SQL Server so that is what would be entered when
registering the group in enterprise manager.

I am currently running all my sites through vhost on a single IP using
IIS 2003. SQL Server is installed on the box where all of the sites
are being served up.

Is it impossible to set-up SQL Server to allow connections over the
web when using a Shared IP?

I have been researching this for over a week now and hope someone out
there can help me.

Thanks in advance!!Chris (melovepoi@.yahoo.com) writes:
> I am new to setting up SQL Server and would greatly appreciate if
> someone can help me out with this issue:
> I have successfully set up SQL Server but want to be able for clients
> of mine to be able to connect to it via the web through enterprise
> manager. I have tried everything I could think of to get it to work. I
> made sure the correct ports where open in the firewall (1433,1434).
> Everytime I try to connecting using the IP of the box that the SQL
> Server resides I get SQL Server does not exist or access denied. I
> verified that the login credentials being used for the database I am
> trying to correct to are correct. Still the same error message.
> How I would like to set it up would be to create a subdomain on the
> box and point it to SQL Server so that is what would be entered when
> registering the group in enterprise manager.
> I am currently running all my sites through vhost on a single IP using
> IIS 2003. SQL Server is installed on the box where all of the sites
> are being served up.
> Is it impossible to set-up SQL Server to allow connections over the
> web when using a Shared IP?

I'm not really sure what you mean with "connect to it via the web through
enterprise manager". Do you mean that they will connect over the Internet?
If they were to go from EM through a web site to SQL Server sounds a
little funny to me.

As for why things are not working, I don't really have much advice, but
I take the occassion to respond, because you may not want to do that
after all.

Exposing SQL Server directly on the Internet is something you should
avoid. And if you do it - make sure that you have SQL Server authentication
turned off. There is no protection in SQL Server against brute-force
attacks, other than logging, so no matter how strong password you have
for "sa", it is likely to be cracked sooner or later if someone is dead
set on doing it. I don't know if you are able to authenticate your clients
through Windows authentication. If you are, you might be safe. But it
might be a better option to offer them a VPN connection from which they
can access SQL Server that way.

What I can add about the error message "SQL Server does not exist or
access denied" is that playing with login credentials on SQL Server
is of no use. The error means that you never find the server, so it
is not able to authenticate you anyway.

This KB article discusses this message more in detail:
http://support.microsoft.com/defaul...B;EN-US;q328306.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In article <1f86f93.0408201949.5a756cf3@.posting.google.com>,
melovepoi@.yahoo.com says...
> I am new to setting up SQL Server and would greatly appreciate if
> someone can help me out with this issue:
> I have successfully set up SQL Server but want to be able for clients
> of mine to be able to connect to it via the web through enterprise
> manager. I have tried everything I could think of to get it to work. I
> made sure the correct ports where open in the firewall (1433,1434).
> Everytime I try to connecting using the IP of the box that the SQL
> Server resides I get SQL Server does not exist or access denied. I
> verified that the login credentials being used for the database I am
> trying to correct to are correct. Still the same error message.

First, let me say one important thing - exposing your SQL server
directly to the internet is always a BAD IDEA. There is no reason to
expose it directly to the internet. You should have the users VPN into
your server and access the SQL Server through the VPN.

It was servers like yours that let slammer spread around the world in
record time.

> How I would like to set it up would be to create a subdomain on the
> box and point it to SQL Server so that is what would be entered when
> registering the group in enterprise manager.
> I am currently running all my sites through vhost on a single IP using
> IIS 2003. SQL Server is installed on the box where all of the sites
> are being served up.
> Is it impossible to set-up SQL Server to allow connections over the
> web when using a Shared IP?

Since you have a router with port-forwarding (or a firewall with port
forwarding) you need to tell people to use your PUBLIC ip address when
they are outside of your network - external people access the SQL Server
using the Public IP that you forwarded to the internal IP.

One other thing - don't give users access to the SA account and don't
give them access to any other database (just their own). We did some
work for a company that let users access the DB over the net, the
company had created super user accounts for people to manage their
database remotely - as it turns out, the accounts had access to every
database in their system - some 30 databases on the server. It was
interesting to run an audit, find personal financial info on the other
databases, and then contact the ISP and the Owners of the databases to
let them know that anyone with access to any database could see all of
them as SA. The entire thing was taken off-line for a week while they
figured out how to manage it (should have taken about an hour).

I strongly suggest, even BEG, that you have users VPN into the
network/server and then gain access to the SQL server - do not expose it
directly to the internet.

--
--
spamfree999@.rrohio.com
(Remove 999 to reply to me)|||Hi Chris

It is possible to allow access via the web. You need to use Enterprise
Manager from 2000 upwards and to check that TCP/IP is the connection
protocol. I would suggest not using the standard port but moving it
much higher up the range to a more 'random' port (purely to reduce
risk of hacking).

I'm not exactly sure what you mean by creating a sub-domain on the
box. My interpretation is that you want to create something like
subdomain.mydomain.com and use that to connect to the database. This
you can do, but not on the box. You will need to talk to your ISP and
get them to create the subdomain and point it to the IP address of
your server. Then I think you will be able to register the server by
entering subdomain.mydomain.com in EM.

Hope this helps

Sam