Mirroring databases connected through a View

I have two databases db_A_primary and db_B_primary, both databases are on one Primary server.

db_B_primary has a View into db_A_primary.

Scenario: db_A_primary goes down and failsover to db_A_mirror on the Mirror server.

In this scenario when the View in db_B_primary is accessed will it automatically be redirected to look at the db_A_mirror database on the Mirror server?

Barry.

When the database fails over, it won't change the contents of the database. If you want the view to always point to a specific server.database.owner.table, you will want to specify it that way in the application. In other cases, you might want the view to point to a local table and wouldn't want to point to a remote server after failover. The bottom line is that it is up to the application to set the behavior desired.

Regards,

Matt Hollingsworth

Sr. Program Manager

Microsoft SQL Server

|||

Your mirrored database can not have a different name from the principal database. Perhaps you meant Server Name?

You can't do this with a view, but in stored procedures, you can use a try/catch to execute a dynamic query on the live server.

Begin Try

Exec sp_executesql N'Select * From db_A_primary.dbo.ATable'

End Try

Begin Catch

Exec sp_executesql N'Select * From MirrorServer.db_A_primary.dbo.ATable'

End Catch

Mirroring and Logins

I've created some SSIS packages to move logins from the Primary server to the Mirror server. But I'm having a small glitch. Lets say I have a database dbA that's mirrored. I then add a login of some sort, say userA. userA's default database is dbA. When I add that login to the mirrored server, how do I get the default database to be dbA? Currently I'm getting the following error from Alter Login:

ALTER LOGIN [userA] WITH DEFAULT_DATABASE=[dbA]

Msg 954, Level 14, State 1, Line 1
The database "dbA" cannot be opened. It is acting as a mirror database. I'm currently adding the logins with master as the default and then using Alter to attempt setting it. I can mask the error with a TRY block, but if I fail over it seems I'm going to have an issue. Any thoughts on this would be greatly appreciated.

Thanks,

Larry

I think the mirrored DB is inaccessible (just think of it never existed)

The only way I can think of to change the default DB, is to actually break the mirror (or swap Primary/Mirror role = manual failover)

alter the default databases, then re-establish the mirror (swap the role again = manual failover back to original Primary/Mirror)

|||

Thanks. I guess we'll have to implement a policy that we only add logins after hours or create a window of time for adding logins where we can take the database down a couple of times (to fail over and back). Seems a bit out there for a High Availability solution since it will increase downtime a lot compared to what we have now.

Thanks again,

Larry

sql

MIRROR and continuity

I have a question and excuse me if I look dumb ... but I can't MIRROR MASTER or other dBs. If I lose the server then (primary) ... will the db still be open? do I not require some of these non-mirrorable database to run?

You cannot mirror the system databases - master, model,msdb.

You can mirror all your user databases.

If you loss the server, you cannot access your databases.

Thanks.

Naras

|||I'm still confused. If I lose the physical server I know that I can't access the database. My question is that if MASTER goes down -- does everything on the server go down? If I'm mirroing TESTER -- and I lose MASTER, can I still leverage TESTER?|||

If MAster is screwed then so is your database server instance.

In that case you would failover to the other partner (the mirror of Tester, which would then become the Principal).

That must be located on another instance of SQL server and therefore must have it's own Master database. If you are using safe mode with a witness (which will allow automatic failover) the partner and witness would no longer be able to connect your initial Tester principal database and would failover automatically. The damaged original Master database is no longer involved in the scenario.