My goal is to have no downtime while rebuilding indexes. Since SQL
2005 doesn't support online indexing for tables with large object types
(ie NTEXT), this is not an option for me. So I think the only other
option is to use P2P replication..
Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
and the hot-standby server without service interruption? This way, if
the live table is locked
during this operation, the hot-standby server would take over the CRUD
statements.
Also, is it allowed to have a single CPU license for two SQL 2005
server if
one is a hot-standby-only server and the standby-only server is only
used for failover purposes. Note that when tables are locked during
ALTER INDEX REBUILD, I assume the failover would kick in. Is that
possible?
Thank you!
MichaelFirst, I'll tackle the larger issues.
A second SQL Server is licensed to be a 'hot standby' server only under the
SQL Server Enterprise Edition license.
Hot Standby (with log shipping) is not a FAILOVER cluster. It will NOT 'kick
in'. The hot standby server must be manually brought on-line after the last
log file is transferred and added.
Even in a Clustered, Fail-Over scenario, FailOver occurs because the server
is down, NOT because a table is inconveniently 'locked'.
I hope this helps you come up with a different strategy.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<mpaine@.htxml.com> wrote in message
news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> My goal is to have no downtime while rebuilding indexes. Since SQL
> 2005 doesn't support online indexing for tables with large object types
> (ie NTEXT), this is not an option for me. So I think the only other
> option is to use P2P replication..
> Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> and the hot-standby server without service interruption? This way, if
> the live table is locked
> during this operation, the hot-standby server would take over the CRUD
> statements.
> Also, is it allowed to have a single CPU license for two SQL 2005
> server if
> one is a hot-standby-only server and the standby-only server is only
> used for failover purposes. Note that when tables are locked during
> ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> possible?
>
> Thank you!
> Michael
>|||I am at loss for another strategy. Is there another common strategy to
workaround timeouts which occur from table locks while reindexing?
I found
http://download.microsoft.com/downl...censingv1.1.doc
which mentions:
"When doing failover support, a server is designated as the passive
server. The purpose of the passive server is to absorb the data and
information held in another server that fails. A passive server does
not need a license, provided that the number of processors in the
passive server is equal or less than those of the active server. The
passive server can take the duties of the active server for 30 days.
Afterward, it must be licensed accordingly."
Are you sure about it can't be a hot-standby server with Standard
edition?
Thank You,
Michael
Arnie Rowland wrote:[vbcol=seagreen]
> First, I'll tackle the larger issues.
> A second SQL Server is licensed to be a 'hot standby' server only under th
e
> SQL Server Enterprise Edition license.
> Hot Standby (with log shipping) is not a FAILOVER cluster. It will NOT 'ki
ck
> in'. The hot standby server must be manually brought on-line after the las
t
> log file is transferred and added.
> Even in a Clustered, Fail-Over scenario, FailOver occurs because the serve
r
> is down, NOT because a table is inconveniently 'locked'.
> I hope this helps you come up with a different strategy.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <mpaine@.htxml.com> wrote in message
> news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...|||I never assert that I know from beans where licensing is concerned. It's
complex and often changing.
I do believe you are correct about the passive server license issue -I
misunderstood how you proposed to configure the servers. However, I think
that the licensing document goes on to indicate that any client queries are
executing on the passive server while at the same time the active server is
still online and 'working' requires a separate license for the passive
server.
From that same document: "Figure 3. Passive Servers. The passive server does
not require a license given that no queries are being executed against it."
When 'FailOver' occurs, the once active server's license is passed over to
the previous passive server. It's not unlike buying a new car and knowing
that your insurance covers you as you drive home and then you have a 'grace'
period to insure the new car.
But as I read your original post, it seems that your purpose is to have
activity on both servers at the same time -indexing on one server, and query
execution on the other server. I'm reasonably sure that is not a allowed
activity for an Active-Passive single license scenario. Bottom line is, in
my opinion, that you can't use one license for two operating servers. BUT,
the definitive answer can be obtained from the VL folks.
And a locked table STILL does not create a 'FailOver' situation.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<mpaine@.htxml.com> wrote in message
news:1154723562.606082.185580@.i42g2000cwa.googlegroups.com...
>I am at loss for another strategy. Is there another common strategy to
> workaround timeouts which occur from table locks while reindexing?
> I found
> http://download.microsoft.com/downl...censingv1.1.doc
> which mentions:
> "When doing failover support, a server is designated as the passive
> server. The purpose of the passive server is to absorb the data and
> information held in another server that fails. A passive server does
> not need a license, provided that the number of processors in the
> passive server is equal or less than those of the active server. The
> passive server can take the duties of the active server for 30 days.
> Afterward, it must be licensed accordingly."
> Are you sure about it can't be a hot-standby server with Standard
> edition?
> Thank You,
> Michael
>
> Arnie Rowland wrote:
>|||Thank you for the licensing clarification.
I guess the root of my question has nothing to do with licensing and
all to do with high-availablility. EE doesn't not support online
indexing for LOB type tables so I am still stuck without a solution.
My only idea is to: (a) switch traffic to database mirrored server (b)
reindex live server, (c) switch traffic back. Is that even possible?
http://www.sql-server-performance.c...ailability2.asp
gives a good overview of the different options and I am more confused
after reading it.
Arnie Rowland wrote:[vbcol=seagreen]
> I never assert that I know from beans where licensing is concerned. It's
> complex and often changing.
> I do believe you are correct about the passive server license issue -I
> misunderstood how you proposed to configure the servers. However, I think
> that the licensing document goes on to indicate that any client queries ar
e
> executing on the passive server while at the same time the active server i
s
> still online and 'working' requires a separate license for the passive
> server.
> From that same document: "Figure 3. Passive Servers. The passive server do
es
> not require a license given that no queries are being executed against it.
"
> When 'FailOver' occurs, the once active server's license is passed over to
> the previous passive server. It's not unlike buying a new car and knowing
> that your insurance covers you as you drive home and then you have a 'grac
e'
> period to insure the new car.
> But as I read your original post, it seems that your purpose is to have
> activity on both servers at the same time -indexing on one server, and que
ry
> execution on the other server. I'm reasonably sure that is not a allowed
> activity for an Active-Passive single license scenario. Bottom line is, in
> my opinion, that you can't use one license for two operating servers. BUT,
> the definitive answer can be obtained from the VL folks.
> And a locked table STILL does not create a 'FailOver' situation.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <mpaine@.htxml.com> wrote in message
> news:1154723562.606082.185580@.i42g2000cwa.googlegroups.com...|||On 4 Aug 2006 12:04:32 -0700, mpaine@.htxml.com wrote:
>My goal is to have no downtime while rebuilding indexes. Since SQL
>2005 doesn't support online indexing for tables with large object types
>(ie NTEXT), this is not an option for me. So I think the only other
>option is to use P2P replication..
How about move the ntext into a 1:1 table with just the NTEXT and PK,
and all other indexes in the other table.
It's not free, but it's cheap.
Josh|||<mpaine@.htxml.com> wrote in message
news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> My goal is to have no downtime while rebuilding indexes. Since SQL
> 2005 doesn't support online indexing for tables with large object types
> (ie NTEXT), this is not an option for me. So I think the only other
> option is to use P2P replication..
> Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> and the hot-standby server without service interruption? This way, if
> the live table is locked
> during this operation, the hot-standby server would take over the CRUD
> statements.
> Also, is it allowed to have a single CPU license for two SQL 2005
> server if
> one is a hot-standby-only server and the standby-only server is only
> used for failover purposes. Note that when tables are locked during
> ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> possible?
>
I think your worries about the loss in availablility due to reindexing are
grossly exaggerated. The restriction for online indexes and large object
types applies only for clustered indexes on tables with large types. So if
you really must have nothing but online indexes, just make the tables with
large object types heaps, with a non-clustered primary key index. EG
drop table itest
go
create table itest
(
id int not null,
data varchar(max),
data1 text,
constraint pk_itest
primary key nonclustered(id)
)
create unique nonclustered index pk_itest
on itest(id)
with (drop_existing=on, online=on)
Alternatively you can use partitioning to minimize the impace of index
rebuilding as a partitioned index can be rebuilt one partition at a time.
As for P2P replication, it would work fine for this, and would allow you to
take a node down to patch the OS, change hardware or whatever. The
"failover" wouldn't happen automatically since P2P has no built-in notion of
failover, and because nothing failed. You would have to manage the traffic
yourself, through the application, DNS or something like an F5 BigIP switch.
For licensing, my understanding is that an unlicensed passive failover node
can only be used with log shipping, mirroring and failover clustering. So
you would need to license the peer node.
David|||This was more than helpful! Thank you
David Browne wrote:
> <mpaine@.htxml.com> wrote in message
> news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> I think your worries about the loss in availablility due to reindexing are
> grossly exaggerated. The restriction for online indexes and large object
> types applies only for clustered indexes on tables with large types. So i
f
> you really must have nothing but online indexes, just make the tables with
> large object types heaps, with a non-clustered primary key index. EG
> drop table itest
> go
> create table itest
> (
> id int not null,
> data varchar(max),
> data1 text,
> constraint pk_itest
> primary key nonclustered(id)
> )
> create unique nonclustered index pk_itest
> on itest(id)
> with (drop_existing=on, online=on)
>
> Alternatively you can use partitioning to minimize the impace of index
> rebuilding as a partitioned index can be rebuilt one partition at a time.
> As for P2P replication, it would work fine for this, and would allow you t
o
> take a node down to patch the OS, change hardware or whatever. The
> "failover" wouldn't happen automatically since P2P has no built-in notion
of
> failover, and because nothing failed. You would have to manage the traffi
c
> yourself, through the application, DNS or something like an F5 BigIP switc
h.
> For licensing, my understanding is that an unlicensed passive failover nod
e
> can only be used with log shipping, mirroring and failover clustering. S
o
> you would need to license the peer node.
> Davidsql
Showing posts with label indexing. Show all posts
Showing posts with label indexing. Show all posts
Monday, March 26, 2012
No downtime
My goal is to have no downtime while rebuilding indexes. Since SQL
2005 doesn't support online indexing for tables with large object types
(ie NTEXT), this is not an option for me. So I think the only other
option is to use P2P replication..
Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
and the hot-standby server without service interruption? This way, if
the live table is locked
during this operation, the hot-standby server would take over the CRUD
statements.
Also, is it allowed to have a single CPU license for two SQL 2005
server if
one is a hot-standby-only server and the standby-only server is only
used for failover purposes. Note that when tables are locked during
ALTER INDEX REBUILD, I assume the failover would kick in. Is that
possible?
Thank you!
MichaelFirst, I'll tackle the larger issues.
A second SQL Server is licensed to be a 'hot standby' server only under the
SQL Server Enterprise Edition license.
Hot Standby (with log shipping) is not a FAILOVER cluster. It will NOT 'kick
in'. The hot standby server must be manually brought on-line after the last
log file is transferred and added.
Even in a Clustered, Fail-Over scenario, FailOver occurs because the server
is down, NOT because a table is inconveniently 'locked'.
I hope this helps you come up with a different strategy.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<mpaine@.htxml.com> wrote in message
news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> My goal is to have no downtime while rebuilding indexes. Since SQL
> 2005 doesn't support online indexing for tables with large object types
> (ie NTEXT), this is not an option for me. So I think the only other
> option is to use P2P replication..
> Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> and the hot-standby server without service interruption? This way, if
> the live table is locked
> during this operation, the hot-standby server would take over the CRUD
> statements.
> Also, is it allowed to have a single CPU license for two SQL 2005
> server if
> one is a hot-standby-only server and the standby-only server is only
> used for failover purposes. Note that when tables are locked during
> ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> possible?
>
> Thank you!
> Michael
>|||I am at loss for another strategy. Is there another common strategy to
workaround timeouts which occur from table locks while reindexing?
I found
http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc
which mentions:
"When doing failover support, a server is designated as the passive
server. The purpose of the passive server is to absorb the data and
information held in another server that fails. A passive server does
not need a license, provided that the number of processors in the
passive server is equal or less than those of the active server. The
passive server can take the duties of the active server for 30 days.
Afterward, it must be licensed accordingly."
Are you sure about it can't be a hot-standby server with Standard
edition?
Thank You,
Michael
Arnie Rowland wrote:
> First, I'll tackle the larger issues.
> A second SQL Server is licensed to be a 'hot standby' server only under the
> SQL Server Enterprise Edition license.
> Hot Standby (with log shipping) is not a FAILOVER cluster. It will NOT 'kick
> in'. The hot standby server must be manually brought on-line after the last
> log file is transferred and added.
> Even in a Clustered, Fail-Over scenario, FailOver occurs because the server
> is down, NOT because a table is inconveniently 'locked'.
> I hope this helps you come up with a different strategy.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <mpaine@.htxml.com> wrote in message
> news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> > My goal is to have no downtime while rebuilding indexes. Since SQL
> > 2005 doesn't support online indexing for tables with large object types
> > (ie NTEXT), this is not an option for me. So I think the only other
> > option is to use P2P replication..
> >
> > Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> > and the hot-standby server without service interruption? This way, if
> > the live table is locked
> > during this operation, the hot-standby server would take over the CRUD
> > statements.
> >
> > Also, is it allowed to have a single CPU license for two SQL 2005
> > server if
> > one is a hot-standby-only server and the standby-only server is only
> > used for failover purposes. Note that when tables are locked during
> > ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> > possible?
> >
> >
> > Thank you!
> > Michael
> >|||I never assert that I know from beans where licensing is concerned. It's
complex and often changing.
I do believe you are correct about the passive server license issue -I
misunderstood how you proposed to configure the servers. However, I think
that the licensing document goes on to indicate that any client queries are
executing on the passive server while at the same time the active server is
still online and 'working' requires a separate license for the passive
server.
From that same document: "Figure 3. Passive Servers. The passive server does
not require a license given that no queries are being executed against it."
When 'FailOver' occurs, the once active server's license is passed over to
the previous passive server. It's not unlike buying a new car and knowing
that your insurance covers you as you drive home and then you have a 'grace'
period to insure the new car.
But as I read your original post, it seems that your purpose is to have
activity on both servers at the same time -indexing on one server, and query
execution on the other server. I'm reasonably sure that is not a allowed
activity for an Active-Passive single license scenario. Bottom line is, in
my opinion, that you can't use one license for two operating servers. BUT,
the definitive answer can be obtained from the VL folks.
And a locked table STILL does not create a 'FailOver' situation.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<mpaine@.htxml.com> wrote in message
news:1154723562.606082.185580@.i42g2000cwa.googlegroups.com...
>I am at loss for another strategy. Is there another common strategy to
> workaround timeouts which occur from table locks while reindexing?
> I found
> http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc
> which mentions:
> "When doing failover support, a server is designated as the passive
> server. The purpose of the passive server is to absorb the data and
> information held in another server that fails. A passive server does
> not need a license, provided that the number of processors in the
> passive server is equal or less than those of the active server. The
> passive server can take the duties of the active server for 30 days.
> Afterward, it must be licensed accordingly."
> Are you sure about it can't be a hot-standby server with Standard
> edition?
> Thank You,
> Michael
>
> Arnie Rowland wrote:
>> First, I'll tackle the larger issues.
>> A second SQL Server is licensed to be a 'hot standby' server only under
>> the
>> SQL Server Enterprise Edition license.
>> Hot Standby (with log shipping) is not a FAILOVER cluster. It will NOT
>> 'kick
>> in'. The hot standby server must be manually brought on-line after the
>> last
>> log file is transferred and added.
>> Even in a Clustered, Fail-Over scenario, FailOver occurs because the
>> server
>> is down, NOT because a table is inconveniently 'locked'.
>> I hope this helps you come up with a different strategy.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> <mpaine@.htxml.com> wrote in message
>> news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
>> > My goal is to have no downtime while rebuilding indexes. Since SQL
>> > 2005 doesn't support online indexing for tables with large object types
>> > (ie NTEXT), this is not an option for me. So I think the only other
>> > option is to use P2P replication..
>> >
>> > Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
>> > and the hot-standby server without service interruption? This way, if
>> > the live table is locked
>> > during this operation, the hot-standby server would take over the CRUD
>> > statements.
>> >
>> > Also, is it allowed to have a single CPU license for two SQL 2005
>> > server if
>> > one is a hot-standby-only server and the standby-only server is only
>> > used for failover purposes. Note that when tables are locked during
>> > ALTER INDEX REBUILD, I assume the failover would kick in. Is that
>> > possible?
>> >
>> >
>> > Thank you!
>> > Michael
>> >
>|||Thank you for the licensing clarification.
I guess the root of my question has nothing to do with licensing and
all to do with high-availablility. EE doesn't not support online
indexing for LOB type tables so I am still stuck without a solution.
My only idea is to: (a) switch traffic to database mirrored server (b)
reindex live server, (c) switch traffic back. Is that even possible?
http://www.sql-server-performance.com/sql_server_high_availability2.asp
gives a good overview of the different options and I am more confused
after reading it.
Arnie Rowland wrote:
> I never assert that I know from beans where licensing is concerned. It's
> complex and often changing.
> I do believe you are correct about the passive server license issue -I
> misunderstood how you proposed to configure the servers. However, I think
> that the licensing document goes on to indicate that any client queries are
> executing on the passive server while at the same time the active server is
> still online and 'working' requires a separate license for the passive
> server.
> From that same document: "Figure 3. Passive Servers. The passive server does
> not require a license given that no queries are being executed against it."
> When 'FailOver' occurs, the once active server's license is passed over to
> the previous passive server. It's not unlike buying a new car and knowing
> that your insurance covers you as you drive home and then you have a 'grace'
> period to insure the new car.
> But as I read your original post, it seems that your purpose is to have
> activity on both servers at the same time -indexing on one server, and query
> execution on the other server. I'm reasonably sure that is not a allowed
> activity for an Active-Passive single license scenario. Bottom line is, in
> my opinion, that you can't use one license for two operating servers. BUT,
> the definitive answer can be obtained from the VL folks.
> And a locked table STILL does not create a 'FailOver' situation.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <mpaine@.htxml.com> wrote in message
> news:1154723562.606082.185580@.i42g2000cwa.googlegroups.com...
> >I am at loss for another strategy. Is there another common strategy to
> > workaround timeouts which occur from table locks while reindexing?
> >
> > I found
> > http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc
> > which mentions:
> >
> > "When doing failover support, a server is designated as the passive
> > server. The purpose of the passive server is to absorb the data and
> > information held in another server that fails. A passive server does
> > not need a license, provided that the number of processors in the
> > passive server is equal or less than those of the active server. The
> > passive server can take the duties of the active server for 30 days.
> > Afterward, it must be licensed accordingly."
> >
> > Are you sure about it can't be a hot-standby server with Standard
> > edition?
> >
> > Thank You,
> > Michael
> >
> >
> >
> > Arnie Rowland wrote:
> >> First, I'll tackle the larger issues.
> >>
> >> A second SQL Server is licensed to be a 'hot standby' server only under
> >> the
> >> SQL Server Enterprise Edition license.
> >>
> >> Hot Standby (with log shipping) is not a FAILOVER cluster. It will NOT
> >> 'kick
> >> in'. The hot standby server must be manually brought on-line after the
> >> last
> >> log file is transferred and added.
> >>
> >> Even in a Clustered, Fail-Over scenario, FailOver occurs because the
> >> server
> >> is down, NOT because a table is inconveniently 'locked'.
> >>
> >> I hope this helps you come up with a different strategy.
> >>
> >> --
> >> Arnie Rowland, Ph.D.
> >> Westwood Consulting, Inc
> >>
> >> Most good judgment comes from experience.
> >> Most experience comes from bad judgment.
> >> - Anonymous
> >>
> >>
> >> <mpaine@.htxml.com> wrote in message
> >> news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> >> > My goal is to have no downtime while rebuilding indexes. Since SQL
> >> > 2005 doesn't support online indexing for tables with large object types
> >> > (ie NTEXT), this is not an option for me. So I think the only other
> >> > option is to use P2P replication..
> >> >
> >> > Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> >> > and the hot-standby server without service interruption? This way, if
> >> > the live table is locked
> >> > during this operation, the hot-standby server would take over the CRUD
> >> > statements.
> >> >
> >> > Also, is it allowed to have a single CPU license for two SQL 2005
> >> > server if
> >> > one is a hot-standby-only server and the standby-only server is only
> >> > used for failover purposes. Note that when tables are locked during
> >> > ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> >> > possible?
> >> >
> >> >
> >> > Thank you!
> >> > Michael
> >> >
> >|||On 4 Aug 2006 12:04:32 -0700, mpaine@.htxml.com wrote:
>My goal is to have no downtime while rebuilding indexes. Since SQL
>2005 doesn't support online indexing for tables with large object types
>(ie NTEXT), this is not an option for me. So I think the only other
>option is to use P2P replication..
How about move the ntext into a 1:1 table with just the NTEXT and PK,
and all other indexes in the other table.
It's not free, but it's cheap.
Josh|||<mpaine@.htxml.com> wrote in message
news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> My goal is to have no downtime while rebuilding indexes. Since SQL
> 2005 doesn't support online indexing for tables with large object types
> (ie NTEXT), this is not an option for me. So I think the only other
> option is to use P2P replication..
> Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> and the hot-standby server without service interruption? This way, if
> the live table is locked
> during this operation, the hot-standby server would take over the CRUD
> statements.
> Also, is it allowed to have a single CPU license for two SQL 2005
> server if
> one is a hot-standby-only server and the standby-only server is only
> used for failover purposes. Note that when tables are locked during
> ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> possible?
>
I think your worries about the loss in availablility due to reindexing are
grossly exaggerated. The restriction for online indexes and large object
types applies only for clustered indexes on tables with large types. So if
you really must have nothing but online indexes, just make the tables with
large object types heaps, with a non-clustered primary key index. EG
drop table itest
go
create table itest
(
id int not null,
data varchar(max),
data1 text,
constraint pk_itest
primary key nonclustered(id)
)
create unique nonclustered index pk_itest
on itest(id)
with (drop_existing=on, online=on)
Alternatively you can use partitioning to minimize the impace of index
rebuilding as a partitioned index can be rebuilt one partition at a time.
As for P2P replication, it would work fine for this, and would allow you to
take a node down to patch the OS, change hardware or whatever. The
"failover" wouldn't happen automatically since P2P has no built-in notion of
failover, and because nothing failed. You would have to manage the traffic
yourself, through the application, DNS or something like an F5 BigIP switch.
For licensing, my understanding is that an unlicensed passive failover node
can only be used with log shipping, mirroring and failover clustering. So
you would need to license the peer node.
David|||This was more than helpful! Thank you
David Browne wrote:
> <mpaine@.htxml.com> wrote in message
> news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> > My goal is to have no downtime while rebuilding indexes. Since SQL
> > 2005 doesn't support online indexing for tables with large object types
> > (ie NTEXT), this is not an option for me. So I think the only other
> > option is to use P2P replication..
> >
> > Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> > and the hot-standby server without service interruption? This way, if
> > the live table is locked
> > during this operation, the hot-standby server would take over the CRUD
> > statements.
> >
> > Also, is it allowed to have a single CPU license for two SQL 2005
> > server if
> > one is a hot-standby-only server and the standby-only server is only
> > used for failover purposes. Note that when tables are locked during
> > ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> > possible?
> >
> I think your worries about the loss in availablility due to reindexing are
> grossly exaggerated. The restriction for online indexes and large object
> types applies only for clustered indexes on tables with large types. So if
> you really must have nothing but online indexes, just make the tables with
> large object types heaps, with a non-clustered primary key index. EG
> drop table itest
> go
> create table itest
> (
> id int not null,
> data varchar(max),
> data1 text,
> constraint pk_itest
> primary key nonclustered(id)
> )
> create unique nonclustered index pk_itest
> on itest(id)
> with (drop_existing=on, online=on)
>
> Alternatively you can use partitioning to minimize the impace of index
> rebuilding as a partitioned index can be rebuilt one partition at a time.
> As for P2P replication, it would work fine for this, and would allow you to
> take a node down to patch the OS, change hardware or whatever. The
> "failover" wouldn't happen automatically since P2P has no built-in notion of
> failover, and because nothing failed. You would have to manage the traffic
> yourself, through the application, DNS or something like an F5 BigIP switch.
> For licensing, my understanding is that an unlicensed passive failover node
> can only be used with log shipping, mirroring and failover clustering. So
> you would need to license the peer node.
> David
2005 doesn't support online indexing for tables with large object types
(ie NTEXT), this is not an option for me. So I think the only other
option is to use P2P replication..
Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
and the hot-standby server without service interruption? This way, if
the live table is locked
during this operation, the hot-standby server would take over the CRUD
statements.
Also, is it allowed to have a single CPU license for two SQL 2005
server if
one is a hot-standby-only server and the standby-only server is only
used for failover purposes. Note that when tables are locked during
ALTER INDEX REBUILD, I assume the failover would kick in. Is that
possible?
Thank you!
MichaelFirst, I'll tackle the larger issues.
A second SQL Server is licensed to be a 'hot standby' server only under the
SQL Server Enterprise Edition license.
Hot Standby (with log shipping) is not a FAILOVER cluster. It will NOT 'kick
in'. The hot standby server must be manually brought on-line after the last
log file is transferred and added.
Even in a Clustered, Fail-Over scenario, FailOver occurs because the server
is down, NOT because a table is inconveniently 'locked'.
I hope this helps you come up with a different strategy.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<mpaine@.htxml.com> wrote in message
news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> My goal is to have no downtime while rebuilding indexes. Since SQL
> 2005 doesn't support online indexing for tables with large object types
> (ie NTEXT), this is not an option for me. So I think the only other
> option is to use P2P replication..
> Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> and the hot-standby server without service interruption? This way, if
> the live table is locked
> during this operation, the hot-standby server would take over the CRUD
> statements.
> Also, is it allowed to have a single CPU license for two SQL 2005
> server if
> one is a hot-standby-only server and the standby-only server is only
> used for failover purposes. Note that when tables are locked during
> ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> possible?
>
> Thank you!
> Michael
>|||I am at loss for another strategy. Is there another common strategy to
workaround timeouts which occur from table locks while reindexing?
I found
http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc
which mentions:
"When doing failover support, a server is designated as the passive
server. The purpose of the passive server is to absorb the data and
information held in another server that fails. A passive server does
not need a license, provided that the number of processors in the
passive server is equal or less than those of the active server. The
passive server can take the duties of the active server for 30 days.
Afterward, it must be licensed accordingly."
Are you sure about it can't be a hot-standby server with Standard
edition?
Thank You,
Michael
Arnie Rowland wrote:
> First, I'll tackle the larger issues.
> A second SQL Server is licensed to be a 'hot standby' server only under the
> SQL Server Enterprise Edition license.
> Hot Standby (with log shipping) is not a FAILOVER cluster. It will NOT 'kick
> in'. The hot standby server must be manually brought on-line after the last
> log file is transferred and added.
> Even in a Clustered, Fail-Over scenario, FailOver occurs because the server
> is down, NOT because a table is inconveniently 'locked'.
> I hope this helps you come up with a different strategy.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <mpaine@.htxml.com> wrote in message
> news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> > My goal is to have no downtime while rebuilding indexes. Since SQL
> > 2005 doesn't support online indexing for tables with large object types
> > (ie NTEXT), this is not an option for me. So I think the only other
> > option is to use P2P replication..
> >
> > Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> > and the hot-standby server without service interruption? This way, if
> > the live table is locked
> > during this operation, the hot-standby server would take over the CRUD
> > statements.
> >
> > Also, is it allowed to have a single CPU license for two SQL 2005
> > server if
> > one is a hot-standby-only server and the standby-only server is only
> > used for failover purposes. Note that when tables are locked during
> > ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> > possible?
> >
> >
> > Thank you!
> > Michael
> >|||I never assert that I know from beans where licensing is concerned. It's
complex and often changing.
I do believe you are correct about the passive server license issue -I
misunderstood how you proposed to configure the servers. However, I think
that the licensing document goes on to indicate that any client queries are
executing on the passive server while at the same time the active server is
still online and 'working' requires a separate license for the passive
server.
From that same document: "Figure 3. Passive Servers. The passive server does
not require a license given that no queries are being executed against it."
When 'FailOver' occurs, the once active server's license is passed over to
the previous passive server. It's not unlike buying a new car and knowing
that your insurance covers you as you drive home and then you have a 'grace'
period to insure the new car.
But as I read your original post, it seems that your purpose is to have
activity on both servers at the same time -indexing on one server, and query
execution on the other server. I'm reasonably sure that is not a allowed
activity for an Active-Passive single license scenario. Bottom line is, in
my opinion, that you can't use one license for two operating servers. BUT,
the definitive answer can be obtained from the VL folks.
And a locked table STILL does not create a 'FailOver' situation.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<mpaine@.htxml.com> wrote in message
news:1154723562.606082.185580@.i42g2000cwa.googlegroups.com...
>I am at loss for another strategy. Is there another common strategy to
> workaround timeouts which occur from table locks while reindexing?
> I found
> http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc
> which mentions:
> "When doing failover support, a server is designated as the passive
> server. The purpose of the passive server is to absorb the data and
> information held in another server that fails. A passive server does
> not need a license, provided that the number of processors in the
> passive server is equal or less than those of the active server. The
> passive server can take the duties of the active server for 30 days.
> Afterward, it must be licensed accordingly."
> Are you sure about it can't be a hot-standby server with Standard
> edition?
> Thank You,
> Michael
>
> Arnie Rowland wrote:
>> First, I'll tackle the larger issues.
>> A second SQL Server is licensed to be a 'hot standby' server only under
>> the
>> SQL Server Enterprise Edition license.
>> Hot Standby (with log shipping) is not a FAILOVER cluster. It will NOT
>> 'kick
>> in'. The hot standby server must be manually brought on-line after the
>> last
>> log file is transferred and added.
>> Even in a Clustered, Fail-Over scenario, FailOver occurs because the
>> server
>> is down, NOT because a table is inconveniently 'locked'.
>> I hope this helps you come up with a different strategy.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> <mpaine@.htxml.com> wrote in message
>> news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
>> > My goal is to have no downtime while rebuilding indexes. Since SQL
>> > 2005 doesn't support online indexing for tables with large object types
>> > (ie NTEXT), this is not an option for me. So I think the only other
>> > option is to use P2P replication..
>> >
>> > Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
>> > and the hot-standby server without service interruption? This way, if
>> > the live table is locked
>> > during this operation, the hot-standby server would take over the CRUD
>> > statements.
>> >
>> > Also, is it allowed to have a single CPU license for two SQL 2005
>> > server if
>> > one is a hot-standby-only server and the standby-only server is only
>> > used for failover purposes. Note that when tables are locked during
>> > ALTER INDEX REBUILD, I assume the failover would kick in. Is that
>> > possible?
>> >
>> >
>> > Thank you!
>> > Michael
>> >
>|||Thank you for the licensing clarification.
I guess the root of my question has nothing to do with licensing and
all to do with high-availablility. EE doesn't not support online
indexing for LOB type tables so I am still stuck without a solution.
My only idea is to: (a) switch traffic to database mirrored server (b)
reindex live server, (c) switch traffic back. Is that even possible?
http://www.sql-server-performance.com/sql_server_high_availability2.asp
gives a good overview of the different options and I am more confused
after reading it.
Arnie Rowland wrote:
> I never assert that I know from beans where licensing is concerned. It's
> complex and often changing.
> I do believe you are correct about the passive server license issue -I
> misunderstood how you proposed to configure the servers. However, I think
> that the licensing document goes on to indicate that any client queries are
> executing on the passive server while at the same time the active server is
> still online and 'working' requires a separate license for the passive
> server.
> From that same document: "Figure 3. Passive Servers. The passive server does
> not require a license given that no queries are being executed against it."
> When 'FailOver' occurs, the once active server's license is passed over to
> the previous passive server. It's not unlike buying a new car and knowing
> that your insurance covers you as you drive home and then you have a 'grace'
> period to insure the new car.
> But as I read your original post, it seems that your purpose is to have
> activity on both servers at the same time -indexing on one server, and query
> execution on the other server. I'm reasonably sure that is not a allowed
> activity for an Active-Passive single license scenario. Bottom line is, in
> my opinion, that you can't use one license for two operating servers. BUT,
> the definitive answer can be obtained from the VL folks.
> And a locked table STILL does not create a 'FailOver' situation.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <mpaine@.htxml.com> wrote in message
> news:1154723562.606082.185580@.i42g2000cwa.googlegroups.com...
> >I am at loss for another strategy. Is there another common strategy to
> > workaround timeouts which occur from table locks while reindexing?
> >
> > I found
> > http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc
> > which mentions:
> >
> > "When doing failover support, a server is designated as the passive
> > server. The purpose of the passive server is to absorb the data and
> > information held in another server that fails. A passive server does
> > not need a license, provided that the number of processors in the
> > passive server is equal or less than those of the active server. The
> > passive server can take the duties of the active server for 30 days.
> > Afterward, it must be licensed accordingly."
> >
> > Are you sure about it can't be a hot-standby server with Standard
> > edition?
> >
> > Thank You,
> > Michael
> >
> >
> >
> > Arnie Rowland wrote:
> >> First, I'll tackle the larger issues.
> >>
> >> A second SQL Server is licensed to be a 'hot standby' server only under
> >> the
> >> SQL Server Enterprise Edition license.
> >>
> >> Hot Standby (with log shipping) is not a FAILOVER cluster. It will NOT
> >> 'kick
> >> in'. The hot standby server must be manually brought on-line after the
> >> last
> >> log file is transferred and added.
> >>
> >> Even in a Clustered, Fail-Over scenario, FailOver occurs because the
> >> server
> >> is down, NOT because a table is inconveniently 'locked'.
> >>
> >> I hope this helps you come up with a different strategy.
> >>
> >> --
> >> Arnie Rowland, Ph.D.
> >> Westwood Consulting, Inc
> >>
> >> Most good judgment comes from experience.
> >> Most experience comes from bad judgment.
> >> - Anonymous
> >>
> >>
> >> <mpaine@.htxml.com> wrote in message
> >> news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> >> > My goal is to have no downtime while rebuilding indexes. Since SQL
> >> > 2005 doesn't support online indexing for tables with large object types
> >> > (ie NTEXT), this is not an option for me. So I think the only other
> >> > option is to use P2P replication..
> >> >
> >> > Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> >> > and the hot-standby server without service interruption? This way, if
> >> > the live table is locked
> >> > during this operation, the hot-standby server would take over the CRUD
> >> > statements.
> >> >
> >> > Also, is it allowed to have a single CPU license for two SQL 2005
> >> > server if
> >> > one is a hot-standby-only server and the standby-only server is only
> >> > used for failover purposes. Note that when tables are locked during
> >> > ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> >> > possible?
> >> >
> >> >
> >> > Thank you!
> >> > Michael
> >> >
> >|||On 4 Aug 2006 12:04:32 -0700, mpaine@.htxml.com wrote:
>My goal is to have no downtime while rebuilding indexes. Since SQL
>2005 doesn't support online indexing for tables with large object types
>(ie NTEXT), this is not an option for me. So I think the only other
>option is to use P2P replication..
How about move the ntext into a 1:1 table with just the NTEXT and PK,
and all other indexes in the other table.
It's not free, but it's cheap.
Josh|||<mpaine@.htxml.com> wrote in message
news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> My goal is to have no downtime while rebuilding indexes. Since SQL
> 2005 doesn't support online indexing for tables with large object types
> (ie NTEXT), this is not an option for me. So I think the only other
> option is to use P2P replication..
> Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> and the hot-standby server without service interruption? This way, if
> the live table is locked
> during this operation, the hot-standby server would take over the CRUD
> statements.
> Also, is it allowed to have a single CPU license for two SQL 2005
> server if
> one is a hot-standby-only server and the standby-only server is only
> used for failover purposes. Note that when tables are locked during
> ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> possible?
>
I think your worries about the loss in availablility due to reindexing are
grossly exaggerated. The restriction for online indexes and large object
types applies only for clustered indexes on tables with large types. So if
you really must have nothing but online indexes, just make the tables with
large object types heaps, with a non-clustered primary key index. EG
drop table itest
go
create table itest
(
id int not null,
data varchar(max),
data1 text,
constraint pk_itest
primary key nonclustered(id)
)
create unique nonclustered index pk_itest
on itest(id)
with (drop_existing=on, online=on)
Alternatively you can use partitioning to minimize the impace of index
rebuilding as a partitioned index can be rebuilt one partition at a time.
As for P2P replication, it would work fine for this, and would allow you to
take a node down to patch the OS, change hardware or whatever. The
"failover" wouldn't happen automatically since P2P has no built-in notion of
failover, and because nothing failed. You would have to manage the traffic
yourself, through the application, DNS or something like an F5 BigIP switch.
For licensing, my understanding is that an unlicensed passive failover node
can only be used with log shipping, mirroring and failover clustering. So
you would need to license the peer node.
David|||This was more than helpful! Thank you
David Browne wrote:
> <mpaine@.htxml.com> wrote in message
> news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> > My goal is to have no downtime while rebuilding indexes. Since SQL
> > 2005 doesn't support online indexing for tables with large object types
> > (ie NTEXT), this is not an option for me. So I think the only other
> > option is to use P2P replication..
> >
> > Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> > and the hot-standby server without service interruption? This way, if
> > the live table is locked
> > during this operation, the hot-standby server would take over the CRUD
> > statements.
> >
> > Also, is it allowed to have a single CPU license for two SQL 2005
> > server if
> > one is a hot-standby-only server and the standby-only server is only
> > used for failover purposes. Note that when tables are locked during
> > ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> > possible?
> >
> I think your worries about the loss in availablility due to reindexing are
> grossly exaggerated. The restriction for online indexes and large object
> types applies only for clustered indexes on tables with large types. So if
> you really must have nothing but online indexes, just make the tables with
> large object types heaps, with a non-clustered primary key index. EG
> drop table itest
> go
> create table itest
> (
> id int not null,
> data varchar(max),
> data1 text,
> constraint pk_itest
> primary key nonclustered(id)
> )
> create unique nonclustered index pk_itest
> on itest(id)
> with (drop_existing=on, online=on)
>
> Alternatively you can use partitioning to minimize the impace of index
> rebuilding as a partitioned index can be rebuilt one partition at a time.
> As for P2P replication, it would work fine for this, and would allow you to
> take a node down to patch the OS, change hardware or whatever. The
> "failover" wouldn't happen automatically since P2P has no built-in notion of
> failover, and because nothing failed. You would have to manage the traffic
> yourself, through the application, DNS or something like an F5 BigIP switch.
> For licensing, my understanding is that an unlicensed passive failover node
> can only be used with log shipping, mirroring and failover clustering. So
> you would need to license the peer node.
> David
Saturday, February 25, 2012
Newbie: Table(s) owner change, users - Indexing
Hi,
I am completely iliterate on SQL Server. Using just common sense and some
basic DB knowledge, along withnet references, I created a database which is
accessed through a VB.net GUI.
My application creates an Access string and uses Windows Authentication for
all users that access the databse.
I noticed that through the intervention of the Sever Admin I was owner of
all the Tables, both mine and the "default" system ones.
But as soon as i changed something or when i copied it the database to a
development server, the owner of the changed/copied tables became me (my
username). This creates problems as any other user that has a login, and
permissions from me (owner) still cannot access the database!.. Only when
the permissions come from the dbo, is the user able to use the application
and querry the database.
Now, when i run :
sp_changeobjectowner tblCodesQA, dbo
on Query Analyzer, i get the following message:
Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line
63
Cannot change owner of object 'tblCodesQA' or one of its child objects
because the new owner 'dbo' already has an object with the same name.
*However* after a refresh, or at least Disconnect-Connect, i see that the
above table has dbo as the new owner !! and of course all designated users
can access the interface.
My question is:
1) why?
2) why sp_changedbowner doesn't work
TIA
-steve
Hi,
If the object owner is not DBO then you need to prefix ownername.tablename
wqhile querying. I am not able to exactly say why this happen and
it got solved after a DB restore. The major chance for this is some one have
changed created the same table in a differerent user, say like this
select * into table from dbo.table
This is the only cjhnace I can think of.
2. Since there is an object with same name in the same database the
sp_changeobjectowner procedure failed.
Thanks
Hari
SQL Server MVP
"steve" <steve@.here.com> wrote in message
news:nk8ge.35541$vN2.885312@.wagner.videotron.net.. .
> Hi,
> I am completely iliterate on SQL Server. Using just common sense and some
> basic DB knowledge, along withnet references, I created a database which
is
> accessed through a VB.net GUI.
> My application creates an Access string and uses Windows Authentication
for
> all users that access the databse.
> I noticed that through the intervention of the Sever Admin I was owner of
> all the Tables, both mine and the "default" system ones.
> But as soon as i changed something or when i copied it the database to a
> development server, the owner of the changed/copied tables became me (my
> username). This creates problems as any other user that has a login, and
> permissions from me (owner) still cannot access the database!.. Only when
> the permissions come from the dbo, is the user able to use the application
> and querry the database.
> Now, when i run :
> sp_changeobjectowner tblCodesQA, dbo
> on Query Analyzer, i get the following message:
> Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line
> 63
> Cannot change owner of object 'tblCodesQA' or one of its child objects
> because the new owner 'dbo' already has an object with the same name.
> *However* after a refresh, or at least Disconnect-Connect, i see that the
> above table has dbo as the new owner !! and of course all designated users
> can access the interface.
> My question is:
> 1) why?
> 2) why sp_changedbowner doesn't work
> TIA
> -steve
>
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:391361
Thanx for your response.
However, this happened as soon as i copied my tables into a new development
server.
Without changing anything
The tables belonged to dbo in the original server. I copied to the
development server and they now belong to my.
I enter the command, get an error but after a refresh it seems that the
command worked...
"Hari Prasad" <hari_prasad_k@.hotmail.com> a crit dans le message de news:
OEPJlscVFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If the object owner is not DBO then you need to prefix ownername.tablename
> wqhile querying. I am not able to exactly say why this happen and
> it got solved after a DB restore. The major chance for this is some one
> have
> changed created the same table in a differerent user, say like this
> select * into table from dbo.table
> This is the only cjhnace I can think of.
> 2. Since there is an object with same name in the same database the
> sp_changeobjectowner procedure failed.
> Thanks
> Hari
> SQL Server MVP
>
> "steve" <steve@.here.com> wrote in message
> news:nk8ge.35541$vN2.885312@.wagner.videotron.net.. .
> is
> for
>
I am completely iliterate on SQL Server. Using just common sense and some
basic DB knowledge, along withnet references, I created a database which is
accessed through a VB.net GUI.
My application creates an Access string and uses Windows Authentication for
all users that access the databse.
I noticed that through the intervention of the Sever Admin I was owner of
all the Tables, both mine and the "default" system ones.
But as soon as i changed something or when i copied it the database to a
development server, the owner of the changed/copied tables became me (my
username). This creates problems as any other user that has a login, and
permissions from me (owner) still cannot access the database!.. Only when
the permissions come from the dbo, is the user able to use the application
and querry the database.
Now, when i run :
sp_changeobjectowner tblCodesQA, dbo
on Query Analyzer, i get the following message:
Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line
63
Cannot change owner of object 'tblCodesQA' or one of its child objects
because the new owner 'dbo' already has an object with the same name.
*However* after a refresh, or at least Disconnect-Connect, i see that the
above table has dbo as the new owner !! and of course all designated users
can access the interface.
My question is:
1) why?
2) why sp_changedbowner doesn't work
TIA
-steve
Hi,
If the object owner is not DBO then you need to prefix ownername.tablename
wqhile querying. I am not able to exactly say why this happen and
it got solved after a DB restore. The major chance for this is some one have
changed created the same table in a differerent user, say like this
select * into table from dbo.table
This is the only cjhnace I can think of.
2. Since there is an object with same name in the same database the
sp_changeobjectowner procedure failed.
Thanks
Hari
SQL Server MVP
"steve" <steve@.here.com> wrote in message
news:nk8ge.35541$vN2.885312@.wagner.videotron.net.. .
> Hi,
> I am completely iliterate on SQL Server. Using just common sense and some
> basic DB knowledge, along withnet references, I created a database which
is
> accessed through a VB.net GUI.
> My application creates an Access string and uses Windows Authentication
for
> all users that access the databse.
> I noticed that through the intervention of the Sever Admin I was owner of
> all the Tables, both mine and the "default" system ones.
> But as soon as i changed something or when i copied it the database to a
> development server, the owner of the changed/copied tables became me (my
> username). This creates problems as any other user that has a login, and
> permissions from me (owner) still cannot access the database!.. Only when
> the permissions come from the dbo, is the user able to use the application
> and querry the database.
> Now, when i run :
> sp_changeobjectowner tblCodesQA, dbo
> on Query Analyzer, i get the following message:
> Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line
> 63
> Cannot change owner of object 'tblCodesQA' or one of its child objects
> because the new owner 'dbo' already has an object with the same name.
> *However* after a refresh, or at least Disconnect-Connect, i see that the
> above table has dbo as the new owner !! and of course all designated users
> can access the interface.
> My question is:
> 1) why?
> 2) why sp_changedbowner doesn't work
> TIA
> -steve
>
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:391361
Thanx for your response.
However, this happened as soon as i copied my tables into a new development
server.
Without changing anything
The tables belonged to dbo in the original server. I copied to the
development server and they now belong to my.
I enter the command, get an error but after a refresh it seems that the
command worked...
"Hari Prasad" <hari_prasad_k@.hotmail.com> a crit dans le message de news:
OEPJlscVFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If the object owner is not DBO then you need to prefix ownername.tablename
> wqhile querying. I am not able to exactly say why this happen and
> it got solved after a DB restore. The major chance for this is some one
> have
> changed created the same table in a differerent user, say like this
> select * into table from dbo.table
> This is the only cjhnace I can think of.
> 2. Since there is an object with same name in the same database the
> sp_changeobjectowner procedure failed.
> Thanks
> Hari
> SQL Server MVP
>
> "steve" <steve@.here.com> wrote in message
> news:nk8ge.35541$vN2.885312@.wagner.videotron.net.. .
> is
> for
>
Newbie: Table(s) owner change, users - Indexing
Hi,
I am completely iliterate on SQL Server. Using just common sense and some
basic DB knowledge, along withnet references, I created a database which is
accessed through a VB.net GUI.
My application creates an Access string and uses Windows Authentication for
all users that access the databse.
I noticed that through the intervention of the Sever Admin I was owner of
all the Tables, both mine and the "default" system ones.
But as soon as i changed something or when i copied it the database to a
development server, the owner of the changed/copied tables became me (my
username). This creates problems as any other user that has a login, and
permissions from me (owner) still cannot access the database!.. Only when
the permissions come from the dbo, is the user able to use the application
and querry the database.
Now, when i run :
sp_changeobjectowner tblCodesQA, dbo
on Query Analyzer, i get the following message:
Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line
63
Cannot change owner of object 'tblCodesQA' or one of its child objects
because the new owner 'dbo' already has an object with the same name.
*However* after a refresh, or at least Disconnect-Connect, i see that the
above table has dbo as the new owner !! and of course all designated users
can access the interface.
My question is:
1) why?
2) why sp_changedbowner doesn't work
TIA
-steveHi,
If the object owner is not DBO then you need to prefix ownername.tablename
wqhile querying. I am not able to exactly say why this happen and
it got solved after a DB restore. The major chance for this is some one have
changed created the same table in a differerent user, say like this
select * into table from dbo.table
This is the only cjhnace I can think of.
2. Since there is an object with same name in the same database the
sp_changeobjectowner procedure failed.
Thanks
Hari
SQL Server MVP
"steve" <steve@.here.com> wrote in message
news:nk8ge.35541$vN2.885312@.wagner.videotron.net...
> Hi,
> I am completely iliterate on SQL Server. Using just common sense and some
> basic DB knowledge, along withnet references, I created a database which
is
> accessed through a VB.net GUI.
> My application creates an Access string and uses Windows Authentication
for
> all users that access the databse.
> I noticed that through the intervention of the Sever Admin I was owner of
> all the Tables, both mine and the "default" system ones.
> But as soon as i changed something or when i copied it the database to a
> development server, the owner of the changed/copied tables became me (my
> username). This creates problems as any other user that has a login, and
> permissions from me (owner) still cannot access the database!.. Only when
> the permissions come from the dbo, is the user able to use the application
> and querry the database.
> Now, when i run :
> sp_changeobjectowner tblCodesQA, dbo
> on Query Analyzer, i get the following message:
> Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line
> 63
> Cannot change owner of object 'tblCodesQA' or one of its child objects
> because the new owner 'dbo' already has an object with the same name.
> *However* after a refresh, or at least Disconnect-Connect, i see that the
> above table has dbo as the new owner !! and of course all designated users
> can access the interface.
> My question is:
> 1) why?
> 2) why sp_changedbowner doesn't work
> TIA
> -steve
>|||Thanx for your response.
However, this happened as soon as i copied my tables into a new development
server.
Without changing anything
The tables belonged to dbo in the original server. I copied to the
development server and they now belong to my.
I enter the command, get an error but after a refresh it seems that the
command worked...
"Hari Prasad" <hari_prasad_k@.hotmail.com> a écrit dans le message de news:
OEPJlscVFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If the object owner is not DBO then you need to prefix ownername.tablename
> wqhile querying. I am not able to exactly say why this happen and
> it got solved after a DB restore. The major chance for this is some one
> have
> changed created the same table in a differerent user, say like this
> select * into table from dbo.table
> This is the only cjhnace I can think of.
> 2. Since there is an object with same name in the same database the
> sp_changeobjectowner procedure failed.
> Thanks
> Hari
> SQL Server MVP
>
> "steve" <steve@.here.com> wrote in message
> news:nk8ge.35541$vN2.885312@.wagner.videotron.net...
>> Hi,
>> I am completely iliterate on SQL Server. Using just common sense and some
>> basic DB knowledge, along withnet references, I created a database which
> is
>> accessed through a VB.net GUI.
>> My application creates an Access string and uses Windows Authentication
> for
>> all users that access the databse.
>> I noticed that through the intervention of the Sever Admin I was owner of
>> all the Tables, both mine and the "default" system ones.
>> But as soon as i changed something or when i copied it the database to a
>> development server, the owner of the changed/copied tables became me (my
>> username). This creates problems as any other user that has a login, and
>> permissions from me (owner) still cannot access the database!.. Only when
>> the permissions come from the dbo, is the user able to use the
>> application
>> and querry the database.
>> Now, when i run :
>> sp_changeobjectowner tblCodesQA, dbo
>> on Query Analyzer, i get the following message:
>> Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner,
>> Line
>> 63
>> Cannot change owner of object 'tblCodesQA' or one of its child objects
>> because the new owner 'dbo' already has an object with the same name.
>> *However* after a refresh, or at least Disconnect-Connect, i see that the
>> above table has dbo as the new owner !! and of course all designated
>> users
>> can access the interface.
>> My question is:
>> 1) why?
>> 2) why sp_changedbowner doesn't work
>> TIA
>> -steve
>>
>
I am completely iliterate on SQL Server. Using just common sense and some
basic DB knowledge, along withnet references, I created a database which is
accessed through a VB.net GUI.
My application creates an Access string and uses Windows Authentication for
all users that access the databse.
I noticed that through the intervention of the Sever Admin I was owner of
all the Tables, both mine and the "default" system ones.
But as soon as i changed something or when i copied it the database to a
development server, the owner of the changed/copied tables became me (my
username). This creates problems as any other user that has a login, and
permissions from me (owner) still cannot access the database!.. Only when
the permissions come from the dbo, is the user able to use the application
and querry the database.
Now, when i run :
sp_changeobjectowner tblCodesQA, dbo
on Query Analyzer, i get the following message:
Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line
63
Cannot change owner of object 'tblCodesQA' or one of its child objects
because the new owner 'dbo' already has an object with the same name.
*However* after a refresh, or at least Disconnect-Connect, i see that the
above table has dbo as the new owner !! and of course all designated users
can access the interface.
My question is:
1) why?
2) why sp_changedbowner doesn't work
TIA
-steveHi,
If the object owner is not DBO then you need to prefix ownername.tablename
wqhile querying. I am not able to exactly say why this happen and
it got solved after a DB restore. The major chance for this is some one have
changed created the same table in a differerent user, say like this
select * into table from dbo.table
This is the only cjhnace I can think of.
2. Since there is an object with same name in the same database the
sp_changeobjectowner procedure failed.
Thanks
Hari
SQL Server MVP
"steve" <steve@.here.com> wrote in message
news:nk8ge.35541$vN2.885312@.wagner.videotron.net...
> Hi,
> I am completely iliterate on SQL Server. Using just common sense and some
> basic DB knowledge, along withnet references, I created a database which
is
> accessed through a VB.net GUI.
> My application creates an Access string and uses Windows Authentication
for
> all users that access the databse.
> I noticed that through the intervention of the Sever Admin I was owner of
> all the Tables, both mine and the "default" system ones.
> But as soon as i changed something or when i copied it the database to a
> development server, the owner of the changed/copied tables became me (my
> username). This creates problems as any other user that has a login, and
> permissions from me (owner) still cannot access the database!.. Only when
> the permissions come from the dbo, is the user able to use the application
> and querry the database.
> Now, when i run :
> sp_changeobjectowner tblCodesQA, dbo
> on Query Analyzer, i get the following message:
> Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line
> 63
> Cannot change owner of object 'tblCodesQA' or one of its child objects
> because the new owner 'dbo' already has an object with the same name.
> *However* after a refresh, or at least Disconnect-Connect, i see that the
> above table has dbo as the new owner !! and of course all designated users
> can access the interface.
> My question is:
> 1) why?
> 2) why sp_changedbowner doesn't work
> TIA
> -steve
>|||Thanx for your response.
However, this happened as soon as i copied my tables into a new development
server.
Without changing anything
The tables belonged to dbo in the original server. I copied to the
development server and they now belong to my.
I enter the command, get an error but after a refresh it seems that the
command worked...
"Hari Prasad" <hari_prasad_k@.hotmail.com> a écrit dans le message de news:
OEPJlscVFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If the object owner is not DBO then you need to prefix ownername.tablename
> wqhile querying. I am not able to exactly say why this happen and
> it got solved after a DB restore. The major chance for this is some one
> have
> changed created the same table in a differerent user, say like this
> select * into table from dbo.table
> This is the only cjhnace I can think of.
> 2. Since there is an object with same name in the same database the
> sp_changeobjectowner procedure failed.
> Thanks
> Hari
> SQL Server MVP
>
> "steve" <steve@.here.com> wrote in message
> news:nk8ge.35541$vN2.885312@.wagner.videotron.net...
>> Hi,
>> I am completely iliterate on SQL Server. Using just common sense and some
>> basic DB knowledge, along withnet references, I created a database which
> is
>> accessed through a VB.net GUI.
>> My application creates an Access string and uses Windows Authentication
> for
>> all users that access the databse.
>> I noticed that through the intervention of the Sever Admin I was owner of
>> all the Tables, both mine and the "default" system ones.
>> But as soon as i changed something or when i copied it the database to a
>> development server, the owner of the changed/copied tables became me (my
>> username). This creates problems as any other user that has a login, and
>> permissions from me (owner) still cannot access the database!.. Only when
>> the permissions come from the dbo, is the user able to use the
>> application
>> and querry the database.
>> Now, when i run :
>> sp_changeobjectowner tblCodesQA, dbo
>> on Query Analyzer, i get the following message:
>> Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner,
>> Line
>> 63
>> Cannot change owner of object 'tblCodesQA' or one of its child objects
>> because the new owner 'dbo' already has an object with the same name.
>> *However* after a refresh, or at least Disconnect-Connect, i see that the
>> above table has dbo as the new owner !! and of course all designated
>> users
>> can access the interface.
>> My question is:
>> 1) why?
>> 2) why sp_changedbowner doesn't work
>> TIA
>> -steve
>>
>
Newbie: Table(s) owner change, users - Indexing
Hi,
I am completely iliterate on SQL Server. Using just common sense and some
basic DB knowledge, along withnet references, I created a database which is
accessed through a VB.net GUI.
My application creates an Access string and uses Windows Authentication for
all users that access the databse.
I noticed that through the intervention of the Sever Admin I was owner of
all the Tables, both mine and the "default" system ones.
But as soon as i changed something or when i copied it the database to a
development server, the owner of the changed/copied tables became me (my
username). This creates problems as any other user that has a login, and
permissions from me (owner) still cannot access the database!.. Only when
the permissions come from the dbo, is the user able to use the application
and querry the database.
Now, when i run :
sp_changeobjectowner tblCodesQA, dbo
on Query Analyzer, i get the following message:
Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line
63
Cannot change owner of object 'tblCodesQA' or one of its child objects
because the new owner 'dbo' already has an object with the same name.
*However* after a refresh, or at least Disconnect-Connect, i see that the
above table has dbo as the new owner !! and of course all designated users
can access the interface.
My question is:
1) why?
2) why sp_changedbowner doesn't work
TIA
-steveHi,
If the object owner is not DBO then you need to prefix ownername.tablename
wqhile querying. I am not able to exactly say why this happen and
it got solved after a DB restore. The major chance for this is some one have
changed created the same table in a differerent user, say like this
select * into table from dbo.table
This is the only cjhnace I can think of.
2. Since there is an object with same name in the same database the
sp_changeobjectowner procedure failed.
Thanks
Hari
SQL Server MVP
"steve" <steve@.here.com> wrote in message
news:nk8ge.35541$vN2.885312@.wagner.videotron.net...
> Hi,
> I am completely iliterate on SQL Server. Using just common sense and some
> basic DB knowledge, along withnet references, I created a database which
is
> accessed through a VB.net GUI.
> My application creates an Access string and uses Windows Authentication
for
> all users that access the databse.
> I noticed that through the intervention of the Sever Admin I was owner of
> all the Tables, both mine and the "default" system ones.
> But as soon as i changed something or when i copied it the database to a
> development server, the owner of the changed/copied tables became me (my
> username). This creates problems as any other user that has a login, and
> permissions from me (owner) still cannot access the database!.. Only when
> the permissions come from the dbo, is the user able to use the application
> and querry the database.
> Now, when i run :
> sp_changeobjectowner tblCodesQA, dbo
> on Query Analyzer, i get the following message:
> Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line
> 63
> Cannot change owner of object 'tblCodesQA' or one of its child objects
> because the new owner 'dbo' already has an object with the same name.
> *However* after a refresh, or at least Disconnect-Connect, i see that the
> above table has dbo as the new owner !! and of course all designated users
> can access the interface.
> My question is:
> 1) why?
> 2) why sp_changedbowner doesn't work
> TIA
> -steve
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:391361
Thanx for your response.
However, this happened as soon as i copied my tables into a new development
server.
Without changing anything
The tables belonged to dbo in the original server. I copied to the
development server and they now belong to my.
I enter the command, get an error but after a refresh it seems that the
command worked...
"Hari Prasad" <hari_prasad_k@.hotmail.com> a crit dans le message de news:
OEPJlscVFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If the object owner is not DBO then you need to prefix ownername.tablename
> wqhile querying. I am not able to exactly say why this happen and
> it got solved after a DB restore. The major chance for this is some one
> have
> changed created the same table in a differerent user, say like this
> select * into table from dbo.table
> This is the only cjhnace I can think of.
> 2. Since there is an object with same name in the same database the
> sp_changeobjectowner procedure failed.
> Thanks
> Hari
> SQL Server MVP
>
> "steve" <steve@.here.com> wrote in message
> news:nk8ge.35541$vN2.885312@.wagner.videotron.net...
> is
> for
>
I am completely iliterate on SQL Server. Using just common sense and some
basic DB knowledge, along withnet references, I created a database which is
accessed through a VB.net GUI.
My application creates an Access string and uses Windows Authentication for
all users that access the databse.
I noticed that through the intervention of the Sever Admin I was owner of
all the Tables, both mine and the "default" system ones.
But as soon as i changed something or when i copied it the database to a
development server, the owner of the changed/copied tables became me (my
username). This creates problems as any other user that has a login, and
permissions from me (owner) still cannot access the database!.. Only when
the permissions come from the dbo, is the user able to use the application
and querry the database.
Now, when i run :
sp_changeobjectowner tblCodesQA, dbo
on Query Analyzer, i get the following message:
Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line
63
Cannot change owner of object 'tblCodesQA' or one of its child objects
because the new owner 'dbo' already has an object with the same name.
*However* after a refresh, or at least Disconnect-Connect, i see that the
above table has dbo as the new owner !! and of course all designated users
can access the interface.
My question is:
1) why?
2) why sp_changedbowner doesn't work
TIA
-steveHi,
If the object owner is not DBO then you need to prefix ownername.tablename
wqhile querying. I am not able to exactly say why this happen and
it got solved after a DB restore. The major chance for this is some one have
changed created the same table in a differerent user, say like this
select * into table from dbo.table
This is the only cjhnace I can think of.
2. Since there is an object with same name in the same database the
sp_changeobjectowner procedure failed.
Thanks
Hari
SQL Server MVP
"steve" <steve@.here.com> wrote in message
news:nk8ge.35541$vN2.885312@.wagner.videotron.net...
> Hi,
> I am completely iliterate on SQL Server. Using just common sense and some
> basic DB knowledge, along withnet references, I created a database which
is
> accessed through a VB.net GUI.
> My application creates an Access string and uses Windows Authentication
for
> all users that access the databse.
> I noticed that through the intervention of the Sever Admin I was owner of
> all the Tables, both mine and the "default" system ones.
> But as soon as i changed something or when i copied it the database to a
> development server, the owner of the changed/copied tables became me (my
> username). This creates problems as any other user that has a login, and
> permissions from me (owner) still cannot access the database!.. Only when
> the permissions come from the dbo, is the user able to use the application
> and querry the database.
> Now, when i run :
> sp_changeobjectowner tblCodesQA, dbo
> on Query Analyzer, i get the following message:
> Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line
> 63
> Cannot change owner of object 'tblCodesQA' or one of its child objects
> because the new owner 'dbo' already has an object with the same name.
> *However* after a refresh, or at least Disconnect-Connect, i see that the
> above table has dbo as the new owner !! and of course all designated users
> can access the interface.
> My question is:
> 1) why?
> 2) why sp_changedbowner doesn't work
> TIA
> -steve
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:391361
Thanx for your response.
However, this happened as soon as i copied my tables into a new development
server.
Without changing anything
The tables belonged to dbo in the original server. I copied to the
development server and they now belong to my.
I enter the command, get an error but after a refresh it seems that the
command worked...
"Hari Prasad" <hari_prasad_k@.hotmail.com> a crit dans le message de news:
OEPJlscVFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If the object owner is not DBO then you need to prefix ownername.tablename
> wqhile querying. I am not able to exactly say why this happen and
> it got solved after a DB restore. The major chance for this is some one
> have
> changed created the same table in a differerent user, say like this
> select * into table from dbo.table
> This is the only cjhnace I can think of.
> 2. Since there is an object with same name in the same database the
> sp_changeobjectowner procedure failed.
> Thanks
> Hari
> SQL Server MVP
>
> "steve" <steve@.here.com> wrote in message
> news:nk8ge.35541$vN2.885312@.wagner.videotron.net...
> is
> for
>
Monday, February 20, 2012
Newbie: Indexing and speeding up querries
Hi,
Are there any references on the basics of indexing?
I am not very familiar with a lot of concepts and dont have the time to
become an expert.
However, I would like to speed up my processing time. This is quite
important since I get
the results back to a VB front-end and long delays tend to hung the
application.
TIA
-stevesql-server-performance.com is a good place to start.
If you don't want to tune queries yourself, then SQL Server provides an
Index Tuning Wizard in Query Analyzer. This will recommend indexes, based on
the provided workload.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"steve" <steve@.here.com> wrote in message
news:FwNbe.31823$iP.563299@.weber.videotron.net...
Hi,
Are there any references on the basics of indexing?
I am not very familiar with a lot of concepts and dont have the time to
become an expert.
However, I would like to speed up my processing time. This is quite
important since I get
the results back to a VB front-end and long delays tend to hung the
application.
TIA
-steve|||In this link look the tips for indexes.
http://www.sql-server-performance.com/
AMB
"steve" wrote:
> Hi,
> Are there any references on the basics of indexing?
> I am not very familiar with a lot of concepts and dont have the time to
> become an expert.
> However, I would like to speed up my processing time. This is quite
> important since I get
> the results back to a VB front-end and long delays tend to hung the
> application.
> TIA
> -steve
>
>
Are there any references on the basics of indexing?
I am not very familiar with a lot of concepts and dont have the time to
become an expert.
However, I would like to speed up my processing time. This is quite
important since I get
the results back to a VB front-end and long delays tend to hung the
application.
TIA
-stevesql-server-performance.com is a good place to start.
If you don't want to tune queries yourself, then SQL Server provides an
Index Tuning Wizard in Query Analyzer. This will recommend indexes, based on
the provided workload.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"steve" <steve@.here.com> wrote in message
news:FwNbe.31823$iP.563299@.weber.videotron.net...
Hi,
Are there any references on the basics of indexing?
I am not very familiar with a lot of concepts and dont have the time to
become an expert.
However, I would like to speed up my processing time. This is quite
important since I get
the results back to a VB front-end and long delays tend to hung the
application.
TIA
-steve|||In this link look the tips for indexes.
http://www.sql-server-performance.com/
AMB
"steve" wrote:
> Hi,
> Are there any references on the basics of indexing?
> I am not very familiar with a lot of concepts and dont have the time to
> become an expert.
> However, I would like to speed up my processing time. This is quite
> important since I get
> the results back to a VB front-end and long delays tend to hung the
> application.
> TIA
> -steve
>
>
Subscribe to:
Posts (Atom)