Islam Gomaa

Database Mirroring for DPM 2007

In this article I will demonstrate how to setup mirroring for the AdvenrureWorks Database in order to be protected using DPM 2007 SP1

Environment setup:

1. DC1 is our domain Controller IP: 192.168.2.3
2. SQL1 is the principal Database server , with SQL 2008 IP:192.168.2.5
3. SQL2 is the mirror Database Server, With SQL 2008 IP:192.168.2.6
4. DPM is the DPM server with DPM 2007 SP1 IP:192.168.2.4
5. Only for the Demo Purpose , I installed SQL server 2008 Express on the Domain controller to be the witness for the mirroring.

Prepare the AdventureWorks for Mirroring:

1. you need to confirm that the recovery mode of the database is FULL to do so follow these steps.
1. on SQL1 select thee AdventureWorks Database
2. Right click and select Properties.
3. in the left hand side menu select Options.
4. in the recovery model option make sure that the Full mode is selected
5. Click OK.
    
2. you will now need  to make a backup of the database. Make sure it is a Full backup and for convenience, make sure that you are not using the Append option so that the BAK file to do so follow these steps.
1. Select the AdventureWorks Database.
2. Right Click and select tasks, then Back up…
3. click on the Add button and select the where to save the bak file.
4. Click on the Options in the left hand side
5. in the Overwrite media , chose Overwrite all existing backup sets.
6. in the Ratability select Verify backup when finished.
7. Click OK
   
3. On SQL 2 the Mirror server, you need to restore the backup of the database that you just made. to do so follow these steps.
1. Right click on Databases and select Restore Database …
2. in the to database type AdventureWorks.
3. in the Source to restore  select From device and browse for the AdventureWorks.bak file
4. in the select the backup sets to restore check the restore box.
5. click on the option in the left hand side
6. in the Recovery state : select Leave the database non-operational …..
7. Click ok
4. Back to SQL1 (Principal Server)we will begin the mirroring setup.
1. Right click on AdventureWorks database.
2. Select Mirroring
3. Click on the Configure Security
4. in the configure Databases Mirroring Security Wizard click next
5. for the demo purpose we are not going to configure a witness we will do the failover manually
6. choose no 
7. in the principal server instance choose SQL1
8. leave the Endpoint name  with the mirroring as a name, then click next
9. Mirroring server instance type sql2
10. in the next win doe type the SQL user that has access to the databases 
11. Voila connect start mirroring.

In an upcoming blog, I will cover the protection of a mirrored database using DPM 2007 SP1

lslam Gomaa

Published Sunday, September 13, 2009 3:38 PM by islam