I have setup transactional replication and its going fine since some time in
testing. I was trying to remove one of the tables from publication, but it
always says that i have to drop subscription inorder to drop table from
publication.
If I drop subscription and recreate new subscription after the table is
dropped, I will be out of synch. with publisher database as publisher is
still on-line.
Is there any easy way to get arround this...
Regards,
Ravi
Hi Ravi,
Not sure if I fully understand your situation but you can definitely drop
the subscription for just the table that you want to drop using
sp_dropsubscription (See Books Online). The UI (SEM & SMSS) doesn't usually
allow you to perform such article-level operation however.
HTH
-Raymond
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:EED2FB1D-7481-4027-B418-55BD7A97322E@.microsoft.com...
>I have setup transactional replication and its going fine since some time
>in
> testing. I was trying to remove one of the tables from publication, but it
> always says that i have to drop subscription inorder to drop table from
> publication.
> If I drop subscription and recreate new subscription after the table is
> dropped, I will be out of synch. with publisher database as publisher is
> still on-line.
> Is there any easy way to get arround this...
> Regards,
> Ravi
>
|||Raymond thanks for the reply, all i ma trying to do is drop one table from
existing publication and subscription.
and if possible add another table to same publication and subscription...
Regards,
Ravi
"Raymond Mak [MSFT]" wrote:
> Hi Ravi,
> Not sure if I fully understand your situation but you can definitely drop
> the subscription for just the table that you want to drop using
> sp_dropsubscription (See Books Online). The UI (SEM & SMSS) doesn't usually
> allow you to perform such article-level operation however.
> HTH
> -Raymond
> "SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
> message news:EED2FB1D-7481-4027-B418-55BD7A97322E@.microsoft.com...
>
>
|||Ravi,
I used this to change a column on a replicated table. It is almost exactly
the script you'll need:
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
alter table tEmployees alter column Forename varchar(100) null
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Ravi,
I keep forgetting that folks may be using Merge replication in which case it
is not possible (read: no supported way) to drop a single article without
reinitializing the entire subscription. But in case you are using
transactional\snapshot replication, I think here is what you need to do:
1) Call sp_dropsubscription to drop subscriptions on the article you want to
drop
2) Call sp_droparticle to drop the article you want to drop
3) Call sp_addarticle to add the new table to your publication
4) Call sp_addsubscrption to add subscription to your new table (this is not
necessary if syspublications.immediate_sync = 1)
5) Run snapshot agent to generate snapshot for the new article, run
distribution agent to apply it
You probably know about this already but dropping subscription on an article
doesn't remove data that has already been replicated to the subscriber. Of
course, you should be able to manually drop the table easily at the
subscriber if that is what you want.
-Raymond
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:CBE581C0-3897-4A40-A740-CB38D33D61C5@.microsoft.com...[vbcol=seagreen]
> Raymond thanks for the reply, all i ma trying to do is drop one table from
> existing publication and subscription.
> and if possible add another table to same publication and subscription...
> Regards,
> Ravi
> "Raymond Mak [MSFT]" wrote:
Showing posts with label setup. Show all posts
Showing posts with label setup. Show all posts
Thursday, March 29, 2012
dropping table from publication
Labels:
database,
dropping,
intesting,
microsoft,
mysql,
oracle,
publication,
replication,
server,
setup,
sql,
table,
tables,
time,
transactional
Tuesday, March 27, 2012
Dropping article
Hi,
I have setup merge replication to run between two SQL 2k (SP3) across the internet. But while selecting the articles via EM I forgot to select "Identity Resolver" for a particular table. And this is creating havoc in that table.
I have to solve this by dropping the article. Or is there any other way?
But if I try to drop the article it says that there is an active subscription for this publication. Would this mean that I will have to drop my entire publication and create the entire thing again?
Can someone help me with this?
Thank you.
Regards,
Karthik
Karthik,
it is not mandatory to reinitialize - you can drop the subscription to a particular article using sp_dropsubscription then drop the article using sp_droparticle, then sp_addarticle to readd then sp_refreshsubscriptions.
Incidentally, what is the error message you see?
Regards,
Paul Ibison
|||Hi Paul,
Thank you for the quick response.
I see a primary key violation for a column that has IDENTITY property set and marked as "YES NOT FOR REPLICATION".
So according to you I do the following -
1. Run sp_dropsubscription at the publisher.
2. Run sp_droparticle
3. Run sp_addarticle
4. Run sp_refreshsubscriptions
I am not all that experienced in Replication. So I still have a few queries on what you have said. I would like to be really clear before I do something as this is the production database.
If I drop a subscription from the publisher, will it not mean that I would need to initialize via the snapshots again?
And what does sp_refreshsubscriptions do actually? I did look at BOL, but didn't understand too much.
Regards,
Karthik.
|||Karthik,
apologies - I just reread your post and noticed that you are using merge replication. In this case my route won't work - it only works for snapshot and transactional. What you can do though is to script out your publication (inc subscriptions),delete the
publication (and prevent users from editing data on the subscriber), make necessary changes to the table, then recreate the publication. For each of the other articles you set @.sync_type to 'none' to establish that the table already exists on the subscrib
er. For the table that you want the identity value, you'll need to have @.sync_type set to 'automatic'.
Alternatively you can have several publications to separate the articles if this is a common issue.
As another point, I'd mention that you can be sure to avoid these PK violations if you set the increment of the identity column in such a way as there can never be an overlap eg publisher uses even numbers and subscriber odd ones. There is an article by M
ichael Hotek on this (http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Hi Paul,
I did generate a script of both the publisher and subscribers. But nowhere could I see a @.sync_type. I searched the entire text file.
May be I forgot to specify that I have a pull subscription.
Do let me know if my posting part of the script file would help.
Thank you once again.
Regards,
Karthik
|||Karthik,
it's a parameter off sp_addmergepullsubscription or sp_addmergesubscription.
However, unfortunately it is not valid on a per article basis
- hadn't
appreciated this subtle difference between transactional and merge, so you'd
have to set @.sync_type set to 'automatic' for the complete subscription. In
your case you might be able to make the required table modification to the
table on the publisher and subscriber then run this. As it involves identity
column changes whose ranges are saved to system tables I suspect this is not
viable and would advise another route:
(a) drop the publication! Add 2 new publications - one with the old tables
apart from the problemmatic one, one with the problemmatic table. For the
first one have @.sync_type set to 'none' (subscriber has schema) and have
@.sync_type set to 'automatic' for the second.
(b) if this is not feasible (due to PK-FK constraints between articles),
then go down the initial route of a complete recreation of the publication.
Sorry it's not easier advice.
Regards,
Paul Ibison
|||Hi Paul,
What you seemed to have mentioned seems quite complicated. I would like to stall it as much as possible. Anyways, may be I am trying to go about this the wrong way as I didn't specify why I want to drop the article.
Is there any way I can specify idenity ranges for an article after it has been published? What I need to do basically is, specify that my table "FTPFile" has a range of 1-1000 from my publisher with a threshold of 80%.
Is there any way of doing this?
Thank you once again!
Regards,
Karthik.
|||Karthik,
the only sp wich might be relevant is:
sp_adjustpublisheridentityrange
From BOL: For a publication which has the auto identity range enabled, the
Distribution Agent or Merge Agent is responsible for automatically adjusting
the identity range in a publication based on its threshold value. However,
if for some reason the Distribution Agent or Merge Agent has not been run
for a period of time, and identity range resource have been consumed heavily
to the point of threshold, users can call sp_adjustpublisheridentityrange to
allocate a new range of values for a Publisher.
The identity ranges are held in a system table and the infrastructure is
also implemented using check constraints, but I would not advise you to
change these manually.
My recommendation would be to set it up again and ensure there can't be any
problems by following Michael Hotek's example: eg publisher uses even
numbers and subscriber odd ones. There is an article on this
(http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Hi Paul,
Thank you for all the help.
I was able to fix the problem as suggested in the link.
Thank you once again.
Regards,
Karthik
I have setup merge replication to run between two SQL 2k (SP3) across the internet. But while selecting the articles via EM I forgot to select "Identity Resolver" for a particular table. And this is creating havoc in that table.
I have to solve this by dropping the article. Or is there any other way?
But if I try to drop the article it says that there is an active subscription for this publication. Would this mean that I will have to drop my entire publication and create the entire thing again?
Can someone help me with this?
Thank you.
Regards,
Karthik
Karthik,
it is not mandatory to reinitialize - you can drop the subscription to a particular article using sp_dropsubscription then drop the article using sp_droparticle, then sp_addarticle to readd then sp_refreshsubscriptions.
Incidentally, what is the error message you see?
Regards,
Paul Ibison
|||Hi Paul,
Thank you for the quick response.
I see a primary key violation for a column that has IDENTITY property set and marked as "YES NOT FOR REPLICATION".
So according to you I do the following -
1. Run sp_dropsubscription at the publisher.
2. Run sp_droparticle
3. Run sp_addarticle
4. Run sp_refreshsubscriptions
I am not all that experienced in Replication. So I still have a few queries on what you have said. I would like to be really clear before I do something as this is the production database.
If I drop a subscription from the publisher, will it not mean that I would need to initialize via the snapshots again?
And what does sp_refreshsubscriptions do actually? I did look at BOL, but didn't understand too much.
Regards,
Karthik.
|||Karthik,
apologies - I just reread your post and noticed that you are using merge replication. In this case my route won't work - it only works for snapshot and transactional. What you can do though is to script out your publication (inc subscriptions),delete the
publication (and prevent users from editing data on the subscriber), make necessary changes to the table, then recreate the publication. For each of the other articles you set @.sync_type to 'none' to establish that the table already exists on the subscrib
er. For the table that you want the identity value, you'll need to have @.sync_type set to 'automatic'.
Alternatively you can have several publications to separate the articles if this is a common issue.
As another point, I'd mention that you can be sure to avoid these PK violations if you set the increment of the identity column in such a way as there can never be an overlap eg publisher uses even numbers and subscriber odd ones. There is an article by M
ichael Hotek on this (http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Hi Paul,
I did generate a script of both the publisher and subscribers. But nowhere could I see a @.sync_type. I searched the entire text file.
May be I forgot to specify that I have a pull subscription.
Do let me know if my posting part of the script file would help.
Thank you once again.
Regards,
Karthik
|||Karthik,
it's a parameter off sp_addmergepullsubscription or sp_addmergesubscription.
However, unfortunately it is not valid on a per article basis
appreciated this subtle difference between transactional and merge, so you'd
have to set @.sync_type set to 'automatic' for the complete subscription. In
your case you might be able to make the required table modification to the
table on the publisher and subscriber then run this. As it involves identity
column changes whose ranges are saved to system tables I suspect this is not
viable and would advise another route:
(a) drop the publication! Add 2 new publications - one with the old tables
apart from the problemmatic one, one with the problemmatic table. For the
first one have @.sync_type set to 'none' (subscriber has schema) and have
@.sync_type set to 'automatic' for the second.
(b) if this is not feasible (due to PK-FK constraints between articles),
then go down the initial route of a complete recreation of the publication.
Sorry it's not easier advice.
Regards,
Paul Ibison
|||Hi Paul,
What you seemed to have mentioned seems quite complicated. I would like to stall it as much as possible. Anyways, may be I am trying to go about this the wrong way as I didn't specify why I want to drop the article.
Is there any way I can specify idenity ranges for an article after it has been published? What I need to do basically is, specify that my table "FTPFile" has a range of 1-1000 from my publisher with a threshold of 80%.
Is there any way of doing this?
Thank you once again!
Regards,
Karthik.
|||Karthik,
the only sp wich might be relevant is:
sp_adjustpublisheridentityrange
From BOL: For a publication which has the auto identity range enabled, the
Distribution Agent or Merge Agent is responsible for automatically adjusting
the identity range in a publication based on its threshold value. However,
if for some reason the Distribution Agent or Merge Agent has not been run
for a period of time, and identity range resource have been consumed heavily
to the point of threshold, users can call sp_adjustpublisheridentityrange to
allocate a new range of values for a Publisher.
The identity ranges are held in a system table and the infrastructure is
also implemented using check constraints, but I would not advise you to
change these manually.
My recommendation would be to set it up again and ensure there can't be any
problems by following Michael Hotek's example: eg publisher uses even
numbers and subscriber odd ones. There is an article on this
(http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Hi Paul,
Thank you for all the help.
I was able to fix the problem as suggested in the link.
Thank you once again.
Regards,
Karthik
Sunday, March 25, 2012
Dropping a table from replication
Transaction replication:
Please help. I am new to replication.
I have setup Transaction replication between two databases. The replication
is running.
I now need to drop a replicate table in the source database but it would not
let me.
I don't want to remove the whole replication inorder to drop a table.
Can you show me how to do it step by step?
Thank You.
RClick the Publication/ Generate SQL Script/ Script the steps to delete.../
Preview/ grab the sp_dropsubscription and sp_droparticle for just the table
you need/ run in QA.
"Sam" <skhou@.computability.ab.ca> wrote in message
news:eaEoIuzKFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Transaction replication:
> Please help. I am new to replication.
> I have setup Transaction replication between two databases. The
replication
> is running.
> I now need to drop a replicate table in the source database but it would
not
> let me.
> I don't want to remove the whole replication inorder to drop a table.
> Can you show me how to do it step by step?
> Thank You.
>
Please help. I am new to replication.
I have setup Transaction replication between two databases. The replication
is running.
I now need to drop a replicate table in the source database but it would not
let me.
I don't want to remove the whole replication inorder to drop a table.
Can you show me how to do it step by step?
Thank You.
RClick the Publication/ Generate SQL Script/ Script the steps to delete.../
Preview/ grab the sp_dropsubscription and sp_droparticle for just the table
you need/ run in QA.
"Sam" <skhou@.computability.ab.ca> wrote in message
news:eaEoIuzKFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Transaction replication:
> Please help. I am new to replication.
> I have setup Transaction replication between two databases. The
replication
> is running.
> I now need to drop a replicate table in the source database but it would
not
> let me.
> I don't want to remove the whole replication inorder to drop a table.
> Can you show me how to do it step by step?
> Thank You.
>
Labels:
database,
databases,
dropping,
microsoft,
mysql,
oracle,
replication,
replicationis,
replicationplease,
running,
server,
setup,
sql,
table,
transaction
Monday, March 19, 2012
Drop Subcription at subscriber
Sql Server 2000. I had a merge publication setup on one table that replicated to several sql servers (all 2000 SP3). The publication was deleted on the publisher - but somehow - the subscription information is still on the subscribers. How can I remove them? When trying to delete the tables at the subscribers I get error that they are part of replication.
P.S. Other tables in the same database are part of different merge replication.
Thanks.
Abe
If you do not want the subscriber database to be participated in replication, you can do
sp_replicationdboption 'subscriber_database', 'subscribe', 'false' on the subscriber side; then drop the table.
Thanks.
This posting is provided AS IS with no warranties, and confers no rights
|||Thank you for your reply. I forgot to mention in the original post (which i justed edited) that the database contains tables (not the table I'm trying to delete) that are part of another publication. Therefore I do not think I can run sp_replicationdboption. Also, I thought you can only run sp_replicationdboption on the publisher - but I stand to be corrected.|||Run sp_dropmergepullsubscription with required parameters at the subscriber database.
Labels:
database,
deleted,
drop,
merge,
microsoft,
mysql,
oracle,
publication,
replicated,
server,
servers,
setup,
sp3,
sql,
subcription,
subscriber,
table
Sunday, February 26, 2012
DROP a Mirrored DB
I setup Database Mirroring for the first time on SQL 2005 SP1 and am at
a point where I want to delete the mirrored test database. However,
when I try to drop the DB on the mirrored server I receive an error.
It won't let me delete the DB since it is in "In Recovery" mode. What
do I have to do to DROP this database? The copy on the primary server
is already removed?
Msg 3743, Level 16, State 1, Line 1
Database Mirror_Test is enabled for Database Mirroring, Database
Mirroring must be removed before dropping the database.
Found out how.
ALTER DATABASE Mirror_Test SET PARTNER OFF
shaun.steckley@.gmail.com wrote:
> I setup Database Mirroring for the first time on SQL 2005 SP1 and am at
> a point where I want to delete the mirrored test database. However,
> when I try to drop the DB on the mirrored server I receive an error.
> It won't let me delete the DB since it is in "In Recovery" mode. What
> do I have to do to DROP this database? The copy on the primary server
> is already removed?
> --
> Msg 3743, Level 16, State 1, Line 1
> Database Mirror_Test is enabled for Database Mirroring, Database
> Mirroring must be removed before dropping the database.
> --
|||Hi Shaun
Check out http://msdn2.microsoft.com/en-us/library/ms180801.aspx for
instructions or look in Books Online.
John
"shaun.steckley@.gmail.com" wrote:
> I setup Database Mirroring for the first time on SQL 2005 SP1 and am at
> a point where I want to delete the mirrored test database. However,
> when I try to drop the DB on the mirrored server I receive an error.
> It won't let me delete the DB since it is in "In Recovery" mode. What
> do I have to do to DROP this database? The copy on the primary server
> is already removed?
> --
> Msg 3743, Level 16, State 1, Line 1
> Database Mirror_Test is enabled for Database Mirroring, Database
> Mirroring must be removed before dropping the database.
> --
>
a point where I want to delete the mirrored test database. However,
when I try to drop the DB on the mirrored server I receive an error.
It won't let me delete the DB since it is in "In Recovery" mode. What
do I have to do to DROP this database? The copy on the primary server
is already removed?
Msg 3743, Level 16, State 1, Line 1
Database Mirror_Test is enabled for Database Mirroring, Database
Mirroring must be removed before dropping the database.
Found out how.
ALTER DATABASE Mirror_Test SET PARTNER OFF
shaun.steckley@.gmail.com wrote:
> I setup Database Mirroring for the first time on SQL 2005 SP1 and am at
> a point where I want to delete the mirrored test database. However,
> when I try to drop the DB on the mirrored server I receive an error.
> It won't let me delete the DB since it is in "In Recovery" mode. What
> do I have to do to DROP this database? The copy on the primary server
> is already removed?
> --
> Msg 3743, Level 16, State 1, Line 1
> Database Mirror_Test is enabled for Database Mirroring, Database
> Mirroring must be removed before dropping the database.
> --
|||Hi Shaun
Check out http://msdn2.microsoft.com/en-us/library/ms180801.aspx for
instructions or look in Books Online.
John
"shaun.steckley@.gmail.com" wrote:
> I setup Database Mirroring for the first time on SQL 2005 SP1 and am at
> a point where I want to delete the mirrored test database. However,
> when I try to drop the DB on the mirrored server I receive an error.
> It won't let me delete the DB since it is in "In Recovery" mode. What
> do I have to do to DROP this database? The copy on the primary server
> is already removed?
> --
> Msg 3743, Level 16, State 1, Line 1
> Database Mirror_Test is enabled for Database Mirroring, Database
> Mirroring must be removed before dropping the database.
> --
>
DROP a Mirrored DB
I setup Database Mirroring for the first time on SQL 2005 SP1 and am at
a point where I want to delete the mirrored test database. However,
when I try to drop the DB on the mirrored server I receive an error.
It won't let me delete the DB since it is in "In Recovery" mode. What
do I have to do to DROP this database? The copy on the primary server
is already removed?
Msg 3743, Level 16, State 1, Line 1
Database Mirror_Test is enabled for Database Mirroring, Database
Mirroring must be removed before dropping the database.Found out how.
ALTER DATABASE Mirror_Test SET PARTNER OFF
shaun.steckley@.gmail.com wrote:
> I setup Database Mirroring for the first time on SQL 2005 SP1 and am at
> a point where I want to delete the mirrored test database. However,
> when I try to drop the DB on the mirrored server I receive an error.
> It won't let me delete the DB since it is in "In Recovery" mode. What
> do I have to do to DROP this database? The copy on the primary server
> is already removed?
> --
> Msg 3743, Level 16, State 1, Line 1
> Database Mirror_Test is enabled for Database Mirroring, Database
> Mirroring must be removed before dropping the database.
> --|||Hi Shaun
Check out http://msdn2.microsoft.com/en-us/library/ms180801.aspx for
instructions or look in Books Online.
John
"shaun.steckley@.gmail.com" wrote:
> I setup Database Mirroring for the first time on SQL 2005 SP1 and am at
> a point where I want to delete the mirrored test database. However,
> when I try to drop the DB on the mirrored server I receive an error.
> It won't let me delete the DB since it is in "In Recovery" mode. What
> do I have to do to DROP this database? The copy on the primary server
> is already removed?
> --
> Msg 3743, Level 16, State 1, Line 1
> Database Mirror_Test is enabled for Database Mirroring, Database
> Mirroring must be removed before dropping the database.
> --
>
a point where I want to delete the mirrored test database. However,
when I try to drop the DB on the mirrored server I receive an error.
It won't let me delete the DB since it is in "In Recovery" mode. What
do I have to do to DROP this database? The copy on the primary server
is already removed?
Msg 3743, Level 16, State 1, Line 1
Database Mirror_Test is enabled for Database Mirroring, Database
Mirroring must be removed before dropping the database.Found out how.
ALTER DATABASE Mirror_Test SET PARTNER OFF
shaun.steckley@.gmail.com wrote:
> I setup Database Mirroring for the first time on SQL 2005 SP1 and am at
> a point where I want to delete the mirrored test database. However,
> when I try to drop the DB on the mirrored server I receive an error.
> It won't let me delete the DB since it is in "In Recovery" mode. What
> do I have to do to DROP this database? The copy on the primary server
> is already removed?
> --
> Msg 3743, Level 16, State 1, Line 1
> Database Mirror_Test is enabled for Database Mirroring, Database
> Mirroring must be removed before dropping the database.
> --|||Hi Shaun
Check out http://msdn2.microsoft.com/en-us/library/ms180801.aspx for
instructions or look in Books Online.
John
"shaun.steckley@.gmail.com" wrote:
> I setup Database Mirroring for the first time on SQL 2005 SP1 and am at
> a point where I want to delete the mirrored test database. However,
> when I try to drop the DB on the mirrored server I receive an error.
> It won't let me delete the DB since it is in "In Recovery" mode. What
> do I have to do to DROP this database? The copy on the primary server
> is already removed?
> --
> Msg 3743, Level 16, State 1, Line 1
> Database Mirror_Test is enabled for Database Mirroring, Database
> Mirroring must be removed before dropping the database.
> --
>
DROP a Mirrored DB
I setup Database Mirroring for the first time on SQL 2005 SP1 and am at
a point where I want to delete the mirrored test database. However,
when I try to drop the DB on the mirrored server I receive an error.
It won't let me delete the DB since it is in "In Recovery" mode. What
do I have to do to DROP this database? The copy on the primary server
is already removed?
--
Msg 3743, Level 16, State 1, Line 1
Database Mirror_Test is enabled for Database Mirroring, Database
Mirroring must be removed before dropping the database.
--Found out how.
ALTER DATABASE Mirror_Test SET PARTNER OFF
shaun.steckley@.gmail.com wrote:
> I setup Database Mirroring for the first time on SQL 2005 SP1 and am at
> a point where I want to delete the mirrored test database. However,
> when I try to drop the DB on the mirrored server I receive an error.
> It won't let me delete the DB since it is in "In Recovery" mode. What
> do I have to do to DROP this database? The copy on the primary server
> is already removed?
> --
> Msg 3743, Level 16, State 1, Line 1
> Database Mirror_Test is enabled for Database Mirroring, Database
> Mirroring must be removed before dropping the database.
> --|||Hi Shaun
Check out http://msdn2.microsoft.com/en-us/library/ms180801.aspx for
instructions or look in Books Online.
John
"shaun.steckley@.gmail.com" wrote:
> I setup Database Mirroring for the first time on SQL 2005 SP1 and am at
> a point where I want to delete the mirrored test database. However,
> when I try to drop the DB on the mirrored server I receive an error.
> It won't let me delete the DB since it is in "In Recovery" mode. What
> do I have to do to DROP this database? The copy on the primary server
> is already removed?
> --
> Msg 3743, Level 16, State 1, Line 1
> Database Mirror_Test is enabled for Database Mirroring, Database
> Mirroring must be removed before dropping the database.
> --
>
a point where I want to delete the mirrored test database. However,
when I try to drop the DB on the mirrored server I receive an error.
It won't let me delete the DB since it is in "In Recovery" mode. What
do I have to do to DROP this database? The copy on the primary server
is already removed?
--
Msg 3743, Level 16, State 1, Line 1
Database Mirror_Test is enabled for Database Mirroring, Database
Mirroring must be removed before dropping the database.
--Found out how.
ALTER DATABASE Mirror_Test SET PARTNER OFF
shaun.steckley@.gmail.com wrote:
> I setup Database Mirroring for the first time on SQL 2005 SP1 and am at
> a point where I want to delete the mirrored test database. However,
> when I try to drop the DB on the mirrored server I receive an error.
> It won't let me delete the DB since it is in "In Recovery" mode. What
> do I have to do to DROP this database? The copy on the primary server
> is already removed?
> --
> Msg 3743, Level 16, State 1, Line 1
> Database Mirror_Test is enabled for Database Mirroring, Database
> Mirroring must be removed before dropping the database.
> --|||Hi Shaun
Check out http://msdn2.microsoft.com/en-us/library/ms180801.aspx for
instructions or look in Books Online.
John
"shaun.steckley@.gmail.com" wrote:
> I setup Database Mirroring for the first time on SQL 2005 SP1 and am at
> a point where I want to delete the mirrored test database. However,
> when I try to drop the DB on the mirrored server I receive an error.
> It won't let me delete the DB since it is in "In Recovery" mode. What
> do I have to do to DROP this database? The copy on the primary server
> is already removed?
> --
> Msg 3743, Level 16, State 1, Line 1
> Database Mirror_Test is enabled for Database Mirroring, Database
> Mirroring must be removed before dropping the database.
> --
>
Friday, February 24, 2012
drive configuration on new sql server 2005 box?
I have a new dell 2850 server with 4 146gb drives attached to a raid 5
controller. I would like to know how I should setup the drives for
installation of server 2003 std os and sql server 2005. This server is going
to be a dedicated sql server to serve databases for web applications.
Thanks and let me know if I should supply any additional info.
Shelly Campbell
This will give you some ideas
http://www.microsoft.com/technet/pro...n/sqlops6.mspx
In BOL, index tab, type NTFS, see note.
SQL Server magazine had a good article on hardware for SQL Server . I can't
post it as it is for subscribers only
http://www.windowsitpro.com/SQLServe...92/46492.html#
In the nutshell, system = RAID1, logs = RAID1, data = case when cash poor
then RAID5 else RAID10 end.
Farmer.
"mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
news:1923B0A6-06A2-4041-850A-C692718D54AB@.microsoft.com...
>I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> controller. I would like to know how I should setup the drives for
> installation of server 2003 std os and sql server 2005. This server is
> going
> to be a dedicated sql server to serve databases for web applications.
> Thanks and let me know if I should supply any additional info.
> Shelly Campbell
|||Hi Shelly:
OS should always stay on RAID 1. No Exceptions should be made here.
For SQL Data, You can use RAID 5 if you wish to though RAID 5 is not the
best solution. For Faster Read\Write RAID 10 is the best solution but then it
has a cost attached to its performance.
Since you have 4x146 GB Drives on the machine, I would recommend that buy
another couple of drives identical in size and host RAID 1 on it and use it
for OS.
For other 4 drives set up RAID 5 and use them for SQL data and log.
Hope this helps.
"mp3nomad" wrote:
> I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> controller. I would like to know how I should setup the drives for
> installation of server 2003 std os and sql server 2005. This server is going
> to be a dedicated sql server to serve databases for web applications.
> Thanks and let me know if I should supply any additional info.
> Shelly Campbell
|||Thanks! This helps.
"Farmer" wrote:
> This will give you some ideas
> http://www.microsoft.com/technet/pro...n/sqlops6.mspx
> In BOL, index tab, type NTFS, see note.
> SQL Server magazine had a good article on hardware for SQL Server . I can't
> post it as it is for subscribers only
> http://www.windowsitpro.com/SQLServe...92/46492.html#
> In the nutshell, system = RAID1, logs = RAID1, data = case when cash poor
> then RAID5 else RAID10 end.
>
> Farmer.
> "mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
> news:1923B0A6-06A2-4041-850A-C692718D54AB@.microsoft.com...
>
>
|||Thanks Mark.
Since we're kind of tight on funds, I'm going to setup the drives as follows:
1 separate 36 gb drive for OS and SQL Server program install.
1 separate 146 gb drive for Tlogs.
3 146 gb disks in RAID 5 array for data.
Does this look acceptable? Not the best, but acceptable?
"mp3nomad" wrote:
> I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> controller. I would like to know how I should setup the drives for
> installation of server 2003 std os and sql server 2005. This server is going
> to be a dedicated sql server to serve databases for web applications.
> Thanks and let me know if I should supply any additional info.
> Shelly Campbell
|||NO, this set up is not acceptable.
If you use only one drive for OS, if the drive fails, everything is gone.
Therefore, you must, must, and must use RAID 1 for OS which will involve a
minimum of 2 drives.
Here is what you need to remember:
RAID 1 requires at least 2 drives.
Raid 5 requires at least 3 drives.
If you use only 1 drive as you are mentionining, you can only build RAID 0
on it which gives no protection against data recovery.
I would say based on your server since it's not on SAN, use 2 drives for
RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data on
it. SQL Binaries can say on the same RAID Volume where OS resides.
I hope this helps.
"mp3nomad" wrote:
[vbcol=seagreen]
> Thanks Mark.
> Since we're kind of tight on funds, I'm going to setup the drives as follows:
> 1 separate 36 gb drive for OS and SQL Server program install.
> 1 separate 146 gb drive for Tlogs.
> 3 146 gb disks in RAID 5 array for data.
> Does this look acceptable? Not the best, but acceptable?
> "mp3nomad" wrote:
|||I see. Ok.
Where would the transaction logs go? On the same RAID volume with the OS?
"Mark" wrote:
[vbcol=seagreen]
> NO, this set up is not acceptable.
> If you use only one drive for OS, if the drive fails, everything is gone.
> Therefore, you must, must, and must use RAID 1 for OS which will involve a
> minimum of 2 drives.
> Here is what you need to remember:
> RAID 1 requires at least 2 drives.
> Raid 5 requires at least 3 drives.
> If you use only 1 drive as you are mentionining, you can only build RAID 0
> on it which gives no protection against data recovery.
> I would say based on your server since it's not on SAN, use 2 drives for
> RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data on
> it. SQL Binaries can say on the same RAID Volume where OS resides.
> I hope this helps.
>
> "mp3nomad" wrote:
|||currently the server is configured as follows:
4 146 gb drives in RAID 5 container partitioned as C:\ (30 gb) and D:\
(remainder of space)
Windows Server 2003 w/ SP1 installed on C:\ partition
nothing else installed as yet
I'm trying to figure out if this setup is ok and then just add an additional
RAID 1 array for the transaction logs?
I appreciate your assistance.
Shelly
"Mark" wrote:
[vbcol=seagreen]
> NO, this set up is not acceptable.
> If you use only one drive for OS, if the drive fails, everything is gone.
> Therefore, you must, must, and must use RAID 1 for OS which will involve a
> minimum of 2 drives.
> Here is what you need to remember:
> RAID 1 requires at least 2 drives.
> Raid 5 requires at least 3 drives.
> If you use only 1 drive as you are mentionining, you can only build RAID 0
> on it which gives no protection against data recovery.
> I would say based on your server since it's not on SAN, use 2 drives for
> RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data on
> it. SQL Binaries can say on the same RAID Volume where OS resides.
> I hope this helps.
>
> "mp3nomad" wrote:
|||Do not put log on same drive where the OS is.
"mp3nomad" wrote:
[vbcol=seagreen]
> currently the server is configured as follows:
> 4 146 gb drives in RAID 5 container partitioned as C:\ (30 gb) and D:\
> (remainder of space)
> Windows Server 2003 w/ SP1 installed on C:\ partition
> nothing else installed as yet
> I'm trying to figure out if this setup is ok and then just add an additional
> RAID 1 array for the transaction logs?
> I appreciate your assistance.
> Shelly
> "Mark" wrote:
|||ok. thanks! we're trying to configure this box the best we can based on the
hardware we can afford to purchase.
"Mark" wrote:
[vbcol=seagreen]
> Do not put log on same drive where the OS is.
> "mp3nomad" wrote:
controller. I would like to know how I should setup the drives for
installation of server 2003 std os and sql server 2005. This server is going
to be a dedicated sql server to serve databases for web applications.
Thanks and let me know if I should supply any additional info.
Shelly Campbell
This will give you some ideas
http://www.microsoft.com/technet/pro...n/sqlops6.mspx
In BOL, index tab, type NTFS, see note.
SQL Server magazine had a good article on hardware for SQL Server . I can't
post it as it is for subscribers only
http://www.windowsitpro.com/SQLServe...92/46492.html#
In the nutshell, system = RAID1, logs = RAID1, data = case when cash poor
then RAID5 else RAID10 end.
Farmer.
"mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
news:1923B0A6-06A2-4041-850A-C692718D54AB@.microsoft.com...
>I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> controller. I would like to know how I should setup the drives for
> installation of server 2003 std os and sql server 2005. This server is
> going
> to be a dedicated sql server to serve databases for web applications.
> Thanks and let me know if I should supply any additional info.
> Shelly Campbell
|||Hi Shelly:
OS should always stay on RAID 1. No Exceptions should be made here.
For SQL Data, You can use RAID 5 if you wish to though RAID 5 is not the
best solution. For Faster Read\Write RAID 10 is the best solution but then it
has a cost attached to its performance.
Since you have 4x146 GB Drives on the machine, I would recommend that buy
another couple of drives identical in size and host RAID 1 on it and use it
for OS.
For other 4 drives set up RAID 5 and use them for SQL data and log.
Hope this helps.
"mp3nomad" wrote:
> I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> controller. I would like to know how I should setup the drives for
> installation of server 2003 std os and sql server 2005. This server is going
> to be a dedicated sql server to serve databases for web applications.
> Thanks and let me know if I should supply any additional info.
> Shelly Campbell
|||Thanks! This helps.
"Farmer" wrote:
> This will give you some ideas
> http://www.microsoft.com/technet/pro...n/sqlops6.mspx
> In BOL, index tab, type NTFS, see note.
> SQL Server magazine had a good article on hardware for SQL Server . I can't
> post it as it is for subscribers only
> http://www.windowsitpro.com/SQLServe...92/46492.html#
> In the nutshell, system = RAID1, logs = RAID1, data = case when cash poor
> then RAID5 else RAID10 end.
>
> Farmer.
> "mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
> news:1923B0A6-06A2-4041-850A-C692718D54AB@.microsoft.com...
>
>
|||Thanks Mark.
Since we're kind of tight on funds, I'm going to setup the drives as follows:
1 separate 36 gb drive for OS and SQL Server program install.
1 separate 146 gb drive for Tlogs.
3 146 gb disks in RAID 5 array for data.
Does this look acceptable? Not the best, but acceptable?
"mp3nomad" wrote:
> I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> controller. I would like to know how I should setup the drives for
> installation of server 2003 std os and sql server 2005. This server is going
> to be a dedicated sql server to serve databases for web applications.
> Thanks and let me know if I should supply any additional info.
> Shelly Campbell
|||NO, this set up is not acceptable.
If you use only one drive for OS, if the drive fails, everything is gone.
Therefore, you must, must, and must use RAID 1 for OS which will involve a
minimum of 2 drives.
Here is what you need to remember:
RAID 1 requires at least 2 drives.
Raid 5 requires at least 3 drives.
If you use only 1 drive as you are mentionining, you can only build RAID 0
on it which gives no protection against data recovery.
I would say based on your server since it's not on SAN, use 2 drives for
RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data on
it. SQL Binaries can say on the same RAID Volume where OS resides.
I hope this helps.
"mp3nomad" wrote:
[vbcol=seagreen]
> Thanks Mark.
> Since we're kind of tight on funds, I'm going to setup the drives as follows:
> 1 separate 36 gb drive for OS and SQL Server program install.
> 1 separate 146 gb drive for Tlogs.
> 3 146 gb disks in RAID 5 array for data.
> Does this look acceptable? Not the best, but acceptable?
> "mp3nomad" wrote:
|||I see. Ok.
Where would the transaction logs go? On the same RAID volume with the OS?
"Mark" wrote:
[vbcol=seagreen]
> NO, this set up is not acceptable.
> If you use only one drive for OS, if the drive fails, everything is gone.
> Therefore, you must, must, and must use RAID 1 for OS which will involve a
> minimum of 2 drives.
> Here is what you need to remember:
> RAID 1 requires at least 2 drives.
> Raid 5 requires at least 3 drives.
> If you use only 1 drive as you are mentionining, you can only build RAID 0
> on it which gives no protection against data recovery.
> I would say based on your server since it's not on SAN, use 2 drives for
> RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data on
> it. SQL Binaries can say on the same RAID Volume where OS resides.
> I hope this helps.
>
> "mp3nomad" wrote:
|||currently the server is configured as follows:
4 146 gb drives in RAID 5 container partitioned as C:\ (30 gb) and D:\
(remainder of space)
Windows Server 2003 w/ SP1 installed on C:\ partition
nothing else installed as yet
I'm trying to figure out if this setup is ok and then just add an additional
RAID 1 array for the transaction logs?
I appreciate your assistance.
Shelly
"Mark" wrote:
[vbcol=seagreen]
> NO, this set up is not acceptable.
> If you use only one drive for OS, if the drive fails, everything is gone.
> Therefore, you must, must, and must use RAID 1 for OS which will involve a
> minimum of 2 drives.
> Here is what you need to remember:
> RAID 1 requires at least 2 drives.
> Raid 5 requires at least 3 drives.
> If you use only 1 drive as you are mentionining, you can only build RAID 0
> on it which gives no protection against data recovery.
> I would say based on your server since it's not on SAN, use 2 drives for
> RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data on
> it. SQL Binaries can say on the same RAID Volume where OS resides.
> I hope this helps.
>
> "mp3nomad" wrote:
|||Do not put log on same drive where the OS is.
"mp3nomad" wrote:
[vbcol=seagreen]
> currently the server is configured as follows:
> 4 146 gb drives in RAID 5 container partitioned as C:\ (30 gb) and D:\
> (remainder of space)
> Windows Server 2003 w/ SP1 installed on C:\ partition
> nothing else installed as yet
> I'm trying to figure out if this setup is ok and then just add an additional
> RAID 1 array for the transaction logs?
> I appreciate your assistance.
> Shelly
> "Mark" wrote:
|||ok. thanks! we're trying to configure this box the best we can based on the
hardware we can afford to purchase.
"Mark" wrote:
[vbcol=seagreen]
> Do not put log on same drive where the OS is.
> "mp3nomad" wrote:
drive configuration on new sql server 2005 box?
I have a new dell 2850 server with 4 146gb drives attached to a raid 5
controller. I would like to know how I should setup the drives for
installation of server 2003 std os and sql server 2005. This server is going
to be a dedicated sql server to serve databases for web applications.
Thanks and let me know if I should supply any additional info.
Shelly CampbellThis will give you some ideas
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx
In BOL, index tab, type NTFS, see note.
SQL Server magazine had a good article on hardware for SQL Server . I can't
post it as it is for subscribers only
http://www.windowsitpro.com/SQLServer/Article/ArticleID/46492/46492.html#
In the nutshell, system = RAID1, logs = RAID1, data = case when cash poor
then RAID5 else RAID10 end.
Farmer.
"mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
news:1923B0A6-06A2-4041-850A-C692718D54AB@.microsoft.com...
>I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> controller. I would like to know how I should setup the drives for
> installation of server 2003 std os and sql server 2005. This server is
> going
> to be a dedicated sql server to serve databases for web applications.
> Thanks and let me know if I should supply any additional info.
> Shelly Campbell|||Hi Shelly:
OS should always stay on RAID 1. No Exceptions should be made here.
For SQL Data, You can use RAID 5 if you wish to though RAID 5 is not the
best solution. For Faster Read\Write RAID 10 is the best solution but then it
has a cost attached to its performance.
Since you have 4x146 GB Drives on the machine, I would recommend that buy
another couple of drives identical in size and host RAID 1 on it and use it
for OS.
For other 4 drives set up RAID 5 and use them for SQL data and log.
Hope this helps.
"mp3nomad" wrote:
> I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> controller. I would like to know how I should setup the drives for
> installation of server 2003 std os and sql server 2005. This server is going
> to be a dedicated sql server to serve databases for web applications.
> Thanks and let me know if I should supply any additional info.
> Shelly Campbell|||Thanks! This helps.
"Farmer" wrote:
> This will give you some ideas
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx
> In BOL, index tab, type NTFS, see note.
> SQL Server magazine had a good article on hardware for SQL Server . I can't
> post it as it is for subscribers only
> http://www.windowsitpro.com/SQLServer/Article/ArticleID/46492/46492.html#
> In the nutshell, system = RAID1, logs = RAID1, data = case when cash poor
> then RAID5 else RAID10 end.
>
> Farmer.
> "mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
> news:1923B0A6-06A2-4041-850A-C692718D54AB@.microsoft.com...
> >I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> > controller. I would like to know how I should setup the drives for
> > installation of server 2003 std os and sql server 2005. This server is
> > going
> > to be a dedicated sql server to serve databases for web applications.
> >
> > Thanks and let me know if I should supply any additional info.
> >
> > Shelly Campbell
>
>|||Thanks Mark.
Since we're kind of tight on funds, I'm going to setup the drives as follows:
1 separate 36 gb drive for OS and SQL Server program install.
1 separate 146 gb drive for Tlogs.
3 146 gb disks in RAID 5 array for data.
Does this look acceptable? Not the best, but acceptable?
"mp3nomad" wrote:
> I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> controller. I would like to know how I should setup the drives for
> installation of server 2003 std os and sql server 2005. This server is going
> to be a dedicated sql server to serve databases for web applications.
> Thanks and let me know if I should supply any additional info.
> Shelly Campbell|||NO, this set up is not acceptable.
If you use only one drive for OS, if the drive fails, everything is gone.
Therefore, you must, must, and must use RAID 1 for OS which will involve a
minimum of 2 drives.
Here is what you need to remember:
RAID 1 requires at least 2 drives.
Raid 5 requires at least 3 drives.
If you use only 1 drive as you are mentionining, you can only build RAID 0
on it which gives no protection against data recovery.
I would say based on your server since it's not on SAN, use 2 drives for
RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data on
it. SQL Binaries can say on the same RAID Volume where OS resides.
I hope this helps.
"mp3nomad" wrote:
> Thanks Mark.
> Since we're kind of tight on funds, I'm going to setup the drives as follows:
> 1 separate 36 gb drive for OS and SQL Server program install.
> 1 separate 146 gb drive for Tlogs.
> 3 146 gb disks in RAID 5 array for data.
> Does this look acceptable? Not the best, but acceptable?
> "mp3nomad" wrote:
> > I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> > controller. I would like to know how I should setup the drives for
> > installation of server 2003 std os and sql server 2005. This server is going
> > to be a dedicated sql server to serve databases for web applications.
> >
> > Thanks and let me know if I should supply any additional info.
> >
> > Shelly Campbell|||I see. Ok.
Where would the transaction logs go? On the same RAID volume with the OS?
"Mark" wrote:
> NO, this set up is not acceptable.
> If you use only one drive for OS, if the drive fails, everything is gone.
> Therefore, you must, must, and must use RAID 1 for OS which will involve a
> minimum of 2 drives.
> Here is what you need to remember:
> RAID 1 requires at least 2 drives.
> Raid 5 requires at least 3 drives.
> If you use only 1 drive as you are mentionining, you can only build RAID 0
> on it which gives no protection against data recovery.
> I would say based on your server since it's not on SAN, use 2 drives for
> RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data on
> it. SQL Binaries can say on the same RAID Volume where OS resides.
> I hope this helps.
>
> "mp3nomad" wrote:
> > Thanks Mark.
> >
> > Since we're kind of tight on funds, I'm going to setup the drives as follows:
> >
> > 1 separate 36 gb drive for OS and SQL Server program install.
> > 1 separate 146 gb drive for Tlogs.
> > 3 146 gb disks in RAID 5 array for data.
> >
> > Does this look acceptable? Not the best, but acceptable?
> >
> > "mp3nomad" wrote:
> >
> > > I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> > > controller. I would like to know how I should setup the drives for
> > > installation of server 2003 std os and sql server 2005. This server is going
> > > to be a dedicated sql server to serve databases for web applications.
> > >
> > > Thanks and let me know if I should supply any additional info.
> > >
> > > Shelly Campbell|||currently the server is configured as follows:
4 146 gb drives in RAID 5 container partitioned as C:\ (30 gb) and D:\
(remainder of space)
Windows Server 2003 w/ SP1 installed on C:\ partition
nothing else installed as yet
I'm trying to figure out if this setup is ok and then just add an additional
RAID 1 array for the transaction logs?
I appreciate your assistance.
Shelly
"Mark" wrote:
> NO, this set up is not acceptable.
> If you use only one drive for OS, if the drive fails, everything is gone.
> Therefore, you must, must, and must use RAID 1 for OS which will involve a
> minimum of 2 drives.
> Here is what you need to remember:
> RAID 1 requires at least 2 drives.
> Raid 5 requires at least 3 drives.
> If you use only 1 drive as you are mentionining, you can only build RAID 0
> on it which gives no protection against data recovery.
> I would say based on your server since it's not on SAN, use 2 drives for
> RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data on
> it. SQL Binaries can say on the same RAID Volume where OS resides.
> I hope this helps.
>
> "mp3nomad" wrote:
> > Thanks Mark.
> >
> > Since we're kind of tight on funds, I'm going to setup the drives as follows:
> >
> > 1 separate 36 gb drive for OS and SQL Server program install.
> > 1 separate 146 gb drive for Tlogs.
> > 3 146 gb disks in RAID 5 array for data.
> >
> > Does this look acceptable? Not the best, but acceptable?
> >
> > "mp3nomad" wrote:
> >
> > > I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> > > controller. I would like to know how I should setup the drives for
> > > installation of server 2003 std os and sql server 2005. This server is going
> > > to be a dedicated sql server to serve databases for web applications.
> > >
> > > Thanks and let me know if I should supply any additional info.
> > >
> > > Shelly Campbell|||Do not put log on same drive where the OS is.
"mp3nomad" wrote:
> currently the server is configured as follows:
> 4 146 gb drives in RAID 5 container partitioned as C:\ (30 gb) and D:\
> (remainder of space)
> Windows Server 2003 w/ SP1 installed on C:\ partition
> nothing else installed as yet
> I'm trying to figure out if this setup is ok and then just add an additional
> RAID 1 array for the transaction logs?
> I appreciate your assistance.
> Shelly
> "Mark" wrote:
> > NO, this set up is not acceptable.
> >
> > If you use only one drive for OS, if the drive fails, everything is gone.
> > Therefore, you must, must, and must use RAID 1 for OS which will involve a
> > minimum of 2 drives.
> >
> > Here is what you need to remember:
> > RAID 1 requires at least 2 drives.
> > Raid 5 requires at least 3 drives.
> >
> > If you use only 1 drive as you are mentionining, you can only build RAID 0
> > on it which gives no protection against data recovery.
> >
> > I would say based on your server since it's not on SAN, use 2 drives for
> > RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data on
> > it. SQL Binaries can say on the same RAID Volume where OS resides.
> >
> > I hope this helps.
> >
> >
> >
> > "mp3nomad" wrote:
> >
> > > Thanks Mark.
> > >
> > > Since we're kind of tight on funds, I'm going to setup the drives as follows:
> > >
> > > 1 separate 36 gb drive for OS and SQL Server program install.
> > > 1 separate 146 gb drive for Tlogs.
> > > 3 146 gb disks in RAID 5 array for data.
> > >
> > > Does this look acceptable? Not the best, but acceptable?
> > >
> > > "mp3nomad" wrote:
> > >
> > > > I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> > > > controller. I would like to know how I should setup the drives for
> > > > installation of server 2003 std os and sql server 2005. This server is going
> > > > to be a dedicated sql server to serve databases for web applications.
> > > >
> > > > Thanks and let me know if I should supply any additional info.
> > > >
> > > > Shelly Campbell|||ok. thanks! we're trying to configure this box the best we can based on the
hardware we can afford to purchase.
"Mark" wrote:
> Do not put log on same drive where the OS is.
> "mp3nomad" wrote:
> > currently the server is configured as follows:
> >
> > 4 146 gb drives in RAID 5 container partitioned as C:\ (30 gb) and D:\
> > (remainder of space)
> >
> > Windows Server 2003 w/ SP1 installed on C:\ partition
> >
> > nothing else installed as yet
> >
> > I'm trying to figure out if this setup is ok and then just add an additional
> > RAID 1 array for the transaction logs?
> >
> > I appreciate your assistance.
> >
> > Shelly
> >
> > "Mark" wrote:
> >
> > > NO, this set up is not acceptable.
> > >
> > > If you use only one drive for OS, if the drive fails, everything is gone.
> > > Therefore, you must, must, and must use RAID 1 for OS which will involve a
> > > minimum of 2 drives.
> > >
> > > Here is what you need to remember:
> > > RAID 1 requires at least 2 drives.
> > > Raid 5 requires at least 3 drives.
> > >
> > > If you use only 1 drive as you are mentionining, you can only build RAID 0
> > > on it which gives no protection against data recovery.
> > >
> > > I would say based on your server since it's not on SAN, use 2 drives for
> > > RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data on
> > > it. SQL Binaries can say on the same RAID Volume where OS resides.
> > >
> > > I hope this helps.
> > >
> > >
> > >
> > > "mp3nomad" wrote:
> > >
> > > > Thanks Mark.
> > > >
> > > > Since we're kind of tight on funds, I'm going to setup the drives as follows:
> > > >
> > > > 1 separate 36 gb drive for OS and SQL Server program install.
> > > > 1 separate 146 gb drive for Tlogs.
> > > > 3 146 gb disks in RAID 5 array for data.
> > > >
> > > > Does this look acceptable? Not the best, but acceptable?
> > > >
> > > > "mp3nomad" wrote:
> > > >
> > > > > I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> > > > > controller. I would like to know how I should setup the drives for
> > > > > installation of server 2003 std os and sql server 2005. This server is going
> > > > > to be a dedicated sql server to serve databases for web applications.
> > > > >
> > > > > Thanks and let me know if I should supply any additional info.
> > > > >
> > > > > Shelly Campbell|||Maybe this is a little out of the box on this one...
In the future I would order smaller drives and more of them. Lets say that
you have the 4 of 146s If you have the drive bays how about 10 72's. Raid
this in pairs as RAID 1. This way you have 5 drive letters where you can
table partition your storage for a better performance.
I know this sounds odd. But I have found that smaller faster drives =better read times.
--Chris Shaw
"mp3nomad" wrote:
> ok. thanks! we're trying to configure this box the best we can based on the
> hardware we can afford to purchase.
> "Mark" wrote:
> > Do not put log on same drive where the OS is.
> >
> > "mp3nomad" wrote:
> >
> > > currently the server is configured as follows:
> > >
> > > 4 146 gb drives in RAID 5 container partitioned as C:\ (30 gb) and D:\
> > > (remainder of space)
> > >
> > > Windows Server 2003 w/ SP1 installed on C:\ partition
> > >
> > > nothing else installed as yet
> > >
> > > I'm trying to figure out if this setup is ok and then just add an additional
> > > RAID 1 array for the transaction logs?
> > >
> > > I appreciate your assistance.
> > >
> > > Shelly
> > >
> > > "Mark" wrote:
> > >
> > > > NO, this set up is not acceptable.
> > > >
> > > > If you use only one drive for OS, if the drive fails, everything is gone.
> > > > Therefore, you must, must, and must use RAID 1 for OS which will involve a
> > > > minimum of 2 drives.
> > > >
> > > > Here is what you need to remember:
> > > > RAID 1 requires at least 2 drives.
> > > > Raid 5 requires at least 3 drives.
> > > >
> > > > If you use only 1 drive as you are mentionining, you can only build RAID 0
> > > > on it which gives no protection against data recovery.
> > > >
> > > > I would say based on your server since it's not on SAN, use 2 drives for
> > > > RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data on
> > > > it. SQL Binaries can say on the same RAID Volume where OS resides.
> > > >
> > > > I hope this helps.
> > > >
> > > >
> > > >
> > > > "mp3nomad" wrote:
> > > >
> > > > > Thanks Mark.
> > > > >
> > > > > Since we're kind of tight on funds, I'm going to setup the drives as follows:
> > > > >
> > > > > 1 separate 36 gb drive for OS and SQL Server program install.
> > > > > 1 separate 146 gb drive for Tlogs.
> > > > > 3 146 gb disks in RAID 5 array for data.
> > > > >
> > > > > Does this look acceptable? Not the best, but acceptable?
> > > > >
> > > > > "mp3nomad" wrote:
> > > > >
> > > > > > I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> > > > > > controller. I would like to know how I should setup the drives for
> > > > > > installation of server 2003 std os and sql server 2005. This server is going
> > > > > > to be a dedicated sql server to serve databases for web applications.
> > > > > >
> > > > > > Thanks and let me know if I should supply any additional info.
> > > > > >
> > > > > > Shelly Campbell|||Chris Shaw wrote:
> Maybe this is a little out of the box on this one...
> In the future I would order smaller drives and more of them. Lets say that
> you have the 4 of 146s If you have the drive bays how about 10 72's. Raid
> this in pairs as RAID 1. This way you have 5 drive letters where you can
> table partition your storage for a better performance.
> I know this sounds odd. But I have found that smaller faster drives => better read times.
> --Chris Shaw
>
It's not the drive size as such that makes it faster but the number of
heads available for operation. Having a RAID with 10 146GB disks might
not be slower than a RAID with 10 72 GB disk. Of course there can be
various differences in design (number of plates etc.) but I think that
will makes a difference that is mostly theoretically.
The general rule of thumb is that you should get as many and as fast
disks as possible.
To get back on topic, I agree that a SQL server with only 4 disks is a
no Win situation in many cases. Another possibility could be to setup 2
RAID 1 arrays, but no matter how you configure it with OS, logfiles and
datafiles, you won't have the optimal setup.
In your case where you need it for a web application, it might be ok
though, but it depends on your need of recovery and availability on the
web server. We have a SQL server that host the database for one of our
web applications and here we restore a new database each night. The
database is also only used for read operations. On this server we run a
RAID1 for OS and a RAID 5 that contains both logfiles and datafiles. I'd
never do this on a server with critical data, but in this case where we
just can restore a new database it doesn't matter. Maybe your setup is
something similar and in that case I think it could be justified to put
log and data files on the same RAID.
Regards
Steen|||thanks for your support!
"mp3nomad" wrote:
> ok. thanks! we're trying to configure this box the best we can based on the
> hardware we can afford to purchase.
> "Mark" wrote:
> > Do not put log on same drive where the OS is.
> >
> > "mp3nomad" wrote:
> >
> > > currently the server is configured as follows:
> > >
> > > 4 146 gb drives in RAID 5 container partitioned as C:\ (30 gb) and D:\
> > > (remainder of space)
> > >
> > > Windows Server 2003 w/ SP1 installed on C:\ partition
> > >
> > > nothing else installed as yet
> > >
> > > I'm trying to figure out if this setup is ok and then just add an additional
> > > RAID 1 array for the transaction logs?
> > >
> > > I appreciate your assistance.
> > >
> > > Shelly
> > >
> > > "Mark" wrote:
> > >
> > > > NO, this set up is not acceptable.
> > > >
> > > > If you use only one drive for OS, if the drive fails, everything is gone.
> > > > Therefore, you must, must, and must use RAID 1 for OS which will involve a
> > > > minimum of 2 drives.
> > > >
> > > > Here is what you need to remember:
> > > > RAID 1 requires at least 2 drives.
> > > > Raid 5 requires at least 3 drives.
> > > >
> > > > If you use only 1 drive as you are mentionining, you can only build RAID 0
> > > > on it which gives no protection against data recovery.
> > > >
> > > > I would say based on your server since it's not on SAN, use 2 drives for
> > > > RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data on
> > > > it. SQL Binaries can say on the same RAID Volume where OS resides.
> > > >
> > > > I hope this helps.
> > > >
> > > >
> > > >
> > > > "mp3nomad" wrote:
> > > >
> > > > > Thanks Mark.
> > > > >
> > > > > Since we're kind of tight on funds, I'm going to setup the drives as follows:
> > > > >
> > > > > 1 separate 36 gb drive for OS and SQL Server program install.
> > > > > 1 separate 146 gb drive for Tlogs.
> > > > > 3 146 gb disks in RAID 5 array for data.
> > > > >
> > > > > Does this look acceptable? Not the best, but acceptable?
> > > > >
> > > > > "mp3nomad" wrote:
> > > > >
> > > > > > I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> > > > > > controller. I would like to know how I should setup the drives for
> > > > > > installation of server 2003 std os and sql server 2005. This server is going
> > > > > > to be a dedicated sql server to serve databases for web applications.
> > > > > >
> > > > > > Thanks and let me know if I should supply any additional info.
> > > > > >
> > > > > > Shelly Campbell
controller. I would like to know how I should setup the drives for
installation of server 2003 std os and sql server 2005. This server is going
to be a dedicated sql server to serve databases for web applications.
Thanks and let me know if I should supply any additional info.
Shelly CampbellThis will give you some ideas
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx
In BOL, index tab, type NTFS, see note.
SQL Server magazine had a good article on hardware for SQL Server . I can't
post it as it is for subscribers only
http://www.windowsitpro.com/SQLServer/Article/ArticleID/46492/46492.html#
In the nutshell, system = RAID1, logs = RAID1, data = case when cash poor
then RAID5 else RAID10 end.
Farmer.
"mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
news:1923B0A6-06A2-4041-850A-C692718D54AB@.microsoft.com...
>I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> controller. I would like to know how I should setup the drives for
> installation of server 2003 std os and sql server 2005. This server is
> going
> to be a dedicated sql server to serve databases for web applications.
> Thanks and let me know if I should supply any additional info.
> Shelly Campbell|||Hi Shelly:
OS should always stay on RAID 1. No Exceptions should be made here.
For SQL Data, You can use RAID 5 if you wish to though RAID 5 is not the
best solution. For Faster Read\Write RAID 10 is the best solution but then it
has a cost attached to its performance.
Since you have 4x146 GB Drives on the machine, I would recommend that buy
another couple of drives identical in size and host RAID 1 on it and use it
for OS.
For other 4 drives set up RAID 5 and use them for SQL data and log.
Hope this helps.
"mp3nomad" wrote:
> I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> controller. I would like to know how I should setup the drives for
> installation of server 2003 std os and sql server 2005. This server is going
> to be a dedicated sql server to serve databases for web applications.
> Thanks and let me know if I should supply any additional info.
> Shelly Campbell|||Thanks! This helps.
"Farmer" wrote:
> This will give you some ideas
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx
> In BOL, index tab, type NTFS, see note.
> SQL Server magazine had a good article on hardware for SQL Server . I can't
> post it as it is for subscribers only
> http://www.windowsitpro.com/SQLServer/Article/ArticleID/46492/46492.html#
> In the nutshell, system = RAID1, logs = RAID1, data = case when cash poor
> then RAID5 else RAID10 end.
>
> Farmer.
> "mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
> news:1923B0A6-06A2-4041-850A-C692718D54AB@.microsoft.com...
> >I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> > controller. I would like to know how I should setup the drives for
> > installation of server 2003 std os and sql server 2005. This server is
> > going
> > to be a dedicated sql server to serve databases for web applications.
> >
> > Thanks and let me know if I should supply any additional info.
> >
> > Shelly Campbell
>
>|||Thanks Mark.
Since we're kind of tight on funds, I'm going to setup the drives as follows:
1 separate 36 gb drive for OS and SQL Server program install.
1 separate 146 gb drive for Tlogs.
3 146 gb disks in RAID 5 array for data.
Does this look acceptable? Not the best, but acceptable?
"mp3nomad" wrote:
> I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> controller. I would like to know how I should setup the drives for
> installation of server 2003 std os and sql server 2005. This server is going
> to be a dedicated sql server to serve databases for web applications.
> Thanks and let me know if I should supply any additional info.
> Shelly Campbell|||NO, this set up is not acceptable.
If you use only one drive for OS, if the drive fails, everything is gone.
Therefore, you must, must, and must use RAID 1 for OS which will involve a
minimum of 2 drives.
Here is what you need to remember:
RAID 1 requires at least 2 drives.
Raid 5 requires at least 3 drives.
If you use only 1 drive as you are mentionining, you can only build RAID 0
on it which gives no protection against data recovery.
I would say based on your server since it's not on SAN, use 2 drives for
RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data on
it. SQL Binaries can say on the same RAID Volume where OS resides.
I hope this helps.
"mp3nomad" wrote:
> Thanks Mark.
> Since we're kind of tight on funds, I'm going to setup the drives as follows:
> 1 separate 36 gb drive for OS and SQL Server program install.
> 1 separate 146 gb drive for Tlogs.
> 3 146 gb disks in RAID 5 array for data.
> Does this look acceptable? Not the best, but acceptable?
> "mp3nomad" wrote:
> > I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> > controller. I would like to know how I should setup the drives for
> > installation of server 2003 std os and sql server 2005. This server is going
> > to be a dedicated sql server to serve databases for web applications.
> >
> > Thanks and let me know if I should supply any additional info.
> >
> > Shelly Campbell|||I see. Ok.
Where would the transaction logs go? On the same RAID volume with the OS?
"Mark" wrote:
> NO, this set up is not acceptable.
> If you use only one drive for OS, if the drive fails, everything is gone.
> Therefore, you must, must, and must use RAID 1 for OS which will involve a
> minimum of 2 drives.
> Here is what you need to remember:
> RAID 1 requires at least 2 drives.
> Raid 5 requires at least 3 drives.
> If you use only 1 drive as you are mentionining, you can only build RAID 0
> on it which gives no protection against data recovery.
> I would say based on your server since it's not on SAN, use 2 drives for
> RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data on
> it. SQL Binaries can say on the same RAID Volume where OS resides.
> I hope this helps.
>
> "mp3nomad" wrote:
> > Thanks Mark.
> >
> > Since we're kind of tight on funds, I'm going to setup the drives as follows:
> >
> > 1 separate 36 gb drive for OS and SQL Server program install.
> > 1 separate 146 gb drive for Tlogs.
> > 3 146 gb disks in RAID 5 array for data.
> >
> > Does this look acceptable? Not the best, but acceptable?
> >
> > "mp3nomad" wrote:
> >
> > > I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> > > controller. I would like to know how I should setup the drives for
> > > installation of server 2003 std os and sql server 2005. This server is going
> > > to be a dedicated sql server to serve databases for web applications.
> > >
> > > Thanks and let me know if I should supply any additional info.
> > >
> > > Shelly Campbell|||currently the server is configured as follows:
4 146 gb drives in RAID 5 container partitioned as C:\ (30 gb) and D:\
(remainder of space)
Windows Server 2003 w/ SP1 installed on C:\ partition
nothing else installed as yet
I'm trying to figure out if this setup is ok and then just add an additional
RAID 1 array for the transaction logs?
I appreciate your assistance.
Shelly
"Mark" wrote:
> NO, this set up is not acceptable.
> If you use only one drive for OS, if the drive fails, everything is gone.
> Therefore, you must, must, and must use RAID 1 for OS which will involve a
> minimum of 2 drives.
> Here is what you need to remember:
> RAID 1 requires at least 2 drives.
> Raid 5 requires at least 3 drives.
> If you use only 1 drive as you are mentionining, you can only build RAID 0
> on it which gives no protection against data recovery.
> I would say based on your server since it's not on SAN, use 2 drives for
> RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data on
> it. SQL Binaries can say on the same RAID Volume where OS resides.
> I hope this helps.
>
> "mp3nomad" wrote:
> > Thanks Mark.
> >
> > Since we're kind of tight on funds, I'm going to setup the drives as follows:
> >
> > 1 separate 36 gb drive for OS and SQL Server program install.
> > 1 separate 146 gb drive for Tlogs.
> > 3 146 gb disks in RAID 5 array for data.
> >
> > Does this look acceptable? Not the best, but acceptable?
> >
> > "mp3nomad" wrote:
> >
> > > I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> > > controller. I would like to know how I should setup the drives for
> > > installation of server 2003 std os and sql server 2005. This server is going
> > > to be a dedicated sql server to serve databases for web applications.
> > >
> > > Thanks and let me know if I should supply any additional info.
> > >
> > > Shelly Campbell|||Do not put log on same drive where the OS is.
"mp3nomad" wrote:
> currently the server is configured as follows:
> 4 146 gb drives in RAID 5 container partitioned as C:\ (30 gb) and D:\
> (remainder of space)
> Windows Server 2003 w/ SP1 installed on C:\ partition
> nothing else installed as yet
> I'm trying to figure out if this setup is ok and then just add an additional
> RAID 1 array for the transaction logs?
> I appreciate your assistance.
> Shelly
> "Mark" wrote:
> > NO, this set up is not acceptable.
> >
> > If you use only one drive for OS, if the drive fails, everything is gone.
> > Therefore, you must, must, and must use RAID 1 for OS which will involve a
> > minimum of 2 drives.
> >
> > Here is what you need to remember:
> > RAID 1 requires at least 2 drives.
> > Raid 5 requires at least 3 drives.
> >
> > If you use only 1 drive as you are mentionining, you can only build RAID 0
> > on it which gives no protection against data recovery.
> >
> > I would say based on your server since it's not on SAN, use 2 drives for
> > RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data on
> > it. SQL Binaries can say on the same RAID Volume where OS resides.
> >
> > I hope this helps.
> >
> >
> >
> > "mp3nomad" wrote:
> >
> > > Thanks Mark.
> > >
> > > Since we're kind of tight on funds, I'm going to setup the drives as follows:
> > >
> > > 1 separate 36 gb drive for OS and SQL Server program install.
> > > 1 separate 146 gb drive for Tlogs.
> > > 3 146 gb disks in RAID 5 array for data.
> > >
> > > Does this look acceptable? Not the best, but acceptable?
> > >
> > > "mp3nomad" wrote:
> > >
> > > > I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> > > > controller. I would like to know how I should setup the drives for
> > > > installation of server 2003 std os and sql server 2005. This server is going
> > > > to be a dedicated sql server to serve databases for web applications.
> > > >
> > > > Thanks and let me know if I should supply any additional info.
> > > >
> > > > Shelly Campbell|||ok. thanks! we're trying to configure this box the best we can based on the
hardware we can afford to purchase.
"Mark" wrote:
> Do not put log on same drive where the OS is.
> "mp3nomad" wrote:
> > currently the server is configured as follows:
> >
> > 4 146 gb drives in RAID 5 container partitioned as C:\ (30 gb) and D:\
> > (remainder of space)
> >
> > Windows Server 2003 w/ SP1 installed on C:\ partition
> >
> > nothing else installed as yet
> >
> > I'm trying to figure out if this setup is ok and then just add an additional
> > RAID 1 array for the transaction logs?
> >
> > I appreciate your assistance.
> >
> > Shelly
> >
> > "Mark" wrote:
> >
> > > NO, this set up is not acceptable.
> > >
> > > If you use only one drive for OS, if the drive fails, everything is gone.
> > > Therefore, you must, must, and must use RAID 1 for OS which will involve a
> > > minimum of 2 drives.
> > >
> > > Here is what you need to remember:
> > > RAID 1 requires at least 2 drives.
> > > Raid 5 requires at least 3 drives.
> > >
> > > If you use only 1 drive as you are mentionining, you can only build RAID 0
> > > on it which gives no protection against data recovery.
> > >
> > > I would say based on your server since it's not on SAN, use 2 drives for
> > > RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data on
> > > it. SQL Binaries can say on the same RAID Volume where OS resides.
> > >
> > > I hope this helps.
> > >
> > >
> > >
> > > "mp3nomad" wrote:
> > >
> > > > Thanks Mark.
> > > >
> > > > Since we're kind of tight on funds, I'm going to setup the drives as follows:
> > > >
> > > > 1 separate 36 gb drive for OS and SQL Server program install.
> > > > 1 separate 146 gb drive for Tlogs.
> > > > 3 146 gb disks in RAID 5 array for data.
> > > >
> > > > Does this look acceptable? Not the best, but acceptable?
> > > >
> > > > "mp3nomad" wrote:
> > > >
> > > > > I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> > > > > controller. I would like to know how I should setup the drives for
> > > > > installation of server 2003 std os and sql server 2005. This server is going
> > > > > to be a dedicated sql server to serve databases for web applications.
> > > > >
> > > > > Thanks and let me know if I should supply any additional info.
> > > > >
> > > > > Shelly Campbell|||Maybe this is a little out of the box on this one...
In the future I would order smaller drives and more of them. Lets say that
you have the 4 of 146s If you have the drive bays how about 10 72's. Raid
this in pairs as RAID 1. This way you have 5 drive letters where you can
table partition your storage for a better performance.
I know this sounds odd. But I have found that smaller faster drives =better read times.
--Chris Shaw
"mp3nomad" wrote:
> ok. thanks! we're trying to configure this box the best we can based on the
> hardware we can afford to purchase.
> "Mark" wrote:
> > Do not put log on same drive where the OS is.
> >
> > "mp3nomad" wrote:
> >
> > > currently the server is configured as follows:
> > >
> > > 4 146 gb drives in RAID 5 container partitioned as C:\ (30 gb) and D:\
> > > (remainder of space)
> > >
> > > Windows Server 2003 w/ SP1 installed on C:\ partition
> > >
> > > nothing else installed as yet
> > >
> > > I'm trying to figure out if this setup is ok and then just add an additional
> > > RAID 1 array for the transaction logs?
> > >
> > > I appreciate your assistance.
> > >
> > > Shelly
> > >
> > > "Mark" wrote:
> > >
> > > > NO, this set up is not acceptable.
> > > >
> > > > If you use only one drive for OS, if the drive fails, everything is gone.
> > > > Therefore, you must, must, and must use RAID 1 for OS which will involve a
> > > > minimum of 2 drives.
> > > >
> > > > Here is what you need to remember:
> > > > RAID 1 requires at least 2 drives.
> > > > Raid 5 requires at least 3 drives.
> > > >
> > > > If you use only 1 drive as you are mentionining, you can only build RAID 0
> > > > on it which gives no protection against data recovery.
> > > >
> > > > I would say based on your server since it's not on SAN, use 2 drives for
> > > > RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data on
> > > > it. SQL Binaries can say on the same RAID Volume where OS resides.
> > > >
> > > > I hope this helps.
> > > >
> > > >
> > > >
> > > > "mp3nomad" wrote:
> > > >
> > > > > Thanks Mark.
> > > > >
> > > > > Since we're kind of tight on funds, I'm going to setup the drives as follows:
> > > > >
> > > > > 1 separate 36 gb drive for OS and SQL Server program install.
> > > > > 1 separate 146 gb drive for Tlogs.
> > > > > 3 146 gb disks in RAID 5 array for data.
> > > > >
> > > > > Does this look acceptable? Not the best, but acceptable?
> > > > >
> > > > > "mp3nomad" wrote:
> > > > >
> > > > > > I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> > > > > > controller. I would like to know how I should setup the drives for
> > > > > > installation of server 2003 std os and sql server 2005. This server is going
> > > > > > to be a dedicated sql server to serve databases for web applications.
> > > > > >
> > > > > > Thanks and let me know if I should supply any additional info.
> > > > > >
> > > > > > Shelly Campbell|||Chris Shaw wrote:
> Maybe this is a little out of the box on this one...
> In the future I would order smaller drives and more of them. Lets say that
> you have the 4 of 146s If you have the drive bays how about 10 72's. Raid
> this in pairs as RAID 1. This way you have 5 drive letters where you can
> table partition your storage for a better performance.
> I know this sounds odd. But I have found that smaller faster drives => better read times.
> --Chris Shaw
>
It's not the drive size as such that makes it faster but the number of
heads available for operation. Having a RAID with 10 146GB disks might
not be slower than a RAID with 10 72 GB disk. Of course there can be
various differences in design (number of plates etc.) but I think that
will makes a difference that is mostly theoretically.
The general rule of thumb is that you should get as many and as fast
disks as possible.
To get back on topic, I agree that a SQL server with only 4 disks is a
no Win situation in many cases. Another possibility could be to setup 2
RAID 1 arrays, but no matter how you configure it with OS, logfiles and
datafiles, you won't have the optimal setup.
In your case where you need it for a web application, it might be ok
though, but it depends on your need of recovery and availability on the
web server. We have a SQL server that host the database for one of our
web applications and here we restore a new database each night. The
database is also only used for read operations. On this server we run a
RAID1 for OS and a RAID 5 that contains both logfiles and datafiles. I'd
never do this on a server with critical data, but in this case where we
just can restore a new database it doesn't matter. Maybe your setup is
something similar and in that case I think it could be justified to put
log and data files on the same RAID.
Regards
Steen|||thanks for your support!
"mp3nomad" wrote:
> ok. thanks! we're trying to configure this box the best we can based on the
> hardware we can afford to purchase.
> "Mark" wrote:
> > Do not put log on same drive where the OS is.
> >
> > "mp3nomad" wrote:
> >
> > > currently the server is configured as follows:
> > >
> > > 4 146 gb drives in RAID 5 container partitioned as C:\ (30 gb) and D:\
> > > (remainder of space)
> > >
> > > Windows Server 2003 w/ SP1 installed on C:\ partition
> > >
> > > nothing else installed as yet
> > >
> > > I'm trying to figure out if this setup is ok and then just add an additional
> > > RAID 1 array for the transaction logs?
> > >
> > > I appreciate your assistance.
> > >
> > > Shelly
> > >
> > > "Mark" wrote:
> > >
> > > > NO, this set up is not acceptable.
> > > >
> > > > If you use only one drive for OS, if the drive fails, everything is gone.
> > > > Therefore, you must, must, and must use RAID 1 for OS which will involve a
> > > > minimum of 2 drives.
> > > >
> > > > Here is what you need to remember:
> > > > RAID 1 requires at least 2 drives.
> > > > Raid 5 requires at least 3 drives.
> > > >
> > > > If you use only 1 drive as you are mentionining, you can only build RAID 0
> > > > on it which gives no protection against data recovery.
> > > >
> > > > I would say based on your server since it's not on SAN, use 2 drives for
> > > > RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data on
> > > > it. SQL Binaries can say on the same RAID Volume where OS resides.
> > > >
> > > > I hope this helps.
> > > >
> > > >
> > > >
> > > > "mp3nomad" wrote:
> > > >
> > > > > Thanks Mark.
> > > > >
> > > > > Since we're kind of tight on funds, I'm going to setup the drives as follows:
> > > > >
> > > > > 1 separate 36 gb drive for OS and SQL Server program install.
> > > > > 1 separate 146 gb drive for Tlogs.
> > > > > 3 146 gb disks in RAID 5 array for data.
> > > > >
> > > > > Does this look acceptable? Not the best, but acceptable?
> > > > >
> > > > > "mp3nomad" wrote:
> > > > >
> > > > > > I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> > > > > > controller. I would like to know how I should setup the drives for
> > > > > > installation of server 2003 std os and sql server 2005. This server is going
> > > > > > to be a dedicated sql server to serve databases for web applications.
> > > > > >
> > > > > > Thanks and let me know if I should supply any additional info.
> > > > > >
> > > > > > Shelly Campbell
drive configuration on new sql server 2005 box?
I have a new dell 2850 server with 4 146gb drives attached to a raid 5
controller. I would like to know how I should setup the drives for
installation of server 2003 std os and sql server 2005. This server is going
to be a dedicated sql server to serve databases for web applications.
Thanks and let me know if I should supply any additional info.
Shelly CampbellThis will give you some ideas
http://www.microsoft.com/technet/pr...in/sqlops6.mspx
In BOL, index tab, type NTFS, see note.
SQL Server magazine had a good article on hardware for SQL Server . I can't
post it as it is for subscribers only
http://www.windowsitpro.com/SQLServ...492/46492.html#
In the nutshell, system = RAID1, logs = RAID1, data = case when cash poor
then RAID5 else RAID10 end.
Farmer.
"mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
news:1923B0A6-06A2-4041-850A-C692718D54AB@.microsoft.com...
>I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> controller. I would like to know how I should setup the drives for
> installation of server 2003 std os and sql server 2005. This server is
> going
> to be a dedicated sql server to serve databases for web applications.
> Thanks and let me know if I should supply any additional info.
> Shelly Campbell|||Hi Shelly:
OS should always stay on RAID 1. No Exceptions should be made here.
For SQL Data, You can use RAID 5 if you wish to though RAID 5 is not the
best solution. For Faster Read\Write RAID 10 is the best solution but then i
t
has a cost attached to its performance.
Since you have 4x146 GB Drives on the machine, I would recommend that buy
another couple of drives identical in size and host RAID 1 on it and use it
for OS.
For other 4 drives set up RAID 5 and use them for SQL data and log.
Hope this helps.
"mp3nomad" wrote:
> I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> controller. I would like to know how I should setup the drives for
> installation of server 2003 std os and sql server 2005. This server is goi
ng
> to be a dedicated sql server to serve databases for web applications.
> Thanks and let me know if I should supply any additional info.
> Shelly Campbell|||Thanks! This helps.
"Farmer" wrote:
> This will give you some ideas
> hardware for SQL Server . I can'
t
> post it as it is for subscribers only
> [url]http://www.windowsitpro.com/SQLServer/Article/ArticleID/46492/46492.html#" target="_blank">http://www.microsoft.com/technet/pr...492/46492.html#
> In the nutshell, system = RAID1, logs = RAID1, data = case when cash poor
> then RAID5 else RAID10 end.
>
> Farmer.
> "mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
> news:1923B0A6-06A2-4041-850A-C692718D54AB@.microsoft.com...
>
>|||Thanks Mark.
Since we're kind of tight on funds, I'm going to setup the drives as follows
:
1 separate 36 gb drive for OS and SQL Server program install.
1 separate 146 gb drive for Tlogs.
3 146 gb disks in RAID 5 array for data.
Does this look acceptable? Not the best, but acceptable?
"mp3nomad" wrote:
> I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> controller. I would like to know how I should setup the drives for
> installation of server 2003 std os and sql server 2005. This server is goi
ng
> to be a dedicated sql server to serve databases for web applications.
> Thanks and let me know if I should supply any additional info.
> Shelly Campbell|||NO, this set up is not acceptable.
If you use only one drive for OS, if the drive fails, everything is gone.
Therefore, you must, must, and must use RAID 1 for OS which will involve a
minimum of 2 drives.
Here is what you need to remember:
RAID 1 requires at least 2 drives.
Raid 5 requires at least 3 drives.
If you use only 1 drive as you are mentionining, you can only build RAID 0
on it which gives no protection against data recovery.
I would say based on your server since it's not on SAN, use 2 drives for
RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data o
n
it. SQL Binaries can say on the same RAID Volume where OS resides.
I hope this helps.
"mp3nomad" wrote:
[vbcol=seagreen]
> Thanks Mark.
> Since we're kind of tight on funds, I'm going to setup the drives as follo
ws:
> 1 separate 36 gb drive for OS and SQL Server program install.
> 1 separate 146 gb drive for Tlogs.
> 3 146 gb disks in RAID 5 array for data.
> Does this look acceptable? Not the best, but acceptable?
> "mp3nomad" wrote:
>|||I see. Ok.
Where would the transaction logs go? On the same RAID volume with the OS?
"Mark" wrote:
[vbcol=seagreen]
> NO, this set up is not acceptable.
> If you use only one drive for OS, if the drive fails, everything is gone.
> Therefore, you must, must, and must use RAID 1 for OS which will involve a
> minimum of 2 drives.
> Here is what you need to remember:
> RAID 1 requires at least 2 drives.
> Raid 5 requires at least 3 drives.
> If you use only 1 drive as you are mentionining, you can only build RAID 0
> on it which gives no protection against data recovery.
> I would say based on your server since it's not on SAN, use 2 drives for
> RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data
on
> it. SQL Binaries can say on the same RAID Volume where OS resides.
> I hope this helps.
>
> "mp3nomad" wrote:
>|||currently the server is configured as follows:
4 146 gb drives in RAID 5 container partitioned as C:\ (30 gb) and D:\
(remainder of space)
Windows Server 2003 w/ SP1 installed on C:\ partition
nothing else installed as yet
I'm trying to figure out if this setup is ok and then just add an additional
RAID 1 array for the transaction logs?
I appreciate your assistance.
Shelly
"Mark" wrote:
[vbcol=seagreen]
> NO, this set up is not acceptable.
> If you use only one drive for OS, if the drive fails, everything is gone.
> Therefore, you must, must, and must use RAID 1 for OS which will involve a
> minimum of 2 drives.
> Here is what you need to remember:
> RAID 1 requires at least 2 drives.
> Raid 5 requires at least 3 drives.
> If you use only 1 drive as you are mentionining, you can only build RAID 0
> on it which gives no protection against data recovery.
> I would say based on your server since it's not on SAN, use 2 drives for
> RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data
on
> it. SQL Binaries can say on the same RAID Volume where OS resides.
> I hope this helps.
>
> "mp3nomad" wrote:
>|||Do not put log on same drive where the OS is.
"mp3nomad" wrote:
[vbcol=seagreen]
> currently the server is configured as follows:
> 4 146 gb drives in RAID 5 container partitioned as C:\ (30 gb) and D:\
> (remainder of space)
> Windows Server 2003 w/ SP1 installed on C:\ partition
> nothing else installed as yet
> I'm trying to figure out if this setup is ok and then just add an addition
al
> RAID 1 array for the transaction logs?
> I appreciate your assistance.
> Shelly
> "Mark" wrote:
>|||ok. thanks! we're trying to configure this box the best we can based on the
hardware we can afford to purchase.
"Mark" wrote:
[vbcol=seagreen]
> Do not put log on same drive where the OS is.
> "mp3nomad" wrote:
>
controller. I would like to know how I should setup the drives for
installation of server 2003 std os and sql server 2005. This server is going
to be a dedicated sql server to serve databases for web applications.
Thanks and let me know if I should supply any additional info.
Shelly CampbellThis will give you some ideas
http://www.microsoft.com/technet/pr...in/sqlops6.mspx
In BOL, index tab, type NTFS, see note.
SQL Server magazine had a good article on hardware for SQL Server . I can't
post it as it is for subscribers only
http://www.windowsitpro.com/SQLServ...492/46492.html#
In the nutshell, system = RAID1, logs = RAID1, data = case when cash poor
then RAID5 else RAID10 end.
Farmer.
"mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
news:1923B0A6-06A2-4041-850A-C692718D54AB@.microsoft.com...
>I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> controller. I would like to know how I should setup the drives for
> installation of server 2003 std os and sql server 2005. This server is
> going
> to be a dedicated sql server to serve databases for web applications.
> Thanks and let me know if I should supply any additional info.
> Shelly Campbell|||Hi Shelly:
OS should always stay on RAID 1. No Exceptions should be made here.
For SQL Data, You can use RAID 5 if you wish to though RAID 5 is not the
best solution. For Faster Read\Write RAID 10 is the best solution but then i
t
has a cost attached to its performance.
Since you have 4x146 GB Drives on the machine, I would recommend that buy
another couple of drives identical in size and host RAID 1 on it and use it
for OS.
For other 4 drives set up RAID 5 and use them for SQL data and log.
Hope this helps.
"mp3nomad" wrote:
> I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> controller. I would like to know how I should setup the drives for
> installation of server 2003 std os and sql server 2005. This server is goi
ng
> to be a dedicated sql server to serve databases for web applications.
> Thanks and let me know if I should supply any additional info.
> Shelly Campbell|||Thanks! This helps.
"Farmer" wrote:
> This will give you some ideas
> hardware for SQL Server . I can'
t
> post it as it is for subscribers only
> [url]http://www.windowsitpro.com/SQLServer/Article/ArticleID/46492/46492.html#" target="_blank">http://www.microsoft.com/technet/pr...492/46492.html#
> In the nutshell, system = RAID1, logs = RAID1, data = case when cash poor
> then RAID5 else RAID10 end.
>
> Farmer.
> "mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
> news:1923B0A6-06A2-4041-850A-C692718D54AB@.microsoft.com...
>
>|||Thanks Mark.
Since we're kind of tight on funds, I'm going to setup the drives as follows
:
1 separate 36 gb drive for OS and SQL Server program install.
1 separate 146 gb drive for Tlogs.
3 146 gb disks in RAID 5 array for data.
Does this look acceptable? Not the best, but acceptable?
"mp3nomad" wrote:
> I have a new dell 2850 server with 4 146gb drives attached to a raid 5
> controller. I would like to know how I should setup the drives for
> installation of server 2003 std os and sql server 2005. This server is goi
ng
> to be a dedicated sql server to serve databases for web applications.
> Thanks and let me know if I should supply any additional info.
> Shelly Campbell|||NO, this set up is not acceptable.
If you use only one drive for OS, if the drive fails, everything is gone.
Therefore, you must, must, and must use RAID 1 for OS which will involve a
minimum of 2 drives.
Here is what you need to remember:
RAID 1 requires at least 2 drives.
Raid 5 requires at least 3 drives.
If you use only 1 drive as you are mentionining, you can only build RAID 0
on it which gives no protection against data recovery.
I would say based on your server since it's not on SAN, use 2 drives for
RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data o
n
it. SQL Binaries can say on the same RAID Volume where OS resides.
I hope this helps.
"mp3nomad" wrote:
[vbcol=seagreen]
> Thanks Mark.
> Since we're kind of tight on funds, I'm going to setup the drives as follo
ws:
> 1 separate 36 gb drive for OS and SQL Server program install.
> 1 separate 146 gb drive for Tlogs.
> 3 146 gb disks in RAID 5 array for data.
> Does this look acceptable? Not the best, but acceptable?
> "mp3nomad" wrote:
>|||I see. Ok.
Where would the transaction logs go? On the same RAID volume with the OS?
"Mark" wrote:
[vbcol=seagreen]
> NO, this set up is not acceptable.
> If you use only one drive for OS, if the drive fails, everything is gone.
> Therefore, you must, must, and must use RAID 1 for OS which will involve a
> minimum of 2 drives.
> Here is what you need to remember:
> RAID 1 requires at least 2 drives.
> Raid 5 requires at least 3 drives.
> If you use only 1 drive as you are mentionining, you can only build RAID 0
> on it which gives no protection against data recovery.
> I would say based on your server since it's not on SAN, use 2 drives for
> RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data
on
> it. SQL Binaries can say on the same RAID Volume where OS resides.
> I hope this helps.
>
> "mp3nomad" wrote:
>|||currently the server is configured as follows:
4 146 gb drives in RAID 5 container partitioned as C:\ (30 gb) and D:\
(remainder of space)
Windows Server 2003 w/ SP1 installed on C:\ partition
nothing else installed as yet
I'm trying to figure out if this setup is ok and then just add an additional
RAID 1 array for the transaction logs?
I appreciate your assistance.
Shelly
"Mark" wrote:
[vbcol=seagreen]
> NO, this set up is not acceptable.
> If you use only one drive for OS, if the drive fails, everything is gone.
> Therefore, you must, must, and must use RAID 1 for OS which will involve a
> minimum of 2 drives.
> Here is what you need to remember:
> RAID 1 requires at least 2 drives.
> Raid 5 requires at least 3 drives.
> If you use only 1 drive as you are mentionining, you can only build RAID 0
> on it which gives no protection against data recovery.
> I would say based on your server since it's not on SAN, use 2 drives for
> RAID 1 and set up OS on it and use 4 drives for RAID 5 and set up SQL Data
on
> it. SQL Binaries can say on the same RAID Volume where OS resides.
> I hope this helps.
>
> "mp3nomad" wrote:
>|||Do not put log on same drive where the OS is.
"mp3nomad" wrote:
[vbcol=seagreen]
> currently the server is configured as follows:
> 4 146 gb drives in RAID 5 container partitioned as C:\ (30 gb) and D:\
> (remainder of space)
> Windows Server 2003 w/ SP1 installed on C:\ partition
> nothing else installed as yet
> I'm trying to figure out if this setup is ok and then just add an addition
al
> RAID 1 array for the transaction logs?
> I appreciate your assistance.
> Shelly
> "Mark" wrote:
>|||ok. thanks! we're trying to configure this box the best we can based on the
hardware we can afford to purchase.
"Mark" wrote:
[vbcol=seagreen]
> Do not put log on same drive where the OS is.
> "mp3nomad" wrote:
>
Tuesday, February 14, 2012
Drill Down Reports Permissions Problem
Hi,
I've successfully setup the reports server 2005, on Windows Server 2003, and
had the permissions for the specific users configured to enable access to
the report. When a user logs into the report server, they can access the
reports I've created and deployed.
The problem arises when they try to drill down to the next level of the
report. I've created a report that is a matrix/crosstab report in the report
builder and the user can see the default (top level) of it without a problem.
However, as soon as they click on a record to drill down and get details on
it, they get an rsAccessDenied error that DOMAIN\User does not have
permissions.
As a test, I gave a specific user full content-manager rights (essentially I
tried everything short of giving them local admin rights to the box) and they
still can't run the drill down.
I'm wondering if they need report builder installed to generate the drill
down on the fly? We are using IWA on the domain to access the report server
itself. I thought I had the permissions set up fine, since they can run the
report at first, and I haven't found any options to allow/disallow drill
downs.
Any suggestions to try would be much appreciated. Thanks in advance for the
help.
-PeteHello Pete,
I would like to know this issue more clearly.
1. Did you mean you create the report in the report builder?
2. Have you provided proper permission on the Report Model you use in the
report builder?
3. Did this issue appeared on all the report you developed in report
builder?
4. Have you applied the latest services pack of SQL Server?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Wei,
Thanks for the quick response. I'll answer your questions in-lin below, and
I appreciate any help you can give.
1. Did you mean you create the report in the report builder?
Yes, I created the data source, and data model using the report manager, and
then created the report using report builder. I did all of this as the local
admin on the box on which reporting services was running, and had no
permissions problems (as I was in the default group on RS). The user was on
an external WinXP bx, without report builder loaded when trying to access the
report. They were manually added in as a Content Manager User on Report
Server, at the top level (which I believe gives them rights to all the lower
levels like th report and model.)
2. Have you provided proper permission on the Report Model you use in the
report builder?
Yes, I did. They are a Content Manager role user at the top level. They
can see the model, as well as the top level of the report.
3. Did this issue appeared on all the report you developed in report
builder?
It only appeared in Cross-tab (matrix) reports that I created. Standard
reports that do not have drill down capabilities ran fine.
4. Have you applied the latest services pack of SQL Server?
No, I haven't. I don't believe they have been approved by internal IT
support yet, but I can check if necessary. Would you recommend I install
just SP1, or the additional hot fixes that are currently available after SP1
as well?
Thank you for the help. Please let me know if you need more information,
and I'd be happy to provide it.
-Pete
"Wei Lu [MSFT]" wrote:
> Hello Pete,
> I would like to know this issue more clearly.
> 1. Did you mean you create the report in the report builder?
> 2. Have you provided proper permission on the Report Model you use in the
> report builder?
> 3. Did this issue appeared on all the report you developed in report
> builder?
> 4. Have you applied the latest services pack of SQL Server?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Pete,
Thanks for the update.
First, please apply the SP1 for SQL 2005 and let me know the result.
Second, I would like to know whether you have grant the proper permission
on the datasource for the user.
That means, did the user have permission to access the datasource?
If the datasource is the sql server, did they have permission to access it?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei,
I checked the user account, and he does have permissions to the data source
- he is able to edit or modify it, and generate a new model if he wanted to.
As I mentioned, the SP1 has not been approved yet for deployment internally.
Is there a knowledge base article that shows how this solves the problem
perhaps that I can reference to attempt to push the addition of it through?
If not, are there any other options or thoughts? Thanks for the help - I
appreciate it. Please let me know if you need more information.
-Pete
"Wei Lu [MSFT]" wrote:
> Hello Pete,
> Thanks for the update.
> First, please apply the SP1 for SQL 2005 and let me know the result.
> Second, I would like to know whether you have grant the proper permission
> on the datasource for the user.
> That means, did the user have permission to access the datasource?
> If the datasource is the sql server, did they have permission to access it?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Pete,
There is no KB about this issue.
When I ask you to check the permission , I mean you need to check whether
the user could access the SQL Server, not whether he could modify the data
source file in the report manager.
Also, could you generate a sample report so that I may try to reproduce
this on my side?
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================.|||Wei,
Thanks for the information. The data source is created using credentials
stored on the report server, which can successfully access the database. We
have one user to access the DB, and don't impersonate the one making the
request.
As for a sample report, I'm not sure can provide that easily. Due to the
environment where this is deployed, we can't let out any data or reports. I
could potentially create a similar one, but that would be on a different
database (Northwind?) and reports server.
The steps to create one are pretty easy, since i am not using ANY advanced
configuration. It is built only with the web-based report builder, and
contains two tables, in a matrix, or cross-tab, report.
Thanks for the help - any other ideas you may have would be appreciated.
-Pete
"Wei Lu [MSFT]" wrote:
> Hello Pete,
> There is no KB about this issue.
> When I ask you to check the permission , I mean you need to check whether
> the user could access the SQL Server, not whether he could modify the data
> source file in the report manager.
> Also, could you generate a sample report so that I may try to reproduce
> this on my side?
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> =====================================================> PLEASE NOTE: The partner managed newsgroups are provided to assist with
> break/fix
> issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================.
>
>|||Hello Pete,
The reason why I need a sample report is that I need to know the model you
created and the report design.
So any simple report which could reproduce the issue may help me to
troubleshooting.
Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)
I've successfully setup the reports server 2005, on Windows Server 2003, and
had the permissions for the specific users configured to enable access to
the report. When a user logs into the report server, they can access the
reports I've created and deployed.
The problem arises when they try to drill down to the next level of the
report. I've created a report that is a matrix/crosstab report in the report
builder and the user can see the default (top level) of it without a problem.
However, as soon as they click on a record to drill down and get details on
it, they get an rsAccessDenied error that DOMAIN\User does not have
permissions.
As a test, I gave a specific user full content-manager rights (essentially I
tried everything short of giving them local admin rights to the box) and they
still can't run the drill down.
I'm wondering if they need report builder installed to generate the drill
down on the fly? We are using IWA on the domain to access the report server
itself. I thought I had the permissions set up fine, since they can run the
report at first, and I haven't found any options to allow/disallow drill
downs.
Any suggestions to try would be much appreciated. Thanks in advance for the
help.
-PeteHello Pete,
I would like to know this issue more clearly.
1. Did you mean you create the report in the report builder?
2. Have you provided proper permission on the Report Model you use in the
report builder?
3. Did this issue appeared on all the report you developed in report
builder?
4. Have you applied the latest services pack of SQL Server?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Wei,
Thanks for the quick response. I'll answer your questions in-lin below, and
I appreciate any help you can give.
1. Did you mean you create the report in the report builder?
Yes, I created the data source, and data model using the report manager, and
then created the report using report builder. I did all of this as the local
admin on the box on which reporting services was running, and had no
permissions problems (as I was in the default group on RS). The user was on
an external WinXP bx, without report builder loaded when trying to access the
report. They were manually added in as a Content Manager User on Report
Server, at the top level (which I believe gives them rights to all the lower
levels like th report and model.)
2. Have you provided proper permission on the Report Model you use in the
report builder?
Yes, I did. They are a Content Manager role user at the top level. They
can see the model, as well as the top level of the report.
3. Did this issue appeared on all the report you developed in report
builder?
It only appeared in Cross-tab (matrix) reports that I created. Standard
reports that do not have drill down capabilities ran fine.
4. Have you applied the latest services pack of SQL Server?
No, I haven't. I don't believe they have been approved by internal IT
support yet, but I can check if necessary. Would you recommend I install
just SP1, or the additional hot fixes that are currently available after SP1
as well?
Thank you for the help. Please let me know if you need more information,
and I'd be happy to provide it.
-Pete
"Wei Lu [MSFT]" wrote:
> Hello Pete,
> I would like to know this issue more clearly.
> 1. Did you mean you create the report in the report builder?
> 2. Have you provided proper permission on the Report Model you use in the
> report builder?
> 3. Did this issue appeared on all the report you developed in report
> builder?
> 4. Have you applied the latest services pack of SQL Server?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Pete,
Thanks for the update.
First, please apply the SP1 for SQL 2005 and let me know the result.
Second, I would like to know whether you have grant the proper permission
on the datasource for the user.
That means, did the user have permission to access the datasource?
If the datasource is the sql server, did they have permission to access it?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei,
I checked the user account, and he does have permissions to the data source
- he is able to edit or modify it, and generate a new model if he wanted to.
As I mentioned, the SP1 has not been approved yet for deployment internally.
Is there a knowledge base article that shows how this solves the problem
perhaps that I can reference to attempt to push the addition of it through?
If not, are there any other options or thoughts? Thanks for the help - I
appreciate it. Please let me know if you need more information.
-Pete
"Wei Lu [MSFT]" wrote:
> Hello Pete,
> Thanks for the update.
> First, please apply the SP1 for SQL 2005 and let me know the result.
> Second, I would like to know whether you have grant the proper permission
> on the datasource for the user.
> That means, did the user have permission to access the datasource?
> If the datasource is the sql server, did they have permission to access it?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Pete,
There is no KB about this issue.
When I ask you to check the permission , I mean you need to check whether
the user could access the SQL Server, not whether he could modify the data
source file in the report manager.
Also, could you generate a sample report so that I may try to reproduce
this on my side?
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================.|||Wei,
Thanks for the information. The data source is created using credentials
stored on the report server, which can successfully access the database. We
have one user to access the DB, and don't impersonate the one making the
request.
As for a sample report, I'm not sure can provide that easily. Due to the
environment where this is deployed, we can't let out any data or reports. I
could potentially create a similar one, but that would be on a different
database (Northwind?) and reports server.
The steps to create one are pretty easy, since i am not using ANY advanced
configuration. It is built only with the web-based report builder, and
contains two tables, in a matrix, or cross-tab, report.
Thanks for the help - any other ideas you may have would be appreciated.
-Pete
"Wei Lu [MSFT]" wrote:
> Hello Pete,
> There is no KB about this issue.
> When I ask you to check the permission , I mean you need to check whether
> the user could access the SQL Server, not whether he could modify the data
> source file in the report manager.
> Also, could you generate a sample report so that I may try to reproduce
> this on my side?
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> =====================================================> PLEASE NOTE: The partner managed newsgroups are provided to assist with
> break/fix
> issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================.
>
>|||Hello Pete,
The reason why I need a sample report is that I need to know the model you
created and the report design.
So any simple report which could reproduce the issue may help me to
troubleshooting.
Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)
Subscribe to:
Posts (Atom)