Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Using Disparate File Paths in SQL Server Database Mirroring Expand / Collapse
Author
Message
Posted Thursday, March 3, 2011 12:53 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 26, 2014 11:10 AM
Points: 63, Visits: 274
Excellent article.
It gave me new trick.

After testing with new File Group and File at principal, mirroring works.

But, I cannot create database snapshot at mirror.

Before adding File, the following one worked.
CREATE DATABASE Mirror_Test_snapshot ON
( NAME = N'Mirror_Test_Data',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Mirror_Test_Data_snapshot.snap')
AS SNAPSHOT OF Mirror_Test;

After adding File at Node1\Mirror_Test_FG_IDX_1.Mirror_Test_FG_IDX_1.ndf at principal
Restoring at Node2\Mirror_Test_FG_IDX_1.ndf at mirror
, this one doesn't work.

CREATE DATABASE Mirror_Test_snapshot ON
( NAME = N'Mirror_Test_Data',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Mirror_Test_Data_snapshot.snap'),
(NAME = N'Mirror_Test_FG_IDX_1',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Node2\Mirror_Test_FG_IDX_1_snapshot.snap')
AS SNAPSHOT OF Mirror_Test;
Post #1072852
Posted Thursday, March 3, 2011 3:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 6,643, Visits: 14,230
tuntun.oo (3/3/2011)
Excellent article.
It gave me new trick.

After testing with new File Group and File at principal, mirroring works.

But, I cannot create database snapshot at mirror.

Before adding File, the following one worked.
CREATE DATABASE Mirror_Test_snapshot ON
( NAME = N'Mirror_Test_Data',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Mirror_Test_Data_snapshot.snap')
AS SNAPSHOT OF Mirror_Test;

After adding File at Node1\Mirror_Test_FG_IDX_1.Mirror_Test_FG_IDX_1.ndf at principal
Restoring at Node2\Mirror_Test_FG_IDX_1.ndf at mirror
, this one doesn't work.

CREATE DATABASE Mirror_Test_snapshot ON
( NAME = N'Mirror_Test_Data',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Mirror_Test_Data_snapshot.snap'),
(NAME = N'Mirror_Test_FG_IDX_1',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Node2\Mirror_Test_FG_IDX_1_snapshot.snap')
AS SNAPSHOT OF Mirror_Test;


do you get an error message, if so what is it?


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1072966
Posted Friday, March 4, 2011 5:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 26, 2014 11:10 AM
Points: 63, Visits: 274
Location: recovery.cpp:3080
Expression: m_recType != REPLICA
SPID: 54
Process ID: 1748
Msg 1823, Level 16, State 1, Line 1
A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
Msg 9003, Level 20, State 5, Line 1
The log scan number (45:1518:0) passed to log scan in database 'Mirror_Test' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

I also attached SQLdump file.

--SQL Server Version -- Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) Dec 10 2010 10:56:29 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)


  Post Attachments 
SQLDump0020.txt (1 view, 46.73 KB)
Post #1073251
Posted Wednesday, April 27, 2011 11:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 24, 2014 11:43 AM
Points: 11, Visits: 62
Nice tip. Thanks for the post.
Post #1099657
Posted Wednesday, April 27, 2011 2:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 6,643, Visits: 14,230
Thank you, please don't forget to rate the article

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1099803
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse