ACTIVE/ACTIVE configuration

We are looking at installing a 2 Node ACTIVE/ACTIVE SQL
Server configuration utilizing two DELL PE6600 servers,
both connected via FIBRE to a DELL CX200(CLARION). The
OS will be clustered in a ACTIVE/ACTIVE configuration as
well.
The main purpose for attempting this is more for
spreading processing across systems more than for
failover capabilities.
I have a few questions, the first would be has anyone
accomplished this? Secondly, in a SQL server
ACTIVE/ACTIVE configuration, can both instances utilize
one shared disk configuration, so that database merging
does not need to happen?
Any help/advice would be greatly appreciated as i'm
drawing up a plan.
Failover clustering is designed for high availability. Failover clustering
is NOT a scale-out technology. There are some scenarios where data
partitioning and merge replication can be used in conjunction with
multi-instance clustering to achieve scale-out, but they require intensive
design and management. You are probably better off buying a bigger box for
your server than trying to scale out.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:81e201c431f4$708e9430$a501280a@.phx.gbl...
> We are looking at installing a 2 Node ACTIVE/ACTIVE SQL
> Server configuration utilizing two DELL PE6600 servers,
> both connected via FIBRE to a DELL CX200(CLARION). The
> OS will be clustered in a ACTIVE/ACTIVE configuration as
> well.
> The main purpose for attempting this is more for
> spreading processing across systems more than for
> failover capabilities.
>

> I have a few questions, the first would be has anyone
> accomplished this? Secondly, in a SQL server
> ACTIVE/ACTIVE configuration, can both instances utilize
> one shared disk configuration, so that database merging
> does not need to happen?
>
No.

> Any help/advice would be greatly appreciated as i'm
> drawing up a plan.
|||I guess with that being said, would you have any ideas in
regards to distributed processing.
High availiability is what i'm looking for, just in an
active/active configuration. Can I not have a sql server
running on two different systems and look at the same
logical data source via SAN solution?

>--Original Message--
>Failover clustering is designed for high availability.
Failover clustering
>is NOT a scale-out technology. There are some scenarios
where data
>partitioning and merge replication can be used in
conjunction with
>multi-instance clustering to achieve scale-out, but they
require intensive
>design and management. You are probably better off
buying a bigger box for
>your server than trying to scale out.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:81e201c431f4$708e9430$a501280a@.phx.gbl...
as
>No.
>
>.
>
|||SQL Failover clustering works on a shared-nothing setup. Each instance has
exclusive control over the underlying resources, including its portion of
the shared storage system. The cluster service arbitrates ownership of the
underlying resources so that only one host node at a time controls each
resource.
Distributed database computing is not quite what is advertised. The TPC-C
benchmark is particularly well suited to a distributed model (coincidence
maybe?), but not all applications work as well. Buying a larger box is
probably where you want to go if you need more processing power.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:830d01c43207$a14ec370$a601280a@.phx.gbl...[vbcol=seagreen]
> I guess with that being said, would you have any ideas in
> regards to distributed processing.
> High availiability is what i'm looking for, just in an
> active/active configuration. Can I not have a sql server
> running on two different systems and look at the same
> logical data source via SAN solution?
>
> Failover clustering
> where data
> conjunction with
> require intensive
> buying a bigger box for
> message
> as
|||That makes since. I have been doing some reading on
active/active cluster configurations. Just want to
understand that in that configuration each instance
serves it's own needs until the time that one fails and
then the other instance picks up the additional workload
of the failed instance. Is that correct?

>--Original Message--
>SQL Failover clustering works on a shared-nothing
setup. Each instance has
>exclusive control over the underlying resources,
including its portion of
>the shared storage system. The cluster service
arbitrates ownership of the
>underlying resources so that only one host node at a
time controls each
>resource.
>Distributed database computing is not quite what is
advertised. The TPC-C
>benchmark is particularly well suited to a distributed
model (coincidence
>maybe?), but not all applications work as well. Buying
a larger box is
>probably where you want to go if you need more
processing power.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:830d01c43207$a14ec370$a601280a@.phx.gbl...
in[vbcol=seagreen]
server[vbcol=seagreen]
scenarios[vbcol=seagreen]
they[vbcol=seagreen]
SQL[vbcol=seagreen]
servers,[vbcol=seagreen]
The[vbcol=seagreen]
configuration[vbcol=seagreen]
anyone[vbcol=seagreen]
utilize[vbcol=seagreen]
merging
>
>.
>
|||Nope. You are confusing instances and hosts. Not surprising since
active-active is a holdover from the SQL 7.0 days and is misleading for SQL
2000.
Lets start with a single-instance cluster. Two nodes (host computers), one
SQL instance. The instance runs on one host at a time. The cluster
services on all nodes monitor the running instance and if it fails a series
of tests (looks-alive and is-alive) the instance is deemed failed and is
restarted. The restart may be on the original node or on the other node.
You can add a second, independant instance that normally runs on the second
node but can run on either node. The two instances have nothing in common
except the set of host nodes that they can run on. There are some extra
concerns when using multiple instances on a cluster to make sure they can
'stack' on a single host node if necessary, but otherwise they do not
interact in any way. People incorrectly refer to this as 'Active-Active',
meaning both nodes have active instances. However, under SQL 2000 you can
have up to 16 instances on a cluster, so the naming quickly can get out of
hand.
Under SQL 7.0 the nodes were not completely interchangable so the
active-passive and active-active names were important. Each node owned a
single instance and the other node could run it but not make certain changes
to the setup. Now, single-instance and multiple-instance naming is more
meaningful since the nodes are truly interchangable. This is especially
true now that Windows 2003 allows 8 node clusters using Enterprise Edition.
(Active-Active-Passive-Passive-Active.. Blah, Blah, Blah).
Sounds like you need a single-instance installation running on fairly beefy
hardware. FYI, the CX-200 has been replaced with the CX-300. If you go
with a system larger than a 6600/6650 I would strongly look at the CX-500 or
CX-700 for the performance benefits.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:837001c43213$70e59cc0$a501280a@.phx.gbl...[vbcol=seagreen]
> That makes since. I have been doing some reading on
> active/active cluster configurations. Just want to
> understand that in that configuration each instance
> serves it's own needs until the time that one fails and
> then the other instance picks up the additional workload
> of the failed instance. Is that correct?
> setup. Each instance has
> including its portion of
> arbitrates ownership of the
> time controls each
> advertised. The TPC-C
> model (coincidence
> a larger box is
> processing power.
> message
> in
> server
> scenarios
> they
> SQL
> servers,
> The
> configuration
> anyone
> utilize
> merging
|||No. SQL Server does not allow shared access to a database. A single SQL
Server can open the database files and access them and that prevents
anything else for getting at the data. This is a data protection mechanism
and exists to prevent possible corruption of the data when two independent
resources attempt to write the same data.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||You may deploy it on SAN
|||I was going to have my two nodes on a SAN solution. Is
there differences from what the other gentleman was
stating in regards to having a SAN solution?

>--Original Message--
>You may deploy it on SAN
>.
>
|||You can store SQL data on a SAN. It is generally the best way to build a
cluster.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:acbc01c4369c$fd6a3290$a601280a@.phx.gbl...[vbcol=seagreen]
> I was going to have my two nodes on a SAN solution. Is
> there differences from what the other gentleman was
> stating in regards to having a SAN solution?

active/active clustering

in an active/active clustering,
do the two nodes have the instance of sqlserver with the same name ?
can we say that we were serving on two hosts load balancing ?
thanks
Comments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"ilkozer" <ilkozer@.hotmail.com> wrote in message
news:%23g4fRIzmEHA.2680@.TK2MSFTNGP15.phx.gbl...
> in an active/active clustering,
> do the two nodes have the instance of sqlserver with the same name ?
Not at the same time.
> can we say that we were serving on two hosts load balancing ?
No.
> thanks
>

Active Directory Queries

Hi,
I'm interested in querying my domain's Active Directory database from SQL
Server 2000. In particular, I would like to be able to take a network user
name and determine its membership in network groups so I can list all the
network logins that user has to a SQL server instance. I would like to do
this from T-SQL, if possible.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgDaniel,
there's not a huge amount of details about ADSI queries, but these links
should help you set it up:
http://msdn.microsoft.com/library/d...
uted_query.asp
http://msdn.microsoft.com/library/d...r />
_94fn.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Paul,
Cool, that looks like a good start. Now I just need to learn those LDAP
queries.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uIGwD$6xGHA.2396@.TK2MSFTNGP03.phx.gbl...
> Daniel,
> there's not a huge amount of details about ADSI queries, but these links
> should help you set it up:
> http://msdn.microsoft.com/library/d...buted_query.asp
> http://msdn.microsoft.com/library/d.../>
12_94fn.asp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>

ACH FILE ORIGINATION RIGHT FROM SQLServer

Hi,

we are trying to develop ach origination (bank direct debits and deposits) right from sqlserver without using ach software...our mgmt team rightly points out "...why use a separate software that you'll have to transfer your data into when sqlserver has all the capabilities to build the files and create tramsmittable, formatted messages.

Does anyone know of any examples...like best practices for setting up the headers (file header, batch header, etc) for multiple re-use...or any sample execs for creating the files on the fly? -- we're experimenting with our own functions for this and will share what we create, but any examples from anyone who has been working on something similar would be great to look at.

Thx much,

Haven't done ACH, but I've created stored procedures to generate some batch files for mainframe consumption before (2 columns per row), one is record id, one is data. Built up a temp table like that, then selected out of the temp table ordered by record id. I wouldn't say it's the greatest solution in the world, but it worked.

SQL Server really is the back end database. It's for storing a retrieving data. The application is (SHOULD) responsible for presenting (formatting) that data into whatever you want. You can use T-SQL (Or I guess the built-in .NET stuff now) to pre-format the data, but I would argue that mixes the data and presentation layers which is against good design practices. Others may argue that the tables, etc are the data layer, and the stored procedures are acting as the presentation layer.

Accounts for SQLServer service vs SQLServerAgent service

I've just changed the account under which SQL Server starts on my
development server. The new account has admin rights, and a corresponding
Exchange email account - so I can now receive alerts at various points.
I've restarted the server, and the SQL Server service seems to be running
OK.
However, I cant get the SQLServerAgent service running. It comes up with the
following error:
Service Control Failure:
An error 1069 - (The service did not start due to a logon failure) occured
while performing this service operation on th SQLServerAgent service.
To be honest, I'm not sure if the Agent service was running previously, so
the change in account details might be a red herring.
To get it working, I changed the account from a Windows account, to the
System Account - under this setup, the Agent started fine, but then I found
the SQL Mail test failed.
Now the SQLServer service uses the Windows Account, but the SQLServerAgent
service uses the System Account, and... touch wood... they both appear to be
working...
I'm just curious if my setup is correct, ie. is this a common scenario or
should both services be under the same (Windows?) account? It seems a little
odd to use differing accounts.
Thanks
ChrisIdeally, from a security perspective, you should use 2 different domain
accounts. Then you can audit what each one is doing.
The SQLServerAgent account, did it have log on locally rights, and if the
server is a domain controller, it needs more than just a Domain User rights.
Regards
--
Mike Epprecht
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
Specialist SQL Server Solutions and Consulting
"CJM" <cjmwork@.yahoo.co.uk> wrote in message
news:%23BvtlNJcDHA.2632@.TK2MSFTNGP12.phx.gbl...
> I've just changed the account under which SQL Server starts on my
> development server. The new account has admin rights, and a corresponding
> Exchange email account - so I can now receive alerts at various points.
> I've restarted the server, and the SQL Server service seems to be running
> OK.
> However, I cant get the SQLServerAgent service running. It comes up with
the
> following error:
> Service Control Failure:
> An error 1069 - (The service did not start due to a logon failure) occured
> while performing this service operation on th SQLServerAgent service.
> To be honest, I'm not sure if the Agent service was running previously, so
> the change in account details might be a red herring.
> To get it working, I changed the account from a Windows account, to the
> System Account - under this setup, the Agent started fine, but then I
found
> the SQL Mail test failed.
> Now the SQLServer service uses the Windows Account, but the SQLServerAgent
> service uses the System Account, and... touch wood... they both appear to
be
> working...
> I'm just curious if my setup is correct, ie. is this a common scenario or
> should both services be under the same (Windows?) account? It seems a
little
> odd to use differing accounts.
> Thanks
> Chris
>
>|||"Mike Epprecht" <mike@.epprecht.net> wrote in message
news:uTBJHTJcDHA.1128@.tk2msftngp13.phx.gbl...
> Ideally, from a security perspective, you should use 2 different domain
> accounts. Then you can audit what each one is doing.
How do you mean?
> The SQLServerAgent account, did it have log on locally rights, and if the
> server is a domain controller, it needs more than just a Domain User
rights.
SQL Server has its own domain account, with admin rights.
The SQL Server Agent uses the 'System Account'
CJM|||Hi Chris,
Form your description, I understand that SQL Server has its own domain
account, with admin rights and the SQL Server Agent uses the 'System
Account' on your computer. Currently, the SQL Mail test failed in the SQL
Server Agent Properties. If I have misunderstood, please feel free to let
me know.
Based on my knowledge, when the SQLServer service uses the Windows Account
and the SQLServerAgent service uses the System Account, the two services
could start as normal. But if you want to use SQL Mail successfully with
SQL Server Agent, you need to have a domain account for SQL Server Agent
service. SQL Mail could not work fine using System Account with SQL Server
Agent.
Also, I found some related information below:
Troubleshooting MSSQLServer or SQLServerAgent Services User Accounts
http://doc.ddart.net/mssql/sql2000/html/trblsql/tr_servdatabse_3c37.htm
Hope it helps
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

access-SQL ODBC link

> Hello,
> I am using trusted windows user connection to SQL
server.
> I created an ODBC link from Access to SQL server. I am
> able to modify data in tables through SQL Enterprise
> Manager however I am not able to modify them in Access.
> What is missing?You need to create a primary key or unique index on any SQLS tables
you want to modify from Access.
--mary
On Sat, 17 Apr 2004 13:34:26 -0700, "JIM.H."
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>server.|||Why do I have to do that? And if my primary key is based
on many columns will that cuse any problem?
>--Original Message--
>You need to create a primary key or unique index on any
SQLS tables
>you want to modify from Access.
>--mary
>On Sat, 17 Apr 2004 13:34:26 -0700, "JIM.H."
><anonymous@.discussions.microsoft.com> wrote:
>
Access.[vbcol=seagreen]
>.
>|||Why? because it won't work if you don't. Access can't locate the row
on the server, update it, and guarantee data integrity if the table
doesn't have a PK. If your PK is based on too many columns, then you
should consider a surrogate PK. A good starting point would be
"Database Design for Mere Mortals: A Hands-On Guide to Relational
Database Design" by Mike Hernandez.
--Mary
On Sun, 18 Apr 2004 17:58:46 -0700, "JIM.H."
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Why do I have to do that? And if my primary key is based
>on many columns will that cuse any problem?
>SQLS tables
>Access.

access-SQL ODBC link

> Hello,
> I am using trusted windows user connection to SQL
server.
> I created an ODBC link from Access to SQL server. I am
> able to modify data in tables through SQL Enterprise
> Manager however I am not able to modify them in Access.
> What is missing?
You need to create a primary key or unique index on any SQLS tables
you want to modify from Access.
--mary
On Sat, 17 Apr 2004 13:34:26 -0700, "JIM.H."
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>server.
|||Why do I have to do that? And if my primary key is based
on many columns will that cuse any problem?
>--Original Message--
>You need to create a primary key or unique index on any
SQLS tables[vbcol=seagreen]
>you want to modify from Access.
>--mary
>On Sat, 17 Apr 2004 13:34:26 -0700, "JIM.H."
><anonymous@.discussions.microsoft.com> wrote:
Access.
>.
>
|||Why? because it won't work if you don't. Access can't locate the row
on the server, update it, and guarantee data integrity if the table
doesn't have a PK. If your PK is based on too many columns, then you
should consider a surrogate PK. A good starting point would be
"Database Design for Mere Mortals: A Hands-On Guide to Relational
Database Design" by Mike Hernandez.
--Mary
On Sun, 18 Apr 2004 17:58:46 -0700, "JIM.H."
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Why do I have to do that? And if my primary key is based
>on many columns will that cuse any problem?
>SQLS tables
>Access.

accessing the database remotely from non-microsoft platform

I have a query of the kind brains of this group.

Is it possible to access a microsoft sqlserver database (for select, insert,
update, delete, and for miscellaneous administrative actions such as table
and index creation and deletion) remotely from a compiled C program (and/or,
from Perl) running on a NON microsoft platform, such as Sun's Solaris and/or
Linux?

What toolset(s) could be used (on the UNIX/Linux end) for such a purpose?
Would it require a corresponding special toolset to be installed on the
Windows server end as well? (I'd prefer not to have to do that if
possible.)

If you are not now rolling on the floor laughing at the "heresy" of such a
notion, thanks in advance.

Dan
drlevy@.glowing.com (replace 'glowing' with 'lucent')Some suggestions here:

http://www.sommarskog.se/mssqlperl/unix.html

Simon

Accessing mirrored databases in SqlServer 2005

Using the new sqlserver 2005 mirroring, can client connections be made to the non-principal database for, say, reporting purposes? Does this affect the failover?

Thanks

For reporting only run with safety off and no witness. Either one is enough to disable auto failover, but quorum (2 of 3) is required to run with safety on so with no witness both systems must be up to update the principal server. Safety off allows running with the mirror down and no witness. To use the mirror as a reporting server you can create database snapshots on an active mirror. This allows point in time read only access to the database.

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