Home > SQL Server 2008 > Some rare facts about Log shipping

Some rare facts about Log shipping


We have tested some rare facts in our test environment and shared the details.

 

Database can be mirrored or log shipped simultaneously

 

Yes . it can also be simultaneously mirrored and log shipped. Typically, when combining log shipping and database mirroring, the mirroring session is established before log shipping, although this is not required. Then the current principal database is configured as the log shipping primary (the principal/primary database), along with one or more remote secondary databases. Also, the mirror database must be configured as a log shipping primary (the mirror/primary database). The log shipping secondary databases should be on different server instances than either the principal/primary server or mirror/primary server.

 

Setting Up Mirroring and Log Shipping Together

Restore backups of the principal/primary database with NORECOVERY onto another server instance to be later used as database mirroring mirror database for the principal/primary database.

Set up database mirroring.

  1. Restore backups of the principal/primary database to other server instances to be later used as log shipping secondary databases for the primary database.
  2. Set up log shipping on the principal database as the primary database for one or more secondary databases.

Need to set up a single share as the backup directory (a backup share). This ensures that after role switching between the principal and mirror servers, backup jobs continue to write to the same directory as before. A best practice is to ensure that this share is located on a different physical server from the servers hosting the databases involved in mirroring and log shipping.

Manually failover from the principal to the mirror.

Step To manually fail over a database mirroring session

  1. Connect to the principal server.
  2. Set the database context to the master database:

USE master;

  1. Issue the following statement on the principal server:

ALTER DATABASE database_name SET PARTNER FAILOVER, where database_name is the mirrored database.

This initiates an immediate transition of the mirror server to the principal role.

On the former principal, clients are disconnected from the database and in-flight transactions are rolled back.

Set up log shipping on the new principal (previously mirror) as the primary database.

 

Log shipping can be possible in different SQL Server versions

 

SQL Server 2008 à 2005 Log shipping not possible because 2008 database can’t be restored at 2005 server.

SQL Server 2005 à 2008—it is possible, but database can be only recovery mode. You can’t put the database read-only or standby  mode.

It is always recommended that both primary and secondary servers have same version.

Advertisements
Categories: SQL Server 2008
  1. January 18, 2013 at 1:29 pm

    Wow, fantastic blog layout! How long have you been blogging for?
    you made blogging look easy. The overall look of your website is magnificent, as well
    as the content!

  2. January 19, 2013 at 10:10 pm

    I absolutely love your website.. Very nice colors & theme.
    Did you build this web site yourself? Please reply back as I’m planning to create my very own site and would like to find out where you got this from or exactly what the theme is named. Appreciate it!

  1. No trackbacks yet.

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: