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 Database Connections SQL Express, how many

How can I determine if everyone has logged off of the SQL Express database, ie, no one else is connected.

I need to clean up some files that are generated in my code, but I only want to do that when everyone has logged off the database?

In MS Access, I could try to re-open the MDB file exclusively.

Is there a connection number etc?

Hi there,

There isn't a connection number per se (well there is the @.@.CONNECTIONS function but that returns all server connections since the server was last started/restarted so it's not really useful for your situation).

You can use the stored procedure sp_who or even sp_who2 by opening up a new query window and typing in "exec sp_who" or "exec sp_who2".

These stored procedures will give you a result set showing all active processes in the database, what database they are currently working in and also what machine (host) they are connected from. Note that a connection may take up multiple processes so you are looking for distinct process IDs in the output from these stored procedures.

I guess you could use the output from the stored procs to see that you're the only person connected to the database (use the combination of the host name, process ID and database name to determine this).

You could be more complicated and actually query system tables for the information you want, but using the stored procedure should be sufficient.

Also, you can set your database to single user to prevent anyone else connecting when you do your work....Actually, from my understanding, setting a database to single user mode actually boot anybody who is currently connected but not you if anyone actually is connected when you set the database to single user mode.

To set single user mode, you can:

1) Right click on your database in Management Studio Express and select "Properties" from the context menu that appears

2) In the "Database Properties" dialog, there will be an option (on the left hand side) for "Options". Click on this

3) There will be a set of properties displayed in the bottom half of the dialog. Scroll through these and there should be a property called "Restrict Access". Change this property (should currently be MULTI_USER) to SINGLE_USER and apply.

4) After you've finished, set the "Restrict Access" property back to MULTI_USER so that other people can connect. Note that sometimes Management Studio Express can have a hissy fit when you try to do this....In that case, close Management Studio Express, re-open it and re-connect to the server and then try again.

NB (1). It might take a little bit of time for all people using your database to be booted once you set the access to SINGLE_USER so wait a bit before doing any work.

NB (2). If you are connecting as an account with SysAdmin privileges (e.g. "sa") and everyone else who connects does not use an account with either SysAdmin, dbowner or dbcreator privileges then you can also set the access to RESTRICTED_USER instead of SINGLE_USER

NB (3). Restricting database access can also be done through the ALTER DATABASE statement if you want to write some SQL for it. I find the GUI is easier, though, but if you want to use SQL then look in SQL Server Books Online for info on the ALTER DATABASE statement.

Hope that helps a bit, but sorry if it doesn't
|||

If you're using a tool such as Management Studio (Full or Express) you can open the Activity Monitor from the Management node. This will tell you all the processes that are open and what they are doing. You can also find information about existing Locks.

If these suggestions don't meet your needs, you could also ask this question on the SQL Database Engine forum or the T-SQL forum. Those forums are great places to ask general questions about how to do general things in SQL Server.

Regards,

Mike

Mark the best posts as Answers!

Accumulated value in table detailrow

I have a dataset with a GROUP BY connected to my table.
In the detailrow I want to add a accumulated value calculated from another
field.
Like this:
MonthX ValueY Acc. value
--
Month 1 10 10
Month 2 20 30
Month 3 30 60
Month 4 40 100
Can I do it by referencing the textboxes in the table, or can i change my
transact-SQL in any way?
(SELECT MonthX,SUM(SomeValue)/SUM(AnotherValue) AS ValueY FROM MyTable GROUP
BY MonthX ORDER BY MonthX)
Please help!Take a look at the reporting services RunningValue function.
"Olof" <Olof@.discussions.microsoft.com> wrote in message
news:4FF60820-A413-4CDB-BAB3-991DBDB8759A@.microsoft.com...
>I have a dataset with a GROUP BY connected to my table.
> In the detailrow I want to add a accumulated value calculated from another
> field.
> Like this:
> MonthX ValueY Acc. value
> --
> Month 1 10 10
> Month 2 20 30
> Month 3 30 60
> Month 4 40 100
> Can I do it by referencing the textboxes in the table, or can i change my
> transact-SQL in any way?
> (SELECT MonthX,SUM(SomeValue)/SUM(AnotherValue) AS ValueY FROM MyTable
> GROUP
> BY MonthX ORDER BY MonthX)
> Please help!|||Thank you very much Stephen, that function was exactly what I needed!!
Greetings
Olof
"Stephen Farmer" wrote:
> Take a look at the reporting services RunningValue function.
>
> "Olof" <Olof@.discussions.microsoft.com> wrote in message
> news:4FF60820-A413-4CDB-BAB3-991DBDB8759A@.microsoft.com...
> >I have a dataset with a GROUP BY connected to my table.
> > In the detailrow I want to add a accumulated value calculated from another
> > field.
> > Like this:
> > MonthX ValueY Acc. value
> > --
> > Month 1 10 10
> > Month 2 20 30
> > Month 3 30 60
> > Month 4 40 100
> >
> > Can I do it by referencing the textboxes in the table, or can i change my
> > transact-SQL in any way?
> > (SELECT MonthX,SUM(SomeValue)/SUM(AnotherValue) AS ValueY FROM MyTable
> > GROUP
> > BY MonthX ORDER BY MonthX)
> >
> > Please help!
>
>

Accessing to another server

Hello there
I have two databases, on two diffrent servers, connected to the same
network.
In order to use them both I:
1. create registry to both servers on the same enterprize manater
2. Set them as remote server on two sides.
Now when i'm trying to access to one server when connecting to other server,
it being connected as Guest. And i would like to access to another server as
Admin, or other user.
How can i do that?Roy
Have you read about Linked Servers?
"roy goldhammer" <roy@.hotmail.com> wrote in message
news:eOinwRG%23FHA.740@.TK2MSFTNGP11.phx.gbl...
> Hello there
> I have two databases, on two diffrent servers, connected to the same
> network.
> In order to use them both I:
> 1. create registry to both servers on the same enterprize manater
> 2. Set them as remote server on two sides.
> Now when i'm trying to access to one server when connecting to other
> server, it being connected as Guest. And i would like to access to another
> server as Admin, or other user.
> How can i do that?
>|||Is there diffrenct between linked server and remote server?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OFMRt3J%23FHA.1032@.TK2MSFTNGP11.phx.gbl...
> Roy
> Have you read about Linked Servers?
>
> "roy goldhammer" <roy@.hotmail.com> wrote in message
> news:eOinwRG%23FHA.740@.TK2MSFTNGP11.phx.gbl...
>|||Roy
By creating Linked Server you will be able to query this (remote)
server like SELECT <> FROM Servername.DataBase.dbo.Table WHERE...
By registering a remote server via EM you can olny view a data if you have
an appropriate permissions
One thing I'd like to mention is you can use OPENROWSET command to query
remote server without creating linked server on it.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:eri9hfL%23FHA.160@.TK2MSFTNGP12.phx.gbl...
> Is there diffrenct between linked server and remote server?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OFMRt3J%23FHA.1032@.TK2MSFTNGP11.phx.gbl...
>

Accessing SQL Server installed on dynamic IP based PC

Hi,
I'm trying to make a application that get data from sql server that is
installed on a pc that is connected to internet through dial-up. As we
know that in dialup based connection pc gets dynamic ip. I want to
fetch sql server data from this PC.
how can we setup ?
Indra BisenYou can use a dynamic DNS web service, like http://www.no-ip.com/ .
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Indra Bisen" <indra.bisen@.gmail.com> wrote in message
news:1155477113.077345.26960@.i3g2000cwc.googlegroups.com...
> Hi,
> I'm trying to make a application that get data from sql server that is
> installed on a pc that is connected to internet through dial-up. As we
> know that in dialup based connection pc gets dynamic ip. I want to
> fetch sql server data from this PC.
> how can we setup ?
> Indra Bisen
>

Accessing SQL Server installed on dynamic IP based PC

