- DB Mirroring is not supported below SQL2005 SP1, SP1 or higher must be installed.
- If you mess up the mirroring config, delete the endpoints created by the process on each system. They are under “Server Objects→Endpoints→Database Mirroring”.
- Ensure the firewall is configured to allow connections to all endpoints. (ports 5022, 5023 generally)
- Restore/Create a db on the primary WITH RECOVERY.
- Backup the DB. For non-new databases, you must restore the db to the primary, then backup the database again, then restore that backup to the secondary. AND THEN do a transaction log backup and restore it to the secondary.
- Restore with that backup to the secondary WITH NO RECOVERY - i.e. a roll-forward is required.
- Run the db mirroring wizard and things should work.
- FIX UP LOGINS- if using a SQL login (not windows) logins will not work after a failover unless the login has the same SID on each server- thus the SID must be set manually. After the SIDS are set to be the same, then the normal login fixup (Update_One) MUST still be run- see two code examples below:
From: http://blogs.msdn.com/chadboyd/archive/2007/01/05/login-failures-connecting-to-new-principal-after-failover-using-database-mirroring.aspx - this query will return create statements that hard code the SID with the created login. On the primary node, run the query. On the secondary node, run the output of the query associated with the login in question. The query:
select 'if not exists (select * from sys.server_principals where name = ''' + p.name + ''') ' + char(13) + char(10) + char(9) +
'create login [' + p.name + '] ' +
case when p.type in('U','G') then 'from windows ' else '' end +
'with ' +
case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' +
'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' +
case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end +
'check_policy = ' + case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +
case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end
else '' end +
'default_database = ' + p.default_database_name +
case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end
from sys.server_principals p
left join sys.sql_logins l
on p.principal_id = l.principal_id
left join sys.credentials c
on l.credential_id = c.credential_id
where p.type in('S','U','G')
and p.name <> 'sa'
And the usual fixup is below, it should only need to be run on one server:
use UC4_500; go EXEC sp_change_users_login 'Update_One', 'uc4', 'uc4'; go
From my work log, more notes:
db mirroring- UC4_500 failed to mirror with “The Service Broker ID for the remote copy of database UC4_500 does not match the ID on the principle server. (Microsoft SQL Server, Error: 1436), then got some more errors, about log space. Trick was 1) backup the db on the primary and restore that to the secondary and 2) do a transaction log backup on the primary and restore that to the secondary and then 3) mirror. Also need to fixup logins with sp_change_login and make sure password must change flag is no longer set.
Typical MS bullshit- after failing over, the app errors out with “password change required” blah blah- so the logins do not work when the db fails to a different server. The source of the issue is apparently different SIDS in SQL for a particular login. Cause identifying a SQL login by it's name only is apparently too damned simple.
Cuit-user1 went down, apparently user2 was the primary node. It's off the network with “application failed to initialize” and a LiveUpdate window open behind, rebooting it and will deal with it later.
So deleted db logins, then recreated in both server instances after failing over, now going to sp_change fixup and see what happens. So tada, CP1 & 2 both running, fail-over, stop and start both, no login problems. Every which way it still works.
The relevant sql code to get the proper command to run for each system is:
select 'if not exists (select * from sys.server_principals where name =
' + p.name +') ' + char(13) + char(10) + char(9) +'create login [' + p.name + '] ' + case when p.type in('U','G') then 'from windows ' else '' end + 'with ' + case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' + 'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' + case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_policy = ' + case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end + case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end else '' end + 'default_database = ' + p.default_database_name + case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' endfrom sys.server_principals p left join sys.sql_logins l on p.principal_id = l.principal_id left join sys.credentials c on l.credential_id = c.credential_id where p.type in('S','U','G') and p.name <> 'sa'
from “This Site”:http://blogs.msdn.com/chadboyd/archive/2007/01/05/login-failures-connecting-to-new-principal-after-failover-using-database-mirroring.aspx
Just for good measure, the other login fixup needed is: use UC4_500; go EXEC sp_change_users_login 'Update_One', 'uc4', 'uc4'; go
Tried all manner of fail-over, CP shutdowns, WP shutdowns, and db fail-overs. It all works. Executors failed over instantly after shutting down the CP they were on. The only issue seems to be the need for dialog clients to reconnect after the loss of a CP.
