Bug when changing the default data and log directories

We have seen this problem on several SQL 2000 Standard Servers and was wonde
ring if anyone else had encountered it:
We started having problems restoring databases. We were receving errors tha
t the physical files paths were incorrect. When we looked at the file paths
for the databases we noticed that they looked like this "e:\mssql\data\\fil
e.mdf" and "F:\mssql\logs\\
logfile.ldf"" Note the double slashes.
What we found that is we changed the default data and log paths through ente
rprise manager. When we did it, we went to EM, clicked the browse button an
d then selected the target directories. This left the settings looking like
this:
"E:\mssql\data\"
"F:\mssql\logs\"
When you create a database through EM, everything works fine. When you crea
te a database using the create database command, it gets created with the do
uble backslashes in the file path for the data and log files.
We have been able to re-create this on several servers. If you take out the
trailing backslashes and stop and start the MSSQL Service (it appears chang
es to this setting only takes affect after a service stop and start even tho
ugh the running value would
indicate otherwise) then the 'create database' function work fine and only i
nserts single slashes from then on.
We are running SQL 2000 SP3 and we haven't been able to find any other docum
ented instance of this. If anyone else can re-create this issue we would be
very grateful to hear that it isn't something we are doing wrong.
ThanksThis is a known issue but I don't think there is a KB article. As you have
found, the default data and log directory strings should not have a trailing
backslash or you get the \\ in the filepath. Whilst it may be fixed in a
future service pack, it seems unlikely as it has been round for a while, is
fairly benign and has a simple workaround.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mike Holleran" <mholleran@.infodir.com> wrote in message
news:ED046519-1519-4579-A6FE-F5EE63C18B05@.microsoft.com...
> We have seen this problem on several SQL 2000 Standard Servers and was
wondering if anyone else had encountered it:
> We started having problems restoring databases. We were receving errors
that the physical files paths were incorrect. When we looked at the file
paths for the databases we noticed that they looked like this
"e:\mssql\data\\file.mdf" and "F:\mssql\logs\\logfile.ldf"" Note the double
slashes.
> What we found that is we changed the default data and log paths through
enterprise manager. When we did it, we went to EM, clicked the browse
button and then selected the target directories. This left the settings
looking like this:
> "E:\mssql\data\"
> "F:\mssql\logs\"
> When you create a database through EM, everything works fine. When you
create a database using the create database command, it gets created with
the double backslashes in the file path for the data and log files.
> We have been able to re-create this on several servers. If you take out
the trailing backslashes and stop and start the MSSQL Service (it appears
changes to this setting only takes affect after a service stop and start
even though the running value would indicate otherwise) then the 'create
database' function work fine and only inserts single slashes from then on.
> We are running SQL 2000 SP3 and we haven't been able to find any other
documented instance of this. If anyone else can re-create this issue we
would be very grateful to hear that it isn't something we are doing wrong.
> Thanks
>
>
>

buffered trigger

Hallo i need to read/write data using triggers between two remote MSSQL2K servers, over an unstable connection.
Can trigger operations be buffered, so they are completed when connection is on?
Or should i make the triggers to write into local tables, then replicated in merge? (i would like to avoid this)

No. Trigger is part of the active transaction so if you have an unstable remote connection, I would suggest you queue your data into a worker queue table and process it later. I suggest you take a look at Service Broker in book online. It's designed for message queue/processing.

|||

This asyncronous read/write data can be resolved using SQL Service Broker a feature of SQL Server Express 2005 (that is free).

My ideea is :

1.The trigger of SQL2K1 server write the data in a Table1 of SQL2K1

2.SSSBroker get the data of Table1 in a Table2 from SSEXPRESServer in a Database1

3.From Table2 SSExpress copy data to SQL2K2

HTH

Broker:Corrupted message

Hello,

Has anyone seen this in profiler? I have two brokers on different servers with one of them being the initiator. All messages end up sitting in the initiator's transmission queue. Profiler on the target broker's machine displays this for every attempt to send by the initiator:

A corrupt message has been received. The End of Conversation and Error flags may not be set in the first sequenced message. This occorred in the message with Conversation ID '...', Initiator: 1, and Message sequence number: 0

In case it's relevant encryption is disabled and endpoints on both servers use windows authentication.

Any hints are greatly appreciated.

Regards,

Svega

Hello,

This is weird. I've tried to reproduce the problem but I couldn't. Can you provide the steps you're doing in the initiator when it ends up like this? What I'm interested in mostly is the exact sequence in which these statements occur: BEGIN TRANSACTION, BEGIN DIALOG, SEND, END CONVERSATION, COMMIT.

Also, what SQL Server versions are the initiator and target? Are they both the November RTM or is one of them an earlier CTP release?

Thanks,
~ Remus

|||

Hello Remus,

Thanks for replying.

Both servers are November RTM. Until yesterday I had the two databases on the same server and everything worked. Then I had moved the target service broker to a different server. Both servers are 2003 Enterprise. Initiator is on a x64 machine, target is on a 32-bit machine. I moved the database by detaching it, copying to the other server and attaching it there. I then created a route to the target service in the intiator's database. Sequence of statements:

1. begin tran

2. begin dialog @.convh from service initiator to service 'target' on contract 'contract' with encryption = off

3. send on conversation @.convh message type [...] (@.body)

4. end conversation @.convh

5. commit tran

When I hit the problem my first thought was it had something to do with security. Since I don't use dialog security and use windows authentication for end points I should not need certificates. However I tried to use certificates at adjacent while investigating so I created certificates in both databases and recreated endpoints to use certificate authentication. I could no longer see the "corrupted message" event on tha target side. Now profiler on the target server does not show any broker activity at all. On the initiator side profiler is constantly getting "Broker:Remote Message Acknowledgement" event and all messages remain in the transmission queue with empty transmission status Netstat shows an established connection between the two servers. I have to mention that I didn't initially create routes back to initiator in the target broker database. I thought I should not need them. I then added them but it didn't change anything.

If you need any more info please let me know.

Regards,

Alex

|||

One more observation: I ran Network Monitor on the target server and I could clearly see packets with messages from the initiator coming and tcp acknowledgements being sent back to the initiator. However all acks contain Checksum = ERROR... I am not sure this is what's causing the problem as acks in other communications seem to have the same issue. It could well be Network Monitor's problem. Just thought this could help in some way.

Regards,

Alex

|||

Alex,

I could reproduce the problem you describe. From what I've found, two conditions have to be met to run into this problem:
- first SEND and the END statements are in one single transaction
- the BEGIN DIALOG statement contains an explicit broker instance

To work around the problem you have three options:
- separate the first SEND and END conversation into two separate transactions
- don't use an explicit broker instance in the BEGIN DIALOG
- change the message exchange patter from BEGIN/SEND/END into an exchange where the initiator does just BEGIN/SEND and it ENDs it's endpoint as a response to the target sending the END from it's side. In general this later case is a better message exchange patter because the first pattern (the BEGIN/SEND/END) is really a 'fire and forget' scenario where the initiator has no means to ever figure out if the message was delivered successfully or not.

The problem is not related to the dialog security or broker endpoint security settings. It can happen only on remote delivery (between 2 SQL instances). The system architecture (x86, AMD64, IA64) doesn't matter either.

The fact that the message gets dropped on the problem scenario is a bug. You can use the SQL Product Feedback center at http://lab.msdn.microsoft.com/productfeedback to report this issue.

Thanks,
~ Remus

|||

Remus,

Thanks a lot for the finding. Of the tree options I have to use broker instance for routing to work because I have multiple instances of the same service on the same server. I will look how I can get around this with the remaining two. Thanks for the prompt investigation.

Regards,

Alex

|||

Remus,

I tried option #1(separate first SEND and END CONVERSATION into different trans) and it doesn't seem to make any difference. Messages are still sitting in the transmission queue. The only change is that now profiler does not show any broker events on either of the servers. But in NetMon I can see that initiator tries to deliver. I will see if I can use #3 somehow ot at all.

Regards,

Alex

|||

This is probably unrelated and I suspect is a misconfiguration somewhere in the broker endpoint security or in the routes. Try to follow the steps in this post http://blogs.msdn.com/remusrusanu/archive/2005/12/20/506221.aspx to diagnose the problem.

HTH,
~ Remus

P.S. I now see you actualy read those, since you posted a comment

|||

Please make sure you select all events in the Broker category, as well as 'Security Audit/Audit Broker Login' and 'Security Audit/Audit Broker Conversation'.

Does the transmission_status column on the initiator side has any value?

|||

I did select all the events. Profilers on both servers show no traces. transmission_status is empty. I've looked at option #3 and it's a little messy to change the message exchange pattern to let target decide when to end dialog. In my case upstream service doesn't really depend on whether the next hop received and processed a message. So the send and forget logic fits well. Still I will spend some more time looking at #3.

Regards,
Alex

|||

Can you check if the sys.dm_broker_connections view contains the row for the broker connection between the 2 SQL instances involved? If it does, can you see if the total_fragmets_sent/total_fragments_received values change?

When an acknowledgemnt for a message was not received the sender will retry that message roughly one every minute. I expect this retry to generate profile events each time it occurs, and also the sys.dm_broker_connections view to change the values, reflecting that the message was actually sent (retry).

Thanks,
~ Remus

|||

Yes there is a row for the connection. total_fragments_sent increases about once every minute by the number of messages in the transmission queue. total_fragments_received does not change. But it's not zero (fragments sent = 7748 and increasing, fragments received = 6). Profiler does not show any activity with all broker events selected plus Audit Broker Conversation and Audit Broker Login. I tried to begin dialog without target broker id and messages still get stuck in the transmission queue. So far I have not been able to send messages to a remote service. Locally everything works fine.

Alex

|||

So the initiator is sending messages, but the target is either dropping them either unable to send back acks for them. Both these scenarios should generate plenty of noise in the profiler on the target. No activity at all in the profiler seems very suspicious to me. I know it sounds silly, but can you ensure that you are connected to the right SQL instance and the profile is actually started?

Also, can you check if the target conversation endpoint was created? Look in sys.conversation_endpoints on the target database and see if you find the target conversation endpoint. You can use the conversation_id column to match the initiator conversation endpoint with the target.

Thanks,
~ Remus

|||

I am back in business with profiler. It was my bad. After I brought up Profiler I unchecked all events that were selected by default in Security Audit, Sessions, etc... so that no event is selected. It's interesting that when you uncheck all events and then click the Show All Events checkbox it hides all columns. Unless you click the Show All Columns checkbox no column is displayed. Then even if you select events to trace without columns profiler does not show any traces. It was late yesterday... :)

I ran some more tests without specifying broker id in begin dialog. Messages get delivered to target queue but what's interesting is that the same messages also remain in transmission queue of initiator. On the target side profiler shows a bunch of Broker:Message Undeliverable with this text: "The message has been dropped because the service broker in the target database is unavailable: 'The service broker is administratively disabled'". However in sys.databases the database has is_broker_enabled=1.

I also tried to end dialogs as you suggested (target ends first) but then realized that if messages are not delivered to target then it won't have a chance to end dialog on its side.

I am now looking to get around this by directly putting messages into the target service's queue over a separate connection to the target database. I understand that this is risky as data consistency can be compromised but for now there doesn't seem to be another way.

Regards,

Alex

|||

Remus,

I also see undeliverables with this text: "This message could not be delivered because the 'receive sequenced message' action cannot be performed in the 'CLOSED' state". And then come all those with "service broker is administrativeli disabled".

Alex

Broker:Corrupted message

Hello,

Has anyone seen this in profiler? I have two brokers on different servers with one of them being the initiator. All messages end up sitting in the initiator's transmission queue. Profiler on the target broker's machine displays this for every attempt to send by the initiator:

A corrupt message has been received. The End of Conversation and Error flags may not be set in the first sequenced message. This occorred in the message with Conversation ID '...', Initiator: 1, and Message sequence number: 0

In case it's relevant encryption is disabled and endpoints on both servers use windows authentication.

Any hints are greatly appreciated.

Regards,

Svega

Hello,

This is weird. I've tried to reproduce the problem but I couldn't. Can you provide the steps you're doing in the initiator when it ends up like this? What I'm interested in mostly is the exact sequence in which these statements occur: BEGIN TRANSACTION, BEGIN DIALOG, SEND, END CONVERSATION, COMMIT.

Also, what SQL Server versions are the initiator and target? Are they both the November RTM or is one of them an earlier CTP release?

Thanks,
~ Remus

|||

Hello Remus,

Thanks for replying.

Both servers are November RTM. Until yesterday I had the two databases on the same server and everything worked. Then I had moved the target service broker to a different server. Both servers are 2003 Enterprise. Initiator is on a x64 machine, target is on a 32-bit machine. I moved the database by detaching it, copying to the other server and attaching it there. I then created a route to the target service in the intiator's database. Sequence of statements:

1. begin tran

2. begin dialog @.convh from service initiator to service 'target' on contract 'contract' with encryption = off

3. send on conversation @.convh message type [...] (@.body)

4. end conversation @.convh

5. commit tran

When I hit the problem my first thought was it had something to do with security. Since I don't use dialog security and use windows authentication for end points I should not need certificates. However I tried to use certificates at adjacent while investigating so I created certificates in both databases and recreated endpoints to use certificate authentication. I could no longer see the "corrupted message" event on tha target side. Now profiler on the target server does not show any broker activity at all. On the initiator side profiler is constantly getting "Broker:Remote Message Acknowledgement" event and all messages remain in the transmission queue with empty transmission status Netstat shows an established connection between the two servers. I have to mention that I didn't initially create routes back to initiator in the target broker database. I thought I should not need them. I then added them but it didn't change anything.

If you need any more info please let me know.

Regards,

Alex

|||

One more observation: I ran Network Monitor on the target server and I could clearly see packets with messages from the initiator coming and tcp acknowledgements being sent back to the initiator. However all acks contain Checksum = ERROR... I am not sure this is what's causing the problem as acks in other communications seem to have the same issue. It could well be Network Monitor's problem. Just thought this could help in some way.

Regards,

Alex

|||

Alex,

I could reproduce the problem you describe. From what I've found, two conditions have to be met to run into this problem:
- first SEND and the END statements are in one single transaction
- the BEGIN DIALOG statement contains an explicit broker instance

To work around the problem you have three options:
- separate the first SEND and END conversation into two separate transactions
- don't use an explicit broker instance in the BEGIN DIALOG
- change the message exchange patter from BEGIN/SEND/END into an exchange where the initiator does just BEGIN/SEND and it ENDs it's endpoint as a response to the target sending the END from it's side. In general this later case is a better message exchange patter because the first pattern (the BEGIN/SEND/END) is really a 'fire and forget' scenario where the initiator has no means to ever figure out if the message was delivered successfully or not.

The problem is not related to the dialog security or broker endpoint security settings. It can happen only on remote delivery (between 2 SQL instances). The system architecture (x86, AMD64, IA64) doesn't matter either.

The fact that the message gets dropped on the problem scenario is a bug. You can use the SQL Product Feedback center at http://lab.msdn.microsoft.com/productfeedback to report this issue.

Thanks,
~ Remus

|||

Remus,

Thanks a lot for the finding. Of the tree options I have to use broker instance for routing to work because I have multiple instances of the same service on the same server. I will look how I can get around this with the remaining two. Thanks for the prompt investigation.

Regards,

Alex

|||

Remus,

I tried option #1(separate first SEND and END CONVERSATION into different trans) and it doesn't seem to make any difference. Messages are still sitting in the transmission queue. The only change is that now profiler does not show any broker events on either of the servers. But in NetMon I can see that initiator tries to deliver. I will see if I can use #3 somehow ot at all.

Regards,

Alex

|||

This is probably unrelated and I suspect is a misconfiguration somewhere in the broker endpoint security or in the routes. Try to follow the steps in this post http://blogs.msdn.com/remusrusanu/archive/2005/12/20/506221.aspx to diagnose the problem.

HTH,
~ Remus

P.S. I now see you actualy read those, since you posted a comment

|||

Please make sure you select all events in the Broker category, as well as 'Security Audit/Audit Broker Login' and 'Security Audit/Audit Broker Conversation'.

Does the transmission_status column on the initiator side has any value?

|||

I did select all the events. Profilers on both servers show no traces. transmission_status is empty. I've looked at option #3 and it's a little messy to change the message exchange pattern to let target decide when to end dialog. In my case upstream service doesn't really depend on whether the next hop received and processed a message. So the send and forget logic fits well. Still I will spend some more time looking at #3.

Regards,
Alex

|||

Can you check if the sys.dm_broker_connections view contains the row for the broker connection between the 2 SQL instances involved? If it does, can you see if the total_fragmets_sent/total_fragments_received values change?

When an acknowledgemnt for a message was not received the sender will retry that message roughly one every minute. I expect this retry to generate profile events each time it occurs, and also the sys.dm_broker_connections view to change the values, reflecting that the message was actually sent (retry).

Thanks,
~ Remus

|||

Yes there is a row for the connection. total_fragments_sent increases about once every minute by the number of messages in the transmission queue. total_fragments_received does not change. But it's not zero (fragments sent = 7748 and increasing, fragments received = 6). Profiler does not show any activity with all broker events selected plus Audit Broker Conversation and Audit Broker Login. I tried to begin dialog without target broker id and messages still get stuck in the transmission queue. So far I have not been able to send messages to a remote service. Locally everything works fine.

Alex

|||

So the initiator is sending messages, but the target is either dropping them either unable to send back acks for them. Both these scenarios should generate plenty of noise in the profiler on the target. No activity at all in the profiler seems very suspicious to me. I know it sounds silly, but can you ensure that you are connected to the right SQL instance and the profile is actually started?

Also, can you check if the target conversation endpoint was created? Look in sys.conversation_endpoints on the target database and see if you find the target conversation endpoint. You can use the conversation_id column to match the initiator conversation endpoint with the target.

Thanks,
~ Remus

|||

I am back in business with profiler. It was my bad. After I brought up Profiler I unchecked all events that were selected by default in Security Audit, Sessions, etc... so that no event is selected. It's interesting that when you uncheck all events and then click the Show All Events checkbox it hides all columns. Unless you click the Show All Columns checkbox no column is displayed. Then even if you select events to trace without columns profiler does not show any traces. It was late yesterday... :)

I ran some more tests without specifying broker id in begin dialog. Messages get delivered to target queue but what's interesting is that the same messages also remain in transmission queue of initiator. On the target side profiler shows a bunch of Broker:Message Undeliverable with this text: "The message has been dropped because the service broker in the target database is unavailable: 'The service broker is administratively disabled'". However in sys.databases the database has is_broker_enabled=1.

I also tried to end dialogs as you suggested (target ends first) but then realized that if messages are not delivered to target then it won't have a chance to end dialog on its side.

I am now looking to get around this by directly putting messages into the target service's queue over a separate connection to the target database. I understand that this is risky as data consistency can be compromised but for now there doesn't seem to be another way.

Regards,

Alex

|||

Remus,

I also see undeliverables with this text: "This message could not be delivered because the 'receive sequenced message' action cannot be performed in the 'CLOSED' state". And then come all those with "service broker is administrativeli disabled".

Alex

Breaking Replication??

Hi All
I have servers A, B and C with databases. Server A is a Publisher and Server
B is the Distributor/Subscriber. Server C is a Subscriber. Server A
replicates 7 publications to both server B and C. Initially when the
publications were very small, there were snapshot replications from A to both
B and C. Thereafter there have been transactional replications. The total
size of the databases has grown to about 30G.
My problem is I want to replace server C with a new server D. I have only 12
hrs to do this. If I do a snapshot replication to server D, it might take me
about 60hrs which I can’t do as this has to be done on a Sunday and ready for
Monday morning. I have a 150K pipe. I would like to avoid breaking the
replication on server B if possible. How can I do this replication within
12hours without breaking replication on server B?
Currently:
A>>>B
A>>>C
Future:
A>>>B
A>>>D
Thank you in advance.
You could create the snapshot, zip it up (WinZip 9.0), ftp, unzip and
restore, then specify an alternative snapshot location when initializing
server D. Alternatively you could zip up a backup of the database, ftp,
unzip restore then synchronize any changes to the data (if it is possible
there could be some). Prevent any further changes then do a nosync
initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Since server D is a new server, why don't you just set this new server as an
additional subscriber while you are still replicating to server C? Once D
has received the snapshot and is replicating normally you can pull server C.
I'm not sure how far away from your datacenter server D will be, but you
could also do the snapshot stuff on the local network (where server A&B are)
and then ship it to it's final location. One sticking point may be if you
are planning on using the same machine name for server D that you have for
server C, but it may not be an issue if you are using anonymous subscribers.
"MittyKom" wrote:

> Hi All
> I have servers A, B and C with databases. Server A is a Publisher and Server
> B is the Distributor/Subscriber. Server C is a Subscriber. Server A
> replicates 7 publications to both server B and C. Initially when the
> publications were very small, there were snapshot replications from A to both
> B and C. Thereafter there have been transactional replications. The total
> size of the databases has grown to about 30G.
> My problem is I want to replace server C with a new server D. I have only 12
> hrs to do this. If I do a snapshot replication to server D, it might take me
> about 60hrs which I can’t do as this has to be done on a Sunday and ready for
> Monday morning. I have a 150K pipe. I would like to avoid breaking the
> replication on server B if possible. How can I do this replication within
> 12hours without breaking replication on server B?
> Currently:
> A>>>B
> A>>>C
> Future:
> A>>>B
> A>>>D
> Thank you in advance.
>

Break mirror availability

I need to break a mirror on one of our reporting servers but it's 24/7 and
pretty busy. Does anyone know if this would interupt db access?
Thanks in advance
By "break mirror", what do you mean exactly? Is it that you have RAID1 and
want to remove one of the disks? In that case, thee should be no
interruption. Just keep in mind that you are vulnerable until the
remirroring has been completed.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:9448F943-9543-4E51-BAB0-21AB10CA4B26@.microsoft.com...
I need to break a mirror on one of our reporting servers but it's 24/7 and
pretty busy. Does anyone know if this would interupt db access?
Thanks in advance
|||Sorry Tom, my bad.
I'm mean database mirroring, not IO.
I want to break the mirror but I'm wondering if the primary will experience
any unavailability when I execute the SET PARTNER = NONE or whatever the
syntax is.
"Tom Moreau" wrote:

> By "break mirror", what do you mean exactly? Is it that you have RAID1 and
> want to remove one of the disks? In that case, thee should be no
> interruption. Just keep in mind that you are vulnerable until the
> remirroring has been completed.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:9448F943-9543-4E51-BAB0-21AB10CA4B26@.microsoft.com...
> I need to break a mirror on one of our reporting servers but it's 24/7 and
> pretty busy. Does anyone know if this would interupt db access?
> Thanks in advance
>
|||You can stop DB mirroring without affecting the principal. What you have to
do later depends on how mirroring was stopped. If you formally did an ALTER
database and told it that you don't want to mirror any more, then when you
do go to mirror again, you'll need to restore a full backup to the mirror
server and resynch. However, if all you did was take the mirror server
offline, then transactions will remain in the log of the primary until you
can get the mirror back online again.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:AD0CCDB5-2D7F-4FCE-94EA-28D814CA837D@.microsoft.com...
Sorry Tom, my bad.
I'm mean database mirroring, not IO.
I want to break the mirror but I'm wondering if the primary will experience
any unavailability when I execute the SET PARTNER = NONE or whatever the
syntax is.
"Tom Moreau" wrote:

> By "break mirror", what do you mean exactly? Is it that you have RAID1
> and
> want to remove one of the disks? In that case, thee should be no
> interruption. Just keep in mind that you are vulnerable until the
> remirroring has been completed.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:9448F943-9543-4E51-BAB0-21AB10CA4B26@.microsoft.com...
> I need to break a mirror on one of our reporting servers but it's 24/7 and
> pretty busy. Does anyone know if this would interupt db access?
> Thanks in advance
>

Break mirror availability

I need to break a mirror on one of our reporting servers but it's 24/7 and
pretty busy. Does anyone know if this would interupt db access?
Thanks in advanceBy "break mirror", what do you mean exactly? Is it that you have RAID1 and
want to remove one of the disks? In that case, thee should be no
interruption. Just keep in mind that you are vulnerable until the
remirroring has been completed.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:9448F943-9543-4E51-BAB0-21AB10CA4B26@.microsoft.com...
I need to break a mirror on one of our reporting servers but it's 24/7 and
pretty busy. Does anyone know if this would interupt db access?
Thanks in advance|||Sorry Tom, my bad.
I'm mean database mirroring, not IO.
I want to break the mirror but I'm wondering if the primary will experience
any unavailability when I execute the SET PARTNER = NONE or whatever the
syntax is.
"Tom Moreau" wrote:

> By "break mirror", what do you mean exactly? Is it that you have RAID1 an
d
> want to remove one of the disks? In that case, thee should be no
> interruption. Just keep in mind that you are vulnerable until the
> remirroring has been completed.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:9448F943-9543-4E51-BAB0-21AB10CA4B26@.microsoft.com...
> I need to break a mirror on one of our reporting servers but it's 24/7 and
> pretty busy. Does anyone know if this would interupt db access?
> Thanks in advance
>|||You can stop DB mirroring without affecting the principal. What you have to
do later depends on how mirroring was stopped. If you formally did an ALTER
database and told it that you don't want to mirror any more, then when you
do go to mirror again, you'll need to restore a full backup to the mirror
server and resynch. However, if all you did was take the mirror server
offline, then transactions will remain in the log of the primary until you
can get the mirror back online again.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:AD0CCDB5-2D7F-4FCE-94EA-28D814CA837D@.microsoft.com...
Sorry Tom, my bad.
I'm mean database mirroring, not IO.
I want to break the mirror but I'm wondering if the primary will experience
any unavailability when I execute the SET PARTNER = NONE or whatever the
syntax is.
"Tom Moreau" wrote:

> By "break mirror", what do you mean exactly? Is it that you have RAID1
> and
> want to remove one of the disks? In that case, thee should be no
> interruption. Just keep in mind that you are vulnerable until the
> remirroring has been completed.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:9448F943-9543-4E51-BAB0-21AB10CA4B26@.microsoft.com...
> I need to break a mirror on one of our reporting servers but it's 24/7 and
> pretty busy. Does anyone know if this would interupt db access?
> Thanks in advance
>

Break mirror availability

I need to break a mirror on one of our reporting servers but it's 24/7 and
pretty busy. Does anyone know if this would interupt db access?
Thanks in advanceBy "break mirror", what do you mean exactly? Is it that you have RAID1 and
want to remove one of the disks? In that case, thee should be no
interruption. Just keep in mind that you are vulnerable until the
remirroring has been completed.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:9448F943-9543-4E51-BAB0-21AB10CA4B26@.microsoft.com...
I need to break a mirror on one of our reporting servers but it's 24/7 and
pretty busy. Does anyone know if this would interupt db access?
Thanks in advance|||Sorry Tom, my bad.
I'm mean database mirroring, not IO.
I want to break the mirror but I'm wondering if the primary will experience
any unavailability when I execute the SET PARTNER = NONE or whatever the
syntax is.
"Tom Moreau" wrote:
> By "break mirror", what do you mean exactly? Is it that you have RAID1 and
> want to remove one of the disks? In that case, thee should be no
> interruption. Just keep in mind that you are vulnerable until the
> remirroring has been completed.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:9448F943-9543-4E51-BAB0-21AB10CA4B26@.microsoft.com...
> I need to break a mirror on one of our reporting servers but it's 24/7 and
> pretty busy. Does anyone know if this would interupt db access?
> Thanks in advance
>|||You can stop DB mirroring without affecting the principal. What you have to
do later depends on how mirroring was stopped. If you formally did an ALTER
database and told it that you don't want to mirror any more, then when you
do go to mirror again, you'll need to restore a full backup to the mirror
server and resynch. However, if all you did was take the mirror server
offline, then transactions will remain in the log of the primary until you
can get the mirror back online again.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:AD0CCDB5-2D7F-4FCE-94EA-28D814CA837D@.microsoft.com...
Sorry Tom, my bad.
I'm mean database mirroring, not IO.
I want to break the mirror but I'm wondering if the primary will experience
any unavailability when I execute the SET PARTNER = NONE or whatever the
syntax is.
"Tom Moreau" wrote:
> By "break mirror", what do you mean exactly? Is it that you have RAID1
> and
> want to remove one of the disks? In that case, thee should be no
> interruption. Just keep in mind that you are vulnerable until the
> remirroring has been completed.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:9448F943-9543-4E51-BAB0-21AB10CA4B26@.microsoft.com...
> I need to break a mirror on one of our reporting servers but it's 24/7 and
> pretty busy. Does anyone know if this would interupt db access?
> Thanks in advance
>