Hi,
I'm trying to make a application that get data from sql server that is
installed on a pc that is connected to internet through dial-up. As we
know that in dialup based connection pc gets dynamic ip. I want to
fetch sql server data from this PC.
how can we setup ?
Indra BisenYou can use a dynamic DNS web service, like http://www.no-ip.com/ .
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Indra Bisen" <indra.bisen@.gmail.com> wrote in message
news:1155477113.077345.26960@.i3g2000cwc.googlegroups.com...
> Hi,
> I'm trying to make a application that get data from sql server that is
> installed on a pc that is connected to internet through dial-up. As we
> know that in dialup based connection pc gets dynamic ip. I want to
> fetch sql server data from this PC.
> how can we setup ?
> Indra Bisen
>

Accessing SQL Server Databases from Networked PC

Hello all. I have just built another PC (New PC)and have it connected to my
Original PC via a LAN. I have my SQL Server 2000 installed on the Original
PC. Both PCs use WinXP Pro OS. I believe I now have to install the Client
Tools on the New PC but after that, how do I access the SQL Server that is
on the Original PC. The SQL Server is setup with mixed (both Windows
Authentication and SQL Server Authentication). Just how do I use the SQL
Query Analyzer that I just installed on the New PC to connect to and
retreive data from the SQL Databases which are on the Original PC? Thanks
in advance for any help, Jim.Hello all. Dissregard this request for assistance as I was able to figure it
out. I can now connect the New PC to the SQL Server in the Original PC,
write my query and the results are displayed on the New PC over the Network.
Have a good day. Jim.

"Jim Richards" <JWRichards@.satx.rr.com> wrote in message
news:RGoOd.38259$uA.22293@.fe1.texas.rr.com...
> Hello all. I have just built another PC (New PC)and have it connected to
> my Original PC via a LAN. I have my SQL Server 2000 installed on the
> Original PC. Both PCs use WinXP Pro OS. I believe I now have to install
> the Client Tools on the New PC but after that, how do I access the SQL
> Server that is on the Original PC. The SQL Server is setup with mixed
> (both Windows Authentication and SQL Server Authentication). Just how do I
> use the SQL Query Analyzer that I just installed on the New PC to connect
> to and retreive data from the SQL Databases which are on the Original PC?
> Thanks in advance for any help, Jim.

Accessing named instances

I am looking to see if anyone has had this problem or ideas of what I might
try. We have been running a Windows 2000 cluster connected to a powervault
for two years as a single instance with no problems.
I am in the process of setting of a Windows 2003 Active-Passive two node
cluster connected to a SAN. I have fourinstances/virtual servers installed
and I can access the "default instance" from any machine with enterprise
manager within our network. This behavior is as expected.
I can only access the named instances from machines within the cluster's
subnet, which is not expected. I have all the instances using port 1433; not
sure if that makes a difference.
Need some help pointing our network team in the right direction. They have
had me turn off the second node, and disable NIC teaming to get down to a
single server, single NIC environment, and I still don't have success.
Any ideas on whether this is a SQL setup problem or a network/firewall
configuration issue?
Respectfully,
Brett
Hi Brett,
Some things to try:
Can the IP addresses of the named instances be resolved (DNS, hosts)?
Maybe you can try connecting to the instance using the IP address.
Or to test, add it in
C:\Windows\System32\drivers\etc\hosts
Chau chau,
Pascalos
"Brett F" wrote:

> I am looking to see if anyone has had this problem or ideas of what I might
> try. We have been running a Windows 2000 cluster connected to a powervault
> for two years as a single instance with no problems.
> I am in the process of setting of a Windows 2003 Active-Passive two node
> cluster connected to a SAN. I have fourinstances/virtual servers installed
> and I can access the "default instance" from any machine with enterprise
> manager within our network. This behavior is as expected.
> I can only access the named instances from machines within the cluster's
> subnet, which is not expected. I have all the instances using port 1433; not
> sure if that makes a difference.
> Need some help pointing our network team in the right direction. They have
> had me turn off the second node, and disable NIC teaming to get down to a
> single server, single NIC environment, and I still don't have success.
> Any ideas on whether this is a SQL setup problem or a network/firewall
> configuration issue?
> Respectfully,
> Brett