locked
logshipping RRS feed

  • Question

  • Hi ,

          iam new   to sql server  can any one explain about log shipping and replication  in sql server . it would be helpful if any one send the documents on log shipping and replication  .

     

     

    ram.

    Thursday, July 31, 2008 11:37 AM

Answers

  • There are 4 high availability alternatives with SQL Server 2005.  Failover Clustering, Database Mirroring, Log Shipping and Replication. It would prob make sense to research all these if you are starting off.

    SQL Server 2005 provides several options for creating high availability for a server or database. High-availability options include the following:

     

    I have included the explanations based on SQL Server 2005 Books Online.  Another book with step-by-step installaitons is

     

    http://www.amazon.com/SQL-Server-2005-Management-Administration/dp/0672329565/ref=pd_bbs_sr_2?ie=UTF8&s=books&qid=1217623606&sr=8-2

    • Failover clustering

      Failover clustering provides high-availability support for an entire instance of SQL Server. A failover cluster is a combination of one or more nodes, or servers, with two or more shared disks. Applications such as SQL Server and Notification Services are each installed into a Microsoft Cluster Service (MSCS) cluster group, known as a resource group. At any time, each resource group is owned by only one node in the cluster. The application service has a virtual name that is independent of the node names, and is referred to as the failover cluster instance name. An application can connect to the failover cluster instance by referencing the failover cluster instance name. The application does not have to know which node hosts the failover cluster instance.

      A SQL Server failover cluster instance appears on the network as a single computer, but has functionality that provides failover from one node to another if the current node becomes unavailable. For example, during a non-disk hardware failure, operating system failure, or planned operating system upgrade, you can configure an instance of SQL Server on one node of a failover cluster to fail over to any other node in the disk group.

      A failover cluster does not protect against disk failure. You can use failover clustering to reduce system downtime and provide higher application availability. Failover clustering is supported in SQL Server 2005 Enterprise Edition, Developer Edition and, with some restrictions, Standard Edition. For more information about failover clustering, see Failover Clustering and Installing a Failover Cluster.

    • Database mirroring

      Database mirroring is primarily a software solution to increase database availability by supporting almost instantaneous failover. Database mirroring can be used to maintain a single standby database, or mirror database, for a corresponding production database that is referred to as the principal database.

      The mirror database is created by restoring a database backup of the principal database with no recovery. This makes the mirror database is inaccessible to clients. However, it is possible to use it indirectly for reporting purposes by creating a database snapshot on the mirror database. The database snapshot provides clients with read-only access to the data in the database as it existed when the snapshot was created.

      Each database mirroring configuration involves a principal server that contains the principal database, and a mirror server that contains the mirror database. The mirror server continuously brings the mirror database up to date with the principal database.

      Database mirroring runs with either synchronous operation in high-safety mode, or asynchronous operation in high-performance mode. In high-performance mode, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance. In high-safety mode, a committed transaction is committed on both partners, but at the risk of increased transaction latency.

      In its simplest configuration, database mirroring involves only the principal and mirror servers. In this configuration, if the principal server is lost, the mirror server can be used as a warm standby server, with possible data loss. High-safety mode supports an alternative configuration, high-safety mode with automatic failover. This configuration involves a third server instance, known as a witness, which enables the mirror server to act as a hot standby server. Failover from the principal database to the mirror database typically takes a few seconds.

      Database mirroring is fully supported in SQL Server Standard Edition and Enterprise Edition, but the failover partners must use the same edition. Server instances that are running on SQL Server Workgroup Edition or Express Edition support the witness role only. For more information about database mirroring, see Database Mirroring.

    • Log shipping

      Like database mirroring, log shipping operates at the database level. Log shipping can be used to maintain one or more warm standby databases, referred to as secondary databases, for a corresponding production database that is referred to as the primary database. Each secondary database is created by restoring a database backup of the primary database with no recovery, or with standby. Restoring with standby permits the resulting secondary database to be used for limited reporting purposes.

      A log shipping configuration includes a single primary server that contains the primary database, one or more secondary servers that each have a secondary database, and a monitor server. Each secondary server updates its secondary database at regular intervals from log backups of the primary database. Log shipping involves a user-modifiable delay between when the primary server creates a log backup of the primary database and when the secondary server restores the log backup. Before a failover can occur, a secondary database must be brought fully up-to-date by manually applying any unrestored log backups.

      Log shipping provides the flexibility of supporting multiple standby databases. If you require multiple standby databases, you can use log shipping alone or as a supplement to database mirroring. When these solutions are used together, the current principal database of the database mirroring configuration is also the current primary database of the log shipping configuration.

      Log shipping is supported in SQL Server 2005 Enterprise Edition, Standard Edition, and Workgroup Edition. For more information about log shipping, see Log Shipping.

    • Replication

      Replication uses a publish-subscribe model, allowing a primary server, referred to as the Publisher, to distribute data to one or more secondary servers, or Subscribers. Replication allows real-time availability and scalability across these servers. It supports filtering to provide a subset of data at Subscribers, and also allows partitioned updates. Subscribers are online and available for reporting or other functions, without query recovery. SQL Server offers three types of replication: snapshot, transactional, and merge. Transactional replication provides the lowest latency and is most commonly used for high availability. For more information, see Improving Scalability and Availability.

      Replication is supported in all editions of SQL Server 2005. Replication publishing is not available with SQL Server 2005 Express Edition or SQL Server 2005 Compact Edition. For a complete list of replication features that are supported by each edition, see Features Supported by the Editions of SQL Server 2005.
    Friday, August 1, 2008 8:47 PM
  • Log shipping - Its used as a high availability solution where the transaction log backups are taken at regular intervals in the primary server and copied to secondary server and restored there using with standby option. The destination database will be Read-only mode. You need to have the database to be in Full/Bulk logged recovery model to configure log shipping..It will replicate all the objects from primary server database to secondary server database.

    Replication - Its used to replicate tables, views, procedures etc. You can specify what objects that needs to be replicated. There are 4 types, 1. Transactional 2. Snapshot 3. Merge and 4. Peer to Peer replication..

    Have a look at the below links for more info,
    Log shipping
    Replication
    Replication basics
    Peer to Peer Replication

    - Deepak
    Saturday, August 2, 2008 12:35 AM

All replies

  • There are 4 high availability alternatives with SQL Server 2005.  Failover Clustering, Database Mirroring, Log Shipping and Replication. It would prob make sense to research all these if you are starting off.

    SQL Server 2005 provides several options for creating high availability for a server or database. High-availability options include the following:

     

    I have included the explanations based on SQL Server 2005 Books Online.  Another book with step-by-step installaitons is

     

    http://www.amazon.com/SQL-Server-2005-Management-Administration/dp/0672329565/ref=pd_bbs_sr_2?ie=UTF8&s=books&qid=1217623606&sr=8-2

    • Failover clustering

      Failover clustering provides high-availability support for an entire instance of SQL Server. A failover cluster is a combination of one or more nodes, or servers, with two or more shared disks. Applications such as SQL Server and Notification Services are each installed into a Microsoft Cluster Service (MSCS) cluster group, known as a resource group. At any time, each resource group is owned by only one node in the cluster. The application service has a virtual name that is independent of the node names, and is referred to as the failover cluster instance name. An application can connect to the failover cluster instance by referencing the failover cluster instance name. The application does not have to know which node hosts the failover cluster instance.

      A SQL Server failover cluster instance appears on the network as a single computer, but has functionality that provides failover from one node to another if the current node becomes unavailable. For example, during a non-disk hardware failure, operating system failure, or planned operating system upgrade, you can configure an instance of SQL Server on one node of a failover cluster to fail over to any other node in the disk group.

      A failover cluster does not protect against disk failure. You can use failover clustering to reduce system downtime and provide higher application availability. Failover clustering is supported in SQL Server 2005 Enterprise Edition, Developer Edition and, with some restrictions, Standard Edition. For more information about failover clustering, see Failover Clustering and Installing a Failover Cluster.

    • Database mirroring

      Database mirroring is primarily a software solution to increase database availability by supporting almost instantaneous failover. Database mirroring can be used to maintain a single standby database, or mirror database, for a corresponding production database that is referred to as the principal database.

      The mirror database is created by restoring a database backup of the principal database with no recovery. This makes the mirror database is inaccessible to clients. However, it is possible to use it indirectly for reporting purposes by creating a database snapshot on the mirror database. The database snapshot provides clients with read-only access to the data in the database as it existed when the snapshot was created.

      Each database mirroring configuration involves a principal server that contains the principal database, and a mirror server that contains the mirror database. The mirror server continuously brings the mirror database up to date with the principal database.

      Database mirroring runs with either synchronous operation in high-safety mode, or asynchronous operation in high-performance mode. In high-performance mode, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance. In high-safety mode, a committed transaction is committed on both partners, but at the risk of increased transaction latency.

      In its simplest configuration, database mirroring involves only the principal and mirror servers. In this configuration, if the principal server is lost, the mirror server can be used as a warm standby server, with possible data loss. High-safety mode supports an alternative configuration, high-safety mode with automatic failover. This configuration involves a third server instance, known as a witness, which enables the mirror server to act as a hot standby server. Failover from the principal database to the mirror database typically takes a few seconds.

      Database mirroring is fully supported in SQL Server Standard Edition and Enterprise Edition, but the failover partners must use the same edition. Server instances that are running on SQL Server Workgroup Edition or Express Edition support the witness role only. For more information about database mirroring, see Database Mirroring.

    • Log shipping

      Like database mirroring, log shipping operates at the database level. Log shipping can be used to maintain one or more warm standby databases, referred to as secondary databases, for a corresponding production database that is referred to as the primary database. Each secondary database is created by restoring a database backup of the primary database with no recovery, or with standby. Restoring with standby permits the resulting secondary database to be used for limited reporting purposes.

      A log shipping configuration includes a single primary server that contains the primary database, one or more secondary servers that each have a secondary database, and a monitor server. Each secondary server updates its secondary database at regular intervals from log backups of the primary database. Log shipping involves a user-modifiable delay between when the primary server creates a log backup of the primary database and when the secondary server restores the log backup. Before a failover can occur, a secondary database must be brought fully up-to-date by manually applying any unrestored log backups.

      Log shipping provides the flexibility of supporting multiple standby databases. If you require multiple standby databases, you can use log shipping alone or as a supplement to database mirroring. When these solutions are used together, the current principal database of the database mirroring configuration is also the current primary database of the log shipping configuration.

      Log shipping is supported in SQL Server 2005 Enterprise Edition, Standard Edition, and Workgroup Edition. For more information about log shipping, see Log Shipping.

    • Replication

      Replication uses a publish-subscribe model, allowing a primary server, referred to as the Publisher, to distribute data to one or more secondary servers, or Subscribers. Replication allows real-time availability and scalability across these servers. It supports filtering to provide a subset of data at Subscribers, and also allows partitioned updates. Subscribers are online and available for reporting or other functions, without query recovery. SQL Server offers three types of replication: snapshot, transactional, and merge. Transactional replication provides the lowest latency and is most commonly used for high availability. For more information, see Improving Scalability and Availability.

      Replication is supported in all editions of SQL Server 2005. Replication publishing is not available with SQL Server 2005 Express Edition or SQL Server 2005 Compact Edition. For a complete list of replication features that are supported by each edition, see Features Supported by the Editions of SQL Server 2005.
    Friday, August 1, 2008 8:47 PM
  •  

    Books Online can be launched by Start, All Programs, SQL Server 2005, Documentation and Tutorials, SQL Server Books Online.

     

    Alternatively, you can download it from MS' website.

     

     

    Configuring Log Shipping

     

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7e629362-464c-48f8-bf68-89930ab0c7a6.htm

     

    Configuring Replication

     

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/repref9/html/4d0fa941-f9ea-4a14-aed9-34df593fc6f2.htm

    Friday, August 1, 2008 8:51 PM
  • Log shipping - Its used as a high availability solution where the transaction log backups are taken at regular intervals in the primary server and copied to secondary server and restored there using with standby option. The destination database will be Read-only mode. You need to have the database to be in Full/Bulk logged recovery model to configure log shipping..It will replicate all the objects from primary server database to secondary server database.

    Replication - Its used to replicate tables, views, procedures etc. You can specify what objects that needs to be replicated. There are 4 types, 1. Transactional 2. Snapshot 3. Merge and 4. Peer to Peer replication..

    Have a look at the below links for more info,
    Log shipping
    Replication
    Replication basics
    Peer to Peer Replication

    - Deepak
    Saturday, August 2, 2008 12:35 AM