Can I do an "alter table add column", with an existing named default?

Hello folks,
Shouldn't I be able to do this? I'm not able to get the syntax to work
Alter table TableName add ColumnName tinyint not null default
DefaultZero
This works:
Alter table TableName add ColumnName tinyint not null default (0)
...but I need to immediately drop the default programatically, and for
that I need the default name, so I'd like to name it myself.
I tried this:
EXEC sp_unbindefault 'TableName .ColumnName'
but got the error "Cannot unbind from 'TableName .ColumnName'. Use
ALTER TABLE DROP CONSTRAINT.
I guess I could query the system tables to figure out the name, but
would prefer not to if I can avoid it.
thanks for any ideas!
SylviaHere's an example with the proper syntax:
ALTER TABLE TableName
ADD ColumnName tinyint NOT NULL
CONSTRAINT DF_TableName_ColumnName DEFAULT 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvia" <Puget4753@.yahoo.com> wrote in message
news:1116288687.747349.22520@.g47g2000cwa.googlegroups.com...
> Hello folks,
> Shouldn't I be able to do this? I'm not able to get the syntax to work
> Alter table TableName add ColumnName tinyint not null default
> DefaultZero
> This works:
> Alter table TableName add ColumnName tinyint not null default (0)
> ...but I need to immediately drop the default programatically, and for
> that I need the default name, so I'd like to name it myself.
> I tried this:
> EXEC sp_unbindefault 'TableName .ColumnName'
> but got the error "Cannot unbind from 'TableName .ColumnName'. Use
> ALTER TABLE DROP CONSTRAINT.
> I guess I could query the system tables to figure out the name, but
> would prefer not to if I can avoid it.
> thanks for any ideas!
> Sylvia
>|||thanks - this works perfectly!

Can I add a named calculation to a measure group

Hi everyone,

Here's my question:

I add a named calculation to one fact table, let's say total cost = units * cost

After doing so, I add this named calculation to a measure group with a sum aggregation method. But while I was processing the cube, an error occurred, which said "OLAP storage engine error..."

Is there anything I had done wrong? or there's better way to achieve the require.

Thanks,

It's possibble to add a named calculation to a measure group...

But If you want to aggregate as sum and generates an error, check the datatypes of the fields Units and Cost... maybe be you need to do a CONVERT... try to explore your facttable and see if there is any error...

Give more details...

regards!!

|||Hello Pedro, thanks for the reply.

I tried it again, and it works fine without an error this time.
|||

I'm happy you get it!

Mark your answer as resolved!

regards!!

Can default and named instance co-exist?

I have 2 Windows 2003 servers running SQL2000. They each have separate
databases. I am in the process of clustering these 2 machines together. I
have purchased a shared external SCSI PowerVault appliance to put the quorom
and the shared databases on. I want to create a Virtual SQL server using
these 2 machines and put their separate databases on the virtual server. My
question is:
Do I have to uninstall SQL server on each machine before I install the
virtual server? If I have to I would have to restore my databases to the new
virtual server
Or can I just install a new named instance of SQL server - choosing the
virtual server in the setup? If I can do this - it would be a lot easier
moving my databases over to the new server.
If I can just create a new virtual server with leaving the original SQL in
tact - will the original still be available to my users if I decide not to
move all the databases over that day?
Is there one way that is better than the other?
1. If you want to use the exact same instance names, uninstall/reinstall for
clustering is needed. If not, you can just virtualize your new instance and
migrate data.
2. Consider reading through this kb for some details
http://support.microsoft.com/kb/224071
3. Your original instance will continue to run while you're installing a new
instance on the same node.
The bottom line, I would create a new instance and ensure it's up and
running before tinkering with the original.
-oj
"Amy Lewis" <AmyLewis@.discussions.microsoft.com> wrote in message
news:223F3934-2F36-4A2D-B5D5-D860055AE5E7@.microsoft.com...
>I have 2 Windows 2003 servers running SQL2000. They each have separate
> databases. I am in the process of clustering these 2 machines together.
> I
> have purchased a shared external SCSI PowerVault appliance to put the
> quorom
> and the shared databases on. I want to create a Virtual SQL server using
> these 2 machines and put their separate databases on the virtual server.
> My
> question is:
> Do I have to uninstall SQL server on each machine before I install the
> virtual server? If I have to I would have to restore my databases to the
> new
> virtual server
> Or can I just install a new named instance of SQL server - choosing the
> virtual server in the setup? If I can do this - it would be a lot easier
> moving my databases over to the new server.
> If I can just create a new virtual server with leaving the original SQL in
> tact - will the original still be available to my users if I decide not to
> move all the databases over that day?
> Is there one way that is better than the other?
|||Each instance of SQL Server requires its own shared disk. You can install
either 1 default instance and 15 named instances or up to 16 named
instances, but each must have its own shared drive. All drives must have a
drive letter, no mount points allowed. For existing nonclustered instances,
if you move the data files to a shared drive then you will have the option
during setup to upgrade the standalone instance to a virtual/clustered
instance (assuming you're using the Enterprise Edition setup).
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

can database name be assigned as a variable?

I need to have a query like below to run to select all the fields from different databases that each database have a table named 'table1'

select * from test...table1

select * from test1..table1

I have the following code sample:

declare @.dbname varchar(50)
set @.dbname='test'
select @.dbname
select * from @.dbname..table1

However I received the error messge when I ran this code. Can anyone help to resolve this?

Thank you!

Hi

One way of doing this is to build the query dynamically and use the Exec, as shown in the example below:

Declare @.xQuery varchar(1000)
Select @.xQuery = 'Select * from test..table1'
Exec(@.xQuery)

Select @.xQuery = 'Select * from test1..table1'
Exec(@.xQuery)

---

Best regards
Georg
www.l4ndash.com - Log4Net Dashboard / Log4Net Viewer

Can connect with IP number but not with machine name

Running MSDE2000A named instance on an XP Pro machine as part of a domain
(W2003 server) , connecting from a client: with a connection
'machinename\instancename' i get no connection ('server not found or accss
denied'); however with the same setup and a connection
'172.0.30.14\instancename' everything works as expected. I can use an alias
for the IP number and that works but an alias for the machine name does not.
With a DHCP server, the IP address isn't permanent so how do I configure the
connection in my application?
More info - Colleagues have suggested that this is a simple DNS problem. But
I can connect to the machine using the DNS names in other apps (e.g. 'my
network Places') - it's just SQL that won't connect.
"quilkin" wrote:

> Running MSDE2000A named instance on an XP Pro machine as part of a domain
> (W2003 server) , connecting from a client: with a connection
> 'machinename\instancename' i get no connection ('server not found or accss
> denied'); however with the same setup and a connection
> '172.0.30.14\instancename' everything works as expected. I can use an alias
> for the IP number and that works but an alias for the machine name does not.
> With a DHCP server, the IP address isn't permanent so how do I configure the
> connection in my application?

Can connect with IP number but not with machine name

Running MSDE2000A named instance on an XP Pro machine as part of a domain
(W2003 server) , connecting from a client: with a connection
'machinename\instancename' i get no connection ('server not found or accss
denied'); however with the same setup and a connection
'172.0.30.14\instancename' everything works as expected. I can use an alias
for the IP number and that works but an alias for the machine name does not.
With a DHCP server, the IP address isn't permanent so how do I configure the
connection in my application?More info - Colleagues have suggested that this is a simple DNS problem. But
I can connect to the machine using the DNS names in other apps (e.g. 'my
network Places') - it's just SQL that won't connect.
"quilkin" wrote:

> Running MSDE2000A named instance on an XP Pro machine as part of a domain
> (W2003 server) , connecting from a client: with a connection
> 'machinename\instancename' i get no connection ('server not found or accss
> denied'); however with the same setup and a connection
> '172.0.30.14\instancename' everything works as expected. I can use an alia
s
> for the IP number and that works but an alias for the machine name does no
t.
> With a DHCP server, the IP address isn't permanent so how do I configure t
he
> connection in my application?

Can connect with IP number but not with machine name

Running MSDE2000A named instance on an XP Pro machine as part of a domain
(W2003 server) , connecting from a client: with a connection
'machinename\instancename' i get no connection ('server not found or accss
denied'); however with the same setup and a connection
'172.0.30.14\instancename' everything works as expected. I can use an alias
for the IP number and that works but an alias for the machine name does not.
With a DHCP server, the IP address isn't permanent so how do I configure the
connection in my application?More info - Colleagues have suggested that this is a simple DNS problem. But
I can connect to the machine using the DNS names in other apps (e.g. 'my
network Places') - it's just SQL that won't connect.
"quilkin" wrote:
> Running MSDE2000A named instance on an XP Pro machine as part of a domain
> (W2003 server) , connecting from a client: with a connection
> 'machinename\instancename' i get no connection ('server not found or accss
> denied'); however with the same setup and a connection
> '172.0.30.14\instancename' everything works as expected. I can use an alias
> for the IP number and that works but an alias for the machine name does not.
> With a DHCP server, the IP address isn't permanent so how do I configure the
> connection in my application?

Can a stored procedure be executed from within a select statement?

Can a stored procedure be executed from within a select statement?

Given a store procedure named: sp_proc

I wish to do something like this:

For each row in the table
execute sp_proc 'parameter1', parameter2'...
end for
...but within a select statement. I know you can do this with stored functions, just not sure what the syntax is for a stored procedure.No, not within a select statment. Well, maybe with OPENQUERY, but even if that did work I would never use it.|||So convert it into a function...

Is there a question here?

Can a cursor variable be assigned to a dynamically named cursor?

(SQL Server 2000, SP3a)
(From a different thread.)
Hello, all!
I have an open global cursor that is created dynamically by stored procedure
A. I'd like
to reference this cursor from stored procedure B. I know the dynamic name o
f the cursor,
but I know of no way to get a "handle" of this cursor so that I can use it f
rom stored
procedure B in a cursor variable.
The [sp_describe_cursor] returns something called a cursor_handle. Can this
be used
somehow to set a cursor variable?
I thought maybe I could do something like this:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor varying output', @.Cursor =
@.Cursor
output
execute('deallocate ' + @.CursorName)
But I get this error:
Server: Msg 181, Level 15, State 1, Line 1
Cannot use the OUTPUT option in a DECLARE statement.
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@.Cursor'.
Which I don't fully understand. But, after some fiddling, it's clearly some
thing with the
[sp_executesql] line. No amount of massaging will get this to work -- my gu
ess is that
the structure of [sp_executesql] won't permit a cursor variable to be handle
d. :-(
Thanks for any help anyone can provide!
John PetersonCursors are usually best avoided because of their performance/resource
implications. Erland has an article on alternative methods for sharing data
between SPs:
http://www.sommarskog.se/share_data.html
For completeness, here's an amended version of your code:
...
SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
...
Now reference the cursor by variable (@.cursor).
David Portas
SQL Server MVP
--|||David,
You're right -- I appreciate that cursors aren't wholly performant, but in m
y case, I'm
writing a management procedure that lends itself well to using cursors.
Thanks for the link on other techniques for sharing data. :-)
I think you solved my issue! From what I can tell, you merely removed the V
ARYING
keyword. From the stored procedure documentation (which I kind of assumed t
hat
[sp_executesql] was leveraging) it seemed as if the VARYING keyword was nece
ssary when
using a cursor variable. But, it appears not to be the case, and that was t
he one
combination I *didn't* try!
Thanks so much! :-)
John Peterson
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> Cursors are usually best avoided because of their performance/resource
> implications. Erland has an article on alternative methods for sharing dat
a
> between SPs:
> http://www.sommarskog.se/share_data.html
> For completeness, here's an amended version of your code:
> ...
> SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> ...
> Now reference the cursor by variable (@.cursor).
> --
> David Portas
> SQL Server MVP
> --
>|||Oddly, it seems like there are some things that can't be done with the curso
r variable.
For example, I tried:
open @.Cursor
But that doesn't appear to work. Only when the OPEN is in the context of th
e dynamic SQL
does it seem to open the cursor for the variable.
Additionally:
close @.Cursor
deallocate @.Cursor
Don't appear to work either. If I try and re-run my code snippet, it compla
ins that the
cursor still exists.
Unless the issue is that there are *two* "handles" to the same cursor (the o
riginal "By
Name" and the variable) -- and I need to essentially close both handles befo
re the cursor
will be destroyed?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
> David,
> You're right -- I appreciate that cursors aren't wholly performant, but in
my case, I'm
> writing a management procedure that lends itself well to using cursors.
> Thanks for the link on other techniques for sharing data. :-)
> I think you solved my issue! From what I can tell, you merely removed the
VARYING
> keyword. From the stored procedure documentation (which I kind of assumed
that
> [sp_executesql] was leveraging) it seemed as if the VARYING keyword was ne
cessary when
> using a cursor variable. But, it appears not to be the case, and that was
the one
> combination I *didn't* try!
> Thanks so much! :-)
> John Peterson
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
>|||I finally settled on this test bed, which appears to work successfully:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor global forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName + ' open @.Cursor'
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor output', @.Cursor = @.Cursor
output
declare @.Name sysname
fetch next from @.Cursor into @.Name
print @.Name
close @.Cursor
deallocate @.Cursor
execute('deallocate ' + @.CursorName)
Thanks again for your help, David! I was dispairing that a solution could b
e found. :-)
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eKHgxlI%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Oddly, it seems like there are some things that can't be done with the cur
sor variable.
> For example, I tried:
> open @.Cursor
> But that doesn't appear to work. Only when the OPEN is in the context of the dyna
mic
SQL
> does it seem to open the cursor for the variable.
> Additionally:
> close @.Cursor
> deallocate @.Cursor
> Don't appear to work either. If I try and re-run my code snippet, it comp
lains that the
> cursor still exists.
> Unless the issue is that there are *two* "handles" to the same cursor (the
original "By
> Name" and the variable) -- and I need to essentially close both handles before the

cursor
> will be destroyed?
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
I'm
>

Can a cursor variable be assigned to a dynamically named cursor?

(SQL Server 2000, SP3a)
(From a different thread.)
Hello, all!
I have an open global cursor that is created dynamically by stored procedure A. I'd like
to reference this cursor from stored procedure B. I know the dynamic name of the cursor,
but I know of no way to get a "handle" of this cursor so that I can use it from stored
procedure B in a cursor variable.
The [sp_describe_cursor] returns something called a cursor_handle. Can this be used
somehow to set a cursor variable?
I thought maybe I could do something like this:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor varying output', @.Cursor = @.Cursor
output
execute('deallocate ' + @.CursorName)
But I get this error:
Server: Msg 181, Level 15, State 1, Line 1
Cannot use the OUTPUT option in a DECLARE statement.
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@.Cursor'.
Which I don't fully understand. But, after some fiddling, it's clearly something with the
[sp_executesql] line. No amount of massaging will get this to work -- my guess is that
the structure of [sp_executesql] won't permit a cursor variable to be handled. :-(
Thanks for any help anyone can provide!
John PetersonCursors are usually best avoided because of their performance/resource
implications. Erland has an article on alternative methods for sharing data
between SPs:
http://www.sommarskog.se/share_data.html
For completeness, here's an amended version of your code:
...
SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
...
Now reference the cursor by variable (@.cursor).
--
David Portas
SQL Server MVP
--|||David,
You're right -- I appreciate that cursors aren't wholly performant, but in my case, I'm
writing a management procedure that lends itself well to using cursors.
Thanks for the link on other techniques for sharing data. :-)
I think you solved my issue! From what I can tell, you merely removed the VARYING
keyword. From the stored procedure documentation (which I kind of assumed that
[sp_executesql] was leveraging) it seemed as if the VARYING keyword was necessary when
using a cursor variable. But, it appears not to be the case, and that was the one
combination I *didn't* try!
Thanks so much! :-)
John Peterson
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> Cursors are usually best avoided because of their performance/resource
> implications. Erland has an article on alternative methods for sharing data
> between SPs:
> http://www.sommarskog.se/share_data.html
> For completeness, here's an amended version of your code:
> ...
> SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> ...
> Now reference the cursor by variable (@.cursor).
> --
> David Portas
> SQL Server MVP
> --
>|||Oddly, it seems like there are some things that can't be done with the cursor variable.
For example, I tried:
open @.Cursor
But that doesn't appear to work. Only when the OPEN is in the context of the dynamic SQL
does it seem to open the cursor for the variable.
Additionally:
close @.Cursor
deallocate @.Cursor
Don't appear to work either. If I try and re-run my code snippet, it complains that the
cursor still exists.
Unless the issue is that there are *two* "handles" to the same cursor (the original "By
Name" and the variable) -- and I need to essentially close both handles before the cursor
will be destroyed?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
> David,
> You're right -- I appreciate that cursors aren't wholly performant, but in my case, I'm
> writing a management procedure that lends itself well to using cursors.
> Thanks for the link on other techniques for sharing data. :-)
> I think you solved my issue! From what I can tell, you merely removed the VARYING
> keyword. From the stored procedure documentation (which I kind of assumed that
> [sp_executesql] was leveraging) it seemed as if the VARYING keyword was necessary when
> using a cursor variable. But, it appears not to be the case, and that was the one
> combination I *didn't* try!
> Thanks so much! :-)
> John Peterson
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> > Cursors are usually best avoided because of their performance/resource
> > implications. Erland has an article on alternative methods for sharing data
> > between SPs:
> >
> > http://www.sommarskog.se/share_data.html
> >
> > For completeness, here's an amended version of your code:
> > ...
> > SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> > EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> > ...
> >
> > Now reference the cursor by variable (@.cursor).
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
> >
>|||I finally settled on this test bed, which appears to work successfully:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor global forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName + ' open @.Cursor'
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor output', @.Cursor = @.Cursor output
declare @.Name sysname
fetch next from @.Cursor into @.Name
print @.Name
close @.Cursor
deallocate @.Cursor
execute('deallocate ' + @.CursorName)
Thanks again for your help, David! I was dispairing that a solution could be found. :-)
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eKHgxlI%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Oddly, it seems like there are some things that can't be done with the cursor variable.
> For example, I tried:
> open @.Cursor
> But that doesn't appear to work. Only when the OPEN is in the context of the dynamic
SQL
> does it seem to open the cursor for the variable.
> Additionally:
> close @.Cursor
> deallocate @.Cursor
> Don't appear to work either. If I try and re-run my code snippet, it complains that the
> cursor still exists.
> Unless the issue is that there are *two* "handles" to the same cursor (the original "By
> Name" and the variable) -- and I need to essentially close both handles before the
cursor
> will be destroyed?
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
> > David,
> >
> > You're right -- I appreciate that cursors aren't wholly performant, but in my case,
I'm
> > writing a management procedure that lends itself well to using cursors.
> >
> > Thanks for the link on other techniques for sharing data. :-)
> >
> > I think you solved my issue! From what I can tell, you merely removed the VARYING
> > keyword. From the stored procedure documentation (which I kind of assumed that
> > [sp_executesql] was leveraging) it seemed as if the VARYING keyword was necessary when
> > using a cursor variable. But, it appears not to be the case, and that was the one
> > combination I *didn't* try!
> >
> > Thanks so much! :-)
> >
> > John Peterson
> >
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> > news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> > > Cursors are usually best avoided because of their performance/resource
> > > implications. Erland has an article on alternative methods for sharing data
> > > between SPs:
> > >
> > > http://www.sommarskog.se/share_data.html
> > >
> > > For completeness, here's an amended version of your code:
> > > ...
> > > SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> > > EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> > > ...
> > >
> > > Now reference the cursor by variable (@.cursor).
> > >
> > > --
> > > David Portas
> > > SQL Server MVP
> > > --
> > >
> > >
> >
> >
>

can a column be named as a variable?

i have declared a variable...lets say @.month
can i have a column named as value of this variable..ie any value this variable returns when executed?
let me know asap
regards
Nikhil
refer to this url:
http://www.algonet.se/~sommar/dynamic_sql.html
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

Can 2K5 Cluster can have 2 instances on each node?

Hi,
We have sql 2005 standard installed as cluster on win2k3 cluster with 8
GB memory on each node. We have one virtual sql server named instance on
first node. Can we create one more instance on the same node and
similarly 2 more instances on the second node. The cluster will be in
active\active mode. Our reason for considering this is that we will be
able to fail over one instance from the node 1 to node2 and node2 still
have the 2nd instance running. Is this possible?
Thanks
DeBe
DeBe's Profile: http://www.dbtalk.net/m115
View this thread: http://www.dbtalk.net/t297735
I think you have some misconceptions on how clustering works. The obsolete
naming of "Active/passive/Active..." is partially responsible for that.
Instances are installed to the cluster. You can have up to sixteen
instances on a cluster. SQL Server 2005 Standard Edition supports up to
two-node clusters. Nodes are the host computers for a cluster. An instance
can run on either node, but only on one node at a time. You cannot install
the same SQL instance to two nodes and have them both serviceing data
requests as if they were the same server. SQL Clustering is a failover
technology, not a scale-out technology. You can create two SQL instances on
a SQL 2005 two-node cluster and let one run normally on each node. During a
failure event one node can run both instances, with some performance
compromises of course.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"DeBe" <DeBe.25toh0@.no-mx.forums.yourdomain.com.au> wrote in message
news:DeBe.25toh0@.no-mx.forums.yourdomain.com.au...
> Hi,
> We have sql 2005 standard installed as cluster on win2k3 cluster with 8
> GB memory on each node. We have one virtual sql server named instance on
> first node. Can we create one more instance on the same node and
> similarly 2 more instances on the second node. The cluster will be in
> active\active mode. Our reason for considering this is that we will be
> able to fail over one instance from the node 1 to node2 and node2 still
> have the 2nd instance running. Is this possible?
> Thanks
>
> --
> DeBe
> DeBe's Profile: http://www.dbtalk.net/m115
> View this thread: http://www.dbtalk.net/t297735
>
|||I will try to make my problem more clear.
I am talking about 4 different instances and they have 4 different
databases
connecting to 4 very different application.
so
Node1-->InsA-->DBa
Node1-->InsB-->DBb
Node2-->InsC-->DBc
Node2-->InsD-->DBd
So I guess my question is is it possible for just InsA to fail over to
Node 2. So that node 2 is now running with 3 instances and node 1 is
running with 1 instance?
My second question would be about the memory. If out of 8 GB Ram on
each node if 1 GB is assigned to os and 3 GB is assigned to each
instance (assuming 2 instances) and if the third instance from the node
2 fails over how will it get the memory for itself?
Thanks
DeBe
DeBe's Profile: http://www.dbtalk.net/m115
View this thread: http://www.dbtalk.net/t297735
|||You can have up to sixteen instances per cluster. Once the instances are
installed, you can set the preferred node order for each instance. That
determines which node is the normal host for this instance. Each instance
is completely independent from the other instances. You can move each
instance independently between nodes without affecting any other instance.
Just as on a stand-alone system with multiple instances, it is the DBA's
responsibility to make sure there are enough resources for all instances to
function properly. Note that SQL will not rebalance memory amongst multiple
instances. I.E. It will not make one instance give up memory so another one
can have it. You must plan ahead for the situation where one node hosts
more than its normal set of instances.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"DeBe" <DeBe.25uu4z@.no-mx.forums.yourdomain.com.au> wrote in message
news:DeBe.25uu4z@.no-mx.forums.yourdomain.com.au...
> I will try to make my problem more clear.
> I am talking about 4 different instances and they have 4 different
> databases
> connecting to 4 very different application.
> so
> Node1-->InsA-->DBa
> Node1-->InsB-->DBb
> Node2-->InsC-->DBc
> Node2-->InsD-->DBd
> So I guess my question is is it possible for just InsA to fail over to
> Node 2. So that node 2 is now running with 3 instances and node 1 is
> running with 1 instance?
> My second question would be about the memory. If out of 8 GB Ram on
> each node if 1 GB is assigned to os and 3 GB is assigned to each
> instance (assuming 2 instances) and if the third instance from the node
> 2 fails over how will it get the memory for itself?
> Thanks
>
> --
> DeBe
> DeBe's Profile: http://www.dbtalk.net/m115
> View this thread: http://www.dbtalk.net/t297735
>
|||Just curious why you need so many instances. One instance can handle 4
DB's.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"DeBe" <DeBe.25uu4z@.no-mx.forums.yourdomain.com.au> wrote in message
news:DeBe.25uu4z@.no-mx.forums.yourdomain.com.au...
I will try to make my problem more clear.
I am talking about 4 different instances and they have 4 different
databases
connecting to 4 very different application.
so
Node1-->InsA-->DBa
Node1-->InsB-->DBb
Node2-->InsC-->DBc
Node2-->InsD-->DBd
So I guess my question is is it possible for just InsA to fail over to
Node 2. So that node 2 is now running with 3 instances and node 1 is
running with 1 instance?
My second question would be about the memory. If out of 8 GB Ram on
each node if 1 GB is assigned to os and 3 GB is assigned to each
instance (assuming 2 instances) and if the third instance from the node
2 fails over how will it get the memory for itself?
Thanks
DeBe
DeBe's Profile: http://www.dbtalk.net/m115
View this thread: http://www.dbtalk.net/t297735