Mirroring the hiscentral database. If you need to manually fix the mirror scroll down to
Disaster Recovery.
Connection string for Database mirroring
If you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application can take advantage of the drivers ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string and the failover partner server.
Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True;
There is ofcourse many other ways to write the connection string using database mirroring, this is just one example pointing out the failover functionality. You can combine this with the other connection strings options available.
details
Database mirroring allows us to maintain a copy of the hiscentral database on another machine.
This allows us to have a backup that can easily be deployed on if access is lost to the primary
hiscentral database.
Database mirroring consists of two copies of the database which are the primary and the mirror.
Only the primary can be accessed, when it is updated the changes propogate to the mirror. Since most
operations on hiscentral are read operations,performance should not be greatly affected. We may find
it takes longer to update the hiscentral database when we get new data from a network.
The mirroring configuration uses kyle as the primary and disrupter as the mirror. Since these machines
are not in the same AD domain, we use certificates to perform authentication between the two machines.
Here's how I setup the endpoint connections on kyle.ucsd.edu. A similar setup was made on disrupter.
/*Create the master key for generating certificates */
create master key encryption by password= 'goodPass'
/*Create a certificate, this certificate is used for the end point connection */
create certificate host_kyle
with subject = 'kyle certificate for database mirroring'
START_DATE='09/08/2011', --make sure this is a day prior to the current date
EXPIRY_DATE='07/12/2020';
/*Back up the certificate, this certificate is copied to disrupter and imported
on this machine */
BACKUP CERTIFICATE host_kyle TO FILE = 'E:\Share\host_kyle.cer';
/*Create the endpoint on port 5022, then endpoint uses kyle_host
certificate for authentication. */
CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5023
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE host_kyle
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = PARTNER
);
GO
/*Create the login that will connect to disrupter */
CREATE LOGIN disrupter_mirrorservice
WITH PASSWORD = 'somepass'
/*Create the user that will connect to disrupter */
CREATE USER disrupter_mirrorservice FOR LOGIN disrupter_mirrorservice
/*Import disrupter's certifcate and allow it to be used to authorize the disrupter_mirrorservice user*/
CREATE CERTIFICATE host_disrutper
AUTHORIZATION disrupter_mirrorservice
FROM FILE = 'H:\certs\host_disrupter.cer'
/*Allow the disrupter_mirrorservice to connect to the endpoint*/
GRANT CONNECT ON ENDPOINT::Mirroring TO disrupter_mirrorservice
Now to setup mirroring, one must create a backup of the database to mirror. The backup is copied
to the other machine and restored using the NORECOVERY option. This is not the default option, but it
must be done in order to use mirroring.
To start mirroring the test database testmirror3.
1. On the mirror server(disrupter)
ALTER DATABASE testmirror3
SET PARTNER = 'TCP://kyle.ucsd.edu:5023'
2. On the primary server(kyle)
ALTER DATABASE testmirror3
SET PARTNER = 'TCP://disrupter.sdsc.edu:5023'
To stop mirroring
ALTER DATABASE testmirror3
SET PARTNER OFF
Disaster Recovery
===Keep mirror
===
disaster approach, this will allow access to mirror database.
ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
you may need to run some alter user commands when the DB becomes a principle
alter user hisCentralUser2 with login=hisCentralUser2
Break mirror
If that does not work:
These two T-sql statements will stop mirroring then restore the mirror database. Afterwards
you will be able to access the mirrored database. However, if you wish to set up mirroring
again you will have to start over by backing up the database and restoring it with NORECOVERY
ALTER DATABASE testmirror3
SET PARTNER OFF
RESTORE DATABASE testmirror3 WITH RECOVERY
Mirror Disconnected
DB is showing (mirror, Disconnected/In Recovery)
notes
--alter endpoint mirroring_5023 state=stopped;
--alter endpoint mirroring_5023 state=started;
ALTER DATABASE hiscentral_sandbox SET PARTNER RESUME
Swap Princple mirror back
* on princlple, right click db, tasks, mirroring
* set to high saftey, click ok
* open tasks>mirroring again
* click failover
* on the new priciple
* open mirroring, set to async
on teh command line
some command to set to full
ALTER DATABASE USGSApr2010 SET PARTNER FAILOVER
some command to set to ansync
== dropping a certificate
- open master, look in certificates
preparing db for mirroring
http://msdn.microsoft.com/en-us/library/ms189047.aspx
- backup db 'full'
- backupdb with 'log'
- move backupfile to other server
- restore with NORECOVERY option
- restore log with NORECOVERY
- configure mirroring as above.
- start mirroring in the sql manager
Making sure users are synchronized
Example from HIScentral:
alter user hisCentralUser2 with login=hisCentralUser2
for usgs
alter user webservice with login=webservice
large database log database file
The log files can become large when the database become un-synchronized.
They do not automatically shrink.
YOU must backup the databse for the logs to be truncated.
1. Backup database, or logs if the DB is recently backed up
2. DBCC SHRINKDATABASE ('hiscentral_sandbox', TRUNCATEONLY )
large database log database file
The log files can become large when the database become un-synchronized.
They do not automatically shrink.
YOU must backup the databse for the logs to be truncated.
- Backup database, or logs if the DB is recently backed up
- DBCC SHRINKDATABASE ('hiscentral_sandbox', TRUNCATEONLY )
Note on recovery stepsafter breaking mirror
- backup database and log (use compression)
- copy to other server
- open RESTORE
- set restore to file
- restore with NORECOVERY (in options of restore)
- mirroring (again)
- on server_mirror (eg distrupter
ALTER DATABASE hiscentral SET PARTNER = 'TCP://kyle.ucsd.edu:5023' - on server_main (eg kyle)
ALTER DATABASE hiscentral SET PARTNER = 'TCP://disrupter.sdsc.edu:5023'
ALTER DATABASE hiscentral SET PARTNER resume
Note on recovery stepsafter breaking mirror
- backup database and log (use compression)
- copy to other server
- open RESTORE
- set restore to file
- restore with NORECOVERY (in options of restore)
- setup mirroring (again)