cannot implement mirroring - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

High Availability (2005)

cannot implement mirroring

Author

Topic

cmrhema
Starting Member

20 Posts

Posted - 2008-04-01 : 03:55:23

Hi,

I have place an question, but in some other place in the same forum.

My apologies for that

I wanted to implement mirroring
I hereby let u know the steps

1. Created a database in Principal server named Mirror1 and one table named tblMirror1.( no values inside the table)

2. Repeated the same in Mirror in Witness

3. Selected the Principal server's database -->right click-->tasks-->mirror

4. from the left hand panel selected Options -->recovery model-->full

5. selected Mirroring from left hand panel-->configure secuirty-->entered mirror and witness name--Success

6. When I click on start mirroring I have an error stating

" Alter failed for database mirror1"

"Database mirroring cannot be enabled because Mirror1 database may have bulk logged changes that have not been backed up etc...."

7. followed this one
BACKUP database mirror1 TO DISK='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\mirror1_FULL.BAK'

got the answer as

Processed 160 pages for database 'mirror1', file 'Mirror1' on file 2.
Processed 1 pages for database 'mirror1', file 'Mirror1_log' on file 2.
BACKUP DATABASE successfully processed 161 pages in 0.318 seconds (4.147 MB/sec).

According to sources
Run a restore of this backup on your mirror.
This can be done through the GUI or using a T-SQL command.

The database restore must use the NO RECOVERY option, so the database stays in a loading state.

Also the database name on the mirror must be the exact same name as the principal

RESTORE TestMirror FROM DISK='C:\Backup\ TestMirror_FULL.BAK' WITH NORECOVERY

Hence did the below

RESTORE database mirror1 FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\mirror1_FULL.BAK' WITH NORECOVERY

ERROR

Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\mirror1_FULL.BAK'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

HENCE MODIFIED AS

use master
go
RESTORE database mirror1 FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\mirror1_FULL.BAK' WITH NORECOVERY

Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\mirror1_FULL.BAK'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Please let me know what shuld i modify

regards
cmrhema

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-04-01 : 04:27:29

Sorry, silly question, but you have moveed this backup file over to your mirror, haven't you?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-01 : 14:46:59

1)Take full backup in Primary server and restore it in Secondary servers with norecovery options
2)Take log backup and restore in Sec server with norecovery option

3)Configure Database mirroring.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-01 : 15:08:15

http://weblogs.sqlteam.com/tarad/archive/2007/02/13/60091.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-01 : 19:30:36

Tara, How would creating endpoints with HTTP work instead of TCP.Will that increase the Speed of the transfer rate? Actually we are getting less speed across geographically dispersed location.what would you suggest or mirroring experts suggest?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-01 : 19:33:38

I've never done it with HTTP.

Our systems are separated by 300 miles. I almost never see a delay in transferring the data unless there has been a massive data change.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-01 : 19:37:35

How much bandwidth you are getting? Mine is 800 miles far.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-01 : 19:59:49

I don't know our network details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-01 : 20:03:40

Actually we were promised to get 30 MB/sec but we are not getting.Network guys optimized the server but still problem exists.I donno how we can increase our bandwidth.Just wanna drop the problem.

cmrhema
Starting Member

20 Posts

Posted - 2008-04-02 : 01:03:48

Hi,
Many thanks to all of you who replied. But still could not succeed.
Followed another tutorial this time.

I underwent the following Steps for creating a mirror in sql server 2005.
1. Downloaded and isntalled sp2 in principal,mirror as well as witness servers
2. Created a new database named it as FinalMirror
3. Right clicked on the database Tasks-backup-BackupType--Full--Ok created FinalMirror.bak
4. Right clicked on the database Tasks-backup-BackupType--Transactional Log--Ok created FinalMirror-Transaction Log Backup

5. Copied this finalmirror.bak from my system to the Mirror Server.
It was stored in the Principal server in the following location
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup

6. Right click on Mirror Server Database--Restore Database --Manually wrote down the ToDatabase
7. Selected from device and selected the added the copied backup file
8. Performed a restore --select the relevant backup--checked the checkbox on restore
9. On the left panel selected the Options and choosed NO RECOVERY
(In the mirror database its displayed as FinalMirror(restoring))
10.performed mirroring and for witness unchecked the "encrypt option"

Mirroring on process

but after I click the start mirroring button the whole tab just vanishes off without displaying anything
in the Mirror server the database is shown as FinalMirror(In Recovery)

i cannot open it .

where am i going wrong
please help

regards
cmrhema

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-02 : 02:50:06

Of course you can't open the mirrored database. That's not possible with database mirroring until a failover occurs.

But did you also restore the transaction log backup on the mirrored database? See my link for all of the steps. It's better if you used the T-SQL method now instead of through the wizard so we can ensure everything gets setup right.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-02 : 09:26:59

Tara, Your blog doesn't explain about synchronous method using Witness Server.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-02 : 13:52:22

That's because our systems are separated by 300 miles. When we initially tried to use synchronous mirroring, it was a huge performance hit on the DML operations on the principal server. So it just doesn't make sense to use synchronous mirroring when the systems are far apart and performance is a concern.

But at least cmrhema can use the link to get mirroring setup as a test.

I think cmrhema's issue is that he/she thinks you can open the database on the mirror server, which is not the case with any kind of database mirroring.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-02 : 14:07:41

Unfortunately standard edition doesn't support asynchronous mirroring,but there is chance of data loss as well with it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-02 : 15:55:46

And that's why we've got clustering setup as well. So we get the speed benefits of mirroring by doing it asynchronous plus HA with clustering. But even if we lose our main site including the cluster, we shouldn't lose much data as mirroring is usually only a second or less behind.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-02 : 16:06:57

Tara,
I liked your strategy for HA and Disaster Recovery. That is brilliant idea. But suppose one of your database got corrupted ,how will you handle that and all apps pointing to that server? Will you manually failover to Secondary server?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-02 : 16:47:02

If the database is corrupted, then it's almost certainly corrupt at the mirror server too. So I'd handle it via restores and also open a case with MS. We are a premier MS customer, so it wouldn't be a problem getting good support on the issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-02 : 16:54:28

Tara,
We are also premier customer of Microsoft(Just kidding

). Anyway you said you are doing asynchronous mirroring ,So how your database in mirror server will also be corrupt if primary databases get corrupt. I think because mirroring is database level,thats why we go for it.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-02 : 16:56:55

Yah it will get corrupt as well because there is no witness serve to watch

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-02 : 18:20:16

The reason why the corruption would be at the mirrored site too is because all of the changes are mirrored. The databases are identical at both sites.

Witness server has nothing to do with the corruption. A database can be corrupt and still be available at the principal server. So a witness server will not help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-02 : 20:31:38

I am confused!! If database mirroring doesn't take care of database corruption while automatic failover then there is no use of saying at database level.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources