SQLServer mirroring hiscentral database

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


preparing db for mirroring

http://msdn.microsoft.com/en-us/library/ms189047.aspx


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.

  1. Backup database, or logs if the DB is recently backed up
  2. DBCC SHRINKDATABASE ('hiscentral_sandbox', TRUNCATEONLY )



Note on recovery stepsafter breaking mirror


Note on recovery stepsafter breaking mirror