SQLServerWiki

“The Only Thing That Is Constant Is Change”

SOME CLARIFICATIONS ON DATABASE MIRRORING AND LOG SHIPPING.

Posted by database-wiki on October 15, 2012

Our Lab Settings:

 

1. Domain controller:

 

BALAJIMA47

 

2. Node 1

 

BALAJIMA471

 

3. Node 2

 

BALAJIMA472

 

4. Standalone server.

 

BREAKFIXMS

I have created a database called MIRROR and also table called EMP with 1000 records into.

clip_image001

I am using the following method:

Details:

 

First, you need a backup of the MIRROR database from the default instance:

 

1. Open SQL Server Management Studio, expand Databases, right-click MIRROR, point to Tasks, and click Back Up.

 

2. In the Backup dialog box, make sure Sales is the selected database to back up and Backup Type is Full.

 

3. Leave the default name in the Name box. In the Description box, enter Full Backup of Mirror.

 

4. Under Destination, a disk device may already be listed. If so, select the device, and click Remove.

 

5. Under Destination, click Add.

 

6. In the Select Backup Destination box, click File Name, and in the text box enter R:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\MIRROR.bak. Click OK.

7. Click OK to start the backup.

 

8. When the backup is complete, you will get a notification; click OK to close it.

Next, you need to back up the transaction log of the Sales database on the default instance:

 

1. Open SQL Server Management Studio, expand Databases, right-click MIRROR, point to Tasks, and click Back Up.

 

2. In the Backup dialog box, make sure Sales is the selected database to back up and Backup Type is Transaction Log.

 

3. Leave the default name in the Name box. In the Description box, enter Transaction Log Backup of MIRROR.

 

4. Make sure R:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\MIRROR-TLOG.trn is the only device listed, and click OK.

 

5. Click OK to start the backup.

 

6. When the backup is complete, you will get a notification; click OK to close it.

Now you are ready to restore the database to the Secondary instance:

1. Open SQL Server Management Studio, and connect to the Second instance by selecting ‘BREAKFIXMS\SHIPPING’. (instance from standalone server)

2. Right-click Databases, and select Restore Database.

3. Enter MIRROR in the To Database box.

4. Restored both MIRROR.bak and MIRROR_TLOG.trn

 

5. On the Options page, in the Restore the Database Files As grid, under the Restore As column, make these changes:

_ Change MIRROR.mdf to MIRROR_SECONDARY.mdf.

_ Change MIRROR_1.ldf to MIRROR_SECONDARY.ldf.

 

6. Also on the Options page, make sure the RESTORE WITH NORECOVERY option is selected.

clip_image002

7. Click OK to begin the restore process.

 

8. Click OK in the dialog box that opens after the restore is complete.

clip_image003

Now you can configure database mirroring:

1. Open SQL Server Management Studio, and connect to the primary instance.

2. Expand Databases, right-click Sales, point to Tasks, and click Mirror.

 

3. Click the Configure Security button to start the Configure Database Mirroring Security

Wizard, which will create the endpoints required for mirroring.

 

4. On the welcome screen, click Next.

 

5. Select No on the Include Witness Server screen, and then click Next.

clip_image004

6. On the Principal Server Instance screen, accept the defaults, and click Next.

clip_image005

8. On the Mirror Server Instance screen, select the Second instance of SQL Server, and click

the Connect button. Then click Connect.

 

9. Accept the defaults that are filled in for you, and click Next.

clip_image006

10. On the Service Accounts screen, leave both account names blank because you configured

the services to use the same accounts. Click Next.

clip_image007

Click Finish to perform the following actions:

On the principal server instance, SQL2K8R2

· Create the mirroring endpoint with the following properties:

· Name: Mirroring

· Listener Port: 5022

· Encryption: Yes

· Role: Partner

· Add the service account for the principal server instance, ‘BREAKFIXLAB\Administrator’ as a login and grant it CONNECT permission on the mirroring endpoint.

On the mirror server instance, BREAKFIXMS\Shipping

· Create the mirroring endpoint with the following properties:

· Name: Mirroring

· Listener Port: 5022

· Encryption: Yes

· Role: Partner

· Add the service account for the mirror server instance, ‘BREAKFIXLAB\Administrator’ as a login and grant it CONNECT permission on the mirroring endpoint.

clip_image008

On Completion:

Check the status of the database both in principal and secondary instance. You should see the following status.

clip_image009

Ok now I am setting up the log shipping,

Backing up and restore steps are same as database mirroring.

After which,

1. Open SQL Server Management Studio, and connect to the primary instance.

 

2. Expand Databases, right-click LOGSHIPPING, point to Tasks, and click Ship Transaction Logs.

clip_image010

 

3. Check the box next to Enable This As a Primary Database in a Log Shipping Configuration.

 

4. Click the Backup Settings button.

 

5. Enter a network path for the backup folder by typing \\10.10.49.87\dbbkp in the first text box.

clip_image011

 

6. Click the Add button under the Secondary Server Instances and Databases grid.

clip_image012

 

7. Click the Connect button next to the Secondary Server Instance text box, and connect to the Second instance.

 

8. On the Initialize Secondary Database tab, make sure the No, the Secondary Database Is Initialized option is selected.

 

clip_image013

 

12. On the Restore Transaction Log tab, select the standby mode option to allow users read-only access to the standby database.

 

13. Click OK to return to the Database Properties dialog box.

 

14. Click OK again to finish configuring log shipping.

 

15. Close the Configuration dialog box when it is complete.

 

So our final setup looks like this.

clip_image014

Questions we need clarification on:-

1. What happens when the mirror goes down?

For Primary Instance,

clip_image015

clip_image016

clip_image017

For manual failover:

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

clip_image018

For automatic failover, you need witness server.

http://technet.microsoft.com/en-us/library/ms175191.aspx

Status of the database after failover, for failover from the secondary to primary server you need to connect to secondary server and follow,

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

clip_image019

2. If we needed to shut down the mirror, is there a way to pause the mirroring for a clean shutdown?

YES,

While applying patch to windows or SQL Server we suggest you to first Pause the mirroring by going to the properties of the mirrored databases

After which you can restart both primary and secondary. Once both the primary instance and secondary instance are online we suggest

you to go to the properties of the mirrored databases in the primary instance and click of Resume mirroring.

clip_image020

clip_image021

3. If the mirror had been shut down for maintenance, does mirroring continue automatically after the server has come up? Is there a way to monitor this & send an email alert?

There is no straight forward way but you can find some ways to accomplish like this:

http://www.mssqltips.com/sqlservertip/1564/database-mirroring-automating-failover-for-multiple-sql-server-databases/

4. Are there any necessary procedures that need to be taken before shutting down the DR server, the one used for log shipping? Does log shipping continue automatically after the server has come up?

There is no steps for this, You log shipping will automatically start.

5. Are there inbuilt SQL tools for monitoring mirroring & log shipping, if there are, please share the links.

For mirroring:

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

For Log shipping:

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

clip_image022

clip_image023

6. The database we are log shipping to is in read only mode. How do you make it usable (R/W)?

Secondary Database: You must initialize the database on a secondary server by restoring a backup of the database from the primary

server using either the NORECOVERY option or the STANDBY option. The STANDBY option allows you to continue restoring backups to the database and gives users read-only access to it; NORECOVERY does not allow users to access the database.

There is no way you can make a secondary database read/Write without breaking the log shipping.

7. Is it possible to make the database we are log shipping R/W during log shopping or do we have to stop the log shipping first?

No, There is no possible way of making secondary database read/Write when the log shopping is configured. Yes you need to remove log shipping.

http://msdn.microsoft.com/en-us/library/ms189071(v=sql.100).aspx

8. I realized that you can only stop log shipping from the primary database; How would we stop log shipping in case the primary server hosting the primary database failed?

You need to follow the below procedure, which is equivalent to failover.

https://technet.microsoft.com/en-us/library/ms178117(v=sql.110).aspx

9. What are the steps of failing over to the database we are failing over to, in case of a failure of production database?

You need to follow the below procedure, which is equivalent to failover.

https://technet.microsoft.com/en-us/library/ms178117(v=sql.110).aspx

10. What are the steps of failing over to the database we are failing over to, in case of a failure of production server?

You need to follow the below procedure, which is equivalent to failover.

https://technet.microsoft.com/en-us/library/ms178117(v=sql.110).aspx

11. What will happen if drop a login in the primary server.

disabling the login in the primary database instance will not have any impact in the secondary database instance as login are present only in the master database and no syncing operations happen between them. Log shipping is all about one particular database and the changes that happens to that database will get reflected in the secondary database(s).

clip_image001[4]

clip_image002[4]

clip_image003[4]

clip_image004[4]

clip_image005[4]

clip_image006[4]

clip_image007[4]

clip_image008[4]

clip_image009[4]

clip_image010[4]

clip_image011[4]

 

Cheers,

Bala

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: