Thursday, October 05, 2006 1:18 PM
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.
Thursday, October 05, 2006 3:27 PM
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)
Friday, October 06, 2006 9:44 AM
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.