December 21, 2008 at 8:35 pm
Hi Folks,
I am using high safety mode for my database mirror. I thought that in this mode that if the mirror fails it takes the principal offline, but when I stop the mirror instance I can still query the principal server. Am I missing something here?
December 22, 2008 at 12:16 am
can you also insert/update/delete on the pricipal instance ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 22, 2008 at 1:22 am
yes - it appears so.
December 22, 2008 at 1:35 am
Can you run this and post the results ?
/*
* DB Mirroring : followup
*
*/
/* show mirrored databases and their mirror-state */
Select db_name(database_id) as dbName
, *
from sys.database_mirroring
Where mirroring_guid is not null -- show only mirrored databases
order by dbName;
/* the "simple" proc */
exec msdb..sp_dbmmonitorresults 'YOURDATABASENAME'
SELECT principal_server_name, mirror_server_name,
database_name, safety_level_desc
FROM sys.database_mirroring_witnesses
__ ADDED __
Can you also check the PARTNER TIMEOUT settings for the database ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 22, 2008 at 5:28 pm
the third query got no result.. I am using high safety not high avialability so there is no witness. Here is a script to get the same result I get on my server
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable2
--===== Create the test table for first query
CREATE TABLE #mytable (dbName sysname,
database_id INT,
mirroring_guid uniqueidentifier,
mirroring_state tinyint,
mirroring_state_desc nvarchar(60),
mirroring_role tinyint,
mirroring_role_desc nvarchar(60),
mirroring_role_sequence int,
mirroring_safety_level tinyint,
mirroring_safety_level_desc nvarchar(30),
mirroring_safety_sequence int,
mirroring_partner_name nvarchar(128),
mirroring_partner_instance nvarchar(128),
mirroring_witness_name nvarchar(128),
mirroring_witness_state tinyint,
mirroring_witness_state_desc nvarchar(60),
mirroring_failover_lsn numeric(25,0),
mirroring_connection_timeout int,
mirroring_redo_queue int,
mirroring_redo_queue_type nvarchar(60) )
--==== table for second query
CREATE TABLE #mytable2 (database_name sysname, role int, mirroring_state int, witness_status int, log_generation_rate int, unsent_log int,
send_rate int, unrestored_log int, recovery_rate int, transaction_delay int, transactions_per_sec int, average_delay int, time_recorded datetime,
time_behind datetime, local_time datetime)
insert into #mytable
values
('test','10','EE6C6242-DB82-418B-A0CD-8F295A455A33','4','SYNCHRONIZED','1','PRINCIPAL','1','2','FULL','1','TCP://AUBRIDESQL01.mondial-assistance.au.local:5023','AUBRIDESQL01\SPARE','','0','UNKNOWN','39000000076800001','10',NULL,'UNLIMITED')
--===== Insert table for second query
INSERT INTO #mytable2
values
('test','1','4','0','0','0','0','0','0','0','0','0','2008-12-22 23:24:00.490','2008-12-22 23:24:00.490','2008-12-23 09:24:00.490')
select *
from #mytable
select *
from #mytable2
December 23, 2008 at 2:42 am
What service pack are you on ?
(should be at least SP1, better SP2, best SP3 😉 )
Select @@version
SQL Server 2005 Books Online (November 2008) topic "Monitoring Database Mirroring "
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 23, 2008 at 4:36 pm
Microsoft SQL Server 2005 - 9.00.3073.00 (Intel X86) Aug 5 2008 12:31:12 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Last time I checked SP3 was CTM release... I noticed it was released properly on the 12 December. We usually install microsoft approved update as recommended by WSUS... I'm not so keen to install SP3 so early after release - I'd sit back and see if there is any problem with it first.
December 24, 2008 at 4:15 am
bodhilove (12/23/2008)
Microsoft SQL Server 2005 - 9.00.3073.00 (Intel X86) Aug 5 2008 12:31:12 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)Last time I checked SP3 was CTM release... I noticed it was released properly on the 12 December. We usually install microsoft approved update as recommended by WSUS... I'm not so keen to install SP3 so early after release - I'd sit back and see if there is any problem with it first.
I understand.
You're on SP2 which is a good thing.
I'm not at the office this week, meaning I have little test opportunities.
From what I see in BOL, primary should go offline when mirror is not available with your meanth setting.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply